SELECT a.val
FROM tb_prd_prd_inst_fea_999 a
WHERE a.prd_fea_type_id = 1054
AND a.prd_inst_id = prd_inst_id
AND rownum = 1
SELECT site_id
FROM tb_pty_empee
WHERE empee_id = accept_empee_id
AND rownum = 1
select a.prd_inst_id,a.latn_id,a.service_nbr,a.prd_inst_stas_id,a.pay_cust_id,a.prd_inst_name, b.code,b.code_name,
nvl(a.user_account,a.service_nbr) AS User_Account,a.accept_empee_id
from tb_prd_prd_inst_999 a,tb_pty_code b where a.if_prepay=b.code and b.code_type = 'IF_PREPAY'
and a.prd_inst_stas_id in(1001,1201,1202,1203) and a.prd_id in ('5000','5221','5100')
create or replace procedure pro_user_pay_model_upload
is
v_prd_inst_id tb_prd_prd_inst_999.prd_inst_id%type;
v_latn_id tb_prd_prd_inst_999.latn_id%type;
v_service_nbr tb_prd_prd_inst_999.service_nbr%type;
v_inst_stas_id tb_prd_prd_inst_999.prd_inst_stas_id%type;
v_pay_cust_id tb_prd_prd_inst_999.pay_cust_id%type;
v_prd_inst_name tb_prd_prd_inst_999.prd_inst_name%type;
v_code tb_pty_code.code%type;
v_code_name tb_pty_code.code_name%type;
v_user_account varchar2(100);
v_accept_empee_id tb_prd_prd_inst_999.accept_empee_id%type;
imsi_number number(10);
mid_number number(10);
mid_service_nbr varchar2(100);
mid_item_value varchar2(100);
imsi_code varchar2(100);
mid_empee_site varchar2(100);
temp_num number(10);
site_id_num number(10);
--定义游标
cursor paymodel is
select a.prd_inst_id,
a.latn_id,
a.service_nbr,
a.prd_inst_stas_id,
a.pay_cust_id,
a.prd_inst_name,
b.code,
b.code_name,
nvl(a.user_account,a.service_nbr) as user_account,
a.accept_empee_id
from tb_prd_prd_inst_999 a,
tb_pty_code b
where a.if_prepay = b.code
and a.prd_inst_stas_id in(1001,1201,1202,1203)
and a.prd_id in ('5000','5221','5100')
and b.code_type = 'if_prepay';
begin
--打开游标
open paymodel ;
loop
--提取游标第一行数据
fetch paymodel into v_prd_inst_id, v_latn_id, v_service_nbr,
v_inst_stas_id,v_pay_cust_id,v_prd_inst_name,
v_code,v_code_name,v_user_account,
v_accept_empee_id;
exit when paymodel%notfound;
--imsi 查询
select count(*) into imsi_number
from snuimcard a
where exists
(
select 1
from tb_prd_prd_inst_fea_999 b
where a.iccidcode = b.val
and a.prd_fea_type_id = 1054
and a.prd_inst_id = v_prd_inst_id
);
select count(*) into mid_number
from tb_user_pay_model_middle a
where (a.productnbr = v_service_nbr or a.prodnbr = v_service_nbr);
select count(*) into site_id_num
from tb_sys_site b
where exists
(
select 1
from tb_pty_empee c
where b.site_id = c.site_id
and c.empee_id = v_accept_empee_id
) ;
if (imsi_number > 0 and site_id_num > 0) then
select imsicode into imsi_code
from snuimcard v
where exists
(
select 1
from tb_prd_prd_inst_fea_999 a
where v.iccidcode = a.al
and a.prd_fea_type_id = 1054
and a.prd_inst_id = v_prd_inst_id
) ;
select b.name into mid_empee_site
from tb_sys_site b
where exists
(
select 1
from tb_pty_empee n
where b.site_id = n.site_id
and n.empee_id = v_accept_empee_id
) ;
if(mid_number > 0) then --在中间表中找到数据
--1、判断付费模式状态是否一致
select productnbr,
itemvalue
into mid_service_nbr ,
mid_item_value
from tb_user_pay_model_middle a
where a.productnbr = v_service_nbr
or
a.prodnbr = v_service_nbr
and rownum = 1;
if (v_code <> mid_item_value) then
update tb_user_pay_model_middle k
set k.itemvalue = v_code,
k.ordertypecd = '216',
k.pay_type=2
where k.productnbr = v_service_nbr;
end if;
else
insert into tb_user_pay_model_middle
(
pay_inst_id,
pay_type,
pay_send_state,
bpmprodtype,
ordertypecd,
bizeventnbr,
prodcode,
oldprodcode,
productnbr,
oldproductnbr,
custname,
useracctnbr,
useracctdomain,
imsi,
oldimsi,
prodstatuscd,
oldstatuscd,
latnid,
cityname,
itemaction,
itemid,
itemname,
itemvalue,
valuename,
oldvalue,
oldvaluename,
empeeid,
empeesite
)
values
(
se_tb_user_pay_model_upload.nextval,
'1',
'100',
'10',
'100',
prd_inst_id,
service_nbr,
'',
service_nbr,
'',
prd_inst_name,
user_account,
'v_useracctdomain',
imsi_code,
'',
inst_stas_id,
'',
latn_id,
'',
'10',
'320001',
'用户付费模式',
code,
'',
'',
'',
accept_empee_id,
mid_empee_site
);
commit;
end if;
end if;
end loop;
--关闭游标
close paymodel;
end;
create or replace procedure pro_user_pay_model_upload
is
v_prd_inst_id tb_prd_prd_inst_999.prd_inst_id%type;
v_latn_id tb_prd_prd_inst_999.latn_id%type;
v_service_nbr tb_prd_prd_inst_999.service_nbr%type;
v_inst_stas_id tb_prd_prd_inst_999.prd_inst_stas_id%type;
v_pay_cust_id tb_prd_prd_inst_999.pay_cust_id%type;
v_prd_inst_name tb_prd_prd_inst_999.prd_inst_name%type;
v_code tb_pty_code.code%type;
v_code_name tb_pty_code.code_name%type;
v_user_account varchar2(100);
v_accept_empee_id tb_prd_prd_inst_999.accept_empee_id%type;
imsi_number number(10);
mid_number number(10);
mid_service_nbr varchar2(100);
mid_item_value varchar2(100);
imsi_code varchar2(100);
mid_empee_site varchar2(100);
temp_num number(10);
site_id_num number(10);
--定义游标
cursor paymodel is
select a.prd_inst_id,
a.latn_id,
a.service_nbr,
a.prd_inst_stas_id,
a.pay_cust_id,
a.prd_inst_name,
b.code,
b.code_name,
nvl(a.user_account,a.service_nbr) as user_account,
a.accept_empee_id
from tb_prd_prd_inst_999 a,
tb_pty_code b
where a.if_prepay = b.code
and a.prd_inst_stas_id in(1001,1201,1202,1203)
and a.prd_id in ('5000','5221','5100')
and b.code_type = 'if_prepay';
begin
--打开游标
open paymodel ;
loop
--提取游标第一行数据
fetch paymodel into v_prd_inst_id, v_latn_id, v_service_nbr,
v_inst_stas_id,v_pay_cust_id,v_prd_inst_name,
v_code,v_code_name,v_user_account,
v_accept_empee_id;
exit when paymodel%notfound;
--imsi 查询
select count(*) into imsi_number
from snuimcard a
where exists
(
select 1
from tb_prd_prd_inst_fea_999 b
where a.iccidcode = b.val
and a.prd_fea_type_id = 1054
and a.prd_inst_id = v_prd_inst_id
);
select count(*) into mid_number
from tb_user_pay_model_middle a
where (a.productnbr = v_service_nbr or a.prodnbr = v_service_nbr);
select count(*) into site_id_num
from tb_sys_site b
where exists
(
select 1
from tb_pty_empee c
where b.site_id = c.site_id
and c.empee_id = v_accept_empee_id
) ;
if (imsi_number > 0 and site_id_num > 0) then
select imsicode into imsi_code
from snuimcard v
where exists
(
select 1
from tb_prd_prd_inst_fea_999 a
where v.iccidcode = a.al
and a.prd_fea_type_id = 1054
and a.prd_inst_id = v_prd_inst_id
) ;
select b.name into mid_empee_site
from tb_sys_site b
where exists
(
select 1
from tb_pty_empee n
where b.site_id = n.site_id
and n.empee_id = v_accept_empee_id
) ;
if(mid_number > 0) then --在中间表中找到数据
--1、判断付费模式状态是否一致
select productnbr,
itemvalue
into mid_service_nbr ,
mid_item_value
from tb_user_pay_model_middle a
where a.productnbr = v_service_nbr
or
a.prodnbr = v_service_nbr
and rownum = 1;
if (v_code <> mid_item_value) then
update tb_user_pay_model_middle k
set k.itemvalue = v_code,
k.ordertypecd = '216',
k.pay_type=2
where k.productnbr = v_service_nbr;
end if;
else
insert into tb_user_pay_model_middle
(
pay_inst_id,
pay_type,
pay_send_state,
bpmprodtype,
ordertypecd,
bizeventnbr,
prodcode,
oldprodcode,
productnbr,
oldproductnbr,
custname,
useracctnbr,
useracctdomain,
imsi,
oldimsi,
prodstatuscd,
oldstatuscd,
latnid,
cityname,
itemaction,
itemid,
itemname,
itemvalue,
valuename,
oldvalue,
oldvaluename,
empeeid,
empeesite
)
values
(
se_tb_user_pay_model_upload.nextval,
'1',
'100',
'10',
'100',
v_prd_inst_id,
v_service_nbr,
'',
v_service_nbr,
'',
v_prd_inst_name,
v_user_account,
'v_useracctdomain',
v_imsi_code,
'',
v_inst_stas_id,
'',
v_latn_id,
'',
'10',
'320001',
'用户付费模式',
v_code,
'',
'',
'',
v_accept_empee_id,
v_mid_empee_site
);
commit;
end if;
end if;
end loop;
--关闭游标
close paymodel;
end;
