There could be multiple ways to archive DB tables. Just posting this is been used in one of the application.
ESPGO
is Application name
Procedure
Name: ESPGO_ARCHIVAL
Frequency:
WEEKEND
Archival
Period: 1 WEEK (7 DAYS)
Purpose: Archival of ESPGO Dynamic Tables:
1.
ESP_TP_CACHE (Archival Table = ESPGO_TP_CACHE_ARCHIVE)
2. ESP_REPORTING_TP_CACHE (Archival
table = ESPGO_REPRTNG_TP_CACHE_ARCHIVE)
3.
EXP_RES_CASE_TRADE_HIS (Archival table = EXP_RES_CASE_TRADE_HIS_ARCHIVE)
4.
EXP_RES_CASE_TRADE /*PURGE*/
5.
EXP_RES_CASE (Archival table = EXP_RES_CASE_ARCHIVE)
6. ESPGO_CASE_INPUT (Archival
table = ESPGO_CASE_INPUT_ARCHIVE)
7.
DBCOE_SCHEDULER (Archival table = DBCOE_SCHEDULER_ARCHIVE)
8.
ESP_REF_NUM (Archival table = ESP_REF_NUM_ARCHIVE)
9. PROCESSING_HISTORY
(Archival table = PROCESSING_HISTORY_ARCHIVE)
Create or Replace PROCEDURE
ESPGO_ARCHIVAL (NumberofDays integer DEFAULT 7)
AS
r number;
v_table_name varchar2(100);
-- VARIABLES FOR RECORD COUNT OF MAIN
TABLES BEFORE ARCHIVAL
rec_cnt1 number; rec_cnt2 number;
rec_cnt3 number; rec_cnt4 number; rec_cnt5 number; rec_cnt6 number; rec_cnt7
number; rec_cnt8 number; rec_cnt9 number;
-- VARIABLES FOR RECORD COUNT OF
ARCHIVAL TABLES BEFORE ARCHIVAL
rec_cnt11 number; rec_cnt12 number;
rec_cnt13 number; rec_cnt14 number; rec_cnt15 number; rec_cnt16 number;
rec_cnt17 number; rec_cnt18 number;
-- VARIABLES FOR RECORD COUNT OF MAIN
TABLES AFTER ARCHIVAL
rec_cnt21 number; rec_cnt22 number;
rec_cnt23 number; rec_cnt24 number; rec_cnt25 number; rec_cnt26 number;
rec_cnt27 number; rec_cnt28 number; rec_cnt29 number;
-- VARIABLES FOR RECORD COUNT OF
ARCHIVAL TABLES AFTER ARCHIVAL
rec_cnt31 number; rec_cnt32 number;
rec_cnt33 number; rec_cnt34 number; rec_cnt35 number; rec_cnt36 number;
rec_cnt37 number; rec_cnt38 number;
err_code number;
err_msg varchar2(4000);
BEGIN
r := 0;
-- VARIABLES FOR RECORD COUNT OF MAIN
TABLES BEFORE ARCHIVAL
rec_cnt1 := 0; rec_cnt2 := 0; rec_cnt3
:= 0; rec_cnt4 := 0; rec_cnt5 := 0; rec_cnt6 := 0; rec_cnt7 := 0; rec_cnt8 :=
0; rec_cnt9 := 0;
-- VARIABLES FOR RECORD COUNT OF
ARCHIVAL TABLES BEFORE ARCHIVAL
rec_cnt11 := 0; rec_cnt12 := 0;
rec_cnt13 := 0; rec_cnt14 := 0; rec_cnt15 := 0; rec_cnt16 := 0; rec_cnt17 := 0;
rec_cnt18 := 0;
-- VARIABLES FOR RECORD COUNT OF MAIN
TABLES AFTER ARCHIVAL
rec_cnt21 := 0; rec_cnt22 := 0;
rec_cnt23 := 0; rec_cnt24 := 0; rec_cnt25 := 0; rec_cnt26 := 0; rec_cnt27 := 0;
rec_cnt28 := 0; rec_cnt29 := 0;
-- VARIABLES FOR RECORD COUNT OF
ARCHIVAL TABLES AFTER ARCHIVAL
rec_cnt31 := 0; rec_cnt32 := 0; rec_cnt33
:= 0; rec_cnt34 := 0; rec_cnt35 := 0; rec_cnt36 := 0; rec_cnt37 := 0; rec_cnt38
:= 0;
-- Record count of Main tables Before Archival
select count(*) into
rec_cnt1 from ESP_TP_CACHE;
select count(*) into
rec_cnt2 from ESP_REPORTING_TP_CACHE;
select count(*) into
rec_cnt3 from EXP_RES_CASE_TRADE_HIS;
select count(*) into
rec_cnt4 from EXP_RES_CASE_TRADE;
select count(*) into
rec_cnt5 from
EXP_RES_CASE;
select count(*) into
rec_cnt6 from ESPGO_CASE_INPUT;
select count(*) into
rec_cnt7 from DBCOE_SCHEDULER;
select count(*) into
rec_cnt8 from ESP_REF_NUM;
select count(*) into
rec_cnt9 from PROCESSING_HISTORY;
-- Entry to Archival Audit log
-- RECORD COUNT OF MAIN TABLES BEFORE
ARCHIVAL
-- 1.ESP_TP_CACHE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN ESP_TP_CACHE TABLE
BEFORE ARCHIVAL = ' || rec_cnt1 FROM DUAL;
COMMIT;
-- 2. ESP_REPORTING_TP_CACHE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
ESP_REPORTING_TP_CACHE TABLE BEFORE ARCHIVAL = ' || rec_cnt2 FROM DUAL;
COMMIT;
--3.EXP_RES_CASE_TRADE_HIS
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
EXP_RES_CASE_TRADE_HIS TABLE BEFORE ARCHIVAL = ' || rec_cnt3 FROM DUAL;
COMMIT;
--4.EXP_RES_CASE_TRADE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN EXP_RES_CASE_TRADE
TABLE BEFORE ARCHIVAL = ' || rec_cnt4 FROM DUAL;
COMMIT;
-- 5.EXP_RES_CASE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN EXP_RES_CASE TABLE
BEFORE ARCHIVAL = ' || rec_cnt5 FROM DUAL;
COMMIT;
-- 6. ESPGO_CASE_INPUT
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN ESPGO_CASE_INPUT
TABLE BEFORE ARCHIVAL = ' || rec_cnt6 FROM DUAL;
COMMIT;
--7.DBCOE_SCHEDULER
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN DBCOE_SCHEDULER
TABLE BEFORE ARCHIVAL = ' || rec_cnt7 FROM DUAL;
COMMIT;
--8. ESP_REF_NUM
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO.
OF RECORDS IN ESP_REF_NUM TABLE BEFORE ARCHIVAL = ' || rec_cnt8 FROM DUAL;
COMMIT;
--8. PROCESSING_HISTORY
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN PROCESSING_HISTORY
TABLE BEFORE ARCHIVAL = ' || rec_cnt9 FROM DUAL;
COMMIT;
-- Record count of Archival
tables Before Archival
Select count(*) into
rec_cnt11 from ESPGO_TP_CACHE_ARCHIVE;
Select count(*) into
rec_cnt12 from ESPGO_REPRTNG_TP_CACHE_ARCHIVE;
Select count(*) into
rec_cnt13 from EXP_RES_CASE_TRADE_HIS_ARCHIVE;
Select count(*) into
rec_cnt14 from EXP_RES_CASE_ARCHIVE;
Select count(*) into rec_cnt15
from ESPGO_CASE_INPUT_ARCHIVE;
Select count(*) into
rec_cnt16 from DBCOE_SCHEDULER_ARCHIVE;
Select count(*) into
rec_cnt17 from ESP_REF_NUM_ARCHIVE;
Select count(*) into
rec_cnt18 from PROCESSING_HISTORY_ARCHIVE;
-- RECORD COUNT OF ARCHIVAL
TABLES BEFORE ARCHIVAL
--
1.ESPGO_TP_CACHE_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
ESPGO_TP_CACHE_ARCHIVE TABLE BEFORE ARCHIVAL = ' || rec_cnt11 FROM DUAL;
COMMIT;
-- 2.
ESPGO_REPRTNG_TP_CACHE_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
ESPGO_REPRTNG_TP_CACHE_ARCHIVE TABLE BEFORE ARCHIVAL = ' || rec_cnt12 FROM
DUAL;
COMMIT;
--3.EXP_RES_CASE_TRADE_HIS_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN EXP_RES_CASE_TRADE_HIS_ARCHIVE
TABLE BEFORE ARCHIVAL = ' || rec_cnt13 FROM DUAL;
COMMIT;
--4.EXP_RES_CASE_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN EXP_RES_CASE_ARCHIVE
TABLE BEFORE ARCHIVAL = ' || rec_cnt14 FROM DUAL;
COMMIT;
--
5.ESPGO_CASE_INPUT_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN ESPGO_CASE_INPUT_ARCHIVE
TABLE BEFORE ARCHIVAL = ' || rec_cnt15 FROM DUAL;
COMMIT;
-- 6. DBCOE_SCHEDULER_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN DBCOE_SCHEDULER_ARCHIVE
TABLE BEFORE ARCHIVAL = ' || rec_cnt16 FROM DUAL;
COMMIT;
--7.ESP_REF_NUM_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
ESP_REF_NUM_ARCHIVE TABLE BEFORE ARCHIVAL = ' || rec_cnt17 FROM DUAL;
COMMIT;
--8. PROCESSING_HISTORY_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
PROCESSING_HISTORY_ARCHIVE TABLE BEFORE ARCHIVAL = ' || rec_cnt18
FROM DUAL;
COMMIT;
-- ARCHIVAL PROCESS START
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'ARCHIVAL PROCESS STARTED' FROM DUAL;
COMMIT;
-- 1. TABLE = ESP TP CACHE
/*
Action : ARCHIVE
Criteria : i) TP_STATUS =
'CASECLOSED'
ii) BPM_CASE_ID IS NULL
iii)(DB_STATUS = 'S' OR DELTA_FLAG = 'YES') - Arhieve only Settled case and no
Archival of Unsettled case -- Added on 07-11-2016 as asked by Vishal for Cognos
Report
iv) SETTLEMENT_DATE < TRUNC(SYSDATE - 7)
Period : 7 Days
Archival Table :
ESPGO_TP_CACHE_ARCHIVE
*/
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'ARCHIVAL
PROCESS STARTED FOR TABLES ESP_TP_CACHE AND ESP_REPORTING_TP_CACHE' FROM
DUAL;
COMMIT;
-- FOR LOOP
FOR i IN
( SELECT ESPEAR_TRADE_NO
FROM ESP_TP_CACHE
WHERE TP_STATUS =
'CASECLOSED'
--AND BPM_CASE_ID IS NULL
/*Commented on 12/JAN/2017 to include all the BPM Cases*/
AND (DB_STATUS = 'S'
OR DELTA_FLAG = 'YES') -- added on 07-NOV-2016 to Archive only Settled
case as required by Vishal in Jira on 14-Oct-2016
AND
(CASE TP_SOURCE
WHEN 'INTRADAY'
THEN DATE '1970-01-01' + ( 1 / 24 / 60 / 60 / 1000) * SETTLEMENT_DATE
ELSE
TO_DATE(SETTLEMENT_DATE,'DD/MON/YYYY')
END
) <
TRUNC(sysdate -
NumberofDays)
-- TRUNC(sysdate - 7) (Previous) -- changed on 07-NOV-2016 as
discussed on 03-Nov with Team, refer my comments in Jira on 03-Nov
)
LOOP
v_table_name := 'ESPGO_TP_CACHE_ARCHIVE';
/*
28-NOV-2016 */
--
Adding below code for over-writing the record in ESPGO_TP_CACHE_ARCHIVE table,
if the trade (of Archival records) already exists in it.
DELETE
FROM ESPGO_TP_CACHE_ARCHIVE WHERE ESPEAR_TRADE_NO = i.ESPEAR_TRADE_NO;
-- Moving data
(ESPEAR_TRADE_NO) to Archival table "ESPGO_TP_CACHE_ARCHIVE"
INSERT INTO
ESPGO_TP_CACHE_ARCHIVE
(
ESPEAR_TRADE_NO,
CASSIT_CODE,
TRANS_TYPE,
ISIN_ID,
SECURITY_NAME,
DEL_PAYMENT_METHOD,
LOCATION_SHORT_CODE,
UNSETTLED_CASH,
DB_STATUS,
EXTERNAL_STATUS,
SETTLEMENT_DATE,
COUNTER_PARTY,
BOOK_NO,
BPM_CASE_ID,
BPM_CASE_STATUS,
DELTA_FLAG,
MESSAGE,
LEGAL_ENTITY,
TRADE_TYPE_CLASS,
SECURITY_NUMBER,
CURRENCY_CODE,
DEPOT_LOCATION_NAME,
BENEFICIARY,
MARKET_PRICE,
TP_SOURCE,
TRADE_DATE,
FX_RATE,
ACTIVE_STATUS,
AGE,
QUEUE_FLAG,
REGION_SCHEDULED_TIME,
CURRENT_TASK_QUEUE,
NO_OF_HOLIDAY,
HZ_MSG_TYPE,
BPM_MESSAGE_TYPE,
PERSIST,
IS_ETF,
UNSETTLED_CASH_GBP,
RPN,
MATCH_STATUS,
SWIFT_ACTION,
SWIFT_DESC,
PRIORITY_CLIENT,
BUY_SELL_INDICATOR,
ACTIONED,
COUNTER_PARTY_NAME,
UNSETTLED_STOCK,
ACC_SHORT_CDE,
ADPSECNO,
AGENT_ACCT_ID,
AGENT_BIC_NAME,
BASKET_ID,
BENEFICIARY_ACCT_ID,
BENEFICIARY_BIC_NAME,
BOND_SHARE,
BOOKEEP_CODE,
CANCELLED_ACTIVE_IND,
CERTIFICATION_COUNTRY,
CPTY_TYP_CDE,
CROSS_RATE,
CUST_TYPE,
CUSTODIAN_ACCT_ID,
CUSTODIAN_BIC_NAME,
DEAL_PRC,
DEAL_STATUS,
DEALER_CD,
DEPO_SHORT_CODE,
FIRM_BOOK_NAME,
FO_PORT_ID,
FRONT_OFF_NO,
GROUP_ID,
LOC_NO,
MARKET_VALUE,
MRKT_CLOSE_TMS,
MRKT_PRC,
NOMINAL_AMOUNT,
NOSTRO_NAME,
NOSTRO_NO,
OFFICE_CODE,
PREV_VERSION_NO,
PROFIT_CENTRE_ID,
PSET_BIC,
REG_REP_CODE,
REGION,
REPORTING_DATE,
SEC_CCY_CDE,
SEC_NO,
SEC_SHORT_CDE,
SETT_CCY_CDE,
SETTLEMENT_VALUE,
TICKET_NO,
TRADE_ORIGIN_ID,
TRADE_SUMMARY_STATUS,
TRADE_TYPE,
TRADE_VERSION_NO,
VESTED_BUS_DTE,
INSERT_DTE,
COMPANY_CODE,
STATUS_DATE,
AGENT_REF,
SOURCE,
TEXT,
STDN_SEQUENCE,
ESPEAR_INSTRUCT_ID,
TP_STATUS,
CREATED_TS,
LAST_MODIFIED_TS,
IS_UPDATE,
FORMATTED_SETTLEMENT,
MARKET,
COUNTER_PARTY_FULL_NAME,
ARCHIVE_DATETIME
)
SELECT
ESPEAR_TRADE_NO,
CASSIT_CODE,
TRANS_TYPE,
ISIN_ID,
SECURITY_NAME,
DEL_PAYMENT_METHOD,
LOCATION_SHORT_CODE,
UNSETTLED_CASH,
DB_STATUS,
EXTERNAL_STATUS,
SETTLEMENT_DATE,
COUNTER_PARTY,
BOOK_NO,
BPM_CASE_ID,
BPM_CASE_STATUS,
DELTA_FLAG,
MESSAGE,
LEGAL_ENTITY,
TRADE_TYPE_CLASS,
SECURITY_NUMBER,
CURRENCY_CODE,
DEPOT_LOCATION_NAME,
BENEFICIARY,
MARKET_PRICE,
TP_SOURCE,
TRADE_DATE,
FX_RATE,
ACTIVE_STATUS,
AGE,
QUEUE_FLAG,
REGION_SCHEDULED_TIME,
CURRENT_TASK_QUEUE,
NO_OF_HOLIDAY,
HZ_MSG_TYPE,
BPM_MESSAGE_TYPE,
PERSIST,
IS_ETF,
UNSETTLED_CASH_GBP,
RPN,
MATCH_STATUS,
SWIFT_ACTION,
SWIFT_DESC,
PRIORITY_CLIENT,
BUY_SELL_INDICATOR,
ACTIONED,
COUNTER_PARTY_NAME,
UNSETTLED_STOCK,
ACC_SHORT_CDE,
ADPSECNO,
AGENT_ACCT_ID,
AGENT_BIC_NAME,
BASKET_ID,
BENEFICIARY_ACCT_ID,
BENEFICIARY_BIC_NAME,
BOND_SHARE,
BOOKEEP_CODE,
CANCELLED_ACTIVE_IND,
CERTIFICATION_COUNTRY,
CPTY_TYP_CDE,
CROSS_RATE,
CUST_TYPE,
CUSTODIAN_ACCT_ID,
CUSTODIAN_BIC_NAME,
DEAL_PRC,
DEAL_STATUS,
DEALER_CD,
DEPO_SHORT_CODE,
FIRM_BOOK_NAME,
FO_PORT_ID,
FRONT_OFF_NO,
GROUP_ID,
LOC_NO,
MARKET_VALUE,
MRKT_CLOSE_TMS,
MRKT_PRC,
NOMINAL_AMOUNT,
NOSTRO_NAME,
NOSTRO_NO,
OFFICE_CODE,
PREV_VERSION_NO,
PROFIT_CENTRE_ID,
PSET_BIC,
REG_REP_CODE,
REGION,
REPORTING_DATE,
SEC_CCY_CDE,
SEC_NO,
SEC_SHORT_CDE,
SETT_CCY_CDE,
SETTLEMENT_VALUE,
TICKET_NO,
TRADE_ORIGIN_ID,
TRADE_SUMMARY_STATUS,
TRADE_TYPE,
TRADE_VERSION_NO,
VESTED_BUS_DTE,
INSERT_DTE,
COMPANY_CODE,
STATUS_DATE,
AGENT_REF,
SOURCE,
TEXT,
STDN_SEQUENCE,
ESPEAR_INSTRUCT_ID,
TP_STATUS,
CREATED_TS,
LAST_MODIFIED_TS,
IS_UPDATE,
FORMATTED_SETTLEMENT,
MARKET,
COUNTER_PARTY_FULL_NAME,
SYSTIMESTAMP
--ARCHIVE_DATETIME
FROM ESP_TP_CACHE
WHERE ESPEAR_TRADE_NO
= i.ESPEAR_TRADE_NO;
-- Deletion of
Records from main table "ESP_TP_CACHE" after moving to Archival table
"ESPGO_TP_CACHE_ARCHIVE"
DELETE FROM
ESP_TP_CACHE WHERE ESPEAR_TRADE_NO =
i.ESPEAR_TRADE_NO;
-- 2. TABLE =
ESP_REPORTING_TP_CACHE
/*
Action : ARCHIVE
Criteria : All
ESPEAR_TRADE_NO of ESP_TP_CACHE Archived above
Period : 7 Days
Archival Table : ESPGO_REPRTNG_TP_CACHE_ARCHIVE
*/
v_table_name :=
'ESP_REPORTING_TP_CACHE';
/*
28-NOV-2016 */
--
Adding below code for over-writing the record in ESPGO_TP_CACHE_ARCHIVE table,
if the trade (of Archival records) already exists in it.
DELETE
FROM ESPGO_REPRTNG_TP_CACHE_ARCHIVE WHERE ESPEAR_TRADE_NO = i.ESPEAR_TRADE_NO;
-- Moving data
(ESPEAR_TRADE_NO) to Archival table "ESPGO_REPRTNG_TP_CACHE_ARCHIVE"
INSERT
INTO ESPGO_REPRTNG_TP_CACHE_ARCHIVE
(
ESPEAR_TRADE_NO,
TP_SOURCE,
SETTLEMENT_DATE,
TRADE_DATE,
MRKT_CLOSE_TMS,
REPORTING_DATE,
VESTED_BUS_DTE,
INSERT_DTE,
CREATED_TS,
LAST_MODIFIED_TS,
IS_UPDATE,
AGE,
ARCHIVE_DATETIME
)
SELECT
ESPEAR_TRADE_NO,
TP_SOURCE,
SETTLEMENT_DATE,
TRADE_DATE,
MRKT_CLOSE_TMS,
REPORTING_DATE,
VESTED_BUS_DTE,
INSERT_DTE,
CREATED_TS,
LAST_MODIFIED_TS,
IS_UPDATE,
AGE,
SYSTIMESTAMP
--ARCHIVE_DATETIME
FROM
ESP_REPORTING_TP_CACHE WHERE ESPEAR_TRADE_NO = i.ESPEAR_TRADE_NO;
-- Deletion of
Records from main table "ESP_REPORTING_TP_CACHE" after moving to
Archival table "ESPGO_REPRTNG_TP_CACHE_ARCHIVE"
DELETE FROM
ESP_REPORTING_TP_CACHE WHERE ESPEAR_TRADE_NO =
i.ESPEAR_TRADE_NO;
COMMIT;
r := r + 1;
END LOOP;
-- Entry to Archival Audit log -
Completion of ESP_TP_CACHE, ESP_REPORTING_TP_CACHE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'ARCHIVAL PROCESS COMPLETED FOR
TABLES ESP_TP_CACHE, ESP_REPORTING_TP_CACHE' FROM DUAL;
COMMIT;
-- Entry to Archival Audit log -
Number of Records Archived
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'No. of Records Archived : '
||r FROM DUAL;
COMMIT;
-- B. ARCHIVAL OF
EXP_RES_CASE_TRADE_HIS,EXP_RES_CASE_TRADE,EXP_RES_CASE (APP TABLES)
/*
EFFECTED TABLES :
1. EXP_RES_CASE_TRADE_HIS
(CHILD TABLE ; TO BE ARCHIVE)
2.
EXP_RES_CASE_TRADE (CHILD TABLE ; TO BE PURGE)
3.
EXP_RES_CASE (PARENT TABLE ; TO BE ARCHIVE)
PERIOD : 1 WEEK (7 DAYS)
CRITERIA/CONDITIONS FROM PARENT
TABLE "EXP_RES_CASE" :
i) RES_ID = 'ESPGO'
ii) RES_CASE_STATUS IN
('CASECLOSED','CANCEL')
iii) extract(day from
(cast(systimestamp at time zone 'GMT' as timestamp(6)) - LAST_MOD_TSMP))
> 14
*/
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'ARCHIVAL PROCESS STARTED FOR TABLES
EXP_RES_CASE_TRADE_HIS,EXP_RES_CASE_TRADE,ESPGO_CASE_INPUT,DBCOE_SCHEDULER,EXP_RES_CASE'
FROM DUAL;
COMMIT;
-- SELECTING MATCHING RECORDS TO
BE ARCHIVE FROM EXP_RES_CASE (PARENT TABLE)
-- FOR LOOP
FOR i IN
(
SELECT EXP_RES_CASE_REF
FROM EXP_RES_CASE
WHERE RES_ID =
'ESPGO'
--AND RES_CASE_STATUS IN
('CASECLOSED','CANCEL') /*Commented on 12/JAN/2017 to include only CASECLOSED
Status*/
AND RES_CASE_STATUS
IN ('CASECLOSED','PNDSETTL') -- Added 'PNDSETTL' on 20-APR-2017
AND extract(day from
(cast(systimestamp at time zone 'GMT' as timestamp(6)) - LAST_MOD_TSMP))
> NumberofDays ---14 (Previous) -- changed on 07-NOV-2016 as
discussed on 03-Nov with Team, refer my comments in Jira on
03-Nov
)
LOOP
-- 3. TABLE =
EXP_RES_CASE_TRADE_HIS
/*
Action : ARCHIVE
Archival Table :
EXP_RES_CASE_TRADE_HIS_ARCHIVE
*/
v_table_name :=
'EXP_RES_CASE_TRADE_HIS';
DELETE FROM
EXP_RES_CASE_TRADE_HIS_ARCHIVE WHERE EXP_RES_CASE_REF = i.EXP_RES_CASE_REF;
-- Moving data
(EXP_RES_CASE_REF) to Archival table "EXP_RES_CASE_TRADE_HIS_ARCHIVE"
INSERT INTO
EXP_RES_CASE_TRADE_HIS_ARCHIVE
(
EXP_RES_CASE_REF,
TRADE_NUM,
TRADE_VER_NUM,
MSG_ID,
CASE_TRADE_SEQ,
RES_ACTION,
RES_USER,
RES_LOG_CATG,
RES_COMMENT,
RES_TSMP,
ACTION_USR,
ACTION_GRP,
ARCHIVE_DATETIME
)
SELECT
EXP_RES_CASE_REF,
TRADE_NUM,
TRADE_VER_NUM,
MSG_ID,
CASE_TRADE_SEQ,
RES_ACTION,
RES_USER,
RES_LOG_CATG,
RES_COMMENT,
RES_TSMP,
ACTION_USR,
ACTION_GRP,
SYSTIMESTAMP
--ARCHIVE_DATETIME
FROM EXP_RES_CASE_TRADE_HIS
WHERE EXP_RES_CASE_REF = i.EXP_RES_CASE_REF;
-- Deletion of Records from
main table "EXP_RES_CASE_TRADE_HIS" after moving to Archival table
"EXP_RES_CASE_TRADE_HIS_ARCHIVE"
DELETE FROM
EXP_RES_CASE_TRADE_HIS WHERE EXP_RES_CASE_REF =
i.EXP_RES_CASE_REF;
-- 4. TABLE =
EXP_RES_CASE_TRADE
/*
Action : PURGE
Purpose : Delete the
same Exception Case Reference Number (EXP_RES_CASE_REF) of above child table
matching the
EXP_RES_CASE_REF of "EXP_RES_CASE" (Parent Table) which are Older
than 14 Days
*/
v_table_name :=
'EXP_RES_CASE_TRADE';
DELETE FROM
EXP_RES_CASE_TRADE WHERE EXP_RES_CASE_REF = i.EXP_RES_CASE_REF;
--5. TABLE =
ESPGO_CASE_INPUT
/*
Action : ARCHIVE
Criteria : All
records with TRADENO = ESP_RES_CASE.MSGID
Archival Table :
ESPGO_CASE_INPUT_ARCHIVE
*/
v_table_name :=
'ESPGO_CASE_INPUT_ARCHIVE';
-- Moving data (TRADENO) to
Archival table "ESPGO_CASE_INPUT_ARCHIVE"
DELETE FROM
ESPGO_CASE_INPUT_ARCHIVE
WHERE
TRADENO IN (SELECT
MSG_ID FROM EXP_RES_CASE ER WHERE EXP_RES_CASE_REF = i.EXP_RES_CASE_REF
AND MSG_ID NOT IN(SELECT BUSINESS_TRANSACTION_REF_NO From DBCOE_SCHEDULER DS
Where DS.SCHEDULE_FLAG IN('SCHEDULED','IN-PROGRESS')));
INSERT INTO
ESPGO_CASE_INPUT_ARCHIVE
(
MSGID,
REGION,
PRIORITYCLIENT,
SWIFTACTION,
UNSETTELEDCASHGBP,
MATCHSTATUS,
SWIFTDESCRIPTION,
UNSETTLEDSTOCK,
TRADENO,
TRADEDATE,
ACCOUNT,
SECURITY,
SETTLEDDATE,
UNSETTLEDCASH,
COUNTERPARTYSHORT,
TRANSTYPE,
SECNUMBER,
PAYCCY,
DELPAYMETHOD,
TRADETYPECLASS,
BENIFICIARYBICNAME,
SWIFTCODE,
MARKET,
CLIENTNAME,
NEXTTASKTYPEID,
NEXTTASKSUBJECT,
TEAMNAME,
PRIORITY,
NEXTPOSSIBLEACTIONS,
TIMESCHEDULEID,
HOLIDAYSCHEDULENAME,
EXCLUDEHOLIDAY,
CALCULATIONMETHOD,
OFFSET,
OFFSETUNIT,
TIMEZONE,
RPN,
TMGCONTRACT,
SCHEDULE_TIME,
LAST_MOD_TSMP,
ETF, /*Added ETF Filter on 12/JAN/2017*/
VERSION,
COUNTERPARTYNAME,
ARCHIVE_DATETIME
)
SELECT
MSGID,
REGION,
PRIORITYCLIENT,
SWIFTACTION,
UNSETTELEDCASHGBP,
MATCHSTATUS,
SWIFTDESCRIPTION,
UNSETTLEDSTOCK,
TRADENO,
TRADEDATE,
ACCOUNT,
SECURITY,
SETTLEDDATE,
UNSETTLEDCASH,
COUNTERPARTYSHORT,
TRANSTYPE,
SECNUMBER,
PAYCCY,
DELPAYMETHOD,
TRADETYPECLASS,
BENIFICIARYBICNAME,
SWIFTCODE,
MARKET,
CLIENTNAME,
NEXTTASKTYPEID,
NEXTTASKSUBJECT,
TEAMNAME,
PRIORITY,
NEXTPOSSIBLEACTIONS,
TIMESCHEDULEID,
HOLIDAYSCHEDULENAME,
EXCLUDEHOLIDAY,
CALCULATIONMETHOD,
OFFSET,
OFFSETUNIT,
TIMEZONE,
RPN,
TMGCONTRACT,
SCHEDULE_TIME,
LAST_MOD_TSMP,
ETF,
VERSION,
COUNTERPARTYNAME,
SYSTIMESTAMP
--ARCHIVE_DATETIME
FROM ESPGO_CASE_INPUT
WHERE
TRADENO IN (SELECT
MSG_ID FROM EXP_RES_CASE ER WHERE EXP_RES_CASE_REF = i.EXP_RES_CASE_REF
AND MSG_ID NOT IN(SELECT BUSINESS_TRANSACTION_REF_NO From DBCOE_SCHEDULER DS
Where DS.SCHEDULE_FLAG IN('SCHEDULED','IN-PROGRESS')));
-- Deletion of Records from
main table "ESPGO_CASE_INPUT" after moving to Archival table
"ESPGO_CASE_INPUT_ARCHIVE"
DELETE FROM
ESPGO_CASE_INPUT
WHERE
TRADENO IN (SELECT
MSG_ID FROM EXP_RES_CASE ER WHERE EXP_RES_CASE_REF = i.EXP_RES_CASE_REF
AND MSG_ID NOT IN(SELECT BUSINESS_TRANSACTION_REF_NO From DBCOE_SCHEDULER DS
Where DS.SCHEDULE_FLAG IN('SCHEDULED','IN-PROGRESS')));
--6. TABLE =
PROCESSING_HISTORY
/*
Action :
ARCHIVE
Criteria
: All records with PROCESSING_HISTORY.BUSINESS_TXN_REF_NO = ESP_RES_CASE.MSGID
Archival
Table : PROCESSING_HISTORY_ARCHIVE
*/
v_table_name
:= 'PROCESSING_HISTORY_ARCHIVE';
-- Moving data
(TRADENO) to Archival table "PROCESSING_HISTORY_ARCHIVE"
DELETE FROM PROCESSING_HISTORY_ARCHIVE
WHERE
BUSINESS_TXN_REF_NO IN(SELECT MSG_ID
FROM EXP_RES_CASE ER WHERE EXP_RES_CASE_REF = i.EXP_RES_CASE_REF
AND MSG_ID NOT IN(SELECT BUSINESS_TRANSACTION_REF_NO From DBCOE_SCHEDULER DS
Where DS.SCHEDULE_FLAG IN('SCHEDULED','IN-PROGRESS')));
INSERT INTO PROCESSING_HISTORY_ARCHIVE
(
PROCESS_ACTION_ID,
PROCESS_APPLICATION_ID,
PROCESS_SNAPSHOT_ID,
PROCESS_ID,
ACTIVITY_ID,
APPLICATION_NAME,
SNAPSHOT_NAME,
PROCESS_NAME,
ACTIVITY_NAME,
CASE_REFERENCE_NO,
TASK_ID,
TASK_COUNTER,
BUSINESS_TXN_REF_NO,
TIME_STAMP,
USER_ACTION,
USER_NAME,
LOG_CATEGORY,
COMMENTS,
PROCESS_GUID,
BIZREF2,
BIZREF3,
BIZREF4,
BIZREF5,
TECHCOMMENTS,
BIZPROCESSID,
ACTIONSTATUS,
BPMPROCESSNAME,
BPMACTIVITYNAME,
BIZTASKID,
ARCHIVE_DATETIME
)
SELECT
PROCESS_ACTION_ID,
PROCESS_APPLICATION_ID,
PROCESS_SNAPSHOT_ID,
PROCESS_ID,
ACTIVITY_ID,
APPLICATION_NAME,
SNAPSHOT_NAME,
PROCESS_NAME,
ACTIVITY_NAME,
CASE_REFERENCE_NO,
TASK_ID,
TASK_COUNTER,
BUSINESS_TXN_REF_NO,
TIME_STAMP,
USER_ACTION,
USER_NAME,
LOG_CATEGORY,
COMMENTS,
PROCESS_GUID,
BIZREF2,
BIZREF3,
BIZREF4,
BIZREF5,
TECHCOMMENTS,
BIZPROCESSID,
ACTIONSTATUS,
BPMPROCESSNAME,
BPMACTIVITYNAME,
BIZTASKID,
SYSTIMESTAMP
--ARCHIVE_DATETIME
FROM
PROCESSING_HISTORY
WHERE
BUSINESS_TXN_REF_NO
IN(SELECT MSG_ID FROM EXP_RES_CASE ER WHERE EXP_RES_CASE_REF =
i.EXP_RES_CASE_REF
AND MSG_ID NOT IN(SELECT BUSINESS_TRANSACTION_REF_NO From DBCOE_SCHEDULER DS
Where DS.SCHEDULE_FLAG IN('SCHEDULED','IN-PROGRESS')));
-- Deletion of
Records from main table "ESPGO_CASE_INPUT" after moving to Archival
table "ESPGO_CASE_INPUT_ARCHIVE"
DELETE FROM PROCESSING_HISTORY
WHERE
BUSINESS_TXN_REF_NO IN(SELECT MSG_ID
FROM EXP_RES_CASE ER WHERE EXP_RES_CASE_REF = i.EXP_RES_CASE_REF
AND MSG_ID NOT IN(SELECT BUSINESS_TRANSACTION_REF_NO From DBCOE_SCHEDULER DS
Where DS.SCHEDULE_FLAG IN('SCHEDULED','IN-PROGRESS')));
-- 8. TABLE = DBCOE_SCHEDULER
/*
Action : ARCHIVE
Criteria :
1.
SCHEDULE_FLAG IN ('COMPLETED','USER-CANCELLED','SKIPPED')
2. All
records with BUSINESS_TRANSACTION_REF_NO = ESP_RES_CASE.MSG_ID
Period : 1 WEEK (7 Days)
Archival Table :
DBCOE_SCHEDULER_ARCHIVE
*/
v_table_name :=
'DBCOE_SCHEDULER_ARCHIVE';
DELETE FROM
DBCOE_SCHEDULER_ARCHIVE
WHERE BUSINESS_TRANSACTION_REF_NO
IN
(SELECT
MSG_ID
FROM EXP_RES_CASE
WHERE EXP_RES_CASE_REF = i.EXP_RES_CASE_REF
)
AND SCHEDULE_FLAG IN
('COMPLETED','USER-CANCELLED','SKIPPED');
-- Moving data to Archival
table "DBCOE_SCHEDULER_ARCHIVE"
INSERT INTO
DBCOE_SCHEDULER_ARCHIVE
(
UNIQUE_ID,
ACRONYM_NAME,
BUSINESS_TRANSACTION_REF_NO,
SCHEDULE_TIME,
SCHEDULE_FLAG,
SERVICE_NAME,
SERVICE_TYPE,
SCHEDULER_ID,
PRIORITY,
SCHEDULE_STARTTIME,
SCHEDULE_ENDTIME,
INPUT_DATA,
REPEATABLE,
AUTO_ROLL_OVER,
BIZPROCESSID,
BIZTASKID,
INSTANCEID,
TASKID,
HANDLER1,
HANDLER2,
TIMEZONE,
MAXRETRYCOUNT,
CURRENTCOUNT,
REPEATAFTERXMINS,
HANDLER3,
HANDLER4,
UNIQUEBIZREF,
PROCESSNAME,
ACTIVITYNAME,
AUTOROLLOVERLINKAGE,
NEXTAUTOROLLOVERJOBID,
CREATED_TSMP,
LAST_MOD_TSMP,
ARCHIVE_DATETIME
)
SELECT
UNIQUE_ID,
ACRONYM_NAME,
BUSINESS_TRANSACTION_REF_NO,
SCHEDULE_TIME,
SCHEDULE_FLAG,
SERVICE_NAME,
SERVICE_TYPE,
SCHEDULER_ID,
PRIORITY,
SCHEDULE_STARTTIME,
SCHEDULE_ENDTIME,
INPUT_DATA,
REPEATABLE,
AUTO_ROLL_OVER,
BIZPROCESSID,
BIZTASKID,
INSTANCEID,
TASKID,
HANDLER1,
HANDLER2,
TIMEZONE,
MAXRETRYCOUNT,
CURRENTCOUNT,
REPEATAFTERXMINS,
HANDLER3,
HANDLER4,
UNIQUEBIZREF,
PROCESSNAME,
ACTIVITYNAME,
AUTOROLLOVERLINKAGE,
NEXTAUTOROLLOVERJOBID,
CREATED_TSMP,
LAST_MOD_TSMP,
SYSTIMESTAMP
--ARCHIVE_DATETIME
FROM DBCOE_SCHEDULER
WHERE BUSINESS_TRANSACTION_REF_NO
IN
(SELECT MSG_ID
FROM EXP_RES_CASE
WHERE EXP_RES_CASE_REF = i.EXP_RES_CASE_REF /*Changed on 31st Jan 2017*/
)
AND SCHEDULE_FLAG IN
('COMPLETED','USER-CANCELLED','SKIPPED') ; /*Changed on 31st Jan 2017*/
-- Deletion of Records from
main table "DBCOE_SCHEDULER" after moving to Archival table
"EXP_RES_CASE_ARCHIVE"
DELETE FROM
DBCOE_SCHEDULER
WHERE
BUSINESS_TRANSACTION_REF_NO IN
(SELECT
MSG_ID
FROM EXP_RES_CASE
WHERE EXP_RES_CASE_REF = i.EXP_RES_CASE_REF
)
AND SCHEDULE_FLAG IN
('COMPLETED','USER-CANCELLED','SKIPPED'); /*Changed on 31st Jan 2017*/
--7. TABLE = EXP_RES_CASE
/*
Action : ARCHIVE
Criteria : Archive
the same Exception Case Reference Number (EXP_RES_CASE_REF) of above Parent
table matching the Criteria as mentioned above
Archival Table :
EXP_RES_CASE_ARCHIVE
*/
v_table_name :=
'EXP_RES_CASE_ARCHIVE';
-- Moving data (EXP_RES_CASE_REF)
to Archival table "EXP_RES_CASE_ARCHIVE"
DELETE FROM
EXP_RES_CASE_ARCHIVE WHERE EXP_RES_CASE_REF = i.EXP_RES_CASE_REF;
INSERT INTO
EXP_RES_CASE_ARCHIVE
(
EXP_RES_CASE_REF,
RES_ID,
BPM_PROC_INST_ID,
RES_CASE_STATUS,
MSG_ID,
REA_CDE,
CASE_CRE_DATE,
LAST_MOD_USER_ID,
LAST_MOD_TSMP,
COR_VAL,
TASK_UID,
ARCHIVE_DATETIME
)
SELECT
EXP_RES_CASE_REF,
RES_ID,
BPM_PROC_INST_ID,
RES_CASE_STATUS,
MSG_ID,
REA_CDE,
CASE_CRE_DATE,
LAST_MOD_USER_ID,
LAST_MOD_TSMP,
COR_VAL,
TASK_UID,
SYSTIMESTAMP
--ARCHIVE_DATETIME
FROM EXP_RES_CASE WHERE
EXP_RES_CASE_REF = i.EXP_RES_CASE_REF;
-- Deletion of Records from
main table "EXP_RES_CASE" after moving to Archival table
"EXP_RES_CASE_ARCHIVE"
DELETE FROM EXP_RES_CASE
WHERE EXP_RES_CASE_REF =
i.EXP_RES_CASE_REF;
COMMIT;
r := r + 1;
END LOOP;
-- Entry to Archival Audit log -
Completion of
EXP_RES_CASE_TRADE_HIS,EXP_RES_CASE_TRADE,ESPGO_CASE_INPUT,EXP_RES_CASE tables
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'ARCHIVAL PROCESS COMPLETED FOR
TABLES
EXP_RES_CASE_TRADE_HIS,EXP_RES_CASE_TRADE,ESPGO_CASE_INPUT,PROCESSING_HISTORY,EXP_RES_CASE'
FROM DUAL;
COMMIT;
-- Entry to Archival Audit log -
Number of Records Archived
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'No.
of Records Archived : ' ||r FROM DUAL;
COMMIT;
-- 9. TABLE = ESP_REF_NUM
/*
Action : ARCHIVE
Criteria :
1. REF_NUM_PARAM1 = 'ESP' AND
REF_NUM_PARAM2 = 'GO'
2. extract(day from (cast(systimestamp
at time zone 'GMT' as timestamp(6)) - LAST_MOD_TSMP)) > 7
Period : 1 WEEK (7 Days)
Archival Table
: ESP_REF_NUM_ARCHIVE
*/
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'ARCHIVAL PROCESS STARTED FOR TABLE
ESP_REF_NUM' FROM DUAL;
COMMIT;
v_table_name :=
'ESP_REF_NUM_ARCHIVE';
-- FOR LOOP
FOR i IN
(
SELECT REF_NUM_PARAM3
FROM ESP_REF_NUM
WHERE REF_NUM_PARAM1 =
'ESP' AND REF_NUM_PARAM2 = 'GO'
AND extract(day from
(cast(systimestamp at time zone 'GMT' as timestamp(6)) - LAST_MOD_TSMP))
> NumberofDays --14 (PREVIOUS) -- changed on 07-NOV-2016 as discussed
on 03-Nov with Team, refer my comments in Jira on 03-Nov
)
LOOP
DELETE FROM ESP_REF_NUM_ARCHIVE
WHERE REF_NUM_PARAM3 = i.REF_NUM_PARAM3;
-- Moving data to Archival
table "ESP_REF_NUM_ARCHIVE"
INSERT INTO
ESP_REF_NUM_ARCHIVE
(
REF_NUM_TYP,
REF_NUM_PARAM1,
REF_NUM_PARAM2,
REF_NUM_PARAM3,
LAST_GEN_SEQ_NUM,
LAST_MOD_TSMP,
ARCHIVE_DATETIME
)
SELECT
REF_NUM_TYP,
REF_NUM_PARAM1,
REF_NUM_PARAM2,
REF_NUM_PARAM3,
LAST_GEN_SEQ_NUM,
LAST_MOD_TSMP,
SYSTIMESTAMP
--ARCHIVE_DATETIME
FROM ESP_REF_NUM WHERE
REF_NUM_PARAM3 = i.REF_NUM_PARAM3;
-- Deletion of Records from
main table "ESP_REF_NUM" after moving to Archival table
"ESP_REF_NUM_ARCHIVE"
DELETE FROM ESP_REF_NUM
WHERE REF_NUM_PARAM3 = i.REF_NUM_PARAM3;
COMMIT;
r := r + 1;
END LOOP;
-- Entry to Archival Audit log -
Completion of ESP_REF_NUM
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'ARCHIVAL PROCESS COMPLETED FOR TABLE
ESP_REF_NUM' FROM DUAL;
COMMIT;
-- Entry to Archival Audit log -
Number of Records Archived
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'No. of Records Archived : '
||r FROM DUAL;
COMMIT;
-- Entry to Archival Audit log -
Archival Process Completed
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'ESPGO ARCHIVAL PROCESS COMPLETED'
FROM DUAL;
COMMIT;
-- POST-ARCHIVAL
-- Record count of Main tables
After Archival
Select count(*) into
rec_cnt21 from ESP_TP_CACHE;
Select count(*) into
rec_cnt22 from ESP_REPORTING_TP_CACHE;
Select count(*) into
rec_cnt23 from EXP_RES_CASE_TRADE_HIS;
Select count(*) into
rec_cnt24 from EXP_RES_CASE_TRADE;
Select count(*) into
rec_cnt25 from
EXP_RES_CASE;
Select count(*) into
rec_cnt26 from ESPGO_CASE_INPUT;
Select count(*) into
rec_cnt27 from DBCOE_SCHEDULER;
Select count(*) into
rec_cnt28 from ESP_REF_NUM;
Select count(*) into
rec_cnt29 from PROCESSING_HISTORY;
-- Entry to Archival Audit
log
-- RECORD COUNT OF MAIN
TABLES AFTER ARCHIVAL
-- 1.ESP_TP_CACHE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO.
OF RECORDS IN ESP_TP_CACHE TABLE AFTER ARCHIVAL = ' || rec_cnt21 FROM DUAL;
COMMIT;
-- 2. ESP_REPORTING_TP_CACHE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO.
OF RECORDS IN ESP_REPORTING_TP_CACHE TABLE AFTER ARCHIVAL = ' || rec_cnt22 FROM
DUAL;
COMMIT;
--3.EXP_RES_CASE_TRADE_HIS
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
EXP_RES_CASE_TRADE_HIS TABLE AFTER ARCHIVAL = ' || rec_cnt23 FROM DUAL;
COMMIT;
--4.EXP_RES_CASE_TRADE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN EXP_RES_CASE_TRADE
TABLE AFTER ARCHIVAL = ' || rec_cnt24 FROM DUAL;
COMMIT;
-- 5.EXP_RES_CASE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN EXP_RES_CASE TABLE
AFTER ARCHIVAL = ' || rec_cnt25 FROM DUAL;
COMMIT;
-- 6. ESPGO_CASE_INPUT
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN ESPGO_CASE_INPUT
TABLE AFTER ARCHIVAL = ' || rec_cnt26 FROM DUAL;
COMMIT;
--7.DBCOE_SCHEDULER
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN DBCOE_SCHEDULER
TABLE AFTER ARCHIVAL = ' || rec_cnt27 FROM DUAL;
COMMIT;
--8. ESP_REF_NUM
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN ESP_REF_NUM TABLE
AFTER ARCHIVAL = ' || rec_cnt28 FROM DUAL;
COMMIT;
--9. PROCESSING_HISTORY
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN PROCESSING_HISTORY
TABLE AFTER ARCHIVAL = ' || rec_cnt29 FROM DUAL;
COMMIT;
-- Record count of Archival
tables After Archival
Select count(*) into
rec_cnt31 from ESPGO_TP_CACHE_ARCHIVE;
Select count(*) into
rec_cnt32 from ESPGO_REPRTNG_TP_CACHE_ARCHIVE;
Select count(*) into
rec_cnt33 from EXP_RES_CASE_TRADE_HIS_ARCHIVE;
Select count(*) into
rec_cnt34 from EXP_RES_CASE_ARCHIVE;
Select count(*) into
rec_cnt35 from ESPGO_CASE_INPUT_ARCHIVE;
Select count(*) into
rec_cnt36 from DBCOE_SCHEDULER_ARCHIVE;
Select count(*) into
rec_cnt37 from ESP_REF_NUM_ARCHIVE;
Select count(*) into
rec_cnt38 from PROCESSING_HISTORY_ARCHIVE;
-- RECORD COUNT OF ARCHIVAL
TABLES BEFORE ARCHIVAL
--
1.ESPGO_TP_CACHE_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
ESPGO_TP_CACHE_ARCHIVE TABLE AFTER ARCHIVAL = ' || rec_cnt31 FROM DUAL;
COMMIT;
-- 2.
ESPGO_REPRTNG_TP_CACHE_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
ESPGO_REPRTNG_TP_CACHE_ARCHIVE TABLE AFTER ARCHIVAL = ' || rec_cnt32 FROM DUAL;
COMMIT;
--3.EXP_RES_CASE_TRADE_HIS_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO.
OF RECORDS IN EXP_RES_CASE_TRADE_HIS_ARCHIVE TABLE AFTER ARCHIVAL = ' ||
rec_cnt33 FROM DUAL;
COMMIT;
--4.EXP_RES_CASE_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
EXP_RES_CASE_ARCHIVE TABLE AFTER ARCHIVAL = ' || rec_cnt34 FROM DUAL;
COMMIT;
--
5.ESPGO_CASE_INPUT_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
ESPGO_CASE_INPUT_ARCHIVE TABLE AFTER ARCHIVAL = ' || rec_cnt35 FROM DUAL;
COMMIT;
-- 6. DBCOE_SCHEDULER_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
DBCOE_SCHEDULER_ARCHIVE TABLE AFTER ARCHIVAL = ' || rec_cnt36 FROM DUAL;
COMMIT;
--7.ESP_REF_NUM_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
ESP_REF_NUM_ARCHIVE TABLE AFTER ARCHIVAL = ' || rec_cnt37 FROM DUAL;
COMMIT;
--8.PROCESSING_HISTORY_ARCHIVE
INSERT INTO ESPGO_ARCHIVAL_LOG
(LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'NO. OF RECORDS IN
PROCESSING_HISTORY_ARCHIVE TABLE AFTER ARCHIVAL = ' || rec_cnt38 FROM DUAL;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
err_code :=
SQLCODE;
err_msg :=
SUBSTR(SQLERRM, 1, 200);
INSERT INTO
ESPGO_ARCHIVAL_LOG (LOG_ID,LOG_DATE,LOG_MESSAGE)
SELECT--ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'ERROR:
' || err_code ||' '||err_msg
ESPGO_ARCHIVE_SEQ_ID.NEXTVAL,SYSTIMESTAMP,'ERROR has encountered in table '||
v_table_name ||' '||'- ERROR - '||err_msg FROM DUAL;
COMMIT;
--raise_application_error(-20001,'An
error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
--raise_application_error(-20001,'An
error was encountered in table '|| v_table_name ||' '||SQLCODE||' -ERROR-
'||SQLERRM);
Raise_Application_Error(-20001,'An error was encountered in table '||
v_table_name ||' '||' - ERROR - '||SQLERRM);
END ESPGO_ARCHIVAL;
No comments:
Post a Comment