Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

ORA error (ORA-08103: object no longer exists)

Roshan BisnathMar 31 2024

Oracle database 19c

Hello Team,

we are getting ORA error (ORA-08103: object no longer exists) while executing stored proc. But the job rerun from beginning and completed successfully.

The proc is querying a view remotely using db link. Kindly advise on this.

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "REGISTRY"."V_IB_TRANSACTION_QUERY" ("REP_ID", "TRAN_ID", "SEC_ID", "IDPORTFOLIO", "CIR", "DATETRADE", "DATESETTLEMENT", "IDTYPETRADE", "QUANTITY", "TRADEPRICE", "CCYSECURITY", "COMMISSION", "AMTSETTLEMENT", "CCYAMTSETTLEMENT", "BROKER", "REGISTERED_OWNER", "IDREGISTERED_OWNER", "PLANMARKER", "CERT_PLAN_REF_NO", "SETTLED", "SUB_ID", "PLAN_TYPE") AS
 SELECT NULL REP_ID, '100' || T.TRAN_ID TRAN_ID, T.TRAN_SECURITY_ID SEC_ID, CASE WHEN FS.FINAL_SUB_ID IS NULL THEN T.TRAN_SUB_ACC_ID ELSE FS.FINAL_SUB_ID END IDPORTFOLIO, NULL AS CIR,
