DBA Data[Home] [Help]

PACKAGE BODY: APPS.JA_CN_GL_BANK_EXP_PKG

Source


1 PACKAGE BODY JA_CN_GL_BANK_EXP_PKG AS
2   --$Header: JACNGBEB.pls 120.4.12020000.5 2013/04/22 08:28:09 chongwan noship $
3   --+=======================================================================+
4   --|               Copyright (c) 1998 Oracle Corporation                   |
5   --|                       Redwood Shores, CA, USA                         |
6   --|                         All rights reserved.                          |
7   --+=======================================================================+
8   --| FILENAME                                                              |
9   --|     JACNGBEB.pls                                                      |
10   --|                                                                       |
11   --| DESCRIPTION                                                           |
12   --|     Use this package to export non-FSG report for general ledger      |
13   --|                                                                       |
14   --| PROCEDURE LIST                                                        |
15   --|      PROCEDURE Export_GL                                              |
16   --|         Add_GL_BALANCE                                                |
17   --|         Add_GL_AVG_BAL                                                |
18   --|         Add_GL_JOURNAL                                                |
19   --|                                                                       |
20   --|                                                                       |
21   --|  DESIGN REFERENCES:                                                   |
22   --|    China General Ledger Data Export (Banking)_TD                      |
23   --| HISTORY                                                               |
24   --|     19-Oct-2012       Jar Wang       Created                          |
25   --+======================================================================*/
26 
27   GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_GL_BANK_EXP_PKG';
28 
29 
30   PROCEDURE Combination_Exec(pn_chart_of_account_id IN NUMBER,
31                              pn_ledger_id           IN NUMBER,
32                              pn_legal_entity_id     IN NUMBER,
33                              pv_bsv                 IN VARCHAR2,
34                              pv_accounting_year     IN VARCHAR2,
35                              pv_start               IN VARCHAR2,
36                              pv_end                 IN VARCHAR2,
37                              pn_branch          NUMBER)
38   IS
39     lv_procedure_name  VARCHAR2(40) := 'Combination_Exec';
40     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
41     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
42   BEGIN
43     --logging for debug
44     FND_FILE.PUT_LINE(FND_FILE.log, lv_procedure_name);
45     IF (ln_proc_level >= ln_dbg_level) THEN
46       FND_LOG.STRING(ln_proc_level,
47                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
48                      '.begin',
49                      'Enter procedure');
50 
51     END IF; --ln_proc_level>=ln_dbg_level
52     FND_FILE.PUT_LINE(FND_FILE.LOG,'pn_branch='||pn_branch);
53     IF pn_branch=1 THEN
54         --Balance
55         Add_GL_BALANCE(pn_chart_of_account_id  ,
56                        pn_ledger_id ,
57                        pn_legal_entity_id ,
58                        pv_bsv  ,
59                        pv_accounting_year ,
60                        pv_start ,
61                        pv_end  );
62     ELSIF pn_branch=2 THEN
63         --Average
64         Add_GL_AVG_BAL(pn_chart_of_account_id ,
65                        pn_ledger_id  ,
66                        pn_legal_entity_id ,
67                        pv_bsv  ,
68                        pv_accounting_year ,
69                        pv_start,
70                        pv_end);
71     ELSIF pn_branch=3 THEN
72         --Journal
73         Add_GL_JOURNAL(pn_chart_of_account_id  ,
74                        pn_ledger_id  ,
75                        pn_legal_entity_id  ,
76                        pv_bsv  ,
77                        pv_accounting_year ,
78                        pv_start ,
79                        pv_end );
80     END IF;
81     --logging for debug
82     IF (ln_proc_level >= ln_dbg_level) THEN
83       FND_LOG.STRING(ln_proc_level,
84                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
85                      'Exit procedure');
86     END IF; -- (ln_proc_level>=ln_dbg_level)
87   EXCEPTION
88     WHEN OTHERS THEN
89       IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
90         FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
91                        GV_MODULE_PREFIX || '.' || lv_procedure_name ||
92                        '.Other_Exception ',
93                        SQLCODE || SQLERRM);
94       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
95       FND_FILE.put_line(FND_FILE.log,
96                         GV_MODULE_PREFIX || '.' || lv_procedure_name ||
97                         SQLCODE || SQLERRM);
98       RAISE;
99   END Combination_Exec;
100 
101 
102   PROCEDURE Recursion_BSV(pn_chart_of_account_id IN NUMBER,
103                            pn_ledger_id           IN NUMBER,
104                            pn_legal_entity_id     IN NUMBER,
105                            pv_bsv                 IN VARCHAR2,
106                            pv_accounting_year     IN VARCHAR2,
107                            pv_start               IN VARCHAR2,
108                            pv_end                 IN VARCHAR2,
109                            pn_branch          NUMBER) IS
110     lv_procedure_name  VARCHAR2(40) := 'Recursion_BSV';
111     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
112     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
113     lv_summary         VARCHAR2(2);
114     l_flex_valueset_id NUMBER;
115     l_bsv_temp         VARCHAR2(100) :=pv_bsv;
116 
117     CURSOR CUR_PARENT(c_valueset_id pls_integer, c_bsv varchar2) IS
118       select ffh.flex_value_set_id, ffh.FLEX_VALUE, SUMMARY_FLAG
119         from FND_FLEX_VALUE_CHILDREN_V ffh
120        where ffh.flex_value_set_id = c_valueset_id
121          and ffh.PARENT_FLEX_VALUE = c_bsv
122          order by  ffh.FLEX_VALUE;
123 
124   BEGIN
125     --logging for debug
126     FND_FILE.PUT_LINE(FND_FILE.log, lv_procedure_name);
127     IF (ln_proc_level >= ln_dbg_level) THEN
128       FND_LOG.STRING(ln_proc_level,
129                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
130                      '.begin',
131                      'Enter procedure');
132       FND_LOG.STRING(ln_proc_level,
133                      lv_procedure_name || '.parameters',
134                      'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
135                      'pn_ledger_id=' || pn_ledger_id || ',' ||
136                      'pn_accounting_year=' || pv_accounting_year);
137 
138     END IF; --ln_proc_level>=ln_dbg_level
139     FND_FILE.put_line(FND_FILE.log,
140                       lv_procedure_name || '.parameters:' ||
141                       'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
142                       'pn_ledger_id=' || pn_ledger_id || ',' ||
143                       'pv_bsv='||pv_bsv ||',' ||
144                       'pn_accounting_year=' || pv_accounting_year);
145 
146       --get valueset_id
147       SELECT fifsv.FLEX_VALUE_SET_ID
148         into l_flex_valueset_id
149         FROM FND_ID_FLEX_SEGMENTS_VL      fifsv,
150              FND_SEGMENT_ATTRIBUTE_VALUES FSAV
151        WHERE FSAV.ATTRIBUTE_VALUE = 'Y'
152          AND FSAV.APPLICATION_ID = 101
153          AND FSAV.ID_FLEX_CODE = 'GL#'
154          AND FSAV.APPLICATION_ID = fifsv.APPLICATION_ID
155          AND FSAV.ID_FLEX_NUM = fifsv.ID_FLEX_NUM
156          AND FSAV.ID_FLEX_CODE = fifsv.ID_FLEX_CODE
157          AND FSAV.APPLICATION_COLUMN_NAME = fifsv.APPLICATION_COLUMN_NAME
158          AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
159          AND fifsv.ID_FLEX_NUM in
160              (SELECT chart_of_accounts_id
161                 FROM gl_ledgers gls
162                where gls.ledger_id = pn_ledger_id);
163 
164          --get bsv summary
165          SELECT SUMMARY_FLAG
166            INTO lv_summary
167            FROM  FND_FLEX_VALUE_SETS ffs, FND_FLEX_VALUES_VL ffv
168           WHERE ffs.flex_value_set_id = ffv.FLEX_VALUE_SET_ID
169             AND ffv.FLEX_VALUE_SET_ID = l_flex_valueset_id
170             AND ffv.FLEX_VALUE = l_bsv_temp;
171          --child
172          IF lv_summary='N' THEN
173              Combination_Exec(pn_chart_of_account_id,
174                              pn_ledger_id,
175                              pn_legal_entity_id ,
176                              pv_bsv            ,
177                              pv_accounting_year ,
178                              pv_start          ,
179                              pv_end             ,
180                              pn_branch         );
181          ELSE
182              --parent
183              FOR l_parent in CUR_PARENT(l_flex_valueset_id,l_bsv_temp) LOOP
184                 l_bsv_temp := l_parent.FLEX_VALUE;
185                 --recursion
186                 IF l_parent.SUMMARY_FLAG='Y' THEN
187                    FND_FILE.put_line(FND_FILE.log,lv_procedure_name||',Y='||l_bsv_temp);
188 
189                    Recursion_BSV(pn_chart_of_account_id,
190                                   pn_ledger_id       ,
191                                   pn_legal_entity_id ,
192                                   l_bsv_temp         ,
193                                   pv_accounting_year ,
194                                   pv_start     ,
195                                   pv_end       ,
196                                   pn_branch    );
197 
198                 ELSE
199                     FND_FILE.put_line(FND_FILE.log,lv_procedure_name||',N='||l_bsv_temp);
200                     Combination_Exec(pn_chart_of_account_id,
201                                      pn_ledger_id,
202                                      pn_legal_entity_id ,
203                                      l_bsv_temp            ,
204                                      pv_accounting_year ,
205                                      pv_start          ,
206                                      pv_end             ,
207                                      pn_branch         );
208                 END IF;
209              END LOOP;
210          END IF;
211 
212     --logging for debug
213     IF (ln_proc_level >= ln_dbg_level) THEN
214       FND_LOG.STRING(ln_proc_level,
215                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
216                      'Exit procedure');
217     END IF; -- (ln_proc_level>=ln_dbg_level)
218   EXCEPTION
219     WHEN OTHERS THEN
220       IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
221         FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
222                        GV_MODULE_PREFIX || '.' || lv_procedure_name ||
223                        '.Other_Exception ',
224                        SQLCODE || SQLERRM);
225       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
226       FND_FILE.put_line(FND_FILE.log,
227                         GV_MODULE_PREFIX || '.' || lv_procedure_name ||
228                         SQLCODE || SQLERRM);
229       RAISE;
230   END Recursion_BSV;
231 
232 
233 
234  --==========================================================================
235   --  PROCEDURE NAME:
236   --
237   --   Export_GL                        Public
238   --
239   --  DESCRIPTION:
240   --
241   --    This procedure is to export non-FSG report for general ledger
242   --
243   --  PARAMETERS:
244   --      Out: pv_errbuf                 NOCOPY VARCHAR2
245   --           pv_retcode                NOCOPY VARCHAR2
246   --      In:  pn_legal_entity_id        NUMBER identifier of legal entity
247   --           pn_chart_of_account_id    NUMBER identifier of chart of account
248   --           pn_ledger_id              NUMBER identifier of ledger
249   --           pv_bsv                    VARCHAR2 balance segment value
250   --           pv_adhoc_prefix           VARCHAR2 adhoc prefix
251   --           pv_industry               VARCHAR2 industry
252   --           pv_accounting_year        VARCHAR2 accounting year
253   --           pv_period_from            VARCHAR2 period from
254   --           pv_period_to              VARCHAR2 period to
255   --           pn_cfs_report_id          NUMBER identifier of cfs report
256   --           pv_xml_template_language  VARCHAR2 xml template language
257   --           pv_xml_template_territory VARCHAR2 xml template territory
258   --           pv_xml_output_format      VARCHAR2 xml output format
259   --
260   --
261   --  CHANGE HISTORY:
262   --      19-Oct-2012   Jar Wang  created
263   --==========================================================================
264   PROCEDURE Export_GL(pv_errbuf                 OUT NOCOPY VARCHAR2,
265                       pv_retcode                OUT NOCOPY VARCHAR2,
266                       pn_chart_of_account_id    IN NUMBER,
267                       pn_ledger_id              IN NUMBER,
268                       pn_legal_entity_id        IN NUMBER,
269                       pv_bsv                    IN VARCHAR2,
270                       pv_adhoc_prefix           IN VARCHAR2,
271                       pv_industry               IN VARCHAR2,
272                       pv_accounting_year        IN VARCHAR2,
273                       pv_period_from            IN VARCHAR2,
274                       pv_period_to              IN VARCHAR2,
275                       pn_cfs_report_id          IN NUMBER,
276                       pv_xml_template_language  IN VARCHAR2,
277                       pv_xml_template_territory IN VARCHAR2,
278                       pv_xml_output_format      IN VARCHAR2) IS
279     lv_gl_element      VARCHAR2(1000);
280     lv_procedure_name  VARCHAR2(40) := 'Export_GL';
281     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
282     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
283     NO_DATA EXCEPTION;
284 
285   BEGIN
286     --logging for debug
287     IF (ln_proc_level >= ln_dbg_level) THEN
288       FND_LOG.STRING(ln_proc_level,
289                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
290                      '.begin',
291                      'Enter procedure');
292       -- logging the parameters
293       FND_LOG.STRING(ln_proc_level,
294                      lv_procedure_name || '.parameters',
295                      'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
296                      'pn_chart_of_account_id=' || pn_chart_of_account_id || ',' ||
297                      'pn_ledger_id=' || pn_ledger_id || ',' ||
298                      'pv_adhoc_prefix=' || pv_adhoc_prefix || ',' ||
299                      'pv_industry=' || pv_industry || ',' ||
300                      'pv_accounting_year=' || pv_accounting_year || ',' ||
301                      'pv_period_from=' || pv_period_from || ',' ||
302                      'pv_period_to=' || pv_period_to || ',' ||
303                      'pn_cfs_report_id=' || pn_cfs_report_id || ',' ||
304                      'pv_xml_template_language=' ||
305                      pv_xml_template_language || ',' ||
306                      'pv_xml_template_territory=' ||
307                      pv_xml_template_territory || ',' ||
308                      'pv_xml_output_format=' || pv_xml_output_format);
309     END IF; --l_proc_level>=l_dbg_level
310     FND_FILE.put_line(FND_FILE.log,
311                       lv_procedure_name || '.parameters:' ||
312                       'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
313                       'pn_chart_of_account_id=' || pn_chart_of_account_id || ',' ||
314                       'pn_ledger_id=' || pn_ledger_id || ',' ||
315                       'pv_adhoc_prefix=' || pv_adhoc_prefix || ',' ||
316                       'pv_industry=' || pv_industry || ',' ||
317                       'pv_accounting_year=' || pv_accounting_year || ',' ||
318                       'pv_period_from=' || pv_period_from || ',' ||
319                       'pv_period_to=' || pv_period_to || ',' ||
320                       'pn_cfs_report_id=' || pn_cfs_report_id || ',' ||
321                       'pv_xml_template_language=' ||
322                       pv_xml_template_language || ',' ||
323                       'pv_xml_template_territory=' ||
324                       pv_xml_template_territory || ',' ||
325                       'pv_xml_output_format=' || pv_xml_output_format);
326 
327     --Step0: Generate XML header part
328     FND_FILE.put_line(FND_FILE.output,
329                       '<?xml version="1.0" encoding="' ||
330                       JA_CN_UTILITY.Get_XML_Encoding || '"?>');
331 
332     --added by jar.wang for bug 16655327
333    /* lv_gl_element := '<' || Ja_Cn_Utility.Get_XML_Tag('GENERAL_LEDGER') ||
334                      ' xmlns="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/Bank/XMLSchema"' ||
335                      ' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"'||
336                      ' xmlns:'||Ja_Cn_Utility.Get_XML_Tag('BANK')||'="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/Bank/XMLSchema"'||
337                      ' xsi:schemaLocation="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/Bank/XMLSchema '||
338                      Ja_Cn_Utility.Get_XML_Tag('GENERAL_LEDGER') ||'.xsd">';
339     lv_gl_element := '<' || Ja_Cn_Utility.Get_XML_Tag('GENERAL_LEDGER') ||
340                      ' xsi:schemaLocation="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema ' ||
341                      Ja_Cn_Utility.Get_XML_Tag('GENERAL_LEDGER') ||
342                      '.xsd" xmlns:' || Ja_Cn_Utility.Get_XML_Tag('BANK') ||
343                      '="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema"' ||
344                      ' xmlns="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">';
345    */
346 
347     lv_gl_element := '<' || Ja_Cn_Utility.Get_XML_Tag('GENERAL_LEDGER') ||
348                       ' xsi:schemaLocation="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/Bank/XMLSchema '||
349                      Ja_Cn_Utility.Get_XML_Tag('GENERAL_LEDGER') ||'.xsd"'||
350                      ' xmlns:'||Ja_Cn_Utility.Get_XML_Tag('BANK')||'="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/Bank/XMLSchema"'
351                      ||' xmlns="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/Bank/XMLSchema"' ||
352                      ' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' ;
353 
354     FND_FILE.put_line(FND_FILE.output, lv_gl_element);
355 
356     FND_FILE.put_line(FND_FILE.log, 'Setup1:Add_GL_BALANCE');
357     Ja_Cn_Utility.Add_Sub_Root_Node('GL_BALANCE_FLIE',Ja_Cn_Utility.GV_TAG_TYPE_START);
358     Recursion_BSV(pn_chart_of_account_id   ,
359                   pn_ledger_id     ,
360                   pn_legal_entity_id ,
361                   pv_bsv             ,
362                   pv_accounting_year ,
363                   pv_period_from     ,
364                   pv_period_to       ,
365                   1            );
366      Print_GL_BALANCE;
367      Ja_Cn_Utility.Add_Sub_Root_Node('GL_BALANCE_FLIE', Ja_Cn_Utility.GV_TAG_TYPE_END);
368 
369     FND_FILE.put_line(FND_FILE.log, 'Setup2:Add_GL_AVG_BAL');
370     Ja_Cn_Utility.Add_Sub_Root_Node('GL_DETAIL_FILE',Ja_Cn_Utility.GV_TAG_TYPE_START);
371     Recursion_BSV(pn_chart_of_account_id   ,
372                   pn_ledger_id     ,
373                   pn_legal_entity_id ,
374                   pv_bsv             ,
375                   pv_accounting_year ,
376                   pv_period_from     ,
377                   pv_period_to       ,
378                   2            );
379     Print_GL_AVG_BAL;
380     Ja_Cn_Utility.Add_Sub_Root_Node('GL_DETAIL_FILE',Ja_Cn_Utility.GV_TAG_TYPE_END);
381 
382 
383     FND_FILE.put_line(FND_FILE.log, 'Setup3:Add_GL_JOURNAL');
384     Ja_Cn_Utility.Add_Sub_Root_Node('GL_JOURNAL_FILE',Ja_Cn_Utility.GV_TAG_TYPE_START);
385     Recursion_BSV(pn_chart_of_account_id   ,
386                   pn_ledger_id     ,
387                   pn_legal_entity_id ,
388                   pv_bsv             ,
389                   pv_accounting_year ,
390                   pv_period_from     ,
391                   pv_period_to       ,
392                   3            );
393     Ja_Cn_Utility.Add_Sub_Root_Node('GL_JOURNAL_FILE', Ja_Cn_Utility.GV_TAG_TYPE_END);
394     FND_FILE.put_line(FND_FILE.output, Ja_Cn_Utility.Get_XML_Tag('GENERAL_LEDGER',Ja_Cn_Utility.GV_TAG_TYPE_END));
395     COMMIT;
396     --logging for debug
397     IF (ln_proc_level >= ln_dbg_level) THEN
398       FND_LOG.STRING(ln_proc_level,
399                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
400                      'Exit procedure');
401     END IF; -- (ln_proc_level>=ln_dbg_level)
402 
403   EXCEPTION
404     WHEN OTHERS THEN
405       IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
406         FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
407                        GV_MODULE_PREFIX || '.' || lv_procedure_name ||
408                        '.Other_Exception ',
409                        SQLCODE || SQLERRM);
410       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
411       pv_retcode := '2';
412       pv_errbuf  := SQLCODE || SQLERRM;
413       FND_FILE.put_line(FND_FILE.log,GV_MODULE_PREFIX||'.'||
414                         lv_procedure_name ||': '|| SQLCODE || SQLERRM);
415   END Export_GL;
416 
417 
418   PROCEDURE Convert_Period(pn_ledger_id       IN NUMBER,
419                            pv_accounting_year IN VARCHAR2,
420                            pv_start           IN VARCHAR2,
421                            pv_end             IN VARCHAR2,
422                            pd_start           OUT NOCOPY DATE,
423                            pd_end             OUT NOCOPY DATE) IS
424     l_start_date date;
425     l_end_date   date;
426   BEGIN
427     --Fetch start date and end date
428     IF pv_start IS NOT NULL THEN
429       SELECT start_date
430         INTO l_start_date
431         FROM GL_PERIOD_STATUSES
432        WHERE ledger_id = pn_ledger_id
433          AND application_id = 101
434          AND period_name = pv_start
435          AND TO_CHAR(period_year) = pv_accounting_year;
436     ELSE
437       l_start_date := to_date(pv_accounting_year || '0101', 'YYYYMMDD');
438     END IF; --l_start_date IS NOT NULL
439 
440     IF pv_start IS NOT NULL THEN
441       SELECT end_date
442         INTO l_end_date
443         FROM GL_PERIOD_STATUSES
444        WHERE ledger_id = pn_ledger_id
445          AND application_id = 101
446          AND period_name = pv_end
447          AND TO_CHAR(period_year) = pv_accounting_year;
448     ELSE
449       l_end_date := to_date(pv_accounting_year || '1231', 'YYYYMMDD');
450     END IF; --l_end_date IS NOT NULL
451     pd_start := l_start_date;
452     pd_end   := l_end_date;
453   EXCEPTION
454     WHEN NO_DATA_FOUND THEN
455       RAISE_APPLICATION_ERROR(-20000,
456                               'Exception Fetch Accounting Period:' ||
457                               sqlerrm);
458   END Convert_Period;
459 
460 
461  --==========================================================================
462   --  PROCEDURE NAME:
463   --
464   --   Add_GL_AVG_BAL                        Public
465   --
466   --  DESCRIPTION:
467   --
468   --    This procedure is to export non-FSG report for general ledger average balance
469   --
470   --  PARAMETERS:
471   --      Out: pv_errbuf                 NOCOPY VARCHAR2
472   --           pv_retcode                NOCOPY VARCHAR2
473   --      In:  pn_legal_entity_id        NUMBER identifier of legal entity
474   --           pn_chart_of_account_id    NUMBER identifier of chart of account
475   --           pn_ledger_id              NUMBER identifier of ledger
476   --           pv_bsv                    VARCHAR2 balance segment value
477   --           pv_accounting_year        VARCHAR2 accounting year
478   --           pv_start                  VARCHAR2 period from
479   --           pv_end                    VARCHAR2 period to
480   --
481   --
482   --  CHANGE HISTORY:
483   --      25-Oct-2012   Jar Wang  created
484   --==========================================================================
485   PROCEDURE Add_GL_BALANCE(pn_chart_of_account_id IN NUMBER,
486                            pn_ledger_id           IN NUMBER,
487                            pn_legal_entity_id     IN NUMBER,
488                            pv_bsv                 IN VARCHAR2,
489                            pv_accounting_year     IN VARCHAR2,
490                            pv_start               IN VARCHAR2,
491                            pv_end                 IN VARCHAR2) IS
492     lv_procedure_name  VARCHAR2(40) := 'Add_GL_BALANCE';
493     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
494     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
495 
496     CURSOR CUR_GL_BAL(c_from date, c_to date) is
497       SELECT Ja_Cn_Utility.GET_ACCOUNTING_SEGMENT(GBS.CODE_COMBINATION_ID) ACCOUNT_SEG,
498              GBS.CURRENCY_CODE,
499              GBS.PERIOD_YEAR,
500              GBS.PERIOD_NUM,
501              --SUM( DECODE(GBS.CURRENCY_CODE,GLS.CURRENCY_CODE,GBS.BEGIN_BALANCE_DR_BEQ,GBS.BEGIN_BALANCE_DR) ) BEGIN_BALANCE_DR,
502              -- SUM( GBS.BEGIN_BALANCE_DR_BEQ )BEGIN_BALANCE_DR_BEQ,
503              --SUM( DECODE(GBS.CURRENCY_CODE,GLS.CURRENCY_CODE,GBS.BEGIN_BALANCE_CR_BEQ,GBS.BEGIN_BALANCE_CR) ) BEGIN_BALANCE_CR,
504              --SUM( GBS.BEGIN_BALANCE_CR_BEQ )BEGIN_BALANCE_CR_BEQ,
505              SUM((DECODE(GBS.CURRENCY_CODE,
506                          GLS.CURRENCY_CODE,
507                          GBS.BEGIN_BALANCE_DR_BEQ,
508                          GBS.BEGIN_BALANCE_DR) -
509                  DECODE(GBS.CURRENCY_CODE,
510                          GLS.CURRENCY_CODE,
511                          GBS.BEGIN_BALANCE_CR_BEQ,
512                          GBS.BEGIN_BALANCE_CR))) BEGIN_BALANCE,
513              SUM((GBS.BEGIN_BALANCE_DR_BEQ - GBS.BEGIN_BALANCE_CR_BEQ)) BEGIN_BALANCE_BEQ,
514              SUM(DECODE(GBS.CURRENCY_CODE,
515                         GLS.CURRENCY_CODE,
516                         GBS.PERIOD_NET_DR_BEQ,
517                         GBS.PERIOD_NET_DR)) PERIOD_NET_DR,
518              SUM(GBS.PERIOD_NET_DR_BEQ) PERIOD_NET_DR_BEQ,
519              SUM(DECODE(GBS.CURRENCY_CODE,
520                         GLS.CURRENCY_CODE,
521                         GBS.PERIOD_NET_CR_BEQ,
522                         GBS.PERIOD_NET_CR)) PERIOD_NET_CR,
523              SUM(GBS.PERIOD_NET_CR_BEQ) PERIOD_NET_CR_BEQ,
524              SUM((DECODE(GBS.CURRENCY_CODE,
525                          GLS.CURRENCY_CODE,
526                          GBS.BEGIN_BALANCE_DR_BEQ,
527                          GBS.BEGIN_BALANCE_DR) -
528                  DECODE(GBS.CURRENCY_CODE,
529                          GLS.CURRENCY_CODE,
530                          GBS.BEGIN_BALANCE_CR_BEQ,
531                          GBS.BEGIN_BALANCE_CR) +
532                  DECODE(GBS.CURRENCY_CODE,
533                          GLS.CURRENCY_CODE,
534                          GBS.PERIOD_NET_DR_BEQ,
535                          GBS.PERIOD_NET_DR) -
536                  DECODE(GBS.CURRENCY_CODE,
537                          GLS.CURRENCY_CODE,
538                          GBS.PERIOD_NET_CR_BEQ,
539                          GBS.PERIOD_NET_CR))) YTD,
540              SUM((GBS.BEGIN_BALANCE_DR_BEQ - GBS.BEGIN_BALANCE_CR_BEQ +
541                  GBS.PERIOD_NET_DR_BEQ - GBS.PERIOD_NET_CR_BEQ)) YTD_BEQ
542         FROM GL_BALANCES GBS, GL_LEDGERS GLS
543        WHERE GBS.LEDGER_ID = GLS.LEDGER_ID
544          AND GBS.LEDGER_ID =pn_ledger_id
545          AND  Ja_Cn_Utility.get_balancing_segment(GBS.CODE_COMBINATION_ID)=pv_bsv
546          AND PERIOD_NAME in
547              (SELECT period_name
548                 FROM GL_PERIOD_STATUSES
549                WHERE ledger_id = pn_ledger_id
550                  AND application_id = 101
551                  AND ((start_date BETWEEN c_from AND c_to) AND
552                      (end_date BETWEEN c_from AND c_to)))
553        GROUP BY Ja_Cn_Utility.GET_ACCOUNTING_SEGMENT(GBS.CODE_COMBINATION_ID),
554                 GBS.CURRENCY_CODE,
555                 GBS.PERIOD_YEAR,
556                 GBS.PERIOD_NUM;
557     l_gl_bal      CUR_GL_BAL%ROWTYPE;
558     l_period_from date;
559     l_period_to   date;
560   BEGIN
561     IF (ln_proc_level >= ln_dbg_level) THEN
562       FND_LOG.STRING(ln_proc_level,
563                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
564                      '.begin',
565                      'Enter procedure');
566      END IF;
567      FND_FILE.put_line(FND_FILE.log, lv_procedure_name||'Setup1.1');
568      --Fetch start date and end date
569      Convert_Period(pn_ledger_id ,
570                      pv_accounting_year  ,
571                      pv_start ,
572                      pv_end ,
573                      l_period_from   ,
574                      l_period_to ) ;
575 
576      OPEN CUR_GL_BAL(l_period_from,l_period_to);
577      LOOP
578        FETCH CUR_GL_BAL INTO l_gl_bal;
579        EXIT WHEN CUR_GL_BAL%NOTFOUND;
580        FND_FILE.put_line(FND_FILE.log, lv_procedure_name||'Setup1.2');
581        INSERT INTO JA_CN_BANK_BALANCE_GT
582          (ACCOUNT_SEG,
583           CURRENCY_CODE,
584           PERIOD_YEAR,
585           PERIOD_NUM,
586           BEGIN_BALANCE,
587           BEGIN_BALANCE_BEQ,
588           PERIOD_NET_DR,
589           PERIOD_NET_DR_BEQ,
590           PERIOD_NET_CR,
591           PERIOD_NET_CR_BEQ,
592           YTD_BALANCE,
593           YTD_BALANCE_BEQ)
594        VALUES
595          (l_gl_bal.account_seg,
596           l_gl_bal.currency_code,
597           l_gl_bal.period_year,
598           l_gl_bal.period_num,
599           l_gl_bal.begin_balance,
600           l_gl_bal.begin_balance_beq,
601           l_gl_bal.period_net_dr,
602           l_gl_bal.period_net_dr_beq,
603           l_gl_bal.period_net_cr,
604           l_gl_bal.period_net_cr_beq,
605           l_gl_bal.ytd,
606           l_gl_bal.ytd_beq);
607      END LOOP;
608      CLOSE CUR_GL_BAL;
609 
610     FND_FILE.put_line(FND_FILE.log, lv_procedure_name||'Setup1.3');
611     --logging for debug
612     IF (ln_proc_level >= ln_dbg_level) THEN
613       FND_LOG.STRING(ln_proc_level,
614                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
615                      'Exit procedure');
616     END IF; -- (ln_proc_level>=ln_dbg_level)
617   EXCEPTION
618     WHEN OTHERS THEN
619       IF (ln_proc_level >= ln_dbg_level)
620       THEN
621         FND_LOG.STRING(ln_proc_level
622                       ,GV_MODULE_PREFIX||'.'||lv_procedure_name||'EXCEPTION'
623                       ,SQLCODE||':'||SQLERRM
624                       );
625         FND_LOG.String(ln_proc_level
626                       ,GV_MODULE_PREFIX||'.'||lv_procedure_name||'. Other_Exception '
627                       ,SQLCODE||':'||SQLERRM
628                       );
629       END IF;
630       RAISE;
631   END Add_GL_BALANCE;
632 
633  --==========================================================================
634   --  PROCEDURE NAME:
635   --
636   --   Add_GL_BALANCE                        Public
637   --
638   --  DESCRIPTION:
639   --
640   --    This procedure is to export non-FSG report for general ledger balance
641   --
642   --  PARAMETERS:
643   --      Out: pv_errbuf                 NOCOPY VARCHAR2
644   --           pv_retcode                NOCOPY VARCHAR2
645   --      In:  pn_legal_entity_id        NUMBER identifier of legal entity
646   --           pn_chart_of_account_id    NUMBER identifier of chart of account
647   --           pn_ledger_id              NUMBER identifier of ledger
648   --           pv_bsv                    VARCHAR2 balance segment value
649   --           pv_accounting_year        VARCHAR2 accounting year
650   --           pv_start                  VARCHAR2 period from
651   --           pv_end                    VARCHAR2 period to
652   --
653   --
654   --  CHANGE HISTORY:
655   --      25-Oct-2012   Jar Wang  created
656   --==========================================================================
657   PROCEDURE Print_GL_BALANCE IS
658     lv_procedure_name  VARCHAR2(40) := 'Add_GL_BALANCE';
659     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
660     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
661 
662     CURSOR CUR_GL_BAL is
663       SELECT ACCOUNT_SEG,
664           CURRENCY_CODE,
665           PERIOD_YEAR,
666           PERIOD_NUM,
667           SUM(BEGIN_BALANCE) BEGIN_BALANCE,
668           SUM(BEGIN_BALANCE_BEQ) BEGIN_BALANCE_BEQ,
669           SUM(PERIOD_NET_DR) PERIOD_NET_DR,
670           SUM(PERIOD_NET_DR_BEQ) PERIOD_NET_DR_BEQ,
671           SUM(PERIOD_NET_CR) PERIOD_NET_CR,
672           SUM(PERIOD_NET_CR_BEQ) PERIOD_NET_CR_BEQ,
673           SUM(YTD_BALANCE) YTD,
674           SUM(YTD_BALANCE_BEQ) YTD_BEQ
675         FROM JA_CN_BANK_BALANCE_GT
676        GROUP BY ACCOUNT_SEG,
677                 CURRENCY_CODE,
678                  PERIOD_YEAR,
679                 PERIOD_NUM
680        HAVING SUM(BEGIN_BALANCE)<>0 OR SUM(PERIOD_NET_DR)<>0 OR SUM(PERIOD_NET_CR)<>0
681        ORDER BY  PERIOD_NUM,ACCOUNT_SEG;
682     l_gl_bal      CUR_GL_BAL%ROWTYPE;
683     l_row_count   pls_integer:=0;
684   BEGIN
685     IF (ln_proc_level >= ln_dbg_level) THEN
686       FND_LOG.STRING(ln_proc_level,
687                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
688                      '.begin',
689                      'Enter procedure');
690      END IF;
691      FND_FILE.put_line(FND_FILE.log, lv_procedure_name||'Setup1.1');
692 
693      OPEN CUR_GL_BAL;
694      LOOP
695        FETCH CUR_GL_BAL INTO l_gl_bal;
696        EXIT WHEN CUR_GL_BAL%NOTFOUND;
697        l_row_count:=l_row_count+1;
698        FND_FILE.put_line(FND_FILE.log, lv_procedure_name||'Setup1.2');
699        Ja_Cn_Utility.Add_Sub_Root_Node('GL_BALANCE',Ja_Cn_Utility.GV_TAG_TYPE_START);
700        Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',l_gl_bal.account_seg);
701        Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE',l_gl_bal.currency_code);
702        Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_YEAR',l_gl_bal.period_year);
703        Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER',l_gl_bal.period_num);
704        Ja_Cn_Utility.Add_Child_Node('BEGINNING_ENTERED_BALANCE',nvl(l_gl_bal.begin_balance,0));
705        Ja_Cn_Utility.Add_Child_Node('BEGINNING_FUNCTIONAL_BALANCE',nvl(l_gl_bal.begin_balance_beq,0));
706        Ja_Cn_Utility.Add_Child_Node('PERIOD_DEBIT_ENTERED_AMOUNT',nvl(l_gl_bal.period_net_dr,0));
707        Ja_Cn_Utility.Add_Child_Node('PERIOD_DEBIT_FUNCTIONAL_AMOUNT',nvl(l_gl_bal.period_net_dr_beq,0));
708        Ja_Cn_Utility.Add_Child_Node('PERIOD_CREDIT_ENTERED_AMOUNT',nvl(l_gl_bal.period_net_cr,0));
709        --Ja_Cn_Utility.Add_Child_Node('PERIOD_CREDIT_FUNCTIONAL_AMOUNT',nvl(l_gl_bal.period_net_cr_beq,0));
710       Ja_Cn_Utility.Add_Child_Node('PERIOD_CREDIT_FUNCTIONAL_AM',nvl(l_gl_bal.period_net_cr_beq,0));
711        Ja_Cn_Utility.Add_Child_Node('END_ENTERED_BALANCE',nvl(l_gl_bal.ytd,0));
712        Ja_Cn_Utility.Add_Child_Node('END_FUNCTIONAL_BALANCE',nvl(l_gl_bal.ytd_beq,0));
713        Ja_Cn_Utility.Add_Sub_Root_Node('GL_BALANCE', Ja_Cn_Utility.GV_TAG_TYPE_END);
714      END LOOP;
715      CLOSE CUR_GL_BAL;
716 
717      --no data found
718      IF l_row_count=0 THEN
719        Ja_Cn_Utility.Add_Sub_Root_Node('GL_BALANCE',Ja_Cn_Utility.GV_TAG_TYPE_START);
720        Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER','');
721        Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE','');
722        Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_YEAR','');
723        Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER','');
724        Ja_Cn_Utility.Add_Child_Node('BEGINNING_ENTERED_BALANCE','');
725        Ja_Cn_Utility.Add_Child_Node('BEGINNING_FUNCTIONAL_BALANCE','');
726        Ja_Cn_Utility.Add_Child_Node('DEBIT_ENTERED_AMOUNT','');
727        Ja_Cn_Utility.Add_Child_Node('DEBIT_FUNCTIONAL_AMOUNT','');
728        Ja_Cn_Utility.Add_Child_Node('CREDIT_ENTERED_AMOUNT','');
729        Ja_Cn_Utility.Add_Child_Node('CREDIT_FUNCTIONAL_AMOUNT','');
730        Ja_Cn_Utility.Add_Child_Node('END_ENTERED_BALANCE','');
731        Ja_Cn_Utility.Add_Child_Node('END_FUNCTIONAL_BALANCE','');
732        Ja_Cn_Utility.Add_Sub_Root_Node('GL_BALANCE', Ja_Cn_Utility.GV_TAG_TYPE_END);
733      END IF;
734 
735      FND_FILE.put_line(FND_FILE.log, lv_procedure_name||'Setup1.3');
736     --logging for debug
737     IF (ln_proc_level >= ln_dbg_level) THEN
738       FND_LOG.STRING(ln_proc_level,
739                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
740                      'Exit procedure');
741     END IF; -- (ln_proc_level>=ln_dbg_level)
742   EXCEPTION
743     WHEN OTHERS THEN
744       IF (ln_proc_level >= ln_dbg_level)
745       THEN
746         FND_LOG.STRING(ln_proc_level
747                       ,GV_MODULE_PREFIX||'.'||lv_procedure_name||'EXCEPTION'
748                       ,SQLCODE||':'||SQLERRM
749                       );
750       END IF;
751       RAISE;
752   END Print_GL_BALANCE;
753 
754 
755 
756 
757   PROCEDURE Add_GL_AVG_BAL(pn_chart_of_account_id IN NUMBER,
758                            pn_ledger_id           IN NUMBER,
759                            pn_legal_entity_id     IN NUMBER,
760                            pv_bsv                 IN VARCHAR2,
761                            pv_accounting_year     IN VARCHAR2,
762                            pv_start               IN VARCHAR2,
763                            pv_end                 IN VARCHAR2)
764   IS
765     lv_procedure_name  VARCHAR2(40) := 'Add_GL_AVG_BAL';
766     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
767     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
768 
769     CURSOR CUR_AVG_JOURNAL(c_from date, c_to date)
770     IS
771       SELECT JA_CN_UTILITY.GET_ACCOUNTING_SEGMENT(GJL.CODE_COMBINATION_ID) ACCOUNT_SEG,
772              GJH.CURRENCY_CODE,
773              TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'YYYY') PERIOD_YEAR,
774              TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'MM') PERIOD_NUM,
775              TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'YYYYMMDD') JOURNAL_DATE,
776              SUM(GJL.ENTERED_DR) ENTERED_DR,
777              SUM(GJL.ACCOUNTED_DR) ACCOUNTED_DR ,
778              SUM(GJL.ENTERED_CR) ENTERED_CR,
779              SUM(GJL.ACCOUNTED_CR) ACCOUNTED_CR
780         FROM GL_JE_HEADERS GJH, GL_JE_LINES GJL
781        WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
782          AND gjh.status = 'P'
783          AND JA_CN_UTILITY.get_balancing_segment(GJL.CODE_COMBINATION_ID) = pv_bsv
784          AND GJH.LEDGER_ID = pn_ledger_id
785          AND GJH.PERIOD_NAME in
786              (SELECT period_name
787                 FROM GL_PERIOD_STATUSES
788                WHERE ledger_id = pn_ledger_id
789                  AND application_id = 101
790                  AND ((start_date BETWEEN c_from AND c_to) AND
791                      (end_date BETWEEN c_from AND c_to)))
792        GROUP BY JA_CN_UTILITY.GET_ACCOUNTING_SEGMENT(GJL.CODE_COMBINATION_ID),GJH.DEFAULT_EFFECTIVE_DATE ,GJH.CURRENCY_CODE;
793       l_period_from DATE;
794       l_period_to   DATE;
795       l_journal     CUR_AVG_JOURNAL%ROWTYPE;
796   BEGIN
797     FND_FILE.put_line(FND_FILE.log, 'Setup2.1');
798     IF (ln_proc_level >= ln_dbg_level) THEN
799       FND_LOG.STRING(ln_proc_level,
800                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
801                      '.begin',
802                      'Enter procedure');
803      END IF;
804 
805      --Fetch start date and end date
806      Convert_Period(pn_ledger_id ,
807                      pv_accounting_year  ,
808                      pv_start ,
809                      pv_end ,
810                      l_period_from   ,
811                      l_period_to ) ;
812 
813     FND_FILE.put_line(FND_FILE.log, 'Setup2.2');
814     OPEN CUR_AVG_JOURNAL(l_period_from,l_period_to);
815     LOOP
816       FETCH CUR_AVG_JOURNAL INTO l_journal;
817       EXIT WHEN CUR_AVG_JOURNAL%NOTFOUND;
818 
819       FND_FILE.put_line(FND_FILE.log, 'Setup2.3');
820       INSERT INTO JA_CN_BANK_AVG_BAL_GT
821         (ACCOUNT_SEG,
822          CURRENCY_CODE,
823          PERIOD_YEAR,
824          PERIOD_NUM,
825          JOURNAL_DATE,
826          ENTERED_DR,
827          ACCOUNTED_DR,
828          ENTERED_CR,
829          ACCOUNTED_CR)
830       VALUES
831         (l_journal.ACCOUNT_SEG,
832          l_journal.CURRENCY_CODE,
833          l_journal.PERIOD_YEAR,
834          l_journal.PERIOD_NUM,
835          l_journal.JOURNAL_DATE,
836          l_journal.ENTERED_DR,
837          l_journal.ACCOUNTED_DR,
838          l_journal.ENTERED_CR,
839          l_journal.ACCOUNTED_CR);
840     END LOOP;
841     CLOSE CUR_AVG_JOURNAL;
842 
843     FND_FILE.put_line(FND_FILE.log, 'Setup2.4');
844     --logging for debug
845     IF (ln_proc_level >= ln_dbg_level) THEN
846       FND_LOG.STRING(ln_proc_level,
847                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
848                      'Exit procedure');
849     END IF; -- (ln_proc_level>=ln_dbg_level)
850   EXCEPTION
851     WHEN OTHERS THEN
852       IF (ln_proc_level >= ln_dbg_level)
853       THEN
854         FND_LOG.STRING(ln_proc_level
855                       ,GV_MODULE_PREFIX||'.'||lv_procedure_name||'EXCEPTION'
856                       ,SQLCODE||':'||SQLERRM
857                       );
858         FND_LOG.String(ln_proc_level
859                       ,GV_MODULE_PREFIX||'.'||lv_procedure_name||'. Other_Exception '
860                       ,SQLCODE||':'||SQLERRM
861                       );
862       END IF;
863       RAISE;
864   END  Add_GL_AVG_BAL;
865 
866 
867 
868   PROCEDURE Print_GL_AVG_BAL
869   IS
870     lv_procedure_name  VARCHAR2(40) := 'Pring_GL_AVG_BAL';
871     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
872     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
873     CURSOR CUR_AVG_JOURNAL
874     IS
875       SELECT  ACCOUNT_SEG,
876              CURRENCY_CODE,
877              PERIOD_YEAR,
878              PERIOD_NUM,
879              JOURNAL_DATE,
880              SUM( ENTERED_DR) ENTERED_DR,
881              SUM( ACCOUNTED_DR) ACCOUNTED_DR ,
882              SUM( ENTERED_CR) ENTERED_CR,
883              SUM( ACCOUNTED_CR) ACCOUNTED_CR
884         FROM JA_CN_BANK_AVG_BAL_GT
885        GROUP BY ACCOUNT_SEG,JOURNAL_DATE,CURRENCY_CODE,PERIOD_YEAR,PERIOD_NUM
886        ORDER BY JOURNAL_DATE,ACCOUNT_SEG;
887     l_journal     CUR_AVG_JOURNAL%ROWTYPE;
888     l_row_count   pls_integer:=0;
889   BEGIN
890     IF (ln_proc_level >= ln_dbg_level) THEN
891       FND_LOG.STRING(ln_proc_level,
892                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
893                      '.begin',
894                      'Enter procedure');
895      END IF;
896      FND_FILE.put_line(FND_FILE.log, 'AVG Setup1.1 ');
897      OPEN CUR_AVG_JOURNAL;
898      LOOP
899       FETCH CUR_AVG_JOURNAL INTO l_journal;
900       EXIT WHEN CUR_AVG_JOURNAL%NOTFOUND;
901       l_row_count :=l_row_count+1;
902       Ja_Cn_Utility.Add_Sub_Root_Node('GL_DETAIL',Ja_Cn_Utility.GV_TAG_TYPE_START);
903       FND_FILE.put_line(FND_FILE.log, 'AVG Setup1.2 ');
904       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',l_journal.ACCOUNT_SEG );
905       Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE',l_journal.CURRENCY_CODE );
906       Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_YEAR',l_journal.PERIOD_YEAR );
907       Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER',l_journal.PERIOD_NUM );
908       Ja_Cn_Utility.Add_Child_Node('JOURNAL_DATE',l_journal.JOURNAL_DATE );
909       Ja_Cn_Utility.Add_Child_Node('DEBIT_ENTERED_AMOUNT',nvl(l_journal.ENTERED_DR,0));
910       Ja_Cn_Utility.Add_Child_Node('DEBIT_FUNCTIONAL_AMOUNT',nvl(l_journal.ACCOUNTED_DR,0));
911       Ja_Cn_Utility.Add_Child_Node('CREDIT_ENTERED_AMOUNT',nvl(l_journal.ENTERED_CR,0));
912       Ja_Cn_Utility.Add_Child_Node('CREDIT_FUNCTIONAL_AMOUNT',nvl(l_journal.ACCOUNTED_CR,0));
913 
914       Ja_Cn_Utility.Add_Sub_Root_Node('GL_DETAIL',Ja_Cn_Utility.GV_TAG_TYPE_END);
915      END LOOP;
916      CLOSE CUR_AVG_JOURNAL;
917      --no data found
918      IF l_row_count=0 THEN
919       Ja_Cn_Utility.Add_Sub_Root_Node('GL_DETAIL',Ja_Cn_Utility.GV_TAG_TYPE_START);
920       Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER','' );
921       Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE','' );
922       Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_YEAR','' );
923       Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER','');
924       Ja_Cn_Utility.Add_Child_Node('JOURNAL_DATE','');
925       Ja_Cn_Utility.Add_Child_Node('DEBIT_ENTERED_AMOUNT','');
926       Ja_Cn_Utility.Add_Child_Node('DEBIT_FUNCTIONAL_AMOUNT','');
927       Ja_Cn_Utility.Add_Child_Node('CREDIT_ENTERED_AMOUNT','');
928       Ja_Cn_Utility.Add_Child_Node('CREDIT_FUNCTIONAL_AMOUNT','');
929 
930       Ja_Cn_Utility.Add_Sub_Root_Node('GL_DETAIL',Ja_Cn_Utility.GV_TAG_TYPE_END);
931      END IF;
932 
933      --logging for debug
934      IF (ln_proc_level >= ln_dbg_level) THEN
935       FND_LOG.STRING(ln_proc_level,
936                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
937                      'Exit procedure');
938      END IF; -- (ln_proc_level>=ln_dbg_level)
939   EXCEPTION
940     WHEN OTHERS THEN
941       IF (ln_proc_level >= ln_dbg_level)
942       THEN
943         FND_LOG.STRING(ln_proc_level
944                       ,GV_MODULE_PREFIX||'.'||lv_procedure_name||'EXCEPTION'
945                       ,SQLCODE||':'||SQLERRM
946                       );
947         FND_LOG.String(ln_proc_level
948                       ,GV_MODULE_PREFIX||'.'||lv_procedure_name||'. Other_Exception '
949                       ,SQLCODE||':'||SQLERRM
950                       );
951       END IF;
952       RAISE;
953    END Print_GL_AVG_BAL;
954 
955  --==========================================================================
956   --  PROCEDURE NAME:
957   --
958   --   Add_GL_JOURNAL                        Public
959   --
960   --  DESCRIPTION:
961   --
962   --    This procedure is to export non-FSG report for general ledger journal
963   --
964   --  PARAMETERS:
965   --      Out: pv_errbuf                 NOCOPY VARCHAR2
966   --           pv_retcode                NOCOPY VARCHAR2
967   --      In:  pn_legal_entity_id        NUMBER identifier of legal entity
968   --           pn_chart_of_account_id    NUMBER identifier of chart of account
969   --           pn_ledger_id              NUMBER identifier of ledger
970   --           pv_bsv                    VARCHAR2 balance segment value
971   --           pv_accounting_year        VARCHAR2 accounting year
972   --           pv_start                  VARCHAR2 period from
973   --           pv_end                    VARCHAR2 period to
974   --
975   --
976   --  CHANGE HISTORY:
977   --      25-Oct-2012   Jar Wang  created
978   --==========================================================================
979   PROCEDURE Add_GL_JOURNAL(pn_chart_of_account_id IN NUMBER,
980                            pn_ledger_id           IN NUMBER,
981                            pn_legal_entity_id     IN NUMBER,
982                            pv_bsv                 IN VARCHAR2,
983                            pv_accounting_year     IN VARCHAR2,
984                            pv_start               IN VARCHAR2,
985                            pv_end                 IN VARCHAR2)
986 
987    IS
988     lv_procedure_name  VARCHAR2(40) := 'Add_GL_JOURNAL';
989     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
990     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
991      CURSOR CUR_JOURNAL(c_from date, c_to date)
992      IS
993       SELECT TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'YYYYMMDD') JOURNAL_DATE,
994              GJH.DESCRIPTION,
995              GJL.JE_LINE_NUM,
996              JA_CN_UTILITY.GET_ACCOUNTING_SEGMENT(GJL.CODE_COMBINATION_ID) ACCOUNT_SEG,
997              GJH.CURRENCY_CODE,
998              GJL.ENTERED_DR,
999              GJL.ACCOUNTED_DR,
1000              GJL.ENTERED_CR,
1001              GJL.ACCOUNTED_CR,
1002              '0' ATTACHMENT,
1003              FU.USER_NAME CREATOR,
1004              FU2.USER_NAME REVIEWER,
1005              FU2.USER_NAME POSTER,
1006              '1' POSTED_FLAG,
1007              '0' CANCEL_FLAG,
1008              TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'MM') PERIOD_NUM,
1009              TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'YYYY') PERIOD_YEAR,
1010              GJH.JE_HEADER_ID,
1011              GJH.PERIOD_NAME,
1012              GJH.JE_BATCH_ID
1013         FROM GL_JE_HEADERS GJH, GL_JE_LINES GJL, FND_USER FU, FND_USER FU2
1014        WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
1015          AND gjh.status = 'P'
1016          AND FU.USER_ID = GJL.CREATED_BY
1017          AND FU2.USER_ID = GJL.LAST_UPDATED_BY
1018          AND JA_CN_UTILITY.get_balancing_segment(GJL.CODE_COMBINATION_ID) = pv_bsv
1019          AND GJH.LEDGER_ID = pn_ledger_id
1020          AND GJH.PERIOD_NAME in
1021              (SELECT period_name
1022                 FROM GL_PERIOD_STATUSES
1023                WHERE ledger_id = pn_ledger_id
1024                  AND application_id = 101
1025                  AND ((start_date BETWEEN c_from AND c_to) AND
1026                      (end_date BETWEEN c_from AND c_to)))
1027        ORDER BY NVL(gjh.POSTING_ACCT_SEQ_VALUE, -1) ASC,
1028                 gjh.default_effective_date ASC,
1029                 gjh.posted_date ASC,
1030                 gjh.je_header_id ASC,
1031                 GJL.JE_LINE_NUM ASC;
1032       l_period_from         DATE;
1033       l_period_to           DATE;
1034       l_journal             CUR_JOURNAL%ROWTYPE;
1035       l_vouchernum_t        pls_integer :=0 ;
1036       l_vouchernum          varchar2(200);
1037       l_row_num             pls_integer:=0;
1038       l_mes                 varchar2(500);
1039       l_journal_header      pls_integer:=0;
1040       l_approver            varchar2(50);
1041       l_poster              varchar2(50);
1042    BEGIN
1043     IF (ln_proc_level >= ln_dbg_level) THEN
1044       FND_LOG.STRING(ln_proc_level,
1045                      GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1046                      '.begin',
1047                      'Enter procedure');
1048      END IF;
1049      --Fetch start date and end date
1050      Convert_Period(pn_ledger_id ,
1051                      pv_accounting_year  ,
1052                      pv_start ,
1053                      pv_end ,
1054                      l_period_from   ,
1055                      l_period_to ) ;
1056 
1057      FND_FILE.put_line(FND_FILE.log,'GL Setup:3.1');
1058 
1059      OPEN CUR_JOURNAL(l_period_from,l_period_to);
1060      LOOP
1061        FETCH CUR_JOURNAL INTO l_journal;
1062        EXIT WHEN CUR_JOURNAL%NOTFOUND;
1063        FND_FILE.put_line(FND_FILE.log,'GL Setup:3.2');
1064        l_row_num :=l_row_num+1;
1065        --get voucher number
1066        SELECT (SELECT NVL(VOUCHER_NUM, '')  FROM JA_CN_BANK_JOURNAL
1067               WHERE JE_HEADER_ID =  l_journal.Je_Header_Id AND  JE_LINE_NUM = l_journal.Je_Line_Num )
1068        INTO l_vouchernum
1069        FROM DUAL;
1070 
1071        IF l_vouchernum IS NULL   THEN
1072          --generate next voucher  number
1073          IF l_journal_header=0 OR l_journal_header <>  l_journal.je_header_id THEN
1074           l_vouchernum_t :=JA_CN_UPDATE_BANK_SEQ_PKG.Fetch_JL_Seq(pn_legal_entity_id  ,
1075                                                                   pv_bsv ,
1076                                                                   pn_ledger_id ,
1077                                                                   l_journal.PERIOD_NAME );
1078          END IF;
1079           FND_FILE.put_line(FND_FILE.log,'GL Setup:3.3:'||l_vouchernum_t);
1080           l_vouchernum := pv_bsv || l_vouchernum_t;
1081           INSERT INTO JA_CN_BANK_JOURNAL
1082             (JE_HEADER_ID,
1083              JE_LINE_NUM,
1084              VOUCHER_NUM,
1085              CREATED_BY,
1086              CREATION_DATE,
1087              LAST_UPDATED_BY,
1088              LAST_UPDATE_DATE,
1089              LAST_UPDATE_LOGIN)
1090           VALUES
1091             (l_journal.JE_HEADER_ID,
1092              l_journal.JE_LINE_NUM,
1093              l_vouchernum,
1094              fnd_global.USER_ID,
1095              sysdate,
1096              fnd_global.USER_ID,
1097              sysdate,
1098              fnd_global.LOGIN_ID);
1099 
1100        END IF;
1101        FND_FILE.put_line(FND_FILE.log,'GL Setup:3.4:'||l_vouchernum);
1102        Ja_Cn_Utility.Add_Sub_Root_Node('GL_JOURNAL',Ja_Cn_Utility.GV_TAG_TYPE_START);
1103        Ja_Cn_Utility.Add_Child_Node('JOURNAL_NUMBER',l_vouchernum);
1104        Ja_Cn_Utility.Add_Child_Node('GL_JOURNAL_CREATION_DATE',l_journal.journal_date);
1105        Ja_Cn_Utility.Add_Child_Node('JOURNAL_DESCRIPTION',l_journal.DESCRIPTION);
1106        Ja_Cn_Utility.Add_Child_Node('JOURNAL_LINE_NUMBER',l_journal.JE_LINE_NUM);
1107        Ja_Cn_Utility.Add_Child_Node('ACCOUNT_NUMBER',l_journal.ACCOUNT_SEG);
1108        Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE',l_journal.CURRENCY_CODE);
1109        Ja_Cn_Utility.Add_Child_Node('DEBIT_ENTERED_AMOUNT',nvl(l_journal.ENTERED_DR,0));
1110        Ja_Cn_Utility.Add_Child_Node('DEBIT_FUNCTIONAL_AMOUNT',nvl(l_journal.ACCOUNTED_DR,0));
1111        Ja_Cn_Utility.Add_Child_Node('CREDIT_ENTERED_AMOUNT',nvl(l_journal.ENTERED_CR,0));
1112        Ja_Cn_Utility.Add_Child_Node('CREDIT_FUNCTIONAL_AMOUNT',nvl(l_journal.ACCOUNTED_CR,0));
1113        Ja_Cn_Utility.Add_Child_Node('ATTACHMENT_QUANTITY',l_journal.ATTACHMENT);
1114        --Ja_Cn_Utility.Add_Child_Node('CREATOR',l_journal.CREATOR); --added by jar.wang for bug 16075191
1115        Ja_Cn_Utility.Add_Child_Node('CREATOR',Get_Fullname(l_journal.CREATOR));
1116        --Ja_Cn_Utility.Add_Child_Node('REVIEWER',l_journal.REVIEWER); --added by jar.wang for bug 16075191
1117        l_approver := Get_Approver(l_journal.je_header_id,l_journal.CREATOR);
1118        Ja_Cn_Utility.Add_Child_Node('REVIEWER',Get_Fullname(l_approver));
1119        --Ja_Cn_Utility.Add_Child_Node('POSTER',l_journal.POSTER);
1120        select fu.user_name
1121          into l_poster from gl_je_batches gjb, fnd_user fu
1122         where gjb.posted_by = fu.user_id
1123           and gjb.je_batch_id = l_journal.je_batch_id;
1124 
1125        Ja_Cn_Utility.Add_Child_Node('POSTER',Get_Fullname(l_poster));
1126 
1127        Ja_Cn_Utility.Add_Child_Node('POSTED',l_journal.POSTED_FLAG);
1128        Ja_Cn_Utility.Add_Child_Node('CANCELLED',l_journal.CANCEL_FLAG);
1129        Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER',l_journal.PERIOD_NUM);
1130        Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_YEAR',l_journal.PERIOD_YEAR);
1131 
1132        Ja_Cn_Utility.Add_Sub_Root_Node('GL_JOURNAL', Ja_Cn_Utility.GV_TAG_TYPE_END);
1133        l_journal_header := l_journal.je_header_id;
1134      END LOOP;
1135      CLOSE CUR_JOURNAL;
1136 
1137      IF l_row_num=0 THEN
1138        FND_MESSAGE.SET_NAME('JA','JA_CN_NO_DATA_FOUND') ;
1139        l_mes := FND_MESSAGE.GET;
1140        FND_FILE.put_line(FND_FILE.log,'Warning:'||pv_bsv||','||l_mes);
1141 
1142        Ja_Cn_Utility.Add_Sub_Root_Node('GL_JOURNAL',Ja_Cn_Utility.GV_TAG_TYPE_START);
1143        Ja_Cn_Utility.Add_Child_Node('JOURNAL_NUMBER','');
1144        Ja_Cn_Utility.Add_Child_Node('GL_JOURNAL_CREATION_DATE','');
1145        Ja_Cn_Utility.Add_Child_Node('JOURNAL_DESCRIPTION','');
1146        Ja_Cn_Utility.Add_Child_Node('JOURNAL_LINE_NUMBER','');
1147        Ja_Cn_Utility.Add_Child_Node('CHART_OF_ACCOUNT','');
1148        Ja_Cn_Utility.Add_Child_Node('CURRENCY_CODE','');
1149        Ja_Cn_Utility.Add_Child_Node('DEBIT_ENTERED_AMOUNT','');
1150        Ja_Cn_Utility.Add_Child_Node('DEBIT_FUNCTIONAL_AMOUNT','');
1151        Ja_Cn_Utility.Add_Child_Node('CREDIT_ENTERED_AMOUNT','');
1152        Ja_Cn_Utility.Add_Child_Node('CREDIT_FUNCTIONAL_AMOUNT','');
1153        Ja_Cn_Utility.Add_Child_Node('ATTACHMENT_QUANTITY','');
1154        Ja_Cn_Utility.Add_Child_Node('CREATOR','');
1155        Ja_Cn_Utility.Add_Child_Node('REVIEWER','');
1156        Ja_Cn_Utility.Add_Child_Node('POSTER','');
1157        Ja_Cn_Utility.Add_Child_Node('POSTED','');
1158        Ja_Cn_Utility.Add_Child_Node('CANCELLED','');
1159        Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD_NUMBER','');
1160        Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_YEAR','');
1161 
1162        Ja_Cn_Utility.Add_Sub_Root_Node('GL_JOURNAL', Ja_Cn_Utility.GV_TAG_TYPE_END);
1163      END IF;
1164 
1165      FND_FILE.put_line(FND_FILE.log,'GL Setup:3.5');
1166     --logging for debug
1167     IF (ln_proc_level >= ln_dbg_level) THEN
1168       FND_LOG.STRING(ln_proc_level,
1169                      GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1170                      'Exit procedure');
1171     END IF; -- (ln_proc_level>=ln_dbg_level)
1172   EXCEPTION
1173     WHEN OTHERS THEN
1174       IF (ln_proc_level >= ln_dbg_level)
1175       THEN
1176         FND_LOG.STRING(ln_proc_level
1177                       ,GV_MODULE_PREFIX||'.'||lv_procedure_name||'EXCEPTION'
1178                       ,SQLCODE||':'||SQLERRM
1179                       );
1180       END IF;
1181       FND_FILE.put_line(FND_FILE.log,GV_MODULE_PREFIX||'.'||
1182                         lv_procedure_name ||': '|| SQLCODE || SQLERRM);
1183       RAISE;
1184    END Add_GL_JOURNAL;
1185 
1186   --added by jar.wang for bug 16075191
1187   FUNCTION Get_FullName(p_username VARCHAR2) return varchar2
1188     as
1189     lv_procedure_name  VARCHAR2(40) := 'Get_FullName';
1190     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1191     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
1192     l_fullname varchar2(50);
1193   BEGIN
1194       BEGIN
1195         select full_name into l_fullname
1196           from PER_ALL_PEOPLE_F
1197          where person_id =
1198                (select employee_id from fnd_user where user_name = p_username)
1199            and nvl(effective_start_date, sysdate) <= sysdate
1200            and nvl(effective_end_date, sysdate) >= sysdate;
1201       EXCEPTION
1202         WHEN NO_DATA_FOUND THEN
1203           l_fullname :=  p_username;
1204       END;
1205 
1206     return l_fullname;
1207 
1208   EXCEPTION
1209     WHEN OTHERS THEN
1210       IF (ln_proc_level >= ln_dbg_level)
1211       THEN
1212         FND_LOG.STRING(ln_proc_level
1213                       ,GV_MODULE_PREFIX||'.'||lv_procedure_name||'EXCEPTION'
1214                       ,SQLCODE||':'||SQLERRM
1215                       );
1216       END IF;
1217       FND_FILE.put_line(FND_FILE.log,GV_MODULE_PREFIX||'.'||
1218                         lv_procedure_name ||': '|| SQLCODE || SQLERRM);
1219       RAISE;
1220   END Get_FullName;
1221 
1222 
1223 
1224   FUNCTION Get_Approver(p_je_header pls_integer,p_fnd_user varchar2) return varchar2
1225     as
1226     lv_procedure_name  VARCHAR2(40) := 'Get_Approver';
1227     ln_dbg_level       NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1228     ln_proc_level      NUMBER := FND_LOG.LEVEL_PROCEDURE;
1229     l_approver varchar2(50);
1230   BEGIN
1231       BEGIN
1232         select assigned_user into l_approver
1233           from wf_item_activity_statuses
1234          where activity_result_code = 'APPROVED'
1235            AND (item_key, notification_id) =
1236                (select max(item_key) item_key, max(notification_id) notification_id
1237                   from wf_notifications
1238                  where from_user =
1239                        (select global_name
1240                           from PER_ALL_PEOPLE_F ff
1241                          where person_id = (select employee_id
1242                                               from fnd_user
1243                                              where user_name = p_fnd_user)
1244                            and nvl(effective_start_date, sysdate) <= sysdate
1245                            and nvl(effective_end_date, sysdate) >= sysdate)
1246                    and user_key =
1247                        (select name
1248                           from gl_je_batches gjb
1249                          where gjb.je_batch_id =
1250                                (select gjh.je_batch_id
1251                                   from gl_je_headers gjh
1252                                  where gjh.je_header_id = p_je_header)));
1253 
1254       EXCEPTION
1255         WHEN NO_DATA_FOUND THEN
1256             SELECT FU2.USER_NAME into l_approver
1257               FROM GL_JE_HEADERS GJH, FND_USER FU2
1258              WHERE GJH.JE_HEADER_ID =p_je_header
1259                AND gjh.status = 'P'
1260                AND FU2.USER_ID = GJH.LAST_UPDATED_BY;
1261       END;
1262     RETURN l_approver;
1263   EXCEPTION
1264     WHEN OTHERS THEN
1265       IF (ln_proc_level >= ln_dbg_level)
1266       THEN
1267         FND_LOG.STRING(ln_proc_level
1268                       ,GV_MODULE_PREFIX||'.'||lv_procedure_name||'EXCEPTION'
1269                       ,SQLCODE||':'||SQLERRM
1270                       );
1271       END IF;
1272       FND_FILE.put_line(FND_FILE.log,GV_MODULE_PREFIX||'.'||
1273                         lv_procedure_name ||': '|| SQLCODE || SQLERRM);
1274       RAISE;
1275 
1276   END Get_Approver;
1277 
1278 END JA_CN_GL_BANK_EXP_PKG;
1279 
1280 
1281