declare
type subs_mobile_no_arr is table of trt_subs_GD_1li_wap_S.subs_mobile_no%type index by pls_integer;
type icp_id_arr is table of trt_subs_GD_1li_wap_S.icp_id%type index by pls_integer;
type icp_code_arr is table of trt_subs_GD_1li_wap_S.icp_code%type index by pls_integer;
type channel_id_arr is table of trt_subs_GD_1li_wap_S.channel_id%type index by pls_integer;
type serv_id_arr is table of trt_subs_GD_1li_wap_S.serv_id%type index by pls_integer;
type brand_id_arr is table of trt_subs_GD_1li_wap_S.brand_id%type index by pls_integer;
type fee_mobile_no_arr is table of trt_subs_GD_1li_wap_S.fee_mobile_no%type index by pls_integer;
type action_id_arr is table of trt_subs_GD_1li_wap_S.action_id%type index by pls_integer;
type crt_his_date_arr is table of trt_subs_GD_1li_wap_S.crt_his_date%type index by pls_integer;
type city_id_arr is table of trt_subs_GD_1li_wap_S.city_id%type index by pls_integer;
type attr_arr is table of trt_subs_GD_1li_wap_S.attr%type index by pls_integer;
type srh_channel_id_arr is table of trt_subs_GD_1li_wap_S.srh_channel_id%type index by pls_integer;
arr_subs_mobile_no subs_mobile_no_arr;
arr_icp_id icp_id_arr;
arr_icp_code icp_code_arr;
arr_channel_id channel_id_arr;
arr_serv_id serv_id_arr;
arr_brand_id brand_id_arr;
arr_fee_mobile_no fee_mobile_no_arr;
arr_action_id action_id_arr;
arr_crt_his_date crt_his_date_arr;
arr_city_id city_id_arr;
arr_attr attr_arr;
arr_srh_channel_id srh_channel_id_arr;
begin
select subs_mobile_no,
icp_id,
icp_code,
channel_id,
serv_id,
brand_id,
fee_mobile_no,
decode(action_id, 'SCS', 'C', 'ADS', 'D') action_id,
crt_his_date,
city_id,
attr,
srh_channel_id bulk collect
into arr_subs_mobile_no,
arr_icp_id,
arr_icp_code,
arr_channel_id,
arr_serv_id,
arr_brand_id,
arr_fee_mobile_no,
arr_action_id,
arr_crt_his_date,
arr_city_id,
arr_attr,
arr_srh_channel_id
from trt_subs_GD_1li_wap_S
where action_id = 'SCS'
or action_id = 'ADS';
if arr_subs_mobile_no.last > 0 then
arr_srh_channel_id srh_channel_id_arr;
begin
select subs_mobile_no,
icp_id,
icp_code,
channel_id,
serv_id,
brand_id,
fee_mobile_no,
decode(action_id, 'SCS', 'C', 'ADS', 'D') action_id,
crt_his_date,
city_id,
attr,
srh_channel_id bulk collect
into arr_subs_mobile_no,
arr_icp_id,
arr_icp_code,
arr_channel_id,
arr_serv_id,
arr_brand_id,
arr_fee_mobile_no,
arr_action_id,
arr_crt_his_date,
arr_city_id,
arr_attr,
arr_srh_channel_id
from trt_subs_GD_1li_wap_S
where action_id = 'SCS'
or action_id = 'ADS';
if arr_subs_mobile_no.last > 0 then
forall i in arr_subs_mobile_no.first .. arr_subs_mobile_no.last
update f_wap_subs_GD_p_s c
set c.city_id = arr_city_id(i),
c.icp_id = arr_icp_id(i),
c.icp_code = arr_icp_code(i),
c.subs_cancel_channel_id = arr_channel_id(i),
c.brand_id = arr_brand_id(i),
c.subs_status_id = arr_action_id(i),
c.fee_mobile_no = arr_fee_mobile_no(i),
c.usubs_date = arr_crt_his_date(i),
c.usubs_day = to_char(arr_crt_his_date(i),
'YYYYMMDD'),
c.idx_day = to_char(arr_crt_his_date(i),
'YYYYMMDD'),
c.is_subs_valid = 0,
c.flow_time = sysdate,
c.srh_channel_id = arr_srh_channel_id(i)
where subs_mobile_no = arr_subs_mobile_no(i)
and serv_id = arr_serv_id(i)
and is_subs_valid = 1;
end if;
end;