关于EBS报表出现‘ 已完成 警告’的问题

在EBS中传统开发报表，我们使用Reports Developer10G来生成XML，然后利用Oracle XML Publisher Desktop 来设计模板，这样的好处是XML取数（SQL）和生成非常方便，我们不用担心。另一种开发方式是利用PLSQL生成XML，然后利用Oracle XML Publisher Desktop 来设计模板。还有一种方式是利用其它程序语言生成XML（PSLQL\JAVA等），利用XML Publisher API来读取XML数据流关联到模板。这种方式比较快速的打开，因为它不走EBS中的并发管理器。

这里我们看一下PLSQL中生成XML的一些问题。直接上图

之前描述错误，这里可以看到利用FND_FILE.PUT_LINE,针对中文的情况 ，请使用  

OUT_PUT('<?xml version="1.0" ?>');  不要使用-- <?xml version = ''1.0'' encoding = ''GBK''?>，而在Reports 工具中却要这么做。

更加方便请使用ORACLE中内置XML引擎来生成。如下，或者定义XMLTYPE来组合。

关于使用的示例链接：http://www.orafaq.com/wiki/DBMS_XMLGEN

更加详细的使用步骤：

Developing XML Publisher Report - using Data Source as PL/SQL Stored Procedure

Background:

Developing sample XML Publisher Report with  Executable Method as 'PL/SQL Stored Procedure'

In my previous post  http://orclapp.blogspot.in/2011/11/using-data-template.html i have explained developing XML Publisher Report using Data Template as Data Source

Prerequisite for the below Example:

1. Create a table 
CREATE TABLE demo_products
(  product_code   NUMBER,
   product_name   VARCHAR2 (100));
2. Insert Values
INSERT INTO demo_products
     VALUES (569, 'Oracle Cost Management');
3. Issue Commit

1. Create a Package Spec & Body with a single Procedure
Spec:

CREATE OR REPLACE PACKAGE APPS.MY_PACKAGE
AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER);
END MY_PACKAGE;
/ 
Body:
CREATE OR REPLACE PACKAGE BODY APPS.MY_PACKAGE
AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER)
   IS
      l_qryCtx      DBMS_XMLGEN.ctxHandle;
      l_query       VARCHAR2 (32000);
      l_length      NUMBER (10);
      l_xmlstr      VARCHAR2 (32000);
      l_offset      NUMBER (10) := 32000;
      l_retrieved   NUMBER (10) := 0;
      l_result      CLOB;
      l_no_rows     NUMBER;
   BEGIN
      l_query := 'SELECT product_code, product_name 
         FROM demo_products 
       WHERE product_code = ' || p_product_id;

      l_qryCtx := DBMS_XMLGEN.newContext (l_query);

      -- set rowset tag to PRODUCTS and row tag to  PRO_DETAILS
      DBMS_XMLGEN.setRowSetTag (l_qryCtx, 'PRODUCTS');
      DBMS_XMLGEN.setRowTag (l_qryCtx, 'PRO_DETAILS');

      -- now get the result
      l_result := DBMS_XMLGEN.getXML (l_qryCtx);
      l_no_rows := DBMS_XMLGEN.GETNUMROWSPROCESSED (l_qryCtx);
      FND_FILE.put_line (FND_FILE.LOG, 'No of rows processed= ' || l_no_rows);

      l_length := NVL (DBMS_LOB.getlength (l_result), 0);
      FND_FILE.put_line (FND_FILE.LOG, 'Length= ' || l_length);

      LOOP
         EXIT WHEN l_length = l_retrieved;

         IF (l_length - l_retrieved) < 32000
         THEN
            SELECT SUBSTR (l_result, l_retrieved + 1) INTO l_xmlstr FROM DUAL;

            l_retrieved := l_length;
            fnd_file.put_line (fnd_file.output, l_xmlstr);
         ELSE
            SELECT SUBSTR (l_result, l_retrieved + 1, l_offset)
              INTO l_xmlstr
              FROM DUAL;

            l_retrieved := l_retrieved + l_offset;
            fnd_file.put_line (fnd_file.output, l_xmlstr);
         END IF;
      END LOOP;

      DBMS_XMLGEN.closeContext (l_qryCtx);
   EXCEPTION
      WHEN OTHERS THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
         raise_application_error (-20001, 'Error in procedure MY_PACKAGE.report');
   END REPORT;
