[Home] [Help]
PACKAGE BODY: APPS.JA_CN_EAB_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_EAB_EXPORT_PKG AS
2 --$Header: JACNVBEB.pls 120.1.12000000.1 2007/08/13 14:09:53 qzhao noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNVBEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used to export electronic accounting book |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Execute_Export |
16 --| PROCEDURE Query_System_Options |
17 --| PROCEDURE Parse_Account_Structure |
18 --| PROCEDURE Query_Currency |
19 --| PROCEDURE Query_Software_Infor |
20 --| |
21 --| HISTORY |
22 --| 03/13/2006 Jackey Li Created |
23 --| 2006-7-10 Jackey Li Update the way how to get account |
24 --| structure due to Bug 5380368 |
25 --+======================================================================*/
26
27 --==== Golbal Variables ============
28 g_module_name VARCHAR2(30) := 'JA_CN_EAB_EXPORT_PKG';
29 g_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
30 g_proc_level NUMBER := FND_LOG.Level_Procedure;
31 g_stmt_level NUMBER := FND_LOG.Level_Statement;
32 g_ledger_id GL_LEDGERS.Ledger_Id%TYPE;
33 g_book_num JA_CN_SYSTEM_PARAMETERS_ALL.BOOK_NUM%TYPE;
34 g_book_name JA_CN_SYSTEM_PARAMETERS_ALL.BOOK_NAME%TYPE;
35 g_company_name JA_CN_SYSTEM_PARAMETERS_ALL.COMPANY_NAME%TYPE;
36 g_organization_id JA_CN_SYSTEM_PARAMETERS_ALL.ORGANIZATION_ID%TYPE;
37 g_ent_quality JA_CN_SYSTEM_PARAMETERS_ALL.ENT_QUALITY%TYPE;
38 g_ent_industry JA_CN_SYSTEM_PARAMETERS_ALL.ENT_INDUSTRY%TYPE;
39 g_account_structure VARCHAR2(2000);
40 g_functional_currency VARCHAR2(1000) := NULL;
41 g_software_name VARCHAR2(100) := NULL;
42 g_software_version VARCHAR2(100) := NULL;
43
44 --==========================================================================
45 -- PROCEDURE NAME:
46 -- Query_Account_Structure Private
47 --
48 -- DESCRIPTION:
49 -- This procedure is used to parse the account structure defined in
50 -- the 'JA_CN_SYSTEM_PARAMETERS_ALL' table
51 --
52 -- PARAMETERS:
53 -- N/A
54 --
55 -- DESIGN REFERENCES:
56 -- CNAO_Electronic_Accounting_Book_Export.doc
57 --
58 -- CHANGE HISTORY:
59 -- 03/13/2006 Jackey Li Created
60 --===========================================================================
61 PROCEDURE Query_Account_Structure (P_COA_ID IN NUMBER
62 )IS
63 l_procedure_name VARCHAR2(30) := 'Parse_Account_Structure';
64
65 l_coa_id NUMBER := P_COA_ID;
66 l_sql varchar2(1000);
67 l_account_structures_kfv VARCHAR2(100) := 'ja_cn_account_structures_kfv';
68 l_comma_position NUMBER;
69
70 BEGIN
71 --log for debug
72 IF (g_proc_level >= g_dbg_level) THEN
73 FND_LOG.STRING(g_proc_level,
74 g_module_name || '.' || l_procedure_name || '.begin',
75 'begin procedure');
76 END IF; --( g_proc_level >= g_dbg_level)
77
78 --Get Chart of Accounts ID from DAS
79
80
81 --Get the account structure from Source form
82 l_sql :=
83 'SELECT '
84 ||' nvl(ACC_STR_V.concatenated_segments, '''') acc_str '
85 ||' FROM Ja_Cn_Sub_Acc_Sources_All SOURCE '
86 ||' ,' || l_account_structures_kfv || ' ACC_STR_V '
87 ||'WHERE ACC_STR_V.account_structure_id = SOURCE.ACCOUNTING_STRUCT_ID'
88 ||' AND SOURCE.CHART_OF_ACCOUNTS_ID = ' || l_coa_id --using variable l_coa_id
89 ;
90 EXECUTE IMMEDIATE l_sql into g_account_structure;
91
92 --Validation
93 LOOP
94 l_comma_position := Instr(g_account_structure, ',,', 0, 1);
95 IF l_comma_position > 0
96 THEN
97 g_account_structure := Replace(g_account_structure, ',,',',');
98 END IF;
99
100 EXIT WHEN l_comma_position<=0 ;
101 END LOOP;
102
103
104 --log for debug
105 IF (g_proc_level >= g_dbg_level) THEN
106 FND_LOG.STRING(g_proc_level,
107 g_module_name || '.' || l_procedure_name || '.end',
108 'end procedure');
109 END IF; --( g_proc_level >= g_dbg_level)
110
111 END Query_Account_Structure;
112
113 --==========================================================================
114 -- PROCEDURE NAME:
115 -- Query_System_Options Private
116 --
117 -- DESCRIPTION:
118 -- This procedure is used to fetch data
119 -- from the 'JA_CN_SYSTEM_PARAMETERS_ALL' table
120 --
121 -- PARAMETERS:
122 -- In: p_le_id legal entity ID
123 -- In:P_COA_ID chart of accounts id
124 --
125 -- DESIGN REFERENCES:
126 -- CNAO_Electronic_Accounting_Book_Export.doc
127 --
128 -- CHANGE HISTORY:
129 -- 03/13/2006 Jackey Li Created
130 -- 2006-7-10 Jackey Li Update the way how to get account
131 -- structure due to Bug 5380368
132 --===========================================================================
133 PROCEDURE Query_System_Options(p_le_id IN NUMBER
134 ,P_COA_ID IN NUMBER) IS
135
136 l_procedure_name VARCHAR2(30) := 'Query_System_Options';
137 l_err_msg VARCHAR2(1000) := NULL;
138 l_account_structure_id JA_CN_SUB_ACC_SOURCES_ALL.ACCOUNTING_STRUCT_ID%TYPE;
139
140 JA_CN_MISSING_BOOK_INFO exception;
141 l_msg_miss_book_info varchar2(2000);
142
143 l_le_id JA_CN_SYSTEM_PARAMETERS_ALL.LEGAL_ENTITY_ID%TYPE := p_le_id;
144 l_coa_id JA_CN_SUB_ACC_SOURCES_ALL.Chart_Of_Accounts_Id%TYPE := P_COA_ID;
145 BEGIN
146 --log for debug
147 IF (g_proc_level >= g_dbg_level)
148 THEN
149 FND_LOG.STRING(g_proc_level,
150 g_module_name || '.' || l_procedure_name || '.begin',
151 'begin procedure');
152 END IF; --( g_proc_level >= g_dbg_level)
153
154 -- fetch data from the 'JA_CN_SYSTEM_PARAMETERS_ALL' table
155 SELECT jcsp.BOOK_NAME
156 ,jcsp.COMPANY_NAME
157 ,jcsp.book_num
158 ,jcsp.ORGANIZATION_ID
159 ,jcsp.ENT_QUALITY
160 ,jcsp.ENT_INDUSTRY
161 INTO g_book_name
162 ,g_company_name
163 ,g_book_num
164 ,g_organization_id
165 ,g_ent_quality
166 ,g_ent_industry
167 FROM JA_CN_SYSTEM_PARAMETERS_ALL jcsp
168 WHERE jcsp.legal_entity_id = l_le_id ;
169
170 select sasc.accounting_struct_id
171 into l_account_structure_id
172 from JA_CN_SUB_ACC_SOURCES_ALL sasc
173 where sasc.chart_of_accounts_id=l_coa_id;
174
175 IF g_book_name is null OR g_company_name is null OR
176 g_book_num is null OR g_organization_id is null OR
177 g_ent_quality is null OR g_ent_industry is null OR
178 l_account_structure_id is null
179 THEN
180 RAISE JA_CN_MISSING_BOOK_INFO;
181 END IF;
182
183 /* -- ?? NOT SURE
184 g_account_structure := JA_CN_UTILITY.Fetch_Account_Structure(p_le_id);
185
186 -- to valide the account_structure
187 Parse_Account_Structure;*/
188
189 --log for debug
190 IF (g_proc_level >= g_dbg_level)
191 THEN
192 FND_LOG.STRING(g_proc_level,
193 g_module_name || '.' || l_procedure_name || '.end',
194 'end procedure');
195 END IF; --( g_proc_level >= g_dbg_level)
196
197 EXCEPTION
198 /* WHEN NO_DATA_FOUND THEN
199 RAISE;
200
201 WHEN OTHERS THEN
202 IF (FND_LOG.LEVEL_UNEXPECTED >= g_dbg_level)
203 THEN
204 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
205 g_module_name || l_procedure_name ||
206 '.OTHER_EXCEPTION',
207 SQLCODE || ':' || SQLERRM);
208 END IF;
209 RAISE;*/
210 WHEN JA_CN_MISSING_BOOK_INFO THEN
211 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
212 ,NAME => 'JA_CN_MISSING_BOOK_INFO'
213 );
214 l_msg_miss_book_info := FND_MESSAGE.Get;
215
216 FND_FILE.put_line(FND_FILE.output, l_msg_miss_book_info);
217
218 IF (g_proc_level >= g_dbg_level)
219 THEN
220 FND_LOG.String( g_proc_level,
221 g_module_name || '.' || l_procedure_name||'.JA_CN_MISSING_BOOK_INFO '
222 ,l_msg_miss_book_info);
223 END IF;
224 RAISE;
225 WHEN NO_DATA_FOUND THEN
226 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
227 ,NAME => 'JA_CN_MISSING_BOOK_INFO'
228 );
229 l_msg_miss_book_info := FND_MESSAGE.Get;
230
231 FND_FILE.put_line(FND_FILE.output, l_msg_miss_book_info);
232
233 IF (g_proc_level >= g_dbg_level)
234 THEN
235 FND_LOG.String( g_proc_level,
236 g_module_name || '.' || l_procedure_name||'.JA_CN_MISSING_BOOK_INFO '
237 ,l_msg_miss_book_info);
238 END IF;
239 RAISE;
240 --retcode := 1;
241 --errbuf := l_msg_miss_book_info;
242 WHEN OTHERS THEN
243 IF (g_proc_level >= g_dbg_level)
244 THEN
245 FND_LOG.String( g_proc_level,
246 g_module_name || '.' || l_procedure_name||'.Other_Exception '
247 ,SQLCODE||':'||SQLERRM
248 );
249 END IF; --(l_proc_level >= l_dbg_level)
250 --retcode := 2;
251 --errbuf := SQLCODE||':'||SQLERRM;
252
253 END Query_System_Options;
254
255 --==========================================================================
256 -- PROCEDURE NAME:
257 -- Query_Currency Private
258 --
259 -- DESCRIPTION:
260 -- This procedure is used to fetch currency for the current SOB
261 --
262 -- PARAMETERS:
263 -- In: p_ledger_id legal entity ID
264 --
265 -- DESIGN REFERENCES:
266 -- CNAO_Electronic_Accounting_Book_Export.doc
267 --
268 -- CHANGE HISTORY:
269 -- 03/13/2006 Jackey Li Created
270 --===========================================================================
271 PROCEDURE Query_Currency(P_LEDGER_ID IN NUMBER) IS
272 l_procedure_name VARCHAR2(30) := 'Query_Currency';
273 l_ledger_id NUMBER ;
274
275 BEGIN
276 --log for debug
277 l_ledger_id := P_LEDGER_ID;
278 IF (g_proc_level >= g_dbg_level) THEN
279 FND_LOG.STRING(g_proc_level,
280 g_module_name || '.' || l_procedure_name || '.begin',
281 'begin procedure');
282 END IF; --( g_proc_level >= g_dbg_level)
283
284 -- the sql is used to get functional_currency
285 SELECT fct.NAME
286 INTO g_functional_currency
287 FROM Gl_LEDGERS ledger, FND_CURRENCIES_TL fct
288 WHERE ledger.currency_code = fct.currency_code
289 AND fct.LANGUAGE = userenv('lang')
290 AND ledger.ledger_id = P_LEDGER_ID;
291
292 --log for debug
293 IF (g_proc_level >= g_dbg_level) THEN
294 FND_LOG.STRING(g_proc_level,
295 g_module_name || '.' || l_procedure_name || '.end',
296 'end procedure');
297 END IF; --( g_proc_level >= g_dbg_level)
298
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 IF (FND_LOG.LEVEL_UNEXPECTED >= g_dbg_level)
302 THEN
303 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
304 g_module_name || l_procedure_name ||
305 '.NO_DATA_FOUND',
306 SQLCODE || ':' || SQLERRM);
307 END IF;
308 RAISE;
309 WHEN OTHERS THEN
310 IF (FND_LOG.LEVEL_UNEXPECTED >= g_dbg_level)
311 THEN
312 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
313 g_module_name || l_procedure_name ||
314 '.OTHER_EXCEPTION',
315 SQLCODE || ':' || SQLERRM);
316 END IF;
317 RAISE;
318
319 END Query_Currency;
320
321 --==========================================================================
322 -- PROCEDURE NAME:
323 -- Query_Software_Infor Private
324 --
325 -- DESCRIPTION:
326 -- This procedure is used to fetch information about the name and version
327 -- for this erp software vendor.
328 --
329 -- PARAMETERS:
330 -- N/A
331 --
332 -- DESIGN REFERENCES:
333 -- CNAO_Electronic_Accounting_Book_Export.doc
334 --
335 -- CHANGE HISTORY:
336 -- 03/13/2006 Jackey Li Created
337 --===========================================================================
338 PROCEDURE Query_Software_Infor IS
339 l_procedure_name VARCHAR2(30) := 'Query_Software_Infor';
340 BEGIN
341 --log for debug
342 IF (g_proc_level >= g_dbg_level) THEN
343 FND_LOG.STRING(g_proc_level,
344 g_module_name || '.' || l_procedure_name || '.begin',
345 'begin procedure');
346 END IF; --( g_proc_level >= g_dbg_level)
347
348 g_software_name := 'ORACLE';
349
350 --the newest version, which should be the max of the 3 sub versions
351 SELECT major_version || '.' || minor_version || '.' || tape_version
352 INTO g_software_version
353 FROM ad_releases
354 WHERE tape_version IN
355 (SELECT MAX(tape_version)
356 FROM ad_releases
357 WHERE minor_version IN
358 (SELECT MAX(minor_version)
359 FROM ad_releases
360 WHERE major_version IN (SELECT MAX(major_version) FROM ad_releases)
361 )
362 AND major_version IN (SELECT MAX(major_version) FROM ad_releases)
363 )
364 AND minor_version IN
365 (SELECT MAX(minor_version)
366 FROM ad_releases
367 WHERE major_version IN (SELECT MAX(major_version) FROM ad_releases)
368 )
369 AND major_version IN (SELECT MAX(major_version) FROM ad_releases);
370
371 --log for debug
372 IF (g_proc_level >= g_dbg_level) THEN
373 FND_LOG.STRING(g_proc_level,
374 g_module_name || '.' || l_procedure_name || '.end',
375 'end procedure');
376 END IF; --( g_proc_level >= g_dbg_level)
377
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 -- set default value with '11i'
381 g_software_version := '11i';
382 WHEN TOO_MANY_ROWS THEN
383 -- set default value with '11i'
384 g_software_version := '11i';
385
386 END Query_Software_Infor;
387
388 --==========================================================================
389 -- PROCEDURE NAME:
390 -- Execute_Export Public
391 --
392 -- DESCRIPTION:
393 -- It is a main procedure used to implement the export functionality
394 --
395 -- PARAMETERS:
396 -- In: P_COA_ID chart of accounts ID
397 -- p_le_id legal entity ID
398 -- P_LEDGER_ID Ledger id
399 -- p_fiscal_year fiscal_year
400 --
401 -- DESIGN REFERENCES:
402 -- CNAO_Electronic_Accounting_Book_Export.doc
403 --
404 -- CHANGE HISTORY:
405 -- 03/13/2006 Jackey Li Created
406 -- 04/30/2007 Yucheng Sun Updated
407 --===========================================================================
408 PROCEDURE Execute_Export(P_COA_ID IN NUMBER
409 ,p_le_id IN NUMBER
410 ,P_LEDGER_ID IN NUMBER
411 ,p_fiscal_year IN VARCHAR2) IS
412
413 l_procedure_name VARCHAR2(30) := 'Execute_Export';
414 l_output_string VARCHAR2(1000) := NULL;
415 l_separator VARCHAR2(1) := FND_GLOBAL.Local_Chr(9);
416 l_quotation VARCHAR2(1) := '"';
417 BEGIN
418 --TODO:
419 -- check parameter
420
421 --log for debug
422 IF (g_proc_level >= g_dbg_level) THEN
423 FND_LOG.STRING(g_proc_level,
424 g_module_name || '.' || l_procedure_name || '.begin',
425 'begin procedure');
426 FND_LOG.STRING(g_proc_level,
427 g_module_name || '.' || l_procedure_name ||
428 '.p_le_id is',
429 to_char(p_le_id));
430 FND_LOG.STRING(g_proc_level,
431 g_module_name || '.' || l_procedure_name ||
432 '.p_fiscal_year is',
433 p_fiscal_year);
434 END IF; --( g_proc_level >= g_dbg_level)
435
436 -- to get information from the table JA_CN_SYSTEM_PARAMETERS_ALL
437 Query_System_Options(p_le_id,P_COA_ID);
438
439 -- Get account structure from the Source Form
440 Query_Account_Structure(P_COA_ID);
441
442 -- to get Currency code
443 Query_Currency(P_LEDGER_ID);
444
445 -- to get information about erp vendor and its version
446 Query_Software_Infor;
447
448 l_output_string := l_quotation || g_book_num || l_quotation ||
449 l_separator || l_quotation || g_book_name ||
450 l_quotation || l_separator || l_quotation ||
451 g_company_name || l_quotation || l_separator ||
452 l_quotation ||
453 g_organization_id || l_quotation || l_separator ||
454 l_quotation || g_ent_quality || l_quotation ||
455 l_separator || l_quotation || g_ent_industry ||
456 l_quotation || l_separator || l_quotation ||
457 g_software_name || l_quotation || l_separator ||
458 l_quotation || g_software_version || l_quotation ||
459 l_separator || l_quotation || p_fiscal_year ||
460 l_quotation || l_separator || l_quotation ||
461 g_functional_currency || l_quotation || l_separator ||
462 l_quotation || g_account_structure || l_quotation;
463
464 FND_FILE.PUT_LINE(Fnd_File.OUTPUT,
465 l_output_string
466 );
467 --Dbms_Output.put_line('SYC TEST & TEST AND TEST1');
468
469 --log for debug
470 IF (g_proc_level >= g_dbg_level) THEN
471 FND_LOG.STRING(g_proc_level,
472 g_module_name || '.' || l_procedure_name || '.end',
473 'end procedure');
474 END IF; --( g_proc_level >= g_dbg_level)
475
476 END Execute_Export;
477
478 END JA_CN_EAB_EXPORT_PKG;
479
480
481
482
483