eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.JA_CN_SI_BANK_DET_PKG

Source


1 PACKAGE BODY JA_CN_SI_BANK_DET_PKG AS
2   --$Header: JACNSBDB.pls 120.0.12020000.7 2013/04/25 08:55:58 chongwan noship $
3   --+=======================================================================+
4   --|               Copyright (c) 1998 Oracle Corporation                   |
5   --|                       Redwood Shores, CA, USA                         |
6   --|                         All rights reserved.                          |
7   --+=======================================================================+
8   --| FILENAME                                                              |
9   --|     JACNSBDB.pls                                                      |
10   --|                                                                       |
11   --| DESCRIPTION                                                           |
12   --|     Use this package to export Electronic Accounting Book             |
13   --|                                                                       |
14   --| PROCEDURE LIST                                                        |
15   --|      PROCEDURE Add_Accounting_Unit_Info                               |
16   --|      PROCEDURE Add_Merchant_Bank_Info                                 |
17   --|      PROCEDURE Add_Parent_BSV                                         |
18   --|      PROCEDURE Add_Accounting_Currency                                |
19   --|      PROCEDURE List_Accounting_Currency                               |
20   --|      PROCEDURE Add_Accounting_Periods                                 |
21   --|      PROCEDURE Add_Accounting_Code_Rule                               |
22   --|      PROCEDURE Add_Inner_Sheet_Account                                |
23   --|      PROCEDURE Add_Extra_Sheet_Account                                |
24   --|      FUNCTION  Get_Level                                              |
25   --|                                                                       |
26   --| HISTORY                                                               |
27   --|     Oct-18-2012 Jar Wang       Created                               |
28   --+======================================================================*/
29   GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_SI_BANK_DET_PKG';
30 
31   --==========================================================================
32   --  PROCEDURE NAME:
33   --    Add_Accounting_Unit_Info                     public
34   --
35   --  DESCRIPTION:
36   --      This procedure returns accountint unit information
37   --
38   --  PARAMETERS:
39   --      In: pn_legal_entity_id           NUMBER
40   --      In: pn_ledger_id                 NUMBER
41   --      In: pv_bsv                       NUMBER
42   --      In: pn_accounting_year         NUMBER
43   --
44   --  CHANGE HISTORY:
45   --    Nov-15-2012 Jar Wang       Created
46   --===========================================================================
47   PROCEDURE Add_Accounting_Unit_Info(pn_legal_entity_id NUMBER,
48                                      pn_ledger_id       NUMBER,
49                                      pv_bsv             VARCHAR2,
50                                      pn_accounting_year NUMBER) IS
51     lv_procedure_name  VARCHAR2(40) := 'Add_Accounting_Unit_Info';
52     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
53     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
54 
55     ln_row_count       NUMBER;
56     l_mes              VARCHAR2(1000);
57     CURSOR electronic_accounting_book_cur(ln_legal_entity_id NUMBER,
58                                           ln_ledger_id       NUMBER,
59                                           lv_bsv             VARCHAR2) IS
60       SELECT jcspa.flex_segment_value Accounting_Unit,
61              jcspa.description Accounting_Unit_Name,
62              jcspa.company_name,
63              jcspa.organization_id,
64              jcspa.ent_quality,
65              jcspa.ent_industry,
66              ar.major_version || '.' || ar.minor_version || '.' ||
67              ar.tape_version software_version,
68              fcv.name,
69              jcspa.cnao_stand_ver
70         FROM JA_CN_SYSTEM_BANK_PARAMS_ALL_V jcspa,
71              gl_ledgers                     gl,
72              ad_releases                    ar,
73              fnd_currencies_vl              fcv
74        WHERE ar.release_id = (SELECT max(release_id) FROM ad_releases)
75          AND fcv.currency_code = gl.currency_code
76          AND gl.ledger_id = ln_ledger_id
77          AND jcspa.legal_entity_id = ln_legal_entity_id
78          AND jcspa.flex_segment_value = lv_bsv;
79 
80   BEGIN
81     --logging for debug
82     FND_FILE.PUT_LINE(FND_FILE.log, lv_procedure_name);
83     IF (ln_proc_level >= ln_dbg_level) THEN
84       FND_LOG.STRING(ln_proc_level,
85                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
86                      '.begin',
87                      'Enter procedure');
88     END IF; --ln_proc_level>=ln_dbg_level
89 
90     ln_row_count := 0;
91 
92     --Ja_Cn_Utility.Add_Sub_Root_Node('ACC_UNIT_INFO_FILE',  Ja_Cn_Utility.GV_TAG_TYPE_START);
93     Ja_Cn_Utility.Add_Sub_Root_Node('ACC_UNIT_INFO', Ja_Cn_Utility.GV_TAG_TYPE_START);
94     FOR v_row IN electronic_accounting_book_cur(pn_legal_entity_id,
95                                                 pn_ledger_id,
96                                                 pv_bsv) LOOP
97       ln_row_count := ln_row_count + 1;
98 
99       Ja_Cn_Utility.Add_Child_Node('ACC_UNIT', v_row.Accounting_Unit);
100       Ja_Cn_Utility.Add_Child_Node('ACC_UNIT_NAME',
101                                    v_row.Accounting_Unit_Name);
102       Ja_Cn_Utility.Add_Child_Node('ORGANIZATION_NUMBER',
103                                    v_row.organization_id);
104       Ja_Cn_Utility.Add_Fixed_Child_Node('ENTERPRISE_QUALITY',
105                                          v_row.ent_quality,
106                                          4);
107       Ja_Cn_Utility.Add_Child_Node('ENTERPRISE_INDUSTRY',
108                                    v_row.ent_industry);
109       Ja_Cn_Utility.Add_Child_Node('ERP_SOFTWARE_VENDOR', 'ORACLE');
110       Ja_Cn_Utility.Add_Child_Node('ERP_SOFTWARE_VERSION',
111                                    v_row.software_version);
112       Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_CURRENCY', v_row.name);
113       Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_YEAR', pn_accounting_year);
114       Ja_Cn_Utility.Add_Child_Node('CNAO_STANDARD_VERSION',
115                                    v_row.cnao_stand_ver);
116     END LOOP;
117 
118     IF (ln_row_count = 0) THEN
119       --Ja_Cn_Utility.Add_Sub_Root_Node('ACC_UNIT_INFO', Ja_Cn_Utility.GV_TAG_TYPE_START);
120       Ja_Cn_Utility.Add_Child_Node('ACC_UNIT', pv_bsv);
121       Ja_Cn_Utility.Add_Child_Node('ACC_UNIT_NAME', NULL);
122       Ja_Cn_Utility.Add_Child_Node('ORGANIZATION_NUMBER', NULL);
123       Ja_Cn_Utility.Add_Child_Node('ENTERPRISE_QUALITY', NULL);
124       Ja_Cn_Utility.Add_Child_Node('ENTERPRISE_INDUSTRY', NULL);
125       Ja_Cn_Utility.Add_Child_Node('ERP_SOFTWARE_VENDOR', NULL);
126       Ja_Cn_Utility.Add_Child_Node('ERP_SOFTWARE_VERSION', NULL);
127       Ja_Cn_Utility.Add_Child_Node('FUNCTIONAL_CURRENCY', NULL);
128       Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_YEAR', NULL);
129       Ja_Cn_Utility.Add_Child_Node('CNAO_STANDARD_VERSION', NULL);
130       --Ja_Cn_Utility.Add_Sub_Root_Node('ACC_UNIT_INFO',Ja_Cn_Utility.GV_TAG_TYPE_END);
131       FND_MESSAGE.SET_NAME('JA','JA_CN_MISSING_BOOK_INFO') ;
132       l_mes := FND_MESSAGE.GET;
133       FND_FILE.put_line(FND_FILE.log,'Warning:'||pv_bsv||','||l_mes);
134     END IF; --ln_row_count = 0
135 
136     Ja_Cn_Utility.Add_Sub_Root_Node('ACC_UNIT_INFO', Ja_Cn_Utility.GV_TAG_TYPE_END);
137     --Ja_Cn_Utility.Add_Sub_Root_Node('ACC_UNIT_INFO_FILE',  Ja_Cn_Utility.GV_TAG_TYPE_END);
138 
139     --logging for debug
140     IF (ln_proc_level >= ln_dbg_level) THEN
141       FND_LOG.STRING(ln_proc_level,
142                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
143                      'Exit procedure');
144     END IF; -- (ln_proc_level>=ln_dbg_level)
145   EXCEPTION
146     WHEN OTHERS THEN
147       IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
148         FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
149                        GV_MODULE_PREFIX || '.' || lv_procedure_name ||
150                        '.Other_Exception ',
151                        SQLCODE || SQLERRM);
152       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
153       FND_FILE.put_line(FND_FILE.log,
154                         GV_MODULE_PREFIX || '.' || lv_procedure_name ||
155                         SQLCODE || SQLERRM);
156       RAISE;
157   END Add_Accounting_Unit_Info;
158 
159   --==========================================================================
160   --  PROCEDURE NAME:
161   --    Add_Merchant_Bank_Info                     public
162   --
163   --  DESCRIPTION:
164   --      This procedure returns accountint unit information
165   --
166   --  PARAMETERS:
167   --      In: pn_legal_entity_id               NUMBER
168   --      In: pn_ledger_id                     NUMBER
169   --      In: pv_bsv                           NUMBER
170   --      In: pn_accounting_year               NUMBER
171   --
172   --  CHANGE HISTORY:
173   --    Nov-15-2012 Jar Wang       Created
174   --===========================================================================
175   PROCEDURE Add_Merchant_Bank_Info(pn_legal_entity_id NUMBER,
176                                    pn_ledger_id       NUMBER,
177                                    pv_bsv             VARCHAR2,
178                                    pn_accounting_year NUMBER) IS
179 
180     lv_procedure_name  VARCHAR2(40) := 'Add_Merchant_Bank_Info';
181     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
182     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
183     CURSOR CUR_BANKING IS
184       SELECT ffv.flex_value sub_flex_value,
185              ffv.description,
186              ffv.parent_flex_value,
187              ffv.HIERARCHY_LEVEL
188         FROM (
189               SELECT v.flex_value_set_id,
190                      h.parent_flex_value,
191                      v.flex_value,
192                      v.description,
193                      v.summary_flag,
194                      v.HIERARCHY_LEVEL
195                 FROM fnd_flex_values_vl            v,
196                      fnd_flex_value_norm_hierarchy h,
197                      fnd_flex_value_sets           s
198                WHERE h.flex_value_set_id = v.flex_value_set_id
199                  AND s.flex_value_set_id = v.flex_value_set_id
200                  AND (((s.format_type = 'N') AND
201                      (fnd_number.canonical_to_number(v.flex_value) BETWEEN
202                      fnd_number.canonical_to_number(h.child_flex_value_low) AND
203                      fnd_number.canonical_to_number(h.child_flex_value_high))) OR
204                      ((s.format_type IN ('D', 'T')) AND
205                      (to_date(v.flex_value,
206                                 (decode(s.maximum_size,
207                                         5,
208                                         'HH24:MI',
209                                         8,
210                                         'HH24:MI:SS',
211                                         9,
212                                         'DD-MON-RR',
213                                         11,
214                                         'DD-MON-YYYY',
215                                         15,
216                                         'DD-MON-RR HH24:MI',
217                                         17,
218                                         'DD-MON-YYYY HH24:MI',
219                                         18,
220                                         'DD-MON-RR HH24:MI:SS',
221                                         20,
222                                         'DD-MON-YYYY HH24:MI:SS'))) BETWEEN
223                      to_date(h.child_flex_value_low,
224                                 (decode(s.maximum_size,
225                                         5,
226                                         'HH24:MI',
227                                         8,
228                                         'HH24:MI:SS',
229                                         9,
230                                         'DD-MON-RR',
231                                         11,
232                                         'DD-MON-YYYY',
233                                         15,
234                                         'DD-MON-RR HH24:MI',
235                                         17,
236                                         'DD-MON-YYYY HH24:MI',
237                                         18,
238                                         'DD-MON-RR HH24:MI:SS',
239                                         20,
240                                         'DD-MON-YYYY HH24:MI:SS'))) AND
241                      to_date(h.child_flex_value_high,
242                                 (decode(s.maximum_size,
243                                         5,
244                                         'HH24:MI',
245                                         8,
246                                         'HH24:MI:SS',
247                                         9,
248                                         'DD-MON-RR',
249                                         11,
250                                         'DD-MON-YYYY',
251                                         15,
252                                         'DD-MON-RR HH24:MI',
253                                         17,
254                                         'DD-MON-YYYY HH24:MI',
255                                         18,
256                                         'DD-MON-RR HH24:MI:SS',
257                                         20,
258                                         'DD-MON-YYYY HH24:MI:SS'))))) OR
259                      ((s.format_type NOT IN ('N', 'D', 'T')) AND
260                      (v.flex_value BETWEEN h.child_flex_value_low AND
261                      h.child_flex_value_high)))
262                  AND ((v.summary_flag = 'Y' AND h.range_attribute = 'P') OR
263                      (v.summary_flag = 'N' AND h.range_attribute = 'C'))
264           )ffv where ffv.FLEX_VALUE_SET_ID =
265              (SELECT fifsv.FLEX_VALUE_SET_ID
266                 FROM FND_ID_FLEX_SEGMENTS_VL      fifsv,
267                      FND_SEGMENT_ATTRIBUTE_VALUES FSAV
268                WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
269                  AND FSAV.APPLICATION_ID = 101
270                  AND FSAV.ID_FLEX_CODE = 'GL#'
271                  AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
272                  AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
273                  AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
274                  AND FSAV.APPLICATION_COLUMN_NAME =
275                      fifsv.APPLICATION_COLUMN_NAME
276                  AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
277                  AND fifsv.ID_FLEX_NUM in
278                      (SELECT chart_of_accounts_id
279                         FROM gl_ledgers gls
280                        where gls.ledger_id = pn_ledger_id))
281          AND flex_value = pv_bsv
282        ORDER by flex_value;
283 
284 
285     l_bank        CUR_BANKING%ROWTYPE;
286     l_row_count   pls_integer := 0;
287     l_single_vale varchar2(50);
288     l_single_desc varchar2(400);
289     l_level       varchar2(50);
290 
291   BEGIN
292     --logging for debug
293     FND_FILE.PUT_LINE(FND_FILE.log, lv_procedure_name);
294     IF (ln_proc_level >= ln_dbg_level) THEN
295       FND_LOG.STRING(ln_proc_level,
296                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
297                      '.begin',
298                      'Enter procedure');
299 
300     END IF; --ln_proc_level>=ln_dbg_level
301     Ja_Cn_Utility.Add_Sub_Root_Node('COMMERCIAL_BANK_ORG', Ja_Cn_Utility.GV_TAG_TYPE_START);
302     OPEN CUR_BANKING;
303     LOOP
304       FETCH CUR_BANKING
305         INTO l_bank;
306       EXIT WHEN CUR_BANKING%NOTFOUND;
307       l_row_count := l_row_count + 1;
308       IF l_row_count> 1 THEN
309         FND_FILE.put_line(FND_FILE.log, GV_MODULE_PREFIX || '.' || lv_procedure_name ||', Warning:'|| l_bank.sub_flex_value
310                                  ||' has over one direct parent value, please validate the hierarchy of the valueset .');
311         EXIT;
312       END IF;
313       --Ja_Cn_Utility.Add_Sub_Root_Node('COMMERCIAL_BANK_ORG', Ja_Cn_Utility.GV_TAG_TYPE_START);
314       Ja_Cn_Utility.Add_Child_Node('BANK_ORG_NUMBER',
315                                    l_bank.sub_flex_value);
316       Ja_Cn_Utility.Add_Child_Node('BANK_ORG_NAME', l_bank.description);
317       Ja_Cn_Utility.Add_Child_Node('SUPERIOR_ORG_NUM',
318                                    l_bank.parent_flex_value);
319       --Ja_Cn_Utility.Add_Child_Node('PAY_SYS_BABK_NUM', ''); updated by jar.wang for bug 16655327
320       --Ja_Cn_Utility.Add_Child_Node('ELECTRONIC_BANK_NUM', '');
321       Ja_Cn_Utility.Add_Child_Node('BANK_LEVEL', l_bank.HIERARCHY_LEVEL);
322       --Ja_Cn_Utility.Add_Sub_Root_Node('COMMERCIAL_BANK_ORG', Ja_Cn_Utility.GV_TAG_TYPE_END);
323 
324     END LOOP;
325     CLOSE CUR_BANKING;
326 
327     IF l_row_count = 0 THEN
328       select ffv.flex_value, ffv.description,ffv.HIERARCHY_LEVEL
329         into l_single_vale, l_single_desc,l_level
330         from fnd_flex_values_vl ffv
331        where ffv.FLEX_VALUE_SET_ID =
332              (SELECT fifsv.FLEX_VALUE_SET_ID
333                 FROM FND_ID_FLEX_SEGMENTS_VL      fifsv,
334                      FND_SEGMENT_ATTRIBUTE_VALUES FSAV
335                WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
336                  AND FSAV.APPLICATION_ID = 101
337                  AND FSAV.ID_FLEX_CODE = 'GL#'
338                  AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
339                  AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
340                  AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
341                  AND FSAV.APPLICATION_COLUMN_NAME =
342                      fifsv.APPLICATION_COLUMN_NAME
343                  AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
344                  AND fifsv.ID_FLEX_NUM =
345                      (SELECT chart_of_accounts_id
346                         FROM gl_ledgers gls
347                        where gls.ledger_id = pn_ledger_id))
348          and ffv.FLEX_VALUE = pv_bsv;
349 
350       --Ja_Cn_Utility.Add_Sub_Root_Node('COMMERCIAL_BANK_ORG', Ja_Cn_Utility.GV_TAG_TYPE_START);
351       Ja_Cn_Utility.Add_Child_Node('BANK_ORG_NUMBER', l_single_vale);
352       Ja_Cn_Utility.Add_Child_Node('BANK_ORG_NAME', l_single_desc);
353       Ja_Cn_Utility.Add_Child_Node('SUPERIOR_ORG_NUM', '');
354       --Ja_Cn_Utility.Add_Child_Node('PAY_SYS_BABK_NUM', ''); updated by jar.wang for bug 16655327
355       --Ja_Cn_Utility.Add_Child_Node('ELECTRONIC_BANK_NUM', '');
356       Ja_Cn_Utility.Add_Child_Node('BANK_LEVEL', l_level);
357       --Ja_Cn_Utility.Add_Sub_Root_Node('COMMERCIAL_BANK_ORG',Ja_Cn_Utility.GV_TAG_TYPE_END);
358     END IF;
359 
360     Ja_Cn_Utility.Add_Sub_Root_Node('COMMERCIAL_BANK_ORG',Ja_Cn_Utility.GV_TAG_TYPE_END);
361   Exception
362   WHEN OTHERS THEN
363       IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
364         FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
365                        GV_MODULE_PREFIX || '.' || lv_procedure_name ||
366                        '.Other_Exception ',
367                        SQLCODE || SQLERRM);
368       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
369       FND_FILE.put_line(FND_FILE.log,
370                         GV_MODULE_PREFIX || '.' || lv_procedure_name ||
371                         SQLCODE || SQLERRM);
372       RAISE;
373   END Add_Merchant_Bank_Info;
374 
375 
376   --==========================================================================
377   --  PROCEDURE NAME:
378   --    Add_Parent_BSV                     public
379   --
380   --  DESCRIPTION:
381   --      This procedure returns accountint unit information with parent flag
382   --
383   --  PARAMETERS:
384   --      In: pn_legal_entity_id               NUMBER
385   --      In: pn_ledger_id                     NUMBER
386   --      In: pv_bsv                           NUMBER
387   --      In: pn_accounting_year               NUMBER
388   --
389   --  CHANGE HISTORY:
390   --    Nov-15-2012 Jar Wang       Created
391   --===========================================================================
392   PROCEDURE Add_Parent_BSV(pn_legal_entity_id NUMBER,
393                             pn_ledger_id       NUMBER,
394                             pv_bsv             VARCHAR2,
395                             pn_accounting_year NUMBER,
396                             pn_branch          PLS_INTEGER) IS
397     lv_procedure_name  VARCHAR2(40) := 'Add_Parent_BSV';
398     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
399     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
400     lv_summary         VARCHAR2(2);
401     l_flex_valueset_id NUMBER;
402     l_bsv_temp         VARCHAR2(100) :=pv_bsv;
403 
404     CURSOR CUR_PARENT(c_valueset_id pls_integer, c_bsv varchar2) IS
405       select ffh.flex_value_set_id, ffh.FLEX_VALUE, SUMMARY_FLAG
406         from FND_FLEX_VALUE_CHILDREN_V ffh
407        where ffh.flex_value_set_id = c_valueset_id
408          and ffh.PARENT_FLEX_VALUE = c_bsv
409          order by  ffh.FLEX_VALUE;
410     l_parent   CUR_PARENT%ROWTYPE;
411   BEGIN
412     --logging for debug
413     IF (ln_proc_level >= ln_dbg_level) THEN
414       FND_LOG.STRING(ln_proc_level,
415                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
416                      '.begin',
417                      'Enter procedure');
418     END IF; --ln_proc_level>=ln_dbg_level
419 
420       --get valueset_id
421       SELECT fifsv.FLEX_VALUE_SET_ID
422         into l_flex_valueset_id
423         FROM FND_ID_FLEX_SEGMENTS_VL      fifsv,
424              FND_SEGMENT_ATTRIBUTE_VALUES FSAV
425        WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
426          AND FSAV.APPLICATION_ID = 101
427          AND FSAV.ID_FLEX_CODE = 'GL#'
428          AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
429          AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
430          AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
431          AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
432          AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
433          AND fifsv.ID_FLEX_NUM in
434              (SELECT chart_of_accounts_id
435                 FROM gl_ledgers gls
436                where gls.ledger_id = pn_ledger_id);
437 
438          --get bsv summary
439          SELECT SUMMARY_FLAG
440            INTO lv_summary
441            FROM FND_FLEX_VALUE_SETS ffs, FND_FLEX_VALUES_VL ffv
442           WHERE ffs.flex_value_set_id = ffv.FLEX_VALUE_SET_ID
443             AND ffv.FLEX_VALUE_SET_ID = l_flex_valueset_id
444             AND ffv.FLEX_VALUE = l_bsv_temp;
445          --child
446          IF lv_summary='N' THEN
447            IF pn_branch = 1 THEN
448               Add_Accounting_Unit_Info(pn_legal_entity_id =>pn_legal_entity_id,
449                                        pn_ledger_id       =>pn_ledger_id,
450                                        pv_bsv             =>pv_bsv,
451                                        pn_accounting_year =>pn_accounting_year) ;
452            ELSIF pn_branch=2 THEN
453               Add_Merchant_Bank_Info(pn_legal_entity_id ,
454                                      pn_ledger_id,
455                                      pv_bsv,
456                                      pn_accounting_year);
457            ELSIF pn_branch=3 THEN
458               List_Accounting_Currency(pn_legal_entity_id ,
459                                       pn_ledger_id       ,
460                                       pv_bsv             ,
461                                       pn_accounting_year );
462            END IF;
463          ELSE
464          --parent
465              OPEN CUR_PARENT(l_flex_valueset_id,l_bsv_temp);
466              LOOP
467                 FETCH CUR_PARENT INTO l_parent;
468                 EXIT WHEN CUR_PARENT%NOTFOUND;
469                 --recursion
470                 l_bsv_temp := l_parent.FLEX_VALUE;
471                 IF l_parent.SUMMARY_FLAG='Y' THEN
472                    FND_FILE.put_line(FND_FILE.log,GV_MODULE_PREFIX || '.' || lv_procedure_name||',Y='||l_parent.FLEX_VALUE);
473                    IF pn_branch = 2 THEN
474                       Add_Merchant_Bank_Info(pn_legal_entity_id ,
475                                              pn_ledger_id,
476                                              l_bsv_temp,
477                                              pn_accounting_year);
478                    END IF ;
479                    Add_Parent_BSV(pn_legal_entity_id,pn_ledger_id,l_bsv_temp,pn_accounting_year,pn_branch);
480                 ELSE
481                    FND_FILE.put_line(FND_FILE.log,GV_MODULE_PREFIX || '.' || lv_procedure_name||',N='||l_parent.FLEX_VALUE);
482                    IF pn_branch = 1 THEN
483                       Add_Accounting_Unit_Info(pn_legal_entity_id =>pn_legal_entity_id,
484                                                pn_ledger_id       =>pn_ledger_id,
485                                                pv_bsv             =>l_bsv_temp,
486                                                pn_accounting_year =>pn_accounting_year) ;
487                    ELSIF pn_branch=2 THEN
488                       Add_Merchant_Bank_Info(pn_legal_entity_id ,
489                                              pn_ledger_id,
490                                              l_bsv_temp,
491                                              pn_accounting_year);
492                    ELSIF pn_branch=3 THEN
493                       List_Accounting_Currency(pn_legal_entity_id ,
494                                               pn_ledger_id       ,
495                                               l_bsv_temp         ,
496                                               pn_accounting_year );
497                    END IF;
498                 END IF;
499              END LOOP;
500              CLOSE CUR_PARENT;
501          END IF;
502 
503   EXCEPTION
504     WHEN OTHERS THEN
505       IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
506         FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
507                        GV_MODULE_PREFIX || '.' || lv_procedure_name ||
508                        '.Other_Exception ',
509                        SQLCODE || SQLERRM);
510       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
511       FND_FILE.put_line(FND_FILE.log,
512                         GV_MODULE_PREFIX || '.' || lv_procedure_name ||
513                         SQLCODE || SQLERRM);
514       RAISE;
515   END Add_Parent_BSV;
516 
517 
518   --==========================================================================
519   --  PROCEDURE NAME:
520   --    Add_Accounting_Currency                     public
521   --
522   --  DESCRIPTION:
523   --      This procedure returns accounting currency in the GL journal
524   --
525   --  PARAMETERS:None
526   --
527   --  CHANGE HISTORY:
528   --    Nov-28-2012 Jar Wang       Created
529   --===========================================================================
530   PROCEDURE Add_Accounting_Currency
531   IS
532     lv_procedure_name VARCHAR2(40) := 'Add_Accounting_Currency';
533     ln_dbg_level      NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
534     ln_proc_level     NUMBER := FND_LOG.LEVEL_PROCEDURE;
535     ln_row_count      NUMBER :=0;
536     -- to get Currencies
537     CURSOR CUR_Currency IS
538       SELECT DISTINCT CURRENCY_CODE, Description CURRENCY_NAME, Description_TL CURRENCY_ENG_NAME
539       FROM JA_CN_SI_CURRENCIES_GT;
540   BEGIN
541     --logging for debug
542     IF (ln_proc_level >= ln_dbg_level) THEN
543       FND_LOG.STRING(ln_proc_level,
544                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
545                      '.begin',
546                      'Enter procedure');
547     END IF; --ln_proc_level>=ln_dbg_level
548     Ja_Cn_Utility.Add_Sub_Root_Node('CURRENCY_FILE',
549                                     Ja_Cn_Utility.GV_TAG_TYPE_START);
550     FOR v_row IN CUR_Currency LOOP
551       ln_row_count := ln_row_count + 1;
552       Ja_Cn_Utility.Add_Sub_Root_Node('CURRENCY',
553                                       Ja_Cn_Utility.GV_TAG_TYPE_START);
554       Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE', v_row.currency_code);
555       Ja_Cn_Utility.Add_Child_Node('CURRENCY_NAME', v_row.CURRENCY_NAME);
556       Ja_Cn_Utility.Add_Child_Node('CURRENCY_ENG_NAME', v_row.CURRENCY_ENG_NAME);
557       Ja_Cn_Utility.Add_Sub_Root_Node('CURRENCY',
558                                       Ja_Cn_Utility.GV_TAG_TYPE_END);
559     END LOOP;
560 
561     IF ln_row_count=0 THEN
562       FND_FILE.put_line(FND_FILE.log,
563                         GV_MODULE_PREFIX || '.' || lv_procedure_name ||':ln_row_count='||ln_row_count);
564       Ja_Cn_Utility.Add_Sub_Root_Node('CURRENCY',
565                                       Ja_Cn_Utility.GV_TAG_TYPE_START);
566       Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE',NULL);
567       Ja_Cn_Utility.Add_Child_Node('CURRENCY_NAME',NULL);
568       Ja_Cn_Utility.Add_Child_Node('CURRENCY_ENG_NAME',NULL);
569       Ja_Cn_Utility.Add_Sub_Root_Node('CURRENCY',
570                                       Ja_Cn_Utility.GV_TAG_TYPE_END);
571     END IF;
572 
573     Ja_Cn_Utility.Add_Sub_Root_Node('CURRENCY_FILE',
574                                     Ja_Cn_Utility.GV_TAG_TYPE_END);
575     --logging for debug
576     IF (ln_proc_level >= ln_dbg_level) THEN
577       FND_LOG.STRING(ln_proc_level,
578                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
579                      'Exit procedure');
580     END IF; -- (ln_proc_level>=ln_dbg_level)
581   EXCEPTION
582     WHEN OTHERS THEN
583       FND_FILE.put_line(FND_FILE.log,
584                         GV_MODULE_PREFIX || '.' || lv_procedure_name  || SQLCODE || SQLERRM);
585       RAISE;
586   END Add_Accounting_Currency;
587   --==========================================================================
588   --  PROCEDURE NAME:
589   --    List_Accounting_Currency                     public
590   --
591   --  DESCRIPTION:
592   --      This procedure returns accounting currency in the GL journal
593   --
594   --  PARAMETERS:
595   --      In: pn_legal_entity_id               NUMBER
596   --      In: pn_ledger_id                     NUMBER
597   --      In: pv_bsv                           NUMBER
598   --      In: pn_accounting_year               NUMBER
599   --
600   --  CHANGE HISTORY:
601   --    Nov-17-2012 Jar Wang       Created
602   --===========================================================================
603   PROCEDURE List_Accounting_Currency(pn_legal_entity_id NUMBER,
604                                       pn_ledger_id       NUMBER,
605                                       pv_bsv             VARCHAR2,
606                                       pn_accounting_year NUMBER) IS
607     lv_procedure_name VARCHAR2(40) := 'List_Accounting_Currency';
608     ln_dbg_level      NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
609     ln_proc_level     NUMBER := FND_LOG.LEVEL_PROCEDURE;
610     -- to get Currencies
611     CURSOR CUR_Currency IS
612       SELECT  fcv.currency_code, fcv.name currency_name
613         FROM fnd_currencies_vl fcv, GL_JE_LINES gjl, GL_JE_HEADERS gjh
614        WHERE fcv.currency_code = gjh.CURRENCY_CODE
615          AND gjh.je_header_id = gjl.je_header_id
616          AND gjh.ledger_id = pn_ledger_id
617          AND gjh.period_name IN
618              (SELECT period_name
619                 FROM gl_periods
620                WHERE PERIOD_SET_NAME =
621                      (SELECT PERIOD_SET_NAME
622                         FROM gl_ledgers
623                        WHERE ledger_id = pn_ledger_id
624                          AND PERIOD_YEAR = pn_accounting_year)
625 
626               )
627          AND ja_cn_utility.get_balancing_segment(gjl.code_combination_id) =
628              pv_bsv;
629     l_currency_en varchar2(20);
630   BEGIN
631     --logging for debug
632     IF (ln_proc_level >= ln_dbg_level) THEN
633       FND_LOG.STRING(ln_proc_level,
634                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
635                      '.begin',
636                      'Enter procedure');
637     END IF; --ln_proc_level>=ln_dbg_level
638 
639     FOR v_row IN CUR_Currency LOOP
640       --get the english name for the currency code
641       SELECT fct.name
642         into l_currency_en
643         FROM FND_CURRENCIES_TL fct
644        WHERE fct.currency_code = v_row.currency_code
645          and language = 'US';
646       INSERT INTO JA_CN_SI_CURRENCIES_GT
647         (CURRENCY_CODE, Description, Description_tl)
648       VALUES
649         (v_row.currency_code, v_row.currency_name, l_currency_en);
650     END LOOP;
651 
652     --logging for debug
653     IF (ln_proc_level >= ln_dbg_level) THEN
654       FND_LOG.STRING(ln_proc_level,
655                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
656                      'Exit procedure');
657     END IF; -- (ln_proc_level>=ln_dbg_level)
658   EXCEPTION
659     WHEN OTHERS THEN
660       IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
661         FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
662                        GV_MODULE_PREFIX || '.' || lv_procedure_name ||
663                        '.Other_Exception ',
664                        SQLCODE || SQLERRM);
665       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
666       FND_FILE.put_line(FND_FILE.log,
667                         GV_MODULE_PREFIX || '.' || lv_procedure_name  || SQLCODE || SQLERRM);
668       RAISE;
669   END List_Accounting_Currency;
670 
671   --==========================================================================
672   --  PROCEDURE NAME:
673   --    Add_Accounting_Currency                     public
674   --
675   --  DESCRIPTION:
676   --      This procedure returns accounting periods in the ledger
677   --
678   --  PARAMETERS:
679   --      In: pn_legal_entity_id               NUMBER
680   --      In: pn_ledger_id                     NUMBER
681   --      In: pn_accounting_year               NUMBER
682   --
683   --  CHANGE HISTORY:
684   --    Nov-17-2012 Jar Wang       Created
685   --===========================================================================
686   PROCEDURE Add_Accounting_Periods(pn_legal_entity_id NUMBER,
687                                    pn_ledger_id       NUMBER,
688                                    pn_accounting_year NUMBER) IS
689     lv_procedure_name VARCHAR2(40) := 'Add_Accounting_Periods';
690     ln_dbg_level      NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
691     ln_proc_level     NUMBER := FND_LOG.LEVEL_PROCEDURE;
692 
693     NO_DATA EXCEPTION;
694 
695     -- to get accounting periods
696     CURSOR accounting_periods_cur(ln_accounting_year NUMBER,
697                                   ln_ledger_id       NUMBER) IS
698       SELECT GP.PERIOD_YEAR, GP.PERIOD_NUM, GP.START_DATE, GP.END_DATE
699         FROM GL_PERIODS GP, GL_LEDGERS GLD
700        WHERE GP.PERIOD_YEAR = ln_accounting_year
701          AND GP.PERIOD_SET_NAME = GLD.PERIOD_SET_NAME
702          AND GP.PERIOD_TYPE= GLD.ACCOUNTED_PERIOD_TYPE
703          AND GLD.LEDGER_ID = ln_ledger_id;
704 
705   BEGIN
706     --logging for debug
707     IF (ln_proc_level >= ln_dbg_level) THEN
708       FND_LOG.STRING(ln_proc_level,
709                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
710                      '.begin',
711                      'Enter procedure');
712     END IF; --l_proc_level>=l_dbg_level
713     Ja_Cn_Utility.Add_Sub_Root_Node('ACC_PERIOD_FILE',
714                                     Ja_Cn_Utility.GV_TAG_TYPE_START);
715     FOR v_row IN accounting_periods_cur(pn_accounting_year, pn_ledger_id) LOOP
716       Ja_Cn_Utility.Add_Sub_Root_Node('ACCOUNTING_PERIOD',
717                                       Ja_Cn_Utility.GV_TAG_TYPE_START);
718       Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_YEAR',
719                                          v_row.period_year,
720                                          4);
721       Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_PERIOD_START_DATE',
722                                          to_char(v_row.start_date,
723                                                  'YYYYMMDD'),
724                                          8);
725       Ja_Cn_Utility.Add_Fixed_Child_Node('ACCOUNTING_PERIOD_END_DATE',
726                                          to_char(v_row.end_date, 'YYYYMMDD'),
727                                          8);
728       Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER',
729                                    v_row.period_num);
730       Ja_Cn_Utility.Add_Sub_Root_Node('ACCOUNTING_PERIOD',
731                                       Ja_Cn_Utility.GV_TAG_TYPE_END);
732 
733     END LOOP;
734     Ja_Cn_Utility.Add_Sub_Root_Node('ACC_PERIOD_FILE',
735                                     Ja_Cn_Utility.GV_TAG_TYPE_END);
736     OPEN accounting_periods_cur(pn_accounting_year, pn_ledger_id);
737     IF (accounting_periods_cur%ROWCOUNT = 0) THEN
738       RAISE NO_DATA;
739     END IF;
740     CLOSE accounting_periods_cur;
741 
742     --logging for debug
743     IF (ln_proc_level >= ln_dbg_level) THEN
744       FND_LOG.STRING(ln_proc_level,
745                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
746                      'Exit procedure');
747     END IF; -- (ln_proc_level>=ln_dbg_level)
748   EXCEPTION
749     WHEN NO_DATA THEN
750       --logging for debug
751       IF (ln_proc_level >= ln_dbg_level) THEN
752         FND_LOG.STRING(ln_proc_level,
753                        GV_MODULE_PREFIX || '.' || lv_procedure_name ||
754                        '.begin',
755                        'The row count is 0');
756       END IF; --l_proc_level>=l_dbg_level
757     WHEN OTHERS THEN
758       IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
759         FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
760                        GV_MODULE_PREFIX || '.' || lv_procedure_name ||
761                        '.Other_Exception ',
762                        SQLCODE || SQLERRM);
763       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
764 
765   END Add_Accounting_Periods;
766 
767 
768   PROCEDURE Add_Accounting_Code_Rule(pn_legal_entity_id NUMBER,
769                                      pn_ledger_id       NUMBER,
770                                      pv_bsv             VARCHAR2,
771                                      pn_accounting_year NUMBER) IS
772     CURSOR CUR_CODE IS
773       select accounting_struct_id, ACCOUNTING_STRUCTURE_FLEX_NUM
774         from ja_cn_system_bank_params_all jcs
775        WHERE jcs.ledger_id = pn_ledger_id --jcs.flex_vale_segment=pv_bsv
776        AND rownum=1
777        ORDER BY flex_segment_value;
778     l_code            cur_code%rowtype;
779     l_rule            varchar2(100);
780     l_row             pls_integer:=0;
781     lv_procedure_name VARCHAR2(40) := 'Add_Accounting_Code_Rule';
782     ln_dbg_level      NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
783     ln_proc_level     NUMBER := FND_LOG.LEVEL_PROCEDURE;
784   BEGIN
785     --logging for debug
786     IF (ln_proc_level >= ln_dbg_level) THEN
787       FND_LOG.STRING(ln_proc_level,
788                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
789                      '.begin',
790                      'Enter procedure');
791     END IF; --l_proc_level>=l_dbg_level
792 
793     Ja_Cn_Utility.Add_Sub_Root_Node('ACCOUNT_STRUC_FILE',
794                                     Ja_Cn_Utility.GV_TAG_TYPE_START);
795     OPEN CUR_CODE;
796     LOOP
797       FETCH cur_code
798         into l_code;
799       EXIT WHEN cur_code%notfound;
800       select fnd_flex_ext.get_segs('JA',
801                                    'ACCT',
802                                    l_code.ACCOUNTING_STRUCTURE_FLEX_NUM,
803                                    l_code.ACCOUNTING_STRUCT_ID)
804         into l_rule
805         from dual;
806       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_STRUCTURE', l_rule);
807       l_row := l_row + 1;
808     END LOOP;
809     CLOSE CUR_CODE;
810 
811     IF l_row = 0 THEN
812       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_STRUCTURE', l_rule);
813     END IF;
814 
815     Ja_Cn_Utility.Add_Sub_Root_Node('ACCOUNT_STRUC_FILE',
816                                     Ja_Cn_Utility.GV_TAG_TYPE_END);
817     --logging for debug
818     IF (ln_proc_level >= ln_dbg_level) THEN
819       FND_LOG.STRING(ln_proc_level,
820                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
821                      'Exit procedure');
822     END IF; -- (ln_proc_level>=ln_dbg_level)
823   END Add_Accounting_Code_Rule;
824 
825   --==========================================================================
826   --  PROCEDURE NAME:
827   --    Add_Inner_Sheet_Account                     public
828   --
829   --  DESCRIPTION:
830   --      This procedure returns inner sheet account
831   --
832   --  PARAMETERS:
833   --      In: pn_legal_entity_id               NUMBER
834   --      In: pn_ledger_id                     NUMBER
835   --      In: pv_bsv                           NUMBER
836   --      In: pn_accounting_year               NUMBER
837   --
838   --  CHANGE HISTORY:
839   --    Nov-17-2012 Jar Wang       Created
840   --===========================================================================
841   PROCEDURE Add_Inner_Sheet_Account(pn_legal_entity_id NUMBER,
842                                     pn_ledger_id       NUMBER,
843                                     pv_bsv             VARCHAR2,
844                                     pn_accounting_year NUMBER) IS
845 
846     CURSOR CUR_INNER(c_valueset_id pls_integer) IS
847       select b.flex_value,
848              t.description,
849              NULL acc_level,
850              (select FLV.MEANING
851                 from FND_LOOKUP_VALUES flv
852                where flv.lookup_type = 'ACCOUNT_TYPE'
853                  and flv.view_APPLICATION_ID = 0
854                  and flv.security_group_id = 0
855                  and language = userenv('LANG')
856                  and flv.lookup_code =
857                      (substr(COMPILED_VALUE_ATTRIBUTES,
858                              instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 2) + 1,
859                              instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 3) - 1 -
860                              instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 2)))) acc_type,
861          (select FLV.MEANING
862                   from FND_LOOKUP_VALUES flv
863                  where flv.lookup_type = 'JA_CN_DEBIT_CREDIT'
864                    and flv.view_APPLICATION_ID = 0
865                    and flv.security_group_id = 0
866                    and language = userenv('LANG')
867                    and flv.lookup_code =             (substr(COMPILED_VALUE_ATTRIBUTES,
868                        instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 7) + 1))) balance_side,
869             to_char(nvl(b.START_DATE_ACTIVE,b.creation_date),'YYYYMMDD') START_DATE_ACTIVE,to_char(b.end_date_active,'YYYYMMDD') end_date_active
870         from FND_FLEX_VALUES_TL T, FND_FLEX_VALUES B
871        where B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
872          and T.LANGUAGE = userenv('LANG')
873          and substr(COMPILED_VALUE_ATTRIBUTES,
874                       instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 5) + 1,
875                       instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 6) - 1 -
876                       instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 5)) = 'I'
877          and b.FLEX_VALUE_SET_ID = c_valueset_id;
878 
879     lv_procedure_name VARCHAR2(40) := 'Add_Inner_Sheet_Account';
880     ln_dbg_level      NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
881     ln_proc_level     NUMBER := FND_LOG.LEVEL_PROCEDURE;
882 
883     l_inner        CUR_INNER%ROWTYPE;
884     l_valueset_id  PLS_INTEGER;
885     l_parent_count PLS_INTEGER;
886     l_level        PLS_INTEGER;
887     l_row          PLS_INTEGER := 0;
888   BEGIN
889     IF (ln_proc_level >= ln_dbg_level) THEN
890       FND_LOG.STRING(ln_proc_level,
891                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
892                      '.begin',
893                      'Enter procedure');
894       FND_LOG.STRING(ln_proc_level,
895                      lv_procedure_name || '.parameters',
896                      'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
897                      'pn_ledger_id=' || pn_ledger_id || ',' ||
898                      'pn_accounting_year=' || pn_accounting_year);
899 
900     END IF; --ln_proc_level>=ln_dbg_level
901 
902     --get valueset id of the coa
903     SELECT fifsv.FLEX_VALUE_SET_ID
904       into l_valueset_id
905       FROM FND_ID_FLEX_SEGMENTS_VL fifsv, FND_SEGMENT_ATTRIBUTE_VALUES FSAV
906      WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
907        AND FSAV.APPLICATION_ID = 101
908        AND FSAV.ID_FLEX_CODE = 'GL#'
909        AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
910        AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
911        AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
912        AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
913        AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
914        AND fifsv.ID_FLEX_NUM in
915            (SELECT chart_of_accounts_id
916               FROM gl_ledgers gls
917              where gls.ledger_id = pn_ledger_id);
918 
919     Ja_Cn_Utility.Add_Sub_Root_Node('INTER_SHEET_ACC_FILE',
920                                     Ja_Cn_Utility.GV_TAG_TYPE_START);
921 
922     OPEN CUR_INNER(l_valueset_id);
923     LOOP
924       FETCH CUR_INNER
925         INTO l_inner;
926       EXIT WHEN CUR_INNER%NOTFOUND;
927       l_row := l_row +1;
928       Ja_Cn_Utility.Add_Sub_Root_Node('INTER_SHEET_ACCOUNT',
929                                       Ja_Cn_Utility.GV_TAG_TYPE_START);
930       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER', l_inner.flex_value);
931       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NAME', l_inner.description);
932       --calculate level
933       select count(1)
934         into l_parent_count
935         from FND_FLEX_VALUE_CHILDREN_V ffvc
936        where ffvc.flex_value_set_id = l_valueset_id
937          and ffvc.flex_value = l_inner.flex_value;
938       if l_parent_count > 1 then
939         Ja_Cn_Utility.Add_Child_Node('ACCOUNT_LEVEL', '0');
940       else
941         l_level := Get_Level(l_valueset_id, l_inner.flex_value);
942         IF l_level= -1 THEN
943           Ja_Cn_Utility.Add_Child_Node('ACCOUNT_LEVEL', '0');
944         ELSE
945            Ja_Cn_Utility.Add_Child_Node('ACCOUNT_LEVEL', l_level);
946         END IF;
947       end if;
948       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_TYPE', l_inner.acc_type);
949       Ja_Cn_Utility.Add_Child_Node('BALANCE_SIDE', l_inner.balance_side);
950       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_STRAT_DATE',
951                                    l_inner.START_DATE_ACTIVE);
952        --updated by jar.wang for bug 16655327
953       IF l_inner.End_Date_Active IS NOT NULL THEN
954         Ja_Cn_Utility.Add_Child_Node('ACCOUNT_END_DATE',
955                                      l_inner.End_Date_Active);
956       END IF;
957       Ja_Cn_Utility.Add_Sub_Root_Node('INTER_SHEET_ACCOUNT',
958                                       Ja_Cn_Utility.GV_TAG_TYPE_END);
959     END LOOP;
960     CLOSE CUR_INNER;
961 
962     IF l_row = 0 THEN
963         Ja_Cn_Utility.Add_Sub_Root_Node('INTER_SHEET_ACCOUNT',
964                                          Ja_Cn_Utility.GV_TAG_TYPE_START);
965         Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER', '');
966         Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NAME', '');
967         Ja_Cn_Utility.Add_Child_Node('ACCOUNT_LEVEL', '');
968         Ja_Cn_Utility.Add_Child_Node('ACCOUNT_TYPE',  '');
969         Ja_Cn_Utility.Add_Child_Node('BALANCE_SIDE',  '');
970         Ja_Cn_Utility.Add_Child_Node('ACCOUNT_STRAT_DATE', '');
971         Ja_Cn_Utility.Add_Child_Node('ACCOUNT_END_DATE',
972                                    l_inner.End_Date_Active);
973         Ja_Cn_Utility.Add_Sub_Root_Node('INTER_SHEET_ACCOUNT',
974                                         Ja_Cn_Utility.GV_TAG_TYPE_END);
975     END IF;
976 
977     Ja_Cn_Utility.Add_Sub_Root_Node('INTER_SHEET_ACC_FILE',
978                                     Ja_Cn_Utility.GV_TAG_TYPE_END);
979     --logging for debug
980     IF (ln_proc_level >= ln_dbg_level) THEN
981       FND_LOG.STRING(ln_proc_level,
982                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
983                      'Exit procedure');
984     END IF; -- (ln_proc_level>=ln_dbg_level)
985   EXCEPTION
986     WHEN OTHERS THEN
987       FND_FILE.put_line(FND_FILE.log,
988                         GV_MODULE_PREFIX || '.' || lv_procedure_name  || SQLCODE || SQLERRM);
989       RAISE;
990   END Add_Inner_Sheet_Account;
991   --==========================================================================
992   --  PROCEDURE NAME:
993   --    Add_Extra_Sheet_Account                     public
994   --
995   --  DESCRIPTION:
996   --      This procedure returns extra sheet account
997   --
998   --  PARAMETERS:
999   --      In: pn_legal_entity_id               NUMBER
1000   --      In: pn_ledger_id                     NUMBER
1001   --      In: pv_bsv                           NUMBER
1002   --      In: pn_accounting_year               NUMBER
1003   --
1004   --  CHANGE HISTORY:
1005   --    Nov-17-2012 Jar Wang       Created
1006   --===========================================================================
1007   PROCEDURE Add_Extra_Sheet_Account(pn_legal_entity_id NUMBER,
1008                                     pn_ledger_id       NUMBER,
1009                                     pv_bsv             VARCHAR2,
1010                                     pn_accounting_year NUMBER)
1011 
1012    IS
1013 
1014     CURSOR CUR_EXTRA(c_flex_id pls_integer) IS
1015      select b.flex_value,COMPILED_VALUE_ATTRIBUTES,
1016                t.description,
1017                NULL acc_level,
1018                (select FLV.MEANING
1019                   from FND_LOOKUP_VALUES flv
1020                  where flv.lookup_type = 'ACCOUNT_TYPE'
1021                    and flv.view_APPLICATION_ID = 0
1022                    and flv.security_group_id = 0
1023                    and language = userenv('LANG')
1024                    and flv.lookup_code =
1025                        (substr(COMPILED_VALUE_ATTRIBUTES,
1026                                instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 2) + 1,
1027                                instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 3) - 1 -
1028                                instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 2)))) acc_type,
1029                (select FLV.MEANING
1030                   from FND_LOOKUP_VALUES flv
1031                  where flv.lookup_type = 'JA_CN_UOM'
1032                    and flv.view_APPLICATION_ID = 0
1033                    and flv.security_group_id = 0
1034                    and language = userenv('LANG')
1035                    and flv.lookup_code =
1036                        (substr(COMPILED_VALUE_ATTRIBUTES,
1037                                instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 6) + 1,
1038                                instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 7) - 1 -
1039                                instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 6)))) account_unit,
1040          (select FLV.MEANING
1041                   from FND_LOOKUP_VALUES flv
1042                  where flv.lookup_type = 'JA_CN_DEBIT_CREDIT'
1043                    and flv.view_APPLICATION_ID = 0
1044                    and flv.security_group_id = 0
1045                    and language = userenv('LANG')
1046                    and flv.lookup_code =             (substr(COMPILED_VALUE_ATTRIBUTES,
1047                        instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 7) + 1))) balance_side,
1048                to_char(nvl(b.START_DATE_ACTIVE,b.creation_date),'YYYYMMDD') START_DATE_ACTIVE,to_char(b.end_date_active,'YYYYMMDD') end_date_active
1049           from FND_FLEX_VALUES_TL T, FND_FLEX_VALUES B
1050          where B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
1051            and T.LANGUAGE = userenv('LANG')
1052            and substr(COMPILED_VALUE_ATTRIBUTES,
1053                       instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 5) + 1,
1054                       instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 6) - 1 -
1055                       instr(COMPILED_VALUE_ATTRIBUTES, fnd_global.local_chr(10), 1, 5)) = 'E'
1056          and b.FLEX_VALUE_SET_ID = c_flex_id;
1057 
1058     lv_procedure_name VARCHAR2(40) := 'Add_Extra_Sheet_Account';
1059     ln_dbg_level      NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1060     ln_proc_level     NUMBER := FND_LOG.LEVEL_PROCEDURE;
1061 
1062     l_extra        CUR_EXTRA%ROWTYPE;
1063     l_valueset_id  PLS_INTEGER;
1064     l_parent_count PLS_INTEGER;
1065     l_level        PLS_INTEGER;
1066     l_row          PLS_INTEGER := 0;
1067   BEGIN
1068     IF (ln_proc_level >= ln_dbg_level) THEN
1069       FND_LOG.STRING(ln_proc_level,
1070                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1071                      '.begin',
1072                      'Enter procedure');
1073     END IF; --ln_proc_level>=ln_dbg_level
1074 
1075     --get valueset id of the coa
1076     SELECT fifsv.FLEX_VALUE_SET_ID
1077       into l_valueset_id
1078       FROM FND_ID_FLEX_SEGMENTS_VL fifsv, FND_SEGMENT_ATTRIBUTE_VALUES FSAV
1079      WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
1080        AND FSAV.APPLICATION_ID = 101
1081        AND FSAV.ID_FLEX_CODE = 'GL#'
1082        AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
1083        AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
1084        AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
1085        AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
1086        AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
1087        AND fifsv.ID_FLEX_NUM in
1088            (SELECT chart_of_accounts_id
1089               FROM gl_ledgers gls
1090              where gls.ledger_id = pn_ledger_id);
1091     Ja_Cn_Utility.Add_Sub_Root_Node('EXTRA_SHEET_ACC_FILE',
1092                                     Ja_Cn_Utility.GV_TAG_TYPE_START);
1093 
1094     OPEN CUR_EXTRA(l_valueset_id);
1095     LOOP
1096       FETCH CUR_EXTRA
1097         INTO l_extra;
1098       EXIT WHEN CUR_EXTRA%NOTFOUND;
1099       Ja_Cn_Utility.Add_Sub_Root_Node('EXTRA_SHEET_ACCOUNT',
1100                                       Ja_Cn_Utility.GV_TAG_TYPE_START);
1101       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER', l_extra.flex_value);
1102       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NAME', l_extra.description);
1103       --calculate level
1104       select count(1)
1105         into l_parent_count
1106         from FND_FLEX_VALUE_CHILDREN_V ffvc
1107        where ffvc.flex_value_set_id = l_valueset_id
1108          and ffvc.flex_value = l_extra.flex_value;
1109 
1110       if l_parent_count > 1 then
1111         Ja_Cn_Utility.Add_Child_Node('ACCOUNT_LEVEL', '0');
1112       else
1113         l_level := Get_Level(l_valueset_id, l_extra.flex_value);
1114         IF l_level= -1 THEN
1115           Ja_Cn_Utility.Add_Child_Node('ACCOUNT_LEVEL', '0');
1116         ELSE
1117            Ja_Cn_Utility.Add_Child_Node('ACCOUNT_LEVEL', l_level);
1118         END IF;
1119       end if;
1120 
1121       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_TYPE', l_extra.acc_type);
1122       Ja_Cn_Utility.Add_Child_Node('EXTRA_SHEET_ACCOUNT_UOM',
1123                                    l_extra.account_unit);
1124       Ja_Cn_Utility.Add_Child_Node('BALANCE_SIDE', l_extra.balance_side);
1125       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_STRAT_DATE',
1126                                    l_extra.START_DATE_ACTIVE);
1127       IF l_extra.End_Date_Active IS NOT NULL THEN
1128         Ja_Cn_Utility.Add_Child_Node('ACCOUNT_END_DATE',
1129                                      l_extra.end_date_active);
1130       END IF;
1131       Ja_Cn_Utility.Add_Sub_Root_Node('EXTRA_SHEET_ACCOUNT',
1132                                       Ja_Cn_Utility.GV_TAG_TYPE_END);
1133       l_row := l_row + 1;
1134     END LOOP;
1135     CLOSE CUR_EXTRA;
1136 
1137     if l_row = 0 then
1138       Ja_Cn_Utility.Print_No_Data_Found_For_Log('CURRENCY');
1139       Ja_Cn_Utility.Add_Sub_Root_Node('EXTRA_SHEET_ACCOUNT',
1140                                       Ja_Cn_Utility.GV_TAG_TYPE_START);
1141       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER', '');
1142       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NAME', '');
1143       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_LEVEL', '');
1144 
1145       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_TYPE', '');
1146       Ja_Cn_Utility.Add_Child_Node('EXTRA_SHEET_ACCOUNT_UOM', '');
1147       Ja_Cn_Utility.Add_Child_Node('BALANCE_SIDE', '');
1148       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_STRAT_DATE', '');
1149       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_END_DATE',
1150                                    '');
1151       Ja_Cn_Utility.Add_Sub_Root_Node('EXTRA_SHEET_ACCOUNT',
1152                                       Ja_Cn_Utility.GV_TAG_TYPE_END);
1153     end if;
1154 
1155     Ja_Cn_Utility.Add_Sub_Root_Node('EXTRA_SHEET_ACC_FILE',
1156                                     Ja_Cn_Utility.GV_TAG_TYPE_END);
1157     --logging for debug
1158     IF (ln_proc_level >= ln_dbg_level) THEN
1159       FND_LOG.STRING(ln_proc_level,
1160                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1161                      'Exit procedure');
1162     END IF; -- (ln_proc_level>=ln_dbg_level)
1163   EXCEPTION
1164     WHEN OTHERS THEN
1165       FND_FILE.put_line(FND_FILE.log,
1166                         GV_MODULE_PREFIX || '.' || lv_procedure_name  || SQLCODE || SQLERRM);
1167       RAISE;
1168   END Add_Extra_Sheet_Account;
1169 
1170   --==========================================================================
1171   --  FUNCTION NAME:
1172   --    Get_Level                     public
1173   --
1174   --  DESCRIPTION:
1175   --      This procedure returns the level of account in the account hierarchy.
1176   --
1177   --  PARAMETERS:
1178   --      In: p_valueset               NUMBER
1179   --      In: p_parent               NUMBER
1180   --
1181   --  CHANGE HISTORY:
1182   --    Nov-10-2012 Jar Wang       Created
1183   --===========================================================================
1184   FUNCTION Get_Level(p_valueset pls_integer, p_parent varchar2) RETURN NUMBER IS
1185     lv_procedure_name VARCHAR2(40) := 'Get_Level';
1186     ln_dbg_level      NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1187     ln_proc_level     NUMBER := FND_LOG.LEVEL_PROCEDURE;
1188     l_level pls_integer := 1;
1189     l_temp  varchar2(400);
1190     l_temp2 varchar2(400);
1191     l_count pls_integer;
1192   BEGIN
1193     IF (ln_proc_level >= ln_dbg_level) THEN
1194       FND_LOG.STRING(ln_proc_level,
1195                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1196                      '.begin',
1197                      'Enter procedure');
1198     END IF; --ln_proc_level>=ln_dbg_level
1199    l_temp := p_parent;
1200     loop
1201       select count(1)
1202         into l_count
1203         from (SELECT v.flex_value_set_id,
1204                      h.parent_flex_value,
1205                      v.flex_value,
1206                      v.description,
1207                      v.summary_flag
1208                 FROM fnd_flex_values_vl            v,
1209                      fnd_flex_value_norm_hierarchy h,
1210                      fnd_flex_value_sets           s
1211                WHERE h.flex_value_set_id = v.flex_value_set_id
1212                  AND v.ENABLED_FLAG = 'Y'
1213                  AND s.flex_value_set_id = v.flex_value_set_id
1214                  AND (((s.format_type = 'N') AND
1215                      (fnd_number.canonical_to_number(v.flex_value) BETWEEN
1216                      fnd_number.canonical_to_number(h.child_flex_value_low) AND
1217                      fnd_number.canonical_to_number(h.child_flex_value_high))) OR
1218                      ((s.format_type IN ('D', 'T')) AND
1219                      (to_date(v.flex_value,
1220                                 (decode(s.maximum_size,
1221                                         5,
1222                                         'HH24:MI',
1223                                         8,
1224                                         'HH24:MI:SS',
1225                                         9,
1226                                         'DD-MON-RR',
1227                                         11,
1228                                         'DD-MON-YYYY',
1229                                         15,
1230                                         'DD-MON-RR HH24:MI',
1231                                         17,
1232                                         'DD-MON-YYYY HH24:MI',
1233                                         18,
1234                                         'DD-MON-RR HH24:MI:SS',
1235                                         20,
1236                                         'DD-MON-YYYY HH24:MI:SS'))) BETWEEN
1237                      to_date(h.child_flex_value_low,
1238                                 (decode(s.maximum_size,
1239                                         5,
1240                                         'HH24:MI',
1241                                         8,
1242                                         'HH24:MI:SS',
1243                                         9,
1244                                         'DD-MON-RR',
1245                                         11,
1246                                         'DD-MON-YYYY',
1247                                         15,
1248                                         'DD-MON-RR HH24:MI',
1249                                         17,
1250                                         'DD-MON-YYYY HH24:MI',
1251                                         18,
1252                                         'DD-MON-RR HH24:MI:SS',
1253                                         20,
1254                                         'DD-MON-YYYY HH24:MI:SS'))) AND
1255                      to_date(h.child_flex_value_high,
1256                                 (decode(s.maximum_size,
1257                                         5,
1258                                         'HH24:MI',
1259                                         8,
1260                                         'HH24:MI:SS',
1261                                         9,
1262                                         'DD-MON-RR',
1263                                         11,
1264                                         'DD-MON-YYYY',
1265                                         15,
1266                                         'DD-MON-RR HH24:MI',
1267                                         17,
1268                                         'DD-MON-YYYY HH24:MI',
1269                                         18,
1270                                         'DD-MON-RR HH24:MI:SS',
1271                                         20,
1272                                         'DD-MON-YYYY HH24:MI:SS'))))) OR
1273                      ((s.format_type NOT IN ('N', 'D', 'T')) AND
1274                      (v.flex_value BETWEEN h.child_flex_value_low AND
1275                      h.child_flex_value_high)))
1276                  AND ((v.summary_flag = 'Y' AND h.range_attribute = 'P') OR
1277                      (v.summary_flag = 'N' AND h.range_attribute = 'C'))) ffvc
1278        where ffvc.flex_value_set_id = p_valueset
1279          and ffvc.flex_value = l_temp;
1280       if l_count = 0 then
1281         exit;
1282       elsif l_count = 1 then
1283         select ffvc.parent_flex_value
1284           into l_temp2
1285           from (SELECT v.flex_value_set_id,
1286                        h.parent_flex_value,
1287                        v.flex_value,
1288                        v.description,
1289                        v.summary_flag
1290                   FROM fnd_flex_values_vl            v,
1291                        fnd_flex_value_norm_hierarchy h,
1292                        fnd_flex_value_sets           s
1293                  WHERE h.flex_value_set_id = v.flex_value_set_id
1294                    AND v.ENABLED_FLAG = 'Y'
1295                    AND s.flex_value_set_id = v.flex_value_set_id
1296                    AND (((s.format_type = 'N') AND
1297                        (fnd_number.canonical_to_number(v.flex_value) BETWEEN
1298                        fnd_number.canonical_to_number(h.child_flex_value_low) AND
1299                        fnd_number.canonical_to_number(h.child_flex_value_high))) OR
1300                        ((s.format_type IN ('D', 'T')) AND
1301                        (to_date(v.flex_value,
1302                                   (decode(s.maximum_size,
1303                                           5,
1304                                           'HH24:MI',
1305                                           8,
1306                                           'HH24:MI:SS',
1307                                           9,
1308                                           'DD-MON-RR',
1309                                           11,
1310                                           'DD-MON-YYYY',
1311                                           15,
1312                                           'DD-MON-RR HH24:MI',
1313                                           17,
1314                                           'DD-MON-YYYY HH24:MI',
1315                                           18,
1316                                           'DD-MON-RR HH24:MI:SS',
1317                                           20,
1318                                           'DD-MON-YYYY HH24:MI:SS'))) BETWEEN
1319                        to_date(h.child_flex_value_low,
1320                                   (decode(s.maximum_size,
1321                                           5,
1322                                           'HH24:MI',
1323                                           8,
1324                                           'HH24:MI:SS',
1325                                           9,
1326                                           'DD-MON-RR',
1327                                           11,
1328                                           'DD-MON-YYYY',
1329                                           15,
1330                                           'DD-MON-RR HH24:MI',
1331                                           17,
1332                                           'DD-MON-YYYY HH24:MI',
1333                                           18,
1334                                           'DD-MON-RR HH24:MI:SS',
1335                                           20,
1336                                           'DD-MON-YYYY HH24:MI:SS'))) AND
1337                        to_date(h.child_flex_value_high,
1338                                   (decode(s.maximum_size,
1339                                           5,
1340                                           'HH24:MI',
1341                                           8,
1342                                           'HH24:MI:SS',
1343                                           9,
1344                                           'DD-MON-RR',
1345                                           11,
1346                                           'DD-MON-YYYY',
1347                                           15,
1348                                           'DD-MON-RR HH24:MI',
1349                                           17,
1350                                           'DD-MON-YYYY HH24:MI',
1351                                           18,
1352                                           'DD-MON-RR HH24:MI:SS',
1353                                           20,
1354                                           'DD-MON-YYYY HH24:MI:SS'))))) OR
1355                        ((s.format_type NOT IN ('N', 'D', 'T')) AND
1356                        (v.flex_value BETWEEN h.child_flex_value_low AND
1357                        h.child_flex_value_high)))
1358                    AND ((v.summary_flag = 'Y' AND h.range_attribute = 'P') OR
1359                        (v.summary_flag = 'N' AND h.range_attribute = 'C'))) ffvc
1360          where ffvc.flex_value_set_id = p_valueset
1361            and ffvc.flex_value = l_temp;
1362         l_temp := l_temp2;
1363 
1364         l_level := l_level + 1;
1365       else
1366         l_level := -1;
1367         exit;
1368       end if;
1369     end loop;
1370     --logging for debug
1371     IF (ln_proc_level >= ln_dbg_level) THEN
1372       FND_LOG.STRING(ln_proc_level,
1373                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1374                      'Exit procedure');
1375     END IF; -- (ln_proc_level>=ln_dbg_level)
1376     return l_level;
1377   EXCEPTION
1378     WHEN OTHERS THEN
1379       FND_FILE.put_line(FND_FILE.log,
1380                         GV_MODULE_PREFIX || '.' || lv_procedure_name  || SQLCODE || SQLERRM);
1381       RAISE;
1382   END Get_Level;
1383 
1384 END JA_CN_SI_BANK_DET_PKG;
1385 
1386 
1387 
1388 
1389 
1390 
1391 
1392 
1393 
1394