test.txt

 <plugin>
		        <groupId>org.apache.maven.plugins</groupId>
		        <artifactId>maven-surefire-plugin</artifactId>
		        <version>2.18.1</version>
		        <configuration>
		          <skipTests>true</skipTests>
		        </configuration>
		      </plugin>
		      
		      currentpolicyaddress
		      
		      ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
		      
		      

select 
    rpsi.POLICY_NO policyNo,rpsi.service_no servicerNo,po.BRANCH_CODE branchCode,si.EMP_NAME empName,
    po.CHANNEL_TYPE AS channelType,bi.BRANCH_NAME branchName,ppi.PREM_DUE_DATE premDate,ctl.DESCRIPTION AS description
     
    from policy po
      inner join   rn_policy_service_info rpsi on  rpsi.policy_no=po.policy_no
      inner join   branch_code_relation bcr on bcr.branch_code=po.branch_code
      inner join   policy_prem_info ppi on ppi.policy_no =po.policy_no
      left join   staff_info si on si.emp_no = rpsi.service_no
      inner join   CHANNEL_TYPE_TBL CTL on CTL.CHANNEL_TYPE = PO.CHANNEL_TYPE
      inner join   branch_info bi  on  bi.branch_code = po.branch_code
      left join gras_assign_failed_record gfr  on gfr.policy_no=rpsi.policy_no 
    where 
    bcr.relative_grade>-1
    and gfr.policy_no is null
		and bcr.relative_branch_code = '864200'
		AND ppi.PREM_DUE_DATE >= "TO_DATE"('2018-12-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
		AND ppi.PREM_DUE_DATE <= "TO_DATE"('2018-12-30 00:00:00','yyyy-MM-dd hh24:mi:ss')
    and po.CHANNEL_TYPE in('01','02','03','04','05','07','09');
    
    
    
    
    select 
count(*)
     from policy po
      inner join   rn_policy_service_info rpsi on  rpsi.policy_no=po.policy_no
      inner join   branch_code_relation bcr on bcr.branch_code=po.branch_code
      inner join   policy_product pp on pp.policy_no=po.policy_no and pp.prod_seq='1'  
      inner join   policy_prem_info ppi on ppi.policy_no =po.policy_no
      inner join   staff_info si on si.emp_no = rpsi.service_no
      inner join   CHANNEL_TYPE_TBL CTL on CTL.CHANNEL_TYPE = PO.CHANNEL_TYPE
      inner join   branch_info bi  on  bi.branch_code = po.branch_code
      left join gras_assign_failed_record gfr  on gfr.policy_no=rpsi.policy_no 
    where 
      bcr.relative_grade>-1
    and gfr.policy_no is  null
    and ppi.prem_status='1'
  
and ppi.prem_source='1'
and pp.prod_seq='1'
    and(rpsi.assign_rule IN ('01', '02') OR rpsi.assign_rule IS NULL)
    and pp.product_code not in('CBAN_TN1','CBAN_UN1','CBAN_VN1')
		and bcr.relative_branch_code = '865104'
		AND ppi.PREM_DUE_DATE >= "TO_DATE"('2018-12-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
		AND ppi.PREM_DUE_DATE < "TO_DATE"('2019-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
    and po.CHANNEL_TYPE in('01','02','03','04','05','07','09');
    
    
    
    
    
    P000000001352005-H000000000005697
    
from policy po
inner join   rn_policy_service_info rpsi on  rpsi.policy_no=po.policy_no
inner join   policy_product pp on pp.policy_no=po.policy_no and pp.prod_seq='1'  
inner join   policy_prem_info ppi on ppi.policy_no =po.policy_no
inner join   branch_code_relation bcr on bcr.branch_code=po.branch_code
inner join   CHANNEL_TYPE_TBL CTL on CTL.CHANNEL_TYPE = PO.CHANNEL_TYPE
--inner join   staff_info si on si.emp_no = rpsi.service_no
inner join   branch_info bi  on  bi.branch_code = po.branch_code
left join gras_assign_failed_record gfr  on gfr.policy_no=rpsi.policy_no  and gfr.is_valid='Y'
where 
bcr.relative_grade>-1
and bcr.relative_branch_code = '864200'
and gfr.policy_no is  null
and ppi.prem_status='1'
and ppi.prem_source='1'
and pp.prod_seq='1'
and rpsi.ASSIGN_BRANCH_CODE is null
and(rpsi.assign_rule IN ('01', '02') OR rpsi.assign_rule IS NULL)
and pp.product_code not in('CBAN_TN1','CBAN_UN1','CBAN_VN1')
and ppi.PREM_DUE_DATE >= "TO_DATE"('2018-12-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
AND ppi.PREM_DUE_DATE < "TO_DATE"('2019-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
AND po.CHANNEL_TYPE not in('06','08','11');


分单结果批量调整满足条件
1.失效件：
（1）保单进入失效月次月至失效日起两年内；
（2）失效原因满足下列其中一项：
     保全：未在规定宽限期内缴纳续期保费；
     保全：借款本金和利息和超过保单现金价值；
     保全：保单的现金价值不足以垫缴保费；
（3）保单渠道与收展服务人员岗位相匹配。
2.缓缴件
（1）保单有效状态的长险（万能险）；
（2）保单渠道与收展服务人员岗位相匹配。





自定义查询保单落图：gras/falling/queryPageData----》FallingMapService.queryPageDatas()
获取分区区域列表：gras/branchGeo/getBranchGeoInfoPageByCode----》grasBranchGeoService.selectBranchGeoInfoPageByCode()
获取区域点面数据：gras/branch/getBranchGeoByBranchCode---》branchInfoService.getBranchGeoByBranchCode()
根据机构代码获取渠道：/gras/branchRegion.getBranchRegion--》grasBranchGeoService.getBranchRegion()

根据机构号查询保单:/gras/resultCheck/selectByBranchCode----》ResultCheckService.selectByBranchCode()

根据传入条件查询保单信息:/gras/resultCheck/queryPolicy--》ResultCheckService.queryPolicy()

FallingMapService.outInResult()
FallingMapService.resultput()
ElasticSearchServiceImpl类
ElasticSearchPolicyServiceImpl类

/gras/menu/updateMenus---》menuService.updateByRoleIdAndMenus()

/gras/resultCheck/getDatasExportToExcel----》ResultCheckService.selectForExcel()

批量调整：gras/resultCheck/adjustEmp----》ResultCheckService.adjustEmp






 1：目前系统中删除操作涉及的表有：GRAS_BRANCH_GEO、GRAS_BRANCH_STYLE、GRAS_AREA_STAFF、GRAS_SYS_ROLE_MENU、GRAS_SYS_MENU、GRAS_ASSIGN_FAILED_RECORD
 
 
 

 
 
 

]863605用户，调整保单号：P000000042641127，收展服务人员：H000000000005697，保单渠道为银代，收展服务人员岗位为银代收展专员


select 

rpsi.POLICY_NO policyNo,rpsi.service_no servicerNo,po.BRANCH_CODE branchCode,si.EMP_NAME empName,
    po.CHANNEL_TYPE AS channelType,bi.BRANCH_NAME branchName,ppi.PREM_DUE_DATE premDate,ctl.DESCRIPTION AS description
from policy po
inner join   rn_policy_service_info rpsi on  rpsi.policy_no=po.policy_no
inner join   policy_product pp on pp.policy_no=po.policy_no and pp.prod_seq='1'  
inner join   policy_prem_info ppi on ppi.policy_no =po.policy_no

inner join   CHANNEL_TYPE_TBL CTL on CTL.CHANNEL_TYPE = PO.CHANNEL_TYPE
inner join   staff_info si on si.emp_no = rpsi.service_no
inner join   branch_info bi  on  bi.branch_code = po.branch_code
left join gras_assign_failed_record gfr  on gfr.policy_no=rpsi.policy_no  and gfr.is_valid='Y'
where 

 gfr.policy_no is  null
and ppi.prem_status='1'
and ppi.prem_source='1'

and(rpsi.assign_rule IN ('01', '02') OR rpsi.assign_rule IS NULL)
and pp.product_code not in('CBAN_TN1','CBAN_UN1','CBAN_VN1')
and ppi.PREM_DUE_DATE >= "TO_DATE"('2018-12-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
AND ppi.PREM_DUE_DATE < "TO_DATE"('2019-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
AND po.CHANNEL_TYPE not in('06','08','11')
and rpsi.ASSIGN_BRANCH_CODE in
(select bcr.branch_code from  branch_code_relation bcr where 
 bcr.relative_grade>-1 and bcr.relative_branch_code='863600');
 
 
 
 
 
 
 
 
 
 
 
 
   select 

rpsi.POLICY_NO policyNo,rpsi.service_no servicerNo,po.BRANCH_CODE branchCode,si.EMP_NAME empName,
    po.CHANNEL_TYPE AS channelType,bi.BRANCH_NAME branchName,ppi.PREM_DUE_DATE premDate,ctl.DESCRIPTION AS description
from policy po
inner join   rn_policy_service_info rpsi on  rpsi.policy_no=po.policy_no
inner join   policy_product pp on pp.policy_no=po.policy_no and pp.prod_seq='1'  
inner join   policy_prem_info ppi on ppi.policy_no =po.policy_no
inner join   CHANNEL_TYPE_TBL CTL on CTL.CHANNEL_TYPE = PO.CHANNEL_TYPE
inner join   staff_info si on si.emp_no = rpsi.service_no
inner join   branch_info bi  on  bi.branch_code = po.branch_code
left join gras_assign_failed_record gfr  on gfr.policy_no=rpsi.policy_no  and gfr.is_valid='Y'
where 

    gfr.policy_no is  null
    and ppi.prem_status='1'
    and ppi.prem_source='1'
    and(rpsi.assign_rule IN ('01', '02') OR rpsi.assign_rule IS NULL)
    and pp.product_code not in('CBAN_TN1','CBAN_UN1','CBAN_VN1')
    AND ppi.PREM_DUE_DATE >= "TO_DATE"(#{nowMonth},'yyyy-MM-dd hh24:mi:ss')
    AND ppi.PREM_DUE_DATE &lt; "TO_DATE"(#{nextMonth},'yyyy-MM-dd hh24:mi:ss')
    <choose>
         <when test="channelType != null and channelType != ''">
                 AND po.channel_type = #{channelType,jdbcType=VARCHAR}
         </when>
         <otherwise>
                AND po.channel_type not in ('06','08','11')
          </otherwise>
    </choose>
    and rpsi.ASSIGN_BRANCH_CODE in
    (select bcr.branch_code from  branch_code_relation bcr where 
    bcr.relative_grade>-1 and bcr.relative_branch_code=#{branchCode,jdbcType=VARCHAR})
    
    
    
    
    
    
    
    
    
    
    
    "UIWL_AN1","UIED_AN1","UIAN_DN1","UIAN_EN1","UBWL_AN1","UBEN_AN1","UBED_CN1"