1

create table jqq_wj_zj_huaxiang_yxcj_fl as
select distinct a.fl from jqq_wj_zj_huaxiang_yxcj a;

select ‘’
from user_tab_columns a
where a.TABLE_NAME = ‘YXCJ_FL’
order by a.COLUMN_ID;

create table yxcj_fl as
with temp0 as(
select *
from jqq_wj_zj_huaxiang_yxcj a
where a.fzxid = 110101
and a.lx = 3
),
temp1 as(
select *
from jqq_wj_zj_huaxiang_yxcj b
where exists (select * from temp0 c where b.orgaid = c.orgaid)
)
select t.orgaid,

   t.organame,
   nvl(max(case when t.fl = '用户拓展能力' then t.lx else 0 end), 0) fl_yh,
   nvl(max(case when t.fl = '宽带拓展能力' then t.lx else 0 end), 0) fl_kd,
   nvl(max(case when t.fl = '终端销售能力' then t.lx else 0 end), 0) fl_zd,
   nvl(max(case when t.fl = '全球通归位能力' then t.lx else 0 end), 0) fl_qqt,
   nvl(max(case when t.fl = '终端拉新' then t.lx else 0 end), 0) fl_zdlx,
   nvl(max(case when t.fl = '家庭拉新' then t.lx else 0 end), 0) fl_kdlx,
   fn_parase_rate(nvl(max(case when t.fl = '用户拓展能力' then t.rate_wc else 0 end), 0)) rate_wc_yh,
   fn_parase_rate(nvl(max(case when t.fl = '宽带拓展能力' then t.rate_wc else 0 end), 0)) rate_wc_kd,
   fn_parase_rate(nvl(max(case when t.fl = '终端销售能力' then t.rate_wc else 0 end), 0)) rate_wc_zd,
   nvl(max(case when t.fl = '全球通归位能力' then t.rate_wc else 0 end), 0) rate_wc_qqt,
   fn_parase_rate(nvl(max(case when t.fl = '终端拉新' then t.rate_wc else 0 end), 0)) rate_wc_zdlx,
   fn_parase_rate(nvl(max(case when t.fl = '家庭拉新' then t.rate_wc else 0 end), 0)) rate_wc_kdlx

from temp1 t
group by t.orgaid, t.organame
order by t.orgaid;
/

create or replace type t_yxcj_fl as object(
orgaid VARCHAR2(40),
organame VARCHAR2(200),
fl_yh NUMBER,
fl_kd NUMBER,
fl_zd NUMBER,
fl_qqt NUMBER,
fl_zdlx NUMBER,
fl_kdlx NUMBER,
rate_wc_yh VARCHAR2(4000),
rate_wc_kd VARCHAR2(4000),
rate_wc_zd VARCHAR2(4000),
rate_wc_qqt NUMBER,
rate_wc_zdlx VARCHAR2(4000),
rate_wc_kdlx VARCHAR2(4000)
);
/
create or replace type t_yxcj_fl_table as table of t_yxcj_fl;
/

create or replace function fn_get_yxcj_fl_list(v_fzxid in varchar2 default null)
return t_yxcj_fl_table
is
v_yxcj_fl_list t_yxcj_fl_table := t_yxcj_fl_table();
begin
select t_yxcj_fl
(orgaid, organame, fl_yh, fl_kd, fl_zd, fl_qqt, fl_zdlx, fl_kdlx, rate_wc_yh,
rate_wc_kd, rate_wc_zd, rate_wc_qqt, rate_wc_zdlx, rate_wc_kdlx)
bulk collect into v_yxcj_fl_list
from (with temp0 as(
select *
from jqq_wj_zj_huaxiang_yxcj a
where a.fzxid = v_fzxid
and a.lx = 3
),
temp1 as(
select *
from jqq_wj_zj_huaxiang_yxcj b
where exists (select * from temp0 c where b.orgaid = c.orgaid)
)
select t.orgaid,
t.organame,
nvl(max(case when t.fl = ‘用户拓展能力’ then t.lx else 0 end), 0) fl_yh,
nvl(max(case when t.fl = ‘宽带拓展能力’ then t.lx else 0 end), 0) fl_kd,
nvl(max(case when t.fl = ‘终端销售能力’ then t.lx else 0 end), 0) fl_zd,
nvl(max(case when t.fl = ‘全球通归位能力’ then t.lx else 0 end), 0) fl_qqt,
nvl(max(case when t.fl = ‘终端拉新’ then t.lx else 0 end), 0) fl_zdlx,
nvl(max(case when t.fl = ‘家庭拉新’ then t.lx else 0 end), 0) fl_kdlx,
fn_parase_rate(nvl(max(case when t.fl = ‘用户拓展能力’ then t.rate_wc else 0 end), 0)) rate_wc_yh,
fn_parase_rate(nvl(max(case when t.fl = ‘宽带拓展能力’ then t.rate_wc else 0 end), 0)) rate_wc_kd,
fn_parase_rate(nvl(max(case when t.fl = ‘终端销售能力’ then t.rate_wc else 0 end), 0)) rate_wc_zd,
nvl(max(case when t.fl = ‘全球通归位能力’ then t.rate_wc else 0 end), 0) rate_wc_qqt,
fn_parase_rate(nvl(max(case when t.fl = ‘终端拉新’ then t.rate_wc else 0 end), 0)) rate_wc_zdlx,
fn_parase_rate(nvl(max(case when t.fl = ‘家庭拉新’ then t.rate_wc else 0 end), 0)) rate_wc_kdlx
from temp1 t
group by t.orgaid, t.organame
order by t.orgaid);
return v_test;
end;
/

select * from table(fn_get_yxcj_fl_list(110103))