Cash Management BANK Stmt Exception Report in XML Publisher

To fetch Details:

SELECT STATEMENT_NUMBER,

       BANK_ACCOUNT_NUM,

       STATEMENT_DATE,

       BANK_NAME,

       BANK_BRANCH_NAME,

       RECORD_STATUS_FLAG,

       CURRENCY_CODE

 FROM  CE_STATEMENT_HEADERS_INT 

 WHERE BANK_ACCOUNT_NUM=:P_ACCOUNT_NUM

 AND STATEMENT_NUMBER=:P_STAT_NUM

AND STATEMENT_DATE BETWEEN NVL(:P_FROM_STMT_DATE,STATEMENT_DATE)  

     AND NVL( :P_TO_STMT_DATE,STATEMENT_DATE)

SELECT BANK_ACCOUNT_NUM,

 STATEMENT_NUMBER,

 LINE_NUMBER,

 TRX_DATE,

 TRX_CODE,

 TRX_TEXT,

 AMOUNT,

 CURRENCY_CODE,

 BANK_TRX_NUMBER,

 CUSTOMER_TEXT,

  XX_GET_ERROR_MSG (CSLI.BANK_ACCOUNT_NUM,CSLI.LINE_NUMBER,CSLI.STATEMENT_NUMBER) 

MEG,

   XX_GET_MESSSAGE(XX_GET_ERROR_MSG 

(CSLI.BANK_ACCOUNT_NUM,CSLI.LINE_NUMBER,CSLI.STATEMENT_NUMBER)) 

ERROR_MESSAGE_DETAILS,

 (SELECT bb.BANK_NAME

       FROM ce_bank_branches_v bb,

           ce_bank_accounts ba

      WHERE ba.BANK_ACCOUNT_NUM=:P_ACCOUNT_NUM  

      AND  ba.bank_branch_id = bb.branch_party_id ) bank_name,

     (SELECT bb.BANK_BRANCH_NAME

      FROM ce_bank_branches_v bb,

           ce_bank_accounts ba

      WHERE ba.BANK_ACCOUNT_NUM=:P_ACCOUNT_NUM  

      AND  ba.bank_branch_id = bb.branch_party_id ) bank_branch_name 

  FROM CE_STATEMENT_LINES_INTERFACE CSLI

 WHERE CSLI.STATEMENT_NUMBER=:P_STAT_NUM

  AND  CSLI.BANK_ACCOUNT_NUM =:P_ACCOUNT_NUM

Function :

create or replace function xx_get_messsage(l_message_name in varchar2)

return varchar2

is

l_message    FND_NEW_MESSAGES.message_text%TYPE;

begin 

   FND_MESSAGE.set_encoded(l_message_name );

   l_message:= FND_MESSAGE.get;

   IF l_message IS NULL THEN

     FND_MESSAGE.set_name('CE', l_message_name );

     l_message:= FND_MESSAGE.get;

   END IF;

   return  (l_message);

  dbms_output.put_line(l_message);

end;

CREATE OR REPLACE FUNCTION xx_get_error_msg (l_bank_acct_no in varchar2,

                            l_line_no in number,

                            l_stmt_no IN VARCHAR2)

   RETURN VARCHAR2

IS

   l_message_name   VARCHAR2 (100);

BEGIN

   BEGIN

      SELECT  cli.message_name

        INTO  l_message_name

        FROM ce_line_interface_errors cli

       WHERE cli.statement_number =l_stmt_no

         AND cli.line_number=l_line_no

         AND  cli.bank_account_num =l_bank_acct_no;

     EXCEPTION

      WHEN OTHERS

      THEN

         l_message_name := NULL;

       --  RETURN (l_message_name);

   END;

   RETURN (l_message_name);