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 rn_policy_service_info rpsi,policy po,branch_code_relation bcr,
     policy_prem_info ppi,staff_info si,CHANNEL_TYPE_TBL CTL,branch_info bi
    where 
    si.emp_no = rpsi.service_no
		and bi.branch_code = po.branch_code
		and CTL.CHANNEL_TYPE = PO.CHANNEL_TYPE
    and rpsi.policy_no=po.policy_no
    and rpsi.policy_no=ppi.policy_no
    and bcr.branch_code=po.branch_code
    and bcr.relative_grade>-1
		and bcr.relative_branch_code = '864200'
		AND ppi.PREM_DUE_DATE >= "TO_DATE"('2018-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
		AND ppi.PREM_DUE_DATE <= "TO_DATE"('2018-01-30 00:00:00','yyyy-MM-dd hh24:mi:ss')
    and po.CHANNEL_TYPE in('01','02','03','04','05','07','09')




SELECT
	NVL(po.POLICY_NO,'NAN') AS PO_POLICY_NO,--保单叿
	NVL(po.APPLICANT_NO ,'NAN')AS PO_APPLICANT_NO, --投保人客户号
	NVL(po.BRANCH_CODE ,'NAN')AS PO_BRANCH_CODE,--对应机构叿
	NVL(po.DEPARTMENT_NO ,'NAN')AS PO_DEPARTMENT_NO,--部门编号
	NVL(po.DUTY_STATUS ,'NAN')AS PO_DUTY_STATUS,--保单责任状怿
	NVL(po.APL_OPTION ,'NAN')AS PO_APL_OPTION,--自垫选择
	NVL(po.CHANNEL_TYPE ,'NAN')AS PO_CHANNEL_TYPE,--渠道编号
	NVL(po.AGENT_NO,'NAN')AS PO_AGENT_NO,--业务员编砿
	NVL(po.APPLY_NO,'NAN')AS PO_APPLY_NO,--投保单号
	po.APPLY_DATE AS PO_APPLY_DATE,--投保日期
	po.ISSUE_DATE AS PO_ISSUE_DATE,--承保日期
	po.EFFECT_DATE AS PO_EFFECT_DATE,--生效日期
	NVL(po.SERVICE_STATUS,'NAN') AS PO_SERVICE_STATUS,--单服务状怿
	NVL(po.COMBINATION_CODE,'NAN') AS PO_COMBINATION_CODE,--产品组合代码
	NVL(po.BUSINESS_SOURCE,'NAN') AS PO_BUSINESS_SOURCE,--业务来源
	NVL(pci.EMAIL_SEQ,'NAN') AS PO_EMAIL_SEQ,--email
	NVL(cp.PHONE_NO,'NAN') AS PO_PHONE_SEQ,--电话
	NVL(pci.MOBILE_PHONE_SEQ,'NAN') AS PCI_MOBILE_PHONE_SEQ,--移动电话
	NVL(pci.HOME_PHONE_SEQ,'NAN') AS PCI_HOME_PHONE_SEQ, --家庭电话
	NVL(pci.OFFICE_PHONE_SEQ,'NAN') AS PCI_OFFICE_PHONE_SEQ,--办公电话
	NVL(ca.ADDRESS_TYPE,'NAN') AS CA_ADDRESS_TYPE,--地址类型
	ca.CLIENT_NO AS CA_CLIENT_NO,--客户编号
	ca.COUNTRY_CODE AS CA_COUNTRY_CODE,--国家编码
	ca.PROVINCE_CODE AS CA_PROVINCE_CODE,--眿直辖市编砿
	ca.CITY_CODE AS CA_CITY_CODE,--城市代码
	ca.DETAIL_ADDRESS AS CA_DETAIL_ADDRESS,--详细地址
	ca.POSTALCODE AS CA_POSTALCODE,--邮政编码
	ca.FULL_ADDRESS AS CA_FULL_ADDRESS,--完整地址
	rnpsi.AGENT_NO AS RNSPI_AGENT_NO,--业务员代砿
	NVL(rnpsi.SERVICE_NO,'NAN') AS RNSPI_SERVICE_NO,--服务人员代码
	rnpsi.SURPERVISE_NO AS RNSPI_SURPERVISE_NO, 
	rnpsi.ORPHAN_MARK AS RNPSI_ORPHAN_MARK,--孤儿单标访
	rnpsi.PAY_TIMES AS RNPSI_PAY_TIMES,--缴次
	rnpsi.ACTURAL_PAY_TIMES AS RNPSI_ACTURAL_PAY_TIMES,--实际缴次
	rnpsi.ASSIGN_MODE AS RNPSI_ASSIGN_MODE,--分单模式
	rnpsi.LAST_ASSIGN_USER AS RNPSI_LAST_ASSIGN_USER,--上次分单人员
	rnpsi.LAST_ASSIGN_DATE AS RNPSI_LAST_ASSIGN_DATE,--上次分单时间
	NVL(rnpsi.ASSIGN_RULE,'NAN' )AS RNPSI_ASSIGN_RULE,--分单规则
	NVL(rnpsi.ASSIGN_BRANCH_CODE,'NAN') AS RNPSI_ASSIGN_BRANCH_CODE,--特殊分单机构
	NVL(rnpsi.ASSIGN_CHANNEL_TYPE,'NAN') AS RNPSI_ASSIGN_CHANNEL_TYPE,--特殊分单渠道
	bi.BRANCH_FULL_NAME AS BI_BRANCH_FULL_NAME,--机构完整名称
	bi.BRANCH_NAME AS BI_BRANCH_NAME,--机构名称
	bi.BRANCH_ADDRESS AS BI_BRANCH_ADDRESS,--机构地址
	bi.PHONE AS BI_PHONE,--电话
	bi.FAX AS BI_FAX,--传真
	bi.POSTCODE AS BI_POSTCODE,--邮编
	bi.EMAIL AS BI_EMAIL,--email
	bi.BRANCH_LEVEL AS BI_BRANCH_LEVEL,--机构级别
	bi.PARENT_BRANCH AS BI_PARENT_BRANCH,--机构代码
	bi.IS_VALID AS BI_IS_VALID,--是否有效
	bi.SERVICE_PHONE AS BI_SERVICE_PHONE,--联系电话
	bi.SERVICE_ADDRESS AS BI_SERVICE_ADDRESS,--服务地址
	bi.COUNTRY_CODE AS BI_COUNTRY_CODE,--国家代码
	bi.PROVINCE_CODE AS BI_PROVINCE_CODE,--省代砿
	bi.CITY_CODE AS BI_CITY_CODE,--城市代码
	bi.AREA_CODE AS BI_AREA_CODE,--区县代码
	bi.BRANCH_CLASS AS BI_BRANCH_CLASS,---机构类型
	bi.CIRC_BRANCH_CODE AS BI_CIRC_BRANCH_CODE,--保监机构代码
	NVL(pbi.BRANCH_CODE,'NAN') AS PBI_BRANCH_CODE,--上级公司代码
	NVL(pbi.BRANCH_NAME,'NAN') AS PBI_BRANCH_NAME,--上级公司名称
	pp.PROD_SEQ AS PP_PROD_SEQ,--产品序号
	pp.PRODUCT_CODE AS PP_PRODUCT_CODE,--产品代码
	pp.INSURED_NO AS PP_INSURED_NO,--被保人客户号
	pp.UNITS AS PP_UNITS,--份数
	pp.DUTY_STATUS AS PP_DUTY_STATUS,--保单险种的责任状怿
	pp.PRODUCT_LEVEL AS PP_PRODUCT_LEVEL,-- 险种档次
	pp.BASE_SUM_INS AS PP_BASE_SUM_INS,--和客户约定的基本保额
	pp.DIVIDEND_SUM_INS AS PP_DIVIDEND_SUM_INS, -- 红利保额
	pp.COVERAGE_PERIOD AS PP_COVERAGE_PERIOD, -- 保险年期
	pp.MATURITY_DATE AS PP_MATURITY_DATE, -- 满期日期
	pp.SPECIAL_TERM AS PP_SPECIAL_TERM, -- 特别约定条款
	pp.RENEWAL_PERMIT AS PP_RENEWAL_PERMIT, -- 自动续保
	pp.INSURED_SEQ AS PP_INSURED_SEQ, -- 产品序号
	ppi.PREM_STATUS AS PP_PREM_STATUS,  -- 缴费状怿
	ppi.CHARGING_METHOD AS PP_CHARGING_METHOD, -- 下期保费收费方式
	ppi.PREM_SOURCE AS PP_PREM_SOURCE, -- 保费来源
	ppi.FREQUENCY AS PP_FREQUENCY, -- 频次
	ppi.PREM_DUE_DATE AS PP_PREM_DUE_DATE, -- 应缴旿
	ppi.MODAL_TOTAL_PREM AS PP_MODAL_TOTAL_PREM,-- 期缴保费合计
	ppi.POLICY_BALANCE AS PP_POLICY_BALANCE,-- 保单余额
	ci.CLIENT_NAME AS CI_CLIENT_NAME,-- 客户姓名
	ci.BIRTHDAY AS CI_BIRTHDAY, -- 客户深入
	ci.SEX_CODE AS CI_SEX_CODE, -- 客户性别
	ci.IDNO AS CI_IDNO, -- 客户证件叿
	ci.EDUCATION_CODE AS CI_EDUCATION_CODE, -- 客户教育程度编号
	ci.MARRIAGE_CODE AS CI_MARRIAGE_CODE, -- 客户婚姻状况
	ci.OCCUPATION_CODE AS CI_OCCUPATION_CODE, -- 职业代码
	ci.WORK_UNIT AS CI_WORK_UNIT, -- 工作单位
	ci.POSITION AS CI_POSITION, -- 职位
	ci.INDUSTRY AS CI_INDUSTRY, -- 行业
	ci.DATA_STATUS AS CI_DATA_STATUS, -- 数据状怿1：在用；0：被合并)
	ci.MERGE_TO_CLIENTNO AS CI_MERGE_TO_CLIENTNO, -- 被合并至的客户号
	pvc.VIP_EFFECT_DATE AS PVC_VIP_EFFECT_DATE, -- vip生效时间
	NVL(pvc.VIP_TYPE , 'NAN')AS PVC_VIP_TYPE, -- vip类型
	pvc.VIP_GRADE AS PVC_VIP_GRADE, -- vip等级
	pvc.VIP_END_DATE AS PVC_VIP_END_DATE,-- vip结束时间
	pvc.VIP_PREM_SUM AS PVC_VIP_PREM_SUM, --VIP客户保费
	pvc.BRANCH_CODE AS PVC_BRANCH_CODE, --最近承保保单机枿
	ch.CHANNEL_TYPE AS CT_CHANNEL_TYPE, -- 保单对应渠道编号
	ch.DESCRIPTION AS CT_DESCRIPTION, -- 渠道吿
	pro.ABBR_NAME AS PRO_ABBR_NAME, -- 保单对应产品简秿
	pro.FULL_NAME AS PRO_FULL_NAME, -- 全称
	pro.INS_TYPE AS PRO_INS_TYPE, -- 主附约：1,主险;2,附加陿3,主险和附加险
	pro.RENEWAL_PERMIT AS PRO_RENEWAL_PERMIT, -- 可否续保
	NVL(ssi.EMP_NAME, 'NAN') AS AGENT_EMP_NAME,-- 业务员姓吿
	NVL(ssi.EMP_NO,'NAN') AS AGENT_EMP_CODE, -- 业务员编叿
	NVL(si.EMP_NAME,'NAN') AS SERVICE_EMP_NAME, -- 服务人员姓名
	NVL(si.EMP_NO,'NAN') AS SERVICE_EMP_NO, -- 服务人员编号
	cci.CLIENT_NO AS INSURED_CLIENT_NO,-- 被保人客户号
	cci.CLIENT_NAME AS INSURED_CLIENT_NAME, -- 被保人姓吿
	di.DEPT_NO AS AGENT_DEPT_NO, -- 业务员所属部门代砿
	di.DEPT_NAME AS AGENT_DEPT_NAME, -- 业务员部门名
	di.PARENT_DEPT AS AGENT_PARENT_DEPT, -- 业务员所属部门上级部闿
	di.DEPT_LEVEL AS AGENT_DEPT_LEVEL, -- 业务员所属部门级刿
	di.BRANCH_CODE AS AGENT_BRANCH_CODE, -- 业务员所属部门所属机枿
	ddi.DEPT_NO AS SERVICE_DEPT_NO, -- 服务人员所属部闿
	ddi.DEPT_NAME AS SERVICE_DEPT_NAME, -- 服务人员所属部门名
	ddi.PARENT_DEPT AS SERVICE_PARENT_DEPT, -- 服务人员所属部门上级部闿
	ddi.DEPT_LEVEL AS SERVICE_DEPT_LEVEL, -- 服务人员所属部门级刿
	ddi.BRANCH_CODE AS SERVICE_BRANCH_CODE, -- 服务人员的部门所属机构号
	ppp.PREM_TERM AS PPP_PREM_TERM, -- 保单该险种的缴费朿
	ppp.PREM_SOURCE AS PPP_PREM_SOURCE, -- 保单该险种的保费来源
	ppp.PERIOD_PREM_SUM AS PPP_PERIOD_PREM_SUM, -- 保单该险种的期缴保费合计
	ppp.ANN_STANDARD_PREM as PPP_ANN_STANDARD_PREM,-- 年缴标准保费
	ppp.ANN_WEAK_ADD_PREM as PPP_ANN_WEAK_ADD_PREM,-- 年缴弱体加费
	ppp.ANN_OCCU_ADD_PREM as PPP_ANN_OCCU_ADD_PREM,-- 年缴职业加费
	ppp.PERIOD_STANDARD_PREM as PPP_PERIOD_STANDARD_PREM,-- 期缴标准保费
	ppp.PERIOD_OCCU_ADD_PREM as PPP_PERIOD_OCCU_ADD_PREM,-- 期缴职业加费
	ppp.PERIOD_WEAK_ADD_PREM as PPP_PERIOD_WEAK_ADD_PREM,-- 期缴弱体加费
	ppp.PAY_TO_DATE as PPP_PAY_TO_DATE,-- 缴至日期
	ppp.ADD_PREM_TERM as PPP_ADD_PREM_TERM,-- 加费期限
	ppp.ADD_PREM_EFF_DATE as PPP_ADD_PREM_EFF_DATE,-- 加费生效旿
	ppp.PREM_PERIOD_TYPE as PPP_PREM_PERIOD_TYPE,-- 缴费期类垿
	ppp.SEX_CODE as PPP_SEX_CODE,-- 被保人性别（用于保费计算）
	ppp.INS_AGE as PPP_INS_AGE,-- 被保人年龄（用于保费计算＿
	ppp.OCCUPATION_CODE as PPP_OCCUPATION_CODE,-- 被保人职业（用俎保费计算＿
	ppp.PREM_DISCOUNT as PPP_PREM_DISCOUNT,-- 保费折扣比例
	ppp.PAYOUT_FEE as PPP_PAYOUT_FEE,-- 支出类费甿
	ppp.CONSULT_FEE as PPP_CONSULT_FEE,-- 咨询类费甿
	gpg.LAT || ',' || gpg.LON AS LOCATION -- 坐标
