[Home] [Help]
PACKAGE BODY: APPS.JA_CN_FSG_XML_SUBMIT_PROG
Source
1 package body ja_cn_fsg_xml_submit_prog AS
2 --$Header: JACNFXRB.pls 120.6 2010/03/31 09:55:17 weihuang ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| JACNFXRB.pls
10 --|
11 --| DESCRIPTION
12 --|
13 --| This package is used to submit FSG report and XML Report publisher.
14 --|
15 --| TYPE LIEST
16 --| None
17 --| PROCEDURE LIST
18 --| PROCEDURE Submit_FSG_XML_Report
19 --| PROCEDURE Get_Lookup_Code
20 --| FUNCTION Submit_FSG_Request
21 --| PROCEDURE Submit_xml_Publisher
22 --| PROCEDURE Submit_FSG_Report
23 --|
24 --| HISTORY
25 --| 06/02/2006 ShuJuan Yan Created
26 --| 27/04/2007 Joy liu Updated
27 --| the order and number of parameter is changed
28 --| 08/02/2010 Wei Huang Add PROCEDURE Submit_FSG_Report
29 --+======================================================================*/
30
31
32 --==========================================================================
33 -- FUNCTION NAME:
34 -- Submit_FSG_Request Private
35 --
36 -- DESCRIPTION:
37 -- This function is used to submit FSG report,
38 -- PARAMETERS:
39 -- In:
40 -- X_APPL_SHORT_NAME Application short name
41 -- X_DATA_ACCESS_SET_ID Data Acess ID
42 -- X_CONCURRENT_REQUEST_ID CONCURRENT REQUEST ID
43 -- X_PROGRAM PROGRAM
44 -- X_COA_ID char of accounts id
45 -- X_ADHOC_PREFIX ADHOC PREFIX
46 -- X_INDUSTRY Industry
47 -- X_FLEX_CODE Flex Code
48 -- X_DEFAULT_LEDGER_SHORT_NAME Default Ledger short Name
49 -- X_REPORT_ID Report ID
50 -- X_ROW_SET_ID Row Set ID
51 -- X_COLUMN_SET_ID Column Set ID
52 -- X_PERIOD_NAME Period Name
53 -- X_UNIT_OF_MEASURE_ID Unit of Measure ID/currency
54 -- X_ROUNDING_OPTION Rounding Option
55 -- X_SEGMENT_OVERRIDE Segment Override
56 -- X_CONTENT_SET_ID Content Set ID
57 -- X_ROW_ORDER_ID Row Order ID
58 -- X_REPORT_DISPLAY_SET_ID Report Display Set ID
59 -- X_OUTPUT_OPTION Out Option
60 -- X_EXCEPTIONS_FLAG Exception
61 -- X_MINIMUM_DISPLAY_LEVEL Minimum Display Level
62 -- X_ACCOUNTING_DATE Accounting Date
63 -- X_PARAMETER_SET_ID Parameter set ID
64 -- X_PAGE_LENGTH Page Lenth
65 -- X_SUBREQUEST_ID SubRequest ID
66 -- X_APPL_DEFLT_NAME Application Default Name
67 -- Out:
68 -- X_CONCURRENT_REQUEST_ID Concrrent Request ID
69 -- X_PROGRAM Program
70 --
71 -- DESIGN REFERENCES:
72 -- None
73 --
74 -- CHANGE HISTORY:
75 -- 06/14/2006 Shujuan Yan Created
76 -- 27/04/2007 Joy liu Updated
77 -- the order and number of parameter is changed
78 --==========================================================================
79
80 FUNCTION Submit_FSG_Request(
81 X_APPL_SHORT_NAME IN VARCHAR2,
82 X_DATA_ACCESS_SET_ID IN NUMBER,
83 X_CONCURRENT_REQUEST_ID OUT NOCOPY NUMBER,
84 X_PROGRAM OUT NOCOPY VARCHAR2,
85 X_COA_ID IN NUMBER,
86 X_ADHOC_PREFIX IN VARCHAR2,
87 X_INDUSTRY IN VARCHAR2,
88 X_FLEX_CODE IN VARCHAR2,
89 X_DEFAULT_LEDGER_SHORT_NAME IN VARCHAR2,
90 X_REPORT_ID IN NUMBER,
91 X_ROW_SET_ID IN NUMBER,
92 X_COLUMN_SET_ID IN NUMBER,
93 X_PERIOD_NAME IN VARCHAR2,
94 X_UNIT_OF_MEASURE_ID IN VARCHAR2,
95 X_ROUNDING_OPTION IN VARCHAR2,
96 X_SEGMENT_OVERRIDE IN VARCHAR2,
97 X_CONTENT_SET_ID IN NUMBER,
98 X_ROW_ORDER_ID IN NUMBER,
99 X_REPORT_DISPLAY_SET_ID IN NUMBER,
100 X_OUTPUT_OPTION IN VARCHAR2,
101 X_EXCEPTIONS_FLAG IN VARCHAR2,
102 X_MINIMUM_DISPLAY_LEVEL IN NUMBER,
103 X_ACCOUNTING_DATE IN DATE,
104 X_PARAMETER_SET_ID IN NUMBER,
105 X_PAGE_LENGTH IN NUMBER,
106 X_SUBREQUEST_ID IN NUMBER,
107 X_APPL_DEFLT_NAME IN VARCHAR2)
108 RETURN BOOLEAN
109 IS
110 TYPE RepTyp IS RECORD
111 (row_set_id NUMBER(15),
112 column_set_id NUMBER(15),
113 unit_of_measure_id VARCHAR2(30),
114 content_set_id NUMBER(15),
115 row_order_id NUMBER(15),
116 rounding_option VARCHAR2(1),
117 parameter_set_id NUMBER(15),
118 minimum_display_level NUMBER(15),
119 report_display_set_id NUMBER(15),
120 output_option VARCHAR2(1),
121 report_title VARCHAR2(240),
122 segment_override VARCHAR2(800));
123
124 report_rec RepTyp;
125
126 req_id NUMBER;
127 rep_run_type rg_report_content_sets.report_run_type%TYPE;
128 L_COMPANY_NAME VARCHAR2(100);--show the company name
129 L_LEGAL_ENTITY NUMBER(15);
130 BEGIN
131 --get legal_entity id
132 L_LEGAL_ENTITY:=Fnd_Profile.VALUE(NAME => 'JA_CN_LEGAL_ENTITY');
133 --get company name
134 SELECT COMPANY_NAME
135 INTO L_COMPANY_NAME
136 FROM JA_CN_SYSTEM_PARAMETERS_ALL
137 WHERE LEGAL_ENTITY_ID=L_LEGAL_ENTITY;
138
139 SELECT row_set_id,
140 column_set_id,
141 unit_of_measure_id,
142 content_set_id,
143 row_order_id,
144 rounding_option,
145 parameter_set_id,
146 minimum_display_level,
147 report_display_set_id,
148 output_option,
149 name,
150 segment_override
151 INTO report_rec
152 FROM RG_REPORTS
153 WHERE REPORT_ID = X_REPORT_ID;
154
155 --
156 -- If content set is used by this report then
157 -- check the report run method.
158 --
159 IF (X_content_set_id IS NOT NULL) THEN
160 SELECT report_run_type
161 INTO rep_run_type
162 FROM rg_report_content_sets
163 WHERE content_set_id = X_content_set_id;
164 ELSE
165 rep_run_type := 'S';
166 END IF;
167
168 IF (rep_run_type = 'P') THEN
169 X_PROGRAM := 'RGSSRQ';
170 req_id := FND_REQUEST.SUBMIT_REQUEST('SQLGL',
171 'RGSSRQ',
172 report_rec.report_title,
173 '',
174 FALSE,
175 TO_CHAR(X_data_access_set_id),
176 TO_CHAR(X_COA_ID),
177 X_ADHOC_PREFIX,
178 X_INDUSTRY,
179 X_FLEX_CODE,
180 X_default_ledger_short_name,
181 TO_CHAR(X_report_id),
182 TO_CHAR(X_row_set_id),
183 TO_CHAR(X_column_set_id),
184 X_period_name,
185 X_unit_of_measure_id,
186 X_rounding_option,
187 X_segment_override,
188 TO_CHAR(X_content_set_id),
189 TO_CHAR(X_row_order_id),
190 TO_CHAR(X_report_display_set_id),
191 X_output_option,
192 X_exceptions_flag,
193 TO_CHAR(X_minimum_display_level),
194 TO_CHAR(X_accounting_date, 'YYYY/MM/DD'),
195 TO_CHAR(X_parameter_set_id),
196 TO_CHAR(X_page_length),
197 X_appl_deflt_name,
198 L_COMPANY_NAME,
199 '', '', '', '', '', '',
200 '', '', '', '', '', '', '', '', '', '',
201 '', '', '', '', '', '', '', '', '', '',
202 '', '', '', '', '', '', '', '', '', '',
203 '', '', '', '', '', '', '', '', '', '',
204 '', '', '', '', '', '', '', '', '', '',
205 '', '', '', '', '', '', '', '', '', '',
206 '', '', '', '', '', '', '', '', '', ''
207 );
208 IF (X_output_option = 'Y') THEN
209 UPDATE FND_CONCURRENT_REQUESTS
210 SET
211 OUTPUT_FILE_TYPE = 'XML'
212 WHERE
213 REQUEST_ID = req_id;
214 END IF;
215 ELSE
216 X_PROGRAM := 'JACNFSGC';
217 req_id := FND_REQUEST.SUBMIT_REQUEST('JA',
218 'JACNFSGC',
219 report_rec.report_title,
220 '',
221 FALSE,
222 TO_CHAR(X_data_access_set_id),
223 TO_CHAR(X_COA_ID),
224 X_ADHOC_PREFIX,
225 X_INDUSTRY,
226 X_FLEX_CODE,
227 X_default_ledger_short_name,
228 TO_CHAR(X_report_id),
229 TO_CHAR(X_row_set_id),
230 TO_CHAR(X_column_set_id),
231 X_period_name,
232 X_unit_of_measure_id,
233 X_rounding_option,
234 X_segment_override,
235 TO_CHAR(X_content_set_id),
236 TO_CHAR(X_row_order_id),
237 TO_CHAR(X_report_display_set_id),
238 X_output_option,
239 X_exceptions_flag,
240 TO_CHAR(X_minimum_display_level),
241 TO_CHAR(X_accounting_date, 'YYYY/MM/DD'),
242 TO_CHAR(X_parameter_set_id),
243 TO_CHAR(X_page_length),
244 TO_CHAR(X_SUBREQUEST_ID),
245 X_appl_deflt_name,
246 L_COMPANY_NAME,
247 '', '', '', '', '',
248 '', '', '', '', '', '', '', '', '', '',
249 '', '', '', '', '', '', '', '', '', '',
250 '', '', '', '', '', '', '', '', '', '',
251 '', '', '', '', '', '', '', '', '', '',
252 '', '', '', '', '', '', '', '', '', '',
253 '', '', '', '', '', '', '', '', '', '',
254 '', '', '', '', '', '', '', '', ''
255 );
256 IF (X_output_option = 'Y') THEN
257 UPDATE FND_CONCURRENT_REQUESTS
258 SET
259 OUTPUT_FILE_TYPE = 'XML'
260 WHERE
261 REQUEST_ID = req_id;
262 END IF;
263 END IF;
264
265 IF (req_id = 0) THEN
266 RETURN FALSE;
267 ELSE
268 X_concurrent_request_id:= req_id;
269 return TRUE;
270 END IF;
271 END Submit_FSG_Request;
272
273
274
275
276 --==========================================================================
277 -- PROCEDURE NAME:
278 -- Submit_xml_publiser Private
279 --
280 -- DESCRIPTION:
281 -- This procedure is used to submit xml publisher concurrent,
282 -- PARAMETERS:
283 -- In: p_template_appl template application
284 -- p_ltemplate template name
285 -- p_template_locale template locale
286 -- p_output_format output format
287 -- Out: x_xml_request_id xml request id
288 -- x_result_flag result flag
289 --
290 -- DESIGN REFERENCES:
291 -- None
292 --
293 -- CHANGE HISTORY:
294 -- 06/03/2006 Shujuan Yan Created
295 --==========================================================================
296 PROCEDURE Submit_xml_Publisher(p_fsg_request_id IN NUMBER,
297 p_template_appl IN VARCHAR2,
298 p_template IN VARCHAR2,
299 p_template_locale IN VARCHAR2,
300
301 p_output_format IN VARCHAR2,
302 x_xml_request_id OUT NOCOPY NUMBER,
303 x_result_flag OUT NOCOPY VARCHAR2) IS
304
305 l_procedure_name VARCHAR2(30) := 'Submit_xml_Publisher';
306 l_runtime_level NUMBER := fnd_log.g_current_runtime_level;
307 l_procedure_level NUMBER := fnd_log.level_procedure;
308 --l_statement_level NUMBER := fnd_log.level_statement;
309 --l_exception_level NUMBER := fnd_log.level_exception;
310 l_complete_flag BOOLEAN;
311 l_phase VARCHAR2(100);
312 l_status VARCHAR2(100);
313 l_del_phase VARCHAR2(100);
314 l_del_status VARCHAR2(100);
315 l_message VARCHAR2(1000);
316
317 BEGIN
318 --log for debug
319 IF (l_procedure_level >= l_runtime_level) THEN
320 fnd_log.STRING(l_procedure_level,
321 g_module_name || '.' || l_procedure_name || '.begin',
322 'begin procedure');
323 END IF; --l_procedure_level >= l_runtime_level
324 -- submit xml publisher concurrent program
325 x_xml_request_id := fnd_request.submit_request('XDO',
326 'XDOREPPB',
327 NULL,
328 SYSDATE,
329 FALSE,
330 'Y', --added by lyb, for bug 6642516
331 p_fsg_request_id,
332 p_template_appl,
333 p_template,
334 p_template_locale,
335 NULL,
336 NULL,
337 p_output_format);
338
339 IF (x_xml_request_id <= 0 OR x_xml_request_id IS NULL)
340 THEN
341 x_result_flag := 'Error';
342 ELSE
343 COMMIT;
344 --Wait for concurrent complete
345 l_complete_flag := fnd_concurrent.wait_for_request(x_xml_request_id,
346 1,
347 0,
348 l_phase,
349 l_status,
350 l_del_phase,
351 l_del_status,
352 l_message);
353 IF l_complete_flag = FALSE OR
354 JA_CN_UTILITY.get_lookup_code(p_lookup_meaning => l_status,
355 p_lookup_type => 'CP_STATUS_CODE') <> 'C'
356 THEN
357 x_result_flag := 'Error';
358 ELSE x_result_flag := 'Sucess';
359 END IF; -- l_complete_flag = false
360 END IF; -- (x_xml_request_id <= 0 OR x_xml_request_id IS NULL)
361
362 --log for debug
363 IF (l_procedure_level >= l_runtime_level)
364 THEN
365 fnd_log.STRING(l_procedure_level,
366 g_module_name || '.' || l_procedure_name || '.end',
367 'end procedure');
368 END IF; --l_procedure_level >= l_runtime_level
369 EXCEPTION
370 WHEN OTHERS THEN
371 --log for debug
372 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
373 THEN
374 fnd_log.STRING(fnd_log.level_unexpected,
375 g_module_name || l_procedure_name ||
376 '. OTHER_EXCEPTION ',
377 SQLCODE || SQLERRM);
378 END IF; -- fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
379 RAISE;
380 END Submit_xml_Publisher;
381 --==========================================================================
382 -- PROCEDURE NAME:
383 -- Submit_FSG_XML_Report Private
384 --
385 -- DESCRIPTION:
386 -- This procedure is used to submit FSG report and XML Report publisher .
387 -- PARAMETERS:
388 -- In:
389 -- P_APPL_SHORT_NAME Application short name
390 -- P_DATA_ACCESS_SET_ID Data Acess ID
391 -- P_CONCURRENT_REQUEST_ID CONCURRENT REQUEST ID
392 -- P_PROGRAM PROGRAM
393 -- P_COA_ID char of accounts id
394 -- P_ADHOC_PREFIX ADHOC PREFIX
395 -- P_INDUSTRY Industry
396 -- P_FLEX_CODE Flex Code
397 -- P_DEFAULT_LEDGER_SHORT_NAME Default Ledger short Name
398 -- P_REPORT_ID Report ID
399 -- P_ROW_SET_ID Row Set ID
400 -- P_COLUMN_SET_ID Column Set ID
401 -- P_PERIOD_NAME Period Name
402 -- P_UNIT_OF_MEASURE_ID Unit of Measure ID/currency
403 -- P_ROUNDING_OPTION Rounding Option
404 -- P_SEGMENT_OVERRIDE Segment Override
405 -- P_CONTENT_SET_ID Content Set ID
406 -- P_ROW_ORDER_ID Row Order ID
407 -- P_REPORT_DISPLAY_SET_ID Report Display Set ID
408 -- P_OUTPUT_OPTION Out Option
409 -- P_EXCEPTIONS_FLAG Exception
410 -- P_MINIMUM_DISPLAY_LEVEL Minimum Display Level
411 -- P_ACCOUNTING_DATE Accounting Date
412 -- P_PARAMETER_SET_ID Parameter set ID
413 -- P_PAGE_LENGTH Page Lenth
414 -- P_SUBREQUEST_ID SubRequest ID
415 -- P_APPL_DEFLT_NAME Application Default Name
416
417 -- p_template Template name
418 -- p_template_locale Template locale
419 -- p_output_format Output format
420 -- p_source_charset source charset
421 -- p_destination_charset destination charset
422 -- p_destination_filename destination filename
423 -- p_source_separator source separator
424 -- Out:
425 -- X_CONCURRENT_REQUEST_ID Concrrent Request ID
426 -- X_PROGRAM Program
427
428 -- DESIGN REFERENCES:
429 -- None
430 --
431 -- CHANGE HISTORY:
432 -- 06/02/2006 Shujuan Yan Created
433 -- 27/04/2007 Joy liu Updated
434 -- the order and number of parameter is changed
435 --===========================================================================
436 PROCEDURE Submit_FSG_XML_Report(errbuf OUT NOCOPY VARCHAR2,
437 retcode OUT NOCOPY VARCHAR2,
438
439 P_DATA_ACCESS_SET_ID IN NUMBER,
440 P_COA_ID IN NUMBER,
441 P_ADHOC_PREFIX IN VARCHAR2,
442 P_INDUSTRY IN VARCHAR2,
443 P_FLEX_CODE IN VARCHAR2,
444 P_DEFAULT_LEDGER_SHORT_NAME IN VARCHAR2,
445 P_REPORT_ID IN NUMBER,
446 P_ROW_SET_ID IN NUMBER,
447 P_COLUMN_SET_ID IN NUMBER,
448 p_PERIOD_NAME IN VARCHAR2,
449 p_UNIT_OF_MEASURE_ID IN VARCHAR2,
450 P_ROUNDING_OPTION IN VARCHAR2,
451 P_SEGMENT_OVERRIDE IN VARCHAR2,
452 P_CONTENT_SET_ID IN NUMBER,
453 P_ROW_ORDER_ID IN NUMBER,
454 P_REPORT_DISPLAY_SET_ID IN NUMBER,
455 P_OUTPUT_OPTION IN VARCHAR2,
456 P_EXCEPTIONS_FLAG IN VARCHAR2,
457 p_MINIMUM_DISPLAY_LEVEL IN NUMBER,
458 p_ACCOUNTING_DATE IN VARCHAR2,
459 P_PARAMETER_SET_ID IN NUMBER,
460 p_PAGE_LENGTH IN NUMBER,
461
462 p_subrequest_id IN NUMBER,
463 P_APPL_DEFLT_NAME IN VARCHAR2,
464
465 p_template IN VARCHAR2,
466 p_template_locale IN VARCHAR2,
467
468
469
470 p_output_format IN VARCHAR2,
471 p_source_charset IN VARCHAR2,
472 p_destination_charset IN VARCHAR2,
473 p_destination_filename IN VARCHAR2,
474 p_source_separator IN VARCHAR2
475 ) IS
476
477 l_procedure_name VARCHAR2(30) := 'Submit_FSG_XML_Report';
478 l_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
479 l_proc_level NUMBER := FND_LOG.Level_Procedure;
480 --l_stmt_level NUMBER := FND_LOG.Level_Statement;
481 l_fsg_request_id NUMBER;
482 l_xml_request_id NUMBER;
483 l_program VARCHAR2(10);
484 l_submit_request_exp EXCEPTION;
485 l_conc_succ BOOLEAN;
486 l_template_appl NUMBER ;--VARCHAR2(50);
487 --result flag
488 l_xml_result_flag VARCHAR2(15);
489 l_charset_result_flag VARCHAR2(15);
490 l_filename_result_flag VARCHAR2(15);
491
492 l_charset_request_id NUMBER;
493 l_filename_request_id NUMBER;
494 BEGIN
495
496 --log for debug
497 IF (l_proc_level >= l_dbg_level) THEN
498 FND_LOG.STRING(l_proc_level,
499 g_module_name || '.' || l_procedure_name || '.begin',
500 'begin procedure');
501 END IF; --( g_proc_level >= g_dbg_level)
502 --call JA_CN_UTILITY.Check_Profile, if it doesn't return true, exit
503 IF JA_CN_UTILITY.Check_Profile() <> TRUE THEN
504 IF (l_proc_level >= l_dbg_level) THEN
505 FND_LOG.STRING(l_proc_level,
506 l_procedure_name,
507 'Check profile failed!');
508 END IF; --l_exception_level >= l_runtime_level
509 retcode := 1;
510 errbuf := '';
511 RETURN;
512 END IF; --JA_CN_UTILITY.Check_Profile() != TRUE
513
514 -- Submit FSG report
515 IF
516 Submit_FSG_Request('JA',
517 P_data_access_set_id,
518 l_fsg_request_id,
519 l_program,
520
521 P_COA_ID,
522 P_ADHOC_PREFIX,
523 P_INDUSTRY,
524 P_FLEX_CODE,
525 P_DEFAULT_LEDGER_SHORT_NAME,
526 P_REPORT_ID,
527 P_ROW_SET_ID,
528 P_COLUMN_SET_ID,
529 p_PERIOD_NAME,
530 p_UNIT_OF_MEASURE_ID,
531 P_ROUNDING_OPTION,
532 P_SEGMENT_OVERRIDE,
533 P_CONTENT_SET_ID ,
534 P_ROW_ORDER_ID,
535 P_REPORT_DISPLAY_SET_ID,
536 P_OUTPUT_OPTION,
537 P_EXCEPTIONS_FLAG,
538 p_MINIMUM_DISPLAY_LEVEL,
539 to_date(p_ACCOUNTING_DATE, 'YYYY/MM/DD HH24:MI:SS'),
540
541 P_PARAMETER_SET_ID,
542 p_PAGE_LENGTH,
543
544 p_subrequest_id,
545 P_APPL_DEFLT_NAME
546
547 )
548 THEN
549 -- Get template application id
550 SELECT APP.APPLICATION_ID
551 INTO l_template_appl
552 FROM FND_APPLICATION_VL APP,
553 FND_CONCURRENT_PROGRAMS FCP,
554 FND_CONCURRENT_REQUESTS R
555 WHERE FCP.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
556 AND R.REQUEST_ID = l_fsg_request_id
557 AND APP.APPLICATION_ID = FCP.APPLICATION_ID;
558 --submit XML publisher concurrent program
559
560 submit_xml_publisher(l_fsg_request_id,
561 l_template_appl,
562 p_template,
563 p_template_locale,
564 p_output_format,
565
566 l_xml_request_id,
567 l_xml_result_flag);
568 IF l_xml_result_flag = 'Error'
569 THEN
570 errbuf := fnd_message.get;
571 retcode := 2;
572 RAISE l_submit_request_exp;
573 ELSE
574 -- submit charset conversion concurrent program
575 JA_CN_UTILITY.submit_charset_conversion(l_xml_request_id,
576 p_source_charset,
577 p_destination_charset,
578 p_source_separator,
579 l_charset_request_id,
580 l_charset_result_flag);
581
582 IF l_charset_result_flag = 'Error'
583 THEN
584 errbuf := fnd_message.get;
585 retcode := 2;
586 RAISE l_submit_request_exp;
587 ELSE
588 --submit change output filename concurrent program
589 JA_CN_UTILITY.change_output_filename(l_xml_request_id,
590 p_destination_charset,
591 p_destination_filename,
592 l_filename_request_id,
593 l_filename_result_flag);
594 IF l_filename_result_flag = 'Error'
595 THEN
596 errbuf := fnd_message.get;
597 retcode := 2;
598 RAISE l_submit_request_exp;
599 END IF; --l_filename_result_flag = 'Error'
600 END IF; --l_charset_result_flag = 'Error'
601 END IF; --l_xml_error_flag = 'Error'
602 END IF;
603
604
605 IF (l_proc_level >= l_dbg_level)
606 THEN
607 FND_LOG.STRING(l_proc_level,
608 g_module_name || '.' || l_procedure_name || '.end',
609 'end procedure');
610 END IF; --( g_proc_level >= g_dbg_level)
611 EXCEPTION
612 WHEN l_submit_request_exp
613 THEN
614 fnd_file.put_line(fnd_file.output, SQLCODE || ':' || SQLERRM);
615 l_conc_succ := fnd_concurrent.set_completion_status(status => 'ERROR',
616 message => SQLCODE || ':' ||
617 SQLERRM);
618 --log for debug
619 IF (l_proc_level >= l_dbg_level)
620 THEN
621 fnd_log.STRING(l_proc_level,
622 g_module_name || '.' || l_procedure_name ||
623 '. Submit_Request_Exception ',
624 SQLCODE || ':' || SQLERRM);
625 END IF; --(l_proc_level >= l_dbg_level)
626 WHEN OTHERS THEN
627 fnd_file.put_line(fnd_file.output, SQLCODE || ':' || SQLERRM);
628 l_conc_succ := fnd_concurrent.set_completion_status(status => 'ERROR',
629 message => SQLCODE || ':' ||
630 SQLERRM);
631 --log for debug
632 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
633 THEN
634 fnd_log.STRING(fnd_log.level_unexpected,
635 g_module_name || l_procedure_name ||
636 '. OTHER_EXCEPTION ',
637 SQLCODE || SQLERRM);
638 END IF;
639 RAISE;
640 END Submit_FSG_XML_Report;
641
642 --==========================================================================
643 -- PROCEDURE NAME:
644 -- Submit_FSG_Report Private
645 --
646 -- DESCRIPTION:
647 -- This procedure is used to submit FSG reports, and then invoke the
648 -- 'Report Item Data Convert program' to covert the FSG output.
649 -- PARAMETERS:
650 -- In:
651 -- pn_data_access_set_id Data Acess ID
652 -- pn_coa_id Chart of Accounts ID
653 -- pv_adhoc_prefix ADHOC PREFIX
654 -- pv_industry Industry
655 -- pv_flex_code Flex Code
656 -- pv_default_ledger_short_name Default Ledger short Name
657 -- pv_period_name Period Name
658 -- pv_output_option Out Option
659 -- pv_exceptions_flag Exception
660 -- pv_accounting_date Accounting Date
661 -- pn_page_length Page Lenth
662 -- pn_subrequest_id SubRequest ID
663 -- pv_appl_deflt_name Application Default Name
664 -- pn_csf_report_id Cash Flow Statement Report ID
665 -- pn_csf_report_num Cash Flow Statement Report Number
666 -- pn_balance_sheet_id Balance Sheet Report ID
667 -- pn_balance_sheet_num Balance Sheet Report Number
668 -- pn_profit_statement_id Profit Statement Report ID
669 -- pn_profit_statement_num Profit Statement Report Number
670 -- pn_owners_equity_id Owners Equity Report ID
671 -- pn_owners_equity_num Owners Equity Report Number
672 -- pv_balance_type Balance Type
673 -- pn_legal_entity_id Legal Entity ID
674 -- pn_ledger_id Ledger ID
675 -- pv_currency_unit Currency Unit
676 -- pv_xml_template_language XML template language
677 -- pv_xml_template_territory XML template territory
678 -- pv_xml_output_format XML output format
679 --
680 -- Out:
681 -- pn_convert_reqid Report Item Data Convert program
682 --
683 -- DESIGN REFERENCES:
684 -- None
685 --
686 -- CHANGE HISTORY:
687 -- 08/02/2010 Wei Huang Created
688 --===========================================================================
689 PROCEDURE Submit_FSG_Report( pn_data_access_set_id IN NUMBER,
690 pn_coa_id IN NUMBER,
691 pv_adhoc_prefix IN VARCHAR2,
692 pv_industry IN VARCHAR2,
693 pv_flex_code IN VARCHAR2,
694 pv_default_ledger_short_name IN VARCHAR2,
695 pv_period_name IN VARCHAR2,
696 pv_output_option IN VARCHAR2,
697 pv_exceptions_flag IN VARCHAR2,
698 pv_accounting_date IN VARCHAR2,
699 pn_page_length IN NUMBER,
700 pn_subrequest_id IN NUMBER,
701 pv_appl_deflt_name IN VARCHAR2,
702 pn_csf_report_id IN NUMBER,
703 pn_csf_report_num IN NUMBER,
704 pn_balance_sheet_id IN NUMBER,
705 pn_balance_sheet_num IN NUMBER,
706 pn_profit_statement_id IN NUMBER,
707 pn_profit_statement_num IN NUMBER,
708 pn_owners_equity_id IN NUMBER,
709 pn_owners_equity_num IN NUMBER,
710 pv_balance_type IN VARCHAR2,
711 pv_currency_unit IN VARCHAR2,
712 pn_legal_entity_id IN NUMBER,
713 pn_ledger_id IN NUMBER,
714 pv_xml_template_language IN VARCHAR2,
715 pv_xml_template_territory IN VARCHAR2,
716 pv_xml_output_format IN VARCHAR2,
717 pn_convert_reqid OUT NOCOPY NUMBER
718 ) IS
719
720 lv_procedure_name VARCHAR2(30) := 'Submit_FSG_XML_Report';
721 ln_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
722 ln_proc_level NUMBER := FND_LOG.Level_Procedure;
723
724 lv_program VARCHAR2(10);
725 le_submit_request_exp EXCEPTION;
726 lb_conc_succ BOOLEAN;
727
728 ln_row_set_id NUMBER;
729 ln_clolumn_set_id NUMBER;
730 lv_unit_of_measure_id VARCHAR2(15);
731 lv_rounding_option VARCHAR2(80);
732 lv_segment_override VARCHAR2(240);
733 ln_content_set_id NUMBER;
734 ln_row_order_id NUMBER;
735 ln_report_display_set_id NUMBER;
736 ln_minimum_display_level NUMBER;
737 ln_parameter_set_id NUMBER;
738
739 ln_csf_comb_req_id NUMBER;
740 ln_balance_sheet_req_id NUMBER;
741 ln_profit_statement_req_id NUMBER;
742 ln_owners_equity_req_id NUMBER;
743
744 lv_error_flag VARCHAR2(1) := 'N';
745 lb_error_status BOOLEAN;
746 lb_submit_succ BOOLEAN;
747
748 ln_reqid_fsg NUMBER;
749 lv_company_name VARCHAR2(100);
750 ln_reqid_cal NUMBER;
751 ln_waiting_interval NUMBER :=10;
752 lv_dev_phase VARCHAR2(100);
753 lv_dev_status VARCHAR2(100);
754 lv_message VARCHAR2(1000);
755 lb_xml_layout BOOLEAN;
756
757 lv_fsg_req_phase fnd_lookup_values.meaning%TYPE;
758 lv_fsg_req_status fnd_lookup_values.meaning%TYPE;
759 lv_fsg_req_status_code fnd_lookup_values.lookup_code%TYPE;
760
761 lv_cal_req_phase fnd_lookup_values.meaning%TYPE;
762 lv_cal_req_status fnd_lookup_values.meaning%TYPE;
763 lv_cal_req_status_code fnd_lookup_values.lookup_code%TYPE;
764
765 lv_comb_req_phase fnd_lookup_values.meaning%TYPE;
766 lv_comb_req_status fnd_lookup_values.meaning%TYPE;
767 lv_comb_req_status_code fnd_lookup_values.lookup_code%TYPE;
768
769 ln_reqid_convert NUMBER;
770
771 lv_convert_req_phase fnd_lookup_values.meaning%TYPE;
772 lv_convert_req_status fnd_lookup_values.meaning%TYPE;
773 lv_convert_req_status_code fnd_lookup_values.lookup_code%TYPE;
774
775
776 CURSOR C_COMPANY_NAME
777 IS
778 SELECT COMPANY_NAME
779 INTO Lv_company_name
780 FROM JA_CN_SYSTEM_PARAMETERS_ALL
781 WHERE LEGAL_ENTITY_ID= pn_legal_entity_id;
782
783 BEGIN
784 --log for debug
785 IF (ln_proc_level >= ln_dbg_level) THEN
786 FND_LOG.STRING(ln_proc_level,
787 g_module_name || '.' || lv_procedure_name || '.begin',
788 'begin procedure');
789 END IF; --( g_proc_level >= g_dbg_level)
790
791 --Call JA_CN_UTILITY.Check_Profile, if it doesn't return true, exit
792 --Profiles include ' JG: Product', which should be set to
793 --'Asia/Pacific Localizations', 'JG: Territory', which should be
794 --set to 'China' and 'JA: CNAO Legal Entity', which should be NOT NULL.
795 IF JA_CN_UTILITY.Check_Profile() <> TRUE THEN
796 IF (ln_proc_level >= ln_dbg_level) THEN
797 FND_LOG.STRING(ln_proc_level,
798 lv_procedure_name,
799 'Check profile failed!');
800 END IF; --l_exception_level >= l_runtime_level
801 --retcode := 1;
802 --errbuf := '';
803 RETURN;
804 END IF; --JA_CN_UTILITY.Check_Profile() != TRUE
805
806 --(1) Submit Balance Sheet Report and get this program request ID.
807 --ROW_SET_ID
808 SELECT rs.axis_set_id
809 INTO ln_row_set_id
810 FROM rg_report_axis_sets rs, rg_reports rp
811 WHERE rp.report_id = pn_balance_sheet_id
812 AND rp.row_set_id = rs.axis_set_id;
813 --COLUMN_SET_ID
814 SELECT cs.axis_set_id
815 INTO ln_clolumn_set_id
816 FROM rg_report_axis_sets cs, rg_reports rp
817 WHERE rp.report_id = pn_balance_sheet_id
818 AND rp.column_set_id = cs.axis_set_id;
819 --Unit of Measure ID/currency
820 BEGIN
821 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
822 INTO lv_unit_of_measure_id
823 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
824 WHERE ldg.ledger_id = acc.default_ledger_id
825 AND acc.access_set_id = pn_data_access_set_id
826 AND rp.report_id = pn_balance_sheet_id;
827 EXCEPTION
828 WHEN NO_DATA_FOUND THEN
829 NULL;
830 END;
831 -- Rounding Option
832 BEGIN
833 SELECT l.meaning
834 INTO lv_rounding_option
835 FROM rg_lookups l, rg_reports r
836 WHERE r.report_id = pn_balance_sheet_id
837 AND l.lookup_type = 'ROUNDING_OPTION'
838 AND l.lookup_code = r.rounding_option;
839 EXCEPTION
840 WHEN NO_DATA_FOUND THEN
841 NULL;
842 END;
843 -- Segment Override
844 BEGIN
845 SELECT segment_override
846 INTO lv_segment_override
847 FROM rg_reports
848 WHERE report_id = pn_balance_sheet_id;
849 EXCEPTION
850 WHEN NO_DATA_FOUND THEN
851 NULL;
852 END;
853 --CONTENT_SET_ID
854 BEGIN
855 SELECT cs.content_set_id
856 INTO ln_content_set_id
857 FROM rg_report_content_sets cs, rg_reports rp
858 WHERE rp.report_id = pn_balance_sheet_id
859 AND rp.content_set_id = cs.content_set_id(+);
860 EXCEPTION
861 WHEN NO_DATA_FOUND THEN
862 NULL;
863 END;
864 --ROW_ORDER_ID
865 BEGIN
866 SELECT ro.row_order_id
867 INTO ln_row_order_id
868 FROM rg_row_orders ro, rg_reports rp
869 WHERE rp.report_id = pn_balance_sheet_id
870 AND rp.row_order_id = ro.row_order_id(+);
871 EXCEPTION
872 WHEN NO_DATA_FOUND THEN
873 NULL;
874 END;
875 --REPORT_DISPLAY_SET_ID
876 BEGIN
877 SELECT ds.report_display_set_id
878 INTO ln_report_display_set_id
879 FROM rg_report_display_sets ds, rg_reports rp
880 WHERE rp.report_id = pn_balance_sheet_id
881 AND rp.report_display_set_id = ds.report_display_set_id(+);
882 EXCEPTION
883 WHEN NO_DATA_FOUND THEN
884 NULL;
885 END;
886 --MINIMUM_DISPLAY_LEVEL
887 BEGIN
888 SELECT lk.lookup_code
889 INTO ln_minimum_display_level
890 FROM rg_reports rp, rg_lookups lk
891 WHERE rp.report_id = pn_balance_sheet_id
892 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
893 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
894 EXCEPTION
895 WHEN NO_DATA_FOUND THEN
896 NULL;
897 END;
898 --PARAMETER_SET_ID
899 BEGIN
900 SELECT rp.parameter_set_id
901 INTO ln_parameter_set_id
902 FROM rg_reports rp
903 WHERE rp.report_id = pn_balance_sheet_id;
904 EXCEPTION
905 WHEN NO_DATA_FOUND THEN
906 NULL;
907 END;
908
909 -- Submit Balance Sheet Report
910 lb_submit_succ := Submit_FSG_Request('JA',
911 pn_data_access_set_id,
912 ln_balance_sheet_req_id,
913 lv_program,
914 pn_coa_id,
915 pv_adhoc_prefix,
916 pv_industry,
917 pv_flex_code,
918 pv_default_ledger_short_name,
919 pn_balance_sheet_id,
920 ln_row_set_id,
921 ln_clolumn_set_id,
922 pv_period_name,
923 lv_unit_of_measure_id,
924 lv_rounding_option,
925 lv_segment_override,
926 ln_content_set_id,
927 ln_row_order_id,
928 ln_report_display_set_id,
929 pv_output_option,
930 pv_exceptions_flag,
931 ln_minimum_display_level,
932 to_date(pv_accounting_date, 'YYYY/MM/DD HH24:MI:SS'),
933 ln_parameter_set_id,
934 pn_page_length,
935 pn_subrequest_id,
936 pv_appl_deflt_name
937 );
938
939 --(2) Submit Profit Statement Report and get this program request ID.
940 --ROW_SET_ID
941 SELECT rs.axis_set_id
942 INTO ln_row_set_id
943 FROM rg_report_axis_sets rs, rg_reports rp
944 WHERE rp.report_id = pn_profit_statement_id
945 AND rp.row_set_id = rs.axis_set_id;
946 --COLUMN_SET_ID
947 SELECT cs.axis_set_id
948 INTO ln_clolumn_set_id
949 FROM rg_report_axis_sets cs, rg_reports rp
950 WHERE rp.report_id = pn_profit_statement_id
951 AND rp.column_set_id = cs.axis_set_id;
952 --Unit of Measure ID/currency
953 BEGIN
954 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
955 INTO lv_unit_of_measure_id
956 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
957 WHERE ldg.ledger_id = acc.default_ledger_id
958 AND acc.access_set_id = pn_profit_statement_id
959 AND rp.report_id = pn_balance_sheet_id;
960 EXCEPTION
961 WHEN NO_DATA_FOUND THEN
962 NULL;
963 END;
964 -- Rounding Option
965 BEGIN
966 SELECT l.meaning
967 INTO lv_rounding_option
968 FROM rg_lookups l, rg_reports r
969 WHERE r.report_id = pn_profit_statement_id
970 AND l.lookup_type = 'ROUNDING_OPTION'
971 AND l.lookup_code = r.rounding_option;
972 EXCEPTION
973 WHEN NO_DATA_FOUND THEN
974 NULL;
975 END;
976 -- Segment Override
977 BEGIN
978 SELECT segment_override
979 INTO lv_segment_override
980 FROM rg_reports
981 WHERE report_id = pn_profit_statement_id;
982 EXCEPTION
983 WHEN NO_DATA_FOUND THEN
984 NULL;
985 END;
986 --CONTENT_SET_ID
987 BEGIN
988 SELECT cs.content_set_id
989 INTO ln_content_set_id
990 FROM rg_report_content_sets cs, rg_reports rp
991 WHERE rp.report_id = pn_profit_statement_id
992 AND rp.content_set_id = cs.content_set_id(+);
993 EXCEPTION
994 WHEN NO_DATA_FOUND THEN
995 NULL;
996 END;
997 --ROW_ORDER_ID
998 BEGIN
999 SELECT ro.row_order_id
1000 INTO ln_row_order_id
1001 FROM rg_row_orders ro, rg_reports rp
1002 WHERE rp.report_id = pn_profit_statement_id
1003 AND rp.row_order_id = ro.row_order_id(+);
1004 EXCEPTION
1005 WHEN NO_DATA_FOUND THEN
1006 NULL;
1007 END;
1008 --REPORT_DISPLAY_SET_ID
1009 BEGIN
1010 SELECT ds.report_display_set_id
1011 INTO ln_report_display_set_id
1012 FROM rg_report_display_sets ds, rg_reports rp
1013 WHERE rp.report_id = pn_profit_statement_id
1014 AND rp.report_display_set_id = ds.report_display_set_id(+);
1015 EXCEPTION
1016 WHEN NO_DATA_FOUND THEN
1017 NULL;
1018 END;
1019 --MINIMUM_DISPLAY_LEVEL
1020 BEGIN
1021 SELECT lk.lookup_code
1022 INTO ln_minimum_display_level
1023 FROM rg_reports rp, rg_lookups lk
1024 WHERE rp.report_id = pn_profit_statement_id
1025 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
1026 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
1027 EXCEPTION
1028 WHEN NO_DATA_FOUND THEN
1029 NULL;
1030 END;
1031 --PARAMETER_SET_ID
1032 BEGIN
1033 SELECT rp.parameter_set_id
1034 INTO ln_parameter_set_id
1035 FROM rg_reports rp
1036 WHERE rp.report_id = pn_profit_statement_id;
1037 EXCEPTION
1038 WHEN NO_DATA_FOUND THEN
1039 NULL;
1040 END;
1041
1042 -- Submit Profit Statement Report
1043 lb_submit_succ := Submit_FSG_Request('JA',
1044 pn_data_access_set_id,
1045 ln_profit_statement_req_id,
1046 lv_program,
1047 pn_coa_id,
1048 pv_adhoc_prefix,
1049 pv_industry,
1050 pv_flex_code,
1051 pv_default_ledger_short_name,
1052 pn_profit_statement_id,
1053 ln_row_set_id,
1054 ln_clolumn_set_id,
1055 pv_period_name,
1056 lv_unit_of_measure_id,
1057 lv_rounding_option,
1058 lv_segment_override,
1059 ln_content_set_id,
1060 ln_row_order_id,
1061 ln_report_display_set_id,
1062 pv_output_option,
1063 pv_exceptions_flag,
1064 ln_minimum_display_level,
1065 to_date(pv_accounting_date, 'YYYY/MM/DD HH24:MI:SS'),
1066 ln_parameter_set_id,
1067 pn_page_length,
1068 pn_subrequest_id,
1069 pv_appl_deflt_name
1070 );
1071
1072
1073 --(3) Submit Profit Statement Report and get this program request ID.
1074 --ROW_SET_ID
1075 SELECT rs.axis_set_id
1076 INTO ln_row_set_id
1077 FROM rg_report_axis_sets rs, rg_reports rp
1078 WHERE rp.report_id = pn_owners_equity_id
1079 AND rp.row_set_id = rs.axis_set_id;
1080 --COLUMN_SET_ID
1081 SELECT cs.axis_set_id
1082 INTO ln_clolumn_set_id
1083 FROM rg_report_axis_sets cs, rg_reports rp
1084 WHERE rp.report_id = pn_owners_equity_id
1085 AND rp.column_set_id = cs.axis_set_id;
1086 --Unit of Measure ID/currency
1087 BEGIN
1088 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
1089 INTO lv_unit_of_measure_id
1090 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
1091 WHERE ldg.ledger_id = acc.default_ledger_id
1092 AND acc.access_set_id = pn_owners_equity_id
1093 AND rp.report_id = pn_balance_sheet_id;
1094 EXCEPTION
1095 WHEN NO_DATA_FOUND THEN
1096 NULL;
1097 END;
1098 -- Rounding Option
1099 BEGIN
1100 SELECT l.meaning
1101 INTO lv_rounding_option
1102 FROM rg_lookups l, rg_reports r
1103 WHERE r.report_id = pn_owners_equity_id
1104 AND l.lookup_type = 'ROUNDING_OPTION'
1105 AND l.lookup_code = r.rounding_option;
1106 EXCEPTION
1107 WHEN NO_DATA_FOUND THEN
1108 NULL;
1109 END;
1110 -- Segment Override
1111 BEGIN
1112 SELECT segment_override
1113 INTO lv_segment_override
1114 FROM rg_reports
1115 WHERE report_id = pn_owners_equity_id;
1116 EXCEPTION
1117 WHEN NO_DATA_FOUND THEN
1118 NULL;
1119 END;
1120 --CONTENT_SET_ID
1121 BEGIN
1122 SELECT cs.content_set_id
1123 INTO ln_content_set_id
1124 FROM rg_report_content_sets cs, rg_reports rp
1125 WHERE rp.report_id = pn_owners_equity_id
1126 AND rp.content_set_id = cs.content_set_id(+);
1127 EXCEPTION
1128 WHEN NO_DATA_FOUND THEN
1129 NULL;
1130 END;
1131 --ROW_ORDER_ID
1132 BEGIN
1133 SELECT ro.row_order_id
1134 INTO ln_row_order_id
1135 FROM rg_row_orders ro, rg_reports rp
1136 WHERE rp.report_id = pn_owners_equity_id
1137 AND rp.row_order_id = ro.row_order_id(+);
1138 EXCEPTION
1139 WHEN NO_DATA_FOUND THEN
1140 NULL;
1141 END;
1142 --REPORT_DISPLAY_SET_ID
1143 BEGIN
1144 SELECT ds.report_display_set_id
1145 INTO ln_report_display_set_id
1146 FROM rg_report_display_sets ds, rg_reports rp
1147 WHERE rp.report_id = pn_owners_equity_id
1148 AND rp.report_display_set_id = ds.report_display_set_id(+);
1149 EXCEPTION
1150 WHEN NO_DATA_FOUND THEN
1151 NULL;
1152 END;
1153 --MINIMUM_DISPLAY_LEVEL
1154 BEGIN
1155 SELECT lk.lookup_code
1156 INTO ln_minimum_display_level
1157 FROM rg_reports rp, rg_lookups lk
1158 WHERE rp.report_id = pn_owners_equity_id
1159 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
1160 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
1161 EXCEPTION
1162 WHEN NO_DATA_FOUND THEN
1163 NULL;
1164 END;
1165 --PARAMETER_SET_ID
1166 BEGIN
1167 SELECT rp.parameter_set_id
1168 INTO ln_parameter_set_id
1169 FROM rg_reports rp
1170 WHERE rp.report_id = pn_owners_equity_id;
1171 EXCEPTION
1172 WHEN NO_DATA_FOUND THEN
1173 NULL;
1174 END;
1175
1176 -- Submit Profit Statement Report
1177 lb_submit_succ := Submit_FSG_Request('JA',
1178 pn_data_access_set_id,
1179 ln_owners_equity_req_id,
1180 lv_program,
1181 pn_coa_id,
1182 pv_adhoc_prefix,
1183 pv_industry,
1184 pv_flex_code,
1185 pv_default_ledger_short_name,
1186 pn_owners_equity_id,
1187 ln_row_set_id,
1188 ln_clolumn_set_id,
1189 pv_period_name,
1190 lv_unit_of_measure_id,
1191 lv_rounding_option,
1192 lv_segment_override,
1193 ln_content_set_id,
1194 ln_row_order_id,
1195 ln_report_display_set_id,
1196 pv_output_option,
1197 pv_exceptions_flag,
1198 ln_minimum_display_level,
1199 to_date(pv_accounting_date, 'YYYY/MM/DD HH24:MI:SS'),
1200 ln_parameter_set_id,
1201 pn_page_length,
1202 pn_subrequest_id,
1203 pv_appl_deflt_name
1204 );
1205
1206 --(4) Invoke the Cash Flow Statement - Generation Program
1207 -- The Generation program invoke the Cash Flow Statement Combination Program,
1208 -- then get the Combination Program request ID.
1209 --ROW_SET_ID
1210 SELECT rs.axis_set_id
1211 INTO ln_row_set_id
1212 FROM rg_report_axis_sets rs, rg_reports rp
1213 WHERE rp.report_id = pn_csf_report_id
1214 AND rp.row_set_id = rs.axis_set_id;
1215 --COLUMN_SET_ID
1216 SELECT cs.axis_set_id
1217 INTO ln_clolumn_set_id
1218 FROM rg_report_axis_sets cs, rg_reports rp
1219 WHERE rp.report_id = pn_csf_report_id
1220 AND rp.column_set_id = cs.axis_set_id;
1221 --Unit of Measure ID/currency
1222 BEGIN
1223 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
1224 INTO lv_unit_of_measure_id
1225 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
1226 WHERE ldg.ledger_id = acc.default_ledger_id
1227 AND acc.access_set_id = pn_csf_report_id
1228 AND rp.report_id = pn_balance_sheet_id;
1229 EXCEPTION
1230 WHEN NO_DATA_FOUND THEN
1231 NULL;
1232 END;
1233 -- Rounding Option
1234 BEGIN
1235 SELECT l.meaning
1236 INTO lv_rounding_option
1237 FROM rg_lookups l, rg_reports r
1238 WHERE r.report_id = pn_csf_report_id
1239 AND l.lookup_type = 'ROUNDING_OPTION'
1240 AND l.lookup_code = r.rounding_option;
1241 EXCEPTION
1242 WHEN NO_DATA_FOUND THEN
1243 NULL;
1244 END;
1245 -- Segment Override
1246 BEGIN
1247 SELECT segment_override
1248 INTO lv_segment_override
1249 FROM rg_reports
1250 WHERE report_id = pn_csf_report_id;
1251 EXCEPTION
1252 WHEN NO_DATA_FOUND THEN
1253 NULL;
1254 END;
1255 --CONTENT_SET_ID
1256 BEGIN
1257 SELECT cs.content_set_id
1258 INTO ln_content_set_id
1259 FROM rg_report_content_sets cs, rg_reports rp
1260 WHERE rp.report_id = pn_csf_report_id
1261 AND rp.content_set_id = cs.content_set_id(+);
1262 EXCEPTION
1263 WHEN NO_DATA_FOUND THEN
1264 NULL;
1265 END;
1266 --ROW_ORDER_ID
1267 BEGIN
1268 SELECT ro.row_order_id
1269 INTO ln_row_order_id
1270 FROM rg_row_orders ro, rg_reports rp
1271 WHERE rp.report_id = pn_csf_report_id
1272 AND rp.row_order_id = ro.row_order_id(+);
1273 EXCEPTION
1274 WHEN NO_DATA_FOUND THEN
1275 NULL;
1276 END;
1277 --REPORT_DISPLAY_SET_ID
1278 BEGIN
1279 SELECT ds.report_display_set_id
1280 INTO ln_report_display_set_id
1281 FROM rg_report_display_sets ds, rg_reports rp
1282 WHERE rp.report_id = pn_csf_report_id
1283 AND rp.report_display_set_id = ds.report_display_set_id(+);
1284 EXCEPTION
1285 WHEN NO_DATA_FOUND THEN
1286 NULL;
1287 END;
1288 --MINIMUM_DISPLAY_LEVEL
1289 BEGIN
1290 SELECT lk.lookup_code
1291 INTO ln_minimum_display_level
1292 FROM rg_reports rp, rg_lookups lk
1293 WHERE rp.report_id = pn_csf_report_id
1294 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
1295 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
1296 EXCEPTION
1297 WHEN NO_DATA_FOUND THEN
1298 NULL;
1299 END;
1300 --PARAMETER_SET_ID
1301 BEGIN
1302 SELECT rp.parameter_set_id
1303 INTO ln_parameter_set_id
1304 FROM rg_reports rp
1305 WHERE rp.report_id = pn_csf_report_id;
1306 EXCEPTION
1307 WHEN NO_DATA_FOUND THEN
1308 NULL;
1309 END;
1310
1311
1312 --(4.1) Submit the first concurrent program 'Cash Flow Statement - FSG',which
1313 --will automatically submit another request for the CFS report to generate XML file
1314 OPEN C_COMPANY_NAME;
1315 FETCH C_COMPANY_NAME INTO lv_company_name;
1316 CLOSE C_COMPANY_NAME;
1317
1318 ln_reqid_fsg:=FND_REQUEST.Submit_Request( 'SQLGL'
1319 ,'RGRARG'
1320 ,''
1321 ,''
1322 ,FALSE
1323 ,pn_data_access_set_id
1324 ,pn_coa_id
1325 ,pv_adhoc_prefix
1326 ,pv_industry
1327 ,pv_flex_code
1328 ,pv_default_ledger_short_name
1329 ,pn_csf_report_id
1330 ,ln_row_set_id
1331 ,ln_clolumn_set_id
1332 ,pv_period_name
1333 ,lv_unit_of_measure_id
1334 ,lv_rounding_option
1335 ,lv_segment_override
1336 ,ln_content_set_id
1337 ,ln_row_order_id
1338 ,ln_report_display_set_id
1339 ,pv_output_option
1340 ,pv_exceptions_flag
1341 ,ln_minimum_display_level
1342 ,pv_accounting_date
1343 ,ln_parameter_set_id
1344 ,pn_page_length
1345 ,pn_subrequest_id
1346 ,pv_appl_deflt_name
1347 ,lv_company_name,
1348 '', '', '', '', '',
1349 '', '', '', '', '', '', '', '', '', '',
1350 '', '', '', '', '', '', '', '', '', '',
1351 '', '', '', '', '', '', '', '', '', '',
1352 '', '', '', '', '', '', '', '', '', '',
1353 '', '', '', '', '', '', '', '', '', '',
1354 '', '', '', '', '', '', '', '', '', '',
1355 '', '', '', '', '', '', '', '', '', ''
1356 );
1357
1358 IF (pv_output_option = 'Y') THEN
1359 UPDATE FND_CONCURRENT_REQUESTS
1360 SET
1361 OUTPUT_FILE_TYPE = 'XML'
1362 WHERE
1363 REQUEST_ID = ln_reqid_fsg;
1364 END IF;
1365 COMMIT;
1366
1367 --(4.2)Waiting for the 'Cash Flow Statement - FSG' completed, and then retrive the
1368 --sub-request id from the table 'fnd_concurrent_requests' for the FSG - CFS report.
1369 IF ln_reqid_fsg<>0
1370 THEN
1371 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_fsg
1372 ,interval => ln_waiting_interval
1373 ,phase => lv_fsg_req_phase
1374 ,status => lv_fsg_req_status
1375 ,dev_phase => lv_dev_phase
1376 ,dev_status => lv_dev_status
1377 ,message => lv_message
1378 )
1379 THEN
1380 --To get lookup code for current status
1381 SELECT
1382 lookup_code
1383 INTO
1384 lv_fsg_req_status_code
1385 FROM
1386 fnd_lookup_values
1387 WHERE lookup_type = 'CP_STATUS_CODE'
1388 AND view_application_id=0
1389 AND security_group_id=0
1390 AND meaning=lv_fsg_req_status
1391 AND enabled_flag='Y'
1392 AND language = USERENV('LANG');
1393
1394 --To judge if the Program - Run Financial Statement Generator'
1395 --has been completed successfully.
1396 --Completed with 'Normal'
1397 IF lv_fsg_req_status_code='C'
1398 THEN
1399 null;
1400 --Completed with 'Warning'
1401 ELSIF lv_fsg_req_status_code='G'
1402 THEN
1403 lv_error_flag:='W';
1404 --Completed with 'Error'
1405 ELSIF lv_fsg_req_status_code='E'
1406 THEN
1407 lv_error_flag:='E';
1408 END IF;
1409 END IF;
1410 ELSE
1411 lv_error_flag:='E';
1412 END IF;
1413
1414
1415 --(4.3)If FSG xml output request for cfs is successfully completed,
1416 -- then submit the second concurrent program 'Cash Flow Statement - Calculation'.
1417 IF nvl(ln_reqid_fsg,0)<>0 AND lv_error_flag='N'
1418 THEN
1419 ln_reqid_cal:=FND_REQUEST.Submit_Request(application => 'JA'
1420 ,program => 'JACNCFSN'
1421 ,argument1 => pn_legal_entity_id
1422 ,argument2 => pn_ledger_id
1423 ,argument3 => pv_period_name
1424 ,argument4 => ln_row_set_id
1425 ,argument5 => lv_rounding_option
1426 ,argument6 => pv_balance_type
1427 ,argument7 => pn_coa_id
1428 ,argument8 => lv_segment_override
1429 );
1430 COMMIT;
1431
1432 --(4.4)Waiting for the 'Cash flow statement - Calculation' successfully completed
1433 -- and then submit concurrent program 'Cash Flow Statement - Combination'.
1434 IF ln_reqid_cal<>0
1435 THEN
1436 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_cal
1437 ,interval => ln_waiting_interval
1438 ,phase => lv_cal_req_phase
1439 ,status => lv_cal_req_status
1440 ,dev_phase => lv_dev_phase
1441 ,dev_status => lv_dev_status
1442 ,message => lv_message
1443 )
1444 THEN
1445 --To get lookup code for current status
1446 SELECT
1447 lookup_code
1448 INTO
1449 lv_cal_req_status_code
1450 FROM
1451 fnd_lookup_values
1452 WHERE lookup_type = 'CP_STATUS_CODE'
1453 AND view_application_id=0
1454 AND security_group_id=0
1455 AND meaning=lv_cal_req_status
1456 AND enabled_flag='Y'
1457 AND language = USERENV('LANG');
1458
1459 --Completed with successful
1460 IF lv_cal_req_status_code='C'
1461 THEN
1462 --Submit the third concurrent program 'Cash flow statement - Combination'
1463 lb_xml_layout := FND_REQUEST.Add_Layout(template_appl_name => 'JA'
1464 ,template_code => 'JACNCFSC'
1465 ,template_language => pv_xml_template_language --'zh' ('en')
1466 ,template_territory => pv_xml_template_territory--'00' ('US')
1467 ,output_format => pv_xml_output_format --'ETEXT'
1468 );
1469
1470 ln_csf_comb_req_id:=FND_REQUEST.Submit_Request(application => 'JA'
1471 ,program => 'JACNCFSC'
1472 ,argument1 => ln_reqid_fsg
1473 ,argument2 => ln_reqid_cal
1474 );
1475 COMMIT;
1476
1477 --(4.5)Waiting for the 'Cash flow statement - Combination' completed
1478 --get its status
1479 IF ln_csf_comb_req_id<>0
1480 THEN
1481 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_csf_comb_req_id
1482 ,interval => ln_waiting_interval
1483 ,phase => lv_comb_req_phase
1484 ,status => lv_comb_req_status
1485 ,dev_phase => lv_dev_phase
1486 ,dev_status => lv_dev_status
1487 ,message => lv_message
1488 )
1489 THEN
1490 --To get lookup code for current status
1491 SELECT
1492 lookup_code
1493 INTO
1494 lv_comb_req_status_code
1495 FROM
1496 fnd_lookup_values
1497 WHERE lookup_type = 'CP_STATUS_CODE'
1498 AND view_application_id=0
1499 AND security_group_id=0
1500 AND meaning=lv_comb_req_status
1501 AND enabled_flag='Y'
1502 AND language = USERENV('LANG');
1503
1504 --Completed with Normal
1505 IF lv_comb_req_status_code='C'
1506 THEN
1507
1508 --dbms_output.put_line('Invoke Report Item Data Convert program');
1509
1510 -- (5) Invoke Report Item Data Convert program to covert the FSG output
1511 ln_reqid_convert:=FND_REQUEST.Submit_Request(application => 'JA'
1512 ,program => 'JACNCDCP'
1513 ,argument1 => ln_csf_comb_req_id
1514 ,argument2 => pn_csf_report_num
1515 ,argument3 => ln_balance_sheet_req_id
1516 ,argument4 => pn_balance_sheet_num
1517 ,argument5 => ln_profit_statement_req_id
1518 ,argument6 => pn_profit_statement_num
1519 ,argument7 => ln_owners_equity_req_id
1520 ,argument8 => pn_owners_equity_num
1521 ,argument9 => pv_currency_unit
1522 ,argument10 => Lv_company_name
1523 );
1524 COMMIT;
1525
1526 --(5.1)Waiting for the 'Report Item Data Convert program' completed
1527 --get its status
1528 IF ln_reqid_convert<>0
1529 THEN
1530 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_convert
1531 ,interval => ln_waiting_interval
1532 ,phase => lv_convert_req_phase
1533 ,status => lv_convert_req_status
1534 ,dev_phase => lv_dev_phase
1535 ,dev_status => lv_dev_status
1536 ,message => lv_message
1537 )
1538 THEN
1539 --To get lookup code for current status
1540 SELECT
1541 lookup_code
1542 INTO
1543 lv_convert_req_status_code
1544 FROM
1545 fnd_lookup_values
1546 WHERE lookup_type = 'CP_STATUS_CODE'
1547 AND view_application_id=0
1548 AND security_group_id=0
1549 AND meaning=lv_comb_req_status
1550 AND enabled_flag='Y'
1551 AND language = USERENV('LANG');
1552
1553 --Completed with Normal
1554 IF lv_convert_req_status_code='C'
1555 THEN
1556 pn_convert_reqid := ln_reqid_convert;
1557 --Completed with 'Warning'
1558 ELSIF lv_convert_req_status_code='G'
1559 THEN
1560 lv_error_flag:='W';
1561 --Completed with 'Error'
1562 ELSIF lv_convert_req_status_code='E'
1563 THEN
1564 lv_error_flag:='E';
1565 END IF; --lv_comb_req_status_code='C'
1566 END IF; --FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_convert
1567 ELSE
1568 lv_error_flag:='E';
1569 END IF; --ln_reqid_convert<>0
1570
1571
1572
1573 --Completed with 'Warning'
1574 ELSIF lv_comb_req_status_code='G'
1575 THEN
1576 lv_error_flag:='W';
1577 --Completed with 'Error'
1578 ELSIF lv_comb_req_status_code='E'
1579 THEN
1580 lv_error_flag:='E';
1581 END IF; --lv_comb_req_status_code='C'
1582 END IF; --FND_CONCURRENT.Wait_For_Request(request_id => l_reqid_comb
1583 ELSE
1584 lv_error_flag:='E';
1585 END IF; --l_reqid_comb<>0
1586
1587 --Completed with 'Warning'
1588 ELSIF lv_cal_req_status_code='G'
1589 THEN
1590 lv_error_flag:='W';
1591 --Completed with 'Error'
1592 ELSIF lv_cal_req_status_code='E'
1593 THEN
1594 lv_error_flag:='E';
1595 END IF;
1596 END IF;
1597 ELSE
1598 lv_error_flag:='E';
1599 END IF;
1600
1601 END IF;
1602
1603 IF lv_error_flag='W'
1604 THEN
1605 lb_error_status:=FND_CONCURRENT.Set_Completion_Status(status => 'WARNING'
1606 ,message => ''
1607 );
1608 ELSIF lv_error_flag='E'
1609 THEN
1610 lb_error_status:=FND_CONCURRENT.Set_Completion_Status(status => 'ERROR'
1611 ,message => ''
1612 );
1613 END IF;
1614
1615 --log for debug
1616 IF (ln_proc_level >= ln_dbg_level)
1617 THEN
1618 FND_LOG.STRING(ln_proc_level
1619 ,g_module_name||'.'||lv_procedure_name
1620 ,lv_error_flag
1621 );
1622 FND_LOG.STRING(ln_proc_level
1623 ,g_module_name||'.'||lv_procedure_name||'.end'
1624 ,'end procedure'
1625 );
1626 END IF;
1627
1628 EXCEPTION
1629 WHEN OTHERS THEN
1630 IF (ln_proc_level >= ln_dbg_level)
1631 THEN
1632 FND_LOG.STRING(ln_proc_level
1633 ,g_module_name||'.'||lv_procedure_name||'EXCEPTION'
1634 ,lv_error_flag
1635 );
1636 FND_LOG.String(ln_proc_level
1637 ,g_module_name||'.'||lv_procedure_name||'. Other_Exception '
1638 ,SQLCODE||':'||SQLERRM
1639 );
1640 END IF;
1641
1642 END Submit_FSG_Report;
1643
1644 --==========================================================================
1645 -- PROCEDURE NAME:
1646 -- Submit_FSG_Report Private
1647 --
1648 -- DESCRIPTION:
1649 -- This procedure is used to submit FSG reports, and then invoke the
1650 -- 'Report Item Data Convert program' to covert the FSG output.
1651 -- PARAMETERS:
1652 -- In:
1653 -- pn_data_access_set_id Data Acess ID
1654 -- pn_coa_id Chart of Accounts ID
1655 -- pv_adhoc_prefix ADHOC PREFIX
1656 -- pv_industry Industry
1657 -- pv_flex_code Flex Code
1658 -- pv_default_ledger_short_name Default Ledger short Name
1659 -- pv_period_name Period Name
1660 -- pv_output_option Out Option
1661 -- pv_exceptions_flag Exception
1662 -- pv_accounting_date Accounting Date
1663 -- pn_page_length Page Lenth
1664 -- pn_subrequest_id SubRequest ID
1665 -- pv_appl_deflt_name Application Default Name
1666 -- pn_csf_report_id Cash Flow Statement Report ID
1667 -- pn_csf_report_num Cash Flow Statement Report Number
1668 -- pn_balance_sheet_id Balance Sheet Report ID
1669 -- pn_balance_sheet_num Balance Sheet Report Number
1670 -- pn_profit_statement_id Profit Statement Report ID
1671 -- pn_profit_statement_num Profit Statement Report Number
1672 -- pn_owners_equity_id Owners Equity Report ID
1673 -- pn_owners_equity_num Owners Equity Report Number
1674 -- pv_balance_type Balance Type
1675 -- pn_legal_entity_id Legal Entity ID
1676 -- pn_ledger_id Ledger ID
1677 -- pv_currency_unit Currency Unit
1678 -- pv_xml_template_language XML template language
1679 -- pv_xml_template_territory XML template territory
1680 -- pv_xml_output_format XML output format
1681 --
1682 -- Out:
1683 --
1684 -- DESIGN REFERENCES:
1685 -- None
1686 --
1687 -- CHANGE HISTORY:
1688 -- 08/02/2010 Wei Huang Created
1689 --===========================================================================
1690 PROCEDURE Submit_FSG_Report( errbuf OUT NOCOPY VARCHAR2,
1691 retcode OUT NOCOPY VARCHAR2,
1692
1693 pn_data_access_set_id IN NUMBER,
1694 pn_coa_id IN NUMBER,
1695 pv_adhoc_prefix IN VARCHAR2,
1696 pv_industry IN VARCHAR2,
1697 pv_flex_code IN VARCHAR2,
1698 pv_default_ledger_short_name IN VARCHAR2,
1699 pv_period_name IN VARCHAR2,
1700 pv_output_option IN VARCHAR2,
1701 pv_exceptions_flag IN VARCHAR2,
1702 pv_accounting_date IN VARCHAR2,
1703 pn_page_length IN NUMBER,
1704 pn_subrequest_id IN NUMBER,
1705 pv_appl_deflt_name IN VARCHAR2,
1706 pn_csf_report_id IN NUMBER,
1707 pn_csf_report_num IN NUMBER,
1708 pn_balance_sheet_id IN NUMBER,
1709 pn_balance_sheet_num IN NUMBER,
1710 pn_profit_statement_id IN NUMBER,
1711 pn_profit_statement_num IN NUMBER,
1712 pn_owners_equity_id IN NUMBER,
1713 pn_owners_equity_num IN NUMBER,
1714 pv_balance_type IN VARCHAR2,
1715 pv_currency_unit IN VARCHAR2,
1716 pn_legal_entity_id IN NUMBER,
1717 pn_ledger_id IN NUMBER,
1718 pv_xml_template_language IN VARCHAR2,
1719 pv_xml_template_territory IN VARCHAR2,
1720 pv_xml_output_format IN VARCHAR2
1721 ) IS
1722
1723 lv_procedure_name VARCHAR2(30) := 'Submit_FSG_XML_Report';
1724 ln_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
1725 ln_proc_level NUMBER := FND_LOG.Level_Procedure;
1726
1727 lv_program VARCHAR2(10);
1728 le_submit_request_exp EXCEPTION;
1729 lb_conc_succ BOOLEAN;
1730
1731 ln_row_set_id NUMBER;
1732 ln_clolumn_set_id NUMBER;
1733 lv_unit_of_measure_id VARCHAR2(15);
1734 lv_rounding_option VARCHAR2(80);
1735 lv_segment_override VARCHAR2(240);
1736 ln_content_set_id NUMBER;
1737 ln_row_order_id NUMBER;
1738 ln_report_display_set_id NUMBER;
1739 ln_minimum_display_level NUMBER;
1740 ln_parameter_set_id NUMBER;
1741
1742 ln_csf_comb_req_id NUMBER;
1743 ln_balance_sheet_req_id NUMBER;
1744 ln_profit_statement_req_id NUMBER;
1745 ln_owners_equity_req_id NUMBER;
1746
1747 lv_error_flag VARCHAR2(1) := 'N';
1748 lb_error_status BOOLEAN;
1749 lb_submit_succ BOOLEAN;
1750
1751 ln_reqid_fsg NUMBER;
1752 lv_company_name VARCHAR2(100);
1753 ln_reqid_cal NUMBER;
1754 ln_waiting_interval NUMBER :=10;
1755 lv_dev_phase VARCHAR2(100);
1756 lv_dev_status VARCHAR2(100);
1757 lv_message VARCHAR2(1000);
1758 lb_xml_layout BOOLEAN;
1759
1760 lv_fsg_req_phase fnd_lookup_values.meaning%TYPE;
1761 lv_fsg_req_status fnd_lookup_values.meaning%TYPE;
1762 lv_fsg_req_status_code fnd_lookup_values.lookup_code%TYPE;
1763
1764 lv_cal_req_phase fnd_lookup_values.meaning%TYPE;
1765 lv_cal_req_status fnd_lookup_values.meaning%TYPE;
1766 lv_cal_req_status_code fnd_lookup_values.lookup_code%TYPE;
1767
1768 lv_comb_req_phase fnd_lookup_values.meaning%TYPE;
1769 lv_comb_req_status fnd_lookup_values.meaning%TYPE;
1770 lv_comb_req_status_code fnd_lookup_values.lookup_code%TYPE;
1771
1772 ln_reqid_convert NUMBER;
1773
1774 CURSOR C_COMPANY_NAME
1775 IS
1776 SELECT COMPANY_NAME
1777 INTO Lv_company_name
1778 FROM JA_CN_SYSTEM_PARAMETERS_ALL
1779 WHERE LEGAL_ENTITY_ID= pn_legal_entity_id;
1780
1781 BEGIN
1782 --log for debug
1783 IF (ln_proc_level >= ln_dbg_level) THEN
1784 FND_LOG.STRING(ln_proc_level,
1785 g_module_name || '.' || lv_procedure_name || '.begin',
1786 'begin procedure');
1787 END IF; --( g_proc_level >= g_dbg_level)
1788
1789 --Call JA_CN_UTILITY.Check_Profile, if it doesn't return true, exit
1790 --Profiles include ' JG: Product', which should be set to
1791 --'Asia/Pacific Localizations', 'JG: Territory', which should be
1792 --set to 'China' and 'JA: CNAO Legal Entity', which should be NOT NULL.
1793 IF JA_CN_UTILITY.Check_Profile() <> TRUE THEN
1794 IF (ln_proc_level >= ln_dbg_level) THEN
1795 FND_LOG.STRING(ln_proc_level,
1796 lv_procedure_name,
1797 'Check profile failed!');
1798 END IF; --l_exception_level >= l_runtime_level
1799 retcode := 1;
1800 errbuf := '';
1801 RETURN;
1802 END IF; --JA_CN_UTILITY.Check_Profile() != TRUE
1803
1804 --(1) Submit Balance Sheet Report and get this program request ID.
1805 --ROW_SET_ID
1806 SELECT rs.axis_set_id
1807 INTO ln_row_set_id
1808 FROM rg_report_axis_sets rs, rg_reports rp
1809 WHERE rp.report_id = pn_balance_sheet_id
1810 AND rp.row_set_id = rs.axis_set_id;
1811 --COLUMN_SET_ID
1812 SELECT cs.axis_set_id
1813 INTO ln_clolumn_set_id
1814 FROM rg_report_axis_sets cs, rg_reports rp
1815 WHERE rp.report_id = pn_balance_sheet_id
1816 AND rp.column_set_id = cs.axis_set_id;
1817 --Unit of Measure ID/currency
1818 BEGIN
1819 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
1820 INTO lv_unit_of_measure_id
1821 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
1822 WHERE ldg.ledger_id = acc.default_ledger_id
1823 AND acc.access_set_id = pn_data_access_set_id
1824 AND rp.report_id = pn_balance_sheet_id;
1825 EXCEPTION
1826 WHEN NO_DATA_FOUND THEN
1827 NULL;
1828 END;
1829 -- Rounding Option
1830 BEGIN
1831 SELECT l.meaning
1832 INTO lv_rounding_option
1833 FROM rg_lookups l, rg_reports r
1834 WHERE r.report_id = pn_balance_sheet_id
1835 AND l.lookup_type = 'ROUNDING_OPTION'
1836 AND l.lookup_code = r.rounding_option;
1837 EXCEPTION
1838 WHEN NO_DATA_FOUND THEN
1839 NULL;
1840 END;
1841 -- Segment Override
1842 BEGIN
1843 SELECT segment_override
1844 INTO lv_segment_override
1845 FROM rg_reports
1846 WHERE report_id = pn_balance_sheet_id;
1847 EXCEPTION
1848 WHEN NO_DATA_FOUND THEN
1849 NULL;
1850 END;
1851 --CONTENT_SET_ID
1852 BEGIN
1853 SELECT cs.content_set_id
1854 INTO ln_content_set_id
1855 FROM rg_report_content_sets cs, rg_reports rp
1856 WHERE rp.report_id = pn_balance_sheet_id
1857 AND rp.content_set_id = cs.content_set_id(+);
1858 EXCEPTION
1859 WHEN NO_DATA_FOUND THEN
1860 NULL;
1861 END;
1862 --ROW_ORDER_ID
1863 BEGIN
1864 SELECT ro.row_order_id
1865 INTO ln_row_order_id
1866 FROM rg_row_orders ro, rg_reports rp
1867 WHERE rp.report_id = pn_balance_sheet_id
1868 AND rp.row_order_id = ro.row_order_id(+);
1869 EXCEPTION
1870 WHEN NO_DATA_FOUND THEN
1871 NULL;
1872 END;
1873 --REPORT_DISPLAY_SET_ID
1874 BEGIN
1875 SELECT ds.report_display_set_id
1876 INTO ln_report_display_set_id
1877 FROM rg_report_display_sets ds, rg_reports rp
1878 WHERE rp.report_id = pn_balance_sheet_id
1879 AND rp.report_display_set_id = ds.report_display_set_id(+);
1880 EXCEPTION
1881 WHEN NO_DATA_FOUND THEN
1882 NULL;
1883 END;
1884 --MINIMUM_DISPLAY_LEVEL
1885 BEGIN
1886 SELECT lk.lookup_code
1887 INTO ln_minimum_display_level
1888 FROM rg_reports rp, rg_lookups lk
1889 WHERE rp.report_id = pn_balance_sheet_id
1890 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
1891 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
1892 EXCEPTION
1893 WHEN NO_DATA_FOUND THEN
1894 NULL;
1895 END;
1896 --PARAMETER_SET_ID
1897 BEGIN
1898 SELECT rp.parameter_set_id
1899 INTO ln_parameter_set_id
1900 FROM rg_reports rp
1901 WHERE rp.report_id = pn_balance_sheet_id;
1902 EXCEPTION
1903 WHEN NO_DATA_FOUND THEN
1904 NULL;
1905 END;
1906
1907 -- Submit Balance Sheet Report
1908 lb_submit_succ := Submit_FSG_Request('JA',
1909 pn_data_access_set_id,
1910 ln_balance_sheet_req_id,
1911 lv_program,
1912 pn_coa_id,
1913 pv_adhoc_prefix,
1914 pv_industry,
1915 pv_flex_code,
1916 pv_default_ledger_short_name,
1917 pn_balance_sheet_id,
1918 ln_row_set_id,
1919 ln_clolumn_set_id,
1920 pv_period_name,
1921 lv_unit_of_measure_id,
1922 lv_rounding_option,
1923 lv_segment_override,
1924 ln_content_set_id,
1925 ln_row_order_id,
1926 ln_report_display_set_id,
1927 pv_output_option,
1928 pv_exceptions_flag,
1929 ln_minimum_display_level,
1930 to_date(pv_accounting_date, 'YYYY/MM/DD HH24:MI:SS'),
1931 ln_parameter_set_id,
1932 pn_page_length,
1933 pn_subrequest_id,
1934 pv_appl_deflt_name
1935 );
1936
1937 --(2) Submit Profit Statement Report and get this program request ID.
1938 --ROW_SET_ID
1939 SELECT rs.axis_set_id
1940 INTO ln_row_set_id
1941 FROM rg_report_axis_sets rs, rg_reports rp
1942 WHERE rp.report_id = pn_profit_statement_id
1943 AND rp.row_set_id = rs.axis_set_id;
1944 --COLUMN_SET_ID
1945 SELECT cs.axis_set_id
1946 INTO ln_clolumn_set_id
1947 FROM rg_report_axis_sets cs, rg_reports rp
1948 WHERE rp.report_id = pn_profit_statement_id
1949 AND rp.column_set_id = cs.axis_set_id;
1950 --Unit of Measure ID/currency
1951 BEGIN
1952 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
1953 INTO lv_unit_of_measure_id
1954 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
1955 WHERE ldg.ledger_id = acc.default_ledger_id
1956 AND acc.access_set_id = pn_profit_statement_id
1957 AND rp.report_id = pn_balance_sheet_id;
1958 EXCEPTION
1959 WHEN NO_DATA_FOUND THEN
1960 NULL;
1961 END;
1962 -- Rounding Option
1963 BEGIN
1964 SELECT l.meaning
1965 INTO lv_rounding_option
1966 FROM rg_lookups l, rg_reports r
1967 WHERE r.report_id = pn_profit_statement_id
1968 AND l.lookup_type = 'ROUNDING_OPTION'
1969 AND l.lookup_code = r.rounding_option;
1970 EXCEPTION
1971 WHEN NO_DATA_FOUND THEN
1972 NULL;
1973 END;
1974 -- Segment Override
1975 BEGIN
1976 SELECT segment_override
1977 INTO lv_segment_override
1978 FROM rg_reports
1979 WHERE report_id = pn_profit_statement_id;
1980 EXCEPTION
1981 WHEN NO_DATA_FOUND THEN
1982 NULL;
1983 END;
1984 --CONTENT_SET_ID
1985 BEGIN
1986 SELECT cs.content_set_id
1987 INTO ln_content_set_id
1988 FROM rg_report_content_sets cs, rg_reports rp
1989 WHERE rp.report_id = pn_profit_statement_id
1990 AND rp.content_set_id = cs.content_set_id(+);
1991 EXCEPTION
1992 WHEN NO_DATA_FOUND THEN
1993 NULL;
1994 END;
1995 --ROW_ORDER_ID
1996 BEGIN
1997 SELECT ro.row_order_id
1998 INTO ln_row_order_id
1999 FROM rg_row_orders ro, rg_reports rp
2000 WHERE rp.report_id = pn_profit_statement_id
2001 AND rp.row_order_id = ro.row_order_id(+);
2002 EXCEPTION
2003 WHEN NO_DATA_FOUND THEN
2004 NULL;
2005 END;
2006 --REPORT_DISPLAY_SET_ID
2007 BEGIN
2008 SELECT ds.report_display_set_id
2009 INTO ln_report_display_set_id
2010 FROM rg_report_display_sets ds, rg_reports rp
2011 WHERE rp.report_id = pn_profit_statement_id
2012 AND rp.report_display_set_id = ds.report_display_set_id(+);
2013 EXCEPTION
2014 WHEN NO_DATA_FOUND THEN
2015 NULL;
2016 END;
2017 --MINIMUM_DISPLAY_LEVEL
2018 BEGIN
2019 SELECT lk.lookup_code
2020 INTO ln_minimum_display_level
2021 FROM rg_reports rp, rg_lookups lk
2022 WHERE rp.report_id = pn_profit_statement_id
2023 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
2024 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
2025 EXCEPTION
2026 WHEN NO_DATA_FOUND THEN
2027 NULL;
2028 END;
2029 --PARAMETER_SET_ID
2030 BEGIN
2031 SELECT rp.parameter_set_id
2032 INTO ln_parameter_set_id
2033 FROM rg_reports rp
2034 WHERE rp.report_id = pn_profit_statement_id;
2035 EXCEPTION
2036 WHEN NO_DATA_FOUND THEN
2037 NULL;
2038 END;
2039
2040 -- Submit Profit Statement Report
2041 lb_submit_succ := Submit_FSG_Request('JA',
2042 pn_data_access_set_id,
2043 ln_profit_statement_req_id,
2044 lv_program,
2045 pn_coa_id,
2046 pv_adhoc_prefix,
2047 pv_industry,
2048 pv_flex_code,
2049 pv_default_ledger_short_name,
2050 pn_profit_statement_id,
2051 ln_row_set_id,
2052 ln_clolumn_set_id,
2053 pv_period_name,
2054 lv_unit_of_measure_id,
2055 lv_rounding_option,
2056 lv_segment_override,
2057 ln_content_set_id,
2058 ln_row_order_id,
2059 ln_report_display_set_id,
2060 pv_output_option,
2061 pv_exceptions_flag,
2062 ln_minimum_display_level,
2063 to_date(pv_accounting_date, 'YYYY/MM/DD HH24:MI:SS'),
2064 ln_parameter_set_id,
2065 pn_page_length,
2066 pn_subrequest_id,
2067 pv_appl_deflt_name
2068 );
2069
2070
2071 --(3) Submit Profit Statement Report and get this program request ID.
2072 --ROW_SET_ID
2073 SELECT rs.axis_set_id
2074 INTO ln_row_set_id
2075 FROM rg_report_axis_sets rs, rg_reports rp
2076 WHERE rp.report_id = pn_owners_equity_id
2077 AND rp.row_set_id = rs.axis_set_id;
2078 --COLUMN_SET_ID
2079 SELECT cs.axis_set_id
2080 INTO ln_clolumn_set_id
2081 FROM rg_report_axis_sets cs, rg_reports rp
2082 WHERE rp.report_id = pn_owners_equity_id
2083 AND rp.column_set_id = cs.axis_set_id;
2084 --Unit of Measure ID/currency
2085 BEGIN
2086 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
2087 INTO lv_unit_of_measure_id
2088 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
2089 WHERE ldg.ledger_id = acc.default_ledger_id
2090 AND acc.access_set_id = pn_owners_equity_id
2091 AND rp.report_id = pn_balance_sheet_id;
2092 EXCEPTION
2093 WHEN NO_DATA_FOUND THEN
2094 NULL;
2095 END;
2096 -- Rounding Option
2097 BEGIN
2098 SELECT l.meaning
2099 INTO lv_rounding_option
2100 FROM rg_lookups l, rg_reports r
2101 WHERE r.report_id = pn_owners_equity_id
2102 AND l.lookup_type = 'ROUNDING_OPTION'
2103 AND l.lookup_code = r.rounding_option;
2104 EXCEPTION
2105 WHEN NO_DATA_FOUND THEN
2106 NULL;
2107 END;
2108 -- Segment Override
2109 BEGIN
2110 SELECT segment_override
2111 INTO lv_segment_override
2112 FROM rg_reports
2113 WHERE report_id = pn_owners_equity_id;
2114 EXCEPTION
2115 WHEN NO_DATA_FOUND THEN
2116 NULL;
2117 END;
2118 --CONTENT_SET_ID
2119 BEGIN
2120 SELECT cs.content_set_id
2121 INTO ln_content_set_id
2122 FROM rg_report_content_sets cs, rg_reports rp
2123 WHERE rp.report_id = pn_owners_equity_id
2124 AND rp.content_set_id = cs.content_set_id(+);
2125 EXCEPTION
2126 WHEN NO_DATA_FOUND THEN
2127 NULL;
2128 END;
2129 --ROW_ORDER_ID
2130 BEGIN
2131 SELECT ro.row_order_id
2132 INTO ln_row_order_id
2133 FROM rg_row_orders ro, rg_reports rp
2134 WHERE rp.report_id = pn_owners_equity_id
2135 AND rp.row_order_id = ro.row_order_id(+);
2136 EXCEPTION
2137 WHEN NO_DATA_FOUND THEN
2138 NULL;
2139 END;
2140 --REPORT_DISPLAY_SET_ID
2141 BEGIN
2142 SELECT ds.report_display_set_id
2143 INTO ln_report_display_set_id
2144 FROM rg_report_display_sets ds, rg_reports rp
2145 WHERE rp.report_id = pn_owners_equity_id
2146 AND rp.report_display_set_id = ds.report_display_set_id(+);
2147 EXCEPTION
2148 WHEN NO_DATA_FOUND THEN
2149 NULL;
2150 END;
2151 --MINIMUM_DISPLAY_LEVEL
2152 BEGIN
2153 SELECT lk.lookup_code
2154 INTO ln_minimum_display_level
2155 FROM rg_reports rp, rg_lookups lk
2156 WHERE rp.report_id = pn_owners_equity_id
2157 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
2158 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
2159 EXCEPTION
2160 WHEN NO_DATA_FOUND THEN
2161 NULL;
2162 END;
2163 --PARAMETER_SET_ID
2164 BEGIN
2165 SELECT rp.parameter_set_id
2166 INTO ln_parameter_set_id
2167 FROM rg_reports rp
2168 WHERE rp.report_id = pn_owners_equity_id;
2169 EXCEPTION
2170 WHEN NO_DATA_FOUND THEN
2171 NULL;
2172 END;
2173
2174 -- Submit Profit Statement Report
2175 lb_submit_succ := Submit_FSG_Request('JA',
2176 pn_data_access_set_id,
2177 ln_owners_equity_req_id,
2178 lv_program,
2179 pn_coa_id,
2180 pv_adhoc_prefix,
2181 pv_industry,
2182 pv_flex_code,
2183 pv_default_ledger_short_name,
2184 pn_owners_equity_id,
2185 ln_row_set_id,
2186 ln_clolumn_set_id,
2187 pv_period_name,
2188 lv_unit_of_measure_id,
2189 lv_rounding_option,
2190 lv_segment_override,
2191 ln_content_set_id,
2192 ln_row_order_id,
2193 ln_report_display_set_id,
2194 pv_output_option,
2195 pv_exceptions_flag,
2196 ln_minimum_display_level,
2197 to_date(pv_accounting_date, 'YYYY/MM/DD HH24:MI:SS'),
2198 ln_parameter_set_id,
2199 pn_page_length,
2200 pn_subrequest_id,
2201 pv_appl_deflt_name
2202 );
2203
2204 --(4) Invoke the Cash Flow Statement - Generation Program
2205 -- The Generation program invoke the Cash Flow Statement Combination Program,
2206 -- then get the Combination Program request ID.
2207 --ROW_SET_ID
2208 SELECT rs.axis_set_id
2209 INTO ln_row_set_id
2210 FROM rg_report_axis_sets rs, rg_reports rp
2211 WHERE rp.report_id = pn_csf_report_id
2212 AND rp.row_set_id = rs.axis_set_id;
2213 --COLUMN_SET_ID
2214 SELECT cs.axis_set_id
2215 INTO ln_clolumn_set_id
2216 FROM rg_report_axis_sets cs, rg_reports rp
2217 WHERE rp.report_id = pn_csf_report_id
2218 AND rp.column_set_id = cs.axis_set_id;
2219 --Unit of Measure ID/currency
2220 BEGIN
2221 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
2222 INTO lv_unit_of_measure_id
2223 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
2224 WHERE ldg.ledger_id = acc.default_ledger_id
2225 AND acc.access_set_id = pn_csf_report_id
2226 AND rp.report_id = pn_balance_sheet_id;
2227 EXCEPTION
2228 WHEN NO_DATA_FOUND THEN
2229 NULL;
2230 END;
2231 -- Rounding Option
2232 BEGIN
2233 SELECT l.meaning
2234 INTO lv_rounding_option
2235 FROM rg_lookups l, rg_reports r
2236 WHERE r.report_id = pn_csf_report_id
2237 AND l.lookup_type = 'ROUNDING_OPTION'
2238 AND l.lookup_code = r.rounding_option;
2239 EXCEPTION
2240 WHEN NO_DATA_FOUND THEN
2241 NULL;
2242 END;
2243 -- Segment Override
2244 BEGIN
2245 SELECT segment_override
2246 INTO lv_segment_override
2247 FROM rg_reports
2248 WHERE report_id = pn_csf_report_id;
2249 EXCEPTION
2250 WHEN NO_DATA_FOUND THEN
2251 NULL;
2252 END;
2253 --CONTENT_SET_ID
2254 BEGIN
2255 SELECT cs.content_set_id
2256 INTO ln_content_set_id
2257 FROM rg_report_content_sets cs, rg_reports rp
2258 WHERE rp.report_id = pn_csf_report_id
2259 AND rp.content_set_id = cs.content_set_id(+);
2260 EXCEPTION
2261 WHEN NO_DATA_FOUND THEN
2262 NULL;
2263 END;
2264 --ROW_ORDER_ID
2265 BEGIN
2266 SELECT ro.row_order_id
2267 INTO ln_row_order_id
2268 FROM rg_row_orders ro, rg_reports rp
2269 WHERE rp.report_id = pn_csf_report_id
2270 AND rp.row_order_id = ro.row_order_id(+);
2271 EXCEPTION
2272 WHEN NO_DATA_FOUND THEN
2273 NULL;
2274 END;
2275 --REPORT_DISPLAY_SET_ID
2276 BEGIN
2277 SELECT ds.report_display_set_id
2278 INTO ln_report_display_set_id
2279 FROM rg_report_display_sets ds, rg_reports rp
2280 WHERE rp.report_id = pn_csf_report_id
2281 AND rp.report_display_set_id = ds.report_display_set_id(+);
2282 EXCEPTION
2283 WHEN NO_DATA_FOUND THEN
2284 NULL;
2285 END;
2286 --MINIMUM_DISPLAY_LEVEL
2287 BEGIN
2288 SELECT lk.lookup_code
2289 INTO ln_minimum_display_level
2290 FROM rg_reports rp, rg_lookups lk
2291 WHERE rp.report_id = pn_csf_report_id
2292 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
2293 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
2294 EXCEPTION
2295 WHEN NO_DATA_FOUND THEN
2296 NULL;
2297 END;
2298 --PARAMETER_SET_ID
2299 BEGIN
2300 SELECT rp.parameter_set_id
2301 INTO ln_parameter_set_id
2302 FROM rg_reports rp
2303 WHERE rp.report_id = pn_csf_report_id;
2304 EXCEPTION
2305 WHEN NO_DATA_FOUND THEN
2306 NULL;
2307 END;
2308
2309
2310 --(4.1) Submit the first concurrent program 'Cash Flow Statement - FSG',which
2311 --will automatically submit another request for the CFS report to generate XML file
2312 OPEN C_COMPANY_NAME;
2313 FETCH C_COMPANY_NAME INTO lv_company_name;
2314 CLOSE C_COMPANY_NAME;
2315
2316 ln_reqid_fsg:=FND_REQUEST.Submit_Request( 'SQLGL'
2317 ,'RGRARG'
2318 ,''
2319 ,''
2320 ,FALSE
2321 ,pn_data_access_set_id
2322 ,pn_coa_id
2323 ,pv_adhoc_prefix
2324 ,pv_industry
2325 ,pv_flex_code
2326 ,pv_default_ledger_short_name
2327 ,pn_csf_report_id
2328 ,ln_row_set_id
2329 ,ln_clolumn_set_id
2330 ,pv_period_name
2331 ,lv_unit_of_measure_id
2332 ,lv_rounding_option
2333 ,lv_segment_override
2334 ,ln_content_set_id
2335 ,ln_row_order_id
2336 ,ln_report_display_set_id
2337 ,pv_output_option
2338 ,pv_exceptions_flag
2339 ,ln_minimum_display_level
2340 ,pv_accounting_date
2341 ,ln_parameter_set_id
2342 ,pn_page_length
2343 ,pn_subrequest_id
2344 ,pv_appl_deflt_name
2345 ,lv_company_name,
2346 '', '', '', '', '',
2347 '', '', '', '', '', '', '', '', '', '',
2348 '', '', '', '', '', '', '', '', '', '',
2349 '', '', '', '', '', '', '', '', '', '',
2350 '', '', '', '', '', '', '', '', '', '',
2351 '', '', '', '', '', '', '', '', '', '',
2352 '', '', '', '', '', '', '', '', '', '',
2353 '', '', '', '', '', '', '', '', '', ''
2354 );
2355
2356 IF (pv_output_option = 'Y') THEN
2357 UPDATE FND_CONCURRENT_REQUESTS
2358 SET
2359 OUTPUT_FILE_TYPE = 'XML'
2360 WHERE
2361 REQUEST_ID = ln_reqid_fsg;
2362 END IF;
2363 COMMIT;
2364
2365 --(4.2)Waiting for the 'Cash Flow Statement - FSG' completed, and then retrive the
2366 --sub-request id from the table 'fnd_concurrent_requests' for the FSG - CFS report.
2367 IF ln_reqid_fsg<>0
2368 THEN
2369 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_fsg
2370 ,interval => ln_waiting_interval
2371 ,phase => lv_fsg_req_phase
2372 ,status => lv_fsg_req_status
2373 ,dev_phase => lv_dev_phase
2374 ,dev_status => lv_dev_status
2375 ,message => lv_message
2376 )
2377 THEN
2378 --To get lookup code for current status
2379 SELECT
2380 lookup_code
2381 INTO
2382 lv_fsg_req_status_code
2383 FROM
2384 fnd_lookup_values
2385 WHERE lookup_type = 'CP_STATUS_CODE'
2386 AND view_application_id=0
2387 AND security_group_id=0
2388 AND meaning=lv_fsg_req_status
2389 AND enabled_flag='Y'
2390 AND language = USERENV('LANG');
2391
2392 --To judge if the Program - Run Financial Statement Generator'
2393 --has been completed successfully.
2394 --Completed with 'Normal'
2395 IF lv_fsg_req_status_code='C'
2396 THEN
2397 null;
2398 --Completed with 'Warning'
2399 ELSIF lv_fsg_req_status_code='G'
2400 THEN
2401 lv_error_flag:='W';
2402 --Completed with 'Error'
2403 ELSIF lv_fsg_req_status_code='E'
2404 THEN
2405 lv_error_flag:='E';
2406 END IF;
2407 END IF;
2408 ELSE
2409 lv_error_flag:='E';
2410 END IF;
2411
2412
2413 --(4.3)If FSG xml output request for cfs is successfully completed,
2414 -- then submit the second concurrent program 'Cash Flow Statement - Calculation'.
2415 IF nvl(ln_reqid_fsg,0)<>0 AND lv_error_flag='N'
2416 THEN
2417 ln_reqid_cal:=FND_REQUEST.Submit_Request(application => 'JA'
2418 ,program => 'JACNCFSN'
2419 ,argument1 => pn_legal_entity_id
2420 ,argument2 => pn_ledger_id
2421 ,argument3 => pv_period_name
2422 ,argument4 => ln_row_set_id
2423 ,argument5 => lv_rounding_option
2424 ,argument6 => pv_balance_type
2425 ,argument7 => pn_coa_id
2426 ,argument8 => lv_segment_override
2427 );
2428 COMMIT;
2429
2430 --(4.4)Waiting for the 'Cash flow statement - Calculation' successfully completed
2431 -- and then submit concurrent program 'Cash Flow Statement - Combination'.
2432 IF ln_reqid_cal<>0
2433 THEN
2434 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_cal
2435 ,interval => ln_waiting_interval
2436 ,phase => lv_cal_req_phase
2437 ,status => lv_cal_req_status
2438 ,dev_phase => lv_dev_phase
2439 ,dev_status => lv_dev_status
2440 ,message => lv_message
2441 )
2442 THEN
2443 --To get lookup code for current status
2444 SELECT
2445 lookup_code
2446 INTO
2447 lv_cal_req_status_code
2448 FROM
2449 fnd_lookup_values
2450 WHERE lookup_type = 'CP_STATUS_CODE'
2451 AND view_application_id=0
2452 AND security_group_id=0
2453 AND meaning=lv_cal_req_status
2454 AND enabled_flag='Y'
2455 AND language = USERENV('LANG');
2456
2457 --Completed with successful
2458 IF lv_cal_req_status_code='C'
2459 THEN
2460 --Submit the third concurrent program 'Cash flow statement - Combination'
2461 lb_xml_layout := FND_REQUEST.Add_Layout(template_appl_name => 'JA'
2462 ,template_code => 'JACNCFSC'
2463 ,template_language => pv_xml_template_language --'zh' ('en')
2464 ,template_territory => pv_xml_template_territory--'00' ('US')
2465 ,output_format => pv_xml_output_format --'ETEXT'
2466 );
2467
2468 ln_csf_comb_req_id:=FND_REQUEST.Submit_Request(application => 'JA'
2469 ,program => 'JACNCFSC'
2470 ,argument1 => ln_reqid_fsg
2471 ,argument2 => ln_reqid_cal
2472 );
2473 COMMIT;
2474
2475 --(4.5)Waiting for the 'Cash flow statement - Combination' completed
2476 --get its status
2477 IF ln_csf_comb_req_id<>0
2478 THEN
2479 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_csf_comb_req_id
2480 ,interval => ln_waiting_interval
2481 ,phase => lv_comb_req_phase
2482 ,status => lv_comb_req_status
2483 ,dev_phase => lv_dev_phase
2484 ,dev_status => lv_dev_status
2485 ,message => lv_message
2486 )
2487 THEN
2488 --To get lookup code for current status
2489 SELECT
2490 lookup_code
2491 INTO
2492 lv_comb_req_status_code
2493 FROM
2494 fnd_lookup_values
2495 WHERE lookup_type = 'CP_STATUS_CODE'
2496 AND view_application_id=0
2497 AND security_group_id=0
2498 AND meaning=lv_comb_req_status
2499 AND enabled_flag='Y'
2500 AND language = USERENV('LANG');
2501
2502 --Completed with Normal
2503 IF lv_comb_req_status_code='C'
2504 THEN
2505
2506 --dbms_output.put_line('Invoke Report Item Data Convert program');
2507
2508 -- (5) Invoke Report Item Data Convert program to covert the FSG output
2509 ln_reqid_convert:=FND_REQUEST.Submit_Request(application => 'JA'
2510 ,program => 'JACNCDCP'
2511 ,argument1 => ln_csf_comb_req_id
2512 ,argument2 => pn_csf_report_num
2513 ,argument3 => ln_balance_sheet_req_id
2514 ,argument4 => pn_balance_sheet_num
2515 ,argument5 => ln_profit_statement_req_id
2516 ,argument6 => pn_profit_statement_num
2517 ,argument7 => ln_owners_equity_req_id
2518 ,argument8 => pn_owners_equity_num
2519 ,argument9 => pv_currency_unit
2520 ,argument10 => Lv_company_name
2521 );
2522 COMMIT;
2523
2524 --Completed with 'Warning'
2525 ELSIF lv_comb_req_status_code='G'
2526 THEN
2527 lv_error_flag:='W';
2528 --Completed with 'Error'
2529 ELSIF lv_comb_req_status_code='E'
2530 THEN
2531 lv_error_flag:='E';
2532 END IF; --lv_comb_req_status_code='C'
2533 END IF; --FND_CONCURRENT.Wait_For_Request(request_id => l_reqid_comb
2534 ELSE
2535 lv_error_flag:='E';
2536 END IF; --l_reqid_comb<>0
2537
2538 --Completed with 'Warning'
2539 ELSIF lv_cal_req_status_code='G'
2540 THEN
2541 lv_error_flag:='W';
2542 --Completed with 'Error'
2543 ELSIF lv_cal_req_status_code='E'
2544 THEN
2545 lv_error_flag:='E';
2546 END IF;
2547 END IF;
2548 ELSE
2549 lv_error_flag:='E';
2550 END IF;
2551
2552 END IF;
2553
2554 IF lv_error_flag='W'
2555 THEN
2556 lb_error_status:=FND_CONCURRENT.Set_Completion_Status(status => 'WARNING'
2557 ,message => ''
2558 );
2559 ELSIF lv_error_flag='E'
2560 THEN
2561 lb_error_status:=FND_CONCURRENT.Set_Completion_Status(status => 'ERROR'
2562 ,message => ''
2563 );
2564 END IF;
2565
2566 --log for debug
2567 IF (ln_proc_level >= ln_dbg_level)
2568 THEN
2569 FND_LOG.STRING(ln_proc_level
2570 ,g_module_name||'.'||lv_procedure_name
2571 ,lv_error_flag
2572 );
2573 FND_LOG.STRING(ln_proc_level
2574 ,g_module_name||'.'||lv_procedure_name||'.end'
2575 ,'end procedure'
2576 );
2577 END IF;
2578
2579 EXCEPTION
2580 WHEN OTHERS THEN
2581 IF (ln_proc_level >= ln_dbg_level)
2582 THEN
2583 FND_LOG.STRING(ln_proc_level
2584 ,g_module_name||'.'||lv_procedure_name||'EXCEPTION'
2585 ,lv_error_flag
2586 );
2587 FND_LOG.String(ln_proc_level
2588 ,g_module_name||'.'||lv_procedure_name||'. Other_Exception '
2589 ,SQLCODE||':'||SQLERRM
2590 );
2591 END IF;
2592
2593 END Submit_FSG_Report;
2594
2595 end ja_cn_fsg_xml_submit_prog;