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);