--CASE WHEN IR.DATETRADE IS NULL THEN TRUNC(T.TRAN_DATE) WHEN IR.DATETRADE > T.TRAN_DATE THEN TRUNC(T.TRAN_DATE) ELSE TRUNC(IR.DATETRADE) END DATETRADE,
T.TRAN_DATE AS DATETRADE,
--NVL2(IR.DATETRADE, TRUNC(T.TRAN_DATE), NULL) DATESETTLEMENT,
T.TRAN_DATE AS DATESETTLEMENT,
TRNT_TRANSACTION IDTYPETRADE, T.TRAN_DIRECTION * T.TRAN_QTY QUANTITY, CASE WHEN NVL(T.TRAN_QTY,0) = 0 THEN NULL ELSE TRUNC(IR.AMTSETTLEMENT/T.TRAN_QTY,2) END AS TRADEPRICE, NVL(CYD.CUR_CODE, CYDEF.CUR_CODE) CCYSECURITY, 0 COMMISSION,--IR.COMMISSION
IR.AMTSETTLEMENT, NVL(CYS.CUR_CODE, CYDEF.CUR_CODE) CCYAMTSETTLEMENT,  cast(NULL AS VARCHAR2(10)) BROKER, --IR.AGENT_NAME BROKER
NVL2(ENT.IDREGISTERED_OWNER, 'Y', 'N') REGISTERED_OWNER,
ENT.IDREGISTERED_OWNER, CASE WHEN UP.CERT_PLAN_TYPE IS NULL THEN 'N' WHEN UP.CERT_PLAN_TYPE = '1' THEN 'N' ELSE 'Y' END PLANMARKER,
UP.CERT_PLAN_REF_NO, 'Y' SETTLED, CASE WHEN FS.FINAL_SUB_ID IS NULL THEN T.TRAN_SUB_ACC_ID ELSE FS.FINAL_SUB_ID END SUB_ID, CASE WHEN UP.CERT_PLAN_TYPE = '1' THEN '' ELSE plan.PRM_DISPLAY_NAME END AS PLAN_TYPE
FROM SMS_TRANSACTION T
JOIN SMS_UNITS U ON U.SEC_ID = T.TRAN_SECURITY_ID
JOIN (SELECT SEC_ID, CUR_CODE FROM SMS_SECURITIES SEC
JOIN SMS_CURRENCIES CYDEF ON CYDEF.CUR_ID = SEC.CUR_ID) CYDEF ON CYDEF.SEC_ID = T.TRAN_SECURITY_ID
LEFT JOIN -- ISSUE
(
SELECT UNIT_TX_ID ID, UNIT_MODIFIED_ON DATETRADE, UNIT_NAV_PRICE TRADEPRICE, CUR_ID_DENOM,
UNIT_AMT_INVESTED AMTSETTLEMENT, CUR_ID_SUBS, UNIT_AGENT_ID AGENT_ID, UNIT_AGENT_COMMISSION COMMISSION,
UNIT_CERT_ID--, AGENT_NAME
FROM SMS_UNIT_TX U
--      LEFT JOIN
--      (
--              SELECT IND_ID ENT_ID, IND_FIRST_NAME || ' ' || IND_LAST_NAME AGENT_NAME from SMS_INDIVIDUALS WHERE IND_AGENT = '1'
--              UNION
--              SELECT NIND_ID ENT_ID, NIND_NAME AGENT_NAME from SMS_NON_INDIVIDUALS WHERE NIND_AGENT = '1'
--      ) AG ON AG.ENT_ID = U.UNIT_AGENT_ID
) IR ON IR.ID = T.TRAN_UNIT_TX_ID AND IR.UNIT_CERT_ID = T.TRAN_CERT_ID
LEFT JOIN SMS_CERTIFICATE UP ON UP.CERT_ID = T.TRAN_CERT_ID
LEFT JOIN SMS_CURRENCIES CYD ON CYD.CUR_ID = IR.CUR_ID_DENOM
LEFT JOIN SMS_CURRENCIES CYS ON CYS.CUR_ID = IR.CUR_ID_SUBS
JOIN
(
SELECT AC.SUB_ID,AC.REG_OWNER_REF, RO.IDREGISTERED_OWNER FROM
(
SELECT S.SUB_ID, REG_OWNER_REF FROM SMS_SHAREHOLDER_SUBACCOUNTS S
JOIN SMS_SHAREHOLDER_MASTERACCOUNTS M ON M.MAS_ACCOUNT_CODE = S.MAS_ACCOUNT_CODE
JOIN V_ENTITY_CIR E ON E.ID = M.IND_NIND_ID
UNION
SELECT S.SUB_ID, REG_OWNER_REF FROM SMS_SHAREHOLDER_SUBACCOUNTS S
JOIN SMS_JOINTACC_HOLDERS J ON J.SUB_ID = S.SUB_ID
JOIN V_ENTITY_CIR E ON E.ID = J.IND_NIND_ID
) AC
LEFT JOIN
(
SELECT SUB_ID, E.IND_NIND_ID IDREGISTERED_OWNER
FROM SMS_JOINTACC_HOLDERS jh
JOIN V_ENTITY_CIR E ON E.ID = jh.IND_NIND_ID
WHERE REG_OWNER = '1'
) RO ON RO.SUB_ID = AC.SUB_ID
) ENT ON ENT.SUB_ID = T.TRAN_SUB_ACC_ID
JOIN SMS_TRANSACTION_TYPES TT ON TT.TRNT_TYPE = T.TRAN_TYPE
LEFT JOIN SMS_PARAMETERS plan ON UP.CERT_PLAN_TYPE = plan.PRM_VALUE AND plan.PRM_DOMAIN_NAME = '77'
LEFT JOIN
(
SELECT DISTINCT SUB_ID,FINAL_SUB_ID
FROM SMS_CONS_SUB
) FS ON FS.SUB_ID = T.TRAN_SUB_ACC_ID
JOIN SMS_SECURITIES SECS ON SECS.SEC_ID = T.TRAN_SECURITY_ID
JOIN SMS_SHAREHOLDER_SUBACCOUNTS SUB ON SUB.SUB_ID = T.TRAN_SUB_ACC_ID
WHERE TRAN_TYPE NOT IN ('U1C', 'U2', 'R27', 'U3B', 'RC02') AND NOT(TRAN_TYPE='R2' AND TRAN_DATE='07-JUN-2011')
AND (SUB.SUB_ACCOUNT_TYPE = 1 OR NVL(SECS.SEC_LISTED, 2) <> 1)
AND NVL(SECS.SEC_IB,'2') = '1'
AND (TRAN_QTY*TRAN_DIRECTION) <> 0
-- AND U.UNI_MANAGER_ID IN (75253,115100,67911,100156)
AND (ENT.IDREGISTERED_OWNER IS NULL OR ENT.IDREGISTERED_OWNER <> 107550)
--UNION ALL
---- RETRIEVE ALL UNAUTHORISED TRANSACTIONS
--SELECT NULL REP_ID, '200' || UT.UNIT_TX_ID TRAN_ID, UT.SEC_ID, UT.UNIT_ENTITY_ID IDPORTFOLIO, ENT.CIR,
--TRUNC(UT.UNIT_DEAL_DATE) DATETRADE, TRUNC(UT.UNIT_DEAL_DATE) DATESETTLEMENT, --UT.UNIT_MODIFIED_ON DATESETTLEMENT,
--TP.IDTYPETRADE, UT.UNIT_QUANTITY QUANTITY, CASE WHEN NVL(UT.UNIT_QUANTITY,0) = 0 THEN NULL ELSE TRUNC(UNIT_AMT_INVESTED/UT.UNIT_QUANTITY,2) END AS TRADEPRICE,
--CYD.CUR_CODE CCYSECURITY, 0 COMMISSION,--UT.UNIT_AGENT_COMMISSION COMMISSION
--UNIT_AMT_INVESTED AMTSETTLEMENT, CYS.CUR_CODE CCYAMTSETTLEMENT,  cast(NULL AS VARCHAR2(10)) BROKER, -- AG.AGENT_NAME BROKER
--NVL2(ENT.IDREGISTERED_OWNER, 'Y', 'N') REGISTERED_OWNER,
--ENT.IDREGISTERED_OWNER, CASE WHEN UT.UNIT_PLAN_REF IS NULL THEN 'N' WHEN UT.UNIT_PLAN_REF = '1' THEN 'N' ELSE 'Y' END PLANMARKER, UT.UNIT_PLAN_REF_NO,
--'N' SETTLED, UT.UNIT_DESTINATION_SUB_ID SUB_ID, CASE WHEN UT.UNIT_PLAN_REF = '1' THEN '' ELSE plan.PRM_DISPLAY_NAME END AS PLAN_TYPE
--FROM SMS_UNIT_TX UT
--JOIN
--(
--      SELECT AC.SUB_ID, AC.IND_NIND_ID, AC.CIR, AC.REG_OWNER_REF, RO.IDREGISTERED_OWNER FROM
--      (
--              SELECT S.SUB_ID, E.IND_NIND_ID, REG_OWNER_REF, E.CIR FROM SMS_SHAREHOLDER_SUBACCOUNTS S
--              JOIN SMS_SHAREHOLDER_MASTERACCOUNTS M ON M.MAS_ACCOUNT_CODE = S.MAS_ACCOUNT_CODE
--              JOIN V_ENTITY_CIR E ON E.ID = M.IND_NIND_ID
--              UNION ALL
--              SELECT S.SUB_ID, E.IND_NIND_ID, REG_OWNER_REF, E.CIR FROM SMS_SHAREHOLDER_SUBACCOUNTS S
--              JOIN SMS_JOINTACC_HOLDERS J ON J.SUB_ID = S.SUB_ID
--              JOIN V_ENTITY_CIR E ON E.ID = J.IND_NIND_ID
--      ) AC
--      LEFT JOIN
--      (
--              SELECT SUB_ID, E.IND_NIND_ID IDREGISTERED_OWNER
--              FROM SMS_JOINTACC_HOLDERS jh
--              JOIN V_ENTITY_CIR E ON E.ID = jh.IND_NIND_ID
--              WHERE REG_OWNER = '1'
--      ) RO ON RO.SUB_ID = AC.SUB_ID
--) ENT ON ENT.SUB_ID = UT.UNIT_DESTINATION_SUB_ID
--JOIN
--(
--       SELECT PRM_VALUE, UPPER(PRM_DISPLAY_NAME) IDTYPETRADE FROM SMS_PARAMETERS WHERE PRM_DOMAIN_NAME = '41'
--) TP ON TP.PRM_VALUE = UT.UNIT_TX_TYPE
--JOIN SMS_UNITS U ON U.SEC_ID = UT.SEC_ID
--LEFT JOIN SMS_CURRENCIES CYD ON CYD.CUR_ID = UT.CUR_ID_DENOM
--LEFT JOIN SMS_CURRENCIES CYS ON CYS.CUR_ID = UT.CUR_ID_SUBS
----LEFT JOIN
----(
----    SELECT IND_ID ENT_ID, IND_FIRST_NAME || ' ' || IND_LAST_NAME AGENT_NAME from SMS_INDIVIDUALS WHERE IND_AGENT = '1'
----    UNION
----    SELECT NIND_ID ENT_ID, NIND_NAME AGENT_NAME from SMS_NON_INDIVIDUALS WHERE NIND_AGENT = '1'
----) AG ON AG.ENT_ID = UT.UNIT_AGENT_ID
--LEFT JOIN SMS_PARAMETERS plan ON UT.UNIT_PLAN_REF = plan.PRM_VALUE AND plan.PRM_DOMAIN_NAME = '77'
--WHERE UNIT_RECORD_STATUS = '2' AND U.UNI_MANAGER_ID = 75253 AND UNIT_AMT_INVESTED > 0
UNION ALL
-- RETRIEVE ALL AUTHORISED RSP TRANSACTIONS
SELECT NULL REP_ID, '100' || T.TRAN_ID TRAN_ID, UT.SEC_ID, CASE WHEN FS.FINAL_SUB_ID IS NULL THEN T.TRAN_SUB_ACC_ID ELSE FS.FINAL_SUB_ID END IDPORTFOLIO, NULL AS CIR,
--CASE WHEN UT.UNIT_MODIFIED_ON > T.TRAN_DATE THEN TRUNC(T.TRAN_DATE) ELSE TRUNC(UT.UNIT_MODIFIED_ON) END DATETRADE,
UT.UNIT_DEAL_DATE AS DATETRADE,
TRUNC(UT.UNIT_DEAL_DATE), --UT.UNIT_DEAL_DATE DATESETTLEMENT,
TRNT_TRANSACTION IDTYPETRADE, UT.UNIT_QUANTITY QUANTITY, CASE WHEN NVL(UT.UNIT_QUANTITY,0) = 0 THEN NULL ELSE TRUNC(UT.UNIT_AMT_INVESTED/UT.UNIT_QUANTITY,2) END AS TRADEPRICE, CYD.CUR_CODE CCYSECURITY, 0 COMMISSION, --UT.UNIT_AGENT_COMMISSION COMMISSION
UT.UNIT_AMT_INVESTED AMTSETTLEMENT, CYS.CUR_CODE CCYAMTSETTLEMENT,  cast(NULL AS VARCHAR2(10)) BROKER, --AGENT_NAME BROKER
NVL2(ENT.IDREGISTERED_OWNER, 'Y', 'N') REGISTERED_OWNER,
ENT.IDREGISTERED_OWNER, CASE WHEN UP.CERT_PLAN_TYPE IS NULL THEN 'N' WHEN UP.CERT_PLAN_TYPE = '1' THEN 'N' ELSE 'Y' END PLANMARKER, UP.CERT_PLAN_REF_NO,
'Y' SETTLED, CASE WHEN FS.FINAL_SUB_ID IS NULL THEN T.TRAN_SUB_ACC_ID ELSE FS.FINAL_SUB_ID END SUB_ID, CASE WHEN UP.CERT_PLAN_TYPE = '1' THEN '' ELSE plan.PRM_DISPLAY_NAME END AS PLAN_TYPE
FROM SMS_UNIT_TX UT
JOIN SMS_TRANSACTION T ON UT.UNIT_TX_ID = T.TRAN_UNIT_TX_ID AND UT.UNIT_CERT_ID = T.TRAN_CERT_ID
JOIN
(
SELECT AC.SUB_ID,AC.REG_OWNER_REF, RO.IDREGISTERED_OWNER FROM
(
SELECT S.SUB_ID, REG_OWNER_REF FROM SMS_SHAREHOLDER_SUBACCOUNTS S
JOIN SMS_SHAREHOLDER_MASTERACCOUNTS M ON M.MAS_ACCOUNT_CODE = S.MAS_ACCOUNT_CODE
JOIN V_ENTITY_CIR E ON E.ID = M.IND_NIND_ID
UNION
SELECT S.SUB_ID, REG_OWNER_REF FROM SMS_SHAREHOLDER_SUBACCOUNTS S
JOIN SMS_JOINTACC_HOLDERS J ON J.SUB_ID = S.SUB_ID
JOIN V_ENTITY_CIR E ON E.ID = J.IND_NIND_ID
) AC
LEFT JOIN
(
SELECT SUB_ID, E.IND_NIND_ID IDREGISTERED_OWNER
FROM SMS_JOINTACC_HOLDERS jh
JOIN V_ENTITY_CIR E ON E.ID = jh.IND_NIND_ID
WHERE REG_OWNER = '1'
) RO ON RO.SUB_ID = AC.SUB_ID
) ENT ON ENT.SUB_ID = UT.UNIT_DESTINATION_SUB_ID
JOIN SMS_TRANSACTION_TYPES TT ON TT.TRNT_TYPE = T.TRAN_TYPE
JOIN SMS_UNITS U ON U.SEC_ID = UT.SEC_ID
LEFT JOIN SMS_CURRENCIES CYD ON CYD.CUR_ID = UT.CUR_ID_DENOM
LEFT JOIN SMS_CURRENCIES CYS ON CYS.CUR_ID = UT.CUR_ID_SUBS
LEFT JOIN SMS_CERTIFICATE UP ON UT.UNIT_CERT_ID = UP.CERT_ID
--LEFT JOIN
--(
--      SELECT IND_ID ENT_ID, IND_FIRST_NAME || ' ' || IND_LAST_NAME AGENT_NAME from SMS_INDIVIDUALS WHERE IND_AGENT = '1'
--      UNION
--      SELECT NIND_ID ENT_ID, NIND_NAME AGENT_NAME from SMS_NON_INDIVIDUALS WHERE NIND_AGENT = '1'
--) AG ON AG.ENT_ID = UT.UNIT_AGENT_ID
LEFT JOIN SMS_PARAMETERS plan ON UP.CERT_PLAN_TYPE = plan.PRM_VALUE AND plan.PRM_DOMAIN_NAME = '77'
LEFT JOIN
(
SELECT DISTINCT SUB_ID,FINAL_SUB_ID
FROM SMS_CONS_SUB
) FS ON FS.SUB_ID = T.TRAN_SUB_ACC_ID
JOIN SMS_SECURITIES SECS ON SECS.SEC_ID = UT.SEC_ID AND NVL(SECS.SEC_IB,'2') = '1'
JOIN SMS_SHAREHOLDER_SUBACCOUNTS SUB ON SUB.SUB_ID = T.TRAN_SUB_ACC_ID
WHERE UT.UNIT_RECORD_STATUS = '1' AND UT.UNIT_TX_TYPE = '3'
AND (SUB.SUB_ACCOUNT_TYPE = 1 OR NVL(SECS.SEC_LISTED, 2) <> 1)
AND NVL(SECS.SEC_IB,'2') = '1'
-- AND U.UNI_MANAGER_ID IN (75253,115100,67911,100156)
AND (ENT.IDREGISTERED_OWNER IS NULL OR ENT.IDREGISTERED_OWNER <> 107550)
UNION ALL
--RSP ADJ
SELECT NULL REP_ID, '200' || T.TRAN_ID TRAN_ID, T.TRAN_SECURITY_ID SEC_ID, CASE WHEN FS.FINAL_SUB_ID IS NULL THEN T.TRAN_SUB_ACC_ID ELSE FS.FINAL_SUB_ID END IDPORTFOLIO, NULL AS CIR,
--CASE WHEN IR.DATETRADE IS NULL THEN TRUNC(T.TRAN_DATE) WHEN IR.DATETRADE > T.TRAN_DATE THEN TRUNC(T.TRAN_DATE) ELSE TRUNC(IR.DATETRADE) END DATETRADE,
T.TRAN_DATE AS DATETRADE,
--NVL2(IR.DATETRADE, TRUNC(T.TRAN_DATE), NULL) DATESETTLEMENT,
T.TRAN_DATE AS DATESETTLEMENT,
RSP_TYPE IDTYPETRADE, T.TRAN_DIRECTION * T.QTY QUANTITY,
CASE WHEN NVL(T.TRAN_QTY,0) = 0 THEN NULL ELSE TRUNC(IR.AMTSETTLEMENT/T.TRAN_QTY,2) END AS TRADEPRICE, NVL(CYD.CUR_CODE, CYDEF.CUR_CODE) CCYSECURITY, 0 COMMISSION,--IR.COMMISSION
CASE WHEN RSP_TYPE = 'RSP ADJ' THEN 0 ELSE IR.AMTSETTLEMENT END AMTSETTLEMENT, NVL(CYS.CUR_CODE, CYDEF.CUR_CODE) CCYAMTSETTLEMENT,  cast(NULL AS VARCHAR2(10)) BROKER, --IR.AGENT_NAME BROKER
NVL2(ENT.IDREGISTERED_OWNER, 'Y', 'N') REGISTERED_OWNER,
ENT.IDREGISTERED_OWNER, CASE WHEN UP.CERT_PLAN_TYPE IS NULL THEN 'N' WHEN UP.CERT_PLAN_TYPE = '1' THEN 'N' ELSE 'Y' END PLANMARKER,
UP.CERT_PLAN_REF_NO, 'Y' SETTLED, CASE WHEN FS.FINAL_SUB_ID IS NULL THEN T.TRAN_SUB_ACC_ID ELSE FS.FINAL_SUB_ID END SUB_ID, CASE WHEN UP.CERT_PLAN_TYPE = '1' THEN '' ELSE plan.PRM_DISPLAY_NAME END AS PLAN_TYPE
FROM
(
SELECT X.*,X.TRAN_QTY AS QTY
FROM
(
SELECT A.TRAN_ID,A.TRAN_DATE,A.TRAN_TYPE,A.TRAN_QTY,A.TRAN_POSITION,A.TRAN_SUB_ACC_ID,
A.TRAN_SECURITY_ID,A.TRAN_CERT_ID,A.TRAN_UNIT_TX_ID,A.TRAN_DIRECTION,
A.TRAN_IB_TRANSACT,A.CERT_PLAN_TYPE,A.CERT_PLAN_REF_NO,
'RSP ADJ' AS RSP_TYPE
FROM
(
SELECT t.*,c.CERT_PLAN_TYPE,c.CERT_PLAN_REF_NO
FROM SMS_TRANSACTION t
JOIN SMS_CERTIFICATE c ON t.TRAN_CERT_ID = c.CERT_ID
WHERE TRAN_TYPE = 'U1C' AND TRAN_DIRECTION = -1 AND TRAN_QTY > 0
) A
JOIN
(
SELECT t.*,c.CERT_PLAN_TYPE,c.CERT_PLAN_REF_NO
FROM SMS_TRANSACTION t
JOIN SMS_CERTIFICATE c ON t.TRAN_CERT_ID = c.CERT_ID
WHERE TRAN_TYPE = 'U1C' AND TRAN_DIRECTION = 1
) B ON A.TRAN_UNIT_TX_ID = B.TRAN_UNIT_TX_ID
AND A.CERT_PLAN_TYPE || '-' || A.CERT_PLAN_REF_NO <> B.CERT_PLAN_TYPE || '-' || B.CERT_PLAN_REF_NO
UNION ALL
SELECT A.TRAN_ID,A.TRAN_DATE,A.TRAN_TYPE,A.TRAN_QTY,A.TRAN_POSITION,A.TRAN_SUB_ACC_ID,
A.TRAN_SECURITY_ID,B.TRAN_CERT_ID,A.TRAN_UNIT_TX_ID,B.TRAN_DIRECTION,
A.TRAN_IB_TRANSACT,B.CERT_PLAN_TYPE,B.CERT_PLAN_REF_NO,
'RSP ADJ' AS RSP_TYPE
FROM
(
SELECT t.*,c.CERT_PLAN_TYPE,c.CERT_PLAN_REF_NO
FROM SMS_TRANSACTION t
JOIN SMS_CERTIFICATE c ON t.TRAN_CERT_ID = c.CERT_ID
WHERE TRAN_TYPE = 'U1C' AND TRAN_DIRECTION = -1 AND TRAN_QTY > 0
) A
JOIN
(
SELECT t.*,c.CERT_PLAN_TYPE,c.CERT_PLAN_REF_NO
FROM SMS_TRANSACTION t
JOIN SMS_CERTIFICATE c ON t.TRAN_CERT_ID = c.CERT_ID
WHERE TRAN_TYPE = 'U1C' AND TRAN_DIRECTION = 1
) B ON A.TRAN_UNIT_TX_ID = B.TRAN_UNIT_TX_ID
AND A.CERT_PLAN_TYPE || '-' || A.CERT_PLAN_REF_NO <> B.CERT_PLAN_TYPE || '-' || B.CERT_PLAN_REF_NO
) X
) T
JOIN SMS_UNITS U ON U.SEC_ID = T.TRAN_SECURITY_ID
JOIN (SELECT SEC_ID, CUR_CODE FROM SMS_SECURITIES SEC
JOIN SMS_CURRENCIES CYDEF ON CYDEF.CUR_ID = SEC.CUR_ID) CYDEF ON CYDEF.SEC_ID = T.TRAN_SECURITY_ID
LEFT JOIN SMS_CERTIFICATE UP ON UP.CERT_ID = T.TRAN_CERT_ID
LEFT JOIN -- ISSUE
(
SELECT UNIT_TX_ID ID, UNIT_MODIFIED_ON DATETRADE, UNIT_NAV_PRICE TRADEPRICE, CUR_ID_DENOM,
UNIT_AMT_INVESTED AMTSETTLEMENT, CUR_ID_SUBS, UNIT_AGENT_ID AGENT_ID, UNIT_AGENT_COMMISSION COMMISSION,
UNIT_CERT_ID--, AGENT_NAME
FROM SMS_UNIT_TX U
) IR ON IR.ID = T.TRAN_UNIT_TX_ID
LEFT JOIN SMS_CURRENCIES CYD ON CYD.CUR_ID = IR.CUR_ID_DENOM
LEFT JOIN SMS_CURRENCIES CYS ON CYS.CUR_ID = IR.CUR_ID_SUBS
JOIN
(
SELECT AC.SUB_ID,AC.REG_OWNER_REF, RO.IDREGISTERED_OWNER FROM
(
SELECT S.SUB_ID, REG_OWNER_REF FROM SMS_SHAREHOLDER_SUBACCOUNTS S
JOIN SMS_SHAREHOLDER_MASTERACCOUNTS M ON M.MAS_ACCOUNT_CODE = S.MAS_ACCOUNT_CODE
JOIN V_ENTITY_CIR E ON E.ID = M.IND_NIND_ID
UNION
SELECT S.SUB_ID, REG_OWNER_REF FROM SMS_SHAREHOLDER_SUBACCOUNTS S
JOIN SMS_JOINTACC_HOLDERS J ON J.SUB_ID = S.SUB_ID
JOIN V_ENTITY_CIR E ON E.ID = J.IND_NIND_ID
) AC
LEFT JOIN
(
SELECT SUB_ID, E.IND_NIND_ID IDREGISTERED_OWNER
FROM SMS_JOINTACC_HOLDERS jh
JOIN V_ENTITY_CIR E ON E.ID = jh.IND_NIND_ID
WHERE REG_OWNER = '1'
) RO ON RO.SUB_ID = AC.SUB_ID
) ENT ON ENT.SUB_ID = T.TRAN_SUB_ACC_ID
JOIN SMS_TRANSACTION_TYPES TT ON TT.TRNT_TYPE = T.TRAN_TYPE
LEFT JOIN SMS_PARAMETERS plan ON UP.CERT_PLAN_TYPE = plan.PRM_VALUE AND plan.PRM_DOMAIN_NAME = '77'
LEFT JOIN
(
SELECT DISTINCT SUB_ID,FINAL_SUB_ID
FROM SMS_CONS_SUB
) FS ON FS.SUB_ID = T.TRAN_SUB_ACC_ID
JOIN SMS_SECURITIES SECS ON SECS.SEC_ID = T.TRAN_SECURITY_ID AND NVL(SECS.SEC_IB,'2') = '1'
JOIN SMS_SHAREHOLDER_SUBACCOUNTS SUB ON SUB.SUB_ID = T.TRAN_SUB_ACC_ID
--WHERE U.UNI_MANAGER_ID IN (75253,115100,67911,100156)
WHERE (ENT.IDREGISTERED_OWNER IS NULL OR ENT.IDREGISTERED_OWNER <> 107550) AND NVL(SECS.SEC_IB,'2') = '1'
AND (SUB.SUB_ACCOUNT_TYPE = 1 OR NVL(SECS.SEC_LISTED, 2) <> 1)
UNION ALL
-- RETRIEVE ALL REPURCHASE TRANSACTIONS
SELECT UR.REP_ID, ('300' || RD.REP_DET_ID) TRAN_ID, UR.SEC_ID, CASE WHEN FS.FINAL_SUB_ID IS NULL THEN T.SUB_ID ELSE FS.FINAL_SUB_ID END IDPORTFOLIO, NULL AS CIR,
--CASE WHEN UR.REP_MODIFIED_ON > UR.REP_DATE THEN TRUNC(UR.REP_DATE) ELSE TRUNC(UR.REP_MODIFIED_ON) END DATETRADE,
UR.REP_DATE AS DATETRADE,
--CASE WHEN REP_RECORD_STATUS = '1' THEN TRUNC(UR.REP_DATE) ELSE NULL END DATESETTLEMENT,
UR.REP_DATE AS DATESETTLEMENT,
CASE WHEN utx.REP_ID IS NULL THEN 'REPURCHASE' ELSE 'SWITCH OUT' END IDTYPETRADE, (RD.UNIT_QTY * -1) QUANTITY, CASE WHEN NVL(RD.UNIT_QTY,0) = 0 THEN NULL ELSE TRUNC(UNIT_AMT/RD.UNIT_QTY,2) END AS TRADEPRICE, CYD.CUR_CODE CCYSECURITY, 0 COMMISSION, --REP_AGENT_COMMISSION COMMISSION
UNIT_AMT AMTSETTLEMENT, CYS.CUR_CODE CCYAMTSETTLEMENT, cast(NULL AS VARCHAR2(10)) BROKER, --AG.AGENT_NAME BROKER,
NVL2(ENT.IDREGISTERED_OWNER, 'Y', 'N') REGISTERED_OWNER,
ENT.IDREGISTERED_OWNER,  CASE WHEN RD.UNIT_PLAN_REF IS NULL THEN 'N' WHEN RD.UNIT_PLAN_REF = '1' THEN 'N' ELSE 'Y' END PLANMARKER, RD.UNIT_PLAN_REF_NO,
CASE WHEN REP_RECORD_STATUS = '1' THEN 'Y' ELSE 'N' END SETTLED, CASE WHEN FS.FINAL_SUB_ID IS NULL THEN T.SUB_ID ELSE FS.FINAL_SUB_ID END SUB_ID, CASE WHEN RD.UNIT_PLAN_REF = '1' THEN '' ELSE plan.PRM_DISPLAY_NAME END AS PLAN_TYPE
FROM SMS_UNIT_REPURCHASE UR
JOIN
(
SELECT MAX(REP_DET_ID) REP_DET_ID, REP_ID, SUM(UNIT_QTY) UNIT_QTY, SUM(UNIT_AMT) UNIT_AMT, UNIT_PLAN_REF, UNIT_PLAN_REF_NO
FROM
(
SELECT RD.REP_DET_ID, RD.REP_ID, RD.UNIT_QTY, RD.UNIT_AMT, UT.CERT_PLAN_TYPE AS UNIT_PLAN_REF, UT.CERT_PLAN_REF_NO AS UNIT_PLAN_REF_NO
FROM SMS_UNIT_REP_DETAILS RD
LEFT JOIN SMS_CERTIFICATE UT on UT.CERT_ID = RD.UNIT_TX_ID
) GROUP BY REP_ID, UNIT_PLAN_REF, UNIT_PLAN_REF_NO
) RD ON UR.REP_ID = RD.REP_ID
JOIN (
SELECT MAX(TRAN_SUB_ACC_ID) SUB_ID, MAX(TRAN_ID) TRAN_ID, TRAN_UNIT_TX_ID
FROM SMS_TRANSACTION
WHERE TRAN_UNIT_TX_ID IS NOT NULL
GROUP BY TRAN_UNIT_TX_ID
) T ON T.TRAN_UNIT_TX_ID = UR.REP_ID
LEFT JOIN SMS_UNIT_TX utx ON UR.REP_ID = utx.REP_ID
JOIN SMS_UNITS U ON U.SEC_ID = UR.SEC_ID
LEFT JOIN SMS_CURRENCIES CYD ON CYD.CUR_ID = UR.CUR_ID_DENOM
LEFT JOIN SMS_CURRENCIES CYS ON CYS.CUR_ID = UR.CUR_ID_SUBS
JOIN
(
SELECT AC.SUB_ID,AC.REG_OWNER_REF, RO.IDREGISTERED_OWNER FROM
(
SELECT S.SUB_ID, REG_OWNER_REF FROM SMS_SHAREHOLDER_SUBACCOUNTS S
JOIN SMS_SHAREHOLDER_MASTERACCOUNTS M ON M.MAS_ACCOUNT_CODE = S.MAS_ACCOUNT_CODE
JOIN V_ENTITY_CIR E ON E.ID = M.IND_NIND_ID
UNION
SELECT S.SUB_ID, REG_OWNER_REF FROM SMS_SHAREHOLDER_SUBACCOUNTS S
JOIN SMS_JOINTACC_HOLDERS J ON J.SUB_ID = S.SUB_ID
JOIN V_ENTITY_CIR E ON E.ID = J.IND_NIND_ID
) AC
LEFT JOIN
(
SELECT SUB_ID, E.IND_NIND_ID IDREGISTERED_OWNER
FROM SMS_JOINTACC_HOLDERS jh
JOIN V_ENTITY_CIR E ON E.ID = jh.IND_NIND_ID
WHERE REG_OWNER = '1'
) RO ON RO.SUB_ID = AC.SUB_ID
) ENT ON ENT.SUB_ID = T.SUB_ID
LEFT JOIN SMS_PARAMETERS plan ON RD.UNIT_PLAN_REF = plan.PRM_VALUE AND plan.PRM_DOMAIN_NAME = '77'
LEFT JOIN
(
SELECT DISTINCT SUB_ID,FINAL_SUB_ID
FROM SMS_CONS_SUB
) FS ON FS.SUB_ID = T.SUB_ID
JOIN SMS_SECURITIES SECS ON SECS.SEC_ID = UR.SEC_ID AND NVL(SECS.SEC_IB,'2') = '1'
JOIN SMS_SHAREHOLDER_SUBACCOUNTS SUB ON SUB.SUB_ID = T.SUB_ID
--WHERE U.UNI_MANAGER_ID IN (75253,115100,67911,100156)
WHERE (ENT.IDREGISTERED_OWNER IS NULL OR ENT.IDREGISTERED_OWNER <> 107550)
AND (SUB.SUB_ACCOUNT_TYPE = 1 OR NVL(SECS.SEC_LISTED, 2) <> 1)
AND NVL(SECS.SEC_IB,'2') = '1'
--LEFT JOIN
--(
--      SELECT IND_ID ENT_ID, IND_FIRST_NAME || ' ' || IND_LAST_NAME AGENT_NAME from SMS_INDIVIDUALS WHERE IND_AGENT = '1'
--      UNION
--      SELECT NIND_ID ENT_ID, NIND_NAME AGENT_NAME from SMS_NON_INDIVIDUALS WHERE NIND_AGENT = '1'
--) AG ON AG.ENT_ID = UR.REP_AGENT_ID
UNION ALL
SELECT NULL REP_ID, '400' || T.TRAN_ID TRAN_ID, T.TRAN_SECURITY_ID SEC_ID, CASE WHEN FS.FINAL_SUB_ID IS NULL THEN T.TRAN_SUB_ACC_ID ELSE FS.FINAL_SUB_ID END IDPORTFOLIO, NULL AS CIR,
--CASE WHEN IR.DATETRADE IS NULL THEN TRUNC(T.TRAN_DATE) WHEN IR.DATETRADE > T.TRAN_DATE THEN TRUNC(T.TRAN_DATE) ELSE TRUNC(IR.DATETRADE) END DATETRADE,
T.TRAN_DATE AS DATETRADE,
--NVL2(IR.DATETRADE, TRUNC(T.TRAN_DATE), NULL) DATESETTLEMENT,
T.TRAN_DATE AS DATESETTLEMENT,
RSP_TYPE IDTYPETRADE, T.TRAN_DIRECTION * T.QTY QUANTITY,
CASE WHEN NVL(T.TRAN_QTY,0) = 0 THEN NULL ELSE TRUNC(IR.AMTSETTLEMENT/T.TRAN_QTY,2) END AS TRADEPRICE, NVL(CYD.CUR_CODE, CYDEF.CUR_CODE) CCYSECURITY, 0 COMMISSION,--IR.COMMISSION
CASE WHEN RSP_TYPE = 'CANCELLATION/ADJUSTMENT' THEN 0 ELSE IR.AMTSETTLEMENT END AMTSETTLEMENT, NVL(CYS.CUR_CODE, CYDEF.CUR_CODE) CCYAMTSETTLEMENT,  cast(NULL AS VARCHAR2(10)) BROKER, --IR.AGENT_NAME BROKER
NVL2(ENT.IDREGISTERED_OWNER, 'Y', 'N') REGISTERED_OWNER,
ENT.IDREGISTERED_OWNER, CASE WHEN UP.CERT_PLAN_TYPE IS NULL THEN 'N' WHEN UP.CERT_PLAN_TYPE = '1' THEN 'N' ELSE 'Y' END PLANMARKER,
UP.CERT_PLAN_REF_NO, 'Y' SETTLED, CASE WHEN FS.FINAL_SUB_ID IS NULL THEN T.TRAN_SUB_ACC_ID ELSE FS.FINAL_SUB_ID END SUB_ID, CASE WHEN UP.CERT_PLAN_TYPE = '1' THEN '' ELSE plan.PRM_DISPLAY_NAME END AS PLAN_TYPE
FROM
(
SELECT X.*,X.TRAN_QTY AS QTY
FROM
(
SELECT A.TRAN_ID,A.TRAN_DATE,A.TRAN_TYPE,A.TRAN_QTY,A.TRAN_POSITION,A.TRAN_SUB_ACC_ID,
A.TRAN_SECURITY_ID,A.TRAN_CERT_ID,A.TRAN_UNIT_TX_ID

Regards,

Roshan

Comments
Post Details
Added on Mar 31 2024
2 comments
33 views