oracle-复杂sql模板

最新推荐文章于 2024-09-14 19:55:29 发布

weixin_34114823

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量205

点赞数

文章标签：数据库 java

OB(即一个自定义的对象，修改OB之前需删掉引用它的NT):

View Code

create or replace type ob_shareholdersrewardtrack as object
(
-- Attributes

  BRANCHname  VARCHAR2(64),
  AGENTCODE   VARCHAR2(64), --营销员代码
  AGENTNAME   VARCHAR2(64), --营销员姓名
  AGENTSTATUS VARCHAR2(64), --签约情况
--QUALIFICATIONNO  agentcert.qualificationno%type,--资格证号
  IDNO              varchar2(64), --资格证号
  Thirdquarterpoli  NUMBER(15),
  Fourthquarterpoli NUMBER(15),
  thirteenmonthrate number(15, 4),
  fourteenmonthrate number(15, 4),
  attendance        VARCHAR2(64), --出勤
  VALUEPREM         NUMBER(15,2), -- 2012年新单价保
  subscription      NUMBER(15), --认购权份数
  RewardsMoney      NUMBER(15), --奖励金额
  agentlevelname    VARCHAR2(64), --级别
  HIREDATE          varchar2(32), --入司时间
  ubranchname       VARCHAR2(64),
  CONTACTADDR       varchar2(128),
  CONTACTPHONE      varchar2(64),
  ext1              varchar2(64), --扩展字段
  ext2              varchar2(64),
  ext3              varchar2(64),
  ext4              varchar2(64),
  ext5              varchar2(64),
  constructor function ob_shareholdersrewardtrack return self as result
)
/
create or replace type body ob_shareholdersrewardtrack is

  -- Member procedures and functions
  CONSTRUCTOR FUNCTION ob_shareholdersrewardtrack RETURN SELF AS RESULT is
  begin
    return;
  end;

end;
/

NT(即一个泛型数组)

View Code

create or replace type nt_shareholdersrewardtrack as table of ob_shareholdersrewardtrack

select *
from Table(Pkg_Operation_RenewRatenew.calcRenewRate('BRA0000000000002',
                                                      'cbranch',
                                                      null,
                                                      '201210',
                                                      '201210',
                                                      null,
                                                      null));

返回的一个NT我们可以将它当做一个table用，字段就是OB里面定义的

PKG(相当于java里面的类，里面有各种自定义的存储过程和函数,OB,NT等等)

View Code

create or replace package pkg_agent_essence is


  --精英人才激励方案追踪表（生成数据）
  function genessencetrack(p_contractcon in varchar2) return nt_essencetrack;

  --精英人才激励方案追踪表（查询数据）
  function getessencetrack(p_branchid    in varchar2,
                           p_cbranchid   in varchar2,
                           p_ubranchid   in varchar2,
                           p_contractcon in varchar2) return nt_essencetrack;

  --获取代理人各月预收业绩
  function getpreresult(p_agentid in varchar2, p_startyearmonth in date)
    return nt_essencetrack;

  --获取代理人各月回单业绩
  function getfactresult(p_agentid in varchar2, p_startyearmonth in date)
    return nt_essencetrack;

  --2012年准股东奖励方案追踪表（查询数据）
  function getshareholdersrewardtrack(p_branchid  in varchar2,
                                      p_cbranchid in varchar2,
                                      p_ubranchid in varchar2,
                                      p_agentcode in varchar2)
    return nt_shareholdersrewardtrack;

  --将拆单情况的保单ID插入到表
  PROCEDURE inserttmpcontractid(p_agentcode in varchar2,
                                p_QUARTER   in varchar2);

  --获得拆分情况下不同代理人的保单的归属件数及应得的价保
  function getcontractbelongcount(p_agentcode in varchar2,
                                  p_QUARTER   in varchar2) return number;

  --获得拆分情况下相同的代理人的保单的归属件数
  function getcontractcount(p_agentcode in varchar2, p_QUARTER in varchar2)
    return number;

