[Home] [Help]
PACKAGE BODY: APPS.JA_CN_CFSSE_GENERATE_PKG
Source
1 PACKAGE BODY JA_CN_CFSSE_GENERATE_PKG AS
2 --$Header: JACNCSEB.pls 120.2.12010000.2 2008/10/28 06:31:50 shyan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNCSEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is the main program for 'Cash Flow Statement |
13 --| for small enterprise - Generation' |
14 --| |
15 --| Public PROCEDURE LIST |
16 --| PROCEDURE Submit_Requests |
17 --| |
18 --| HISTORY |
19 --| 03/22/2006 Jackey Li Created |
20 --| 2006-06-26 Jackey Li Updated |
21 --| add seven parameters for the procedure |
22 --| 'Submit_Requests' |
23 --| 09/22/2008 Chaoqun Wu Updated for CNAO Enhancement |
24 --| 14/10/2008 Chaoqun Wu Fix Bug# 7481444 |
25 --+======================================================================*/
26
27 --==== Golbal Variables ============
28 g_module_name VARCHAR2(30) := 'JA_CN_CFSSE_GENERATE_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
33 --==========================================================================
34 -- PROCEDURE NAME:
35 -- Submit_Requests Public
36 --
37 -- DESCRIPTION:
38 -- It is responsible for submit four concurrent programs
39 -- in turn to generate the final output file in 'TXT' format for CNAO.
40 --
41 -- PARAMETERS:
42 -- In: p_legal_entity_id legal entity ID
43 -- p_set_of_bks_id set of books ID
44 -- p_coa_id Chart of Accounts ID
45 -- p_adhoc_prefix Ad hoc prefix for FSG report
46 -- p_industry Industry with constant value 'C'
47 -- p_id_flex_code ID flex code
48 -- p_report_id FSG report id
49 -- p_perid_name GL period Name
50 -- p_axis_set_id FSG report row Set ID
51 -- p_colset_id FSG report column Set ID
52 -- p_rounding_option Rounding option
53 -- p_segment_override Segment override
54 -- p_accounting_date Accounting date
55 -- p_parameter_set_id Parameter set id
56 -- p_max_page_length Maximum page length
57 -- p_balance_type Type of balance
58 -- p_internal_trx_flag intercompany transactions flag
59 --
60 -- DESIGN REFERENCES:
61 -- CNAO_Cashflow_Statement_Generation(SE)_TD.doc
62 --
63 -- CHANGE HISTORY:
64 -- 03/22/2006 Jackey Li Created
65 -- 2006-06-26 Jackey Li add seven parameters
66 -- 14/10/2008 Chaoqun Wu Fix bug# 7481444
67 --===========================================================================
68 PROCEDURE Submit_Requests(p_legal_entity_id IN NUMBER
69 ,p_ledger_id IN NUMBER
70 ,P_DATA_ACCESS_SET_ID IN NUMBER--added by lyb
71 ,p_coa_id IN NUMBER
72 ,p_adhoc_prefix IN VARCHAR2
73 ,p_industry IN VARCHAR2
74 ,p_id_flex_code IN VARCHAR2
75 ,p_ledger_name IN VARCHAR2
76 ,p_report_id IN NUMBER
77 ,p_axis_set_id IN NUMBER
78 ,p_colset_id IN NUMBER
79 ,p_period_name IN VARCHAR2
80 ,p_currency_code IN VARCHAR2
81 ,p_rounding_option IN VARCHAR2
82 ,p_segment_override IN VARCHAR2
83 ,p_content_set_id IN NUMBER
84 ,P_ROW_ORDER_ID IN NUMBER
85 ,P_REPORT_DISPLAY_SET_ID IN NUMBER
86 ,p_OUTPUT_OPTION IN VARCHAR2
87 ,p_EXCEPTIONS_FLAG IN VARCHAR2
88 ,p_MINIMUM_DISPLAY_LEVEL IN NUMBER
89 ,p_accounting_date IN varchar2
90 ,p_parameter_set_id IN NUMBER
91 ,P_PAGE_LENGTH IN NUMBER
92 ,p_SUBREQUEST_ID IN NUMBER
93 ,P_APPL_NAME IN VARCHAR2
94
95 ,p_balance_type IN VARCHAR2
96 --,p_internal_trx_flag IN VARCHAR2
97 ,p_xml_template_language IN VARCHAR2
98 ,p_xml_template_territory IN VARCHAR2
99 ,p_xml_output_format IN VARCHAR2
100 ,p_source_charset IN VARCHAR2
101 ,p_destination_charset IN VARCHAR2
102 ,p_destination_filename IN VARCHAR2
103 ,p_source_separator IN VARCHAR2
104 ) IS
105
106 l_procedure_name VARCHAR2(30) := 'Submit_Requests';
107 --Request id for 'Program - Run Financial Statement Generator'
108 l_reqid_fsg NUMBER;
109 --Request id for 'Cash flow statement for small enterprise - Calculation'
110 l_reqid_cal NUMBER;
111 --Request id for the FSG - CFS report that automatically submitted by
112 --'Program - Run Financial Statement Generator'
113 l_reqid_fsg_cfs NUMBER;
114
115 l_fsg_req_phase fnd_lookup_values.lookup_code%TYPE;
116 l_fsg_req_status fnd_lookup_values.lookup_code%TYPE;
117 l_fsg_req_phase_cfs fnd_lookup_values.lookup_code%TYPE;
118 l_fsg_req_status_cfs fnd_lookup_values.lookup_code%TYPE;
119 l_cal_req_phase fnd_lookup_values.lookup_code%TYPE;
120 l_cal_req_status fnd_lookup_values.lookup_code%TYPE;
121
122 -- Request id for the 'Cash flow statement for small enterprise - Combination'
123 l_reqid_combination NUMBER;
124 -- Request id for the 'XML report publisher'
125 l_reqid_xmlpublisher NUMBER;
126
127 l_combination_req_phase fnd_lookup_values.lookup_code%TYPE;
128 l_combination_req_status fnd_lookup_values.lookup_code%TYPE;
129
130 l_user_phase fnd_lookup_values.meaning%TYPE;
131 l_user_status fnd_lookup_values.meaning%TYPE;
132
133 l_reqid_cvt NUMBER;
134 l_cvt_req_status VARCHAR2(100);
135
136 l_reqid_chg NUMBER;
137 l_chg_req_status VARCHAR2(100);
138
139 l_error_flag BOOLEAN;
140 l_error_msg VARCHAR2(1000) := NULL;
141 l_err_code VARCHAR2(1) := 'N';
142 l_exc_cp EXCEPTION;
143 l_xml_layout BOOLEAN;
144
145 l_legal_entity_name hr_all_organization_units_tl.NAME%TYPE;
146 l_currency_code fnd_currencies.currency_code%TYPE;
147
148 L_COMPANY_NAME varchar2(100);
149
150 --Cursor to get request_id for CFS FSG xml output
151 CURSOR c_reqid_fsg_cfs IS
152 SELECT request_id
153 FROM fnd_concurrent_requests
154 WHERE parent_request_id = l_reqid_fsg;
155
156 --Cursor to get functional currency code of current set of book
157 --this cursor is updated by lyb.
158 CURSOR c_func_currency_code
159 IS
160 SELECT
161 currency_code
162 FROM
163 gl_ledgers
164 WHERE
165 ledger_id=p_ledger_id;
166
167 --Cursor to get legal entity name
168 CURSOR c_legal_entity IS
169 SELECT hou.NAME
170 FROM hr_organization_units hou
171 WHERE hou.organization_id = p_legal_entity_id;
172
173 CURSOR C_COMPANY_NAME
174 IS
175 SELECT COMPANY_NAME
176 FROM JA_CN_SYSTEM_PARAMETERS_ALL
177 WHERE LEGAL_ENTITY_ID= p_legal_entity_id;
178
179 BEGIN
180 --log for debug
181 IF (g_proc_level >= g_dbg_level)
182 THEN
183 FND_LOG.STRING(g_proc_level,
184 g_module_name || '.' || l_procedure_name || '.begin',
185 'begin procedure');
186 END IF; --( g_proc_level >= g_dbg_level)
187
188 --FND_FILE.Put_Line(FND_FILE.LOG,
189 -- 'Submit Financial Statement Generator');
190
191 --To get functional currency code of current set of book
192 OPEN c_func_currency_code;
193 FETCH c_func_currency_code
194 INTO l_currency_code;
195 CLOSE c_func_currency_code;
196
197 --To get name for current legal entity
198 OPEN c_legal_entity;
199 FETCH c_legal_entity
200 INTO l_legal_entity_name;
201 CLOSE c_legal_entity;
202
203 OPEN C_COMPANY_NAME;
204 FETCH C_COMPANY_NAME INTO L_COMPANY_NAME;
205 CLOSE C_COMPANY_NAME;
206
207 --Submit the first concurrent program 'Program - Run Financial Statement Generator',which
208 --will automatically submit another request for the CFS report to generate XML file
209 l_reqid_fsg:=FND_REQUEST.Submit_Request( 'SQLGL'
210 ,'RGRARG'
211 ,''
212 ,''
213 ,FALSE
214 ,P_DATA_ACCESS_SET_ID
215 ,p_coa_id
216 ,p_adhoc_prefix
217 ,p_industry
218 ,p_id_flex_code
219 ,p_ledger_name
220 ,p_report_id
221 ,p_axis_set_id
222 ,p_colset_id
223 ,p_period_name
224 ,p_currency_code
225 ,p_rounding_option
226 ,p_segment_override
227 ,p_content_set_id
228 ,P_ROW_ORDER_ID
229 ,P_REPORT_DISPLAY_SET_ID
230 ,p_OUTPUT_OPTION
231 ,p_EXCEPTIONS_FLAG
232 ,p_MINIMUM_DISPLAY_LEVEL
233 ,p_ACCOUNTING_DATE
234 ,p_parameter_set_id
235 ,P_PAGE_LENGTH
236 ,p_SUBREQUEST_ID
237 ,P_APPL_NAME
238 ,L_COMPANY_NAME,
239 '', '', '', '', '',
240 '', '', '', '', '', '', '', '', '', '',
241 '', '', '', '', '', '', '', '', '', '',
242 '', '', '', '', '', '', '', '', '', '',
243 '', '', '', '', '', '', '', '', '', '',
244 '', '', '', '', '', '', '', '', '', '',
245 '', '', '', '', '', '', '', '', '', '',
246 '', '', '', '', '', '', '', '', '', ''
247 );
248
249 IF (p_OUTPUT_OPTION = 'Y') THEN
250 UPDATE FND_CONCURRENT_REQUESTS
251 SET
252 OUTPUT_FILE_TYPE = 'XML'
253 WHERE
254 REQUEST_ID = l_reqid_fsg;
255 END IF;
256 COMMIT;
257
258 --log for debug
259 IF (g_stmt_level >= g_dbg_level)
260 THEN
261 FND_LOG.STRING(g_stmt_level,
262 g_module_name || '.' || l_procedure_name ||
263 '.submit FSG',
264 'the FSG CP id is ' || l_reqid_fsg);
265 END IF; --( g_stmt_level >= g_dbg_level)
266
267 --Waiting for the 'Program - Run Financial Statement Generator' completed,
268 -- and then retrive the sub-request id
269 -- from the table 'fnd_concurrent_requests' for the FSG - CFS report.
270 IF l_reqid_fsg <> 0
271 THEN
272 IF FND_CONCURRENT.Wait_For_Request(request_id => l_reqid_fsg,
273 INTERVAL => 5,
274 phase => l_user_phase,
275 status => l_user_status,
276 dev_phase => l_fsg_req_phase,
277 dev_status => l_fsg_req_status,
278 message => l_error_msg)
279 THEN
280 --FND_FILE.Put_Line(FND_FILE.LOG,
281 -- 'FSG CP status is ' || l_fsg_req_status);
282
283 --IF status is 'NORMAL'
284 IF l_fsg_req_status = 'NORMAL'
285 THEN
286
287 --log for debug
288 IF (g_stmt_level >= g_dbg_level)
289 THEN
290 FND_LOG.STRING(g_stmt_level,
291 g_module_name || '.' || l_procedure_name ||
292 '.submit FSG-CFS',
293 'the FSG-CFS CP id is ' || l_reqid_fsg_cfs);
294 END IF; --( g_stmt_level >= g_dbg_level)
295
296 ELSIF l_fsg_req_status = 'WARNING'
297 THEN
298 l_err_code := 'W';
299 ELSE
300 l_err_code := 'E';
301 END IF; --l_fsg_req_phase='Completed'
302 END IF; -- FND_CONCURRENT.Wait_For_Request
303
304 END IF; --l_reqid_fsg<>0
305
306 --if FSG xml output request for cfs is completed, then submit
307 -- the 'Cash flow statement for small enterprise- Calculation' program
308 IF nvl(l_reqid_fsg,
309 0) <> 0
310 AND l_err_code = 'N'
311 THEN
312
313 --Waiting for the concurrent program completed
314 /* IF FND_CONCURRENT.Wait_For_Request(request_id => l_reqid_fsg_cfs,
315 INTERVAL => 5,
316 phase => l_user_phase,
317 status => l_user_status,
318 dev_phase => l_fsg_req_phase_cfs,
319 dev_status => l_fsg_req_status_cfs,
320 message => l_error_msg)
321 THEN*/
322 --IF status is 'NORMAL'
323 -- IF l_fsg_req_status_cfs = 'NORMAL'
324 -- THEN
325 --FND_FILE.Put_Line(FND_FILE.LOG,
326 -- 'Submit Cash flow statement for small enterprise - Calculation');
327
328 --Submit the second concurrent program
329 -- 'Cash flow statement for small enterprise - Calculation'
330 l_reqid_cal := FND_REQUEST.Submit_Request(application => 'JA'
331 --,program => 'JACNCFSN'
332 ,program => 'JACNCCEC' --Fix bug# 7481444
333 ,argument1 => p_legal_entity_id
334 ,argument2 => p_ledger_id
335 ,argument3 => p_period_name
336 ,argument4 => p_axis_set_id
337 ,argument5 => p_rounding_option
338 ,argument6 => p_balance_type
339 -- ,argument7 => p_internal_trx_flag
340 ,argument7 => p_coa_id
341 ,argument8 => p_segment_override --Added for CNAO Enhancement
342 );
343
344
345
346 COMMIT;
347 --Waiting for the 'Cash flow statement for small enterprise - Calculation' completed and then submit
348 --'Cash Flow Statement for small enterprise - Combination' program
349
350 --log for debug
351 IF (g_stmt_level >= g_dbg_level)
352 THEN
353 FND_LOG.STRING(g_stmt_level,
354 g_module_name || '.' || l_procedure_name ||
355 '.submit CFS for small enterprise - Calculation',
356 'CFS for small enterprise - Calculation CP id is ' ||
357 l_reqid_cal);
358 END IF; --( g_stmt_level >= g_dbg_level)
359
360 IF l_reqid_cal <> 0
361 THEN
362
363 IF FND_CONCURRENT.Wait_For_Request(request_id => l_reqid_cal,
364 INTERVAL => 5,
365 phase => l_user_phase,
366 status => l_user_status,
367 dev_phase => l_cal_req_phase,
368 dev_status => l_cal_req_status,
369 message => l_error_msg)
370 THEN
371 --IF status is 'NORMAL'
372 IF l_cal_req_status = 'NORMAL'
373 THEN
374 --FND_FILE.Put_Line(FND_FILE.LOG,
375 -- 'Submit Cash flow statement for SE - Combination');
376
377 --Submit the third concurrent program 'Cash flow statement for SE - Combination'
378 --As output of Cash flow statement - Combination' is in XML format and
379 --need to associate with XML publisher template automatically,
380 --it is required to set layout before submit the program, bug 5168016
381
382 l_xml_layout := FND_REQUEST.Add_Layout(template_appl_name => 'JA',
383 template_code => 'JACNCFSS',
384 template_language => p_xml_template_language, --'zh' ('en')
385 template_territory => p_xml_template_territory, --'00' ('US')
386 output_format => p_xml_output_format --'ETEXT' (
387 );
388
389 l_reqid_combination := FND_REQUEST.Submit_Request(application => 'JA',
390 program => 'JACNCFSS',
391 argument1 => l_reqid_fsg,
392 argument2 => l_reqid_cal);
393 COMMIT;
394
395 --log for debug
396 IF (g_stmt_level >= g_dbg_level)
397 THEN
398 FND_LOG.STRING(g_stmt_level,
399 g_module_name || '.' || l_procedure_name ||
400 '.submit CFS for small enterprise - Combination',
401 'CFS for small enterprise - Combination CP id is ' ||
402 l_reqid_combination);
403 END IF; --( g_stmt_level >= g_dbg_level)
404
405 --Waiting for the 'Cash flow statement for small enterprise
406 -- Combination' completed AND THEN submit THE 'XML Report Publisher'
407 IF l_reqid_combination <> 0
408 THEN
409
410 IF FND_CONCURRENT.Wait_For_Request(request_id => l_reqid_combination,
411 INTERVAL => 5,
412 phase => l_user_phase,
413 status => l_user_status,
414 dev_phase => l_combination_req_phase,
415 dev_status => l_combination_req_status,
416 message => l_error_msg)
417 THEN
418 --IF status is 'NORMAL'
419 IF l_combination_req_status = 'NORMAL'
420 THEN
421 --Submit characrter set conversion program
422 --to convert charaterset of output file
423 JA_CN_UTILITY.Submit_Charset_Conversion(p_xml_request_id => l_reqid_combination,
424 p_source_charset => p_source_charset,
425 p_destination_charset => p_destination_charset,
426 p_source_separator => p_source_separator,
427 x_charset_request_id => l_reqid_cvt,
428 x_result_flag => l_cvt_req_status);
429 IF l_cvt_req_status = 'Success'
430 THEN
431 --Submit "Change File Name" concurrent program
432 --to change name of output file
433 JA_CN_UTILITY.Change_Output_Filename(p_xml_request_id => l_reqid_combination,
434 p_destination_charset => p_destination_charset,
435 p_destination_filename => p_destination_filename,
436 x_filename_request_id => l_reqid_chg,
437 x_result_flag => l_chg_req_status);
438 IF l_chg_req_status = 'Success'
439 THEN
440 NULL;
441 ELSIF l_chg_req_status = 'Warning'
442 THEN
443 l_err_code := 'W';
444 ELSIF l_chg_req_status = 'Error'
445 THEN
446 l_err_code := 'E';
447 END IF; --l_chg_req_status='Success'
448
449 ELSIF l_cvt_req_status = 'Warning'
450 THEN
451 l_err_code := 'W';
452 ELSIF l_cvt_req_status = 'Error'
453 THEN
454 l_err_code := 'E';
455 END IF; --l_cvt_req_status='Success'
456
457 ELSIF l_combination_req_status = 'WARNING'
458 THEN
459 l_err_code := 'W';
460 ELSE
461 l_err_code := 'E';
462 END IF; -- l_combination_req_phase='NORMAL'
463 END IF; --FND_CONCURRENT.Wait_For_Request(request_id => l_reqid_combination
464
465 END IF; --l_reqid_combination<>0
466
467 ELSIF l_cal_req_status = 'WARNING'
468 THEN
469 l_err_code := 'W';
470 ELSE
471 l_err_code := 'E';
472 END IF; --IF l_cal_req_status = 'NORMAL'
473 END IF; --IF FND_CONCURRENT.Wait_For_Request(request_id => l_reqid_cal,
474
475 END IF; --l_reqid_cal <> 0
476 /*
477 ELSIF l_fsg_req_status_cfs = 'WARNING'
478 THEN
479 l_err_code := 'W';
480 ELSE
481 l_err_code := 'E';
482 END IF; -- l_fsg_req_status_cfs = 'NORMAL'*/
483
484 -- END IF; --FND_CONCURRENT.Wait_For_Request
485
486 END IF; --nvl(l_reqid_fsg_cfs,0)<>0
487
488 IF l_err_code = 'E'
489 THEN
490 --If any of above four concurrent porgrams is failed, set current generation
491 --program to status 'error'
492 l_error_flag := FND_CONCURRENT.Set_Completion_Status(status => 'ERROR',
493 message => '');
494 ELSIF l_err_code = 'W'
495 THEN
496 l_error_flag := FND_CONCURRENT.Set_Completion_Status(status => 'WARNING',
497 message => '');
498 END IF;
499
500 --log for debug
501 IF (g_proc_level >= g_dbg_level)
502 THEN
503 FND_LOG.STRING(g_proc_level,
504 g_module_name || '.' || l_procedure_name || '.end',
505 'end procedure');
506 END IF; --( g_proc_level >= g_dbg_level)
507
508 END Submit_Requests;
509
510 END JA_CN_CFSSE_GENERATE_PKG;
511