[Home] [Help]
PACKAGE BODY: APPS.JA_CN_PS_FSG_XML_SUBMIT_PROG
Source
1 PACKAGE BODY JA_CN_PS_FSG_XML_SUBMIT_PROG AS
2 --$Header: JACNPFXB.pls 120.0 2010/09/01 09:49:15 wuwu noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| JACNPFXB.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_Report
19 --|
20 --| HISTORY
21 --| 06-Aug-2010 Chaoqun Wu Created
22 --+======================================================================*/
23
24 --==========================================================================
25 -- PROCEDURE NAME:
26 -- Submit_FSG_Report public
27 --
28 -- DESCRIPTION:
29 -- This procedure is used to submit FSG reports, and then invoke the
30 -- 'Report Item Data Convert program' to covert the FSG output.
31 -- PARAMETERS:
32 -- In:
33 -- pn_data_access_set_id Data Acess ID
34 -- pn_coa_id Chart of Accounts ID
35 -- pv_adhoc_prefix ADHOC PREFIX
36 -- pv_industry Industry
37 -- pv_flex_code Flex Code
38 -- pv_default_ledger_short_name Default Ledger short Name
39 -- pv_period_name Period Name
40 -- pv_output_option Out Option
41 -- pv_exceptions_flag Exception
42 -- pv_accounting_date Accounting Date
43 -- pn_page_length Page Lenth
44 -- pn_subrequest_id SubRequest ID
45 -- pv_appl_deflt_name Application Default Name
46 -- pn_balance_sheet_id Balance Sheet Report ID
47 -- pn_balance_sheet_num Balance Sheet Report Number
48 -- pn_revenue_expenditure_id Revenue Expenditure Report ID
49 -- pn_revenue_expenditure_num Revenue Expenditure Report Number
50 -- pn_expenditure_detail_id Expenditure Detail Report ID
51 -- pn_expenditure_detail_num Expenditure Detail Report Number
52 -- pn_legal_entity_id Legal Entity ID
53 -- pn_ledger_id Ledger ID
54 -- pv_currency_unit Currency Unit
55 -- pv_xml_template_language XML template language
56 -- pv_xml_template_territory XML template territory
57 -- pv_xml_output_format XML output format
58 --
59 -- Out:
60 -- pn_convert_reqid Report Item Data Convert program
61 --
62 -- DESIGN REFERENCES:
63 -- None
64 --
65 -- CHANGE HISTORY:
66 -- 06-Aug-2010 Chaoqun Wu Created
67 --===========================================================================
68
69 PROCEDURE Submit_FSG_Report
70 (pn_data_access_set_id IN NUMBER,
71 pn_coa_id IN NUMBER,
72 pv_adhoc_prefix IN VARCHAR2,
73 pv_industry IN VARCHAR2,
74 pv_flex_code IN VARCHAR2,
75 pv_default_ledger_short_name IN VARCHAR2,
76 pv_period_name IN VARCHAR2,
77 pv_output_option IN VARCHAR2,
78 pv_exceptions_flag IN VARCHAR2,
79 pn_page_length IN NUMBER,
80 pn_subrequest_id IN NUMBER,
81 pv_appl_deflt_name IN VARCHAR2,
82 pn_balance_sheet_id IN NUMBER,
83 pn_balance_sheet_num IN NUMBER,
84 pn_revenue_expenditure_id IN NUMBER,
85 pn_revenue_expenditure_num IN NUMBER,
86 pn_expenditure_detail_id IN NUMBER,
87 pn_expenditure_detail_num IN NUMBER,
88 pv_currency_unit IN VARCHAR2,
89 pn_legal_entity_id IN NUMBER,
90 pn_ledger_id IN NUMBER,
91 pv_accounting_date IN VARCHAR2,
92 pv_xml_template_language IN VARCHAR2,
93 pv_xml_template_territory IN VARCHAR2,
94 pv_xml_output_format IN VARCHAR2,
95 pn_convert_reqid OUT NOCOPY NUMBER
96 )
97 IS
98
99 lv_procedure_name VARCHAR2(30) := 'Submit_FSG_XML_Report';
100 ln_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
101 ln_proc_level NUMBER := FND_LOG.Level_Procedure;
102
103 lv_program VARCHAR2(10);
104 le_submit_request_exp EXCEPTION;
105 lb_conc_succ BOOLEAN;
106
107 ln_row_set_id NUMBER;
108 ln_clolumn_set_id NUMBER;
109 lv_unit_of_measure_id VARCHAR2(15);
110 lv_rounding_option VARCHAR2(80);
111 lv_segment_override VARCHAR2(240);
112 ln_content_set_id NUMBER;
113 ln_row_order_id NUMBER;
114 ln_report_display_set_id NUMBER;
115 ln_minimum_display_level NUMBER;
116 ln_parameter_set_id NUMBER;
117
118 ln_balance_sheet_req_id NUMBER;
119 ln_revenue_expenditure_req_id NUMBER;
120 ln_expenditure_detail_req_id NUMBER;
121
122 lv_error_flag VARCHAR2(1) := 'N';
123 lb_error_status BOOLEAN;
124 lb_submit_succ BOOLEAN;
125
126 ln_reqid_fsg NUMBER;
127 lv_company_name VARCHAR2(100);
128 ln_reqid_cal NUMBER;
129 ln_waiting_interval NUMBER :=10;
130 lv_dev_phase VARCHAR2(100);
131 lv_dev_status VARCHAR2(100);
132 lv_message VARCHAR2(1000);
133 lb_xml_layout BOOLEAN;
134
135 lv_fsg_req_phase fnd_lookup_values.meaning%TYPE;
136 lv_fsg_req_status fnd_lookup_values.meaning%TYPE;
137 lv_fsg_req_status_code fnd_lookup_values.lookup_code%TYPE;
138
139 ln_reqid_convert NUMBER;
140
141 lv_convert_req_phase fnd_lookup_values.meaning%TYPE;
142 lv_convert_req_status fnd_lookup_values.meaning%TYPE;
143 lv_convert_req_status_code fnd_lookup_values.lookup_code%TYPE;
144
145
146 CURSOR C_COMPANY_NAME
147 IS
148 SELECT COMPANY_NAME
149 INTO Lv_company_name
150 FROM JA_CN_SYSTEM_PARAMETERS_ALL
151 WHERE LEGAL_ENTITY_ID= pn_legal_entity_id;
152
153 BEGIN
154 --log for debug
155 IF (ln_proc_level >= ln_dbg_level) THEN
156 FND_LOG.STRING(ln_proc_level,
157 g_module_name || '.' || lv_procedure_name || '.begin',
158 'begin procedure');
159 END IF; --( g_proc_level >= g_dbg_level)
160
161 --Call JA_CN_UTILITY.Check_Profile, if it doesn't return true, exit
162 --Profiles include ' JG: Product', which should be set to
163 --'Asia/Pacific Localizations', 'JG: Territory', which should be
164 --set to 'China' and 'JA: CNAO Legal Entity', which should be NOT NULL.
165 IF JA_CN_UTILITY.Check_Profile() <> TRUE THEN
166 IF (ln_proc_level >= ln_dbg_level) THEN
167 FND_LOG.STRING(ln_proc_level,
168 lv_procedure_name,
169 'Check profile failed!');
170 END IF; --l_exception_level >= l_runtime_level
171 RETURN;
172 END IF; --JA_CN_UTILITY.Check_Profile() != TRUE
173
174 --(1) Submit Balance Sheet Report and get this program request ID.
175 --ROW_SET_ID
176 SELECT rs.axis_set_id
177 INTO ln_row_set_id
178 FROM rg_report_axis_sets rs, rg_reports rp
179 WHERE rp.report_id = pn_balance_sheet_id
180 AND rp.row_set_id = rs.axis_set_id;
181 --COLUMN_SET_ID
182 SELECT cs.axis_set_id
183 INTO ln_clolumn_set_id
184 FROM rg_report_axis_sets cs, rg_reports rp
185 WHERE rp.report_id = pn_balance_sheet_id
186 AND rp.column_set_id = cs.axis_set_id;
187 --Unit of Measure ID/currency
188 BEGIN
189 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
190 INTO lv_unit_of_measure_id
191 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
192 WHERE ldg.ledger_id = acc.default_ledger_id
193 AND acc.access_set_id = pn_data_access_set_id
194 AND rp.report_id = pn_balance_sheet_id;
195 EXCEPTION
196 WHEN NO_DATA_FOUND THEN
197 NULL;
198 END;
199 -- Rounding Option
200 BEGIN
201 SELECT l.meaning
202 INTO lv_rounding_option
203 FROM rg_lookups l, rg_reports r
204 WHERE r.report_id = pn_balance_sheet_id
205 AND l.lookup_type = 'ROUNDING_OPTION'
206 AND l.lookup_code = r.rounding_option;
207 EXCEPTION
208 WHEN NO_DATA_FOUND THEN
209 NULL;
210 END;
211 -- Segment Override
212 BEGIN
213 SELECT segment_override
214 INTO lv_segment_override
215 FROM rg_reports
216 WHERE report_id = pn_balance_sheet_id;
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 NULL;
220 END;
221 --CONTENT_SET_ID
222 BEGIN
223 SELECT cs.content_set_id
224 INTO ln_content_set_id
225 FROM rg_report_content_sets cs, rg_reports rp
226 WHERE rp.report_id = pn_balance_sheet_id
227 AND rp.content_set_id = cs.content_set_id(+);
228 EXCEPTION
229 WHEN NO_DATA_FOUND THEN
230 NULL;
231 END;
232 --ROW_ORDER_ID
233 BEGIN
234 SELECT ro.row_order_id
235 INTO ln_row_order_id
236 FROM rg_row_orders ro, rg_reports rp
237 WHERE rp.report_id = pn_balance_sheet_id
238 AND rp.row_order_id = ro.row_order_id(+);
239 EXCEPTION
240 WHEN NO_DATA_FOUND THEN
241 NULL;
242 END;
243 --REPORT_DISPLAY_SET_ID
244 BEGIN
245 SELECT ds.report_display_set_id
246 INTO ln_report_display_set_id
247 FROM rg_report_display_sets ds, rg_reports rp
248 WHERE rp.report_id = pn_balance_sheet_id
249 AND rp.report_display_set_id = ds.report_display_set_id(+);
250 EXCEPTION
251 WHEN NO_DATA_FOUND THEN
252 NULL;
253 END;
254 --MINIMUM_DISPLAY_LEVEL
255 BEGIN
256 SELECT lk.lookup_code
257 INTO ln_minimum_display_level
258 FROM rg_reports rp, rg_lookups lk
259 WHERE rp.report_id = pn_balance_sheet_id
260 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
261 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
262 EXCEPTION
263 WHEN NO_DATA_FOUND THEN
264 NULL;
265 END;
266 --PARAMETER_SET_ID
267 BEGIN
268 SELECT rp.parameter_set_id
269 INTO ln_parameter_set_id
270 FROM rg_reports rp
271 WHERE rp.report_id = pn_balance_sheet_id;
272 EXCEPTION
273 WHEN NO_DATA_FOUND THEN
274 NULL;
275 END;
276
277 -- Submit Balance Sheet Report
278 lb_submit_succ := JA_CN_FSG_XML_SUBMIT_PROG.Submit_FSG_Request('JA',
279 pn_data_access_set_id,
280 ln_balance_sheet_req_id,
281 lv_program,
282 pn_coa_id,
283 pv_adhoc_prefix,
284 pv_industry,
285 pv_flex_code,
286 pv_default_ledger_short_name,
287 pn_balance_sheet_id,
288 ln_row_set_id,
289 ln_clolumn_set_id,
290 pv_period_name,
291 lv_unit_of_measure_id,
292 lv_rounding_option,
293 lv_segment_override,
294 ln_content_set_id,
295 ln_row_order_id,
296 ln_report_display_set_id,
297 pv_output_option,
298 pv_exceptions_flag,
299 ln_minimum_display_level,
300 to_date(pv_accounting_date, 'YYYY/MM/DD HH24:MI:SS'),
301 ln_parameter_set_id,
302 pn_page_length,
303 pn_subrequest_id,
304 pv_appl_deflt_name
305 );
306
307 --(2) Submit Revenue and Expenditure Report and get this program request ID.
308 --ROW_SET_ID
309 SELECT rs.axis_set_id
310 INTO ln_row_set_id
311 FROM rg_report_axis_sets rs, rg_reports rp
312 WHERE rp.report_id = pn_revenue_expenditure_id --parameter: pn_revenue_expenditure_id
313 AND rp.row_set_id = rs.axis_set_id;
314 --COLUMN_SET_ID
315 SELECT cs.axis_set_id
316 INTO ln_clolumn_set_id
317 FROM rg_report_axis_sets cs, rg_reports rp
318 WHERE rp.report_id = pn_revenue_expenditure_id --parameter: pn_revenue_expenditure_id
319 AND rp.column_set_id = cs.axis_set_id;
320 --Unit of Measure ID/currency
321 BEGIN
322 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
323 INTO lv_unit_of_measure_id
324 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
325 WHERE ldg.ledger_id = acc.default_ledger_id
326 AND acc.access_set_id = pn_data_access_set_id
327 AND rp.report_id = pn_revenue_expenditure_id; --parameter: pn_revenue_expenditure_id
328 EXCEPTION
329 WHEN NO_DATA_FOUND THEN
330 NULL;
331 END;
332 -- Rounding Option
333 BEGIN
334 SELECT l.meaning
335 INTO lv_rounding_option
336 FROM rg_lookups l, rg_reports r
337 WHERE r.report_id = pn_revenue_expenditure_id --parameter: pn_revenue_expenditure_id
338 AND l.lookup_type = 'ROUNDING_OPTION'
339 AND l.lookup_code = r.rounding_option;
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 NULL;
343 END;
344 -- Segment Override
345 BEGIN
346 SELECT segment_override
347 INTO lv_segment_override
348 FROM rg_reports
349 WHERE report_id = pn_revenue_expenditure_id; --parameter: pn_revenue_expenditure_id
350 EXCEPTION
351 WHEN NO_DATA_FOUND THEN
352 NULL;
353 END;
354 --CONTENT_SET_ID
355 BEGIN
356 SELECT cs.content_set_id
357 INTO ln_content_set_id
358 FROM rg_report_content_sets cs, rg_reports rp
359 WHERE rp.report_id = pn_revenue_expenditure_id --parameter: pn_revenue_expenditure_id
360 AND rp.content_set_id = cs.content_set_id(+);
361 EXCEPTION
362 WHEN NO_DATA_FOUND THEN
363 NULL;
364 END;
365 --ROW_ORDER_ID
366 BEGIN
367 SELECT ro.row_order_id
368 INTO ln_row_order_id
369 FROM rg_row_orders ro, rg_reports rp
370 WHERE rp.report_id = pn_revenue_expenditure_id --parameter: pn_revenue_expenditure_id
371 AND rp.row_order_id = ro.row_order_id(+);
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN
374 NULL;
375 END;
376 --REPORT_DISPLAY_SET_ID
377 BEGIN
378 SELECT ds.report_display_set_id
379 INTO ln_report_display_set_id
380 FROM rg_report_display_sets ds, rg_reports rp
381 WHERE rp.report_id = pn_revenue_expenditure_id --parameter: pn_revenue_expenditure_id
382 AND rp.report_display_set_id = ds.report_display_set_id(+);
383 EXCEPTION
384 WHEN NO_DATA_FOUND THEN
385 NULL;
386 END;
387 --MINIMUM_DISPLAY_LEVEL
388 BEGIN
389 SELECT lk.lookup_code
390 INTO ln_minimum_display_level
391 FROM rg_reports rp, rg_lookups lk
392 WHERE rp.report_id = pn_revenue_expenditure_id --parameter: pn_revenue_expenditure_id
393 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
394 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
395 EXCEPTION
396 WHEN NO_DATA_FOUND THEN
397 NULL;
398 END;
399 --PARAMETER_SET_ID
400 BEGIN
401 SELECT rp.parameter_set_id
402 INTO ln_parameter_set_id
403 FROM rg_reports rp
404 WHERE rp.report_id = pn_revenue_expenditure_id; --parameter: pn_revenue_expenditure_id
405 EXCEPTION
406 WHEN NO_DATA_FOUND THEN
407 NULL;
408 END;
409 -- Submit Revenue and Expenditure Report
410 lb_submit_succ := JA_CN_FSG_XML_SUBMIT_PROG.Submit_FSG_Request('JA',
411 pn_data_access_set_id,
412 ln_revenue_expenditure_req_id,
413 lv_program,
414 pn_coa_id,
415 pv_adhoc_prefix,
416 pv_industry,
417 pv_flex_code,
418 pv_default_ledger_short_name,
419 pn_revenue_expenditure_id, --parameter: pn_revenue_expenditure_id,
420 ln_row_set_id,
421 ln_clolumn_set_id,
422 pv_period_name,
423 lv_unit_of_measure_id,
424 lv_rounding_option,
425 lv_segment_override,
426 ln_content_set_id,
427 ln_row_order_id,
428 ln_report_display_set_id,
429 pv_output_option,
430 pv_exceptions_flag,
431 ln_minimum_display_level,
432 to_date(pv_accounting_date, 'YYYY/MM/DD HH24:MI:SS'),
433 ln_parameter_set_id,
434 pn_page_length,
435 pn_subrequest_id,
436 pv_appl_deflt_name
437 );
438
439 --(3) Submit Expenditure Detail Report and get this program request ID.
440 --ROW_SET_ID
441 SELECT rs.axis_set_id
442 INTO ln_row_set_id
443 FROM rg_report_axis_sets rs, rg_reports rp
444 WHERE rp.report_id = pn_expenditure_detail_id --parameter: pn_expenditure_detail_id
445 AND rp.row_set_id = rs.axis_set_id;
446 --COLUMN_SET_ID
447 SELECT cs.axis_set_id
448 INTO ln_clolumn_set_id
449 FROM rg_report_axis_sets cs, rg_reports rp
450 WHERE rp.report_id = pn_expenditure_detail_id --parameter: pn_expenditure_detail_id
451 AND rp.column_set_id = cs.axis_set_id;
452 --Unit of Measure ID/currency
453 BEGIN
454 SELECT nvl(rp.unit_of_measure_id, ldg.currency_code)
455 INTO lv_unit_of_measure_id
456 FROM rg_reports rp, gl_ledgers ldg, gl_access_sets acc
457 WHERE ldg.ledger_id = acc.default_ledger_id
458 AND acc.access_set_id = pn_data_access_set_id
459 AND rp.report_id = pn_expenditure_detail_id; --parameter: pn_expenditure_detail_id
460 EXCEPTION
461 WHEN NO_DATA_FOUND THEN
462 NULL;
463 END;
464 -- Rounding Option
465 BEGIN
466 SELECT l.meaning
467 INTO lv_rounding_option
468 FROM rg_lookups l, rg_reports r
469 WHERE r.report_id = pn_expenditure_detail_id --parameter: pn_expenditure_detail_id
470 AND l.lookup_type = 'ROUNDING_OPTION'
471 AND l.lookup_code = r.rounding_option;
472 EXCEPTION
473 WHEN NO_DATA_FOUND THEN
474 NULL;
475 END;
476 -- Segment Override
477 BEGIN
478 SELECT segment_override
479 INTO lv_segment_override
480 FROM rg_reports
481 WHERE report_id = pn_expenditure_detail_id; --parameter: pn_expenditure_detail_id
482 EXCEPTION
483 WHEN NO_DATA_FOUND THEN
484 NULL;
485 END;
486 --CONTENT_SET_ID
487 BEGIN
488 SELECT cs.content_set_id
489 INTO ln_content_set_id
490 FROM rg_report_content_sets cs, rg_reports rp
491 WHERE rp.report_id = pn_expenditure_detail_id --parameter: pn_expenditure_detail_id
492 AND rp.content_set_id = cs.content_set_id(+);
493 EXCEPTION
494 WHEN NO_DATA_FOUND THEN
495 NULL;
496 END;
497 --ROW_ORDER_ID
498 BEGIN
499 SELECT ro.row_order_id
500 INTO ln_row_order_id
501 FROM rg_row_orders ro, rg_reports rp
502 WHERE rp.report_id = pn_expenditure_detail_id --parameter: pn_expenditure_detail_id
503 AND rp.row_order_id = ro.row_order_id(+);
504 EXCEPTION
505 WHEN NO_DATA_FOUND THEN
506 NULL;
507 END;
508 --REPORT_DISPLAY_SET_ID
509 BEGIN
510 SELECT ds.report_display_set_id
511 INTO ln_report_display_set_id
512 FROM rg_report_display_sets ds, rg_reports rp
513 WHERE rp.report_id = pn_expenditure_detail_id --parameter: pn_expenditure_detail_id
514 AND rp.report_display_set_id = ds.report_display_set_id(+);
515 EXCEPTION
516 WHEN NO_DATA_FOUND THEN
517 NULL;
518 END;
519 --MINIMUM_DISPLAY_LEVEL
520 BEGIN
521 SELECT lk.lookup_code
522 INTO ln_minimum_display_level
523 FROM rg_reports rp, rg_lookups lk
524 WHERE rp.report_id = pn_expenditure_detail_id --parameter: pn_expenditure_detail_id
525 AND TO_CHAR(rp.minimum_display_level) = lk.lookup_code(+)
526 AND lk.lookup_type = 'GL_DISPLAY_LEVEL';
527 EXCEPTION
528 WHEN NO_DATA_FOUND THEN
529 NULL;
530 END;
531 --PARAMETER_SET_ID
532 BEGIN
533 SELECT rp.parameter_set_id
534 INTO ln_parameter_set_id
535 FROM rg_reports rp
536 WHERE rp.report_id = pn_expenditure_detail_id; --parameter: pn_expenditure_detail_id
537 EXCEPTION
538 WHEN NO_DATA_FOUND THEN
539 NULL;
540 END;
541 -- Submit Expenditure Detail Report
542 lb_submit_succ := JA_CN_FSG_XML_SUBMIT_PROG.Submit_FSG_Request('JA',
543 pn_data_access_set_id,
544 ln_expenditure_detail_req_id,
545 lv_program,
546 pn_coa_id,
547 pv_adhoc_prefix,
548 pv_industry,
549 pv_flex_code,
550 pv_default_ledger_short_name,
551 pn_expenditure_detail_id, --parameter: pn_expenditure_detail_id
552 ln_row_set_id,
553 ln_clolumn_set_id,
554 pv_period_name,
555 lv_unit_of_measure_id,
556 lv_rounding_option,
557 lv_segment_override,
558 ln_content_set_id,
559 ln_row_order_id,
560 ln_report_display_set_id,
561 pv_output_option,
562 pv_exceptions_flag,
563 ln_minimum_display_level,
564 to_date(pv_accounting_date, 'YYYY/MM/DD HH24:MI:SS'),
565 ln_parameter_set_id,
566 pn_page_length,
567 pn_subrequest_id,
568 pv_appl_deflt_name
569 );
570 COMMIT;
571
572 --(3.1)Waiting for the 'Expenditure Detail Report Generation' completed
573 --get its status
574 IF ln_expenditure_detail_req_id<>0
575 THEN
576 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_expenditure_detail_req_id
577 ,interval => ln_waiting_interval
578 ,phase => lv_fsg_req_phase
579 ,status => lv_fsg_req_status
580 ,dev_phase => lv_dev_phase
581 ,dev_status => lv_dev_status
582 ,message => lv_message
583 )
584 THEN
585 --To get lookup code for current status
586 SELECT
587 lookup_code
588 INTO
589 lv_fsg_req_status_code
590 FROM
591 fnd_lookup_values
592 WHERE lookup_type = 'CP_STATUS_CODE'
593 AND view_application_id=0
594 AND security_group_id=0
595 AND meaning=lv_fsg_req_status
596 AND enabled_flag='Y'
597 AND language = USERENV('LANG');
598
599 --Completed with Normal
600 IF lv_fsg_req_status_code='C'
601 THEN
602
603 OPEN C_COMPANY_NAME;
604 FETCH C_COMPANY_NAME INTO lv_company_name;
605 CLOSE C_COMPANY_NAME;
606
607 -- (4) Invoke Report Item Data Convert program to covert the FSG output
608 ln_reqid_convert:=FND_REQUEST.Submit_Request(application => 'JA'
609 ,program => 'JACNPRDC'
610 ,argument1 => ln_balance_sheet_req_id
611 ,argument2 => pn_balance_sheet_num
612 ,argument3 => ln_revenue_expenditure_req_id
613 ,argument4 => pn_revenue_expenditure_num
614 ,argument5 => ln_expenditure_detail_req_id
615 ,argument6 => pn_expenditure_detail_num
616 ,argument7 => pv_currency_unit
617 ,argument8 => Lv_company_name
618 );
619 COMMIT;
620
621 --(4.1)Waiting for the 'Report Item Data Convert program' completed
622 --get its status
623 IF ln_reqid_convert<>0
624 THEN
625 IF FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_convert
626 ,interval => ln_waiting_interval
627 ,phase => lv_convert_req_phase
628 ,status => lv_convert_req_status
629 ,dev_phase => lv_dev_phase
630 ,dev_status => lv_dev_status
631 ,message => lv_message
632 )
633 THEN
634 --To get lookup code for current status
635 SELECT
636 lookup_code
637 INTO
638 lv_convert_req_status_code
639 FROM
640 fnd_lookup_values
641 WHERE lookup_type = 'CP_STATUS_CODE'
642 AND view_application_id=0
643 AND security_group_id=0
644 AND meaning=lv_convert_req_status
645 AND enabled_flag='Y'
646 AND language = USERENV('LANG');
647
648 --Completed with Normal
649 IF lv_convert_req_status_code='C'
650 THEN
651 pn_convert_reqid := ln_reqid_convert;
652 --Completed with 'Warning'
653 ELSIF lv_convert_req_status_code='G'
654 THEN
655 lv_error_flag:='W';
656 --Completed with 'Error'
657 ELSIF lv_convert_req_status_code='E'
658 THEN
659 lv_error_flag:='E';
660 END IF; --lv_fsg_req_status_code='C'
661 END IF; --FND_CONCURRENT.Wait_For_Request(request_id => ln_reqid_convert
662 ELSE
663 lv_error_flag:='E';
664 END IF; --ln_reqid_convert<>0
665
666 --Completed with 'Warning'
667 ELSIF lv_fsg_req_status_code='G'
668 THEN
669 lv_error_flag:='W';
670 --Completed with 'Error'
671 ELSIF lv_fsg_req_status_code='E'
672 THEN
673 lv_error_flag:='E';
674 END IF; --lv_fsg_req_status_code='C'
675 END IF; --FND_CONCURRENT.Wait_For_Request(request_id => ln_expenditure_detail_req_id
676 ELSE
677 lv_error_flag:='E';
678 END IF; --ln_expenditure_detail_req_id<>0
679
680 IF lv_error_flag='W'
681 THEN
682 lb_error_status:=FND_CONCURRENT.Set_Completion_Status(status => 'WARNING'
683 ,message => ''
684 );
685 ELSIF lv_error_flag='E'
686 THEN
687 lb_error_status:=FND_CONCURRENT.Set_Completion_Status(status => 'ERROR'
688 ,message => ''
689 );
690 END IF;
691
692 --log for debug
693 IF (ln_proc_level >= ln_dbg_level)
694 THEN
695 FND_LOG.STRING(ln_proc_level
696 ,g_module_name||'.'||lv_procedure_name
697 ,lv_error_flag
698 );
699 FND_LOG.STRING(ln_proc_level
700 ,g_module_name||'.'||lv_procedure_name||'.end'
701 ,'end procedure'
702 );
703 END IF;
704
705 EXCEPTION
706 WHEN OTHERS THEN
707 IF (ln_proc_level >= ln_dbg_level)
708 THEN
709 FND_LOG.STRING(ln_proc_level
710 ,g_module_name||'.'||lv_procedure_name||'EXCEPTION'
711 ,lv_error_flag
712 );
713 FND_LOG.String(ln_proc_level
714 ,g_module_name||'.'||lv_procedure_name||'. Other_Exception '
715 ,SQLCODE||':'||SQLERRM
716 );
717 END IF;
718
719 END Submit_FSG_Report;
720
721 END JA_CN_PS_FSG_XML_SUBMIT_PROG;