end pkg_agent_essence;
/
create or replace package body pkg_agent_essence is

  --精英人才激励方案追踪表（生成数据）
  function genessencetrack(p_contractcon in varchar2) return nt_essencetrack is
    p_nt_essencetrack nt_essencetrack;
    m_nt_essencetrack nt_essencetrack;
    i                 smallint := 1;
    p_yearmonth       date;
    p_startyearmonth  date;
    p_prehiremonth    date;
    p_qualificationno varchar2(64);
    p_maxpreyearmonth date;
  begin
    p_nt_essencetrack := new nt_essencetrack();
    p_startyearmonth  := to_date('2012-04-01', 'yyyy-mm-dd');
    for cur in (select b.branchname      BRANCHNAME,
                       br.branchname     CBRANCHNAME,
                       bra.branchname    UBRANCHNAME,
                       b.branchid        branchid,
                       br.branchid       cbranchid,
                       bra.branchid      ubranchid,
                       ag.agentid        AGENTID,
                       ag.agentcode      AGENTCODE,
                       ag.agentname      AGENTNAME,
                       ag.hiredate,
                       ca.agentlevelname AGENTLEVELNAME
                  from agentbroker    ag,
                       caseagentlevel ca,
                       branch         b,
                       branch         br,
                       branch         bra
                 where ag.branchid = b.branchid
                   and ag.cbranchid = br.branchid
                   and ag.ubranchid = bra.branchid
                   and ag.agentlevelcode = ca.agentlevelcode
                   and ag.agentstatus = 'AGENTSTATUS_09'
                   and trunc(ag.hiredate) <= trunc(sysdate - 1)
                      --  and ag.agentid = 'AGE0000005185554'
                      /*                   and ag.agentcode in
                      ('8013399', '8004803', '8007575', '8008964', '8008345',
                       'A412905', 'A410385', '8009919', '8007568', 'A32063',
                       '8014233', '8009327', '8007411', '8005008', '8008053',
                       '8010625', '8011061')*/
                   and ag.agenttype not in
                       ('AGENTTYPE_V', 'AGENTTYPE_T', 'AGENTTYPE_X')) loop
      p_nt_essencetrack.extend;
      p_nt_essencetrack(i) := new ob_essencetrack();
    
      p_nt_essencetrack(I).createdate := sysdate;
      p_nt_essencetrack(I).branchid := cur.branchid;
      p_nt_essencetrack(I).cbranchid := cur.cbranchid;
      p_nt_essencetrack(I).ubranchid := cur.ubranchid;
      p_nt_essencetrack(I).BRANCHNAME := cur.BRANCHNAME;
      p_nt_essencetrack(I).CBRANCHNAME := cur.CBRANCHNAME;
      p_nt_essencetrack(I).UBRANCHNAME := cur.UBRANCHNAME;
      p_nt_essencetrack(I).AGENTCODE := cur.AGENTCODE;
      p_nt_essencetrack(I).AGENTNAME := cur.AGENTNAME;
      p_nt_essencetrack(I).AGENTLEVELNAME := cur.AGENTLEVELNAME;
      p_nt_essencetrack(I).QULIFICATED := 'N';
      p_nt_essencetrack(I).contractcon := p_contractcon;
    
      --是否持证
      begin
        select ag.qualificationno
          into p_qualificationno
          from agentcert ag
         where ag.agentid = cur.agentid;
      exception
        when others then
          p_nt_essencetrack(I).QULIFICATED := 'N';
      end;
    
      if p_qualificationno is not null then
        p_nt_essencetrack(I).QULIFICATED := 'Y';
      end if;
    
      --计算入围月份
      --计算回单
      if p_contractcon = 'CONDITION_02' then
        m_nt_essencetrack := getfactresult(cur.agentid, p_startyearmonth);
      
        --先获取最大未入围月份如果为本月则获取第二大未入围月份
        begin
          select max(t.exp2)
            into p_yearmonth
            from table(m_nt_essencetrack) t
           where t.exp1 < 10000
             and t.exp2 >= trunc(cur.hiredate, 'month');
        
          if p_yearmonth is null then
            --根据入司日期进行判断
            if trunc(cur.hiredate, 'month') > p_startyearmonth then
              p_yearmonth := add_months(trunc(cur.hiredate, 'month'), -1);
            else
              p_yearmonth := trunc(add_months(p_startyearmonth, -1),
                                   'month');
            end if;
          end if;
        
        exception
          when others then
            --根据入司日期进行判断
            if trunc(cur.hiredate, 'month') > p_startyearmonth then
              p_yearmonth := add_months(trunc(cur.hiredate, 'month'), -1);
            else
              p_yearmonth := trunc(add_months(p_startyearmonth, -1),
                                   'month');
            end if;
        end;
      
        --如果为本月则获取第二大未入围月份
        if p_yearmonth = trunc(sysdate - 1, 'month') then
          begin
            select max(t.exp2)
              into p_yearmonth
              from table(m_nt_essencetrack) t
             where t.exp1 < 10000
               and t.exp2 < trunc(sysdate - 1, 'month')
               and t.exp2 >= trunc(cur.hiredate, 'month');
          
            if p_yearmonth is null then
              --根据入司日期进行判断
              if trunc(cur.hiredate, 'month') > p_startyearmonth then
                p_yearmonth := add_months(trunc(cur.hiredate, 'month'), -1);
              else
                p_yearmonth := trunc(add_months(p_startyearmonth, -1),
                                     'month');
              end if;
            end if;
          
          exception
            when others then
              --根据入司日期进行判断
              if trunc(cur.hiredate, 'month') > p_startyearmonth then
                p_yearmonth := add_months(trunc(cur.hiredate, 'month'), -1);
              else
                p_yearmonth := trunc(add_months(p_startyearmonth, -1),
                                     'month');
              end if;
            
          end;
        
          p_nt_essencetrack(i).FINALISTYEARMONTH := to_char(add_months(p_yearmonth,
                                                                       1),
                                                            'yyyymm');
          p_nt_essencetrack(i).CNTMONTH := months_between(trunc(sysdate - 1,
                                                                'month'),
                                                          p_yearmonth) - 1;
        else
          p_nt_essencetrack(i).FINALISTYEARMONTH := to_char(add_months(p_yearmonth,
                                                                       1),
                                                            'yyyymm');
          p_nt_essencetrack(i).CNTMONTH := months_between(trunc(sysdate - 1,
                                                                'month'),
                                                          p_yearmonth);
        end if;
      
        --计算本月业绩
        begin
          select t.exp1
            into p_nt_essencetrack(i).exp1
            from table(m_nt_essencetrack) t
           where t.exp2 = trunc(sysdate - 1, 'month');
        exception
          when others then
            p_nt_essencetrack(i).exp1 := '';
        end;
      
        --计算预收
      else
        --根据入司日期获取开始预收年月
        if extract(day from cur.hiredate) > 25 then
          p_prehiremonth := trunc(add_months(cur.hiredate, 1), 'month');
        else
          p_prehiremonth := trunc(cur.hiredate, 'month');
        end if;
      
        m_nt_essencetrack := getpreresult(cur.agentid, p_startyearmonth);
      
        --获取预收结束年月（以当前日期的前一天进行判断）
        if extract(day from sysdate - 1) > 25 then
          p_maxpreyearmonth := trunc(add_months(sysdate - 1, 1), 'month');
        else
          p_maxpreyearmonth := trunc(sysdate - 1, 'month');
        end if;
      
        --先获取最大未入围月份
        begin
          select max(t.exp2)
            into p_yearmonth
            from table(m_nt_essencetrack) t
           where t.exp1 < 10000
             and t.exp2 >= p_prehiremonth;
        
          if p_yearmonth is null then
            --根据入司日期进行判断
            if p_prehiremonth > p_startyearmonth then
              p_yearmonth := add_months(p_prehiremonth, -1);
            else
              p_yearmonth := trunc(add_months(p_startyearmonth, -1),
                                   'month');
            end if;
          end if;
        
        exception
          when others then
            --根据入司日期进行判断
            if p_prehiremonth > p_startyearmonth then
              p_yearmonth := add_months(p_prehiremonth, -1);
            else
              p_yearmonth := trunc(add_months(p_startyearmonth, -1),
                                   'month');
            end if;
        end;
      
        --如果为本月则获取第二大未入围月份
        if p_yearmonth = p_maxpreyearmonth then
          begin
            select max(t.exp2)
              into p_yearmonth
              from table(m_nt_essencetrack) t
             where t.exp1 < 10000
               and t.exp2 >= p_prehiremonth
               and t.exp2 < p_maxpreyearmonth;
          
            if p_yearmonth is null then
              --根据入司日期进行判断
              if p_prehiremonth > p_startyearmonth then
                p_yearmonth := add_months(p_prehiremonth, -1);
              else
                p_yearmonth := trunc(add_months(p_startyearmonth, -1),
                                     'month');
              end if;
            end if;
          
          exception
            when others then
              --根据入司日期进行判断
              if p_prehiremonth > p_startyearmonth then
                p_yearmonth := add_months(p_prehiremonth, -1);
              else
                p_yearmonth := trunc(add_months(p_startyearmonth, -1),
                                     'month');
              end if;
          end;
        
          p_nt_essencetrack(i).FINALISTYEARMONTH := to_char(trunc(add_months(p_yearmonth,
                                                                             1),
                                                                  'month'),
                                                            'yyyymm');
          p_nt_essencetrack(i).CNTMONTH := months_between(p_maxpreyearmonth,
                                                          p_yearmonth) - 1;
        else
          p_nt_essencetrack(i).FINALISTYEARMONTH := to_char(trunc(add_months(p_yearmonth,
                                                                             1),
                                                                  'month'),
                                                            'yyyymm');
          p_nt_essencetrack(i).CNTMONTH := months_between(p_maxpreyearmonth,
                                                          p_yearmonth);
        end if;
      
        --计算本月业绩
        begin
          select t.exp1
            into p_nt_essencetrack(i).exp1
            from table(m_nt_essencetrack) t
           where t.exp2 = p_maxpreyearmonth;
        exception
          when others then
            p_nt_essencetrack(i).exp1 := '';
        end;
      
      end if;
    
      if p_nt_essencetrack(i).CNTMONTH = 0 then
        p_nt_essencetrack(i).CNTMONTH := '';
        p_nt_essencetrack(i).FINALISTYEARMONTH := '';
      end if;
    
      i := i + 1;
    end loop;
    return p_nt_essencetrack;
  end;

  --精英人才激励方案追踪表（查询数据）
  function getessencetrack(p_branchid    in varchar2,
                           p_cbranchid   in varchar2,
                           p_ubranchid   in varchar2,
                           p_contractcon in varchar2) return nt_essencetrack is
    p_nt_essencetrack nt_essencetrack;
    i                 smallint := 1;
  begin
    p_nt_essencetrack := new nt_essencetrack();
    for cur in (select *
                  from tmp_essencetrack tm
                 where 1 = 1
                   and (p_branchid is null or tm.branchid = p_branchid)
                   and (p_cbranchid is null or tm.cbranchid = p_cbranchid)
                   and (p_ubranchid is null or tm.branchid = p_ubranchid)
                   and tm.contractcon = p_contractcon
                 order by tm.branchid  asc,
                          tm.cbranchid asc,
                          tm.ubranchid asc,
                          tm.exp1      desc) loop
    
      p_nt_essencetrack.extend;
      p_nt_essencetrack(i) := new ob_essencetrack();
    
      p_nt_essencetrack(I).CREATEDATE := cur.CREATEDATE;
      p_nt_essencetrack(I).BRANCHID := cur.BRANCHID;
      p_nt_essencetrack(I).CBRANCHID := cur.CBRANCHID;
      p_nt_essencetrack(I).UBRANCHID := cur.UBRANCHID;
      p_nt_essencetrack(I).BRANCHNAME := cur.BRANCHNAME;
      p_nt_essencetrack(I).CBRANCHNAME := cur.CBRANCHNAME;
      p_nt_essencetrack(I).UBRANCHNAME := cur.UBRANCHNAME;
      p_nt_essencetrack(I).AGENTID := cur.AGENTID;
      p_nt_essencetrack(I).AGENTCODE := cur.AGENTCODE;
      p_nt_essencetrack(I).AGENTNAME := cur.AGENTNAME;
      p_nt_essencetrack(I).AGENTLEVELNAME := cur.AGENTLEVELNAME;
      p_nt_essencetrack(I).FINALISTYEARMONTH := cur.FINALISTYEARMONTH;
      p_nt_essencetrack(I).CNTMONTH := cur.CNTMONTH;
      p_nt_essencetrack(I).QULIFICATED := cur.QULIFICATED;
      p_nt_essencetrack(I).CONTRACTCON := cur.CONTRACTCON;
      p_nt_essencetrack(I).EXP1 := round(cur.EXP1);
      p_nt_essencetrack(I).EXP2 := cur.EXP2;
      p_nt_essencetrack(I).EXP3 := cur.EXP3;
      p_nt_essencetrack(I).EXP4 := cur.EXP4;
      p_nt_essencetrack(I).EXP5 := cur.EXP5;
    
      i := i + 1;
    end loop;
    return p_nt_essencetrack;
  end;

  --获取代理人各月预收业绩
  function getpreresult(p_agentid in varchar2, p_startyearmonth in date)
    return nt_essencetrack is
    p_nt_essencetrack nt_essencetrack;
    i                 smallint := 1;
    p_start           date; --开始日期
    p_end             date; --结束日期
    p_yearmonth       date; --未入围年月
    p_endyearmonth    date; --结束年月
  begin
    p_nt_essencetrack := new nt_essencetrack();
    p_yearmonth       := p_startyearmonth;
  
    if extract(day from sysdate - 1) > 25 then
      p_endyearmonth := trunc(add_months(sysdate - 1, 1), 'month');
    else
      p_endyearmonth := trunc(sysdate - 1, 'month');
    end if;
  
    while trunc(p_yearmonth, 'month') <= p_endyearmonth loop
      --计算预收开始和结束日期
      p_start := trunc(add_months(p_yearmonth, -1), 'month') + 25;
      p_end   := trunc(p_yearmonth, 'month') + 24;
      p_nt_essencetrack.extend;
      p_nt_essencetrack(i) := new ob_essencetrack();
      p_nt_essencetrack(i).agentid := p_agentid;
      p_nt_essencetrack(i).exp2 := trunc(p_yearmonth, 'month');
    
      --计算该月业绩
      begin
        select nvl(sum(co.valueprem), 0)
          into p_nt_essencetrack(i).exp1
          from contract co
         where trunc(co.acceptdate, 'dd') >= trunc(p_start, 'dd')
           and trunc(co.acceptdate, 'dd') <= trunc(p_end, 'dd')
           and co.agentid = p_agentid
           and co.ascriptionmode = 'A'
           and co.businesstype = 'L'
           and not exists (select contracteid
                  from contractex
                 where selholdfalg = 'HKBOOLEAN_1'
                   and contracteid = co.contractid);
      exception
        when others then
          p_nt_essencetrack(i).exp1 := 0;
      end;
      p_yearmonth := add_months(p_yearmonth, 1);
      i           := i + 1;
    end loop;
    return p_nt_essencetrack;
  end;

  --获取代理人各月回单业绩
  function getfactresult(p_agentid in varchar2, p_startyearmonth in date)
    return nt_essencetrack is
    p_nt_essencetrack nt_essencetrack;
    p_yearmonth       date;
    i                 smallint := 1;
  begin
    p_nt_essencetrack := new nt_essencetrack();
    p_yearmonth       := p_startyearmonth;
    while trunc(p_yearmonth, 'month') <= trunc(sysdate - 1, 'month') loop
      p_nt_essencetrack.extend;
      p_nt_essencetrack(i) := new ob_essencetrack();
      p_nt_essencetrack(i).agentid := p_agentid;
      p_nt_essencetrack(i).exp2 := trunc(p_yearmonth, 'month');
    
      --计算该月业绩
      begin
        select nvl(sum(co.valueprem), 0)
          into p_nt_essencetrack(i).exp1
          from contract co
         where trunc(co.policyreturndate, 'month') =
               trunc(p_yearmonth, 'month')
           and co.agentid = p_agentid
           and co.ascriptionmode = 'A'
           and co.businesstype = 'L'
           and co.contractstatus = 'CONTRACTSTATUS_C2'
           and not exists (select contracteid
                  from contractex
                 where selholdfalg = 'HKBOOLEAN_1'
                   and contracteid = co.contractid);
      exception
        when others then
          p_nt_essencetrack(i).exp1 := 0;
      end;
    
      p_yearmonth := add_months(p_yearmonth, 1);
      i           := i + 1;
    end loop;
    return p_nt_essencetrack;
  end;

  --2012年准股东奖励方案追踪表（查询数据）
  function getshareholdersrewardtrack(p_branchid  in varchar2,
                                      p_cbranchid in varchar2,
                                      p_ubranchid in varchar2,
                                      p_agentcode in varchar2)
    return nt_shareholdersrewardtrack is
    p_nt_shareholdersrewardtrack   nt_shareholdersrewardtrack;
    i                              smallint := 1;
    temp_agentcode                 varchar2(64);
    Thirdquarterviolationpolicount integer;
    fouthquarterviolationpolicount integer;
    policount1                     integer;
    policount2                     integer;
    temp_BRANCHNAME                VARCHAR2(64);
  
    temp_AGENTNAME         varchar2(64);
    temp_AGENTSTATUS       varchar2(64);
    temp_IDNO              varchar2(64);
    temp_THIRDQUARTERPOLI  NUMBER(15);
    temp_FOURTHQUARTERPOLI NUMBER(15);
    temp_VALUEPREM         NUMBER(15, 2);
    temp_AGENTLEVELNAME    VARCHAR2(64);
    temp_HIREDATE          varchar2(32);
    temp_UBRANCHNAME       VARCHAR2(64);
    temp_CONTACTADDR       varchar2(128);
    temp_CONTACTPHONE      varchar2(64);
  
    temp_thirteenmonthrate NUMBER(15, 4) := 0;
    temp_fourteenmonthrate NUMBER(15, 4) := 0;
    temp_name              varchar2(32);
  begin
    p_nt_shareholdersrewardtrack := new nt_shareholdersrewardtrack();
  
    for cur in (select a.agentcode
                  from agentbroker a
                 where (p_agentcode is null or
                       a.agentcode = replace(p_agentcode, ' ', ''))
                   and (p_branchid is null or a.branchid = p_branchid)
                   and (p_cbranchid is null or a.cbranchid = p_cbranchid)
                   and (p_ubranchid is null or a.ubranchid = p_ubranchid)) loop
      temp_agentcode := cur.agentcode;
    
      --将各季度的违规单子存入tmp_contract表
      inserttmpcontractid(temp_agentcode, '三季度');
      inserttmpcontractid(temp_agentcode, '四季度');
      inserttmpcontractid(temp_agentcode, '其他季度');
    
      --获得各季度代理人在第一种违规拆单情况下的保单归属个数
      policount1 := getcontractcount(temp_agentcode, '三季度');
      policount2 := getcontractcount(temp_agentcode, '四季度');
    
      --获得各季度代理人在第二种违规拆单情况下的保单归属个数
      Thirdquarterviolationpolicount := getcontractbelongcount(temp_agentcode,
                                                               '三季度');
      fouthquarterviolationpolicount := getcontractbelongcount(temp_agentcode,
                                                               '四季度');
    
      --继续率
      begin
        select agentname
          into temp_name
          from agentbroker
         where agentcode = temp_agentcode;
      exception
        when others then
          temp_name := null;
          dbms_output.put_line('继续率查询代理人姓名时出错' || temp_agentcode);
      end;
    
      begin
        select renewrate
          into temp_thirteenmonthrate
          from (select *
                  from table(pkg_operation_renewrate.calcRenewRate('',
                                                                   'personal',
                                                                   'm13',
                                                                   '201201',
                                                                   '201212',
                                                                   temp_name,
                                                                   'CHANNELTYPE_A')))
         where agentid is not null;
      
      exception
        when others then
          temp_thirteenmonthrate := 0;
          dbms_output.put_line('13月继续率查询时出错' || temp_agentcode);
        
      end;
    
      begin
        select renewrate
          into temp_fourteenmonthrate
          from (select *
                  from table(pkg_operation_renewrate.calcRenewRate('',
                                                                   'personal',
                                                                   'm25',
                                                                   '201201',
                                                                   '201212',
                                                                   temp_name,
                                                                   'CHANNELTYPE_A')))
         where agentid is not null;
      
      exception
        when others then
          temp_fourteenmonthrate := 0;
          dbms_output.put_line('25月继续率查询时出错' || temp_agentcode);
        
      end;
    
      begin
        select c.branchname,
               a.agentcode,
               a.agentname,
               f.chnname agentstatus,
               b.idno,
               /*
               三季度新寿险保单件数（价保≥500）,不含自保件，考虑拆单情况
               */
               --正常保单数
               ((select count(1)
                   from contract a, agentbroker b, policy d
                  where d.contractid = a.contractid
                    and a.agentid = b.agentid
                    and a.applicantname != b.agentname
                    and d.insuredname != b.agentname
                    and a.businesstype = 'L'
                    and a.policyreturndate >=
                        to_date('20120701', 'yyyy/MM/dd')
                    and a.policyreturndate <
                        to_date('20121001', 'yyyy/MM/dd')
                    and a.valueprem >= 500
                    and a.agentcode = temp_agentcode
                    and a.contractid not in
                        (select a.contractid
                           from tmp_contractid a
                          where a.quarter = '三季度'
                            and a.agentcode = temp_agentcode)) +
               --违规拆单一的保单数
               (policount1) +
               --违规拆单二的保单数
               Thirdquarterviolationpolicount) Thirdquarterpoli,
               
               /*
               四季度新寿险保单件数（价保≥500）,不含自保件，考虑拆单情况  
               */
               --正常保单数  
               ((select count(1)
                   from contract a, agentbroker b, policy d
                  where d.contractid = a.contractid
                    and a.agentid = b.agentid
                    and a.applicantname != b.agentname
                    and d.insuredname != b.agentname
                    and a.businesstype = 'L'
                    and a.policyreturndate >=
                        to_date('20121001', 'yyyy/MM/dd')
                    and a.policyreturndate <
                        to_date('20130101', 'yyyy/MM/dd')
                    and a.valueprem >= 500
                    and a.agentcode = temp_agentcode
                    and a.contractid not in
                        (select a.contractid
                           from tmp_contractid a
                          where a.quarter = '四季度'
                            and a.agentcode = temp_agentcode)) +
               --违规拆单一的保单数
               (policount2) +
               --违规拆单二的保单数
               fouthquarterviolationpolicount) fouthquarterpoli,
               
               --2012年新单价保
               ((select NVL(sum(a.valueprem), 0)
                   from contract a, agentbroker b, policy d
                  where d.contractid = a.contractid
                    and a.agentid = b.agentid
                    and a.applicantname != b.agentname
                    and d.insuredname != b.agentname
                    and a.policyreturndate >=
                        to_date('20120101', 'yyyy/MM/dd')
                    and a.policyreturndate <
                        to_date('20130101', 'yyyy/MM/dd')
                    and a.agentcode = temp_agentcode
                    and a.contractid not in
                        (select a.contractid
                           from tmp_contractid a
                          where a.agentcode = temp_agentcode)) +
               --拆分情况下违规保单一价保
               (select NVL(sum(a.valueprem), 0)
                   from tmp_contractid a
                  where a.agentcode = temp_agentcode
                    and a.splitconttype = 'A') +
               --拆分情况下违规保单二价保
               NVL(pkg_agent_essence.getcontractbelongcount(temp_agentcode,
                                                             null),
                    0)) valueprem,
               h.agentlevelname,
               to_char(a.HIREDATE, 'yyyy/MM/dd'),
               e.branchname ubranchname,
               b.CONTACTADDR,
               b.CONTACTPHONE
          into temp_BRANCHNAME,
               temp_AGENTCODE,
               temp_AGENTNAME,
               temp_AGENTSTATUS,
               temp_IDNO,
               temp_THIRDQUARTERPOLI,
               temp_FOURTHQUARTERPOLI,
               temp_VALUEPREM,
               temp_AGENTLEVELNAME,
               temp_HIREDATE,
               temp_UBRANCHNAME,
               temp_CONTACTADDR,
               temp_CONTACTPHONE
          from agentbroker    a,
               customer       b,
               branch         c,
               branch         e,
               syscode        f,
               caseagentlevel h
         where a.agentstatus = f.recordid
           and a.customerid = b.customerid
           and a.branchid = c.branchid
           and a.ubranchid = e.branchid
           and a.agentlevelcode = h.agentlevelcode
           and a.agentcode = temp_agentcode;
      exception
        when others then
          temp_BRANCHNAME        := null;
          temp_AGENTCODE         := null;
          temp_AGENTNAME         := null;
          temp_AGENTSTATUS       := null;
          temp_IDNO              := null;
          temp_THIRDQUARTERPOLI  := null;
          temp_FOURTHQUARTERPOLI := null;
          temp_VALUEPREM         := null;
          temp_AGENTLEVELNAME    := null;
          temp_HIREDATE          := null;
          temp_UBRANCHNAME       := null;
          temp_CONTACTADDR       := null;
          temp_CONTACTPHONE      := null;
          dbms_output.put_line('代理人查询：' || temp_AGENTCODE || sqlerrm);
      end;
      p_nt_shareholdersrewardtrack.extend;
      p_nt_shareholdersrewardtrack(i) := new ob_shareholdersrewardtrack();
    
      p_nt_shareholdersrewardtrack(i).BRANCHNAME := temp_BRANCHNAME;
      p_nt_shareholdersrewardtrack(i).AGENTCODE := temp_AGENTCODE;
      p_nt_shareholdersrewardtrack(i).AGENTNAME := temp_AGENTNAME;
      p_nt_shareholdersrewardtrack(i).AGENTSTATUS := temp_AGENTSTATUS;
      p_nt_shareholdersrewardtrack(i).IDNO := temp_IDNO;
      p_nt_shareholdersrewardtrack(i).THIRDQUARTERPOLI := temp_THIRDQUARTERPOLI;
      p_nt_shareholdersrewardtrack(i).FOURTHQUARTERPOLI := temp_FOURTHQUARTERPOLI;
      p_nt_shareholdersrewardtrack(i).THIRTEENMONTHRATE := temp_thirteenmonthrate;
      p_nt_shareholdersrewardtrack(i).FOURTEENMONTHRATE := temp_fourteenmonthrate;
      p_nt_shareholdersrewardtrack(i).ATTENDANCE := null; --出勤
      p_nt_shareholdersrewardtrack(i).VALUEPREM := temp_VALUEPREM;
      p_nt_shareholdersrewardtrack(i).SUBSCRIPTION := null; --认购权份数
      p_nt_shareholdersrewardtrack(i).REWARDSMONEY := null; --奖励金额
      p_nt_shareholdersrewardtrack(i).AGENTLEVELNAME := temp_AGENTLEVELNAME;
      p_nt_shareholdersrewardtrack(i).HIREDATE := temp_HIREDATE;
      p_nt_shareholdersrewardtrack(i).UBRANCHNAME := temp_UBRANCHNAME;
      p_nt_shareholdersrewardtrack(i).CONTACTADDR := temp_CONTACTADDR;
      p_nt_shareholdersrewardtrack(i).CONTACTPHONE := temp_CONTACTPHONE;
      i := i + 1;
    
    end loop;
    return p_nt_shareholdersrewardtrack;
  
  exception
    when others then
      dbms_output.put_line(temp_agentcode || '----' || sqlerrm);
    
  end;

  --将拆单情况的保单ID插入到表tmp_contractid
  procedure inserttmpcontractid(p_agentcode in varchar2,
                                p_QUARTER   in varchar2) is
    p_contractid       varchar2(32);
    temp_a             varchar2(32);
    temp_agentcode     varchar2(32);
    p_approvedate      date;
    p_bizacceptid      varchar2(32);
    p_policyreturndate date;
    --p_valueprem        number(15, 2);
  begin
    --三季度的拆单保单
    if (p_QUARTER = '三季度') then
      --第一种类型的拆单保单
      for cur in (select a.policyreturndate,
                         c.productid,
                         e.applicantid,
                         b.insuredname,
                         a.agentcode
                    from contract    a,
                         product     c,
                         coverage    d,
                         policy      b,
                         applicant   e,
                         agentbroker f
                   where a.contractid = d.contractid
                     and c.productid = d.productid
                     and a.contractid = b.contractid
                     and a.applicantid = e.applicantid
                     and a.agentid = f.agentid
                     and e.customername != f.agentname
                     and b.insuredname != f.agentname
                     and a.businesstype = 'L'
                     and a.policyreturndate >=
                         to_date('20120701', 'yyyy/MM/dd')
                     and a.policyreturndate <
                         to_date('20121001', 'yyyy/MM/dd')
                     and a.valueprem >= 500
                     and a.agentcode = p_agentcode
                   group by policyreturndate,
                            a.agentcode,
                            c.productid,
                            e.applicantid,
                            b.insuredname
                  having(count(distinct a.agentcode) = count(distinct e.applicantid) or count(distinct a.agentcode) = count(distinct b.insuredname)) and count(distinct c.productid) = count(distinct a.agentcode) and count(distinct c.productid) = count(distinct to_char(a.policyreturndate, 'yyyymm')) and count(distinct a.agentcode) = 1 and count(distinct a.contractid) > 1) loop
      
        for cur2 in (select distinct a.contractid, a.valueprem
                       from contract a, policy b, coverage c, product d
                      where a.contractid = c.contractid
                        and a.contractid = b.contractid
                        and c.productid = d.productid
                        and (a.applicantid = cur.applicantid or
                            b.insuredname = cur.insuredname)
                        and a.policyreturndate = cur.policyreturndate
                        and a.agentcode = cur.agentcode
                        and d.productid = cur.productid) loop
          p_contractid := cur2.contractid;
          --假如临时表中有此保单的数据，获得保单临时表的主键ID
          begin
            select tmpcon
              into temp_a
              from tmp_contractid
             where contractid = p_contractid
               and rownum = 1;
          exception
            when others then
              temp_a := null;
              dbms_output.put_line(cur.agentcode || '  ' || sqlerrm);
          end;
        
          --没有此保单的数据，才往数据库中插入此保单对应的数据
          begin
            if (temp_a is null) then
              insert into tmp_contractid
              values
                (pkg_seq_proc.gettmpcontractid,
                 p_contractid,
                 p_QUARTER,
                 p_agentcode,
                 null,
                 null,
                 null,
                 null,
                 null,
                 'A',
                 cur2.valueprem);
              commit;
            end if;
          exception
            when others then
              rollback;
              dbms_output.put_line('拆单的单子' || p_contractid || '插入失败' ||
                                   sqlerrm);
          end;
        end loop;
      
      end loop;
    
      --第二种类型的拆单保单
      for cur in (select a.policyreturndate, a.applicantid, b.insuredname
                    from contract a, policy b, applicant c, agentbroker d
                   where a.contractid = b.contractid
                     and a.applicantid = c.applicantid
                     and a.agentid = d.agentid
                     and c.customername != d.agentname
                     and b.insuredname != d.agentname
                     and a.businesstype = 'L'
                     and a.policyreturndate >=
                         to_date('20120701', 'yyyy/MM/dd')
                     and a.policyreturndate <
                         to_date('20121001', 'yyyy/MM/dd')
                     and a.valueprem >= 500
                   group by policyreturndate, a.applicantid, b.insuredname
                  having(count(distinct to_char(a.policyreturndate, 'yyyymm')) = count(distinct a.applicantid) or count(distinct to_char(a.policyreturndate, 'yyyymm')) = count(distinct b.insuredname)) and count(distinct to_char(a.policyreturndate, 'yyyymm')) = 1 and count(distinct a.agentcode) > 1) loop
      
        for cur2 in (select distinct a.contractid,
                                     a.valueprem,
                                     a.agentcode,
                                     a.applicantid,
                                     a.approvedate,
                                     a.policyreturndate,
                                     d.insuredname,
                                     e.bizacceptid
                       from contract a, policy d, bizaccept e
                      where a.contractid = d.contractid
                        and a.contractid = e.contractid
                        and (a.applicantid = cur.applicantid or
                            d.insuredname = cur.insuredname)
                        and a.policyreturndate = cur.policyreturndate) loop
          p_contractid       := cur2.contractid;
          temp_agentcode     := cur2.agentcode;
          p_approvedate      := cur2.approvedate;
          p_bizacceptid      := cur2.bizacceptid;
          p_policyreturndate := cur2.policyreturndate;
          --假如临时表中有此保单的数据，获得保单临时表的主键ID
          begin
            select tmpcon
              into temp_a
              from tmp_contractid
             where contractid = p_contractid
               and rownum = 1;
          exception
            when others then
              temp_a := null;
              dbms_output.put_line(cur2.agentcode || '  ' || sqlerrm);
          end;
        
          --没有此保单的数据，才往数据库中插入此保单对应的数据
          begin
            if (temp_a is null) then
              insert into tmp_contractid
              values
                (pkg_seq_proc.gettmpcontractid,
                 p_contractid,
                 p_QUARTER,
                 temp_agentcode,
                 cur.applicantid,
                 p_approvedate,
                 cur.insuredname,
                 p_bizacceptid,
                 p_policyreturndate,
                 'B',
                 cur2.valueprem);

              commit;
            end if;
          exception
            when others then
              rollback;
              dbms_output.put_line('拆单的单子' || p_contractid || '插入失败' ||
                                   sqlerrm);
          end;
        end loop;
      end loop;
    
      --四季度的拆单保单
    elsif (p_QUARTER = '四季度') then
      --第一种类型的拆单保单
      for cur in (select a.policyreturndate,
                         c.productid,
                         e.applicantid,
                         b.insuredname,
                         a.agentcode
                    from contract    a,
                         product     c,
                         coverage    d,
                         policy      b,
                         applicant   e,
                         agentbroker f
                   where a.contractid = d.contractid
                     and c.productid = d.productid
                     and a.contractid = b.contractid
                     and a.applicantid = e.applicantid
                     and a.agentid = f.agentid
                     and e.customername != f.agentname
                     and b.insuredname != f.agentname
                     and a.businesstype = 'L'
                     and a.policyreturndate >=
                         to_date('20121001', 'yyyy/MM/dd')
                     and a.policyreturndate <
                         to_date('20130101', 'yyyy/MM/dd')
                     and a.valueprem >= 500
                     and a.agentcode = p_agentcode
                   group by policyreturndate,
                            a.agentcode,
                            c.productid,
                            e.applicantid,
                            b.insuredname
                  having(count(distinct a.agentcode) = count(distinct e.applicantid) or count(distinct a.agentcode) = count(distinct b.insuredname)) and count(distinct c.productid) = count(distinct a.agentcode) and count(distinct c.productid) = count(distinct to_char(a.policyreturndate, 'yyyymm')) and count(distinct a.agentcode) = 1 and count(distinct a.contractid) > 1) loop
      
        for cur2 in (select distinct a.contractid, a.valueprem
                       from contract a, policy b, coverage c, product d
                      where a.contractid = c.contractid
                        and a.contractid = b.contractid
                        and c.productid = d.productid
                        and (a.applicantid = cur.applicantid or
                            b.insuredname = cur.insuredname)
                        and a.policyreturndate = cur.policyreturndate
                        and a.agentcode = cur.agentcode
                        and d.productid = cur.productid) loop
          p_contractid := cur2.contractid;
        
          --假如临时表中有此保单的数据，获得保单临时表的主键ID
          begin
            select tmpcon
              into temp_a
              from tmp_contractid
             where contractid = p_contractid
               and rownum = 1;
          exception
            when others then
              temp_a := null;
              dbms_output.put_line(cur.agentcode || '  ' || sqlerrm);
          end;
        
          --没有此保单的数据，才往数据库中插入此保单对应的数据
          begin
            if (temp_a is null) then
              insert into tmp_contractid
              values
                (pkg_seq_proc.gettmpcontractid,
                 p_contractid,
                 p_QUARTER,
                 p_agentcode,
                 null,
                 null,
                 null,
                 null,
                 null,
                 'A',
                 cur2.valueprem);
              commit;
            end if;
          exception
            when others then
              rollback;
              dbms_output.put_line('拆单的单子' || p_contractid || '插入失败' ||
                                   sqlerrm);
          end;
        
        end loop;
      
      end loop;
    
      --第二种类型的拆单保单
      for cur in (select a.policyreturndate, a.applicantid, b.insuredname
                    from contract a, policy b, applicant c, agentbroker d
                   where a.contractid = b.contractid
                     and a.applicantid = c.applicantid
                     and a.agentid = d.agentid
                     and c.customername != d.agentname
                     and b.insuredname != d.agentname
                     and a.businesstype = 'L'
                     and a.policyreturndate >=
                         to_date('20121001', 'yyyy/MM/dd')
                     and a.policyreturndate <
                         to_date('20130101', 'yyyy/MM/dd')
                     and a.valueprem >= 500
                   group by policyreturndate, a.applicantid, b.insuredname
                  having(count(distinct to_char(a.policyreturndate, 'yyyymm')) = count(distinct a.applicantid) or count(distinct to_char(a.policyreturndate, 'yyyymm')) = count(distinct b.insuredname)) and count(distinct to_char(a.policyreturndate, 'yyyymm')) = 1 and count(distinct a.agentcode) > 1) loop
      
        for cur2 in (select distinct a.contractid,
                                     a.valueprem,
                                     a.agentcode,
                                     a.applicantid,
                                     a.approvedate,
                                     a.policyreturndate,
                                     d.insuredname,
                                     e.bizacceptid
                       from contract a, policy d, bizaccept e
                      where a.contractid = d.contractid
                        and a.contractid = e.contractid
                        and (a.applicantid = cur.applicantid or
                            d.insuredname = cur.insuredname)
                        and a.policyreturndate = cur.policyreturndate) loop
          p_contractid       := cur2.contractid;
          temp_agentcode     := cur2.agentcode;
          p_approvedate      := cur2.approvedate;
          p_bizacceptid      := cur2.bizacceptid;
          p_policyreturndate := cur2.policyreturndate;
          --假如临时表中有此保单的数据，获得保单临时表的主键ID
          begin
            select tmpcon
              into temp_a
              from tmp_contractid
             where contractid = p_contractid
               and rownum = 1;
          exception
            when others then
              temp_a := null;
              dbms_output.put_line(cur2.agentcode || '  ' || sqlerrm);
          end;
        
          --没有此保单的数据，才往数据库中插入此保单对应的数据
          begin
            if (temp_a is null) then
              insert into tmp_contractid
              values
                (pkg_seq_proc.gettmpcontractid,
                 p_contractid,
                 p_QUARTER,
                 temp_agentcode,
                 cur.applicantid,
                 p_approvedate,
                 cur.insuredname,
                 p_bizacceptid,
                 p_policyreturndate,
                 'B',
                 cur2.valueprem);
              commit;
            end if;
          exception
            when others then
              rollback;
              dbms_output.put_line('拆单的单子' || p_contractid || '插入失败' ||
                                   sqlerrm);
          end;
        
        end loop;
      end loop;
    
    else
      --第一种类型的拆单保单
      for cur in (select a.policyreturndate,
                         c.productid,
                         e.applicantid,
                         b.insuredname,
                         a.agentcode
                    from contract    a,
                         product     c,
                         coverage    d,
                         policy      b,
                         applicant   e,
                         agentbroker f
                   where a.contractid = d.contractid
                     and c.productid = d.productid
                     and a.contractid = b.contractid
                     and a.applicantid = e.applicantid
                     and a.agentid = f.agentid
                     and e.customername != f.agentname
                     and b.insuredname != f.agentname
                     and a.businesstype = 'L'
                     and a.policyreturndate >=
                         to_date('20120101', 'yyyy/MM/dd')
                     and a.policyreturndate <
                         to_date('20120701', 'yyyy/MM/dd')
                     and a.valueprem >= 500
                     and a.agentcode = p_agentcode
                   group by policyreturndate,
                            a.agentcode,
                            c.productid,
                            e.applicantid,
                            b.insuredname
                  having(count(distinct a.agentcode) = count(distinct e.applicantid) or count(distinct a.agentcode) = count(distinct b.insuredname)) and count(distinct c.productid) = count(distinct a.agentcode) and count(distinct c.productid) = count(distinct to_char(a.policyreturndate, 'yyyymm')) and count(distinct a.agentcode) = 1 and count(distinct a.contractid) > 1) loop
      
        for cur2 in (select distinct a.contractid, a.valueprem
                       from contract a, policy b, coverage c, product d
                      where a.contractid = c.contractid
                        and a.contractid = b.contractid
                        and c.productid = d.productid
                        and (a.applicantid = cur.applicantid or
                            b.insuredname = cur.insuredname)
                        and a.policyreturndate = cur.policyreturndate
                        and a.agentcode = cur.agentcode
                        and d.productid = cur.productid) loop
          p_contractid := cur2.contractid;
        
          --假如临时表中有此保单的数据，获得保单临时表的主键ID
          begin
            select tmpcon
              into temp_a
              from tmp_contractid
             where contractid = p_contractid
               and rownum = 1;
          exception
            when others then
              temp_a := null;
              dbms_output.put_line(cur.agentcode || '  ' || sqlerrm);
          end;
        
          --没有此保单的数据，才往数据库中插入此保单对应的数据
          begin
            if (temp_a is null) then
              insert into tmp_contractid
              values
                (pkg_seq_proc.gettmpcontractid,
                 p_contractid,
                 p_QUARTER,
                 p_agentcode,
                 null,
                 null,
                 null,
                 null,
                 null,
                 'A',
                 cur2.valueprem);
              commit;
            end if;
          exception
            when others then
              rollback;
              dbms_output.put_line('拆单的单子' || p_contractid || '插入失败' ||
                                   sqlerrm);
          end;
        
        end loop;
      
      end loop;
    
      --第二种类型的拆单保单
      for cur in (select a.policyreturndate, a.applicantid, b.insuredname
                    from contract a, policy b, applicant c, agentbroker d
                   where a.contractid = b.contractid
                     and a.applicantid = c.applicantid
                     and a.agentid = d.agentid
                     and c.customername != d.agentname
                     and b.insuredname != d.agentname
                     and a.businesstype = 'L'
                     and a.policyreturndate >=
                         to_date('20120101', 'yyyy/MM/dd')
                     and a.policyreturndate <
                         to_date('20120701', 'yyyy/MM/dd')
                     and a.valueprem >= 500
                   group by policyreturndate, a.applicantid, b.insuredname
                  having(count(distinct to_char(a.policyreturndate, 'yyyymm')) = count(distinct a.applicantid) or count(distinct to_char(a.policyreturndate, 'yyyymm')) = count(distinct b.insuredname)) and count(distinct to_char(a.policyreturndate, 'yyyymm')) = 1 and count(distinct a.agentcode) > 1) loop
      
        for cur2 in (select distinct a.contractid,
                                     a.agentcode,
                                     a.applicantid,
                                     a.approvedate,
                                     a.policyreturndate,
                                     d.insuredname,
                                     e.bizacceptid,
                                     a.valueprem
                       from contract a, policy d, bizaccept e
                      where a.contractid = d.contractid
                        and a.contractid = e.contractid
                        and (a.applicantid = cur.applicantid or
                            d.insuredname = cur.insuredname)
                        and a.policyreturndate = cur.policyreturndate) loop
          p_contractid       := cur2.contractid;
          temp_agentcode     := cur2.agentcode;
          p_approvedate      := cur2.approvedate;
          p_bizacceptid      := cur2.bizacceptid;
          p_policyreturndate := cur2.policyreturndate;
          --假如临时表中有此保单的数据，获得保单临时表的主键ID
          begin
            select tmpcon
              into temp_a
              from tmp_contractid
             where contractid = p_contractid
               and rownum = 1;
          exception
            when others then
              temp_a := null;
              dbms_output.put_line(cur2.agentcode || '  ' || sqlerrm);
          end;
        
          --没有此保单的数据，才往数据库中插入此保单对应的数据
          begin
            if (temp_a is null) then
              insert into tmp_contractid
              values
                (pkg_seq_proc.gettmpcontractid,
                 p_contractid,
                 p_QUARTER,
                 temp_agentcode,
                 cur.applicantid,
                 p_approvedate,
                 cur.insuredname,
                 p_bizacceptid,
                 p_policyreturndate,
                 'B',
                 cur2.valueprem);
              commit;
            end if;
          exception
            when others then
              rollback;
              dbms_output.put_line('拆单的单子' || p_contractid || '插入失败' ||
                                   sqlerrm);
          end;
        
        end loop;
      end loop;
    end if;
  
  end;

  ----获得拆分情况下不同代理人的保单的归属人及应得的价保
  function getcontractbelongcount(p_agentcode in varchar2,
                                  p_QUARTER   in varchar2) return number is
    ret_count number := 0;
  
    p_applicantid            varchar2(32);
    p_insuredname            varchar2(32);
    p_policyreturndate_month varchar2(32);
    temp_bizacceptid         varchar2(32);
    temp_agentcode           varchar2(32);
    temp_valueprem           number(15, 2);
  begin
    if (p_QUARTER is not null) then
      for cur in (select distinct a.applicantid,
                                  a.insuredname,
                                  to_char(a.policyreturndate, 'yyyymm') policyreturndate_month
                  
                    from tmp_contractid a
                   where a.agentcode = p_agentcode
                     and a.quarter = p_QUARTER
                     and a.splitconttype = 'B') loop
      
        p_applicantid            := cur.applicantid;
        p_insuredname            := cur.insuredname;
        p_policyreturndate_month := cur.policyreturndate_month;
      
        if (p_applicantid is not null and p_insuredname is not null and
           p_policyreturndate_month is not null) then
        
          begin
            select min(bizacceptid)
              into temp_bizacceptid
              from tmp_contractid a
             where a.quarter = p_quarter
               and (a.applicantid = p_applicantid OR
                   A.INSUREDNAME = p_insuredname)
               and to_char(a.policyreturndate, 'yyyymm') =
                   p_policyreturndate_month
               and a.splitconttype = 'B';
          exception
            when others then
              temp_bizacceptid := null;
              dbms_output.put_line(p_agentcode || p_QUARTER || '  ' ||
                                   sqlerrm);
          end;
        
          if (temp_bizacceptid is not null) then
          
            begin
              select agentcode
                into temp_agentcode
                from tmp_contractid a
               where a.bizacceptid = temp_bizacceptid
                 and rownum = 1;
            exception
              when others then
                temp_agentcode := null;
                dbms_output.put_line(p_agentcode || p_QUARTER || '  ' ||
                                     sqlerrm);
            end;
          
            if (temp_agentcode is not null and temp_agentcode = p_agentcode) then
            
              ret_count := ret_count + 1;
            
            end if;
          end if;
        end if;
      
      end loop;
      return ret_count;
    else
      for cur in (select distinct a.applicantid,
                                  a.insuredname,
                                  to_char(a.policyreturndate, 'yyyymm') policyreturndate_month
                    from tmp_contractid a
                   where a.agentcode = p_agentcode
                     and a.splitconttype = 'B') loop
      
        p_applicantid            := cur.applicantid;
        p_insuredname            := cur.insuredname;
        p_policyreturndate_month := cur.policyreturndate_month;
      
        if (p_applicantid is not null and p_insuredname is not null and
           p_policyreturndate_month is not null) then
          begin
            select min(bizacceptid)
              into temp_bizacceptid
              from tmp_contractid a
             where (a.applicantid = p_applicantid OR
                   A.INSUREDNAME = p_insuredname)
               and to_char(a.policyreturndate, 'yyyymm') =
                   p_policyreturndate_month
               and a.splitconttype = 'B';
          exception
            when others then
              temp_bizacceptid := null;
              dbms_output.put_line(p_agentcode || p_QUARTER || '  ' ||
                                   sqlerrm);
          end;
        
          if (temp_bizacceptid is not null) then
            begin
              select agentcode, a.valueprem
                into temp_agentcode, temp_valueprem
                from tmp_contractid a
               where a.bizacceptid = temp_bizacceptid
                 and rownum = 1;
            exception
              when others then
                temp_agentcode := null;
                temp_valueprem := 0;
                dbms_output.put_line(p_agentcode || p_QUARTER || '  ' ||
                                     sqlerrm);
            end;
          
            if (temp_agentcode is not null and temp_agentcode = p_agentcode) then
              ret_count := ret_count + temp_valueprem;
            
            end if;
          end if;
        end if;
      
      end loop;
      return ret_count;
    end if;
  
  exception
    when others then
      dbms_output.put_line('获得不同代理人保单归属失败' || p_agentcode || sqlerrm);
  end;

  --获得拆分情况下相同的代理人的保单的归属件数
  function getcontractcount(p_agentcode in varchar2, p_QUARTER in varchar2)
    return number is
    ret_number number;
  begin
    if (p_QUARTER = '三季度') then
      begin
        select count(1)
          into ret_number
          from (select a.policyreturndate,
                       c.productid,
                       e.applicantid,
                       b.insuredname,
                       a.agentcode
                  from contract    a,
                       product     c,
                       coverage    d,
                       policy      b,
                       applicant   e,
                       agentbroker f
                 where a.contractid = d.contractid
                   and c.productid = d.productid
                   and a.contractid = b.contractid
                   and a.applicantid = e.applicantid
                   and a.agentid = f.agentid
                   and e.customername != f.agentname
                   and b.insuredname != f.agentname
                   and a.businesstype = 'L'
                   and a.policyreturndate >=
                       to_date('20120701', 'yyyy/MM/dd')
                   and a.policyreturndate <
                       to_date('20121001', 'yyyy/MM/dd')
                   and a.valueprem >= 500
                   and a.agentcode = p_agentcode
                 group by policyreturndate,
                          a.agentcode,
                          c.productid,
                          e.applicantid,
                          b.insuredname
                having(count(distinct a.agentcode) = count(distinct e.applicantid) or count(distinct a.agentcode) = count(distinct b.insuredname)) and count(distinct c.productid) = count(distinct a.agentcode) and count(distinct c.productid) = count(distinct to_char(a.policyreturndate, 'yyyymm')) and count(distinct a.agentcode) = 1 and count(distinct a.contractid) > 1);
      exception
        when others then
          dbms_output.put_line('查询代理人' || p_agentcode || '的' || p_QUARTER ||
                               '的保单归属件数出错');
      end;
    else
      begin
        select count(1)
          into ret_number
          from (select a.policyreturndate,
                       c.productid,
                       e.applicantid,
                       b.insuredname,
                       a.agentcode
                  from contract    a,
                       product     c,
                       coverage    d,
                       policy      b,
                       applicant   e,
                       agentbroker f
                 where a.contractid = d.contractid
                   and c.productid = d.productid
                   and a.contractid = b.contractid
                   and a.applicantid = e.applicantid
                   and a.agentid = f.agentid
                   and e.customername != f.agentname
                   and b.insuredname != f.agentname
                   and a.businesstype = 'L'
                   and a.policyreturndate >=
                       to_date('20121001', 'yyyy/MM/dd')
                   and a.policyreturndate <
                       to_date('20130101', 'yyyy/MM/dd')
                   and a.valueprem >= 500
                   and a.agentcode = p_agentcode
                 group by policyreturndate,
                          a.agentcode,
                          c.productid,
                          e.applicantid,
                          b.insuredname
                having(count(distinct a.agentcode) = count(distinct e.applicantid) or count(distinct a.agentcode) = count(distinct b.insuredname)) and count(distinct c.productid) = count(distinct a.agentcode) and count(distinct c.productid) = count(distinct to_char(a.policyreturndate, 'yyyymm')) and count(distinct a.agentcode) = 1 and count(distinct a.contractid) > 1);
      exception
        when others then
          dbms_output.put_line('查询代理人' || p_agentcode || '的' || p_QUARTER ||
                               '的保单归属件数出错');
      end;
    
    end if;
    return ret_number;
  
  exception
    when others then
      dbms_output.put_line('查询代理人' || p_agentcode || '的保单归属件数出错');
  end;
end pkg_agent_essence;
/