个人账户检查软件代码

1、基础信息错误
spool c:\基础信息错误.txt
SELECT '姓名为空' from dual;
SELECT zg_fzh
FROM DA_ZGJBQK WHERE ZG_XM IS NULL;

SELECT '性别错误' from dual;
SELECT zg_fzh,zg_xb
FROM DA_ZGJBQK WHERE zg_xb IS NULL or zg_xb NOT IN ('男','女');

SELECT '辅助为空' from dual;
SELECT zg_fzh
FROM DA_ZGJBQK WHERE ZG_FZH IS NULL;

SELECT '社会保障号为空或不等于15、17、18' from dual;
SELECT count(zg_fzh)
FROM DA_ZGJBQK WHERE ZG_SHBZH IS NULL OR NOT (LENGTH(ZG_SHBZH)=15 OR LENGTH(ZG_SHBZH)=17 OR LENGTH(ZG_SHBZH)=18);

SELECT '出生时间为空' from dual;
SELECT count(zg_fzh)
FROM DA_ZGJBQK WHERE ZG_csrq IS NULL;

SELECT '出生时间格式不合法' from dual;
SELECT count(zg_fzh)
FROM DA_ZGJBQK WHERE ZG_CSRQ-TRUNC(ZG_CSRQ/100,0)*100>12 OR ZG_CSRQ-TRUNC(ZG_CSRQ/100,0)*100=0;

SELECT '出生时间超出范围' from dual;
SELECT count(zg_fzh)
FROM DA_ZGJBQK WHERE (ZG_CSRQ<193800 OR ZG_CSRQ>199400) AND FLAG=0;

SELECT '参加工作时间为空' from dual;
SELECT count(zg_fzh)
FROM DA_ZGJBQK WHERE ZG_cjgzny IS NULL;

SELECT '参加工作时间格式不合法' from dual;
SELECT count(zg_fzh)
FROM DA_ZGJBQK WHERE ZG_CJGZNY-TRUNC(ZG_CJGZNY/100,0)*100>12 OR ZG_CJGZNY-TRUNC(ZG_CJGZNY/100,0)*100=0;

SELECT '参加工作时间超出范围' from dual;
SELECT count(zg_fzh)
FROM DA_ZGJBQK WHERE ZG_CJGZNY<ZG_CSRQ AND FLAG=0;

SELECT '视同缴费年限为空' from dual;
SELECT count(zg_fzh)
FROM DA_ZGJBQK WHERE zg_stjfnx IS NULL;

SELECT '实际缴费年限为空' from dual;
SELECT count(zg_fzh)
FROM DA_ZGJBQK WHERE zg_sjjfnx IS NULL;

SELECT '缴费基数超出范围' from dual;
SELECT COUNT(ZGN_FZH) FROM ND_ZGNTZ 
WHERE (ZGN_JFJS>(SELECT SBN_DSYPJGZ*3 FROM ND_SBNTZ WHERE ZGN_ND=SBN_ND)
OR ZGN_JFJS<(SELECT SBN_DSYPJGZ*0.6 FROM ND_SBNTZ WHERE ZGN_ND=SBN_ND));

SELECT '累计缴费月数超过范围' from dual;
SELECT COUNT(*) FROM ND_ZGNTZ
WHERE ZGN_LJJFY>141  AND ZGN_ND=2006 AND ZGN_FZH IN (SELECT ZG_FZH FROM DA_ZGJBQK WHERE FLAG=0);

SELECT '有基本情况没有年台帐' from dual;
select count(*) from 
(SELECT zg_fzh FROM DA_ZGJBQK 
MINUS
SELECT DISTINCT ZGN_FZH FROM ND_ZGNTZ);

SELECT '有年台帐没有基本情况' from dual;
select count(*) from 
(SELECT DISTINCT ZGN_FZH FROM ND_ZGNTZ
MINUS
SELECT zg_fzh FROM DA_ZGJBQK);

SELECT '有基本情况没有分配台帐' from dual;
select count(*) from 
(SELECT zg_fzh FROM DA_ZGJBQK 
MINUS
SELECT DISTINCT ZGF_FZH FROM FP_ZGFPTZ);

SELECT '有分配台帐没有基本情况' from dual;
select count(*) from 
(SELECT DISTINCT ZGF_FZH FROM FP_ZGFPTZ
MINUS
SELECT zg_fzh FROM DA_ZGJBQK); 

