[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