FROM
	GRAS_POLICY_GEO gpg
LEFT JOIN POLICY_CONTACT_INFO pci ON pci.policy_no = gpg.POLICY_NO
LEFT JOIN POLICY po ON po.POLICY_NO = gpg.POLICY_NO
LEFT JOIN POLICY_PRODUCT_PREM ppp ON ppp.POLICY_NO = gpg.POLICY_NO
LEFT JOIN CLIENT_ADDRESS ca ON ca.ADDRESS_SEQ = pci.ADDRESS_SEQ
LEFT JOIN RN_POLICY_SERVICE_INFO rnpsi ON rnpsi.POLICY_NO = gpg.POLICY_NO
LEFT JOIN BRANCH_INFO bi ON bi.BRANCH_CODE = po.BRANCH_CODE AND bi.IS_VALID = 'Y'
LEFT JOIN BRANCH_INFO pbi ON pbi.BRANCH_CODE = bi.PARENT_BRANCH AND pbi.IS_VALID = 'Y'
LEFT JOIN POLICY_PRODUCT pp ON pp.POLICY_NO = gpg.POLICY_NO
LEFT JOIN POLICY_PREM_INFO ppi ON ppi.POLICY_NO = gpg.POLICY_NO
LEFT JOIN CLIENT_INFORMATION ci ON ci.CLIENT_NO = ca.CLIENT_NO
LEFT JOIN POS_VIP_CLIENT pvc ON pvc.CLIENT_NO = ci.CLIENT_NO AND pvc.VIP_END_DATE IS NULL
LEFT JOIN CHANNEL_TYPE_TBL ch ON ch.CHANNEL_TYPE = po.CHANNEL_TYPE
LEFT JOIN PRODUCT pro ON pro.PRODUCT_CODE = pp.PRODUCT_CODE
LEFT JOIN STAFF_INFO ssi ON ssi.EMP_NO = po.AGENT_NO
LEFT JOIN STAFF_INFO si ON si.EMP_NO = rnpsi.SERVICE_NO
LEFT JOIN DEPARTMENT_INFO di ON di.DEPT_NO = ssi.DEPT_NO
LEFT JOIN DEPARTMENT_INFO ddi ON ddi.DEPT_NO = si.DEPT_NO
LEFT JOIN CLIENT_INFORMATION cci ON cci.CLIENT_NO = pp.INSURED_NO
LEFT JOIN CLIENT_PHONE cp ON cp.PHONE_SEQ = pci.PHONE_SEQ
WHERE 
	 pp.PROD_SEQ = PPP.PROD_SEQ