[Home] [Help]
PACKAGE BODY: APPS.JA_CN_SI_BANK_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_SI_BANK_EXPORT_PKG AS
2 --$Header: JACNSBEB.pls 120.0.12020000.3 2013/04/22 08:29:12 chongwan noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNSBEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export shared information |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Export_Shared_Information |
16 --| Submit_Request |
17 --| HISTORY | |
18 --| 16-Oct-2012 Jar Wang Created |
19 --+======================================================================*/
20
21 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_SI_BANK_EXPORT_PKG';
22
23 --==========================================================================
24 -- PROCEDURE NAME:
25 -- Export_Shared_Information public
26 --
27 -- DESCRIPTION:
28 -- This procedure generate shared information for banking
29 --
30 -- PARAMETERS:
31 -- In: pn_coa_id NUMBER
32 -- In: pn_ledger_id NUMBER
33 -- In: pn_legal_entity_id NUMBER
34 -- In: pv_bsv NUMBER
35 -- In: pn_accounting_year NUMBER
36 --
37 -- CHANGE HISTORY:
38 -- Nov-20-2012 Jar Wang Created
39 --===========================================================================
40 PROCEDURE Export_Shared_Information(pv_errbuf OUT NOCOPY VARCHAR2,
41 pv_retcode OUT NOCOPY VARCHAR2,
42 pn_coa_id IN NUMBER,
43 pn_ledger_id IN NUMBER,
44 pn_legal_entity_id IN NUMBER,
45 pv_bsv IN VARCHAR2,
46 pv_accounting_year IN VARCHAR2) IS
47 lv_procedure_name VARCHAR2(40) := 'Export_Shared_Information';
48 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
49 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
50 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
51 lv_gl_element VARCHAR2(1000);
52
53 BEGIN
54 --logging for debug
55 IF (ln_proc_level >= ln_dbg_level) THEN
56 FND_LOG.STRING(ln_proc_level,
57 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
58 '.begin',
59 'Enter procedure');
60
61 -- logging the parameters
62 FND_LOG.STRING(ln_proc_level,
63 lv_procedure_name || '.parameters',
64 'pn_ledger_id=' || pn_ledger_id || ',' ||
65 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
66 'pv_bsv=' || pv_bsv || ',' ||
67 'pv_accounting_year=' || pv_accounting_year || ',' ||
68 'pn_coa_id=' || pn_coa_id);
69 END IF; --l_proc_level>=l_dbg_level
70
71 FND_FILE.put_line(FND_FILE.log,
72 lv_procedure_name || '.parameters:' ||
73 'pn_ledger_id=' || pn_ledger_id || ',' ||
74 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
75 'pv_bsv=' || pv_bsv || ',' ||
76 'pv_accounting_year=' || pv_accounting_year || ',' ||
77 'pn_coa_id=' || pn_coa_id);
78
79 -- export the header
80 -- here need to change
81 FND_FILE.put_line(FND_FILE.output,
82 '<?xml version="1.0" encoding="' ||
83 JA_CN_UTILITY.Get_XML_Encoding || '"?>');
84 /*lv_gl_element := '<' || Ja_Cn_Utility.Get_XML_Tag('SHARED_INFORMATION') ||
85 ' xsi:schemaLocation="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema ' ||
86 Ja_Cn_Utility.Get_XML_Tag('SHARED_INFORMATION') ||
87 '.xsd" xmlns:' || Ja_Cn_Utility.Get_XML_Tag('BANK') ||
88 '="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema"
89 xmlns="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">';
90 */
91 --added by jar.wang for bug 16655327
92 /* lv_gl_element := '<' || Ja_Cn_Utility.Get_XML_Tag('SHARED_INFORMATION') ||
93 ' xmlns="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/Bank/XMLSchema"' ||
94 ' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"'||
95 ' xmlns:'||Ja_Cn_Utility.Get_XML_Tag('BANK')||'="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/Bank/XMLSchema"'||
96 ' xsi:schemaLocation="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/Bank/XMLSchema '||
97 Ja_Cn_Utility.Get_XML_Tag('SHARED_INFORMATION') ||'.xsd">';*/
98 lv_gl_element := '<' || Ja_Cn_Utility.Get_XML_Tag('SHARED_INFORMATION') ||' xsi:schemaLocation="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/Bank/XMLSchema '||
99 Ja_Cn_Utility.Get_XML_Tag('SHARED_INFORMATION') ||'.xsd"'||
100 ' xmlns:'||Ja_Cn_Utility.Get_XML_Tag('BANK')||'="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/Bank/XMLSchema"'||
101 ' xmlns="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/Bank/XMLSchema"' ||
102 ' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">';
103 FND_FILE.put_line(FND_FILE.output, lv_gl_element);
104
105 --begin basic info
106 Ja_Cn_Utility.Add_Sub_Root_Node('BASIC_INFO', Ja_Cn_Utility.GV_TAG_TYPE_START);
107
108 --Add banking base info
109 Ja_Cn_Utility.Add_Sub_Root_Node('ACC_UNIT_INFO_FILE', Ja_Cn_Utility.GV_TAG_TYPE_START);
110 JA_CN_SI_BANK_DET_PKG.Add_Parent_BSV(pn_legal_entity_id => pn_legal_entity_id,
111 pn_ledger_id => pn_ledger_id,
112 pv_bsv => pv_bsv,
113 pn_accounting_year => TO_NUMBER(pv_accounting_year),
114 pn_branch =>1);
115
116 Ja_Cn_Utility.Add_Sub_Root_Node('ACC_UNIT_INFO_FILE', Ja_Cn_Utility.GV_TAG_TYPE_END);
117
118
119 --Add merchant bank info
120 Ja_Cn_Utility.Add_Sub_Root_Node('COMMERCIAL_BANK_ORG_FILE', Ja_Cn_Utility.GV_TAG_TYPE_START);
121 JA_CN_SI_BANK_DET_PKG.Add_Parent_BSV(pn_legal_entity_id => pn_legal_entity_id,
122 pn_ledger_id => pn_ledger_id,
123 pv_bsv => pv_bsv,
124 pn_accounting_year => TO_NUMBER(pv_accounting_year),
125 pn_branch =>2);
126 Ja_Cn_Utility.Add_Sub_Root_Node('COMMERCIAL_BANK_ORG_FILE', Ja_Cn_Utility.GV_TAG_TYPE_END);
127 --Add currency
128 JA_CN_SI_BANK_DET_PKG.Add_Parent_BSV(pn_legal_entity_id => pn_legal_entity_id,
129 pn_ledger_id => pn_ledger_id,
130 pv_bsv => pv_bsv,
131 pn_accounting_year => TO_NUMBER(pv_accounting_year),
132 pn_branch =>3);
133 JA_CN_SI_BANK_DET_PKG.Add_Accounting_Currency;
134
135 --end basic info
136 Ja_Cn_Utility.Add_Sub_Root_Node('BASIC_INFO', Ja_Cn_Utility.GV_TAG_TYPE_END);
137 Ja_Cn_Utility.Add_Sub_Root_Node('FINANCE_SYSTEM', Ja_Cn_Utility.GV_TAG_TYPE_START);
138 --Add period
139 JA_CN_SI_BANK_DET_PKG.Add_Accounting_Periods(pn_legal_entity_id => pn_legal_entity_id,
140 pn_ledger_id => pn_ledger_id,
141 --pv_bsv => pv_bsv,
142 pn_accounting_year => TO_NUMBER(pv_accounting_year));
143
144 --Add accounting code
145 JA_CN_SI_BANK_DET_PKG.Add_Accounting_Code_Rule(pn_legal_entity_id => pn_legal_entity_id,
146 pn_ledger_id => pn_ledger_id,
147 pv_bsv => pv_bsv,
148 pn_accounting_year => TO_NUMBER(pv_accounting_year));
149 --Add inner account
150 JA_CN_SI_BANK_DET_PKG.Add_Inner_Sheet_Account(pn_legal_entity_id ,
151 pn_ledger_id ,
152 pv_bsv ,
153 TO_NUMBER(pv_accounting_year) ) ;
154
155 --Add extra account
156 JA_CN_SI_BANK_DET_PKG.Add_Extra_Sheet_Account(pn_legal_entity_id ,
157 pn_ledger_id ,
158 pv_bsv ,
159 TO_NUMBER(pv_accounting_year) ) ;
160
161 Ja_Cn_Utility.Add_Sub_Root_Node('FINANCE_SYSTEM', Ja_Cn_Utility.GV_TAG_TYPE_END);
162
163 FND_FILE.put_line(FND_FILE.output, Ja_Cn_Utility.Get_XML_Tag('SHARED_INFORMATION', Ja_Cn_Utility.GV_TAG_TYPE_END));
164
165 --logging for debug
166 IF (ln_proc_level >= ln_dbg_level) THEN
167 FND_LOG.STRING(ln_proc_level,
168 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
169 'Exit procedure');
170 END IF; -- (ln_proc_level>=ln_dbg_level)
171 EXCEPTION
172 WHEN OTHERS THEN
173 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
174 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
175 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
176 '.Other_Exception ',
177 SQLCODE || SQLERRM);
178 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
179 FND_FILE.put_line(FND_FILE.log,
180 lv_procedure_name || SQLCODE || SQLERRM);
181 pv_retcode := 2;
182 raise;
183 END Export_Shared_Information;
184
185 --==========================================================================
186 -- PROCEDURE NAME:
187 -- Submit_Request public
188 --
189 -- DESCRIPTION:
190 -- This procedure convert shared information
191 --
192 -- PARAMETERS:
193 -- pn_coa_id IN NUMBER,
194 -- pn_ledger_id IN NUMBER,
195 -- pn_legal_entity_id IN NUMBER,
196 -- pv_bsv IN VARCHAR2,
197 -- pv_accounting_year IN VARCHAR2,
198 -- pv_source_char_set IN VARCHAR2,
199 -- pv_dest_char_set IN VARCHAR
200 --
201 -- CHANGE HISTORY:
202 -- Nov-22-2012 Jar Wang Created
203 --===========================================================================
204 PROCEDURE Submit_Request(pv_errbuf OUT NOCOPY VARCHAR2,
205 pv_retcode OUT NOCOPY VARCHAR2,
206 pn_coa_id IN NUMBER,
207 pn_ledger_id IN NUMBER,
208 pn_legal_entity_id IN NUMBER,
209 pv_bsv IN VARCHAR2,
210 pv_accounting_year IN VARCHAR2,
211 pv_source_char_set IN VARCHAR2,
212 pv_dest_char_set IN VARCHAR2) IS
213 NO_DATA EXCEPTION;
214
215 lv_procedure_name VARCHAR2(40) := 'Submit_Request';
216 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
217 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
218 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
219 lv_error_status BOOLEAN;
220
221 ln_reqid_gle NUMBER;
222 lv_gle_req_phase fnd_lookup_values.meaning%TYPE;
223 lv_gle_req_status fnd_lookup_values.meaning%TYPE;
224 lv_gle_req_status_code fnd_lookup_values.lookup_code%TYPE;
225
226 lv_error_flag VARCHAR2(1);
227 ln_waiting_interval NUMBER := 10;
228 lv_dev_phase VARCHAR2(100);
229 lv_dev_status VARCHAR2(100);
230 lv_message VARCHAR2(1000);
231
232 ln_reqid_cvt NUMBER;
233 lv_cvt_req_status VARCHAR2(100);
234
235 ln_reqid_chg NUMBER;
236 lv_chg_req_status VARCHAR2(100);
237
238 lv_output_file_name VARCHAR2(100);
239 l_bsv_le BOOLEAN;
240 l_mes VARCHAR2(2000);
241 l_das_bsv pls_integer;
242 l_access_set PLS_INTEGER:=FND_PROFILE.VALUE('GL_ACCESS_SET_ID');
243 BEGIN
244 --logging for debug
245 IF (ln_proc_level >= ln_dbg_level) THEN
246 FND_LOG.STRING(ln_proc_level,
247 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
248 '.begin',
249 'Enter procedure');
250
251 -- logging the parameters
252 FND_LOG.STRING(ln_proc_level,
253 lv_procedure_name || '.parameters',
254 'pn_ledger_id=' || pn_ledger_id || ',' ||
255 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
256 'pv_bsv=' || pv_bsv || ',' ||
257 'pv_accounting_year=' || pv_accounting_year || ',' ||
258 'pv_source_char_set=' || pv_source_char_set || ',' ||
259 'pv_dest_char_set=' || pv_dest_char_set || ',' ||
260 'pn_coa_id=' || pn_coa_id);
261 END IF; --l_proc_level>=l_dbg_level
262
263 FND_FILE.put_line(FND_FILE.log,
264 lv_procedure_name || '.parameters:' ||
265 'pn_ledger_id=' || pn_ledger_id || ',' ||
266 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
267 'pv_bsv=' || pv_bsv || ',' ||
268 'pv_accounting_year=' || pv_accounting_year || ',' ||
269 'pv_source_char_set=' || pv_source_char_set || ',' ||
270 'pv_dest_char_set=' || pv_dest_char_set || ',' ||
271 'pn_coa_id=' || pn_coa_id);
272
273 --Step1: Validate the bsv whether is in the legal entity
274 l_bsv_le := ja_cn_sysops_bank_pkg.check_le_bsv(p_ledger => pn_ledger_id,
275 p_legal_entity => pn_legal_entity_id,
276 p_bsv => pv_bsv);
277 IF l_bsv_le <> true THEN
278 pv_retcode :=1;
279 FND_MESSAGE.SET_NAME('JA','JA_CN_BSV_VALIDATION') ;
280 l_mes := FND_MESSAGE.GET;
281 FND_FILE.PUT_LINE(FND_FILE.LOG,GV_MODULE_PREFIX || '.' || lv_procedure_name||':'||l_mes);
282 --APP_EXCEPTION.RAISE_EXCEPTION ;
283 RETURN;
284 ELSE
285 l_das_bsv :=ja_cn_sysops_bank_pkg.Check_DAS_BSV(p_ledger => pn_ledger_id,
286 p_das => l_access_set,
287 p_bsv => pv_bsv);
288
289 IF l_das_bsv =-2 THEN
290 FND_MESSAGE.SET_NAME('FND','FLEX-EXCLUDED BY SEC. RULE') ;
291 FND_MESSAGE.SET_TOKEN ('MESSAGE',pv_bsv);
292 l_mes := FND_MESSAGE.GET;
293 FND_FILE.PUT_LINE(FND_FILE.LOG,'Waring:'||l_mes);
294 pv_retcode :=1;
295 RETURN;
296 ELSIF l_das_bsv =-1 THEN
297 FND_MESSAGE.SET_NAME('SQLGL','GLCORE_NO_ACCESS') ;
298 l_mes := FND_MESSAGE.GET;
299 FND_FILE.PUT_LINE(FND_FILE.LOG,'Waring:'||l_mes);
300 pv_retcode :=1;
301 RETURN;
302 END IF;
303 END IF;
304
305 --Step2: Submit SI Export program
306 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 2: Submit SI Export program');
307 ln_reqid_gle := FND_REQUEST.Submit_Request(application => 'JA',
308 program => 'JACNSIBGP',
309 argument1 => pn_coa_id,
310 argument2 => pn_ledger_id,
311 argument3 => pn_legal_entity_id,
312 argument4 => pv_bsv,
313 argument5 => pv_accounting_year);
314 COMMIT;
315 --Waiting for the 'Export Report for Shared Information' completed
316 --get its status
317
318 IF ln_reqid_gle <> 0 THEN
319
320 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_gle,
321 interval => ln_waiting_interval,
322 phase => lv_gle_req_phase,
323 status => lv_gle_req_status,
324 dev_phase => lv_dev_phase,
325 dev_status => lv_dev_status,
326 message => lv_message) THEN
327 --To get lookup code for current status
328 SELECT lookup_code
329 INTO lv_gle_req_status_code
330 FROM fnd_lookup_values
331 WHERE lookup_type = 'CP_STATUS_CODE'
332 AND view_application_id = 0
333 AND security_group_id = 0
334 AND meaning = lv_gle_req_status
335 AND enabled_flag = 'Y'
336 AND language = USERENV('LANG');
337
338 --Completed with Normal
339 IF lv_gle_req_status_code = 'C' THEN
340
341 --Step3: Submit characrter set conversion program
342 --to convert charaterset of output file
343 FND_FILE.PUT_LINE(FND_FILE.LOG,
344 'Step 3: Submit Characrter Set Conversion program');
345 JA_CN_SYSOPS_BANK_PKG.Submit_XML_Charset_Conversion(p_xml_request_id => ln_reqid_gle,
346 p_source_charset => JA_CN_UTILITY.Get_XML_Encoding,
347 p_destination_charset => pv_dest_char_set,
348 p_source_separator => NULL,
349 x_charset_request_id => ln_reqid_cvt,
350 x_result_flag => lv_cvt_req_status);
351 IF lv_cvt_req_status = 'Success' THEN
352 --Step4: Submit "Change File Name" concurrent program
353 --to change name of output file
354 FND_FILE.PUT_LINE(FND_FILE.LOG,
355 'Step 4: Submit File Name Renaming program');
356 lv_output_file_name := Ja_Cn_Utility.Generate_File_name(pv_module_name => 'SI',
357 pv_accounting_year => pv_accounting_year,
358 pv_ledger_id => pn_ledger_id,
359 pv_from_period => null,
360 pv_to_period => null);
361 --logging the variables
362 IF (ln_statement_level >= ln_dbg_level) THEN
363 FND_LOG.STRING(ln_statement_level,
364 lv_procedure_name,
365 'lv_output_file_name:' || lv_output_file_name);
366 END IF; --(ln_statement_level >= ln_dbg_level)
367 FND_FILE.put_line(FND_FILE.log,
368 lv_procedure_name || '.variable:' ||
369 'lv_output_file_name =' ||
370 lv_output_file_name);
371
372 JA_CN_UTILITY.Change_Output_Filename(p_xml_request_id => ln_reqid_gle,
373 p_destination_charset => pv_dest_char_set,
374 p_destination_filename => lv_output_file_name,
375 x_filename_request_id => ln_reqid_chg,
376 x_result_flag => lv_chg_req_status);
377 IF lv_chg_req_status = 'Success' THEN
378 NULL;
379 ELSIF lv_chg_req_status = 'Warning' THEN
380 lv_error_flag := 'W';
381 ELSIF lv_chg_req_status = 'Error' THEN
382 lv_error_flag := 'E';
383 END IF; --l_chg_req_status='Success'
384
385 ELSIF lv_cvt_req_status = 'Warning' THEN
386 lv_error_flag := 'W';
387 ELSIF lv_cvt_req_status = 'Error' THEN
388 lv_error_flag := 'E';
389 END IF; --l_cvt_req_status='Success'
390
391 --Completed with 'Warning'
392 ELSIF lv_gle_req_status_code = 'G' THEN
393 lv_error_flag := 'W';
394 --Completed with 'Error'
395 ELSIF lv_gle_req_status_code = 'E' THEN
396 lv_error_flag := 'E';
397 END IF; --FND_CONCURRENT.Wait_For_Request(request_id => l_reqid_comb
398 ELSE
399 lv_error_flag := 'E';
400 END IF; --FND_CONCURRENT.Wait_For_Request
401 ELSE
402 lv_error_flag := 'E';
403 END IF; --ln_reqid_gle<>0
404
405 --If any of above concurrent porgrams is Warning/Failed, set current SI export
406 --program to status 'Warning'/'Error' accordingly.
407 IF lv_error_flag = 'W' THEN
408 lv_error_status := FND_CONCURRENT.Set_Completion_Status(status => 'WARNING',
409 message => '');
410 ELSIF lv_error_flag = 'E' THEN
411 lv_error_status := FND_CONCURRENT.Set_Completion_Status(status => 'ERROR',
412 message => '');
413 END IF; --lv_error_flag='W'
414
415 --logging for debug
416 IF (ln_proc_level >= ln_dbg_level) THEN
417 FND_LOG.STRING(ln_proc_level,
418 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
419 'Exit procedure');
420 END IF; -- (ln_proc_level>=ln_dbg_level)
421
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
426 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
427 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
428 '.Other_Exception ',
429 SQLCODE || SQLERRM);
430 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
431 FND_FILE.put_line(FND_FILE.log,
432 lv_procedure_name || SQLCODE || SQLERRM);
433 IF pv_retcode IS NULL THEN
434 pv_retcode := 2;
435 END IF;
436 RAISE;
437 END Submit_Request;
438
439 END JA_CN_SI_BANK_EXPORT_PKG;
440
441