[Home] [Help]
PACKAGE BODY: APPS.JA_CN_SI_EXPORT_PROG
Source
1 PACKAGE BODY JA_CN_SI_EXPORT_PROG AS
2 --$Header: JACNSIPB.pls 120.3 2010/04/06 07:48:59 jianliu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNSIPB.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 --| |
18 --| |
19 --| HISTORY |
20 --| 03/17/2010 Jason Liu Created |
21 --| 03/18/2010 Qingyi Wang Modify the paramters and service logic |
22 --+======================================================================*/
23
24 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_SI_EXPORT_PROG';
25
26 --==========================================================================
27 -- PROCEDURE NAME:
28 --
29 -- Export_Shared_Information Public
30 --
31 -- DESCRIPTION:
32 --
33 -- This procedure is to export shared information
34 --
35 -- PARAMETERS:
36 -- Out: pv_errbuf NOCOPY VARCHAR2
37 -- pv_retcode NOCOPY VARCHAR2
38 -- In: pn_coa_id NUMBER identifier of chart of account
39 -- pn_legal_entity_id NUMBER identifier of legal entity
40 -- pn_ledger_id NUMBER identifier of ledger
41 -- pv_accounting_year VARCHAR2 accounting year
42 --
43 --
44 --
45 -- DESIGN REFERENCES:
46 -- CNAO_V2_SI_TD.doc
47 --
48 -- CHANGE HISTORY:
49 --
50 -- 17-MAR-2010 Jason Liu created
51 -- 18-MAR-2010 Qingyi Wang modify the paramters and service logic
52 --==========================================================================
53
54 PROCEDURE Export_Shared_Information
55 ( pv_errbuf OUT NOCOPY VARCHAR2
56 , pv_retcode OUT NOCOPY VARCHAR2
57 , pn_coa_id IN NUMBER
58 , pn_legal_entity_id IN NUMBER
59 , pn_ledger_id IN NUMBER
60 , pv_accounting_year IN VARCHAR2
61 )
62 IS
63 lv_procedure_name VARCHAR2(40) := 'Export_Shared_Information';
64 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
65 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
66 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
67 lv_gl_element VARCHAR2(1000);
68
69 BEGIN
70 --logging for debug
71 IF (ln_proc_level>=ln_dbg_level)
72 THEN
73 FND_LOG.STRING( ln_proc_level
74 , GV_MODULE_PREFIX ||'.' || lv_procedure_name ||
75 '.begin'
76 , 'Enter procedure'
77 );
78
79 -- logging the parameters
80 FND_LOG.STRING(ln_proc_level,
81 lv_procedure_name ||
82 '.parameters',
83 'pn_legal_entity_id=' || pn_legal_entity_id|| ',' ||
84 'pn_ledger_id=' || pn_ledger_id || ',' ||
85 'pv_accounting_year=' || pv_accounting_year || ',' ||
86 'pn_coa_id=' || pn_coa_id);
87 END IF; --l_proc_level>=l_dbg_level
88
89 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
90 '.parameters:' ||
91 'pn_legal_entity_id=' || pn_legal_entity_id|| ',' ||
92 'pn_ledger_id=' || pn_ledger_id || ',' ||
93 'pv_accounting_year=' || pv_accounting_year || ',' ||
94 'pn_coa_id=' || pn_coa_id);
95
96 -- export the header
97 -- here need to change
98 FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding="'||JA_CN_UTILITY.Get_XML_Encoding||'"?>');
99 lv_gl_element := '<' || Ja_Cn_Utility.Get_XML_Tag('SHARED_INFORMATION')
100 || ' xsi:schemaLocation="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema '
101 || Ja_Cn_Utility.Get_XML_Tag('SHARED_INFORMATION')
102 || '.xsd" xmlns:'
103 || Ja_Cn_Utility.Get_XML_Tag('SOE')
104 || '="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema"
105 xmlns="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/SOE/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">';
106 FND_FILE.put_line(FND_FILE.output, lv_gl_element);
107
108 -- call the procedures to export the reports seperately here begin
109 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log( pv_step_number => '2.1'
110 , pv_report_name => 'ELECTRONIC_ACCOUNTING_BOOK');
111 JA_CN_GL_EAB_EXPORT_PKG.Add_Electronic_Accounting_Book( pn_legal_entity_id => pn_legal_entity_id
112 , pn_ledger_id => pn_ledger_id
113 , pn_accounting_year => TO_NUMBER(pv_accounting_year)
114 );
115 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log( pv_step_number => '2.2'
116 , pv_report_name => 'ACCOUNTING_PERIOD');
117 JA_CN_ACP_EXPORT_PKG.Add_Accounting_Periods( pn_legal_entity_id => pn_legal_entity_id
118 , pn_ledger_id => pn_ledger_id
119 , pn_accounting_year => TO_NUMBER(pv_accounting_year)
120 );
121 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log( pv_step_number => '2.3'
122 , pv_report_name => 'JOURNAL_CATEGORY');
123 JA_CN_JC_EXPORT_PKG.Add_Journal_Categories( pn_legal_entity_id => pn_legal_entity_id
124 , pn_ledger_id => pn_ledger_id
125 , pn_accounting_year => TO_NUMBER(pv_accounting_year)
126 );
127 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log( pv_step_number => '2.4'
128 , pv_report_name => 'EXCHANGE_RATE_TYPE');
129 JA_CN_ERT_EXPORT_PKG.Add_Exchange_Rate_Types( pn_legal_entity_id => pn_legal_entity_id
130 , pn_ledger_id => pn_ledger_id
131 , pn_accounting_year => TO_NUMBER(pv_accounting_year)
132 );
133 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log( pv_step_number => '2.5'
134 , pv_report_name => 'CURRENCY');
135 JA_CN_CUR_EXPORT_PKG.Add_Currencies( pn_legal_entity_id => pn_legal_entity_id
136 , pn_ledger_id => pn_ledger_id
137 , pn_accounting_year => TO_NUMBER(pv_accounting_year)
138 );
139 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log( pv_step_number => '2.6'
140 , pv_report_name => 'SETTLEMENT_METHOD');
141 JA_CN_SM_EXPORT_PKG.Add_Settlement_Method( pn_ledger_id => pn_ledger_id
142 , pn_legal_entity_id => pn_legal_entity_id
143 , pv_accounting_year => pv_accounting_year
144 );
145 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log( pv_step_number => '2.7'
146 , pv_report_name => 'DEPARTMENT_RECORDS');
147 JA_CN_HRMS_EXPORT_PKG.Add_Department_Records( pn_legal_entity_id => pn_legal_entity_id
148 , pn_ledger_id => pn_ledger_id
149 , pv_accounting_year => pv_accounting_year
150 );
151 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log( pv_step_number => '2.8'
152 , pv_report_name => 'EMPLOYEE_RECORDS');
153 JA_CN_HRMS_EXPORT_PKG.Add_Employee_Records( pn_legal_entity_id => pn_legal_entity_id
154 , pn_ledger_id => pn_ledger_id
155 , pv_accounting_year => pv_accounting_year
156 );
157 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log( pv_step_number => '2.9'
158 , pv_report_name => 'SUPPLIER_RECORD');
159 JA_CN_SR_EXPORT_PKG.Add_Supplier( pn_legal_entity_id => pn_legal_entity_id
160 , pv_accounting_year => pv_accounting_year
161 );
162 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log( pv_step_number => '2.10'
163 , pv_report_name => 'CUSTOMER_RECORD');
164 JA_CN_CR_EXPORT_PKG.Add_Customer(pn_legal_entity_id => pn_legal_entity_id
165 ,pv_accounting_year => pv_accounting_year);
166 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log( pv_step_number => '2.11'
167 , pv_report_name => 'USER_DEFINED_RECORDS');
168 JA_CN_UDR_EXPORT.Add_User_Defined_Records( pn_coa_id => pn_coa_id
169 , pn_le_id => pn_legal_entity_id
170 , pn_ledger_id => pn_ledger_id
171 , pv_accounting_year => pv_accounting_year
172 );
173 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log( pv_step_number => '2.12'
174 , pv_report_name => 'USER_DEFINED_RECORD_VALUE');
175 JA_CN_UDRV_EXPORT.Add_User_Defined_Record_Value( pn_coa_id => pn_coa_id
176 , pn_le_id => pn_legal_entity_id
177 , pn_ledger_id => pn_ledger_id
178 , pv_accounting_year => pv_accounting_year
179 );
180 -- call the procedures to export the reports seperately here end
181
182 FND_FILE.put_line( FND_FILE.output
183 , Ja_Cn_Utility.Get_XML_Tag('SHARED_INFORMATION', Ja_Cn_Utility.GV_TAG_TYPE_END));
184
185 --logging for debug
186 IF (ln_proc_level >= ln_dbg_level)
187 THEN
188 FND_LOG.STRING( ln_proc_level
189 , GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
190 'Exit procedure');
191 END IF; -- (ln_proc_level>=ln_dbg_level)
192 EXCEPTION
193 WHEN OTHERS THEN
194 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
195 THEN
196 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
197 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
198 '.Other_Exception ',
199 SQLCODE || SQLERRM);
200 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
201 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
202 pv_retcode := 2;
203 END Export_Shared_Information;
204
205
206 --==========================================================================
207 -- PROCEDURE NAME:
208 --
209 -- Submit_Request Public
210 --
211 -- DESCRIPTION:
212 --
213 -- This procedure is to export report for shared information
214 --
215 -- PARAMETERS:
216 -- Out: pv_errbuf NOCOPY VARCHAR2
217 -- pv_retcode NOCOPY VARCHAR2
218 -- In: pn_coa_id NUMBER identifier of chart of account
219 -- pn_legal_entity_id NUMBER identifier of legal entity
220 -- pn_ledger_id NUMBER identifier of ledger
221 -- pv_accounting_year VARCHAR2 accounting year
222 -- pv_source_char_set VARCHAR2 source character set
223 -- pv_dest_char_set VARCHAR2 destination character set
224 --
225 -- DESIGN REFERENCES:
226 -- GL_Shujuan.doc
227 --
228 -- CHANGE HISTORY:
229 -- 01-Mar-2010 Qingyi Wang created
230 --==========================================================================
231
232 PROCEDURE Submit_Request
233 ( pv_errbuf OUT NOCOPY VARCHAR2
234 , pv_retcode OUT NOCOPY VARCHAR2
235 , pn_coa_id IN NUMBER
236 , pn_legal_entity_id IN NUMBER
237 , pn_ledger_id IN NUMBER
238 , pv_accounting_year IN VARCHAR2
239 , pv_source_char_set IN VARCHAR2
240 , pv_dest_char_set IN VARCHAR2
241 )
242 IS
243 NO_DATA EXCEPTION;
244
245 lv_procedure_name VARCHAR2(40) := 'Export_SI';
246 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
247 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
248 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
249 lv_error_status BOOLEAN;
250
251 ln_reqid_gle NUMBER;
252 lv_gle_req_phase fnd_lookup_values.meaning%TYPE;
253 lv_gle_req_status fnd_lookup_values.meaning%TYPE;
254 lv_gle_req_status_code fnd_lookup_values.lookup_code%TYPE;
255
256 lv_error_flag VARCHAR2(1);
257
258 ln_waiting_interval NUMBER :=10;
259 lv_dev_phase VARCHAR2(100);
260 lv_dev_status VARCHAR2(100);
261 lv_message VARCHAR2(1000);
262
263 ln_reqid_cvt NUMBER;
264 lv_cvt_req_status VARCHAR2(100);
265
266 ln_reqid_chg NUMBER;
267 lv_chg_req_status VARCHAR2(100);
268
269 lv_output_file_name VARCHAR2(100);
270
271 BEGIN
272 --logging for debug
273 IF (ln_proc_level>=ln_dbg_level)
274 THEN
275 FND_LOG.STRING( ln_proc_level
276 , GV_MODULE_PREFIX ||'.' || lv_procedure_name ||
277 '.begin'
278 , 'Enter procedure'
279 );
280
281 -- logging the parameters
282 FND_LOG.STRING(ln_proc_level,
283 lv_procedure_name ||
284 '.parameters',
285 'pn_legal_entity_id=' || pn_legal_entity_id|| ',' ||
286 'pn_ledger_id=' || pn_ledger_id || ',' ||
287 'pv_accounting_year=' || pv_accounting_year || ',' ||
288 'pv_source_char_set=' || pv_source_char_set || ',' ||
289 'pv_dest_char_set=' || pv_dest_char_set || ',' ||
290 'pn_coa_id=' || pn_coa_id);
291 END IF; --l_proc_level>=l_dbg_level
292
293 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
294 '.parameters:' ||
295 'pn_legal_entity_id=' || pn_legal_entity_id|| ',' ||
296 'pn_ledger_id=' || pn_ledger_id || ',' ||
297 'pv_accounting_year=' || pv_accounting_year || ',' ||
298 'pv_source_char_set=' || pv_source_char_set || ',' ||
299 'pv_dest_char_set=' || pv_dest_char_set || ',' ||
300 'pn_coa_id=' || pn_coa_id);
301
302 --Step1: Check system option
303 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 1: Check System Option');
304 IF NOT(Ja_Cn_Utility.Check_System_Option(pn_legal_entity_id))
305 THEN
306 pv_retcode := 1;
307 pv_errbuf := '';
308 RETURN;
309 END IF; --(Ja_Cn_Utility.Check_System_Option(pn_legal_entity_id))
310
311 --Step2: Submit SI Export program
312 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 2: Submit SI Export program');
313 ln_reqid_gle:=FND_REQUEST.Submit_Request( application => 'JA'
314 , program => 'JACNSIGP'
315 , argument1 => pn_coa_id
316 , argument2 => pn_legal_entity_id
317 , argument3 => pn_ledger_id
318 , argument4 => pv_accounting_year
319 );
320 COMMIT;
321 --Waiting for the 'Export Report for Shared Information' completed
322 --get its status
323
324 IF ln_reqid_gle<>0
325 THEN
326
327 IF FND_CONCURRENT.Wait_For_Request( request_id => ln_reqid_gle
328 , interval => ln_waiting_interval
329 , phase => lv_gle_req_phase
330 , status => lv_gle_req_status
331 , dev_phase => lv_dev_phase
332 , dev_status => lv_dev_status
333 , message => lv_message
334 )
335 THEN
336 --To get lookup code for current status
337 SELECT
338 lookup_code
339 INTO
340 lv_gle_req_status_code
341 FROM
342 fnd_lookup_values
343 WHERE lookup_type = 'CP_STATUS_CODE'
344 AND view_application_id=0
345 AND security_group_id=0
346 AND meaning=lv_gle_req_status
347 AND enabled_flag='Y'
348 AND language = USERENV('LANG');
349
350 --Completed with Normal
351 IF lv_gle_req_status_code='C'
352 THEN
353
354 --Step3: Submit characrter set conversion program
355 --to convert charaterset of output file
356 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 3: Submit Characrter Set Conversion program');
357 Ja_Cn_Utility.Submit_XML_Charset_Conversion( p_xml_request_id => ln_reqid_gle
358 , p_source_charset => JA_CN_UTILITY.Get_XML_Encoding
359 , p_destination_charset => pv_dest_char_set
360 , p_source_separator => NULL
361 , x_charset_request_id => ln_reqid_cvt
362 , x_result_flag => lv_cvt_req_status
363 );
364 IF lv_cvt_req_status='Success'
365 THEN
366 --Step4: Submit "Change File Name" concurrent program
367 --to change name of output file
368 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 4: Submit File Name Renaming program');
369 lv_output_file_name := Ja_Cn_Utility.Generate_File_name( pv_module_name => 'SI'
370 , pv_accounting_year => pv_accounting_year
371 , pv_ledger_id => pn_ledger_id
372 , pv_from_period => null
373 , pv_to_period => null
374 );
375 --logging the variables
376 IF (ln_statement_level >= ln_dbg_level)
377 THEN
378 FND_LOG.STRING(ln_statement_level,
379 lv_procedure_name,
380 'lv_output_file_name:' || lv_output_file_name);
381 END IF; --(ln_statement_level >= ln_dbg_level)
382 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
383 '.variable:' ||
384 'lv_output_file_name =' || lv_output_file_name);
385
386 JA_CN_UTILITY.Change_Output_Filename( p_xml_request_id => ln_reqid_gle
387 , p_destination_charset => pv_dest_char_set
388 , p_destination_filename => lv_output_file_name
389 , x_filename_request_id => ln_reqid_chg
390 , x_result_flag => lv_chg_req_status
391 );
392 IF lv_chg_req_status='Success'
393 THEN
394 NULL;
395 ELSIF lv_chg_req_status='Warning'
396 THEN
397 lv_error_flag:='W';
398 ELSIF lv_chg_req_status='Error'
399 THEN
400 lv_error_flag:='E';
401 END IF;--l_chg_req_status='Success'
402
403 ELSIF lv_cvt_req_status='Warning'
404 THEN
405 lv_error_flag:='W';
406 ELSIF lv_cvt_req_status='Error'
407 THEN
408 lv_error_flag:='E';
409 END IF; --l_cvt_req_status='Success'
410
411 --Completed with 'Warning'
412 ELSIF lv_gle_req_status_code='G'
413 THEN
414 lv_error_flag:='W';
415 --Completed with 'Error'
416 ELSIF lv_gle_req_status_code='E'
417 THEN
418 lv_error_flag:='E';
419 END IF; --FND_CONCURRENT.Wait_For_Request(request_id => l_reqid_comb
420 ELSE
421 lv_error_flag:='E';
422 END IF; --FND_CONCURRENT.Wait_For_Request
423 ELSE
424 lv_error_flag:='E';
425 END IF; --ln_reqid_gle<>0
426
427 --If any of above concurrent porgrams is Warning/Failed, set current SI export
428 --program to status 'Warning'/'Error' accordingly.
429 IF lv_error_flag='W'
430 THEN
431 lv_error_status:=FND_CONCURRENT.Set_Completion_Status( status => 'WARNING'
432 , message => ''
433 );
434 ELSIF lv_error_flag='E'
435 THEN
436 lv_error_status:=FND_CONCURRENT.Set_Completion_Status( status => 'ERROR'
437 , message => ''
438 );
439 END IF; --lv_error_flag='W'
440
441 --logging for debug
442 IF (ln_proc_level >= ln_dbg_level)
443 THEN
444 FND_LOG.STRING( ln_proc_level
445 ,GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
446 'Exit procedure');
447 END IF; -- (ln_proc_level>=ln_dbg_level)
448
449 --logging for debug
450 IF (ln_proc_level>=ln_dbg_level)
451 THEN
452 FND_LOG.STRING( ln_proc_level
453 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
454 , 'Exit procedure'
455 );
456 END IF; -- (ln_proc_level>=ln_dbg_level)
457
458 EXCEPTION
459 WHEN OTHERS THEN
460 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
461 THEN
462 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
463 , GV_MODULE_PREFIX || '.' || lv_procedure_name ||
464 '.Other_Exception ',
465 SQLCODE || SQLERRM);
466 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
467 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
468
469 END Submit_Request;
470
471 END JA_CN_SI_EXPORT_PROG;
472