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