END MY_PACKAGE;

/

2. Define Executable
Navigation: Application Developer > Concurrent > Executable 

Provide Executable, Short Name, Application, Description & also
Execution Method:  PL/SQL Stored Procedure
Execution File Name:  MY_PACKAGE.REPORT

3. Define Concurrent Program
Navigation: Application Developer > Concurrent > Program 

Provide Program, Short name, Application, Description & also
- Executable Name as defined in the above step

- Output Format should be  XML
- Define a Parameter p_product_id
- Associate Concurrent Program to the Request Group. 

4. Create Data Definition
Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
The code should be exactly same as concurrent program short name.

5. Create Template. Register Template with the XML Publisher
Navigation: XML Publisher Administrator -> Templates -> Create Template

6. Run the Concurrent Program to see the output

Note:
If you need any extra help from Step2 refer my previous post  http://orclapp.blogspot.in/2011/11/using-data-template.html

select dbms_xmlgen.getxml('select t.* from dba_users t where t.user_id<20 ') from dual;

select dbms_xmlgen.getxml('
  2  select department_id, department_name,
  3  cursor(select first_name, last_name
  4  from employees e
  5  where e.department_id = d.department_id) emp_row
  6  from departments d
  7  where rownum < 4
  8* ') from dual

OPEN  CUR_INVOICE FOR 
       SELECT 
       PH.REQ_NUMBER,
       PH.NAME,
       PH.VENDOR_NAME,
       PH.LAST_NAME,
       PH.BANK_NAME,
       PH.DEP,
       PH.BANK_ACCOUNT_NUM,
       DECODE(PH.AMOUNT_IN,0,'0',TO_CHAR(PH.AMOUNT_IN,'FM999,999,999,999,999.00')) AMOUNT_IN,       
       TO_CHAR(PH.CREATION_DATE,'YYYY-MM-DD') CRATE_DATE,    
       DECODE(PH.REQ_AMOUNT,0,'0',TO_CHAR(PH.REQ_AMOUNT,'FM999,999,999,999,999.00')) REQ_AMOUNT, 
       PH.MEANING1, 
       PH.MEANING2,
       PH.MEANING3,
       PH.DESCRIPTION,     
       PH.NAME "NAME1",
       TO_CHAR(SYSDATE,'YYYY-MM-DD') "PRINT_DATE",
       PH.INVOICE_CURRENCY_CODE,
       Cux_Mea_Reports.AMOUNT_CONVERT(PH.AMOUNT_IN) AMOUNT_IN_F,
       Cux_Mea_Reports.AMOUNT_CONVERT(PH.REQ_AMOUNT) REQ_AMOUNT_F       
  FROM CUX_PAYREQ_HEADERS_V PH
 WHERE PH.DOC_TYPE_ID IN (0, 1)
     AND PH.PAYREQ_HEAD_ID = P_ITEM_ID ;

  --定义XML CLOB文件
  L_QRYCTX := DBMS_XMLGEN.newContext (CUR_INVOICE);

  --设置XML文件标签
DBMS_XMLGEN.setRowSetTag (L_QRYCTX, 'CUX_INVOICE');
DBMS_XMLGEN.setRowTag (L_QRYCTX, 'LIST_G_BANK_NAME');

  --返回CLOB数据
  L_RESULT := DBMS_XMLGEN.getXML (L_QRYCTX);

  --判断是否有行
  L_NO_ROWS:= DBMS_XMLGEN.getNumRowsProcessed(L_QRYCTX); 
  IF  L_NO_ROWS=0 THEN GOTO NEXT_LABEL;END IF;
 
  L_XMLSTR:=DBMS_XMLGEN.CONVERT(L_RESULT, 1);
  OUT_PUT(L_XMLSTR);
  
 <<NEXT_LABEL>>
  DBMS_XMLGEN.closeContext (L_QRYCTX);   */