[Home] [Help]
PACKAGE BODY: APPS.JA_CN_FSG_XML_BANK_PKG
Source
1 package body ja_cn_fsg_xml_bank_pkg AS
2 --$Header: JACNFXBB.pls 120.0.12020000.4 2013/02/20 09:56:19 chongwan noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| JACNFXBB.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 --| 19/11/2012 Jar Wang Updated for CANO BANKING
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 -- 19/11/2012 Jar Wang Updated for CANO BANKING
79 --==========================================================================
80
81 FUNCTION Submit_FSG_Request(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_BSV IN VARCHAR2,
86 X_LEGAL_ID IN NUMBER,
87 X_COA_ID IN NUMBER,
88 X_ADHOC_PREFIX IN VARCHAR2,
89 X_INDUSTRY IN VARCHAR2,
90 X_FLEX_CODE IN VARCHAR2,
91 X_DEFAULT_LEDGER_SHORT_NAME IN VARCHAR2,
92 X_REPORT_ID IN NUMBER,
93 X_ROW_SET_ID IN NUMBER,
94 X_COLUMN_SET_ID IN NUMBER,
95 X_PERIOD_NAME IN VARCHAR2,
96 X_UNIT_OF_MEASURE_ID IN VARCHAR2,
97 X_ROUNDING_OPTION IN VARCHAR2,
98 X_SEGMENT_OVERRIDE IN VARCHAR2,
99 X_CONTENT_SET_ID IN NUMBER,
100 X_ROW_ORDER_ID IN NUMBER,
101 X_REPORT_DISPLAY_SET_ID IN NUMBER,
102 X_OUTPUT_OPTION IN VARCHAR2,
103 X_EXCEPTIONS_FLAG IN VARCHAR2,
104 X_MINIMUM_DISPLAY_LEVEL IN NUMBER,
105 X_ACCOUNTING_DATE IN DATE,
106 X_PARAMETER_SET_ID IN NUMBER,
107 X_PAGE_LENGTH IN NUMBER,
108 X_SUBREQUEST_ID IN NUMBER,
109 X_APPL_DEFLT_NAME IN VARCHAR2)
110 RETURN BOOLEAN IS
111 TYPE RepTyp IS RECORD(
112 row_set_id NUMBER(15),
113 column_set_id NUMBER(15),
114 unit_of_measure_id VARCHAR2(30),
115 content_set_id NUMBER(15),
116 row_order_id NUMBER(15),
117 rounding_option VARCHAR2(1),
118 parameter_set_id NUMBER(15),
119 minimum_display_level NUMBER(15),
120 report_display_set_id NUMBER(15),
121 output_option VARCHAR2(1),
122 report_title VARCHAR2(240),
123 segment_override VARCHAR2(800));
124
125 report_rec RepTyp;
126
127 req_id NUMBER;
128 rep_run_type rg_report_content_sets.report_run_type%TYPE;
129 L_COMPANY_NAME VARCHAR2(100) :=X_BSV;
130 lv_fsg_req_phase fnd_lookup_values.meaning%TYPE;
131 lv_fsg_req_status fnd_lookup_values.meaning%TYPE;
132 lv_fsg_req_status_code fnd_lookup_values.lookup_code%TYPE;
133 ln_waiting_interval number:=10;
134 lv_dev_phase VARCHAR2(100);
135 lv_dev_status VARCHAR2(100);
136 lv_message VARCHAR2(1000);
137 v_request_flag BOOLEAN;
138 BEGIN
139
140 SELECT row_set_id,
141 column_set_id,
142 unit_of_measure_id,
143 content_set_id,
144 row_order_id,
145 rounding_option,
146 parameter_set_id,
147 minimum_display_level,
148 report_display_set_id,
149 output_option,
150 name,
151 segment_override
152 INTO report_rec
153 FROM RG_REPORTS
154 WHERE REPORT_ID = X_REPORT_ID;
155
156 --
157 -- If content set is used by this report then
158 -- check the report run method.
159 --
160 IF (X_content_set_id IS NOT NULL) THEN
161 SELECT report_run_type
162 INTO rep_run_type
163 FROM rg_report_content_sets
164 WHERE content_set_id = X_content_set_id;
165 ELSE
166 rep_run_type := 'S';
167 END IF;
168
169 IF (rep_run_type = 'P') THEN
170 X_PROGRAM := 'RGSSRQ';
171 req_id := FND_REQUEST.SUBMIT_REQUEST('SQLGL',
172 'RGSSRQ',
173 report_rec.report_title,
174 '',
175 FALSE,
176 TO_CHAR(X_data_access_set_id),
177 TO_CHAR(X_COA_ID),
178 X_ADHOC_PREFIX,
179 X_INDUSTRY,
180 X_FLEX_CODE,
181 X_default_ledger_short_name,
182 TO_CHAR(X_report_id),
183 TO_CHAR(X_row_set_id),
184 TO_CHAR(X_column_set_id),
185 X_period_name,
186 X_unit_of_measure_id,
187 X_rounding_option,
188 X_segment_override,
189 TO_CHAR(X_content_set_id),
190 TO_CHAR(X_row_order_id),
191 TO_CHAR(X_report_display_set_id),
192 X_output_option,
193 X_exceptions_flag,
194 TO_CHAR(X_minimum_display_level),
195 TO_CHAR(X_accounting_date,
196 'YYYY/MM/DD'),
197 TO_CHAR(X_parameter_set_id),
198 TO_CHAR(X_page_length),
199 X_appl_deflt_name,
200 '',
201 '',
202 '',
203 '',
204 '',
205 '',
206 '',
207 '',
208 '',
209 '',
210 '',
211 '',
212 '',
213 '',
214 '',
215 '',
216 '',
217 '',
218 '',
219 '',
220 '',
221 '',
222 '',
223 '',
224 '',
225 '',
226 '',
227 '',
228 '',
229 '',
230 '',
231 '',
232 '',
233 '',
234 '',
235 '',
236 '',
237 '',
238 '',
239 '',
240 '',
241 '',
242 '',
243 '',
244 '',
245 '',
246 '',
247 '',
248 '',
249 '',
250 '',
251 '',
252 '',
253 '',
254 '',
255 '',
256 '',
257 '',
258 '',
259 '',
260 '',
261 '',
262 '',
263 '',
264 '',
265 '',
266 '',
267 '',
268 '',
269 '',
270 '',
271 '',
272 '',
273 '',
274 '',
275 '');
276 IF (X_output_option = 'Y') THEN
277 UPDATE FND_CONCURRENT_REQUESTS
278 SET OUTPUT_FILE_TYPE = 'XML'
279 WHERE REQUEST_ID = req_id;
280 END IF;
281 ELSE
282 X_PROGRAM := 'JACNFSGC';
283 req_id := FND_REQUEST.SUBMIT_REQUEST('JA',
284 'JACNFSGC',
285 report_rec.report_title,
286 '',
287 FALSE,
288 TO_CHAR(X_data_access_set_id),
289 TO_CHAR(X_COA_ID),
290 X_ADHOC_PREFIX,
291 X_INDUSTRY,
292 X_FLEX_CODE,
293 X_default_ledger_short_name,
294 TO_CHAR(X_report_id),
295 TO_CHAR(X_row_set_id),
296 TO_CHAR(X_column_set_id),
297 X_period_name,
298 X_unit_of_measure_id,
299 X_rounding_option,
300 X_segment_override,
301 TO_CHAR(X_content_set_id),
302 TO_CHAR(X_row_order_id),
303 TO_CHAR(X_report_display_set_id),
304 X_output_option,
305 X_exceptions_flag,
306 TO_CHAR(X_minimum_display_level),
307 TO_CHAR(X_accounting_date,
308 'YYYY/MM/DD'),
309 TO_CHAR(X_parameter_set_id),
310 TO_CHAR(X_page_length),
311 TO_CHAR(X_SUBREQUEST_ID),
312 X_appl_deflt_name,
313 chr(0));
314 IF (X_output_option = 'Y') THEN
315 UPDATE FND_CONCURRENT_REQUESTS
316 SET OUTPUT_FILE_TYPE = 'XML'
317 WHERE REQUEST_ID = req_id;
318 END IF;
319 END IF;
320
321 IF (req_id = 0) THEN
322 ROLLBACK;
323 RETURN FALSE;
324 ELSE
325 X_concurrent_request_id := req_id;
326
327 COMMIT;
328 v_request_flag := fnd_concurrent.wait_for_request(request_id => req_id,
329 INTERVAL => ln_waiting_interval,
330 max_wait => 0,
331 phase => lv_fsg_req_phase,
332 status => lv_fsg_req_status,
333 dev_phase => lv_dev_phase,
334 dev_status => lv_dev_status,
335 message => lv_message);
336
337 SELECT lookup_code
338 INTO lv_fsg_req_status_code
339 FROM fnd_lookup_values
340 WHERE lookup_type = 'CP_STATUS_CODE'
341 AND view_application_id = 0
342 AND security_group_id = 0
343 AND meaning = lv_fsg_req_status
344 AND enabled_flag = 'Y'
345 AND language = USERENV('LANG');
346
347 IF lv_fsg_req_status_code='C' THEN
348 return TRUE;
349 ELSE
350 RETURN FALSE;
351 END IF;
352 END IF;
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Submit_FSG_Request:' || SQLERRM);
357 END Submit_FSG_Request;
358
359 --==========================================================================
360 -- PROCEDURE NAME:
361 -- Submit_xml_publiser Private
362 --
363 -- DESCRIPTION:
364 -- This procedure is used to submit xml publisher concurrent,
365 -- PARAMETERS:
366 -- In: p_template_appl template application
367 -- p_ltemplate template name
368 -- p_template_locale template locale
369 -- p_output_format output format
370 -- Out: x_xml_request_id xml request id
371 -- x_result_flag result flag
372 --
373 -- DESIGN REFERENCES:
374 -- None
375 --
376 -- CHANGE HISTORY:
377 -- 06/03/2006 Shujuan Yan Created
378 -- 19/11/2012 Jar Wang Updated for CANO BANKING
379 --==========================================================================
380 PROCEDURE Submit_xml_Publisher(p_fsg_request_id IN NUMBER,
381 p_template_appl IN VARCHAR2,
382 p_template IN VARCHAR2,
383 p_template_locale IN VARCHAR2,
384
385 p_output_format IN VARCHAR2,
386 x_xml_request_id OUT NOCOPY NUMBER,
387 x_result_flag OUT NOCOPY VARCHAR2) IS
388
389 l_procedure_name VARCHAR2(30) := 'Submit_xml_Publisher';
390 l_runtime_level NUMBER := fnd_log.g_current_runtime_level;
391 l_procedure_level NUMBER := fnd_log.level_procedure;
392 --l_statement_level NUMBER := fnd_log.level_statement;
393 --l_exception_level NUMBER := fnd_log.level_exception;
394 l_complete_flag BOOLEAN;
395 l_phase VARCHAR2(100);
396 l_status VARCHAR2(100);
397 l_del_phase VARCHAR2(100);
398 l_del_status VARCHAR2(100);
399 l_message VARCHAR2(1000);
400
401 BEGIN
402 --log for debug
403 IF (l_procedure_level >= l_runtime_level) THEN
404 fnd_log.STRING(l_procedure_level,
405 g_module_name || '.' || l_procedure_name || '.begin',
406 'begin procedure');
407 END IF; --l_procedure_level >= l_runtime_level
408 -- submit xml publisher concurrent program
409 x_xml_request_id := fnd_request.submit_request('XDO',
410 'XDOREPPB',
411 NULL,
412 SYSDATE,
413 FALSE,
414 'Y', --added by lyb, for bug 6642516
415 p_fsg_request_id,
416 p_template_appl,
417 p_template,
418 p_template_locale,
419 NULL,
420 NULL,
421 p_output_format);
422
423 IF (x_xml_request_id <= 0 OR x_xml_request_id IS NULL) THEN
424 x_result_flag := 'Error';
425 ELSE
426 COMMIT;
427 --Wait for concurrent complete
428 l_complete_flag := fnd_concurrent.wait_for_request(x_xml_request_id,
429 1,
430 0,
431 l_phase,
432 l_status,
433 l_del_phase,
434 l_del_status,
435 l_message);
436 IF l_complete_flag = FALSE OR
437 JA_CN_UTILITY.get_lookup_code(p_lookup_meaning => l_status,
438 p_lookup_type => 'CP_STATUS_CODE') <> 'C' THEN
439 x_result_flag := 'Error';
440 ELSE
441 x_result_flag := 'Sucess';
442 END IF; -- l_complete_flag = false
443 END IF; -- (x_xml_request_id <= 0 OR x_xml_request_id IS NULL)
444
445 --log for debug
446 IF (l_procedure_level >= l_runtime_level) THEN
447 fnd_log.STRING(l_procedure_level,
448 g_module_name || '.' || l_procedure_name || '.end',
449 'end procedure');
450 END IF; --l_procedure_level >= l_runtime_level
451 EXCEPTION
452 WHEN OTHERS THEN
453 --log for debug
454 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
455 fnd_log.STRING(fnd_log.level_unexpected,
456 g_module_name || l_procedure_name ||
457 '. OTHER_EXCEPTION ',
458 SQLCODE || SQLERRM);
459 END IF; -- fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
460 RAISE;
461 END Submit_xml_Publisher;
462 --==========================================================================
463 -- PROCEDURE NAME:
464 -- Submit_FSG_XML_Report Private
465 --
466 -- DESCRIPTION:
467 -- This procedure is used to submit FSG report and XML Report publisher .
468 -- PARAMETERS:
469 -- In:
470 -- P_APPL_SHORT_NAME Application short name
471 -- P_DATA_ACCESS_SET_ID Data Acess ID
472 -- P_CONCURRENT_REQUEST_ID CONCURRENT REQUEST ID
473 -- P_PROGRAM PROGRAM
474 -- P_COA_ID char of accounts id
475 -- P_ADHOC_PREFIX ADHOC PREFIX
476 -- P_INDUSTRY Industry
477 -- P_FLEX_CODE Flex Code
478 -- P_DEFAULT_LEDGER_SHORT_NAME Default Ledger short Name
479 -- P_REPORT_ID Report ID
480 -- P_ROW_SET_ID Row Set ID
481 -- P_COLUMN_SET_ID Column Set ID
482 -- P_PERIOD_NAME Period Name
483 -- P_UNIT_OF_MEASURE_ID Unit of Measure ID/currency
484 -- P_ROUNDING_OPTION Rounding Option
485 -- P_SEGMENT_OVERRIDE Segment Override
486 -- P_CONTENT_SET_ID Content Set ID
487 -- P_ROW_ORDER_ID Row Order ID
488 -- P_REPORT_DISPLAY_SET_ID Report Display Set ID
489 -- P_OUTPUT_OPTION Out Option
490 -- P_EXCEPTIONS_FLAG Exception
491 -- P_MINIMUM_DISPLAY_LEVEL Minimum Display Level
492 -- P_ACCOUNTING_DATE Accounting Date
493 -- P_PARAMETER_SET_ID Parameter set ID
494 -- P_PAGE_LENGTH Page Lenth
495 -- P_SUBREQUEST_ID SubRequest ID
496 -- P_APPL_DEFLT_NAME Application Default Name
497
498 -- p_template Template name
499 -- p_template_locale Template locale
500 -- p_output_format Output format
501 -- p_source_charset source charset
502 -- p_destination_charset destination charset
503 -- p_destination_filename destination filename
504 -- p_source_separator source separator
505 -- Out:
506 -- X_CONCURRENT_REQUEST_ID Concrrent Request ID
507 -- X_PROGRAM Program
508
509 -- DESIGN REFERENCES:
510 -- None
511 --
512 -- CHANGE HISTORY:
513 -- 06/02/2006 Shujuan Yan Created
514 -- 27/04/2007 Joy liu Updated
515 -- the order and number of parameter is changed
516 -- 19/11/2012 Jar Wang Updated for CANO BANKING
517 --===========================================================================
518 PROCEDURE Submit_FSG_XML_Report(errbuf OUT NOCOPY VARCHAR2,
519 retcode OUT NOCOPY VARCHAR2,
520
521 P_DATA_ACCESS_SET_ID IN NUMBER,
522 P_BSV IN VARCHAR2,
523 P_LEGAL_ID IN NUMBER,
524 P_COA_ID IN NUMBER,
525 P_ADHOC_PREFIX IN VARCHAR2,
526 P_INDUSTRY IN VARCHAR2,
527 P_FLEX_CODE IN VARCHAR2,
528 P_DEFAULT_LEDGER_SHORT_NAME IN VARCHAR2,
529 P_REPORT_ID IN NUMBER,
530 P_ROW_SET_ID IN NUMBER,
531 P_COLUMN_SET_ID IN NUMBER,
532 p_PERIOD_NAME IN VARCHAR2,
533 p_UNIT_OF_MEASURE_ID IN VARCHAR2,
534 P_ROUNDING_OPTION IN VARCHAR2,
535 P_SEGMENT_OVERRIDE IN VARCHAR2,
536 P_CONTENT_SET_ID IN NUMBER,
537 P_ROW_ORDER_ID IN NUMBER,
538 P_REPORT_DISPLAY_SET_ID IN NUMBER,
539 P_OUTPUT_OPTION IN VARCHAR2,
540 P_EXCEPTIONS_FLAG IN VARCHAR2,
541 p_MINIMUM_DISPLAY_LEVEL IN NUMBER,
542 p_ACCOUNTING_DATE IN VARCHAR2,
543 P_PARAMETER_SET_ID IN NUMBER,
544 p_PAGE_LENGTH IN NUMBER,
545
546 p_subrequest_id IN NUMBER,
547 P_APPL_DEFLT_NAME IN VARCHAR2,
548
549 p_template IN VARCHAR2,
550 p_template_locale IN VARCHAR2,
551 p_output_format IN VARCHAR2,
552 p_source_charset IN VARCHAR2,
553 p_destination_charset IN VARCHAR2,
554 p_destination_filename IN VARCHAR2,
555 p_source_separator IN VARCHAR2) IS
556
557 l_procedure_name VARCHAR2(30) := 'Submit_FSG_XML_Report';
558 l_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
559 l_proc_level NUMBER := FND_LOG.Level_Procedure;
560 --l_stmt_level NUMBER := FND_LOG.Level_Statement;
561 l_fsg_request_id NUMBER;
562 l_xml_request_id NUMBER;
563 l_program VARCHAR2(10);
564 l_submit_request_exp EXCEPTION;
565 l_conc_succ BOOLEAN;
566 l_template_appl NUMBER; --VARCHAR2(50);
567 --result flag
568 l_xml_result_flag VARCHAR2(15);
569 l_charset_result_flag VARCHAR2(15);
570 l_filename_result_flag VARCHAR2(15);
571
572 l_charset_request_id NUMBER;
573 l_filename_request_id NUMBER;
574 BEGIN
575
579 g_module_name || '.' || l_procedure_name || '.begin',
576 --log for debug
577 IF (l_proc_level >= l_dbg_level) THEN
578 FND_LOG.STRING(l_proc_level,
580 'begin procedure');
581 END IF; --( g_proc_level >= g_dbg_level)
582
583 --call JA_CN_UTILITY.Check_Profile, if it doesn't return true, exit
584 /*IF JA_CN_UTILITY.Check_Profile() <> TRUE THEN
585 IF (l_proc_level >= l_dbg_level) THEN
586 FND_LOG.STRING(l_proc_level,
587 l_procedure_name,
588 'Check profile failed!');
589 END IF; --l_exception_level >= l_runtime_level
590 retcode := 1;
591 errbuf := '';
592 RETURN;
593 END IF; --JA_CN_UTILITY.Check_Profile() != TRUE
594 */
595 -- Submit FSG report
596 IF Submit_FSG_Request('JA',
597 P_data_access_set_id,
598 l_fsg_request_id,
599 l_program,
600 P_BSV,
601 P_LEGAL_ID,
602 P_COA_ID,
603 P_ADHOC_PREFIX,
604 P_INDUSTRY,
605 P_FLEX_CODE,
606 P_DEFAULT_LEDGER_SHORT_NAME,
607 P_REPORT_ID,
608 P_ROW_SET_ID,
609 P_COLUMN_SET_ID,
610 p_PERIOD_NAME,
611 p_UNIT_OF_MEASURE_ID,
612 P_ROUNDING_OPTION,
613 P_SEGMENT_OVERRIDE,
614 P_CONTENT_SET_ID,
615 P_ROW_ORDER_ID,
616 P_REPORT_DISPLAY_SET_ID,
617 P_OUTPUT_OPTION,
618 P_EXCEPTIONS_FLAG,
619 p_MINIMUM_DISPLAY_LEVEL,
620 to_date(p_ACCOUNTING_DATE, 'YYYY/MM/DD HH24:MI:SS'),
621
622 P_PARAMETER_SET_ID,
623 p_PAGE_LENGTH,
624
625 p_subrequest_id,
626 P_APPL_DEFLT_NAME
627
628 ) THEN
629 -- Get template application id
630 SELECT APP.APPLICATION_ID
631 INTO l_template_appl
632 FROM FND_APPLICATION_VL APP,
633 FND_CONCURRENT_PROGRAMS FCP,
634 FND_CONCURRENT_REQUESTS R
635 WHERE FCP.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
636 AND R.REQUEST_ID = l_fsg_request_id
637 AND APP.APPLICATION_ID = FCP.APPLICATION_ID;
638 --submit XML publisher concurrent program
639
640 submit_xml_publisher(l_fsg_request_id,
641 l_template_appl,
642 p_template,
643 p_template_locale,
644 p_output_format,
645
646 l_xml_request_id,
647 l_xml_result_flag);
648 IF l_xml_result_flag = 'Error' THEN
649 errbuf := fnd_message.get;
650 retcode := 2;
651 RAISE l_submit_request_exp;
652 ELSE
653 -- submit charset conversion concurrent program
654 JA_CN_UTILITY.submit_charset_conversion(l_xml_request_id,
655 p_source_charset,
656 p_destination_charset,
657 p_source_separator,
658 l_charset_request_id,
659 l_charset_result_flag);
660
661 IF l_charset_result_flag = 'Error' THEN
662 errbuf := fnd_message.get;
663 retcode := 2;
664 RAISE l_submit_request_exp;
665 ELSE
666 --submit change output filename concurrent program
667 JA_CN_UTILITY.change_output_filename(l_xml_request_id,
668 p_destination_charset,
669 p_destination_filename,
670 l_filename_request_id,
671 l_filename_result_flag);
672 IF l_filename_result_flag = 'Error' THEN
673 errbuf := fnd_message.get;
674 retcode := 2;
675 RAISE l_submit_request_exp;
676 END IF; --l_filename_result_flag = 'Error'
677 END IF; --l_charset_result_flag = 'Error'
678 END IF; --l_xml_error_flag = 'Error'
679 END IF;
680
681 IF (l_proc_level >= l_dbg_level) THEN
682 FND_LOG.STRING(l_proc_level,
683 g_module_name || '.' || l_procedure_name || '.end',
684 'end procedure');
685 END IF; --( g_proc_level >= g_dbg_level)
686 EXCEPTION
687 WHEN l_submit_request_exp THEN
688 fnd_file.put_line(fnd_file.output, SQLCODE || ':' || SQLERRM);
689 l_conc_succ := fnd_concurrent.set_completion_status(status => 'ERROR',
690 message => SQLCODE || ':' ||
691 SQLERRM);
692 --log for debug
693 IF (l_proc_level >= l_dbg_level) THEN
694 fnd_log.STRING(l_proc_level,
695 g_module_name || '.' || l_procedure_name ||
696 '. Submit_Request_Exception ',
697 SQLCODE || ':' || SQLERRM);
698 END IF; --(l_proc_level >= l_dbg_level)
699 WHEN OTHERS THEN
700 fnd_file.put_line(fnd_file.output, SQLCODE || ':' || SQLERRM);
701 l_conc_succ := fnd_concurrent.set_completion_status(status => 'ERROR',
702 message => SQLCODE || ':' ||
703 SQLERRM);
704 --log for debug
705 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
706 fnd_log.STRING(fnd_log.level_unexpected,
707 g_module_name || l_procedure_name ||
708 '. OTHER_EXCEPTION ',
709 SQLCODE || SQLERRM);
710 END IF;
711 RAISE;
712 END Submit_FSG_XML_Report;
713
714 --==========================================================================
715 -- PROCEDURE NAME:
716 -- Get_FSG_Override
717 -- PARAMETERS:
718 -- IN:
719 -- P_LEDGER_ID Ledger Id
720 -- P_REPORT_ID FSG Report Id
721 --
722 -- DESIGN REFERENCES:
723 -- None
724 --
725 -- CHANGE HISTORY:
726 -- 19/Feb/2013 Jar Wang Created
727 --==========================================================================
728 Function Get_FSG_Override(P_LEDGER_ID PLS_INTEGER,P_REPORT_ID PLS_INTEGER,P_BSV VARCHAR2) RETURN VARCHAR2
729 IS
730 CURSOR cur_segments is
731 select application_column_name, default_value
732 from FND_ID_FLEX_SEGMENTS_VL
733 where id_flex_code = 'GL#'
734 and id_flex_num = (select chart_of_accounts_id
735 from gl_ledgers
736 where ledger_id =P_LEDGER_ID);
737 l_segment cur_segments%ROWTYPE;
738 l_bsv_segment varchar2(20);
739 l_coa_id pls_integer;
740 l_delimiter varchar2(2);
741 l_override VARCHAR2(1000);
742 BEGIN
743 select chart_of_accounts_id
744 into l_coa_id
745 from gl_ledgers
746 where ledger_id = P_LEDGER_ID;
747 select fnd_flex_ext.get_delimiter('SQLGL',
748 'GL#',
749 l_coa_id
750 ) into l_delimiter from dual;
751 select segment_override
752 into l_override
753 from rg_reports rp
754 where rp.report_id = P_REPORT_ID;
755
756 SELECT fs.application_column_name
757 into l_bsv_segment
758 FROM FND_ID_FLEX_SEGMENTS_VL fs, fnd_segment_attribute_values av
759 WHERE fs.application_column_name = av.application_column_name
760 AND av.id_flex_code = 'GL#'
761 AND fs.id_flex_code = av.id_flex_code
762 AND av.id_flex_num = l_coa_id
763 AND fs.application_id = 101
764 AND fs.id_flex_num = av.id_flex_num
765 AND av.segment_attribute_type in ('GL_BALANCING')
766 AND av.attribute_value = 'Y';
767
768 if l_override is null then
769 OPEN cur_segments;
770 LOOP
771 FETCH cur_segments into l_segment;
772 EXIT WHEN cur_segments%notfound;
773
774 IF l_segment.application_column_name=l_bsv_segment THEN
775 l_override := l_override || P_BSV||l_delimiter;
776 ELSE
777 l_override := l_override || l_segment.default_value||l_delimiter;
778 END IF;
779 END LOOP;
780 CLOSE cur_segments;
781 l_override :=l_delimiter|| substr(l_override,0,length(l_override)-1);
782 end if;
783 return l_override;
784 END Get_FSG_Override;
785
786
787 --==========================================================================
788 -- PROCEDURE NAME:
789 -- Get_FSG_Currency
790 -- PARAMETERS:
791 -- IN:
792 -- P_LEDGER_ID Ledger Id
793 -- P_REPORT_ID FSG Report Id
794 --
795 -- DESIGN REFERENCES:
796 -- None
797 --
798 -- CHANGE HISTORY:
799 -- 7/Feb/2013 Jar Wang Created
800 --==========================================================================
801 Function Get_FSG_Currency(P_LEDGER_ID PLS_INTEGER,P_REPORT_ID PLS_INTEGER) return varchar2
802 is
803 l_fsg_currency varchar2(10);
804 begin
805 select unit_of_measure_id
806 into l_fsg_currency
807 from rg_reports rp
808 where rp.report_id = P_REPORT_ID;
809 if l_fsg_currency is null then
810 begin
811 select CURRENCY_CODE
812 INTO l_fsg_currency
813 from gl_ledgers
814 where LEDGER_ID = P_LEDGER_ID
815 and ledger_category_code = 'SECONDARY';
816 exception
817 when no_data_found then
818 null;
819 end;
820 end if;
821 return l_fsg_currency;
822 exception
823 when others then
824 null;
825 end Get_FSG_Currency;
826
827 --==========================================================================
828 -- PROCEDURE NAME:
829 -- Submit_FSG_Report Private
830 --
831 -- DESCRIPTION:
832 -- This procedure is used to submit FSG reports, and then invoke the
833 -- 'Report Item Data Convert program' to covert the FSG output.
834 -- PARAMETERS:
835 -- In:
836 -- pn_data_access_set_id Data Acess ID
837 -- pn_coa_id Chart of Accounts ID
838 -- pv_adhoc_prefix ADHOC PREFIX
839 -- pv_industry Industry
840 -- pv_flex_code Flex Code
841 -- pv_default_ledger_short_name Default Ledger short Name
842 -- pv_period_name Period Name
843 -- pv_output_option Out Option
844 -- pv_exceptions_flag Exception
845 -- pv_accounting_date Accounting Date
846 -- pn_page_length Page Lenth
847 -- pn_subrequest_id SubRequest ID
848 -- pv_appl_deflt_name Application Default Name
849 -- pn_csf_report_id Cash Flow Statement Report ID
850 -- pn_csf_report_num Cash Flow Statement Report Number
851 -- pn_balance_sheet_id Balance Sheet Report ID
852 -- pn_balance_sheet_num Balance Sheet Report Number
853 -- pn_profit_statement_id Profit Statement Report ID
854 -- pn_profit_statement_num Profit Statement Report Number
855 -- pn_owners_equity_id Owners Equity Report ID
856 -- pn_owners_equity_num Owners Equity Report Number
857 -- pv_balance_type Balance Type
858 -- pn_legal_entity_id Legal Entity ID
859 -- pn_ledger_id Ledger ID
860 -- pv_currency_unit Currency Unit
861 -- pv_xml_template_language XML template language
862 -- pv_xml_template_territory XML template territory
863 -- pv_xml_output_format XML output format
864 --
865 -- Out:
866 -- pn_convert_reqid Report Item Data Convert program
867 --
868 -- DESIGN REFERENCES:
869 -- None
870 --
871 -- CHANGE HISTORY:
872 -- 08/02/2010 Wei Huang Created
873 -- 19/11/2012 Jar Wang Updated for CANO BANKING
874 --===========================================================================
875 PROCEDURE Submit_FSG_Report(pn_data_access_set_id IN NUMBER,
876 pn_coa_id IN NUMBER,
877 pv_adhoc_prefix IN VARCHAR2,
878 pv_industry IN VARCHAR2,
879 pv_flex_code IN VARCHAR2,
880 pv_default_ledger_short_name IN VARCHAR2,
881 pv_period_name IN VARCHAR2,
882 pv_output_option IN VARCHAR2,
883 pv_exceptions_flag IN VARCHAR2,
884 pv_accounting_date IN VARCHAR2,
885 pn_page_length IN NUMBER,
886 pn_subrequest_id IN NUMBER,
887 pv_appl_deflt_name IN VARCHAR2,
888 pn_csf_report_id IN NUMBER,
889 pn_csf_report_num IN NUMBER,
890 pn_balance_sheet_id IN NUMBER,
891 pn_balance_sheet_num IN NUMBER,
892 pn_profit_statement_id IN NUMBER,
893 pn_profit_statement_num IN NUMBER,
894 pn_owners_equity_id IN NUMBER,
895 pn_owners_equity_num IN NUMBER,
896 pv_balance_type IN VARCHAR2,
897 pv_currency_unit IN VARCHAR2,
898 pv_bsv IN VARCHAR2,
899 pn_legal_entity_id IN NUMBER,
900 pn_ledger_id IN NUMBER,
901 pv_xml_template_language IN VARCHAR2,
902 pv_xml_template_territory IN VARCHAR2,
903 pv_xml_output_format IN VARCHAR2,
904 pn_convert_reqid OUT NOCOPY NUMBER) IS
905
906 lv_procedure_name VARCHAR2(30) := 'Submit_FSG_Report';
907 ln_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
908 ln_proc_level NUMBER := FND_LOG.Level_Procedure;
909
910 lv_program VARCHAR2(10);
911 le_submit_request_exp EXCEPTION;
912
913 ln_row_set_id NUMBER;
914 ln_clolumn_set_id NUMBER;
915 lv_unit_of_measure_id VARCHAR2(15);
916 lv_rounding_option VARCHAR2(80);
917 lv_segment_override VARCHAR2(240);
918 ln_content_set_id NUMBER;
919 ln_row_order_id NUMBER;
920 ln_report_display_set_id NUMBER;
921 ln_minimum_display_level NUMBER;
922 ln_parameter_set_id NUMBER;
923
924 ln_balance_sheet_req_id NUMBER;
925 ln_profit_statement_req_id NUMBER;
926 ln_csf_report_id NUMBER;
927 ln_owners_equity_req_id NUMBER;
928 lv_currency_unit VARCHAR2(50):=pv_currency_unit;
929
930 lv_error_flag VARCHAR2(1) := 'N';
931 lb_error_status BOOLEAN;
932 lb_submit_succ BOOLEAN;
933
934 lv_company_name VARCHAR2(100) := pv_bsv;
935 ln_waiting_interval NUMBER := 10;
936 lv_dev_phase VARCHAR2(100);
937 lv_dev_status VARCHAR2(100);
938 lv_message VARCHAR2(1000);
939
940 ln_reqid_convert NUMBER;
941
942 lv_convert_req_phase fnd_lookup_values.meaning%TYPE;
943 lv_convert_req_status fnd_lookup_values.meaning%TYPE;
944 lv_convert_req_status_code fnd_lookup_values.lookup_code%TYPE;
945
946 BEGIN
947 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FSG Begin:');
948 --log for debug
949 IF (ln_proc_level >= ln_dbg_level) THEN
950 FND_LOG.STRING(ln_proc_level,
951 g_module_name || '.' || lv_procedure_name || '.begin',
952 'begin procedure');
953 END IF; --( g_proc_level >= g_dbg_level)
954
955 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FSG:Balance Sheet Report');
956 --(1) Balance Sheet Report and get this program request ID.
957 --ROW_SET_ID
958 SELECT rs.axis_set_id
959 INTO ln_row_set_id
960 FROM rg_report_axis_sets rs, rg_reports rp
961 WHERE rp.report_id = pn_balance_sheet_id
962 AND rp.row_set_id = rs.axis_set_id;
963 --COLUMN_SET_ID
964 SELECT cs.axis_set_id
965 INTO ln_clolumn_set_id
966 FROM rg_report_axis_sets cs, rg_reports rp
967 WHERE rp.report_id = pn_balance_sheet_id
968 AND rp.column_set_id = cs.axis_set_id;
969 --Unit of Measure ID/currency
970 BEGIN
971 --added by jarwang for bug#16264989
972 lv_unit_of_measure_id:= Get_FSG_Currency(pn_ledger_id,pn_balance_sheet_id) ;
973 If lv_unit_of_measure_id is null then
974 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
975 INTO lv_unit_of_measure_id
976 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
977 WHERE ldg.ledger_id = acc.default_ledger_id
978 AND acc.access_set_id = pn_data_access_set_id
979 AND rp.report_id = pn_balance_sheet_id;
980 end if;
981
982 select MEANING into lv_currency_unit
983 from RG_REPORT_AXES rga, RG_LOOKUPS LK
984 where LK.LOOKUP_CODE = rga.display_units
985 and LK.LOOKUP_TYPE = 'DISPLAY_UNITS'
986 AND AXIS_SET_ID = ln_clolumn_set_id
987 and rownum = 1
988 ORDER BY AXIS_SEQ;
989 if nvl(lv_currency_unit,'-10')='-10' then
990 SELECT LK.MEANING
991 INTO lv_currency_unit
992 FROM RG_LOOKUPS LK
993 WHERE LK.LOOKUP_TYPE = 'DISPLAY_UNITS'
994 and LOOKUP_CODE = 0;
995 end if;
996 EXCEPTION
997 WHEN NO_DATA_FOUND THEN
998 NULL;
999 END;
1000 -- Rounding Option
1001 BEGIN
1002 SELECT l.meaning
1003 INTO lv_rounding_option
1004 FROM rg_lookups l, rg_reports r
1005 WHERE r.report_id = pn_balance_sheet_id
1006 AND l.lookup_type = 'ROUNDING_OPTION'
1007 AND l.lookup_code = r.rounding_option;
1008 EXCEPTION
1009 WHEN NO_DATA_FOUND THEN
1010 NULL;
1011 END;
1012 -- Segment Override
1013 BEGIN
1014 --added by jarwang for bug#16264989
1015 lv_segment_override:= Get_FSG_Override(pn_ledger_id,pn_balance_sheet_id,pv_bsv);
1016
1017 EXCEPTION
1018 WHEN NO_DATA_FOUND THEN
1019 NULL;
1020 END;
1021 --CONTENT_SET_ID
1022 BEGIN
1023 SELECT cs.content_set_id
1024 INTO ln_content_set_id
1025 FROM rg_report_content_sets cs, rg_reports rp
1026 WHERE rp.report_id = pn_balance_sheet_id
1027 AND rp.content_set_id = cs.content_set_id(+);
1028 EXCEPTION
1029 WHEN NO_DATA_FOUND THEN
1030 NULL;
1031 END;
1032 --ROW_ORDER_ID
1033 BEGIN
1034 SELECT ro.row_order_id
1035 INTO ln_row_order_id
1036 FROM rg_row_orders ro, rg_reports rp
1037 WHERE rp.report_id = pn_balance_sheet_id
1038 AND rp.row_order_id = ro.row_order_id(+);
1039 EXCEPTION
1040 WHEN NO_DATA_FOUND THEN
1041 NULL;
1042 END;
1043 --REPORT_DISPLAY_SET_ID
1044 BEGIN
1045 SELECT ds.report_display_set_id
1046 INTO ln_report_display_set_id
1047 FROM rg_report_display_sets ds, rg_reports rp
1048 WHERE rp.report_id = pn_balance_sheet_id
1049 AND rp.report_display_set_id = ds.report_display_set_id(+);
1050 EXCEPTION
1051 WHEN NO_DATA_FOUND THEN
1052 NULL;
1053 END;
1054 --MINIMUM_DISPLAY_LEVEL
1055 BEGIN
1056 SELECT lk.lookup_code
1057 INTO ln_minimum_display_level
1058 FROM rg_reports rp, rg_lookups lk
1059 WHERE rp.report_id = pn_balance_sheet_id
1060 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
1061 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
1062 EXCEPTION
1063 WHEN NO_DATA_FOUND THEN
1064 NULL;
1065 END;
1066 --PARAMETER_SET_ID
1067 BEGIN
1068 SELECT rp.parameter_set_id
1069 INTO ln_parameter_set_id
1070 FROM rg_reports rp
1071 WHERE rp.report_id = pn_balance_sheet_id;
1072 EXCEPTION
1073 WHEN NO_DATA_FOUND THEN
1074 NULL;
1075 END;
1076 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Submit FSG:Balance Sheet Report');
1077 -- Submit Balance Sheet Report
1078 lb_submit_succ := Submit_FSG_Request('JA',
1079 pn_data_access_set_id,
1080 ln_balance_sheet_req_id,
1081 lv_program,
1082 pv_bsv,
1083 pn_legal_entity_id,
1084 pn_coa_id,
1085 pv_adhoc_prefix,
1086 pv_industry,
1087 pv_flex_code,
1088 pv_default_ledger_short_name,
1089 pn_balance_sheet_id,
1090 ln_row_set_id,
1091 ln_clolumn_set_id,
1092 pv_period_name,
1093 lv_unit_of_measure_id,
1094 lv_rounding_option,
1095 lv_segment_override,
1096 ln_content_set_id,
1097 ln_row_order_id,
1098 ln_report_display_set_id,
1099 pv_output_option,
1100 pv_exceptions_flag,
1101 ln_minimum_display_level,
1102 to_date(pv_accounting_date,
1103 'YYYY/MM/DD HH24:MI:SS'),
1104 ln_parameter_set_id,
1105 pn_page_length,
1106 pn_subrequest_id,
1107 pv_appl_deflt_name);
1108 IF lb_submit_succ THEN
1109 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FSG:Profit Statement Report');
1110 --(2) Profit Statement Report and get this program request ID.
1111 --ROW_SET_ID
1112 SELECT rs.axis_set_id
1113 INTO ln_row_set_id
1114 FROM rg_report_axis_sets rs, rg_reports rp
1115 WHERE rp.report_id = pn_profit_statement_id
1116 AND rp.row_set_id = rs.axis_set_id;
1117 --COLUMN_SET_ID
1118 SELECT cs.axis_set_id
1119 INTO ln_clolumn_set_id
1120 FROM rg_report_axis_sets cs, rg_reports rp
1121 WHERE rp.report_id = pn_profit_statement_id
1122 AND rp.column_set_id = cs.axis_set_id;
1123 --Unit of Measure ID/currency
1124 BEGIN
1125 --added by jarwang for bug#16264989
1126 lv_unit_of_measure_id:= Get_FSG_Currency(pn_ledger_id,pn_profit_statement_id) ;
1127 If lv_unit_of_measure_id is null then
1128 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
1129 INTO lv_unit_of_measure_id
1130 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
1131 WHERE ldg.ledger_id = acc.default_ledger_id
1132 AND acc.access_set_id = pn_data_access_set_id
1133 AND rp.report_id = pn_profit_statement_id;
1134 end if;
1135
1136 select MEANING into lv_currency_unit
1137 from RG_REPORT_AXES rga, RG_LOOKUPS LK
1138 where LK.LOOKUP_CODE = rga.display_units
1139 and LK.LOOKUP_TYPE = 'DISPLAY_UNITS'
1140 AND AXIS_SET_ID = ln_clolumn_set_id
1141 and rownum = 1
1142 ORDER BY AXIS_SEQ;
1143 if nvl(lv_currency_unit,'-10')='-10' then
1144 SELECT LK.MEANING
1145 INTO lv_currency_unit
1146 FROM RG_LOOKUPS LK
1147 WHERE LK.LOOKUP_TYPE = 'DISPLAY_UNITS'
1148 and LOOKUP_CODE = 0;
1149 end if;
1150 EXCEPTION
1151 WHEN NO_DATA_FOUND THEN
1152 NULL;
1153 END;
1154 -- Rounding Option
1155 BEGIN
1156 SELECT l.meaning
1157 INTO lv_rounding_option
1158 FROM rg_lookups l, rg_reports r
1159 WHERE r.report_id = pn_profit_statement_id
1160 AND l.lookup_type = 'ROUNDING_OPTION'
1161 AND l.lookup_code = r.rounding_option;
1162 EXCEPTION
1163 WHEN NO_DATA_FOUND THEN
1164 NULL;
1165 END;
1166 -- Segment Override
1167 BEGIN
1168 --added by jarwang for bug#16264989
1169 lv_segment_override:= Get_FSG_Override(pn_ledger_id,pn_profit_statement_id,pv_bsv);
1170 EXCEPTION
1171 WHEN NO_DATA_FOUND THEN
1172 NULL;
1173 END;
1174 --CONTENT_SET_ID
1175 BEGIN
1176 SELECT cs.content_set_id
1177 INTO ln_content_set_id
1178 FROM rg_report_content_sets cs, rg_reports rp
1179 WHERE rp.report_id = pn_profit_statement_id
1180 AND rp.content_set_id = cs.content_set_id(+);
1181 EXCEPTION
1182 WHEN NO_DATA_FOUND THEN
1183 NULL;
1184 END;
1185 --ROW_ORDER_ID
1186 BEGIN
1187 SELECT ro.row_order_id
1188 INTO ln_row_order_id
1189 FROM rg_row_orders ro, rg_reports rp
1190 WHERE rp.report_id = pn_profit_statement_id
1191 AND rp.row_order_id = ro.row_order_id(+);
1192 EXCEPTION
1193 WHEN NO_DATA_FOUND THEN
1194 NULL;
1195 END;
1196 --REPORT_DISPLAY_SET_ID
1197 BEGIN
1198 SELECT ds.report_display_set_id
1199 INTO ln_report_display_set_id
1200 FROM rg_report_display_sets ds, rg_reports rp
1201 WHERE rp.report_id = pn_profit_statement_id
1202 AND rp.report_display_set_id = ds.report_display_set_id(+);
1203 EXCEPTION
1204 WHEN NO_DATA_FOUND THEN
1205 NULL;
1206 END;
1207 --MINIMUM_DISPLAY_LEVEL
1208 BEGIN
1209 SELECT lk.lookup_code
1210 INTO ln_minimum_display_level
1211 FROM rg_reports rp, rg_lookups lk
1212 WHERE rp.report_id = pn_profit_statement_id
1213 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
1214 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
1215 EXCEPTION
1216 WHEN NO_DATA_FOUND THEN
1217 NULL;
1218 END;
1219 --PARAMETER_SET_ID
1220 BEGIN
1221 SELECT rp.parameter_set_id
1222 INTO ln_parameter_set_id
1223 FROM rg_reports rp
1224 WHERE rp.report_id = pn_profit_statement_id;
1225 EXCEPTION
1226 WHEN NO_DATA_FOUND THEN
1227 NULL;
1228 END;
1229
1230 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FSG:Submit Profit Statement Report');
1231 -- Submit Profit Statement Report
1232 lb_submit_succ := Submit_FSG_Request('JA',
1233 pn_data_access_set_id,
1234 ln_profit_statement_req_id,
1235 lv_program,
1236 pv_bsv,
1237 pn_legal_entity_id,
1238 pn_coa_id,
1239 pv_adhoc_prefix,
1240 pv_industry,
1241 pv_flex_code,
1242 pv_default_ledger_short_name,
1243 pn_profit_statement_id,
1244 ln_row_set_id,
1245 ln_clolumn_set_id,
1246 pv_period_name,
1247 lv_unit_of_measure_id,
1248 lv_rounding_option,
1249 lv_segment_override,
1250 ln_content_set_id,
1251 ln_row_order_id,
1252 ln_report_display_set_id,
1253 pv_output_option,
1254 pv_exceptions_flag,
1255 ln_minimum_display_level,
1256 to_date(pv_accounting_date,
1257 'YYYY/MM/DD HH24:MI:SS'),
1258 ln_parameter_set_id,
1259 pn_page_length,
1260 pn_subrequest_id,
1261 pv_appl_deflt_name);
1262 END IF;
1263 IF lb_submit_succ THEN
1264 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FSG:Owners Equity Report');
1265 -- (3) Statement of Cash flows Report and get this program request ID.
1266 --ROW_SET_ID
1267 SELECT rs.axis_set_id
1268 INTO ln_row_set_id
1269 FROM rg_report_axis_sets rs, rg_reports rp
1270 WHERE rp.report_id = pn_csf_report_id
1271 AND rp.row_set_id = rs.axis_set_id;
1272 --COLUMN_SET_ID
1273 SELECT cs.axis_set_id
1274 INTO ln_clolumn_set_id
1275 FROM rg_report_axis_sets cs, rg_reports rp
1276 WHERE rp.report_id = pn_csf_report_id
1277 AND rp.column_set_id = cs.axis_set_id;
1278 --Unit of Measure ID/currency
1279 BEGIN
1280 --added by jarwang for bug#16264989
1281 lv_unit_of_measure_id:= Get_FSG_Currency(pn_ledger_id,pn_csf_report_id) ;
1282 If lv_unit_of_measure_id is null then
1283 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
1284 INTO lv_unit_of_measure_id
1285 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
1286 WHERE ldg.ledger_id = acc.default_ledger_id
1287 AND acc.access_set_id = pn_data_access_set_id
1288 AND rp.report_id = pn_csf_report_id;
1289 end if;
1290
1291 select MEANING into lv_currency_unit
1292 from RG_REPORT_AXES rga, RG_LOOKUPS LK
1293 where LK.LOOKUP_CODE = rga.display_units
1294 and LK.LOOKUP_TYPE = 'DISPLAY_UNITS'
1295 AND AXIS_SET_ID = ln_clolumn_set_id
1296 and rownum = 1
1297 ORDER BY AXIS_SEQ;
1298 if nvl(lv_currency_unit,'-10')='-10' then
1299 SELECT LK.MEANING
1300 INTO lv_currency_unit
1301 FROM RG_LOOKUPS LK
1302 WHERE LK.LOOKUP_TYPE = 'DISPLAY_UNITS'
1303 and LOOKUP_CODE = 0;
1304 end if;
1305 EXCEPTION
1306 WHEN NO_DATA_FOUND THEN
1307 NULL;
1308 END;
1309 -- Rounding Option
1310 BEGIN
1311 SELECT l.meaning
1312 INTO lv_rounding_option
1313 FROM rg_lookups l, rg_reports r
1314 WHERE r.report_id = pn_csf_report_id
1315 AND l.lookup_type = 'ROUNDING_OPTION'
1316 AND l.lookup_code = r.rounding_option;
1317 EXCEPTION
1318 WHEN NO_DATA_FOUND THEN
1319 NULL;
1320 END;
1321 -- Segment Override
1322 BEGIN
1323 --added by jarwang for bug#16264989
1324 lv_segment_override:= Get_FSG_Override(pn_ledger_id,pn_csf_report_id,pv_bsv) ;
1325 EXCEPTION
1326 WHEN NO_DATA_FOUND THEN
1327 NULL;
1328 END;
1329 --CONTENT_SET_ID
1330 BEGIN
1331 SELECT cs.content_set_id
1332 INTO ln_content_set_id
1333 FROM rg_report_content_sets cs, rg_reports rp
1334 WHERE rp.report_id = pn_csf_report_id
1335 AND rp.content_set_id = cs.content_set_id(+);
1336 EXCEPTION
1337 WHEN NO_DATA_FOUND THEN
1338 NULL;
1339 END;
1340 --ROW_ORDER_ID
1341 BEGIN
1342 SELECT ro.row_order_id
1343 INTO ln_row_order_id
1344 FROM rg_row_orders ro, rg_reports rp
1345 WHERE rp.report_id = pn_csf_report_id
1346 AND rp.row_order_id = ro.row_order_id(+);
1347 EXCEPTION
1348 WHEN NO_DATA_FOUND THEN
1349 NULL;
1350 END;
1351 --REPORT_DISPLAY_SET_ID
1352 BEGIN
1353 SELECT ds.report_display_set_id
1354 INTO ln_report_display_set_id
1355 FROM rg_report_display_sets ds, rg_reports rp
1356 WHERE rp.report_id = pn_csf_report_id
1357 AND rp.report_display_set_id = ds.report_display_set_id(+);
1358 EXCEPTION
1359 WHEN NO_DATA_FOUND THEN
1360 NULL;
1361 END;
1362 --MINIMUM_DISPLAY_LEVEL
1363 BEGIN
1364 SELECT lk.lookup_code
1365 INTO ln_minimum_display_level
1366 FROM rg_reports rp, rg_lookups lk
1367 WHERE rp.report_id = pn_csf_report_id
1368 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
1369 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
1370 EXCEPTION
1371 WHEN NO_DATA_FOUND THEN
1372 NULL;
1373 END;
1374 --PARAMETER_SET_ID
1375 BEGIN
1376 SELECT rp.parameter_set_id
1377 INTO ln_parameter_set_id
1378 FROM rg_reports rp
1379 WHERE rp.report_id = pn_csf_report_id;
1380 EXCEPTION
1381 WHEN NO_DATA_FOUND THEN
1382 NULL;
1383 END;
1384
1385
1386 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FSG:Submit Cash flows Report');
1387 -- Submit Cash flows Report Report Id
1388 lb_submit_succ := Submit_FSG_Request('JA',
1389 pn_data_access_set_id,
1390 ln_csf_report_id,
1391 lv_program,
1392 pv_bsv,
1393 pn_legal_entity_id,
1394 pn_coa_id,
1395 pv_adhoc_prefix,
1396 pv_industry,
1397 pv_flex_code,
1398 pv_default_ledger_short_name,
1399 pn_csf_report_id,
1400 ln_row_set_id,
1401 ln_clolumn_set_id,
1402 pv_period_name,
1403 lv_unit_of_measure_id,
1404 lv_rounding_option,
1405 lv_segment_override,
1406 ln_content_set_id,
1407 ln_row_order_id,
1408 ln_report_display_set_id,
1409 pv_output_option,
1410 pv_exceptions_flag,
1411 ln_minimum_display_level,
1412 to_date(pv_accounting_date,
1413 'YYYY/MM/DD HH24:MI:SS'),
1414 ln_parameter_set_id,
1415 pn_page_length,
1416 pn_subrequest_id,
1417 pv_appl_deflt_name);
1418 END IF;
1419 IF lb_submit_succ THEN
1420 --(4) Owners Equity Report and get this program request ID.
1421 --ROW_SET_ID
1422 SELECT rs.axis_set_id
1423 INTO ln_row_set_id
1424 FROM rg_report_axis_sets rs, rg_reports rp
1425 WHERE rp.report_id = pn_owners_equity_id
1426 AND rp.row_set_id = rs.axis_set_id;
1427 --COLUMN_SET_ID
1428 SELECT cs.axis_set_id
1429 INTO ln_clolumn_set_id
1430 FROM rg_report_axis_sets cs, rg_reports rp
1431 WHERE rp.report_id = pn_owners_equity_id
1432 AND rp.column_set_id = cs.axis_set_id;
1433 --Unit of Measure ID/currency
1434 BEGIN
1435 --added by jarwang for bug#16264989
1436 lv_unit_of_measure_id:= Get_FSG_Currency(pn_ledger_id,pn_owners_equity_id) ;
1437 If lv_unit_of_measure_id is null then
1438 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
1439 INTO lv_unit_of_measure_id
1440 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
1441 WHERE ldg.ledger_id = acc.default_ledger_id
1442 AND acc.access_set_id = pn_data_access_set_id
1443 AND rp.report_id = pn_owners_equity_id;
1444 end if;
1445
1446 select MEANING into lv_currency_unit
1447 from RG_REPORT_AXES rga, RG_LOOKUPS LK
1448 where LK.LOOKUP_CODE = rga.display_units
1449 and LK.LOOKUP_TYPE = 'DISPLAY_UNITS'
1450 AND AXIS_SET_ID = ln_clolumn_set_id
1451 and rownum = 1
1452 ORDER BY AXIS_SEQ;
1453 if nvl(lv_currency_unit,'-10')='-10' then
1454 SELECT LK.MEANING
1455 INTO lv_currency_unit
1456 FROM RG_LOOKUPS LK
1457 WHERE LK.LOOKUP_TYPE = 'DISPLAY_UNITS'
1458 and LOOKUP_CODE = 0;
1459 end if;
1460 EXCEPTION
1461 WHEN NO_DATA_FOUND THEN
1462 NULL;
1463 END;
1464 -- Rounding Option
1465 BEGIN
1466 SELECT l.meaning
1467 INTO lv_rounding_option
1468 FROM rg_lookups l, rg_reports r
1469 WHERE r.report_id = pn_owners_equity_id
1470 AND l.lookup_type = 'ROUNDING_OPTION'
1471 AND l.lookup_code = r.rounding_option;
1472 EXCEPTION
1473 WHEN NO_DATA_FOUND THEN
1474 NULL;
1475 END;
1476 -- Segment Override
1477 BEGIN
1478 --added by jarwang for bug#16264989
1479 lv_segment_override:= Get_FSG_Override(pn_ledger_id,pn_owners_equity_id,pv_bsv) ;
1480 EXCEPTION
1481 WHEN NO_DATA_FOUND THEN
1482 NULL;
1483 END;
1484 --CONTENT_SET_ID
1485 BEGIN
1486 SELECT cs.content_set_id
1487 INTO ln_content_set_id
1488 FROM rg_report_content_sets cs, rg_reports rp
1489 WHERE rp.report_id = pn_owners_equity_id
1490 AND rp.content_set_id = cs.content_set_id(+);
1491 EXCEPTION
1492 WHEN NO_DATA_FOUND THEN
1493 NULL;
1494 END;
1495 --ROW_ORDER_ID
1496 BEGIN
1497 SELECT ro.row_order_id
1498 INTO ln_row_order_id
1499 FROM rg_row_orders ro, rg_reports rp
1500 WHERE rp.report_id = pn_owners_equity_id
1501 AND rp.row_order_id = ro.row_order_id(+);
1502 EXCEPTION
1503 WHEN NO_DATA_FOUND THEN
1504 NULL;
1505 END;
1506 --REPORT_DISPLAY_SET_ID
1507 BEGIN
1508 SELECT ds.report_display_set_id
1509 INTO ln_report_display_set_id
1510 FROM rg_report_display_sets ds, rg_reports rp
1511 WHERE rp.report_id = pn_owners_equity_id
1512 AND rp.report_display_set_id = ds.report_display_set_id(+);
1513 EXCEPTION
1514 WHEN NO_DATA_FOUND THEN
1515 NULL;
1516 END;
1517 --MINIMUM_DISPLAY_LEVEL
1518 BEGIN
1519 SELECT lk.lookup_code
1520 INTO ln_minimum_display_level
1521 FROM rg_reports rp, rg_lookups lk
1522 WHERE rp.report_id = pn_owners_equity_id
1523 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
1524 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
1525 EXCEPTION
1526 WHEN NO_DATA_FOUND THEN
1527 NULL;
1528 END;
1529 --PARAMETER_SET_ID
1530 BEGIN
1531 SELECT rp.parameter_set_id
1532 INTO ln_parameter_set_id
1533 FROM rg_reports rp
1534 WHERE rp.report_id = pn_owners_equity_id;
1535 EXCEPTION
1536 WHEN NO_DATA_FOUND THEN
1537 NULL;
1538 END;
1539 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FSG:Submit Owners Equity Report');
1540 -- Submit Owners Equity Report Id
1541 lb_submit_succ := Submit_FSG_Request('JA',
1542 pn_data_access_set_id,
1543 ln_owners_equity_req_id,
1544 lv_program,
1545 pv_bsv,
1546 pn_legal_entity_id,
1547 pn_coa_id,
1548 pv_adhoc_prefix,
1549 pv_industry,
1550 pv_flex_code,
1551 pv_default_ledger_short_name,
1552 pn_owners_equity_id,
1553 ln_row_set_id,
1554 ln_clolumn_set_id,
1555 pv_period_name,
1556 lv_unit_of_measure_id,
1557 lv_rounding_option,
1558 lv_segment_override,
1559 ln_content_set_id,
1560 ln_row_order_id,
1561 ln_report_display_set_id,
1562 pv_output_option,
1563 pv_exceptions_flag,
1564 ln_minimum_display_level,
1565 to_date(pv_accounting_date,
1566 'YYYY/MM/DD HH24:MI:SS'),
1567 ln_parameter_set_id,
1568 pn_page_length,
1569 pn_subrequest_id,
1570 pv_appl_deflt_name);
1571 END IF;
1572 IF lb_submit_succ THEN
1573 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FSG:JACNBDCP');
1574 --------------------------------------------------------------
1575 ln_reqid_convert := FND_REQUEST.Submit_Request(application => 'JA',
1576 program => 'JACNBDCP',
1577 argument1 => ln_csf_report_id, --ln_csf_comb_req_id,
1578 argument2 => pn_csf_report_num,
1579 argument3 => ln_balance_sheet_req_id,
1580 argument4 => pn_balance_sheet_num,
1581 argument5 => ln_profit_statement_req_id,
1582 argument6 => pn_profit_statement_num,
1583 argument7 => ln_owners_equity_req_id,
1584 argument8 => pn_owners_equity_num,
1585 argument9 => lv_currency_unit,
1586 argument10 => lv_company_name);
1587 COMMIT;
1588
1589 --(5.1)Waiting for the 'Report Item Data Convert program' completed
1590 --get its status
1591 IF ln_reqid_convert <> 0 THEN
1592 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_convert,
1593 interval => ln_waiting_interval,
1594 phase => lv_convert_req_phase,
1595 status => lv_convert_req_status,
1596 dev_phase => lv_dev_phase,
1597 dev_status => lv_dev_status,
1598 message => lv_message) THEN
1599 --To get lookup code for current status
1600 SELECT lookup_code
1601 INTO lv_convert_req_status_code
1602 FROM fnd_lookup_values
1603 WHERE lookup_type = 'CP_STATUS_CODE'
1604 AND view_application_id = 0
1605 AND security_group_id = 0
1606 AND meaning = lv_convert_req_status
1607 AND enabled_flag = 'Y'
1608 AND language = USERENV('LANG');
1609
1610
1611 --Completed with Normal
1612 IF lv_convert_req_status_code = 'C' THEN
1613 pn_convert_reqid := ln_reqid_convert;
1614 --Completed with 'Warning'
1615 ELSIF lv_convert_req_status_code = 'G' THEN
1616 lv_error_flag := 'W';
1617 --Completed with 'Error'
1618 ELSIF lv_convert_req_status_code = 'E' THEN
1619 lv_error_flag := 'E';
1620 END IF; --lv_comb_req_status_code='C'
1621 END IF; --FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_convert
1622 ELSE
1623 lv_error_flag := 'E';
1624 END IF; --ln_reqid_convert<>0
1625 END IF;
1626
1627 IF lv_error_flag = 'W' THEN
1628 lb_error_status := FND_CONCURRENT.Set_Completion_Status(status => 'WARNING',
1629 message => '');
1630 ELSIF lv_error_flag = 'E' THEN
1631 lb_error_status := FND_CONCURRENT.Set_Completion_Status(status => 'ERROR',
1632 message => '');
1633 END IF;
1634
1635 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FSG:End');
1636 --log for debug
1637 IF (ln_proc_level >= ln_dbg_level) THEN
1638 FND_LOG.STRING(ln_proc_level,
1639 g_module_name || '.' || lv_procedure_name,
1640 lv_error_flag);
1641 FND_LOG.STRING(ln_proc_level,
1642 g_module_name || '.' || lv_procedure_name || '.end',
1643 'end procedure');
1644 END IF;
1645
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 IF (ln_proc_level >= ln_dbg_level) THEN
1649 FND_LOG.STRING(ln_proc_level,
1650 g_module_name || '.' || lv_procedure_name ||
1651 'EXCEPTION',
1652 lv_error_flag);
1653 FND_LOG.String(ln_proc_level,
1654 g_module_name || '.' || lv_procedure_name ||
1655 '. Other_Exception ',
1656 SQLCODE || ':' || SQLERRM);
1657 END IF;
1658
1659 END Submit_FSG_Report;
1660
1661
1662
1663 end ja_cn_fsg_xml_bank_pkg;
1664
1665
1666