[Home] [Help]
PACKAGE BODY: APPS.JA_CN_PS_GL_EXPORT_PROG
Source
1 PACKAGE BODY JA_CN_PS_GL_EXPORT_PROG AS
2 --$Header: JACNPGLB.pls 120.2 2010/09/06 03:10:48 wuwu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNPGLB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export report for General Ledger (Public |
13 --| Sector) |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Export_GL |
17 --| PROCEDURE Submit_Request |
18 --| |
19 --| |
20 --| HISTORY |
21 --| 06-Aug-2010 Chaoqun Wu Created |
22 --+======================================================================*/
23
24 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_PS_GL_EXPORT_PROG';
25
26 --==========================================================================
27 -- PROCEDURE NAME:
28 --
29 -- Export_GL Public
30 --
31 -- DESCRIPTION:
32 --
33 -- This procedure is to export non-FSG report for general ledger (public sector)
34 --
35 -- PARAMETERS:
36 -- Out: pv_errbuf NOCOPY VARCHAR2
37 -- pv_retcode NOCOPY VARCHAR2
38 -- In: pn_legal_entity_id NUMBER identifier of legal entity
39 -- pn_chart_of_account_id NUMBER identifier of chart of account
40 -- pn_ledger_id NUMBER identifier of ledger
41 -- pv_accounting_year VARCHAR2 accounting year
42 -- pv_period_from VARCHAR2 period from
43 -- pv_period_to VARCHAR2 period to
44 -- pv_xml_template_language VARCHAR2 xml template language
45 -- pv_xml_template_territory VARCHAR2 xml template territory
46 -- pv_xml_output_format VARCHAR2 xml output format
47 --
48 -- DESIGN REFERENCES:
49 -- TDD_1213_FIN_GL_P_CNAOV2_GL.doc
50 --
51 -- CHANGE HISTORY:
52 -- 06-Aug-2010 Chaoqun Wu created
53 --==========================================================================
54 PROCEDURE Export_GL
55 (pv_errbuf OUT NOCOPY VARCHAR2
56 ,pv_retcode OUT NOCOPY VARCHAR2
57 ,pn_legal_entity_id IN NUMBER
58 ,pn_chart_of_account_id IN NUMBER
59 ,pn_ledger_id IN NUMBER
60 ,pv_accounting_year IN VARCHAR2
61 ,pv_period_from IN VARCHAR2
62 ,pv_period_to IN VARCHAR2
63 ,pv_xml_template_language IN VARCHAR2
64 ,pv_xml_template_territory IN VARCHAR2
65 ,pv_xml_output_format IN VARCHAR2
66 )
67 IS
68 lv_gl_element VARCHAR2(1000);
69 lv_procedure_name VARCHAR2(40) := 'Export_GL';
70 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
71 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
72 NO_DATA EXCEPTION;
73
74 BEGIN
75 --logging for debug
76 IF (ln_proc_level >= ln_dbg_level)
77 THEN
78 FND_LOG.STRING(ln_proc_level,
79 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
80 '.begin',
81 'Enter procedure');
82 -- logging the parameters
83 FND_LOG.STRING(ln_proc_level,
84 lv_procedure_name ||
85 '.parameters',
86 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
87 'pn_chart_of_account_id=' || pn_chart_of_account_id || ',' ||
88 'pn_ledger_id=' || pn_ledger_id || ',' ||
89 'pv_accounting_year=' || pv_accounting_year || ',' ||
90 'pv_period_from=' || pv_period_from || ',' ||
91 'pv_period_to=' || pv_period_to);
92 END IF; --l_proc_level>=l_dbg_level
93 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
94 '.parameters:'||
95 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
96 'pn_chart_of_account_id=' || pn_chart_of_account_id || ',' ||
97 'pn_ledger_id=' || pn_ledger_id || ',' ||
98 'pv_accounting_year=' || pv_accounting_year || ',' ||
99 'pv_period_from=' || pv_period_from || ',' ||
100 'pv_period_to=' || pv_period_to);
101
102 --Step1: Generate XML header part
103 FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding="'||JA_CN_UTILITY.Get_XML_Encoding||'"?>');
104 lv_gl_element := '<' || Ja_Cn_Utility.Get_XML_Tag('GENERAL_LEDGER') || ' xsi:schemaLocation="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/PSGA/XMLSchema ' ||
105 Ja_Cn_Utility.Get_XML_Tag('GENERAL_LEDGER') || '.xsd" xmlns:' ||
106 Ja_Cn_Utility.Get_XML_Tag('PUBLIC_SECTOR') || '="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/PSGA/XMLSchema"'||
107 ' xmlns="http://sxbw.audit.gov.cn/AccountingSoftwareDataInterfaceStandard/2010/PSGA/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">';
108
109 FND_FILE.put_line(FND_FILE.output, lv_gl_element);
110
111 --Step2: Export GL Basic Information
112 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log('2.1', 'GL_BASIC_INFORMATION');
113 JA_CN_PS_GBI_EXPORT_PKG.Add_GL_Basic_Info(pn_coa_id => pn_chart_of_account_id);
114
115 --Step3: Export Journal Category
116 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log('2.2','JOURNAL_CATEGORY');
117 JA_CN_JC_EXPORT_PKG.Add_Journal_Categories( 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
122 --Step4: Export COA
123 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log('2.3', 'CHART_OF_ACCOUNT');
124 JA_CN_COA_EXPORT_PKG.Add_COA(pn_coa_id => pn_chart_of_account_id
125 ,pn_ledger_id => pn_ledger_id
126 ,pn_le_id => pn_legal_entity_id
127 ,pv_xml_template_language => pv_xml_template_language
128 ,pv_xml_template_territory => pv_xml_template_territory
129 ,pv_xml_output_format => pv_xml_output_format
130 );
131
132 --Step5: Export Subsidiary Item of Account
133 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log('2.4', 'SUBSIDIARY_ITEM_OF_ACCOUNT');
134 JA_CN_SIOA_EXPORT_PKG.Add_Sub_Item_Of_Account(pn_ledger_id => pn_ledger_id
135 ,pn_legal_entity_id => pn_legal_entity_id
136 ,pn_chart_of_account_id => pn_chart_of_account_id
137 ,pv_accounting_year => pv_accounting_year
138 );
139
140 --Step6: Export Accounting Period Amount and Balance
141 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log('2.5', 'ACC_PERIOD_AMOUNT_AND_BALANCE');
142 JA_CN_APAB_EXPORT_PKG.Add_Account_Amount_Balance(pn_legal_entity_id => pn_legal_entity_id
143 ,pn_ledger_id => pn_ledger_id
144 ,pv_accounting_year => pv_accounting_year
145 ,pn_coa_id => pn_chart_of_account_id
146 ,pv_period_from => pv_period_from
147 ,pv_period_to => pv_period_to
148 );
149 --Step7: Export Journal
150 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log('2.6', 'JOURNAL');
151 JA_CN_JE_EXPORT_PKG.Add_Journal(pn_legal_entity_id => pn_legal_entity_id
152 ,pn_ledger_id => pn_ledger_id
153 ,pv_accounting_year => pv_accounting_year
154 ,pn_coa_id => pn_chart_of_account_id
155 ,pv_period_from => pv_period_from
156 ,pv_period_to => pv_period_to
157 );
158 --Step8: Export Current Year Budget
159 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log('2.7', 'CURRENT_YEAR_BUDGET_TARGET');
160 JA_CN_PS_CYB_EXPORT_PKG.Add_Current_Year_Budget(pn_legal_entity_id => pn_legal_entity_id
161 ,pn_ledger_id => pn_ledger_id
162 ,pn_coa_id => pn_chart_of_account_id
163 ,pv_accounting_year => pv_accounting_year
164 ,pv_period_from => pv_period_from
165 ,pv_period_to => pv_period_to
166 );
167
168 --Step9: Export Budget Expenditure
169 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log('2.8', 'BUDGET_EXPENDITURE');
170 JA_CN_PS_BE_EXPORT_PKG.Add_Budget_Expenditure(pn_legal_entity_id => pn_legal_entity_id
171 ,pn_ledger_id => pn_ledger_id
172 ,pn_coa_id => pn_chart_of_account_id
173 ,pv_accounting_year => pv_accounting_year
174 ,pv_period_from => pv_period_from
175 ,pv_period_to => pv_period_to
176 );
177 --Step10: Export Budget Income
178 Ja_Cn_Utility.Print_Concurrent_Steps_For_Log('2.9', 'BUDGET_INCOME');
179 JA_CN_PS_BI_EXPORT_PKG.Add_Budget_Income(pn_legal_entity_id => pn_legal_entity_id
180 ,pn_ledger_id => pn_ledger_id
181 ,pn_coa_id => pn_chart_of_account_id
182 ,pv_accounting_year => pv_accounting_year
183 ,pv_period_from => pv_period_from
184 ,pv_period_to => pv_period_to
185 );
186
187 FND_FILE.put_line(FND_FILE.output
188 ,Ja_Cn_Utility.Get_XML_Tag('GENERAL_LEDGER'
189 ,Ja_Cn_Utility.GV_TAG_TYPE_END
190 )
191 );
192
193
194 --logging for debug
195 IF (ln_proc_level >= ln_dbg_level)
196 THEN
197 FND_LOG.STRING(ln_proc_level,
198 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
199 'Exit procedure');
200 END IF; -- (ln_proc_level>=ln_dbg_level)
201
202 EXCEPTION
203 WHEN OTHERS THEN
204 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
205 THEN
206 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
207 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
208 '.Other_Exception ',
209 SQLCODE || SQLERRM);
210 END IF;
211 pv_retcode := '2';
212 pv_errbuf := SQLCODE || SQLERRM;
213 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
214
215 END Export_GL;
216
217 --==========================================================================
218 -- PROCEDURE NAME:
219 --
220 -- Submit_Request Public
221 --
222 -- DESCRIPTION:
223 --
224 -- This procedure is to export report for general ledger
225 --
226 -- PARAMETERS:
227 -- Out: pv_errbuf NOCOPY VARCHAR2
228 -- pv_retcode NOCOPY VARCHAR2
229 -- In: pn_legal_entity_id NUMBER identifier of legal entity
230 -- pn_chart_of_account_id NUMBER identifier of chart of account
231 -- pn_ledger_id NUMBER identifier of ledger
232 -- pv_adhoc_prefix VARCHAR2 adhoc prefix
233 -- pv_industry VARCHAR2 industry
234 -- pv_accounting_year VARCHAR2 accounting year
235 -- pv_period_from VARCHAR2 period from
236 -- pv_period_to VARCHAR2 period to
237 -- pn_bs_report_id NUMBER identifier of balance sheet report
238 -- pn_re_report_id NUMBER identifier of revenue and expenditure report
239 -- pn_ed_report_id NUMBER identifier of expenditure detail report
240 -- pv_currency_unit VARCHAR2 currency unit
241 -- pv_dest_char_set VARCHAR2 destination character set
242 -- pv_source_separator VARCHAR2 source separator
243 -- pn_data_access_set_id NUMBER identifier of data access set
244 -- pv_flex_code VARCHAR2 flex code
245 -- pv_default_ledger_short_name VARCHAR2 defualt ledger short name
246 -- pv_output_option VARCHAR2 output option
247 -- pv_exceptions_flag VARCHAR2 exceptions flag
248 -- pn_page_length NUMBER page length
249 -- pn_subrequest_id NUMBER identifier of subrequest
250 -- pv_appl_deflt_name VARCHAR2 application default name
251 -- pv_xml_template_language VARCHAR2 xml template language
252 -- pv_xml_template_territory VARCHAR2 xml template territory
253 -- pv_xml_output_format VARCHAR2 xml output format
254 -- pn_balance_sheet_num NUMBER balance sheet number
255 -- pn_revenue_exp_num NUMBER revenue and expenditure number
256 -- pn_exp_detail_num NUMBER number of expenditure detail report
257 --
258 -- DESIGN REFERENCES:
259 -- TDD_1213_FIN_GL_P_CNAOV2_GL.doc
260 --
261 -- CHANGE HISTORY:
262 -- 06-Aug-2010 Chaoqun Wu created
263 --==========================================================================
264
265 PROCEDURE Submit_Request
266 (pv_errbuf OUT NOCOPY VARCHAR2
267 ,pv_retcode OUT NOCOPY VARCHAR2
268 ,pn_legal_entity_id IN NUMBER
269 ,pn_coa_id IN NUMBER
270 ,pn_ledger_id IN NUMBER
271 ,pv_adhoc_prefix IN VARCHAR2
272 ,pv_industry IN VARCHAR2
273 ,pv_accounting_year IN VARCHAR2
274 ,pv_period_from IN VARCHAR2
275 ,pv_period_to IN VARCHAR2
276 ,pn_bs_report_id IN NUMBER
277 ,pn_re_report_id IN NUMBER
278 ,pn_ed_report_id IN NUMBER
279 ,pv_currency_unit IN VARCHAR2
280 ,pv_dest_char_set IN VARCHAR2
281 ,pv_source_separator IN VARCHAR2
282 ,pn_data_access_set_id IN NUMBER
283 ,pv_flex_code IN VARCHAR2
284 ,pv_default_ledger_short_name IN VARCHAR2
285 ,pv_output_option IN VARCHAR2
286 ,pv_exceptions_flag IN VARCHAR2
287 ,pn_page_length IN NUMBER
288 ,pn_subrequest_id IN NUMBER
289 ,pv_appl_deflt_name IN VARCHAR2
290 ,pv_xml_template_language IN VARCHAR2
291 ,pv_xml_template_territory IN VARCHAR2
292 ,pv_xml_output_format IN VARCHAR2
293 ,pn_balance_sheet_num IN NUMBER
294 ,pn_revenue_exp_num IN NUMBER
295 ,pn_exp_detail_num IN NUMBER
296 )
297 IS
298 lv_procedure_name VARCHAR2(40) := 'Export_GL';
299 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
300 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
301 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
302 lv_error_status BOOLEAN;
303
304 ln_reqid_gle NUMBER;
305 lv_gle_req_phase fnd_lookup_values.meaning%TYPE;
306 lv_gle_req_status fnd_lookup_values.meaning%TYPE;
307 lv_gle_req_status_code fnd_lookup_values.lookup_code%TYPE;
308
309 lv_error_flag VARCHAR2(1);
310 ln_waiting_interval NUMBER :=10;
311 lv_dev_phase VARCHAR2(100);
312 lv_dev_status VARCHAR2(100);
313 lv_message VARCHAR2(1000);
314
315 ln_reqid_cvt NUMBER;
316 lv_cvt_req_status VARCHAR2(100);
317
318 ln_reqid_chg NUMBER;
319 lv_chg_req_status VARCHAR2(100);
320
321 ln_convert_reqid NUMBER;
322 ln_reqid_comb NUMBER;
323 lv_comb_req_phase fnd_lookup_values.meaning%TYPE;
324 lv_comb_req_status fnd_lookup_values.meaning%TYPE;
325 lv_comb_req_status_code fnd_lookup_values.lookup_code%TYPE;
326 lv_output_file_name VARCHAR2(100);
327 lv_period_from VARCHAR2(20):= pv_period_from;
328 lv_period_to VARCHAR2(20):= pv_period_to;
329 lv_accounting_date VARCHAR2(30) := NULL;
330
331 BEGIN
332 --logging for debug
333 IF (ln_proc_level >= ln_dbg_level)
334 THEN
335 FND_LOG.STRING(ln_proc_level,
336 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
337 '.begin',
338 'Enter procedure');
339 -- logging the parameters
340 FND_LOG.STRING(ln_proc_level,
341 lv_procedure_name ||
342 '.parameters',
343 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
344 'pn_ledger_id=' || pn_ledger_id || ',' ||
345 'pv_adhoc_prefix=' || pv_adhoc_prefix || ',' ||
346 'pv_industry=' || pv_industry || ',' ||
347 'pv_accounting_year=' || pv_accounting_year || ',' ||
348 'pv_period_from=' || pv_period_from || ',' ||
349 'pv_period_to=' || pv_period_to || ',' ||
350 'pn_bs_report_id=' || pn_bs_report_id || ',' ||
351 'pn_re_report_id=' || pn_re_report_id || ',' ||
352 'pn_ed_report_id=' || pn_ed_report_id || ',' ||
353 'pv_currency_unit=' || pv_currency_unit || ',' ||
354 'pv_dest_char_set=' || pv_dest_char_set || ',' ||
355 'pv_source_separator=' || pv_source_separator || ',' ||
356 'pn_data_access_set_id=' || pn_data_access_set_id || ',' ||
357 'pv_default_ledger_short_name=' || pv_default_ledger_short_name || ',' ||
358 'pv_output_option=' || pv_output_option || ',' ||
359 'pv_exceptions_flag=' || pv_exceptions_flag || ',' ||
360 'pn_page_length=' || pn_page_length || ',' ||
361 'pn_subrequest_id=' || pn_subrequest_id || ',' ||
362 'pv_appl_deflt_name=' || pv_appl_deflt_name || ',' ||
363 'pv_xml_template_language=' || pv_xml_template_language || ',' ||
364 'pv_xml_template_territory=' || pv_xml_template_territory || ',' ||
365 'pv_xml_output_format=' || pv_xml_output_format || ',' ||
366 'pn_balance_sheet_num=' || pn_balance_sheet_num || ',' ||
367 'pn_revenue_exp_num=' || pn_revenue_exp_num || ',' ||
368 'pn_exp_detail_num=' || pn_exp_detail_num);
369
370 END IF; --l_proc_level>=l_dbg_level
371
372 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
373 '.parameters:' ||
374 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
375 'pn_ledger_id=' || pn_ledger_id || ',' ||
376 'pv_adhoc_prefix=' || pv_adhoc_prefix || ',' ||
377 'pv_industry=' || pv_industry || ',' ||
378 'pv_accounting_year=' || pv_accounting_year || ',' ||
379 'pv_period_from=' || pv_period_from || ',' ||
380 'pv_period_to=' || pv_period_to || ',' ||
381 'pn_bs_report_id=' || pn_bs_report_id || ',' ||
382 'pn_re_report_id=' || pn_re_report_id || ',' ||
383 'pn_ed_report_id=' || pn_ed_report_id || ',' ||
384 'pv_currency_unit=' || pv_currency_unit || ',' ||
385 'pv_dest_char_set=' || pv_dest_char_set || ',' ||
386 'pv_source_separator=' || pv_source_separator || ',' ||
387 'pn_data_access_set_id=' || pn_data_access_set_id || ',' ||
388 'pv_default_ledger_short_name=' || pv_default_ledger_short_name || ',' ||
389 'pv_output_option=' || pv_output_option || ',' ||
390 'pv_exceptions_flag=' || pv_exceptions_flag || ',' ||
391 'pn_page_length=' || pn_page_length || ',' ||
392 'pn_subrequest_id=' || pn_subrequest_id || ',' ||
393 'pv_appl_deflt_name=' || pv_appl_deflt_name || ',' ||
394 'pv_xml_template_language=' || pv_xml_template_language || ',' ||
395 'pv_xml_template_territory=' || pv_xml_template_territory || ',' ||
396 'pv_xml_output_format=' || pv_xml_output_format || ',' ||
397 'pn_balance_sheet_num=' || pn_balance_sheet_num || ',' ||
398 'pn_revenue_exp_num=' || pn_revenue_exp_num || ',' ||
399 'pn_exp_detail_num=' || pn_exp_detail_num);
400
401 --Step0: Check system option
402 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 0: Check System Option');
403 IF NOT(Ja_Cn_Utility.Check_System_Option(pn_legal_entity_id))
404 THEN
405 pv_retcode := 1;
406 pv_errbuf := '';
407 RETURN;
408 END IF; --(Ja_Cn_Utility.Check_System_Option(pn_legal_entity_id))
409
410 --Step1: Check profile
411 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 1: Check Profile');
412 IF NOT(JA_CN_UTILITY.Check_Profile)
413 THEN
414 pv_retcode := 1;
415 pv_errbuf := '';
416 RETURN;
417 END IF;
418
419 --Handling period from and period to if any of them is null
420 IF lv_period_from IS NULL
421 THEN
422 BEGIN
423 SELECT PERIOD_NAME
424 INTO lv_period_from
425 FROM (SELECT PERIOD_NAME
426 FROM GL_PERIOD_STATUSES
427 WHERE APPLICATION_ID = 101
428 AND LEDGER_ID = pn_ledger_id
429 AND PERIOD_YEAR = pv_accounting_year
430 AND CLOSING_STATUS <> 'N'
431 AND CLOSING_STATUS <> 'F'
432 ORDER BY START_DATE ASC)
433 WHERE ROWNUM = 1;
434 EXCEPTION
435 WHEN OTHERS THEN
436 NULL;
437 END;
438 END IF;
439
440 IF lv_period_to IS NULL
441 THEN
442 BEGIN
443 SELECT PERIOD_NAME
444 INTO lv_period_to
445 FROM (SELECT PERIOD_NAME
446 FROM GL_PERIOD_STATUSES
447 WHERE APPLICATION_ID = 101
448 AND LEDGER_ID = pn_ledger_id
449 AND PERIOD_YEAR = pv_accounting_year
450 AND CLOSING_STATUS <> 'N'
451 AND CLOSING_STATUS <> 'F'
452 ORDER BY START_DATE DESC)
453 WHERE ROWNUM = 1;
454 EXCEPTION
455 WHEN OTHERS THEN
456 NULL;
457 END;
458 END IF;
459
460 BEGIN
461 SELECT TO_CHAR(END_DATE,
462 'YYYY/MM/DD HH:MM:SS')
463 INTO lv_accounting_date
464 FROM GL_PERIOD_STATUSES
465 WHERE APPLICATION_ID = 101
466 AND LEDGER_ID = pn_ledger_id
467 AND PERIOD_NAME = lv_period_to;
468 EXCEPTION
469 WHEN OTHERS THEN
470 NULL;
471 END;
472
473 --Step2: Submit GL Non-FSG Export program
474 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 2: Submit GL Non-FSG Export Program (Public Sector)');
475 ln_reqid_gle:=FND_REQUEST.Submit_Request(application => 'JA'
476 ,program => 'JACNPGLE'
477 ,argument1 => pn_legal_entity_id
478 ,argument2 => pn_coa_id
479 ,argument3 => pn_ledger_id
480 ,argument4 => pv_accounting_year
481 ,argument5 => lv_period_from
482 ,argument6 => lv_period_to
483 ,argument7 => pv_xml_template_language
484 ,argument8 => pv_xml_template_territory
485 ,argument9 => pv_xml_output_format
486 );
487 COMMIT;
488 --Waiting for the 'Export Non-FSG Report for General Ledger (Public Sector)' completed
489 --get its status
490
491 IF ln_reqid_gle<>0
492 THEN
493
494 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_gle
495 ,interval => ln_waiting_interval
496 ,phase => lv_gle_req_phase
497 ,status => lv_gle_req_status
498 ,dev_phase => lv_dev_phase
499 ,dev_status => lv_dev_status
500 ,message => lv_message
501 )
502 THEN
503
504 --To get lookup code for current status
505 SELECT
506 lookup_code
507 INTO
508 lv_gle_req_status_code
509 FROM
510 fnd_lookup_values
511 WHERE lookup_type = 'CP_STATUS_CODE'
512 AND view_application_id=0
513 AND security_group_id=0
514 AND meaning=lv_gle_req_status
515 AND enabled_flag='Y'
516 AND language = USERENV('LANG');
517
518 --Completed with Normal
519 IF lv_gle_req_status_code='C'
520 THEN
521 --Step3: submit FSG report
522 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 3: Submit FSG report');
523
524 ja_cn_ps_fsg_xml_submit_prog.Submit_FSG_Report(pn_data_access_set_id => pn_data_access_set_id
525 ,pn_coa_id => pn_coa_id
526 ,pv_adhoc_prefix => pv_adhoc_prefix
527 ,pv_industry => pv_industry
528 ,pv_flex_code => pv_flex_code
529 ,pv_default_ledger_short_name => pv_default_ledger_short_name
530 ,pv_period_name => lv_period_to
531 ,pv_output_option => pv_output_option
532 ,pv_exceptions_flag => pv_exceptions_flag
533 ,pn_page_length => pn_page_length
534 ,pn_subrequest_id => pn_subrequest_id
535 ,pv_appl_deflt_name => pv_appl_deflt_name
536 ,pn_balance_sheet_id => pn_bs_report_id
537 ,pn_balance_sheet_num => pn_balance_sheet_num
538 ,pn_revenue_expenditure_id => pn_re_report_id
539 ,pn_revenue_expenditure_num => pn_revenue_exp_num
540 ,pn_expenditure_detail_id => pn_ed_report_id
541 ,pn_expenditure_detail_num => pn_exp_detail_num
542 ,pv_currency_unit => pv_currency_unit
543 ,pn_legal_entity_id => pn_legal_entity_id
544 ,pn_ledger_id => pn_ledger_id
545 ,pv_accounting_date => lv_accounting_date
546 ,pv_xml_template_language => pv_xml_template_language
547 ,pv_xml_template_territory => pv_xml_template_territory
548 ,pv_xml_output_format => pv_xml_output_format
549 ,pn_convert_reqid => ln_convert_reqid
550 );
551 IF ln_convert_reqid<>0
552 THEN
553
554 --Step4: Submit combination program
555 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 4: Submit Combination program');
556 ln_reqid_comb:=FND_REQUEST.Submit_Request(application => 'JA'
557 ,program => 'JACNPCCP'
558 ,argument1 => ln_reqid_gle
559 ,argument2 => ln_convert_reqid
560 );
561 COMMIT;
562 --Waiting for the 'combination program' completed
563 --get its status
564
565 IF ln_reqid_comb<>0
566 THEN
567
568 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_comb
569 ,interval => ln_waiting_interval
570 ,phase => lv_comb_req_phase
571 ,status => lv_comb_req_status
572 ,dev_phase => lv_dev_phase
573 ,dev_status => lv_dev_status
574 ,message => lv_message
575 )
576 THEN
577
578 --To get lookup code for current status
579 SELECT
580 lookup_code
581 INTO
582 lv_comb_req_status_code
583 FROM
584 fnd_lookup_values
585 WHERE lookup_type = 'CP_STATUS_CODE'
586 AND view_application_id=0
587 AND security_group_id=0
588 AND meaning=lv_comb_req_status --Fixing bug 9547791
589 AND enabled_flag='Y'
590 AND language = USERENV('LANG');
591
592 --Completed with Normal
593 IF lv_comb_req_status_code='C'
594 THEN
595
596 --Step5: Submit characrter set conversion program
597 --to convert charaterset of output file
598 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 5: Submit Characrter Set Conversion program');
599 JA_CN_UTILITY.Submit_XML_Charset_Conversion(p_xml_request_id => ln_reqid_comb
600 ,p_source_charset => JA_CN_UTILITY.Get_XML_Encoding
601 ,p_destination_charset => pv_dest_char_set
602 ,p_source_separator => NULL
603 ,x_charset_request_id => ln_reqid_cvt
604 ,x_result_flag => lv_cvt_req_status
605 );
606 IF lv_cvt_req_status='Success'
607 THEN
608 --Step6: Submit "Change File Name" concurrent program
609 --to change name of output file
610 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 6: Submit File Name Renaming program');
611 lv_output_file_name := Ja_Cn_Utility.Generate_File_name(pv_module_name => 'GL'
612 ,pv_accounting_year => pv_accounting_year
613 ,pv_ledger_id => pn_ledger_id
614 ,pv_from_period => lv_period_from
615 ,pv_to_period => lv_period_to
616 );
617 --logging the variables
618 IF (ln_statement_level >= ln_dbg_level)
619 THEN
620 FND_LOG.STRING(ln_statement_level,
621 lv_procedure_name,
622 'lv_output_file_name:' || lv_output_file_name);
623 END IF; --(ln_statement_level >= ln_dbg_level)
624 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
625 '.variable:' ||
626 'lv_output_file_name=' || lv_output_file_name);
627
628 JA_CN_UTILITY.Change_Output_Filename(p_xml_request_id => ln_reqid_comb
629 ,p_destination_charset => pv_dest_char_set
630 ,p_destination_filename => lv_output_file_name
631 ,x_filename_request_id => ln_reqid_chg
632 ,x_result_flag => lv_chg_req_status
633 );
634 IF lv_chg_req_status='Success'
635 THEN
636 NULL;
637 ELSIF lv_chg_req_status='Warning'
638 THEN
639 lv_error_flag:='W';
640 ELSIF lv_chg_req_status='Error'
641 THEN
642 lv_error_flag:='E';
643 END IF; --l_chg_req_status='Success'
644
645
646 ELSIF lv_cvt_req_status='Warning'
647 THEN
648 lv_error_flag:='W';
649 ELSIF lv_cvt_req_status='Error'
650 THEN
651 lv_error_flag:='E';
652 END IF; --l_cvt_req_status='Success'
653
654 --Completed with 'Warning'
655 ELSIF lv_comb_req_status_code='G'
656 THEN
657 lv_error_flag:='W';
658 --Completed with 'Error'
659 ELSIF lv_comb_req_status_code='E'
660 THEN
661 lv_error_flag:='E';
662 END IF; --lv_comb_req_status_code='C'
663 END IF; --FND_CONCURRENT.Wait_For_Request(request_id => l_reqid_comb
664 ELSE
665 lv_error_flag:='E';
666 END IF; --ln_reqid_comb<>0
667
668 ELSE
669 lv_error_flag:='E';
670 END IF; --ln_convert_reqid<>0
671
672 --Completed with 'Warning'
673 ELSIF lv_gle_req_status_code='G'
674 THEN
675 lv_error_flag:='W';
676 --Completed with 'Error'
677 ELSIF lv_gle_req_status_code='E'
678 THEN
679 lv_error_flag:='E';
680 END IF; --lv_gle_req_status_code='C'
681 END IF; --FND_CONCURRENT.Wait_For_Request(request_id => l_reqid_comb
682 ELSE
683 lv_error_flag:='E';
684 END IF; --ln_reqid_gle<>0
685
686 --If any of above concurrent porgrams is Warning/Failed, set current GL export
687 --program to status 'Warning'/'Error' accordingly.
688 IF lv_error_flag='W'
689 THEN
690 lv_error_status:=FND_CONCURRENT.Set_Completion_Status( status => 'WARNING'
691 , message => ''
692 );
693 ELSIF lv_error_flag='E'
694 THEN
695 lv_error_status:=FND_CONCURRENT.Set_Completion_Status( status => 'ERROR'
696 , message => ''
697 );
698 END IF; --lv_error_flag='W'
699
700 --logging for debug
701 IF (ln_proc_level >= ln_dbg_level)
702 THEN
703 FND_LOG.STRING(ln_proc_level,
704 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
705 'Exit procedure');
706 END IF; -- (ln_proc_level>=ln_dbg_level)
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
711 THEN
712 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
713 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
714 '.Other_Exception ',
715 SQLCODE || SQLERRM);
716 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
717 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
718
719 END Submit_Request;
720
721 END JA_CN_PS_GL_EXPORT_PROG;