CREATE OR REPLACE PACKAGE PKG_REPORT_205 IS
-- Created : 2011-5-20 10:27:24
-- Purpose : 客户经理存量名单客户四项核心指标执行情况统计表（保有率）
TYPE tp_org IS RECORD
(
org2_id VARCHAR2(9) --分行编码
, org2_nm VARCHAR2(160) --分行名称
, org_id VARCHAR2(9) --机构编码
, org_nm VARCHAR2(160) --机构名称
, org_type VARCHAR2(10) --机构类别
, spe_org VARCHAR2(2) --机构属性
, am_no VARCHAR2(20) --客户经理编号
, am_nm VARCHAR2(20) --客户经理姓名
, am_grade VARCHAR2(30) --客户经理等级
, cust_amt NUMBER(20) --客户数量
, curr_reten_amt NUMBER(20) --保有客户数量
, curr_amt NUMBER(20) --AUM5+客户数量
, curr_reten_rate NUMBER(20,4) --当前保有率
, last_reten_amt NUMBER(20) --上年底保有客户数量
, last_amt NUMBER(20) --上年底AUM5+客户数量
, last_reten_rate NUMBER(20,4) --上年底保有率
, reten_rate_change NUMBER(20,4) --保有率较年初变化情况
, para_value NUMBER(20,2) --参数值
, plan_rate NUMBER(20,4) --计划完成率
, plan_rate_rank2 NUMBER(20,2) --计划完成率排名(分行)
, plan_rate_rank1 NUMBER(20,2) --计划完成率排名(区)
);
TYPE ref_org IS REF CURSOR RETURN tp_org ;
TYPE tbl_org IS TABLE OF tp_org ;
TYPE tp_tmp_org IS TABLE OF tp_org INDEX BY PLS_INTEGER ;
TYPE tp_branch IS RECORD
(
org2_id VARCHAR2(9) --分行编码
, org2_nm VARCHAR2(160) --分行名称
, org_amt NUMBER(20) --机构数量
, am_amt NUMBER(20) --客户经理数量
, cust_amt NUMBER(20) --客户数量
, curr_reten_amt NUMBER(20) --保有客户数量
, curr_amt NUMBER(20) --list客户数量
, curr_reten_rate NUMBER(20,4) --当前保有率
, last_reten_amt NUMBER(20) --上年底保有客户数量
, last_amt NUMBER(20) --上年底list客户数量
, last_reten_rate NUMBER(20,4) --上年底保有率
, reten_rate_change NUMBER(20,4) --保有率较年初变化情况
, para_value NUMBER(20,2) --参数值
, am_avg_change NUMBER(20,4) --客户经理平均较年初变化
, org_avg_change NUMBER(20,4) --点均较年初变化
, lower_org_amt NUMBER(20) --低于全行平均水平的网点数
, lower_org_rate NUMBER(20,4) --低于全行平均水平的网占比
, plan_rate NUMBER(20,4) --计划完成率
, plan_rate_rank1 NUMBER(20) --计划完成率排名(区)
, last_am_amt NUMBER(20) --上年底客户经理数量
, last_org_amt NUMBER(20) --上年底网点数量
);
TYPE ref_branch IS REF CURSOR RETURN tp_branch ;
TYPE tbl_branch IS TABLE OF tp_branch ;
TYPE tbl_tmp_branch IS TABLE OF tp_branch INDEX BY PLS_INTEGER ;
-- Public constant declarations
rowcnt_default_limit CONSTANT INTEGER := 100;
-- Public variable declarations
-- Public function and procedure declarations
FUNCTION reten_rate_basic(p_stat_dt VARCHAR2) RETURN ref_org;
FUNCTION org_reten_rate(p_stat_dt VARCHAR2) RETURN tbl_org PIPELINED PARALLEL_ENABLE ;
FUNCTION branch_reten_rate(p_stat_dt VARCHAR2) RETURN tbl_branch PIPELINED PARALLEL_ENABLE;
END PKG_REPORT_205;
/
CREATE OR REPLACE PACKAGE BODY PKG_REPORT_205 IS
-- Function and procedure implementations
FUNCTION reten_rate_basic(p_stat_dt VARCHAR2) RETURN ref_org IS
cur_basic ref_org ;
v_stat_dt VARCHAR2(8) := regexp_replace(p_stat_dt,'-| ');
BEGIN
OPEN cur_basic FOR
------------------------------------------------------------------------
-------------统计上年底或当前有存量客户的客户经理的保有情况-------------
WITH
liststore AS (
--统计出当前的存量名单客户或去年的存量名单客户
--使用stat_dt作为是什么时间的存量名单客户的标识
SELECT asst.cust_no
, asst.start_dt AS stat_dt
FROM f_cust_asst_avg_his asst
INNER JOIN f_cm_cust_rela_his rela
ON rela.cust_no = asst.cust_no
WHERE
asst.prod_id = '10000'
AND (
(asst.start_dt = v_stat_dt AND
rela.start_dt <= v_stat_dt AND
rela.end_dt > v_stat_dt AND
rela.cust_group = '01'
)
OR
(
asst.start_dt = SUBSTR(v_stat_dt,1,4) -1 || '1231' AND
rela.start_dt <= SUBSTR(v_stat_dt,1,4) -1 || '1231' AND
rela.end_dt > SUBSTR(v_stat_dt,1,4) -1 || '1231' AND
rela.cust_group = '01'
)
)
)
, cust_asst_stat AS (
--1、统计上表中，客户的liststore.stat_dt日期asst_avg和liststore.stat_dt上年底的asst_avg
--2、列转行
--stat_dt标识是什么时间的存量名单用户
SELECT asst.cust_no
, liststore.stat_dt
, SUM (CASE WHEN asst.start_dt = liststore.stat_dt
THEN asst.asst_avg
ELSE NULL
END
) AS stat_asst
, SUM (CASE WHEN asst.start_dt = SUBSTR(liststore.stat_dt,1,4) -1 || '1231'
THEN asst.asst_avg
ELSE NULL
END
) AS before_stat_asst
FROM f_cust_asst_avg_his asst
INNER JOIN liststore
ON asst.cust_no = liststore.cust_no
AND asst.prod_id = '10000'
AND (
asst.start_dt = liststore.stat_dt
OR
asst.start_dt = SUBSTR(liststore.stat_dt,1,4) -1 || '1231'
)
GROUP BY asst.cust_no,liststore.stat_dt
--ORDER BY asst.cust_No
)
, reten_para AS (
--取出当前和上年底的保有率参数
SELECT
SUM (CASE WHEN warning.start_dt <= v_stat_dt AND
warning.end_dt > v_stat_dt
THEN warning.retention_val
ELSE NULL
END
) AS curr_reten_para
, SUM (CASE WHEN warning.start_dt <= substr(v_stat_dt,1,4) -1 || '1231' AND
warning.end_dt > substr(v_stat_dt,1,4) -1 || '1231'
THEN warning.retention_val
ELSE NULL
END
) AS last_reten_para
FROM f_cm_param_warning warning
WHERE
--当前
(warning.start_dt <= v_stat_dt AND warning.end_dt > v_stat_dt) OR
--上年底
( warning.start_dt <= substr(v_stat_dt,1,4) -1 || '1231' AND
warning.end_dt > substr(v_stat_dt,1,4) -1 || '1231')
)
, cust_reten AS (
--存量名单客户保有标志
--stat_dt说明是什么时间的客户
SELECT asst.cust_no
, asst.stat_dt
, CASE WHEN DECODE(asst.stat_dt,v_stat_dt,para.curr_reten_para,para.last_reten_para) IS NULL
--没有保有参数认为保有
THEN 1
ELSE
CASE WHEN asst.before_stat_asst IS NULL --如果统计日期的上年底没有资产，则认为保有
THEN 1
WHEN asst.before_stat_asst=0 AND asst.before_stat_asst = 0
THEN
CASE WHEN 0 >= para.curr_reten_para
THEN 0
ELSE 1
END
WHEN asst.before_stat_asst=0
THEN 1
WHEN (1 - asst.stat_asst / asst.before_stat_asst) >=
DECODE(asst.stat_dt,v_stat_dt,para.curr_reten_para,para.last_reten_para)
THEN 0
ELSE
1
END
END AS reten_flg --保有标志
-- , DECODE(asst.stat_dt,v_stat_dt,para.curr_reten_para,para.last_reten_para) AS reten_para
-- , asst.stat_asst
-- , asst.before_stat_asst
FROM cust_asst_stat asst
LEFT JOIN reten_para para
ON 1=1
)
-- select * from cust_reten;
, am_cust AS (
--客户经理对应的存量名单制客户
--stat_dt标识什么时间的关系
--客户经理和客户关系有可能跨越两个段，所以...
SELECT rela.am_no
, rela.cust_no
, CASE WHEN rela.start_dt <= v_stat_dt AND
rela.end_dt > v_stat_dt AND
rela.start_dt <= SUBSTR(v_stat_dt,1,4) -1 || '1231' AND
rela.end_dt > SUBSTR(v_stat_dt,1,4) -1 || '1231'
THEN 'ALL'
WHEN rela.start_dt <= v_stat_dt AND
rela.end_dt > v_stat_dt
THEN v_stat_dt
ELSE
SUBSTR(v_stat_dt,1,4) -1 || '1231'
END AS stat_dt
FROM f_cm_cust_rela_his rela
WHERE
( rela.start_dt <= v_stat_dt
AND rela.end_dt > v_stat_dt
AND rela.cust_group = '01'
)
OR
( rela.start_dt <= SUBSTR(v_stat_dt,1,4) -1 || '1231'
AND rela.end_dt > SUBSTR(v_stat_dt,1,4) -1 || '1231'
AND rela.cust_group = '01'
)
)
, am_reten AS (
--客户经理对应的存量名单上用户的保有状态。
--stat_dt标识什么时间的客户关系
--reten_flg该客户是否保有1保有，0非保有
--（有的上年底日期的am_no可能并不在当前的am_no中，但是用当前的left join am之后，只会出现当前的am_no）
SELECT reten.stat_dt AS stat_dt
, rela.am_no
, rela.cust_no
, reten.reten_flg
FROM am_cust rela
INNER JOIN cust_reten reten
ON rela.cust_no = reten.cust_no
AND (
rela.stat_dt = 'ALL'
OR rela.stat_dt = reten.stat_dt
)
)
, am_reten_amt AS (
--客户经理对应的存量名单上用户的数量，保有数量。
--（am_no可能并不在当前的am_no中，但是用当前的left join am之后，只会出现当前的am_no）
SELECT
am_no
, SUM (DECODE(reten.stat_dt,v_stat_dt,reten.reten_flg,0))
AS curr_reten_amt
, SUM (DECODE(reten.stat_dt,v_stat_dt,1,0)) --decode最后取0：如果日期没有存量名单用户如何处理
AS curr_amt
, SUM (DECODE(reten.stat_dt,SUBSTR(v_stat_dt,1,4) -1 ||'1231',reten.reten_flg,0))
AS last_reten_amt
, SUM (DECODE(reten.stat_dt,SUBSTR(v_stat_dt,1,4) -1 ||'1231',1,0))
AS last_amt
FROM am_reten reten
GROUP BY reten.am_no
)
------------------------------------------------------------------------------
-----------------------统计机构当前所对应的所有客户经理-----------------------
, am_no AS(
--取截止日期之前本年所有的客户经理，包括当前已删除的
SELECT DISTINCT am_no,UPPER(grade) grade
FROM f_cm_am_grade_his
WHERE grade IN ('NMUDMP-amem-wdkhjl','NMUDMP-amem-wdkhjlzg')
AND start_dt <= v_stat_dt
AND end_dt > substr(v_stat_dt, 1, 4)||'0101'
)
, am AS(
--取客户经理本月最早所在的机构,客户经理身份证号，客户经理姓名
SELECT am_no.am_no
, info.cert_no
, info.am_nm
, MIN(am_no.grade) AS grade --机构及上统计经理数量时不计算主管。
, MAX(org.org_id) KEEP(dense_rank FIRST ORDER BY org.start_dt ASC) AS org_id
FROM am_no
LEFT JOIN f_cm_am_org_his org
ON am_no.am_no = org.am_no
AND org.start_dt <= v_stat_dt
AND org.end_dt > SUBSTR(v_stat_dt, 1, 6)||'01'
LEFT JOIN f_cm_am_info info
ON am_no.am_no = info.am_no
GROUP BY am_no.am_no
, info.cert_no
, info.am_nm
)
, org_am AS (
SELECT org.org2_id
, org.org2_nm
, org.org4_id AS org_id
, org.org4_nm AS org_nm
, org.spe_org
, am.am_no
, am.am_nm
, am.grade
FROM view_f_cm_org2_code org
INNER JOIN am
ON org.org4_id = am.org_id
WHERE org.year_id = SUBSTR(v_stat_dt,1,4)
)
------------------------------------------------------------------------------
, cust_amt AS (
--客户经理所辖存量名单制客户数量
SELECT am_no --客户经理编号
, SUM(t.cust_all_cnt) AS cust_amt --客户数量
FROM
s_am_cust_group_num_prd t
WHERE t.stat_dt = v_stat_dt
AND t.cust_group = '01'
GROUP BY am_no
)
SELECT org.org2_id
, org.org2_nm
, org.org_id
, org.org_nm
, para.org_typ
, org.spe_org
, org.am_no
, org.am_nm
, org.grade
, amt.cust_amt
, reten.curr_reten_amt
, reten.curr_amt
, reten.curr_reten_amt / NULLIF(reten.curr_amt,0) AS curr_reten_rate
, reten.last_reten_amt
, reten.last_amt
, reten.last_reten_amt / NULLIF(reten.last_amt,0) AS last_reten_rate
, (
reten.curr_reten_amt / NULLIF(reten.curr_amt,0)
-
reten.last_reten_amt / NULLIF(reten.last_amt,0)
) AS reten_rate_change
, para.param_value
, (
reten.curr_reten_amt / NULLIF(reten.curr_amt,0)
-
reten.last_reten_amt / NULLIF(reten.last_amt,0)
) / NULLIF(para.param_value,0)
AS plan_rate
, RANK() OVER( PARTITION BY org2_id ORDER BY
(
reten.curr_reten_amt / NULLIF(reten.curr_amt,0)
-
reten.last_reten_amt / NULLIF(reten.last_amt,0)
) / NULLIF(para.param_value,0) DESC NULLS LAST
) AS plan_rate_rank2
, RANK() OVER( ORDER BY
(
reten.curr_reten_amt / NULLIF(reten.curr_amt,0)
-
reten.last_reten_amt / NULLIF(reten.last_amt,0)
) / NULLIF(para.param_value,0) DESC NULLS LAST
) AS plan_rate_rank1
FROM am_reten_amt reten
LEFT JOIN org_am org
ON org.am_no = reten.am_no
LEFT JOIN cust_amt amt
ON amt.am_no = org.am_no
LEFT JOIN f_cm_param_org4_listcust para
ON para.org4_id = org.org_id
AND para.start_dt <= v_stat_dt
AND para.end_dt > v_stat_dt
AND para.ind_cd = '02';
RETURN cur_basic;
EXCEPTION
WHEN OTHERS
THEN raise_application_error(-20000,SQLERRM );
END ;
FUNCTION org_reten_rate(p_stat_dt VARCHAR2) RETURN tbl_org PIPELINED PARALLEL_ENABLE IS
v_stat_dt VARCHAR2(8) := regexp_replace(p_stat_dt,'-| ');
v_cur ref_org := reten_rate_basic(v_stat_dt);
v_out_row tp_org;
v_array tp_tmp_org;
BEGIN
LOOP
FETCH v_cur BULK COLLECT INTO v_array LIMIT rowcnt_default_limit ;
EXIT WHEN v_array.count = 0 ;
FOR i IN 1 .. v_array.count
LOOP
v_out_row.org2_id := v_array(i).org2_id;
v_out_row.org2_nm := v_array(i).org2_nm;
v_out_row.org_id := v_array(i).org_id;
v_out_row.org_nm := v_array(i).org_nm;
v_out_row.org_type := v_array(i).org_type;
v_out_row.spe_org := v_array(i).spe_org;
v_out_row.am_no := v_array(i).am_no;
v_out_row.am_nm := v_array(i).am_nm;
v_out_row.am_grade := v_array(i).am_grade;
v_out_row.cust_amt := v_array(i).cust_amt;
v_out_row.curr_reten_amt := v_array(i).curr_reten_amt;
v_out_row.curr_amt := v_array(i).curr_amt;
v_out_row.curr_reten_rate := v_array(i).curr_reten_rate;
v_out_row.last_reten_amt := v_array(i).last_reten_amt;
v_out_row.last_amt := v_array(i).last_amt;
v_out_row.last_reten_rate := v_array(i).last_reten_rate;
v_out_row.reten_rate_change := v_array(i).reten_rate_change;
v_out_row.para_value := v_array(i).para_value ;
v_out_row.plan_rate := v_array(i).plan_rate ;
v_out_row.plan_rate_rank2 := v_array(i).plan_rate_rank2;
v_out_row.plan_rate_rank1 := v_array(i).plan_rate_rank1;
PIPE ROW ( v_out_row) ;
END LOOP;
END LOOP;
CLOSE v_cur ;
RETURN ;
EXCEPTION
WHEN OTHERS
THEN raise_application_error(-20000,SQLERRM );
END;
FUNCTION branch_reten_rate(p_stat_dt VARCHAR2) RETURN tbl_branch PIPELINED PARALLEL_ENABLE IS
cur_branch ref_branch ;
v_out_row tp_branch;
v_array tbl_tmp_branch;
v_stat_dt VARCHAR2(8) := regexp_replace(p_stat_dt,'-| ');
BEGIN
OPEN cur_branch FOR
--------------------------------------------------------
------------------统计真正机构视角信息------------------
WITH org_view AS (
SELECT
org2_id
, org2_nm
, org_id
, org_nm
, MIN(org_type) AS org_type
, MIN(spe_org) AS spe_org
, COUNT(DISTINCT CASE WHEN curr_amt IS NOT NULL AND am_grade = 'NMUDMP-AMEM-WDKHJL'
THEN am_no
ELSE NULL
END ) AS am_amt --只有网点客户经理,有存量客户的客户经理作为计数使用
-- , COUNT(DISTINCT decode(am_grade,'NMUDMP-AMEM-WDKHJL',am_no,NULL)) AS am_amt --只有网点客户经理作为计数使用
, SUM (cust_amt) AS cust_amt
, SUM (curr_reten_amt) AS curr_reten_amt
, SUM (curr_amt) AS curr_amt
, SUM (curr_reten_amt) / nullif(SUM(curr_amt),0) AS curr_reten_rate
, SUM (last_reten_amt) AS last_reten_amt
, SUM (last_amt) AS last_amt
, SUM (last_reten_amt) / nullif(SUM(last_amt),0) AS last_reten_rate
, SUM (curr_reten_amt) / nullif(SUM(curr_amt),0)
- nvl(SUM (last_reten_amt) / nullif(SUM(last_amt),0),0)
AS reten_rate_change
, MIN (para_value) AS para_value
, (
SUM (curr_reten_amt) / nullif(SUM(curr_amt),0)
-
nvl(SUM (last_reten_amt) / nullif(SUM(last_amt),0),0)
) / NULLIF(MIN(para_value),0) AS plan_rate
, rank() OVER(PARTITION BY org2_id ORDER BY
(
SUM (curr_reten_amt) / nullif(SUM(curr_amt),0)
-
nvl(SUM (last_reten_amt) / nullif(SUM(last_amt),0),0)
) / NULLIF(MIN(para_value),0)
DESC
NULLS LAST
) AS plan_rate_rank2
, rank() OVER(
ORDER BY
(
SUM (curr_reten_amt) / nullif(SUM(curr_amt),0)
-
nvl(SUM (last_reten_amt) / nullif(SUM(last_amt),0),0)
)
/ NULLIF(MIN(para_value),0) DESC
NULLS LAST
) AS plan_rate_rank1
FROM TABLE(org_reten_rate(v_stat_dt))
GROUP BY org2_id,org2_nm,org_id,org_nm
)
, org_det AS (
--计算机构是否小于全行平均
SELECT
org2_id
, org2_nm
, org_id
, org_nm
, org_type
, spe_org
, am_amt
, cust_amt
, curr_reten_amt
, curr_amt
, curr_reten_rate
, last_reten_amt
, last_amt
, last_reten_rate
, reten_rate_change
, para_value
, plan_rate
, plan_rate_rank2
, plan_rate_rank1
, CASE WHEN curr_reten_rate IS NULL
OR curr_reten_rate <
(SUM(curr_reten_amt) over()/NULLIF(SUM(curr_amt) over(),0))
THEN 1
ELSE 0
END AS lower_diavg_flg
FROM org_view
)
--------------------------------------------------------------
-----统计上年分行对应的机构数量，有存量客户的客户经理数量-----
-----统计当前分行对应的机构数量， -----
, list_am_last AS (
--上年底持有名单制客户的客户经理
SELECT am_no
FROM f_cm_cust_rela_his rela
WHERE rela.start_dt <= SUBSTR(v_stat_dt,1,4) -1 || '1231'
AND rela.end_dt > SUBSTR(v_stat_dt,1,4) -1 || '1231'
AND rela.cust_group = '01'
GROUP BY rela.am_no
)
, org4_am_last AS (
--上年底org4和am的关系
SELECT MIN(his.org_id) KEEP(dense_rank FIRST ORDER BY his.start_dt ASC) AS org_id
, am_no
FROM f_cm_am_org_his his
WHERE his.start_dt <= SUBSTR(v_stat_dt,1,4) -1 || '1231'
AND his.end_dt > SUBSTR(v_stat_dt,1,4) -1 || '1201'
GROUP BY his.am_no
)
, org4_am_amt_last AS (
--上年机构对应 有存量客户的客户经理数量
SELECT org4.org_id
, COUNT(list.am_no) AS am_amt
FROM org4_am_last org4
INNER JOIN list_am_last list
ON org4.am_no = list.am_no
GROUP BY org4.org_id
)
, org2 AS (
--上年底分行对应的网点数量和有存量客户的客户经理数量
--当前分行对应的网点数量
SELECT
org.org2_id
, COUNT(
CASE WHEN org.spe_org = '0'
AND org.year_id = SUBSTR(v_stat_dt,1,4)-1
THEN org.org4_id
END
) AS last_org_amt
, SUM(decode(org.year_id,SUBSTR(v_stat_dt,1,4)-1,org4.am_amt)) AS last_am_amt
, COUNT (
CASE WHEN org.spe_org = '0'
AND org.year_id = SUBSTR(v_stat_dt,1,4)
THEN org.org4_id
END
) AS curr_org_amt
FROM view_f_cm_org2_code org
LEFT JOIN org4_am_amt_last org4
ON org.org4_id = org4.org_id
WHERE org.year_id IN (SUBSTR(v_stat_dt,1,4) -1,SUBSTR(v_stat_dt,1,4))
GROUP BY org.org2_id
)
-------------------------------------------------------------
SELECT
nst.org2_id
, nst.org2_nm
, MIN(org2.curr_org_amt) AS org_amt
, SUM(nst.am_amt) AS am_amt
, SUM (nst.cust_amt) AS cust_amt
, SUM (nst.curr_reten_amt) AS curr_reten_amt
, SUM (nst.curr_amt) AS curr_amt
, SUM (nst.curr_reten_amt) / nullif(SUM (nst.curr_amt),0) AS curr_reten_rate
, SUM (nst.last_reten_amt) AS last_reten_amt
, SUM (nst.last_amt) AS last_amt
, SUM (nst.last_reten_amt) / nullif(SUM (nst.last_amt),0) AS last_reten_rate
, SUM (nst.curr_reten_amt) / nullif(SUM (nst.curr_amt),0)
-
nvl(SUM (nst.last_reten_amt) / nullif(SUM (nst.last_amt),0),0)
AS reten_rate_change
, MIN(para.param_value) AS para_value
,
--客户经理平均较年初变化:
(
SUM (nst.curr_reten_amt) / NULLIF (SUM (nst.curr_amt),0) / NULLIF (SUM(nst.am_amt),0)
)
-
NVL( SUM (nst.last_reten_amt) / NULLIF (SUM (nst.last_amt),0) / NULLIF (MIN(org2.last_am_amt),0),0)
AS am_avg_change
,
--点均较年初变化:
(
SUM (nst.curr_reten_amt) / NULLIF (SUM (nst.curr_amt),0) / NULLIF (MIN(org2.curr_org_amt),0)
)
-
NVL(SUM (nst.last_reten_amt) / NULLIF (SUM (nst.last_amt),0) / NULLIF (MIN(org2.last_org_amt),0) , 0)
AS org_avg_change
,
--低于全行平均水平的网点数:低于全行平均水平的网点数,
SUM ( decode(nst.spe_org,'0',nst.lower_diavg_flg)) AS lower_org_amt
,
--低于全行平均水平的网占比: 低于全行平均水平的网点数/二级行网点数
SUM ( decode(nst.spe_org,'0',nst.lower_diavg_flg))
/ nullif(COUNT(nst.org_id),0) AS lower_org_rate
, (
SUM (nst.curr_reten_amt) / NULLIF (SUM (nst.curr_amt),0)
-
NVL(SUM (nst.last_reten_amt) / NULLIF (SUM (nst.last_amt),0),0)
) / NULLIF(MIN(para.param_value),0) AS plan_rate
, rank() OVER(
ORDER BY
(
SUM (nst.curr_reten_amt) / NULLIF (SUM (nst.curr_amt),0)
-
NVL(SUM (nst.last_reten_amt) / NULLIF (SUM (nst.last_amt),0),0)
) / NULLIF(MIN(para.param_value),0)
DESC
NULLS LAST
) AS plan_rate_rank1
, MIN(org2.last_am_amt) AS last_am_amt --上年底am数量
, MIN(org2.last_org_amt) AS last_org_amt --上年底机构数量
FROM org_det nst
left JOIN f_cm_param_org2_listcust para
ON nst.org2_id = para.org2_id
AND para.start_dt <= v_stat_dt
AND para.end_dt > v_stat_dt
AND para.ind_cd = '02'
LEFT JOIN org2
ON org2.org2_id = nst.org2_id
GROUP BY nst.org2_id,nst.org2_nm
ORDER BY nst.org2_id
;
LOOP
FETCH cur_branch BULK COLLECT INTO v_array LIMIT rowcnt_default_limit ;
EXIT WHEN v_array.count = 0 ;
FOR i IN 1 .. v_array.count
LOOP
v_out_row.org2_id := v_array(i).org2_id ;
v_out_row.org2_nm := v_array(i).org2_nm ;
v_out_row.org_amt := v_array(i).org_amt ;
v_out_row.am_amt := v_array(i).am_amt ;
v_out_row.cust_amt := v_array(i).cust_amt ;
v_out_row.curr_reten_amt := v_array(i).curr_reten_amt;
v_out_row.curr_amt := v_array(i).curr_amt;
v_out_row.curr_reten_rate := v_array(i).curr_reten_rate;
v_out_row.last_reten_amt := v_array(i).last_reten_amt;
v_out_row.last_amt := v_array(i).last_amt;
v_out_row.last_reten_rate := v_array(i).last_reten_rate;
v_out_row.reten_rate_change := v_array(i).reten_rate_change;
v_out_row.para_value := v_array(i).para_value ;
v_out_row.am_avg_change := v_array(i).am_avg_change ;
v_out_row.org_avg_change := v_array(i).org_avg_change ;
v_out_row.lower_org_amt := v_array(i).lower_org_amt ;
v_out_row.lower_org_rate := v_array(i).lower_org_rate ;
v_out_row.plan_rate := v_array(i).plan_rate ;
v_out_row.plan_rate_rank1 := v_array(i).plan_rate_rank1 ;
v_out_row.last_am_amt := v_array(i).last_am_amt ;
v_out_row.last_org_amt := v_array(i).last_org_amt ;
PIPE ROW ( v_out_row) ;
END LOOP;
END LOOP;
CLOSE cur_branch ;
RETURN ;
EXCEPTION
WHEN OTHERS
THEN raise_application_error(-20000,SQLERRM );
END;
END PKG_REPORT_205;
/
procedure,package,ref cursor , nested table,record ....
最新推荐文章于 2020-03-26 18:01:13 发布
1610
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
