/* Create Temp tables */

CREATE TABLE TM_RPA_OIDS AS ( 
SELECT 
  OBJECTID
FROM 
  CACHE_0_10707
WHERE 
  LTRPA_GMTEFFECTIVEDATETIME > TO_DATE('01/01/2004', 'MM/DD/YYYY') AND
  LTRPA_GMTEFFECTIVEDATETIME < TO_DATE('06/30/2007', 'MM/DD/YYYY'));


/*  Creating temp tables for CacheTable CACHE_0_10707_3137*/ 
CREATE TABLE TM_RPA_MVPIDS_3137 AS (
SELECT 
  RSBROKERREF_TYPEID AS OTID,
  RSBROKERREF_ID AS OID
FROM 
  CACHE_0_10707_3137
WHERE 
  OBJECTID IN (select OBJECTID from TM_RPA_OIDS));

  
/*  Creating temp tables for  CacheTable CACHE_0_10707_2889 */ 
CREATE TABLE TM_RPA_MVPIDS_2889 AS (
SELECT 
  LTRPA_RPADJGRPOBJECTREF_TYPEID AS OTID,
  LTRPA_RPADJGRPOBJECTREF_ID AS OID
FROM 
  CACHE_0_10707_2889
WHERE 
  OBJECTID IN (select OBJECTID from TM_RPA_OIDS));


/*  Creating temp tables for CacheTable CACHE_0_10707_2842 */ 
CREATE TABLE TM_RPA_MVPIDS_2842 AS (
SELECT 
  LTRPA_CAPSGRPOBJREF_TYPEID AS OTID,
  LTRPA_CAPSGRPOBJREF_ID AS OID
FROM 
  CACHE_0_10707_2842
WHERE 
  OBJECTID IN (select OBJECTID from TM_RPA_OIDS));


/*  Creating temp tables forCacheTable CACHE_0_10707_2898 */ 
CREATE TABLE TM_RPA_MVPIDS_2898 AS (
SELECT 
  LTRPA_GENRULESGRPOBJREF_TYPEID AS OTID,
  LTRPA_GENRULESGRPOBJREF_ID AS OID
FROM 
  CACHE_0_10707_2898
WHERE 
  OBJECTID IN (select OBJECTID from TM_RPA_OIDS));

exit;

/* Start Delete here */

DELETE FROM LG_RPA_EFFDTPROGRAMPRODUCTS WHERE RPAID IN (SELECT OBJECTID AS RPAID FROM TM_RPA_OIDS);

COMMIT; 

DELETE FROM LG_RPA_ASJRULEASSERTIONS WHERE ADJRULEID in ( SELECT  ADJRULEID FROM   LG_RPA_PROGRAMPRODUCTRULES WHERE  RPAID IN (select OBJECTID AS RPAID from TM_RPA_OIDS));

COMMIT; 

DELETE FROM LG_RPA_ADJRULENOOFASSERTIONS WHERE ADJRULEID in (SELECT ADJRULEID FROM LG_RPA_PROGRAMPRODUCTRULES WHERE RPAID IN (select OBJECTID AS RPAID from TM_RPA_OIDS));

COMMIT; 

DELETE FROM LG_RPA_RULEADJUSTENTS WHERE ADJRULEID in (SELECT ADJRULEID FROM LG_RPA_PROGRAMPRODUCTRULES WHERE RPAID IN (select OBJECTID AS RPAID from TM_RPA_OIDS));

COMMIT; 

DELETE FROM LG_RPA_PROGRAMPRODUCTRULES WHERE RPAID IN (select OBJECTID AS RPAID from TM_RPA_OIDS);

COMMIT; 

DELETE FROM LG_RPA_GR_RULEASSERTIONS WHERE GRRULEID in (SELECT GRRULEID FROM LG_RPA_GR_RULES WHERE RPAID IN (select OBJECTID AS RPAID from TM_RPA_OIDS));

COMMIT; 

