|
--
|
|
-- NOTICE: Make sure no pending DML changes left uncommitted/rollback.
|
|
-- DDL will commit pending DML changes.
|
|
--
|
|
|
|
-- 00. DDL full table backup
|
|
create table mst_investment_accounts_bkp_2026mmdd_xxx as select * from mst_investment_accounts;
|
|
create table trx_ut_transactions_bkp_2026mmdd_xxx as select * from trx_ut_transactions;
|
|
|
|
-- 01. DQL capture data before patches
|
|
select one_time_subs_flag, pledge_status, count(*)
|
|
from mst_investment_accounts
|
|
where one_time_subs_flag = -1
|
|
group by one_time_subs_flag, pledge_status;
|
|
select inv_account_id, inv_account_no, one_time_subs_flag, pledge_status
|
|
from mst_investment_accounts
|
|
where one_time_subs_flag = -1;
|
|
|
|
select i.one_time_subs_flag, t.trx_type, t.redemption_type, count(*)
|
|
from trx_ut_transactions t join mst_investment_accounts i on t.inv_account_id = i.inv_account_id
|
|
where i.one_time_subs_flag = -1 and t.trx_type = 20 and t.trx_no like '25-%'
|
|
group by i.one_time_subs_flag, t.trx_type, t.redemption_type;
|
|
select i.one_time_subs_flag, t.trx_id, t.trx_no, t.trx_type, t.redemption_type
|
|
from trx_ut_transactions t join mst_investment_accounts i on t.inv_account_id = i.inv_account_id
|
|
where i.one_time_subs_flag = -1 and t.trx_type = 20 and t.trx_no like '25-%';
|
|
|
|
-- 02. DML update investment accounts pledge status to 'Yes' (-1)
|
|
update mst_investment_accounts
|
|
set pledge_status = -1
|
|
where one_time_subs_flag = -1;
|
|
|
|
-- 03. DML update transactions redemption type flag to partial unit (1)
|
|
update trx_ut_transactions t
|
|
set t.redemption_type = 1
|
|
where t.trx_type = 20 and t.trx_no like '25-%'
|
|
and exists (
|
|
select 1
|
|
from mst_investment_accounts i
|
|
where i.one_time_subs_flag = -1
|
|
and i.inv_account_id = t.inv_account_id
|
|
)
|
|
and t.product_id not in (
|
|
select p.product_id
|
|
from mst_ut_products p
|
|
where p.issuer_product_code = 'ULI36'
|
|
)
|
|
;
|
|
|
|
-- 04 DQL capture data after patches
|
|
select one_time_subs_flag, pledge_status, count(*)
|
|
from mst_investment_accounts
|
|
where one_time_subs_flag = -1
|
|
group by one_time_subs_flag, pledge_status;
|
|
select inv_account_id, inv_account_no, one_time_subs_flag, pledge_status
|
|
from mst_investment_accounts
|
|
where one_time_subs_flag = -1;
|
|
|
|
select i.one_time_subs_flag, t.trx_type, t.redemption_type, count(*)
|
|
from trx_ut_transactions t join mst_investment_accounts i on t.inv_account_id = i.inv_account_id
|
|
where i.one_time_subs_flag = -1 and t.trx_type = 20 and t.trx_no like '25-%'
|
|
group by i.one_time_subs_flag, t.trx_type, t.redemption_type;
|
|
select i.one_time_subs_flag, t.trx_id, t.trx_no, t.trx_type, t.redemption_type
|
|
from trx_ut_transactions t join mst_investment_accounts i on t.inv_account_id = i.inv_account_id
|
|
where i.one_time_subs_flag = -1 and t.trx_type = 20 and t.trx_no like '25-%';
|
|
|
|
-- 05 rollback or commit
|
|
-- rollback;
|
|
--or
|
|
-- commit;
|