SELECT '缺2005年度年台帐' from dual;
select zgn_fzh,zgn_nd from nd_zgntz where zgn_nd=2004
intersect
select zgn_fzh,zgn_nd from nd_zgntz where zgn_nd=2006
minus
select zgn_fzh,zgn_nd from nd_zgntz where zgn_nd=2005;

SELECT '缺2006年度年台帐' from dual;
select zgn_fzh,zgn_nd from nd_zgntz where zgn_nd=2005
intersect
select zgn_fzh,zgn_nd from nd_zgntz where zgn_nd=2007
minus
select zgn_fzh,zgn_nd from nd_zgntz where zgn_nd=2006;

select '2007年台账与个人帐户不符' from dual;
SELECT zg_fzh
FROM DA_ZGJBQK,ND_ZGNTZ
WHERE ZG_FZH=ZGN_FZH AND ZGN_ND=2007 
AND abs(ZGN_sNDGRCCE+ZGN_sNDQYCCE-(ZG_GRJJ+ZG_QYHZ))>0.1
and zg_fzh in(
select zgn_fzh from nd_zgntz where zgn_nd=2007);
select '结束' from dual;

spool off




2、漏个人账户记帐年
SET PAGESIZE 90000
SPOOL C:\遗漏记帐年.txt
SELECT ZGN_FZH,MAX(ZGN_ND),MIN(ZGN_ND),COUNT(ZGN_ND),MAX(ZGN_ND) - MIN(ZGN_ND)+1 - COUNT(ZGN_ND)  BB FROM ND_ZGNTZ
GROUP BY ZGN_FZH HAVING MAX(ZGN_ND) - MIN(ZGN_ND)+1 - COUNT(ZGN_ND) >0;
SPOOL OFF

3、多计、少计问题
set pagesize 90000
spool c:\多计少计.txt
SELECT zg_fzh FROM DA_QYJBQK,DA_ZGJBQK
WHERE QY_DM=ZG_QYDM AND ZG_FZH IN(
SELECT zgn_fzh
FROM ND_ZGNTZ,(SELECT
ZGF_FZH,SUM((ZGF_GRSJBJ+ZGF_QYSHBJ+ZGF_GRbjLX+ZGF_QYBhLX)*(1+(13-ZGF_JXY)/12*0.023))
ZFJF FROM FP_ZGFPTZ
WHERE ZGF_JKRQ BETWEEN 20050700 AND 20060700 GROUP BY ZGF_FZH) BBB
WHERE ZGN_FZH=ZGF_FZH AND ZGN_ND=2005 AND
abs(ZFJF-(ZGN_BNDGRCCE+ZGN_BNDQYCCE-1.023*(ZGN_SNDGRCCE+ZGN_SNDQYCCE))) >0.1
)
SELECT zg_fzh FROM DA_QYJBQK,DA_ZGJBQK
WHERE QY_DM=ZG_QYDM AND ZG_FZH IN(
SELECT zgn_fzh
FROM ND_ZGNTZ,(SELECT
ZGF_FZH,SUM((ZGF_GRSJBJ+ZGF_QYSHBJ+ZGF_GRbjLX+ZGF_QYBhLX)*(1+(13-ZGF_JXY)/12*0.027))
ZFJF FROM FP_ZGFPTZ
WHERE ZGF_JKRQ BETWEEN 20060700 AND 20070700 GROUP BY ZGF_FZH) BBB
WHERE ZGN_FZH=ZGF_FZH AND ZGN_ND=2006 AND
abs(ZFJF-(ZGN_BNDGRCCE+ZGN_BNDQYCCE-1.023*(ZGN_SNDGRCCE+ZGN_SNDQYCCE))) >0.1
)
order BY QY_dm,zg_fzh;
spool off

4、年度结转错误
spool c:\年度结转.txt
SELECT A.ZGN_FZH
FROM ND_ZGNTZ A,ND_ZGNTZ B
WHERE A.ZGN_FZH=B.ZGN_FZH AND A.ZGN_ND=2005 AND B.ZGN_ND=2006
AND abs(A.ZGN_BNDGRCCE+A.ZGN_BNDQYCCE-B.ZGN_SNDGRCCE-B.ZGN_SNDQYCCE)>0.1;
SELECT A.ZGN_FZH
FROM ND_ZGNTZ A,ND_ZGNTZ B
WHERE A.ZGN_FZH=B.ZGN_FZH AND A.ZGN_ND=2006 AND B.ZGN_ND=2007
AND abs(A.ZGN_BNDGRCCE+A.ZGN_BNDQYCCE-B.ZGN_SNDGRCCE-B.ZGN_SNDQYCCE)>0.1;
spool off