DELETE FROM LG_RPA_GRRULENOOFASSERTIONS WHERE GRRULEID in (SELECT GRRULEID FROM LG_RPA_GR_RULES WHERE RPAID IN (select OBJECTID AS RPAID from TM_RPA_OIDS));

COMMIT; 

DELETE FROM LG_RPA_GR_RULES WHERE RPAID IN (select OBJECTID AS RPAID from TM_RPA_OIDS);

COMMIT; 

DELETE FROM LG_RPA_CAPRULEASSERTIONS WHERE CAPRULEID in (SELECT CAPRULEID FROM LG_RPA_CAPPROGRAMPRODUCTS WHERE RPAID IN (select OBJECTID AS RPAID from TM_RPA_OIDS));

COMMIT; 

DELETE FROM LG_RPA_CAPRULENOOFASSERTIONS WHERE CAPRULEID in (SELECT CAPRULEID FROM LG_RPA_CAPPROGRAMPRODUCTS WHERE RPAID IN (select OBJECTID AS RPAID from TM_RPA_OIDS));

COMMIT; 

DELETE FROM LG_RPA_CAPADJUSTMENTS WHERE CAPRULEID in (SELECT CAPRULEID FROM LG_RPA_CAPPROGRAMPRODUCTS WHERE RPAID IN (select OBJECTID AS RPAID from TM_RPA_OIDS));

COMMIT; 

DELETE FROM LG_RPA_CAPPROGRAMPRODUCTS WHERE RPAID IN (select OBJECTID AS RPAID from TM_RPA_OIDS);

COMMIT; 

DELETE from  CACHE_0_10707_2889 WHERE OBJECTID IN ( select OBJECTID from TM_RPA_OIDS);

COMMIT;

DELETE FROM TB_GLOBAL_OBJECTS WHERE OBJECTTYPEID = (
SELECT 
  DISTINCT(OTID)
FROM 
  TM_RPA_MVPIDS_2889) and OBJECTID IN (SELECT OID FROM TM_RPA_MVPIDS_2889);  

COMMIT;


DELETE from  CACHE_0_10707_2842 WHERE OBJECTID IN ( select OBJECTID from TM_RPA_OIDS);

COMMIT;

DELETE FROM TB_GLOBAL_OBJECTS WHERE OBJECTTYPEID = (
SELECT 
  DISTINCT(OTID)
FROM 
  TM_RPA_MVPIDS_2842) and OBJECTID IN (SELECT OID FROM TM_RPA_MVPIDS_2842);  
  
COMMIT;


DELETE from  CACHE_0_10707_2898 WHERE OBJECTID IN ( select OBJECTID from TM_RPA_OIDS);

COMMIT;

DELETE FROM TB_GLOBAL_OBJECTS WHERE OBJECTTYPEID = (
SELECT 
  DISTINCT(OTID)
FROM 
  TM_RPA_MVPIDS_2898) and OBJECTID IN (SELECT OID FROM TM_RPA_MVPIDS_2898);    

COMMIT;


DELETE from  CACHE_0_10707_3137 WHERE OBJECTID IN ( select OBJECTID from TM_RPA_OIDS);

COMMIT;

DELETE FROM TB_GLOBAL_OBJECTS WHERE OBJECTTYPEID = (
SELECT 
  DISTINCT(OTID)
FROM 
  TM_RPA_MVPIDS_3137) and OBJECTID IN (SELECT OID FROM TM_RPA_MVPIDS_3137);  

COMMIT;


DELETE from CACHE_0_10707 WHERE OBJECTID IN ( select OBJECTID from TM_RPA_OIDS);

COMMIT;

DELETE  FROM 
  TB_GLOBAL_OBJECTS
WHERE 
  OBJECTTYPEID = 10707 AND
  OBJECTID IN (SELECT OBJECTID FROM TM_RPA_OIDS);
  
  
COMMIT; 

DROP TABLE TM_RPA_MVPIDS_2889;

DROP TABLE TM_RPA_MVPIDS_2842;

DROP TABLE TM_RPA_MVPIDS_2898;

DROP TABLE TM_RPA_MVPIDS_3137;
  
DROP TABLE TM_RPA_OIDS;