Hello All,
I have created a view vw_trxn_history_la_test with the below mentioned query.
Oracle Version:
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
CREATE OR REPLACE FORCE VIEW VW_TRXN_HISTORY_LA_TEST
AS
SELECT eisctrxnno,
FOLIO_NO,
trxnno,
SCHEME_NAME,
TRANSACTION_TYPE,
INVESTOR_NAME,
PAN_STATUS,
SUM(UNITS) UNITS,
NAV ,
SUM(AMOUNT) AMOUNT,
BRANCH_NAME,
TRXNDATE,
trxntypcod
FROM
(SELECT eisctrxnno,
trxnno,
A.FOLIONO FOLIO_NO,
C.SCHEME_NAME SCHEME_NAME,
A.TRXNTYPDES TRANSACTION_TYPE,
E.INVFNAME AS INVESTOR_NAME,
CASE
WHEN E.IVALIDPAN = 'Y'
THEN 'Yes'
ELSE 'No'
END AS PAN_STATUS,
A.UNITS,
A.PRICE AS NAV ,
A.plot_AMOUNT amount,
D.BRCHNAME BRANCH_NAME,
a.trxntypcod,
A.TRXNDATE AS TRXNDATE
FROM ADVISOR_PORTAL.CAMS_PROCESSED_TRAN_SPLIT A,
MAST_FUNDINFO C,
ADVISOR_PORTAL.CAMS_BRANCH D,
ADVISOR_PORTAL.CAMS_FOLIO E
WHERE TRIM(A.SCHCODE) =TRIM(C.SCHEME_CODE)
AND A.FOLIONO =E.FOLIONO
AND TRIM(A.BRCHCODE) =TRIM(D.BRCHCODE)
AND A.SCHCODE IN ('48N','48NZ','43N','43NZ')
)
-- WHERE FOLIO_NO = '5226610'
GROUP BY eisctrxnno,
FOLIO_NO,
trxnno,
SCHEME_NAME,
TRANSACTION_TYPE,
INVESTOR_NAME,
PAN_STATUS,
NAV ,
BRANCH_NAME,
TRXNDATE,
TRXNTYPCOD
ORDER BY TRXNDATE DESC;
Query
select * from vw_trxn_history_la_test where folio_no = '5226610'
Query works fine but sometimes we get an error message while executing the query.
If I execute the same query from another session, it works fine. the issue is intermittent not always.
Error Message :
ORA-02051: another session or branch in same transaction failed or finalized
ORA-02063: preceding line from "DBLINK"
02051. 00000- "another session in same transaction failed"
*Cause: a session at the same site with the same global transaction ID failed.
*Action: none necessary, transaction automatically recovered.
Error at Line 18 Column:14
I checked online but could not find any action for me to resolve this problem. please suggest.