2016权益单qingyuan

===========================================begin

-----20180428养老权益单统计

select * from gdsimis.ac01 a where a.aac147='440127195607180918';

create table ac83_yl_cic001 as 

SELECT c.aac001,

       round(sum(c.aae019) / count(distinct c.aae002), 2) cic001

  FROM gdylsy.ac83 c, gdylsy.ac82 b

 WHERE c.aaz220 = b.aaz220

   and c.aae002 between 201601 and 201612

   and c.aae140 = '110'

   and c.aaa036 in ('110098','110004','110001', '110002', '110003', '110025', '110038', '110495')

   and c.bae023 = 'S'

   --and c.aac001 =1000000004700785

   group by c.aac001, c.aae140, b.baf307;

select * from ac83_yl a where a.aac001=1000000004700785;

---少加了新旧办法补差，待遇项目'110004' ，多了代扣补充医疗保险费 110161

---------------------------------更新月平均养老待遇

MERGE INTO ac83_yl t1

USING (SELECT * FROM ac83_yl_cic001) t2

ON ( t1.aac001=t2.aac001)

WHEN MATCHED THEN

  UPDATE SET t1.cic001 = t2.cic001

;

select * from gdsimis.ac01 a where a.aac147='440127195610230517';

select * from ac83_yl a where a.aac001=1000000004788043;

----------------------------------------更新当年调整金

select count(1) from ac83_yl a where a.aae041>201606;

--cytz nianyue 首次领取养老待遇须 <= 201606 

update ac83_yl a set a.cic002=0 where a.aae041>201606;

--上年度养老金标准

--取2015年12月的待遇，如果是16年1月到6月前领取待遇的，直接取这个待遇数值即可

alter table ac83_yl add dy201512 number;

update ac83_yl  set dy201512=null ;

MERGE INTO ac83_yl t1 

using  

(SELECT C.AAC001, SUM(C.AAE019) AAE019

  FROM AC83 C, AC83_YL B

 WHERE C.AAC001 = B.AAC001

   AND C.AAE002 = 201512

   AND C.AAE140 = '110'

   AND C.AAA036 IN ('110098', '110004', '110001', '110002', '110003',

        '110025', '110038', '110495')

   AND C.BAE023 = 'S'

 GROUP BY C.AAC001) t2 

ON ( t1.aac001=t2.aac001)

WHEN MATCHED THEN

  UPDATE SET t1.dy201512 = t2.AAE019

;  

select count(1) from ac83_yl a where a.dy201512 is null;

MERGE INTO ac83_yl t1 

using  

(SELECT C.AAC001, SUM(C.AAE019) AAE019

  FROM AC83 C, AC83_YL B

 WHERE C.AAC001 = B.AAC001

   AND C.AAE002 = b.aae041

   AND C.AAE140 = '110'

   AND C.AAA036 IN ('110098', '110004', '110001', '110002', '110003',

        '110025', '110038', '110495')

   AND C.BAE023 = 'S'

   and b.dy201512 is null

 GROUP BY C.AAC001) t2 

ON ( t1.aac001=t2.aac001)

WHEN MATCHED THEN

  UPDATE SET t1.dy201512 = t2.AAE019

;  

--35+ 上年度养老金标准*4.8%+ （缴费年限-15）向上取整*1

SELECT round(35 + A.DY201512 * 0.048 + (case when nvl(a.paymonth,0)>180 then CEIL(a.paymonth / 12)-15 else 0 end) +

       DECODE(NVL(A.AAC006, 0), 0, 0, 1200),2) tdje

  FROM AC83_YL A;

 

alter table ac83_yl add paymonth number;

--select aae200,(ceil(a.aae200/12)-15) from ic10 a where a.aac001=1000000004679085;

MERGE INTO ac83_yl t1

USING 

(SELECT B.AAC001, max(B.AAE200) aae200

  FROM IC10 B, AC83_YL A

 WHERE A.AAC001 = B.AAC001

   AND B.AAE140 = '110'

   and A.AAE041 <= 201606

   group by b.aac001) t2

ON ( t1.aac001=t2.aac001)

WHEN MATCHED THEN

  UPDATE SET t1.paymonth = t2.aae200

; 

----------------------

----2016年6月30日（含当日）前年满75周岁的，一次性加发1200元===19410630 qian 

select a.aac006,b.aac001 from gdsimis.ac01 a,ac83_yl b where a.aac001=b.aac001 

and a.aac006<=19410630;

alter table ac83_yl add aac006 number;

MERGE INTO ac83_yl t1

USING 

(select a.aac006,b.aac001 from gdsimis.ac01 a,ac83_yl b where a.aac001=b.aac001 

and a.aac006<=19410630) t2

ON ( t1.aac001=t2.aac001)

WHEN MATCHED THEN

  UPDATE SET t1.aac006 = t2.aac006

; 

---------------------------------------------

ALTER TABLE gdylsyggyw.ACQ3 MODIFY baz663 DEFAULT gdylsy.SEQ_baz663.Nextval;

insert into gdylsyggyw.ACQ3(aae001, aac001, aae041, aae042, cic101, cic102, baz002, aaa027)

select aae001, aac001, aae041, aae042, cic001, cic002, baz002, aaa027 from ac83_yl a;

--ACQ3-个人权益单（养老）

SELECT * FROM gdylsyggyw.ACQ3 a WHERE a.baz002=1000000000000040;

===========================================end