建包：
1 CREATE OR REPLACE Package Pkg_Weiyl Is 2 Pkg_Name Constant Varchar2(20) := 'pkg_weiyl'; 3 Too_Young Constant Number := -20001; 4 Exc_Too_Young Exception; 5 Pragma Exception_Init(Exc_Too_Young, -20001); 6 Procedure Updateaae140(Pi_Aac002 In Varchar2, 7 Pi_Aae140 In Varchar2, 8 Po_Fhz Out Varchar2, 9 Po_Msg Out Varchar2); 10 Procedure Updateidcard(Pi_Bae007 In Varchar2, 11 Pi_Flag In Varchar2, 12 Pi_Aac002 In Varchar2, 13 Pi_Aac002_New In Varchar2, 14 Pi_Aae013 In Varchar2, 15 Pi_Aae011 In Varchar2, 16 Pi_Bae001 In Varchar2, 17 Po_Fhz Out Varchar2, 18 Po_Msg Out Varchar2); 19 Procedure Cancelupdate(Pi_Bae007 In Varchar2, 20 Po_Fhz Out Varchar2, 21 Po_Msg Out Varchar2); 22 Procedure Updateidfh(Pi_Bae007 In Varchar2, 23 Pi_Aae012 In Varchar2, 24 Po_Fhz Out Varchar2, 25 Po_Msg Out Varchar2); 26 Procedure Validateidcard(Pi_Aac001 In Number, 27 Pi_Aac002_New In Varchar2, 28 Po_Fhz Out Varchar2, 29 Po_Msg Out Varchar2); 30 Procedure Vali_Idcard_Lenandchar(Pi_Aac002 In Varchar2, 31 Po_Fhz Out Varchar2, 32 Po_Msg Out Varchar2); 33 Procedure Updateidcard_Fortest(Pi_Aac002 In Varchar2, 34 Pi_Aac002_New In Varchar2, 35 Pi_Aae013 In Varchar2, 36 Pi_Aae011 In Varchar2, 37 Pi_Bae001 In Varchar2, 38 Po_Fhz Out Varchar2, 39 Po_Msg Out Varchar2); 40 --截取字符串 split_type 是自己建的类型 CREATE or replace type split_comma is table of varchar2(4000); 41 -- 取出字符串的方法 ： select column_value from table (pkg_weiyl.split_dh('dd,aa,134')); 42 Function Split_Dh(p_Str In Varchar2, 43 p_Delimiter In Varchar2 Default (',') --分隔符，默认逗号 44 ) Return Split_Type; 45 46 Function Func_Wyl(Pi_Aaa100 Varchar2, Pi_Aaa102 Varchar2) 47 --字典转换，把代码值转换成对应的可理解的中文,卫永乐,20141105 48 Return Varchar2; 49 Function Func_Check_Para(Pi_Aab999 In Varchar2, 50 Pi_Pch In Varchar2, 51 Pi_Aae001 In Number, 52 Pi_Aae036 In Varchar2, 53 Pi_Aac027 In Number) 54 /*拼接where条件 过程 check_sdnmdc 的游标的where条件*/ 55 Return Varchar2; 56 57 Procedure Xjyyzf(Pi_Bae007 In Varchar2, 58 Pi_Operid In Varchar2, 59 Pi_Aae037 In Varchar2, 60 Pi_Aae038 In Varchar2, 61 Po_Fhz Out Varchar2, 62 Po_Msg Out Varchar2); 63 --- 循环调用的过程，解决每次都要自己写一个declare plsql代码块， 64 --入参： 65 Procedure Prc_Xunhuan(Pi_Prcname In Varchar2, --要调用的过程名 66 Pi_Tabname In Varchar2, --cursor取值的表 67 Po_Fhz Out Varchar2, 68 Po_Msg Out Varchar2); 69 Procedure Xjyyzf_Callback(Pi_Bae007 In Varchar2, 70 Pi_Operid In Varchar2, 71 Po_Fhz Out Varchar2, 72 Po_Msg Out Varchar2); 73 Procedure Xjyyzfqx(Pi_Bae007 In Varchar2, 74 Po_Fhz Out Varchar2, 75 Po_Msg Out Varchar2); 76 -- 添加自治事务 77 Procedure Autonomous_Tran(Pi_Aac001 In Varchar2, 78 Po_Fhz Out Varchar2, 79 Po_Msg Out Varchar2); 80 /*手工添加表级锁，使用场景，如果要操作一张大表的大部分数据， 81 如果不加表级锁，那么就会耗费大量的资源，这种情况下可以使用 82 手工给表加锁，释放方式 rollback，或者commit 83 */ 84 Procedure Update_Ac02_Aae140(Pi_Aae140 In Varchar2, 85 Pi_Fhz Out Varchar2, 86 Po_Msg Out Varchar2); 87 88 Procedure Querycheck(Pi_Aac002 In Varchar2, 89 Pi_Aac003 In Varchar2, 90 Po_Fhz Out Varchar2, 91 Po_Msg Out Varchar2); 92 93 Procedure Check_Ac02(Pi_Aac002 In Varchar2, 94 Po_Aac001 Out Number, 95 Po_Cac012 Out Varchar2, 96 Po_Aab001 Out Varchar2, 97 Po_Fhz Out Varchar2, 98 Po_Msg Out Varchar2); 99 Procedure Check_Skc84(Pi_Aac001 In Varchar2, 100 Po_Fhz Out Varchar2, 101 Po_Msg Out Varchar2); 102 Procedure Check_Sdnmdcc(Pi_Aab999 In Varchar2, 103 PI_PCH in varchar2, 104 Po_Fhz Out Varchar2, 105 Po_Msg Out Varchar2); 106 Procedure Check_Sdnmdc(Pi_Bae001 In Varchar2, 107 Pi_Aab999 In Varchar2, 108 Pi_Pch In Varchar2, 109 /* PI_GLT in varchar2,*/ 110 Pi_Aae001 In Varchar2, 111 Pi_Aae036 In Varchar2, 112 Pi_Aac027 In Varchar2, 113 Pi_Bzw In Varchar2, 114 Pi_Oper In Varchar2, 115 Po_Fhz Out Varchar2, 116 Po_Msg Out Varchar2); 117 Procedure Check_Sdnmdc_Multi(Pi_Bae001 In Varchar2, 118 Pi_Aab999 In Varchar2, 119 Pi_Pch In Varchar2, 120 /* PI_GLT in varchar2,*/ 121 Pi_Aae001 In Varchar2, 122 Pi_Aae036 In Varchar2, 123 Pi_Aac027 In Varchar2, 124 Pi_Bzw In Varchar2, 125 Pi_Oper In Varchar2, 126 Po_Fhz Out Varchar2, 127 Po_Msg Out Varchar2); 128 Procedure Updatekbb5(Pi_Bae007 In Varchar2, 129 Pi_Ckz545 In Varchar2, 130 Pi_Ckb626 In Varchar2, 131 Pi_Ckb627 In Varchar2, 132 Pi_Ckb629 In Varchar2, 133 Pi_Ckb630 In Varchar2, 134 Po_Fhz Out Varchar2, 135 Po_Msg Out Varchar2); 136 Procedure Getaaz601(Pi_Rc In Varchar2, 137 po_aaz601 out number, 138 Po_Fhz Out Varchar2, 139 Po_Msg Out Varchar2); 140 Procedure Insertfw_Zsk(PI_AAA200 in varchar2, 141 Pi_Aae202 In Varchar2, 142 Pi_Aaa203 In Varchar2, 143 Pi_Aae008 In Varchar2, 144 PI_AAE011 IN VARCHAR2, 145 PI_AAE906 IN VARCHAR2, 146 PI_BZ IN VARCHAR2, 147 Po_Fhz Out Varchar2, 148 Po_Msg Out Varchar2); 149 /*拼接两个字符串,练手嵌套存储过程*/ 150 procedure testNestedPro(pi_xing in varchar2, 151 pi_ming in varchar2, 152 po_fhz out varchar2, 153 po_msg out varchar2); 154 /*触摸屏查询标记*/ 155 procedure cancelCmp(PI_SERIALNUM in varchar2, 156 po_fhz out varchar2, 157 po_msg out varchar2); 158 /*取消征集通知单*/ 159 procedure cancelAaz288(PI_OPERID in varchar2, 160 PI_AAZ288 in varchar2, 161 po_fhz out varchar2, 162 po_msg out varchar2); 163 /*删除知识库核销的附件内容，否则数据里的不必要的附件会越来越多*/ 164 procedure deleteZskFile(PI_CAE232 in varchar2, 165 po_fhz out varchar2, 166 po_msg out varchar2); 167 procedure generatexmmx(pi_ksrq in varchar2, 168 pi_zzrq in varchar2, 169 po_fhz out varchar2, 170 po_msg out varchar2); 171 procedure rebuild_sic86(pi_aac001 in varchar2, 172 po_fhz out varchar2, 173 po_msg out varchar2); 174 procedure rebuild_ab07(pi_aab001 in varchar2, 175 pi_ksny in varchar2, 176 pi_zzny in varchar2, 177 pi_aae140 in varchar2, 178 po_fhz out varchar2, 179 po_msg out varchar2); 180 181 /*Pkg_Ryhb_Pl_New 182 --批量合并，初始数据生成 183 Procedure Plhb_Start(Pi_Bae001 In Varchar2, 184 Pi_Aab001 In Number, 185 Pi_Jbr In Varchar2, 186 Po_Fhz Out Varchar2, 187 Po_Msg Out Varchar2) 188 */ 189 procedure generate_plhb_data(pi_bae001 in varchar2, 190 po_fhz out varchar2, 191 po_msg out varchar2); 192 procedure queryZSK(PI_AAE906 in varchar2, 193 PO_AAE202 out varchar2, 194 PO_AAE008 out varchar2, 195 po_fhz out varchar2, 196 po_msg out varchar2); 197 /* 198 生成失地农民汇总数据 199 by weiyongel 20160519 200 */ 201 procedure generate_sdnmhzsj(PI_BAE001 in varchar2, 202 po_fhz out varchar2, 203 po_msg out varchar2); 204 /* 205 生成失地农民清理数据 206 by weiyongel 20160519 207 */ 208 procedure generate_sdnmqlsj(PI_BAE001 in varchar2, 209 po_fhz out varchar2, 210 po_msg out varchar2); 211 /*生成失地农民数据清理后的变化字段，用于查询失地农民数据清理模块*/ 212 procedure generate_sdnmdatachange(PI_AAC001 in varchar2, 213 PI_AAZ288 in varchar2, 214 PO_FHZ out varchar2, 215 PO_MSG out varchar2); 216 /*检查ac35时间 ，增减员时用*/ 217 procedure checkAC35Tim(PI_AAC002 in varchar2, 218 pi_aab999 in varchar2, 219 Po_AAE042 OUT varchar2, 220 PO_FHZ out varchar2, 221 PO_MSG out varchar2); 222 /* 重新统计ac43 aae002 ,20160526 */ 223 procedure cxtj_ac43(PI_AAB001 in varchar2, 224 PO_FHZ out varchar2, 225 PO_MSG out varchar2); 226 procedure getAAC027(PI_AAC002 in varchar2, 227 PO_AAC027 OUT varchar2, 228 PO_FHZ out varchar2, 229 PO_MSG out varchar2); 230 /* for test ,20160530 */ 231 procedure myInsert(PI_AAC002 in varchar2, 232 PO_FHZ out varchar2, 233 PO_MSG out varchar2); 234 /*统计 社会保险参保情况查询 */ 235 procedure tongji_shbx(PI_AAC001 in varchar2, 236 PO_FHZ out varchar2, 237 PO_MSG out varchar2) ; 238 End Pkg_Weiyl;
建立包体：
1 / 2 CREATE OR REPLACE Package Body Pkg_Weiyl Is 3 c_Pkg_Name Constant Varchar2(20) := 'PKG_WEIYL'; 4 --修改险种 5 Procedure Updateaae140(Pi_Aac002 In Varchar2, 6 Pi_Aae140 In Varchar2, 7 Po_Fhz Out Varchar2, 8 Po_Msg Out Varchar2) Is 9 v_Count Number(2); 10 Begin 11 Po_Fhz := '1'; 12 Po_Msg := '成功'; 13 Select Count(*) Into v_Count From Sab11 Where Bcc347 = Pi_Aac002; 14 If v_Count > 0 Then 15 Update Ac02 16 Set Aac008 = '2' 17 Where Aae140 = '342' 18 And Aac001 = (Select Aac001 19 From Ac01 20 Where Aac002 = Pi_Aac002 21 And Aae140 = Pi_Aae140); 22 End If; 23 Exception 24 When No_Data_Found Then 25 Po_Fhz := '0'; 26 Po_Msg := '失败'; 27 End Updateaae140; 28 29 --修改身份证号 30 Procedure Updateidcard(Pi_Bae007 In Varchar2, 31 Pi_Flag In Varchar2, 32 Pi_Aac002 In Varchar2, 33 Pi_Aac002_New In Varchar2, 34 Pi_Aae013 In Varchar2, 35 Pi_Aae011 In Varchar2, 36 Pi_Bae001 In Varchar2, 37 Po_Fhz Out Varchar2, 38 Po_Msg Out Varchar2) Is 39 v_Aac001 Ac02.Aac001%Type; 40 v_Prc Varchar2(20); 41 v_Aab001 Ac02.Aab001%Type; 42 v_Yl_Count Number(2); --养老待遇记录数 43 v_Msg Varchar2(200); 44 v_Aaa076 Ac60.Aaa076%Type; 45 v_Prcname Varchar2(200); 46 v_Params Varchar2(500); 47 v_Sqlerrm Varchar2(500); 48 v_Aac003 Ac01.Aac003%Type; 49 v_Aac002_Tmp Ac01.Aac002%Type; 50 Begin 51 -- 初始化返回值 52 Po_Fhz := '1'; 53 Po_Msg := ''; 54 v_Prc := '.updateIDCard'; 55 v_Prcname := c_Pkg_Name || v_Prc; 56 v_Params := ',传入参数为:pi_aac002=' || Pi_Aac002 || ',pi_aac002_new=' || 57 Pi_Aac002_New || ',pi_aae013=' || Pi_Aae013 || 58 ',pi_aae011=' || Pi_Aae011 || ',pi_bae001=' || Pi_Bae001; 59 Select Aac001, Aab001 60 Into v_Aac001, v_Aab001 61 From Ac01 62 Where Aac002 = Pi_Aac002; 63 -- 调用校验过程进行判断 64 Validateidcard(v_Aac001, Pi_Aac002_New, Po_Fhz, Po_Msg); 65 If Po_Fhz <> '1' Then 66 Return; 67 End If; 68 69 -- 先做 是否有养老待遇的判断,如果有就直接返回，不更新ac01.aac002 70 Select Count(1) Into v_Yl_Count From Ac60 Where Aac001 = v_Aac001; 71 If v_Yl_Count > 0 Then 72 -- 如果有养老待遇，那么抛出更详细的结果，以便于前台更容易理解 73 -- 只取第一条 74 Select Aaa076 75 Into v_Aaa076 76 From Ac60 77 Where Aac001 = v_Aac001 78 And Rownum = 1; 79 Select Func_Wyl('AAA076', v_Aaa076) Into v_Msg From Dual; 80 Po_Fhz := '-2'; 81 Po_Msg := v_Prcname || '执行失败，该人员存在养老待遇类型为 "' || v_Msg || 82 '" 的养老待遇，且待遇状态正常，因此不能更新身份证'; 83 Return; 84 End If; 85 -- 更新ac01.AAC002 86 Begin 87 -- 根据传入的标志来判断是该笔业务是改成正确身份证还是改成错误身份证， 88 If (Pi_Flag = '0') Then 89 /*v_aac002_tmp := BXGX_SEQ_aac002_tmp.Nextval||substr(pi_aac002,7,length(pi_aac002)-6);*/ 90 v_Aac002_Tmp := Pi_Aac002_New; 91 Else 92 v_Aac002_Tmp := Pi_Aac002_New; 93 End If; 94 Update Ac01 Set Aac002 = v_Aac002_Tmp Where Aac001 = v_Aac001; 95 Exception 96 When Others Then 97 v_Sqlerrm := Substr(Sqlerrm, 1, 9); 98 /* 捕获,唯一性约束冲突*/ 99 If v_Sqlerrm = 'ORA-00001' Then 100 Select Aac003 101 Into v_Aac003 102 From Ac01 103 Where Aac002 = Pi_Aac002_New; 104 Po_Fhz := v_Prcname || '_-3'; 105 Po_Msg := '系统里已经存在身份证为' || Pi_Aac002_New || '的参保人了,姓名:' || 106 v_Aac003 || ',因此不能修改'; 107 Elsif v_Sqlerrm <> 'ORA-00001' Then 108 /*SQLERRM=ORA-00001*/ 109 /*没法成功捕捉到sqlerrm*/ 110 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 111 Po_Fhz := v_Prcname || '_94'; 112 End If; 113 114 --po_msg := pkg_fun.F_ERRMSG(v_prcName,sqlcode,sqlerrm,v_params); 115 --po_fhz := v_prcName||'_91'; 116 Return; 117 End; 118 Insert Into Ac25 119 (Aaz163, 120 Bae001, 121 Aab001, 122 Aac001, 123 Cae129, 124 Aac050, 125 Aae160, 126 Cac038, 127 Bae007, 128 Aae011, 129 Aae036, 130 Bce326, 131 Aae012, 132 Bhe949, 133 Cae030, 134 Cae031, 135 Aae013, 136 Aac002, 137 Aac002_New) 138 Values 139 (Seq_Bxgx_Aaz163.Nextval, --使用原来的序列号 140 Pi_Bae001, 141 v_Aab001, 142 v_Aac001, 143 To_Char(Sysdate, 'yyyymm'), 144 '50', -- 50,变更类型为 修改资料 145 '1933', -- 1933,变更原因, 其它 146 '', 147 /*seq_ac25_bae007.nextval,*/ -- 业务流水号 148 Pi_Bae007, 149 Pi_Aae011, 150 To_Char(Sysdate, 'yyyymmddhh24miss'), 151 '0', 152 '', 153 To_Char(Sysdate, 'yyyymmddhh24miss'), 154 '', 155 '', 156 Pi_Aae013, 157 Pi_Aac002, 158 v_Aac002_Tmp); 159 Update Ac01 Set Aac002 = Pi_Aac002 Where Aac002 = v_Aac002_Tmp; 160 Exception 161 When No_Data_Found Then 162 -- Sqlcode, Sqlerrm 163 Po_Fhz := '-1'; 164 Po_Msg := '过程' || Pkg_Name || v_Prc || '报错,ac01表里没有找到该人员'; 165 End Updateidcard; 166 167 Procedure Cancelupdate(Pi_Bae007 In Varchar2, 168 Po_Fhz Out Varchar2, 169 Po_Msg Out Varchar2) Is 170 v_Aac002 Ac01.Aac002%Type; 171 v_Aac002y Ac25.Aac002%Type; 172 Begin 173 -- 初始化返回值 174 175 Po_Fhz := '-1'; 176 Po_Msg := '退单失败'; 177 Select a.Aac002_New 178 Into v_Aac002 179 From Ac25 a 180 Where a.Bae007 = Pi_Bae007; 181 Select a.Aac002 Into v_Aac002y From Ac25 a Where a.Bae007 = Pi_Bae007; 182 -- 更新复核标志 183 update ac25 set bce326 = '9' where bae007 = Pi_Bae007; 184 Begin 185 Update Ac01 Set Aac002 = v_Aac002y Where Aac002 = v_Aac002; 186 Po_Fhz := '1'; 187 Po_Msg := '成功'; 188 Exception 189 When Others Then 190 Po_Fhz := '-2'; 191 Po_Msg := '退单失败2'; 192 End; 193 194 End; 195 196 Procedure Updateidfh(Pi_Bae007 In Varchar2, 197 Pi_Aae012 In Varchar2, 198 Po_Fhz Out Varchar2, 199 Po_Msg Out Varchar2) Is 200 v_aac002 ac01.aac002%type; 201 v_cnt number(2); 202 Begin 203 -- 初始化返回值 204 Po_Fhz := '-1'; 205 Po_Msg := '添加复核人'; 206 Begin 207 Update Ac25 208 Set Aae012 = Pi_Aae012, Bce326 = '1' 209 Where Bae007 = Pi_Bae007; 210 select count(1) 211 into v_cnt 212 from ac01 213 where aac002 in (Select Aac002_New From Ac25 Where Bae007 = Pi_Bae007); 214 if v_cnt > 0 then 215 Select Aac002_New into v_aac002 From Ac25 Where Bae007 = Pi_Bae007; 216 Po_Fhz := '-2'; 217 Po_Msg := '修改后的新身份证号'||v_aac002||',在新系统已经存在,请回退重新办理!pkg_weiyl.Updateidfh ,Pi_Bae007:'||Pi_Bae007||',Pi_Aae012:'||Pi_Aae012; 218 return; 219 else 220 Update Ac01 221 Set Aac002 = 222 (Select b.Aac002_New From Ac25 b Where Bae007 = Pi_Bae007) 223 Where Aac002 = (Select Aac002 From Ac25 Where Bae007 = Pi_Bae007); 224 end if; 225 226 Po_Fhz := '1'; 227 Po_Msg := '添加复核人成功'; 228 End; 229 Exception 230 When Others Then 231 Po_Fhz := '-1'; 232 Po_Msg := '添加复核人失败,pkg_weiyl.Updateidfh ,Pi_Bae007:'||Pi_Bae007||',Pi_Aae012:'||Pi_Aae012; 233 End Updateidfh; 234 --身份证校验，把医保局，信息中心提出的身份证修改的条件 分出来，单独校验 235 --身份证校验，把医保局，信息中心提出的身份证修改的条件 分出来，单独校验 236 Procedure Validateidcard(Pi_Aac001 In Number, 237 Pi_Aac002_New In Varchar2, 238 Po_Fhz Out Varchar2, 239 Po_Msg Out Varchar2) Is 240 v_Prcname Varchar2(200) := c_Pkg_Name || '.validateIDCARD'; 241 v_Params Varchar2(500) := 'pi_aac001=' || Pi_Aac001; 242 v_Aae240 Skc81.Aae240%Type; 243 v_Lc31_Count Number(2); 244 v_Count_Skc81 Number(2); 245 v_Aac002_Tmp Ac25.Aac002_New%Type; 246 v_Count_Cardinfo Number(2); 247 v_Count_Ac60 Number(2); 248 v_Count_Kc21 Number(2); 249 /*function validate_aac002 return varchar2 is 250 v_aac002_new ac25.aac002_new%type; 251 v_aac002_after number(20); 252 begin 253 select a.aac002_new into v_aac002_new from ac25 a where a.aac001 = pi_aac001; 254 v_aac002_after := to_number(v_aac002_new); 255 return '1'; 256 exception 257 when others then 258 return '-1'; 259 end;*/ 260 Begin 261 -- 初始化 262 Po_Fhz := '1'; 263 -- 对修改后的身份证进行校验，防止不是纯数字，长度已经在前台进行了校验 264 Vali_Idcard_Lenandchar(Pi_Aac002_New, Po_Fhz, Po_Msg); 265 If Po_Fhz <> '1' Then 266 Return; 267 End If; 268 269 -- 1 医保局提出的要求，如果skc81.aae240>0 ，余额大于0 就不让改身份证。 270 Begin 271 -- 先要判断是否 skc81 是否有数据，有的话在判断 skc81.aae240是否大于0 ，如果skc81 没有数据的话 就不查 余额 272 Select Count(*) 273 Into v_Count_Skc81 274 From Skc81 a 275 Where a.Aac001 = Pi_Aac001; 276 If v_Count_Skc81 > 0 Then 277 Select Nvl(a.Aae240, 0) 278 Into v_Aae240 279 From Skc81 a 280 Where a.Aac001 = Pi_Aac001; 281 If v_Aae240 > 0 Then 282 /*po_fhz := v_prcName || '_91,医疗账户余额为' || v_aae240 || '元 ,因此不允许修改';*/ 283 Po_Fhz := v_Prcname || 284 '_91,职工医疗账户不为0，此模块不允许修改身份证号，请到本人参保地核实身份证号在其他业务模块办理！'; 285 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 286 Return; 287 /*享受五险待遇的不能修改，所以包括了失业 享受五险待遇的不能修改， 288 所以包括了失业 没办卡，又享受了失业待遇的 而且个人医保账户为0的*/ 289 --如果账户小于0，且 290 Elsif v_Aae240 <= 0 Then 291 Begin 292 Select Aac002 293 Into v_Aac002_Tmp 294 From Ac01 295 Where Aac001 = Pi_Aac001; 296 Select Count(*) 297 Into v_Count_Cardinfo 298 From Card_Info a 299 Where a.Idcard = v_Aac002_Tmp; 300 If v_Count_Cardinfo = 0 Then 301 --如果没有卡信息，然后再对五险进行判断， 302 --add 20150224 有卡也要进行判断 303 Select Count(*) 304 Into v_Count_Ac60 305 From Ac60 306 Where Aaa076 In ('0401', 307 '0403', 308 '0404', 309 '0411', 310 '0421', 311 '0702', 312 '0810', 313 '0821') 314 And Aae116 <> 4 315 And Aac001 = Pi_Aac001; 316 If v_Count_Ac60 > 0 Then 317 Po_Fhz := v_Prcname || '_-92,' || 318 '该人员没有卡信息，但是有享受待遇，不能修改身份证,请到本人参保地核实身份证号在其他业务模块办理！！'; 319 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, 320 Sqlcode, 321 Sqlerrm, 322 v_Params); 323 Return; 324 End If; 325 --add 20150224 有卡也要进行判断 326 Elsif v_Count_Cardinfo > 0 Then 327 Select Count(*) 328 Into v_Count_Ac60 329 From Ac60 330 Where Aaa076 In ('0401', 331 '0403', 332 '0404', 333 '0411', 334 '0421', 335 '0702', 336 '0810', 337 '0821') 338 And Aae116 <> 4 339 And Aac001 = Pi_Aac001; 340 If v_Count_Ac60 > 0 Then 341 Po_Fhz := v_Prcname || '_-92,' || 342 '该人员有卡信息，且有享受待遇，不能修改身份证,请到本人参保地核实身份证号在其他业务模块办理！'; 343 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, 344 Sqlcode, 345 Sqlerrm, 346 v_Params); 347 Return; 348 End If; 349 End If; 350 Exception 351 When No_Data_Found Then 352 Po_Fhz := v_Prcname || '_-91,没有找到该人员的基本信息'; 353 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, 354 Sqlcode, 355 Sqlerrm, 356 v_Params); 357 End; 358 End If; 359 End If; 360 361 End; 362 -- 2 信息中心提出的要求，有工伤认定的，也不允许修改 363 Begin 364 Select Count(*) Into v_Lc31_Count From Lc31 Where Aac001 = Pi_Aac001; 365 If v_Lc31_Count > 0 Then 366 Po_Fhz := v_Prcname || 367 '_92,该人员存在工伤认定记录，不能修改身份证,请到本人参保地核实身份证号在其他业务模块办理！'; 368 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 369 Return; 370 End If; 371 End; 372 373 -- 3 住院的不能修改身份证， 374 Begin 375 Select Count(1) 376 Into v_Count_Kc21 377 From Kc21 378 Where Aac001 = Pi_Aac001 379 And Ckc544 = '1'; 380 If v_Count_Kc21 > 0 Then 381 /*po_fhz := v_prcName||'_-93,该人员存在在院记录，不允许修改身份证！';*/ 382 Po_Fhz := '_-93,该人员存在在院记录，不能修改身份证,请到本人参保地核实身份证号在其他业务模块办理！'; 383 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 384 Rollback; 385 Return; 386 End If; 387 End; 388 389 Exception 390 When Others Then 391 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 392 Po_Fhz := '-1'; 393 End Validateidcard; 394 /*校验身份证是否合法，包括长度，身份证字母*/ 395 Procedure Vali_Idcard_Lenandchar(Pi_Aac002 In Varchar2, 396 Po_Fhz Out Varchar2, 397 Po_Msg Out Varchar2) Is 398 v_Aac002 Ac01.Aac002%Type; 399 v_Count Number(2); 400 v_Params Varchar2(500) := ',传入参数 pi_aac002 = ' || Pi_Aac002; 401 v_Procname Varchar2(50) := c_Pkg_Name || '.vali_IdCard_lenAndChar'; 402 v_Aac002_n Number(20); -- 数值型，用于接收转换后的身份证号 403 v_Char Varchar2(2); -- 用于接收字符 404 v_Char2 Varchar2(2); 405 Begin 406 -- 初始化返回值 407 Po_Fhz := '1'; 408 Po_Msg := Pkg_Fun.f_Errmsg(v_Procname, Sqlcode, Sqlerrm, v_Params); 409 Select Count(*) Into v_Count From Ac01 Where Aac002 = Pi_Aac002; 410 If v_Count > 0 Then 411 Select Trim(Pi_Aac002) Into v_Aac002 From Dual; 412 -- 检查是否为18位 413 If Length(v_Aac002) <> 18 Then 414 Po_Fhz := v_Procname || '_-91,身份证长度不为18位'; 415 Po_Msg := Pkg_Fun.f_Errmsg(v_Procname, Sqlcode, Sqlerrm, v_Params); 416 Return; 417 End If; 418 Begin 419 -- 检查前17位是否有字母 420 v_Aac002 := Substr(v_Aac002, 1, Length(v_Aac002) - 1); 421 v_Aac002_n := To_Number(v_Aac002); 422 Exception 423 When Value_Error Then 424 Po_Fhz := v_Procname || '_-92,身份证的前17位中含有非数值型字符'; 425 Po_Msg := Pkg_Fun.f_Errmsg(v_Procname, Sqlcode, Sqlerrm, v_Params); 426 Return; 427 End; 428 -- 如果最后一位是字母，; 429 Begin 430 -- 检查前17位是否有字母 431 Select Trim(Aac002) 432 Into v_Aac002 433 From Ac01 434 Where Aac002 = Pi_Aac002; 435 v_Aac002 := Substr(v_Aac002, Length(v_Aac002), 1); 436 v_Aac002_n := To_Number(v_Aac002); 437 Exception 438 When Value_Error Then 439 -- 说明最后一位是字母 440 -- 如果最后一位是字母，检查最后一位数是否为x; 441 Select Chr(88) Into v_Char From Dual; 442 Select Upper((Substr(v_Aac002, Length(v_Aac002), 1))) 443 Into v_Char2 444 From Dual; --存放截取的最后一位字符 445 If v_Char2 <> v_Char Then 446 Po_Fhz := v_Procname || '_-93,身份证的最后一位不是大写的X'; 447 Po_Msg := Pkg_Fun.f_Errmsg(v_Procname, 448 Sqlcode, 449 Sqlerrm, 450 v_Params); 451 Return; 452 End If; 453 End; 454 End If; 455 Exception 456 When Others Then 457 Po_Fhz := v_Procname || '_-94,未知错误'; 458 Po_Msg := Pkg_Fun.f_Errmsg(v_Procname, Sqlcode, Sqlerrm, v_Params); 459 End Vali_Idcard_Lenandchar; 460 461 --修改身份证号 462 Procedure Updateidcard_Fortest(Pi_Aac002 In Varchar2, 463 Pi_Aac002_New In Varchar2, 464 Pi_Aae013 In Varchar2, 465 Pi_Aae011 In Varchar2, 466 Pi_Bae001 In Varchar2, 467 Po_Fhz Out Varchar2, 468 Po_Msg Out Varchar2) Is 469 v_Aac001 Ac02.Aac001%Type; 470 v_Prc Varchar2(200); 471 v_Aab001 Ac02.Aab001%Type; 472 v_Yl_Count Number(2); --养老待遇记录数 473 v_Msg Varchar2(200); 474 v_Aaa076 Ac60.Aaa076%Type; 475 v_Prcname Varchar2(200); 476 v_Params Varchar2(500); 477 Begin 478 -- 初始化返回值 479 Po_Fhz := '1'; 480 Po_Msg := ''; 481 v_Prc := '.updateIDCard_fortest'; 482 v_Prcname := c_Pkg_Name || v_Prc; 483 v_Params := ',传入参数为:pi_aac002=' || Pi_Aac002 || ',pi_aac002_new=' || 484 Pi_Aac002_New || ',pi_aae013' || Pi_Aae013 || 485 ',pi_aae011=' || Pi_Aae011 || ',pi_bae001=' || Pi_Bae001; 486 Select Aac001, Aab001 487 Into v_Aac001, v_Aab001 488 From Ac01 489 Where Aac002 = Pi_Aac002; 490 -- 先做 是否有养老待遇的判断,如果有就直接返回，不更新ac01.aac002 491 Select Count(1) Into v_Yl_Count From Ac60 Where Aac001 = v_Aac001; 492 If v_Yl_Count > 0 Then 493 -- 如果有养老待遇，那么抛出更详细的结果，以便于前台更容易理解 494 -- 只取第一条 495 Select Aaa076 496 Into v_Aaa076 497 From Ac60 498 Where Aac001 = v_Aac001 499 And Rownum = 1; 500 Select Func_Wyl('AAA076', v_Aaa076) Into v_Msg From Dual; 501 Po_Fhz := '-1'; 502 Po_Msg := Pkg_Name || v_Prc || 503 '执行失败，该人员有待遇享受信息，此模块不允许修改身份证号!养老待遇类型为 "' || v_Msg || 504 '" 的养老待遇，且待遇状态正常'; 505 Return; 506 End If; 507 -- 更新ac01.AAC002 508 Update Ac01 Set Aac002 = Pi_Aac002_New Where Aac001 = v_Aac001; 509 Insert Into Ac25 510 (Aaz163, 511 Bae001, 512 Aab001, 513 Aac001, 514 Cae129, 515 Aac050, 516 Aae160, 517 Cac038, 518 Bae007, 519 Aae011, 520 Aae036, 521 Bce326, 522 Aae012, 523 Bhe949, 524 Cae030, 525 Cae031, 526 Aae013) 527 Values 528 (Seq_Bxgx_Aaz163.Nextval, --使用原来的序列号 529 Pi_Bae001, 530 v_Aab001, 531 v_Aac001, 532 '201310', 533 '50', -- 50,变更类型为 修改资料 534 '1933', -- 1933,变更原因, 其它 535 '', 536 '99999999', -- 业务流水号 537 Pi_Aae011, 538 To_Char(Sysdate, 'yyyymmddhh24miss'), 539 '1', 540 Pi_Aae011, 541 To_Char(Sysdate, 'yyyymmddhh24miss'), 542 '', 543 '', 544 Pi_Aae013); 545 546 Exception 547 When No_Data_Found Then 548 -- Sqlcode, Sqlerrm 549 Po_Fhz := v_Prcname || '_01'; 550 Po_Msg := '过程' || Pkg_Name || v_Prc || '报错,ac01表里没有找到该人员,sqlcode:' || 551 Sqlcode || ',sqlerrm:' || Sqlerrm; 552 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 553 Return; 554 End Updateidcard_Fortest; 555 556 -- 截取字符串 557 -- create or replace type split_type is table of varchar2(4000) 558 -- 取出字符串的方法 ： select column_value from table (pkg_weiyl.split_dh('dd,aa,134')); 559 Function Split_Dh(p_Str In Varchar2, 560 p_Delimiter In Varchar2 Default (',') --分隔符，默认逗号 561 ) Return Split_Type Is 562 j Int := 0; 563 i Int := 1; 564 Len Int := 0; 565 Len1 Int := 0; 566 Str Varchar2(4000); 567 My_Split Split_Type := Split_Type(); 568 Begin 569 -- 要分割的字符串的长度 570 Len := Length(p_Str); 571 -- 分隔符的长度 572 Len1 := Length(p_Delimiter); 573 574 While j < Len Loop 575 j := Instr(p_Str, p_Delimiter, i); 576 577 If j = 0 Then 578 j := Len; 579 Str := Substr(p_Str, i); 580 My_Split.Extend; 581 My_Split(My_Split.Count) := Str; 582 583 If i >= Len Then 584 Exit; 585 End If; 586 Else 587 Str := Substr(p_Str, i, j - i); 588 i := j + Len1; 589 My_Split.Extend; 590 My_Split(My_Split.Count) := Str; 591 End If; 592 End Loop; 593 594 Return My_Split; 595 End Split_Dh; 596 597 --字典转换，把代码值转换成对应的可理解的中文,卫永乐,20141105 598 Function Func_Wyl(Pi_Aaa100 Varchar2, Pi_Aaa102 Varchar2) Return Varchar2 Is 599 v_Aaa103 Varchar2(1000); 600 Begin 601 Select Aaa103 602 Into v_Aaa103 603 From Aa10 a 604 Where a.Aaa100 = Pi_Aaa100 605 And a.Aaa102 = Pi_Aaa102; 606 Return v_Aaa103; 607 Exception 608 When No_Data_Found Then 609 Dbms_Output.Put_Line('没有找到数据'); 610 End; 611 612 Function Func_Check_Para(Pi_Aab999 In Varchar2, --单位编号 613 Pi_Pch In Varchar2, --批次号 614 Pi_Aae001 In Number, 615 Pi_Aae036 In Varchar2, 616 Pi_Aac027 In Number) 617 /*拼接where条件 过程 check_sdnmdc 的游标的where条件*/ 618 Return Varchar2 Is 619 v_where varchar2(500); 620 Begin 621 /*v_where := ' where 1=1 and ';*/ 622 v_where := ' null or 1=1 '; 623 /*Select * From v_sdnm_sjql_dc Where Aab999 = To_Char(Pi_Aab999);*/ 624 625 if pi_aab999 <> '0' then 626 v_where := v_where||' and aab999='''||pi_aab999||''''; 627 elsif Pi_Aab999 = '0' then 628 v_where := ' null or 1=1 '; 629 end if; 630 if Pi_Aae001<> '0' then 631 v_where := v_where||' and aae001='||pi_aae001; 632 /*elsif Pi_Aae001 = '0' then 633 v_where := v_where||' and aae001='||pi_aae001;*/ 634 end if; 635 if Pi_Aae036<> '0' then 636 v_where := v_where||' and substr(aae036,1,6)='''||Pi_Aae036||''''; 637 /*elsif Pi_Aae036 = '0' then 638 v_where := v_where||' and substr(aae036,1,6)='''||Pi_Aae036||'''';*/ 639 end if; 640 if Pi_Aac027<> '0' then 641 v_where := v_where||' and aac027='||Pi_Aac027||' '; 642 end if; 643 Return v_where; 644 End Func_Check_Para; 645 646 --县级公立医院支付 647 Procedure Xjyyzf(Pi_Bae007 In Varchar2, 648 Pi_Operid In Varchar2, 649 Pi_Aae037 In Varchar2, 650 Pi_Aae038 In Varchar2, 651 Po_Fhz Out Varchar2, 652 Po_Msg Out Varchar2) Is 653 v_Bae007 Skc70.Bae007%Type; 654 v_Time Skc70.Aae015%Type; 655 Cursor c_Bae007 Is 656 Select Column_Value From Table(Pkg_Weiyl.Split_Dh(Pi_Bae007)); 657 Begin 658 Po_Msg := '成功'; 659 Po_Fhz := '1'; 660 Select To_Char(Sysdate, 'yyyymmddhh24miss') Into v_Time From Dual; 661 662 For v_Bae007 In c_Bae007 Loop 663 -- 清空改经办人的临时表 664 Delete From t_Skc70 a 665 Where a.Bae007 = Pi_Bae007 666 And a.Aae011 = Pi_Operid; 667 Insert Into t_Skc70 668 (Bae007, Aae011) 669 Values 670 (v_Bae007.Column_Value, Pi_Operid); 671 Update Skc70 672 Set Aae117 = '1', 673 Cae295 = Pi_Operid, 674 Aae015 = v_Time, 675 Aae037 = Substr(Pi_Aae037, 1, 8), 676 Aae038 = Substr(Pi_Aae038, 1, 8) 677 Where Bae007 = v_Bae007.Column_Value; 678 End Loop; 679 Exception 680 When Others Then 681 Rollback; 682 Po_Msg := '失败'; 683 Po_Fhz := '-1'; 684 End Xjyyzf; 685 686 --- 循环调用的过程，解决每次都要自己写一个declare plsql代码块， 687 --入参：还没写完 688 689 Procedure Prc_Xunhuan(Pi_Prcname In Varchar2, --要调用的过程名 690 Pi_Tabname In Varchar2, --cursor取值的表 691 Po_Fhz Out Varchar2, 692 Po_Msg Out Varchar2) Is 693 v_Prcname Varchar2(400) := '.prc_xunhuan'; 694 v_Params Varchar2(200) := 'pi_prcName=' || Pi_Prcname || 695 ',pi_tabName:' || Pi_Tabname; 696 /*cursor cur_xunhuan is 697 select * from pi_tabName where aac001 = '';*/ 698 Begin 699 700 Null; 701 Exception 702 When Others Then 703 Po_Fhz := ''; 704 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 705 End; 706 Procedure Xjyyzf_Callback(Pi_Bae007 In Varchar2, 707 Pi_Operid In Varchar2, 708 Po_Fhz Out Varchar2, 709 Po_Msg Out Varchar2) Is 710 v_Bae007 Skc70.Bae007%Type; 711 v_Time Skc70.Aae015%Type; 712 Cursor c_Bae007 Is 713 Select Column_Value From Table(Pkg_Weiyl.Split_Dh(Pi_Bae007)); 714 Begin 715 Po_Msg := '成功'; 716 Po_Fhz := '1'; 717 For v_Bae007 In c_Bae007 Loop 718 -- 清空改经办人的临时表 719 Delete From t_Skc70 a 720 Where a.Bae007 = v_Bae007.Column_Value 721 And a.Aae011 = Pi_Operid; 722 End Loop; 723 Exception 724 When Others Then 725 --rollback; 726 Po_Msg := '失败'; 727 Po_Fhz := '-1'; 728 End Xjyyzf_Callback; 729 730 --县级公立医院支付取消 731 Procedure Xjyyzfqx(Pi_Bae007 In Varchar2, 732 Po_Fhz Out Varchar2, 733 Po_Msg Out Varchar2) Is 734 v_Bae007 Skc70.Bae007%Type; 735 Cursor c_Bae007 Is 736 Select Column_Value From Table(Pkg_Weiyl.Split_Dh(Pi_Bae007)); 737 Begin 738 Po_Msg := '成功'; 739 Po_Fhz := '1'; 740 For v_Bae007 In c_Bae007 Loop 741 --清空临时表 742 Delete From t_Skc70 Where Bae007 = v_Bae007.Column_Value; 743 Update Skc70 Set Aae117 = '0' Where Bae007 = v_Bae007.Column_Value; 744 End Loop; 745 Exception 746 When Others Then 747 Po_Msg := '失败'; 748 Po_Fhz := '-1'; 749 End Xjyyzfqx; 750 /*自治事务*/ 751 Procedure Autonomous_Tran(Pi_Aac001 In Varchar2, 752 Po_Fhz Out Varchar2, 753 Po_Msg Out Varchar2) Is 754 Pragma Autonomous_Transaction; --自治事务关键字 755 v_Proname Varchar2(400); 756 v_Aab001 Number(20); 757 Begin 758 Po_Fhz := '1'; 759 Po_Msg := '成功！'; 760 v_Aab001 := Seq_Bxgx_Aab001.Nextval; 761 Insert Into Xzl_One (Aac002) Values (v_Aab001); -- v_aab001 是number,竟然可以插入varchar2类型字段 762 --commit; 763 Update Xzl_One 764 Set Aac003 = 765 (v_Aab001 || '姓名') 766 Where Aac002 = v_Aab001 || ''; --但是查却没法查到 767 Commit; 768 Exception 769 When Others Then 770 Rollback; 771 Po_Fhz := '-1'; 772 Po_Msg := '失败,' || Sqlcode || ',sqlerrm:' || Sqlerrm; 773 Return; 774 End Autonomous_Tran; 775 /*手工添加表级锁，使用场景，如果要操作一张大表的大部分数据， 776 如果不加表级锁，那么就会耗费大量的资源，这种情况下可以使用 777 手工给表加锁，释放方式 rollback，或者commit 778 使用目的：更改 ac02_tmp表 的险种状态，ac02_tmp 表结构和数据 779 和ac02的一样 780 */ 781 Procedure Update_Ac02_Aae140(Pi_Aae140 In Varchar2, 782 Pi_Fhz Out Varchar2, 783 Po_Msg Out Varchar2) Is 784 Cursor Cur_Ac02 Return Ac02_Tmp%Rowtype Is 785 Select * From Ac02_Tmp Where Aae140 = Pi_Aae140; 786 v_Cur_Ac02 Ac02_Tmp%Rowtype; 787 Begin 788 --sys.dbms_profiler.start_profiler; 789 Lock Table Ac02_Tmp In Exclusive Mode Nowait; 790 Open Cur_Ac02; 791 Loop 792 Fetch Cur_Ac02 793 Into v_Cur_Ac02; 794 Exit When Cur_Ac02%Notfound; --检查是否找到， 795 -- 找到的话就就更新 796 Update Ac02_Tmp 797 Set Aac008 = '8' 798 Where Aae140 = Pi_Aae140 799 And Aac008 = '2'; 800 Null; 801 End Loop; 802 Close Cur_Ac02; 803 Commit; --释放表级锁 804 --sys.dbms_profiler.stop_profiler; 805 End Update_Ac02_Aae140; 806 807 Procedure Querycheck(Pi_Aac002 In Varchar2, 808 Pi_Aac003 In Varchar2, 809 Po_Fhz Out Varchar2, 810 Po_Msg Out Varchar2) Is 811 v_Count Number(2); 812 Begin 813 --初始化返回值 814 Po_Fhz := '-1'; 815 Po_Msg := '失败,该人员没有卡信息'; 816 If Pi_Aac002 = '0' Then 817 Select Count(*) Into v_Count From Card_Info Where Name = Pi_Aac003; 818 If v_Count > 0 Then 819 Po_Fhz := '1'; 820 Po_Msg := '成功'; 821 End If; 822 End If; 823 If Pi_Aac003 = '0' Then 824 Select Count(*) Into v_Count From Card_Info Where Idcard = Pi_Aac002; 825 If v_Count > 0 Then 826 Po_Fhz := '1'; 827 Po_Msg := '成功'; 828 End If; 829 End If; 830 End; 831 Procedure Check_Ac02(Pi_Aac002 In Varchar2, 832 Po_Aac001 Out Number, 833 Po_Cac012 Out Varchar2, 834 Po_Aab001 Out Varchar2, 835 Po_Fhz Out Varchar2, 836 Po_Msg Out Varchar2) Is 837 v_Count1 Number(2); 838 v_Count2 Number(2); 839 v_Count3 Number(2); 840 v_Aab001 Ac02.Aab001%Type; 841 v_Aac001 Ac01.Aac001%Type; 842 v_Cac012 Sac01.Cac012%Type; 843 Begin 844 -- 初始化返回值 845 Po_Fhz := '1'; 846 Select Count(1) Into v_Count1 From Ac01 Where Aac002 = Pi_Aac002; 847 If v_Count1 <= 0 Then 848 Po_Msg := '该人员不存在'; 849 Po_Fhz := '-1'; 850 Return; 851 End If; 852 Select Aac001 Into v_Aac001 From Ac01 Where Aac002 = Pi_Aac002; 853 Select Count(1) Into v_Count2 From Sac01 Where Aac001 = v_Aac001; 854 If v_Count2 <= 0 Then 855 Po_Msg := '该人员缺少对应的sac01的信息,'; 856 Po_Fhz := '-1'; 857 Return; 858 End If; 859 Select a.Cac012 Into v_Cac012 From Sac01 a Where Aac001 = v_Aac001; 860 Select Count(1) 861 Into v_Count3 862 From Ac02 863 Where Aae140 = '342' 864 And Aac001 = v_Aac001 865 And Aac008 = '1'; 866 If v_Count3 <= 0 Then 867 Po_Msg := '该人员没有正常参保'; 868 Po_Fhz := '-1'; 869 Return; 870 End If; 871 Select Distinct (Aab001) 872 Into v_Aab001 873 From Ac02 874 Where Aac001 = v_Aac001 875 And Aae140 = '342' 876 And Aac008 = '1'; 877 Po_Aab001 := v_Aab001; 878 Po_Aac001 := v_Aac001; 879 Po_Cac012 := v_Cac012; 880 End; 881 882 Procedure Check_Skc84(Pi_Aac001 In Varchar2, 883 Po_Fhz Out Varchar2, 884 Po_Msg Out Varchar2) Is 885 v_Count1 Number(2); 886 v_Count2 Number(2); 887 v_Aac001 Ac01.Aac001%Type; 888 Begin 889 -- 初始化返回值 890 Po_Fhz := '1'; 891 Select Count(1) 892 Into v_Count1 893 From Skc84 894 Where Aac001 = Pi_Aac001 895 And Aae140 = '342'; 896 If v_Count1 <= 0 Then 897 Po_Msg := '该人员不存在当年的医保缴费'; 898 Po_Fhz := '-1'; 899 Return; 900 End If; 901 End; 902 /*失地农民数据清理导出前， 903 把导出的数据保存到ic58里，防止再次导出 904 */ 905 Procedure Check_Sdnmdcc(Pi_Aab999 In Varchar2, 906 PI_PCH in varchar2, 907 Po_Fhz Out Varchar2, 908 Po_Msg Out Varchar2) Is 909 v_Aac001 Ac01.Aac001%Type; 910 v_Prcname Varchar2(200); 911 v_Params Varchar2(200); 912 v_Ic58_Count Number(5); 913 Cursor c_Ydcry Is 914 Select * From v_Sdnm_Sjql_Dc Where Aab999 = To_Char(Pi_Aab999); 915 Begin 916 v_Prcname := 'pkg_weiyl.check_sdnmdc'; 917 v_Params := 'pi_aab999:' || Pi_Aab999; 918 Po_Fhz := '1'; 919 Po_Msg := '成功'; 920 /*清空ic61*/ 921 For c_Tmp In c_Ydcry Loop 922 Insert Into Ic58 (Aac001, Aab999) Values (c_Tmp.Aac001, Pi_Aab999); 923 /*导出用这个表*/ 924 Insert Into Ic61 (Aac001, Aab999) Values (c_Tmp.Aac001, Pi_Aab999); 925 /*加入批次号 */ 926 insert into ic58_dc (aac001,aab999,aaz601) values (c_Tmp.Aac001,Pi_Aab999,PI_PCH); 927 End Loop; 928 Select Count(1) Into v_Ic58_Count From Ic58 Where Aab999 = Pi_Aab999; 929 If v_Ic58_Count > 0 Then 930 Po_Fhz := '2'; 931 Po_Msg := '该单位已经进行过导出业务的操作，此次导出将导出该单位下剩余部分的人员，如果之前已经全部导出，那么此次将不导出数据'; 932 End If; 933 Exception 934 When Others Then 935 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 936 Po_Fhz := '-1'; 937 End Check_Sdnmdcc; 938 /* 939 现在正在使用的失地农民数据导出 940 这里使用了动态sql， 941 注意点：在存储过程里的增删改查语句如果有拼接， 942 那么一定要用动态sql,否则可能能够编译通过，但是 943 执行的时候没有达到预期。因为：拼接的where条件很可能被 944 引擎当作普通字符串处理，也就什么都没查得到，自然更新数据就没法更新成功 945 */ 946 Procedure Check_Sdnmdc(Pi_Bae001 In Varchar2, 947 Pi_Aab999 In Varchar2, 948 Pi_Pch In Varchar2, 949 /* PI_GLT in varchar2,*/ 950 Pi_Aae001 In Varchar2, 951 Pi_Aae036 In Varchar2, 952 Pi_Aac027 In Varchar2, 953 Pi_Bzw In Varchar2, 954 Pi_Oper In Varchar2, 955 Po_Fhz Out Varchar2, 956 Po_Msg Out Varchar2) Is 957 v_Aac001 Ac01.Aac001%Type; 958 v_Prcname Varchar2(200); 959 v_Params Varchar2(200); 960 v_Ic58_Count Number(5); 961 v_Tmp Varchar2(400); 962 v_Sql Varchar2(1000); 963 Cursor c_Ydcry Is 964 Select * 965 From v_Sdnm_Sjql_Dc_Multi 966 Where Aab999 = Func_Check_Para(Pi_Aab999, 967 Pi_Pch, 968 Pi_Aae001, 969 Pi_Aae036, 970 Pi_Aac027); 971 Begin 972 973 v_Prcname := 'pkg_weiyl.check_sdnmdc'; 974 v_Params := 'pi_aab999:' || Pi_Aab999; 975 Po_Fhz := '1'; 976 Po_Msg := '成功'; 977 /*拼接where条件*/ 978 Select Func_Check_Para(Pi_Aab999, 979 Pi_Pch, 980 Pi_Aae001, 981 Pi_Aae036, 982 Pi_Aac027) 983 Into v_Tmp 984 From Dual; 985 /*insert into ic58_dcsj() values (select * from v_sdnm_sjql_dc a where 1=1 and aab999 = '02002489');*/ 986 /*v_Sql := 'Insert Into Ic58_Dcsj 987 Select a.*, ' || To_Char(Sysdate, 'yyyymmddhh24miss') || ',' || 988 Pi_Pch || ',' || '1' || ' 989 From v_Sdnm_Sjql_Dc a 990 Where a.bae001 = '''||Pi_Bae001||''' and Aab999 = ' || v_Tmp || 991 ' And Not Exists (Select * 992 From Ic58_Dcsj z 993 Where z.Aaz288 = a.Aaz288 994 And z.Aac001 = a.Aac001) ';*/ 995 -- 20160505 altered 996 /* v_Sql := 'Insert Into Ic58_Dcsj 997 Select a.*, ' || To_Char(Sysdate, 'yyyymmddhh24miss') || ',' || 998 Pi_Pch || ',' || '''' || Pi_Bzw || '''' || ',''' || Pi_Oper || ''' 999 From v_Sdnm_Sjql_Dc a 1000 Where Aab999 = ' || v_Tmp || ' and bae001 = ''' || 1001 pi_bae001 || ''' And Not Exists (Select * 1002 From Ic58_Dcsj z 1003 Where z.Aaz288 = a.Aaz288 1004 And z.Aac001 = a.Aac001) ';*/ 1005 v_Sql := 'Insert Into Ic58_Dcsj 1006 Select a.*, '||'1,' || To_Char(Sysdate, 'yyyymmddhh24miss') || ',' || 1007 Pi_Pch || ',' || '''' || Pi_Bzw || '''' || ',''' || Pi_Oper || ''' 1008 From v_Sdnm_Sjql_Dc a 1009 Where Aab999 = ' || v_Tmp || ' and bae001 = ''' || 1010 pi_bae001 || ''' And Not Exists (Select * 1011 From Ic58_Dcsj z 1012 Where z.Aaz288 = a.Aaz288 1013 And z.Aac001 = a.Aac001) '; 1014 Execute Immediate v_Sql; 1015 /*清空ic61*/ 1016 /*For c_Tmp In c_Ydcry Loop 1017 Insert Into Ic58 (Aac001, Aab999) Values (c_Tmp.Aac001, Pi_Aab999); 1018 导出用这个表 1019 Insert Into Ic61 (Aac001, Aab999) Values (c_Tmp.Aac001, Pi_Aab999); 1020 加入批次号 1021 Insert Into Ic58_Dc 1022 (Aac001, Aab999, Aaz601, Bzw) 1023 Values 1024 (c_Tmp.Aac001, Pi_Aab999, Pi_Pch, '1'); 1025 备份失地农民数据 1026 update ic58_dcsj q set q.dcsj=To_Char(Sysdate, 'yyyymmddhh24miss'),q.aaz601=PI_PCH; 1027 End Loop;*/ 1028 /*Select Count(1) Into v_Ic58_Count From Ic58 Where Aab999 = Pi_Aab999;*/ 1029 Select Count(1) 1030 Into v_Ic58_Count 1031 From Ic58_Dcsj 1032 Where Aaz601 <> Pi_Pch 1033 And Aab999 = Pi_Aab999; 1034 If v_Ic58_Count > 0 Then 1035 Po_Fhz := '2'; 1036 Po_Msg := '该单位已经进行过导出业务的操作，此次导出将导出该单位下剩余部分的人员，如果之前已经全部导出，那么此次将不导出数据'; 1037 End If; 1038 Exception 1039 When Others Then 1040 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 1041 Po_Fhz := '-1'; 1042 End Check_Sdnmdc; 1043 1044 /*失地农民数据清理导出前， 1045 把导出的数据保存到ic58里，防止再次导出 1046 导出单条的check 1047 02002489 1048 */ 1049 Procedure Check_Sdnmdc_Multi(Pi_Bae001 In Varchar2, 1050 Pi_Aab999 In Varchar2, 1051 Pi_Pch In Varchar2, 1052 /* PI_GLT in varchar2,*/ 1053 Pi_Aae001 In Varchar2, 1054 Pi_Aae036 In Varchar2, 1055 Pi_Aac027 In Varchar2, 1056 Pi_Bzw In Varchar2, 1057 Pi_Oper In Varchar2, 1058 Po_Fhz Out Varchar2, 1059 Po_Msg Out Varchar2) Is 1060 v_Aac001 Ac01.Aac001%Type; 1061 v_Prcname Varchar2(200); 1062 v_Params Varchar2(200); 1063 v_Ic58_Count Number(5); 1064 v_Tmp Varchar2(400); 1065 v_Sql Varchar2(1000); 1066 Cursor c_Ydcry Is 1067 Select * 1068 From v_Sdnm_Sjql_Dc 1069 Where Aab999 = Func_Check_Para(Pi_Aab999, 1070 Pi_Pch, 1071 Pi_Aae001, 1072 Pi_Aae036, 1073 Pi_Aac027); 1074 Begin 1075 1076 v_Prcname := 'pkg_weiyl.Check_Sdnmdc_Multi'; 1077 v_Params := 'pi_aab999:' || Pi_Aab999; 1078 Po_Fhz := '1'; 1079 Po_Msg := '成功'; 1080 /*拼接where条件*/ 1081 Select Func_Check_Para(Pi_Aab999, 1082 Pi_Pch, 1083 Pi_Aae001, 1084 Pi_Aae036, 1085 Pi_Aac027) 1086 Into v_Tmp 1087 From Dual; 1088 /*insert into ic58_dcsj() values (select * from v_sdnm_sjql_dc a where 1=1 and aab999 = '02002489');*/ 1089 /*v_Sql := 'Insert Into Ic58_Dcsj 1090 Select a.*, ' || To_Char(Sysdate, 'yyyymmddhh24miss') || ',' || 1091 Pi_Pch || ',' || ''''||Pi_Bzw||''''||','''|| Pi_Oper|| ''' 1092 From v_Sdnm_Sjql_Dc_multi a 1093 Where Aab999 = ' || v_Tmp || 1094 ' and bae001 = '''||pi_bae001||''' And Not Exists (Select * 1095 From Ic58_Dcsj z 1096 Where z.Aaz288 = a.Aaz288 1097 And z.Aac001 = a.Aac001) ';*/ 1098 v_Sql := 'Insert Into Ic58_Dcsj 1099 Select a.*, ' || To_Char(Sysdate, 'yyyymmddhh24miss') || ',' || 1100 Pi_Pch || ',' || ''''||Pi_Bzw||''''||','''|| Pi_Oper|| ''' 1101 From v_Sdnm_Sjql_Dc_multi a 1102 Where Aab999 = ' || v_Tmp || 1103 ' and bae001 = '''||pi_bae001||''' And Not Exists (Select * 1104 From Ic58_Dcsj z 1105 Where z.Aaz288 = a.Aaz288 1106 And z.Aac001 = a.Aac001) '; 1107 Execute Immediate v_Sql; 1108 /*清空ic61*/ 1109 /*For c_Tmp In c_Ydcry Loop 1110 Insert Into Ic58 (Aac001, Aab999) Values (c_Tmp.Aac001, Pi_Aab999); 1111 导出用这个表 1112 Insert Into Ic61 (Aac001, Aab999) Values (c_Tmp.Aac001, Pi_Aab999); 1113 加入批次号 1114 Insert Into Ic58_Dc 1115 (Aac001, Aab999, Aaz601, Bzw) 1116 Values 1117 (c_Tmp.Aac001, Pi_Aab999, Pi_Pch, '1'); 1118 备份失地农民数据 1119 update ic58_dcsj q set q.dcsj=To_Char(Sysdate, 'yyyymmddhh24miss'),q.aaz601=PI_PCH; 1120 End Loop;*/ 1121 /*Select Count(1) Into v_Ic58_Count From Ic58 Where Aab999 = Pi_Aab999;*/ 1122 Select Count(1) 1123 Into v_Ic58_Count 1124 From Ic58_Dcsj 1125 Where Aaz601 <> Pi_Pch 1126 And Aab999 = Pi_Aab999; 1127 If v_Ic58_Count > 0 Then 1128 Po_Fhz := '2'; 1129 Po_Msg := '该单位已经进行过导出业务的操作，此次导出将导出该单位下剩余部分的人员，如果之前已经全部导出，那么此次将不导出数据'; 1130 End If; 1131 Exception 1132 When Others Then 1133 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 1134 Po_Fhz := '-1'; 1135 End Check_Sdnmdc_Multi; 1136 1137 Procedure Updatekbb5(Pi_Bae007 In Varchar2, 1138 Pi_Ckz545 In Varchar2, 1139 Pi_Ckb626 In Varchar2, 1140 Pi_Ckb627 In Varchar2, 1141 Pi_Ckb629 In Varchar2, 1142 Pi_Ckb630 In Varchar2, 1143 Po_Fhz Out Varchar2, 1144 Po_Msg Out Varchar2) Is 1145 Begin 1146 Po_Fhz := '1'; 1147 Po_Msg := '成功'; 1148 Update Kbb5 1149 Set Ckz545 = Pi_Ckz545, 1150 Ckb626 = Pi_Ckb626, 1151 Ckb627 = Pi_Ckb627, 1152 Ckb629 = Pi_Ckb629, 1153 Ckb630 = Pi_Ckb630 1154 Where Bae007 = Pi_Bae007; 1155 End; 1156 Procedure Getaaz601(Pi_Rc In Varchar2, 1157 po_aaz601 out number, 1158 Po_Fhz Out Varchar2, 1159 Po_Msg Out Varchar2) Is 1160 v_aaz601 ic58_dc.aaz601%type ; 1161 Begin 1162 Po_Fhz := '1'; 1163 Po_Msg := '成功'; 1164 Select Seq_Ggyw_Aaz601.Nextval into po_aaz601 From Dual; 1165 exception 1166 when others then 1167 po_msg := 'pkg_weiyl.Getaaz601 获取批次号失败'; 1168 Po_Fhz := '-1'; 1169 End; 1170 /* 1171 知识库相关过程 1172 20160323 by weiyongle 1173 */ 1174 Procedure Insertfw_Zsk(PI_AAA200 in varchar2, 1175 Pi_Aae202 In Varchar2, 1176 Pi_Aaa203 In Varchar2, 1177 Pi_Aae008 In Varchar2, 1178 PI_AAE011 IN VARCHAR2, 1179 PI_AAE906 IN VARCHAR2, 1180 PI_BZ IN VARCHAR2, 1181 Po_Fhz Out Varchar2, 1182 Po_Msg Out Varchar2) is 1183 begin 1184 po_fhz := '1'; 1185 po_msg := '成功'; 1186 null; 1187 /*如果是'1'，那么说明是修改*/ 1188 if pi_bz = '1' then 1189 update fw_zsk set aae202=Pi_Aae202,aaa203=Pi_Aaa203,aae008=pi_aae008,aae011 = PI_AAE011 where aae906 = PI_AAE906; 1190 elsif pi_bz ='0' then 1191 insert into fw_zsk (aaa200,aae202,aaa203,aae008,aae011,aae036,aae906) values (PI_AAA200,Pi_Aae202,Pi_Aaa203,pi_aae008,PI_AAE011,To_Char(Sysdate, 'yyyymmddhh24miss'),SEQ_FW_AAE906.Nextval); 1192 end if; 1193 exception 1194 when others then 1195 po_fhz := '-1'; 1196 po_msg := '失败'; 1197 end Insertfw_Zsk; 1198 /*拼接两个字符串, 1199 练手嵌套存储过程 1200 注意点：使用嵌套存储过程时，在declaration中可以同时定义变量 1201 但是 定义的变量只能够写在嵌套存储过程的前面，否则没法编译通过 1202 20160407 1203 */ 1204 procedure testNestedPro(pi_xing in varchar2, 1205 pi_ming in varchar2, 1206 po_fhz out varchar2, 1207 po_msg out varchar2) is 1208 v_name varchar2(200); 1209 v_test ac01.aac001%type; 1210 procedure print_name(pi_para1 in varchar2, 1211 pi_para2 in varchar2, 1212 po_name out varchar2) is 1213 begin 1214 po_name := pi_para1 || pi_para2; 1215 exception 1216 when others then 1217 po_name := '没有成功拼接两个入参'; 1218 end print_name; 1219 begin 1220 --初始化 返回值 1221 1222 po_fhz := '1'; 1223 po_msg := '失败'; 1224 print_name(pi_xing, pi_ming, v_name); 1225 if (length(v_name) > 100) then 1226 po_msg := '拼接的字符串太长！'; 1227 else 1228 po_msg := '成功拼接,拼接好的字符串为：' || v_name; 1229 end if; 1230 begin 1231 null; 1232 end ; 1233 begin 1234 /*select aac002 into v_test from ac01 where aac001 = 1234;*/ 1235 update ac01 set aac001 = 1000035129 where aac002 = '511011198604126824'; 1236 exception when others then 1237 pkg_weiyltools.get_e_msg(sqlerrm,'AC01','aac001','1000035129',' where aac002 = 511011198604126824',po_fhz,po_msg); 1238 return; 1239 end; 1240 1241 /*exception 1242 when others then 1243 po_fhz := '-1'; 1244 po_msg := '失败';*/ 1245 end testNestedPro; 1246 /*触摸屏查询标记*/ 1247 procedure cancelCmp(PI_SERIALNUM in varchar2, 1248 po_fhz out varchar2, 1249 po_msg out varchar2) is 1250 v_Prc varchar2(40); 1251 v_Prcname varchar2(50); 1252 v_Params varchar2(400); 1253 v_Sqlerrm varchar2(100); 1254 begin 1255 --初始化 1256 -- 初始化返回值 1257 Po_Fhz := '1'; 1258 Po_Msg := ''; 1259 v_Prc := '.updateIDCard'; 1260 v_Prcname := c_Pkg_Name || v_Prc; 1261 v_Params := ',传入参数为:PI_SERIALNUM=' || PI_SERIALNUM; 1262 update printserialnum 1263 set bce326 = '1' 1264 where SERIALNUM = PI_SERIALNUM 1265 and bce326 <> '1'; 1266 exception 1267 when others then 1268 v_Sqlerrm := Substr(Sqlerrm, 1, 9); 1269 /* 捕获,无效数字*/ 1270 If v_Sqlerrm = 'ORA-01722' Then 1271 Po_Fhz := v_Prcname || '_3'; 1272 Po_Msg := '数据库报错,提示无效数字,可能的原因是：入参：' || PI_SERIALNUM || 1273 '可能是数值型，应该为字符串类型'; 1274 return; 1275 else 1276 po_fhz := v_Prcname || '_1'; 1277 po_msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 1278 return; 1279 end if; 1280 end cancelCmp; 1281 1282 /*取消征集通知单*/ 1283 procedure cancelAaz288(PI_OPERID in varchar2, 1284 PI_AAZ288 in varchar2, 1285 po_fhz out varchar2, 1286 po_msg out varchar2) is 1287 v_cnt_ab07 number(3); 1288 v_cnt_ad21 number(2); 1289 v_cnt_ac43 number(4); 1290 v_Prc varchar2(400); 1291 v_aae111 ad21.aae111%type; 1292 begin 1293 -- 初始化返回值 1294 Po_Fhz := '1'; 1295 Po_Msg := '成功'; 1296 v_Prc := '.cancelAaz288'; 1297 /*校验*/ 1298 select distinct (aae111) 1299 into v_aae111 1300 from V_BXGX_JJGL_ZZZJ_DWZSJHMX 1301 where aaz288 = PI_AAZ288; 1302 if v_aae111 <> '0' then 1303 Po_Fhz := '-11'; 1304 Po_Msg := '征收计划的到账标识不为【未到账】'; 1305 return; 1306 end if; 1307 /*更新ab07*/ 1308 select count(1) into v_cnt_ab07 from ab07 where aaz288 = PI_AAZ288; 1309 if v_cnt_ab07 > 0 then 1310 update ab07 set aaz288 = null where aaz288 = PI_AAZ288; 1311 end if; 1312 /*更新ad21*/ 1313 select count(1) into v_cnt_ad21 from ad21 where aaz288 = PI_AAZ288; 1314 if v_cnt_ad21 > 0 then 1315 update ad21 1316 set cae033 = 2, 1317 cae030 = PI_OPERID, 1318 cae031 = to_char(sysdate, 'yyyymmddhh24miss') 1319 where aaz288 = PI_AAZ288; 1320 end if; 1321 /*更新ab43*/ 1322 select count(1) into v_cnt_ac43 from ac43 where aaz288 = PI_AAZ288; 1323 if v_cnt_ac43 > 0 then 1324 update ac43 set aaz288 = '' where aaz288 = PI_AAZ288; 1325 end if; 1326 exception 1327 when others then 1328 -- Sqlcode, Sqlerrm 1329 Po_Fhz := '-1'; 1330 Po_Msg := '过程' || Pkg_Name || v_Prc || '报错,ac01表里没有找到该人员'; 1331 end cancelAaz288; 1332 /*删除知识库核销的附件内容，否则数据里的不必要的附件会越来越多*/ 1333 procedure deleteZskFile(PI_CAE232 in varchar2, 1334 po_fhz out varchar2, 1335 po_msg out varchar2) IS 1336 v_cae232_cnt number(4); 1337 v_Prc varchar2(100); 1338 v_Prcname varchar2(100); 1339 v_Params varchar2(400); 1340 begin 1341 --初始化 1342 Po_Fhz := '1'; 1343 Po_Msg := '成功'; 1344 v_Prc := '.updateIDCard'; 1345 v_Prcname := c_Pkg_Name || v_Prc; 1346 v_Params := ',传入参数为:PI_CAE232=' || PI_CAE232; 1347 select count(1) 1348 into v_cae232_cnt 1349 from wyl_file a 1350 where a.cae232 = PI_CAE232; 1351 if v_cae232_cnt > 0 then 1352 delete from wyl_file where cae232 = PI_CAE232; 1353 end if; 1354 --异常 1355 exception 1356 when others then 1357 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 1358 Po_Fhz := '-1'; 1359 end deleteZskFile; 1360 /* 1361 生成项目明细统计报表数据 1362 */ 1363 procedure generatexmmx(pi_ksrq in varchar2, 1364 pi_zzrq in varchar2, 1365 po_fhz out varchar2, 1366 po_msg out varchar2) is 1367 v_count number(3); 1368 v_tongji fw_xmmx%rowtype; 1369 cursor cur_fw_xmmx is 1370 select * 1371 from fw_xmmx 1372 where aae008 >= pi_ksrq 1373 and aae008 <= pi_zzrq; 1374 cursor cur_jhnywc is 1375 --计划内完成验证的 1376 select a.bae010 , count(1) num1 1377 from fw_xmmx a 1378 where a.aae008 >= pi_ksrq 1379 and a.aae008 <= pi_zzrq 1380 and a.aae022 = '01' 1381 and a.aae026 = '1' 1382 and cae011 = '04' 1383 group by a.bae010; 1384 1385 cursor cur_jhwywc is 1386 --计划外完成验证的 1387 select a.bae010 , count(1) num1 1388 from fw_xmmx a 1389 where a.aae008 >= pi_ksrq 1390 and a.aae008 <= pi_zzrq 1391 and a.aae022 = '02' 1392 and a.aae026 = '1' 1393 and cae011 = '04' 1394 group by a.bae010; 1395 1396 cursor cur_jhnwwc is 1397 --计划内未完成验证的 3 1398 select a.bae010 , count(1) num2 1399 from fw_xmmx a 1400 where a.aae008 >= pi_ksrq 1401 and a.aae008 <= pi_zzrq 1402 and a.aae022 = '01' 1403 and a.aae026 is null 1404 and cae011 <> '04' 1405 and cae011 <> '05' 1406 group by a.bae010; 1407 1408 1409 cursor cur_jhwwwc is 1410 --计划外未完成验证的 1411 select a.bae010 , count(1) num2 1412 from fw_xmmx a 1413 where a.aae008 >= pi_ksrq 1414 and a.aae008 <= pi_zzrq 1415 and a.aae022 = '02' 1416 and a.aae026 is null 1417 and cae011 <> '04' 1418 and cae011 <> '05' 1419 group by a.bae010; 1420 1421 cursor cur_jhnywc_gs is 1422 --计划内完成工时 4 1423 select a.bae010 , sum(a.aae018) jhgs,sum(a.aae019) sjgs 1424 from fw_xmmx a 1425 where a.aae008 >= pi_ksrq 1426 and a.aae008 <= pi_zzrq 1427 and a.aae022 = '01' 1428 and a.aae026 = '1' 1429 and cae011 = '04' 1430 group by a.bae010; 1431 1432 cursor cur_jhwywc_gs is 1433 --计划外完成工时 1434 select a.bae010 , sum(a.aae018) jhgs,sum(a.aae019) sjgs 1435 from fw_xmmx a 1436 where a.aae008 >= pi_ksrq 1437 and a.aae008 <= pi_zzrq 1438 and a.aae022 = '02' 1439 and a.aae026 = '1' 1440 and cae011 = '04' 1441 group by a.bae010; 1442 1443 cursor cur_jhnwwc_gs is 1444 --计划内未完成工时 1445 select a.bae010, sum(a.aae018) jhgs, sum(a.aae019) sjgs 1446 from fw_xmmx a 1447 where a.aae008 >= pi_ksrq 1448 and a.aae008 <= pi_zzrq 1449 and a.aae022 = '01' 1450 and a.aae026 is null 1451 and cae011 <> '04' 1452 and cae011 <> '05' 1453 group by a.bae010; 1454 1455 1456 cursor cur_jhwwwc_gs is 1457 --计划外未完成工时 1458 select a.bae010, sum(a.aae018) jhgs, sum(a.aae019) sjgs 1459 from fw_xmmx a 1460 where a.aae008 >= pi_ksrq 1461 and a.aae008 <= pi_zzrq 1462 and a.aae022 = '02' 1463 and a.aae026 is null 1464 and cae011 <> '04' 1465 and cae011 <> '05' 1466 group by a.bae010; 1467 1468 begin 1469 po_fhz := '1'; 1470 po_msg := '成功'; 1471 delete from fw_xmmx_tongji; 1472 insert into fw_xmmx_tongji (bae010) select AAA102 from aa10 where aaa100 = 'BAE010'; 1473 /*循环插入计划内 已经 完成验证的*/ 1474 for v_cur in cur_jhnywc loop 1475 update fw_xmmx_tongji set jhn_ywc=v_cur.num1,ksrq=pi_ksrq,zzrq=pi_zzrq where bae010 = v_cur.bae010; 1476 null; 1477 end loop; 1478 1479 /*循环插入计划外 已经 完成验证的*/ 1480 for v_cur_ in cur_jhwywc loop 1481 update fw_xmmx_tongji set jhw_ywc=v_cur_.num1,ksrq=pi_ksrq,zzrq=pi_zzrq where bae010 = v_cur_.bae010; 1482 null; 1483 end loop; 1484 1485 /*循环更新计划内 未 完成验证的*/ 1486 for v_cur2 in cur_jhnwwc loop 1487 update fw_xmmx_tongji k set jhn_wwc= v_cur2.num2 where k.bae010 = v_cur2.bae010; 1488 null; 1489 end loop; 1490 1491 /*循环更新计划外 未 完成验证的*/ 1492 for v_cur2_ in cur_jhwwwc loop 1493 update fw_xmmx_tongji k set jhw_wwc= v_cur2_.num2 where k.bae010 = v_cur2_.bae010; 1494 null; 1495 end loop; 1496 1497 /*计划内完成工时 3 */ 1498 for v_cur3 in cur_jhnywc_gs loop 1499 /*update fw_xmmx_tongji k set jhn_ywcgs= v_cur3.jhgs,jhn_ where k.bae010 = v_cur2.bae010;*/ 1500 update fw_xmmx_tongji k set jhn_ywcgs=v_cur3.jhgs,jhn_ywcgs_sj = v_cur3.sjgs where k.bae010 = v_cur3.bae010; 1501 null; 1502 end loop; 1503 1504 /*计划外完成工时 3 */ 1505 for v_cur3_ in cur_jhwywc_gs loop 1506 /*update fw_xmmx_tongji k set jhn_ywcgs= v_cur3.jhgs,jhn_ where k.bae010 = v_cur2.bae010;*/ 1507 update fw_xmmx_tongji k set jhw_ywcgs=v_cur3_.jhgs,jhw_ywcgs_sj = v_cur3_.sjgs where k.bae010 = v_cur3_.bae010; 1508 null; 1509 end loop; 1510 1511 1512 /*计划内未完成工时 4*/ 1513 for v_cur4 in cur_jhnwwc_gs loop 1514 update fw_xmmx_tongji k set jhn_ywcgs=v_cur4.jhgs,jhn_ywcgs_sj = v_cur4.sjgs where k.bae010 = v_cur4.bae010; 1515 null; 1516 end loop; 1517 1518 /*计划外未完成工时 4*/ 1519 for v_cur4_ in cur_jhwwwc_gs loop 1520 update fw_xmmx_tongji k set jhw_ywcgs=v_cur4_.jhgs,jhw_ywcgs_sj = v_cur4_.sjgs where k.bae010 = v_cur4_.bae010; 1521 null; 1522 end loop; 1523 1524 null; 1525 end; 1526 /* 1527 修正sic86 的单位编号，以sic84.aab001为依据, 用于pkg_zhgl.Ylgrzh_Cxtj，以弥补重新统计过程的缺陷 1528 add by weiyongle 20160506 1529 */ 1530 procedure rebuild_sic86(pi_aac001 in varchar2, 1531 po_fhz out varchar2, 1532 po_msg out varchar2) is 1533 v_cae121 sic84.aae002%type; 1534 v_cnt_sic86 number(2); 1535 v_cnt_ac20 number(2); 1536 v_aab001 sic86.aab001%type; 1537 v_aae041 ac20.aae041%type; 1538 v_ksny sic86.aae001%type; 1539 v_zzny sic86.aae001%type; 1540 cursor c_aae002_sic84 is 1541 select max(cae121) cae121, aab001 1542 from sic84 1543 where aac001 = pi_aac001 1544 group by substr(aae002, 1, 4), aab001 1545 order by cae121; 1546 begin 1547 --初始化参数 1548 po_fhz := '-1'; 1549 po_msg := '成功'; 1550 for v_cur in c_aae002_sic84 loop 1551 v_cae121 := v_cur.cae121; 1552 select count(1) 1553 into v_cnt_sic86 1554 from sic86 1555 where aac001 = pi_aac001 1556 and aae001 = substr(v_cae121, 1, 4); 1557 begin 1558 if v_cnt_sic86 != 1 then 1559 po_fhz := '-1'; 1560 po_msg := '该人员' || pi_aac001 || '在 ' || substr(v_cae121, 1, 4) || 1561 '年度有多条个人养老账户,请核查!'; 1562 return; 1563 end if; 1564 /*update sic86 1565 set aab001 = v_cur.aab001 1566 where aac001 = pi_aac001 1567 and aae001 = substr(v_aae002, 1, 4);*/ 1568 update sic86 1569 set aab001 = 1570 (select aab001 1571 from sic84 1572 where aac001 = pi_aac001 1573 /* 必须按照cae121来 判断，而不能用 aae002来判断 ，否则有些情况会出问题 */ 1574 and cae121 = v_cur.cae121) 1575 where aac001 = pi_aac001 1576 and aae001 = substr(v_cae121, 1, 4); 1577 exception 1578 when others then 1579 po_fhz := '-2'; 1580 po_msg := '更新人员' || pi_aac001 || ',' || substr(v_cae121, 1, 4) || 1581 '年度的养老账户时发生系统错误!'; 1582 return; 1583 end; 1584 end loop; 1585 /*select min(substr(aae041,1,4)) from ac20 where aac001 = 1000194637;-- ksny 1586 select min(substr(cae121,1,4))-1 from sic84 where aac001 = 1000194637; -- zzny */ 1587 -- add by weiyongle 20160527 1588 begin 1589 select min(substr(aae041, 1, 4)) 1590 into v_ksny 1591 from ac20 1592 where aac001 = pi_aac001; 1593 select min(substr(cae121, 1, 4)) 1594 into v_zzny 1595 from sic84 1596 where aac001 = pi_aac001; 1597 /* 1598 如果ac20的最小年份小于sic84的最小年份， 1599 那么从ac20的最小年份到sic84最小年份的上一年开始循环 1600 */ 1601 if v_ksny < v_zzny then 1602 v_zzny := v_zzny - 1; 1603 for i in v_ksny .. v_zzny loop 1604 begin 1605 select max(aae041) 1606 into v_aae041 1607 from ac20 1608 where aac001 = pi_aac001 1609 and substr(aae041, 1, 4) = i; 1610 select count(1) 1611 into v_cnt_ac20 1612 from ac20 1613 where aac001 = pi_aac001 1614 and aae041 = v_aae041; 1615 /*如果最小年份有多条开始月份相同的的ac20记录，那么报错*/ 1616 if v_cnt_ac20 > 1 then 1617 po_fhz := '-2'; 1618 po_msg := '改人员的养老历史账户在' || i || '年度有多条以' || v_aae041 || 1619 '作为开始年月的记录,不符合常理，请先到历史维护模块进行数据修正'; 1620 return; 1621 else 1622 select aab001 1623 into v_aab001 1624 from ac20 b 1625 where aac001 = pi_aac001 1626 and b.aae041 = v_aae041; 1627 end if; 1628 end; 1629 1630 /*update sic86 1631 set aab001 = 1632 (select aab001 1633 from ac20 b 1634 where aac001 = pi_aac001 and substr(b.aae041, 1, 4) = v_ksny) 1635 where aac001 = pi_aac001 1636 and aae001 = i;*/ 1637 update sic86 1638 set aab001 = v_aab001 1639 where aac001 = pi_aac001 1640 and aae001 = i; 1641 end loop; 1642 end if; 1643 end; 1644 1645 end rebuild_sic86; 1646 1647 procedure rebuild_ab07(pi_aab001 in varchar2, 1648 pi_ksny in varchar2, 1649 pi_zzny in varchar2, 1650 pi_aae140 in varchar2, 1651 po_fhz out varchar2, 1652 po_msg out varchar2) is 1653 1654 cursor c_ac43 is 1655 select * 1656 from ac43 1657 where aab001 = pi_aab001 1658 and aaz288 is null; 1659 begin 1660 1661 /*for i in ksny .. zzny loop 1662 null; 1663 pkg_yuejie.dwzjjh_cxtj_aae140(pi_aab001, 1664 pi_aae002 => :pi_aae002, 1665 pi_aae140 => :pi_aae140, 1666 po_fhz => :po_fhz, 1667 po_msg => :po_msg); 1668 end loop;*/ 1669 --初始化参数 1670 po_fhz := '1'; 1671 po_msg := '成功'; 1672 /*for v_cur in c_ac43 loop 1673 pkg_yuejie.dwzjjh_cxtj_aae140(pi_aab001, 1674 v_cur.aae002, 1675 '110', 1676 po_fhz, 1677 po_msg); 1678 end loop;*/ 1679 1680 for i in pi_ksny..pi_zzny loop 1681 pkg_yuejie.dwzjjh_cxtj_aae140(pi_aab001, 1682 i, 1683 pi_aae140, 1684 po_fhz, 1685 po_msg); 1686 end loop; 1687 1688 exception 1689 when others then 1690 po_fhz := '-1'; 1691 po_msg := '失败'; 1692 return; 1693 end rebuild_ab07; 1694 1695 /*Pkg_Ryhb_Pl_New 1696 --批量合并，初始数据生成 1697 Procedure Plhb_Start(Pi_Bae001 In Varchar2, 1698 Pi_Aab001 In Number, 1699 Pi_Jbr In Varchar2, --- 10000641 1700 Po_Fhz Out Varchar2, 1701 Po_Msg Out Varchar2) 1702 */ 1703 procedure generate_plhb_data(pi_bae001 in varchar2, 1704 po_fhz out varchar2, 1705 po_msg out varchar2) is 1706 cursor cur_dwxx is 1707 select * from ae01 where bae001 = pi_bae001; 1708 begin 1709 -- 初始化 1710 po_msg := '成功'; 1711 po_fhz := '1'; 1712 for v_cur in cur_dwxx loop 1713 Pkg_Ryhb_Pl_New.Plhb_Start(pi_bae001, 1714 v_cur.aab001, 1715 '10000641', 1716 po_fhz, 1717 po_msg); 1718 end loop; 1719 exception 1720 when others then 1721 po_msg := '失败'; 1722 po_fhz := '-1'; 1723 end; 1724 /*查询知识库数据*/ 1725 procedure queryZSK(PI_AAE906 in varchar2, 1726 PO_AAE202 out varchar2, 1727 PO_AAE008 out varchar2, 1728 po_fhz out varchar2, 1729 po_msg out varchar2) is 1730 v_cnt number(2); 1731 cursor c_zsk is 1732 select * from fw_zsk where aae906 = pi_aae906; 1733 begin 1734 -- 初始化参数 1735 po_fhz := '1'; 1736 po_msg := '获取数据成功！'; 1737 select count(1) into v_cnt from fw_zsk where aae906 = pi_aae906; 1738 if v_cnt = 1 then 1739 for v_zsk in c_zsk loop 1740 po_aae202 := v_zsk.aae202; 1741 po_aae008 := v_zsk.aae008; 1742 end loop; 1743 else 1744 po_fhz := '-1'; 1745 po_msg := '_9,返回值过多'; 1746 end if; 1747 end queryZSK; 1748 /* 1749 生成失地农民汇总数据 1750 by weiyongel 20160519 1751 */ 1752 procedure generate_sdnmhzsj(PI_BAE001 in varchar2, 1753 po_fhz out varchar2, 1754 po_msg out varchar2) is 1755 v_aab383 ic51.aab383%type; 1756 v_aic452 ic51.aic452%type; 1757 v_aic453 ic51.aic453%type; 1758 v_rs ic5152_hzb.rs%type; 1759 v_cnt number(2); 1760 cursor c_Ic5152_Temp is 1761 select * 1762 from Ic5152_Temp 1763 where bae001 = pi_bae001 1764 and imp_flag = '1' 1765 and zfbtfs <> null; 1766 begin 1767 -- 初始化参数 1768 po_fhz := '1'; 1769 po_msg := '数据汇总成功！'; 1770 /*for v_c_Ic5152_Temp in c_Ic5152_Temp loop 1771 1772 null; 1773 end loop;*/ 1774 begin 1775 select count(1) into v_cnt from ic5152_hzb where bae001 = pi_bae001; 1776 /*如果大于0,那么就先清除这个汇总表*/ 1777 if v_cnt > 0 then 1778 delete from ic5152_hzb where bae001 = pi_bae001; 1779 end if; 1780 end; 1781 select sum(nvl(aab383, 0)) 1782 into v_aab383 1783 from Ic5152_Temp 1784 where bae001 = pi_bae001 1785 and imp_flag = '1' 1786 and zfbtfs is not null; 1787 select count(distinct(aac002)) 1788 into v_rs 1789 from Ic5152_Temp 1790 where bae001 = pi_bae001 1791 and imp_flag = '1' 1792 and zfbtfs is not null; 1793 select sum(nvl(aic452, 0)) 1794 into v_aic452 1795 from Ic5152_Temp 1796 where bae001 = pi_bae001 1797 and imp_flag = '1' 1798 and zfbtfs is not null; 1799 select sum(nvl(aic453, 0)) 1800 into v_aic453 1801 from Ic5152_Temp 1802 where bae001 = pi_bae001 1803 and imp_flag = '1' 1804 and zfbtfs is not null; 1805 insert into ic5152_hzb 1806 (bae001,rs, aab383, aic452, aic453) 1807 values 1808 (PI_BAE001,v_rs, v_aab383, v_aic452, v_aic453); 1809 -- 调用生成清理数据的过程 1810 generate_sdnmqlsj(PI_BAE001,po_fhz,po_msg); 1811 exception 1812 when others then 1813 po_fhz := '-1'; 1814 po_msg := 'pkg_weiyl.generate_sdnmhzsj 数据汇总失败！'; 1815 return; 1816 end generate_sdnmhzsj; 1817 1818 /* 1819 生成失地农民清理数据 1820 by weiyongel 20160519 1821 */ 1822 procedure generate_sdnmqlsj(PI_BAE001 in varchar2, 1823 po_fhz out varchar2, 1824 po_msg out varchar2) is 1825 v_aab383 ic51.aab383%type; 1826 v_aic452 ic51.aic452%type; 1827 v_aic453 ic51.aic453%type; 1828 v_rs ic5152_qlb .rs%type; 1829 v_cnt number(2); 1830 cursor c_Ic5152_Temp is 1831 select sum(nvl(a.aab383, 0)) aab383, 1832 sum(nvl(a.aic453, 0)) aic453, 1833 sum(nvl(a.aic452, 0)) aic452, 1834 count(distinct(a.aac002)) rs, 1835 a.aae001 aae001 1836 from Ic5152_Temp a 1837 where bae001 = PI_BAE001 1838 and imp_flag = '1' 1839 and zfbtfs is not null 1840 group by aae001; 1841 begin 1842 -- 初始化参数 1843 po_fhz := '1'; 1844 po_msg := '生成失地农民清理数据成功！'; 1845 begin 1846 select count(1) into v_cnt from ic5152_qlb where bae001 = pi_bae001; 1847 /*如果大于0,那么就先删除这个汇总表*/ 1848 if v_cnt > 0 then 1849 delete from ic5152_qlb where bae001 = pi_bae001; 1850 end if; 1851 end; 1852 for v_c_Ic5152_Temp in c_Ic5152_Temp loop 1853 /*插入清理数据表 ，用于报表数据提取 */ 1854 insert into ic5152_qlb 1855 (bae001, aae001, rs, aab383, aic452, aic453) 1856 values 1857 (PI_BAE001, 1858 v_c_Ic5152_Temp.aae001, 1859 v_c_Ic5152_Temp.rs, 1860 v_c_Ic5152_Temp.Aab383, 1861 v_c_Ic5152_Temp.Aic452, 1862 v_c_Ic5152_Temp.Aic453); 1863 null; 1864 end loop; 1865 1866 exception 1867 when others then 1868 po_fhz := '-1'; 1869 po_msg := 'pkg_weiyl.generate_sdnmhzsj 数据汇总失败！'; 1870 return; 1871 end generate_sdnmqlsj; 1872 1873 /*生成失地农民数据清理后的变化字段，用于查询失地农民数据清理模块*/ 1874 procedure generate_sdnmdatachange(PI_AAC001 in varchar2, 1875 PI_AAZ288 in varchar2, 1876 PO_FHZ out varchar2, 1877 PO_MSG out varchar2) is 1878 cursor c_ic51_bf is 1879 select * 1880 from ic51_bf 1881 where aac001 = pi_aac001 1882 and aaz288 = pi_aaz288; 1883 cursor c_ic51 is 1884 select * 1885 from ic51 1886 where aac001 = pi_aac001 1887 and aaz288 = pi_aaz288; 1888 v_cnt_bf number(2); 1889 v_aic443_tmp ic51_bf.aic443%type; 1890 begin 1891 /*--正常或暂停参保并且是老系统导入的可以修改个人缴费金额 1892 Update Ic51 1893 Set Aic443 = v_Ic5152_Temp.Aic443, 1894 Aic444 = v_Ic5152_Temp.Aic444, 1895 Aic020 = v_Ic5152_Temp.Aic020, 1896 Aab383 = v_Ic5152_Temp.Aab383, 1897 Aic446 = v_Ic5152_Temp.Aic446, 1898 Aic452 = v_Ic5152_Temp.Aic452, 1899 Aic448 = v_Ic5152_Temp.Aic448, 1900 Aic453 = v_Ic5152_Temp.Aic453, 1901 Aic021 = v_Ic5152_Temp.Aic021, 1902 Aaa041 = v_Ic5152_Temp.Aaa041, 1903 Aae002 = v_Ic5152_Temp.Aae002 1904 Where Aac001 = v_Aac001 1905 And Aaz288 = v_Ic5152_Temp.Aaz288 1906 And Bce094 = '1' 1907 And Aae016 = '1';*/ 1908 -- 初始化参数 1909 po_fhz := '1'; 1910 po_msg := '生成变更记录数据成功！'; 1911 -- 1. 先做判断，如果已经生成了相应的变更记录，那么就先删除相应的变更记录表，然后重新生成最新的数据 1912 begin 1913 select count(1) 1914 into v_cnt_bf 1915 from ic5152_bg 1916 where aac001 = pi_aac001 1917 and aaz288 = pi_aaz288; 1918 if v_cnt_bf > 0 then 1919 delete from ic5152_bg 1920 where aaz288 = pi_aaz288 1921 and aac001 = pi_aac001; 1922 end if; 1923 end; 1924 -- 2. 生成最新的变更记录表 1925 for v_bf in c_ic51_bf loop 1926 begin 1927 -- 1 先插入aaz288,aac001,aae001,aae002等信息 1928 insert into ic5152_bg 1929 (aaz288, bae001, aac001, aae002, aae001) 1930 values 1931 (PI_AAZ288, v_bf.bae001, v_bf.aac001, v_bf.aae002, v_bf.aae001); 1932 for v_ic51 in c_ic51 loop 1933 -- a. 先直接更新变更记录表 1934 update ic5152_bg a 1935 set a.aic020 = v_ic51.aic020, 1936 a.aic020_ = v_bf.aic020, 1937 a.aic443 = v_ic51.aic443, 1938 a.aic443_ = v_bf.aic443, 1939 a.aic444 = v_ic51.aic444, 1940 a.aic444_ = v_bf.aic444, 1941 a.aab383 = v_ic51.aab383, 1942 a.aab383_ = v_bf.aab383, 1943 a.aic446 = v_ic51.aic446, 1944 a.aic446_ = v_bf.aic446, 1945 a.aic452 = v_ic51.aic452, 1946 a.aic452_ = v_bf.aic452, 1947 a.aic448 = v_ic51.aic448, 1948 a.aic448_ = v_bf.aic448, 1949 a.aic453 = v_ic51.aic453, 1950 a.aic453_ = v_bf.aic453, 1951 a.aic021 = v_ic51.aic021, 1952 a.aic021_ = v_bf.aic021, 1953 a.aaa041 = v_ic51.aaa041, 1954 a.aaa041_ = v_bf.aaa041 1955 where aaz288 = pi_aaz288; 1956 1957 if v_ic51.aic443 != v_bf.aic443 then 1958 update ic5152_bg 1959 set aic443 = v_ic51.aic443, aic443_ = v_bf.aic443 1960 where aaz288 = pi_aaz288; 1961 end if; 1962 1963 if v_ic51.aic444 != v_bf.aic444 then 1964 update ic5152_bg 1965 set aic444 = v_ic51.aic444, aic444_ = v_bf.aic444 1966 where aaz288 = pi_aaz288; 1967 end if; 1968 1969 if v_ic51.aab383 != v_bf.aab383 then 1970 update ic5152_bg 1971 set aab383 = v_ic51.aab383, aab383_ = v_bf.aab383 1972 where aaz288 = pi_aaz288; 1973 end if; 1974 1975 /*if v_ic51.aic443 != v_bf.aic443 then 1976 update ic5152_bg aic443,aic443_ set v_ic51.aic433,v_bf.aic443; 1977 end if; 1978 1979 if v_ic51.aic443 != v_bf.aic443 then 1980 update ic5152_bg aic443,aic443_ set v_ic51.aic433,v_bf.aic443; 1981 end if; 1982 1983 if v_ic51.aic443 != v_bf.aic443 then 1984 update ic5152_bg aic443,aic443_ set v_ic51.aic433,v_bf.aic443; 1985 end if;*/ 1986 end loop; 1987 exception 1988 when others then 1989 po_fhz := '-11'; 1990 po_msg := '生成变更记录数据失败！'; 1991 end; 1992 end loop; 1993 exception 1994 when others then 1995 po_fhz := '-1'; 1996 po_msg := '生成变更记录数据失败！'; 1997 end generate_sdnmdatachange; 1998 /*检查ac35时间 ，增减员时用*/ 1999 procedure checkAC35Tim(PI_AAC002 in varchar2, 2000 pi_aab999 in varchar2, 2001 Po_AAE042 OUT varchar2, 2002 PO_FHZ out varchar2, 2003 PO_MSG out varchar2) is 2004 v_cnt number(3); 2005 v_cnt2 number(12); 2006 v_cnt3 number(12); 2007 v_aac001 ac01.aac001%type; 2008 v_aae042 ac35.aae042%type; 2009 n_aab001 ae01.aab001%type; 2010 begin 2011 --初始化 2012 -- 初始化参数 2013 po_fhz := '1'; 2014 po_msg := 'success'; 2015 select count(1) into v_cnt from ac01 where aac002 = pi_aac002; 2016 2017 2018 if v_cnt > 0 then 2019 2020 select aab001 into n_aab001 from ae01 where aab999 = pi_aab999; 2021 2022 select nvl(max(aae042),'111111') 2023 into Po_AAE042 2024 from ac35 2025 where aac001 in (select aac001 2026 from ac01 2027 where aac002 = pi_aac002 2028 ) 2029 and aab001 = n_aab001; 2030 2031 else 2032 Po_AAE042 := '111111'; 2033 end if; 2034 exception 2035 when others then 2036 po_fhz := '-1'; 2037 po_msg := '获取ac35.aae042出错，success'; 2038 end checkAC35Tim; 2039 2040 /* 重新统计ac43 aae002 ,20160526 */ 2041 procedure cxtj_ac43(PI_AAB001 in varchar2, 2042 PO_FHZ out varchar2, 2043 PO_MSG out varchar2) is 2044 v_cnt number(5); 2045 v_cae122 ac43.cae122%type; 2046 v_aae002 ac43.aae002%type; 2047 cursor c_ac43 is 2048 select * 2049 from ac43 2050 where aab001 = pi_aab001 2051 and aaz288 is null; 2052 begin 2053 --初始化 2054 -- 初始化参数 2055 po_fhz := '1'; 2056 po_msg := 'success'; 2057 begin 2058 select count(1) 2059 into v_cnt 2060 from ac43 2061 where aab001 = pi_aab001 2062 and aaz288 is null; 2063 if v_cnt > 0 then 2064 for v_cur_ac43 in c_ac43 loop 2065 /*if v_cur_ac43.aae002 != v_cur_ac43.cae122 then 2066 update ac43 2067 set aae002 = v_cur_ac43.cae122 2068 where aab001 = pi_aab001 2069 and aaz288 is null 2070 and cae122 = v_cur_ac43.cae122; 2071 end if;*/ 2072 update ac43 2073 set aae002 = v_cur_ac43.cae122 2074 where aab001 = pi_aab001 2075 and aaz288 is null 2076 and cae122 = v_cur_ac43.cae122; 2077 end loop; 2078 end if; 2079 end; 2080 end cxtj_ac43; 2081 2082 procedure getAAC027(PI_AAC002 in varchar2, 2083 PO_AAC027 OUT varchar2, 2084 PO_FHZ out varchar2, 2085 PO_MSG out varchar2) IS 2086 v_cnt number(1); 2087 v_aac027 ac01.aac027%type; 2088 begin 2089 --初始化 2090 -- 初始化参数 2091 po_fhz := '1'; 2092 po_msg := 'success'; 2093 select count(1) into v_cnt from ac01 where aac002 = pi_aac002; 2094 if v_cnt>0 then 2095 select aac027 into v_aac027 from ac01 where aac002 = pi_aac002; 2096 PO_AAC027 := v_aac027; 2097 end if; 2098 exception when others then 2099 po_fhz := '-1'; 2100 po_msg := 'error'; 2101 end getAAC027; 2102 2103 /* for test ,20160530 */ 2104 procedure myInsert(PI_AAC002 in varchar2, 2105 PO_FHZ out varchar2, 2106 PO_MSG out varchar2) is 2107 begin 2108 --初始化 2109 -- 初始化参数 2110 po_fhz := '1'; 2111 po_msg := 'success'; 2112 insert into xzl_one(aac002) values (pi_aac002); 2113 exception 2114 when others then 2115 po_fhz := '-1'; 2116 po_msg := 'error'; 2117 end myInsert; 2118 /*统计 社会保险参保情况查询 */ 2119 procedure tongji_shbx(PI_AAC001 in varchar2, 2120 PO_FHZ out varchar2, 2121 PO_MSG out varchar2) is 2122 v_cnt number(2); 2123 v_cic818 T_BXGX_SHBXCBQKCXJG_WYL.Cic818%type; 2124 v_cic819 T_BXGX_SHBXCBQKCXJG_WYL.Cic819%type; 2125 v_aae042_ac20 ac20.aae042%type; 2126 v_aae041_ac20 ac20.aae041%type; 2127 v_aae041_sac14 sac14.aae041%type; 2128 v_aae042_sac14 sac14.aae042%type; 2129 v_yf number(2); 2130 v_Prc Varchar2(20); 2131 v_Prcname Varchar2(200); 2132 v_Params Varchar2(500); 2133 cursor cur_t is 2134 select count(1) cnt, aae001 2135 from T_BXGX_SHBXCBQKCXJG_WYL 2136 where aac001 = pi_aac001 2137 group by aae001 2138 order by aae001; 2139 begin 2140 -- 初始化返回值 2141 Po_Fhz := '1'; 2142 Po_Msg := ''; 2143 v_Prc := '.tongji_shbx'; 2144 v_Prcname := c_Pkg_Name || v_Prc; 2145 v_Params := ',传入参数为:pi_aac001=' || Pi_Aac001; 2146 -- 自建表 T_BXGX_SHBXCBQKCXJG_WYL 1000745288 2147 /* 2148 从这里取值，可以直接复制到 表 T_BXGX_SHBXCBQKCXJG_WYL 中 2149 select bae001, 2150 aae001, 2151 aac001, 2152 cac002, 2153 aac003, 2154 aac004, 2155 aac002, 2156 csrq, 2157 cgrq, 2158 aae200, 2159 aab999, 2160 aab004, 2161 cic818, 2162 sum(cic819) cic819, 2163 aic058, 2164 aic074, 2165 aic072, 2166 aic075, 2167 aic076, 2168 aic077, 2169 aic078, 2170 aic079, 2171 aae087, 2172 jfsm, 2173 cbzt, 2174 ccjfrq, 2175 zmjfrq, 2176 dqjfgz 2177 from v_bxgx_shbxcbqkcxjg_xgy_ 2178 where aac001 = 1000745288 2179 group by bae001, 2180 aae001, 2181 aac001, 2182 cac002, 2183 aac003, 2184 aac004, 2185 aac002, 2186 csrq, 2187 cgrq, 2188 aae200, 2189 aab999, 2190 aab004, 2191 cic818, 2192 aic058, 2193 aic074, 2194 aic072, 2195 aic075, 2196 aic076, 2197 aic077, 2198 aic078, 2199 aic079, 2200 aae087, 2201 jfsm, 2202 cbzt, 2203 ccjfrq, 2204 zmjfrq, 2205 dqjfgz order by aae001;*/ 2206 -- 清空临时表 2207 delete from T_BXGX_SHBXCBQKCXJG_WYL where aac001 = pi_aac001; 2208 delete from T_BXGX_SHBXCBQKCXJG_WYL_ where aac001 = pi_aac001; 2209 /*插入数据*/ 2210 2211 insert into T_BXGX_SHBXCBQKCXJG_WYL 2212 select bae001, 2213 aac001, 2214 cac002, 2215 aac003, 2216 aac004, 2217 aac002, 2218 csrq, 2219 cgrq, 2220 aae200, 2221 aab999, 2222 aab004, 2223 aae001, 2224 cic818, 2225 sum(nvl(cic819, 0)) cic819, 2226 aic058, 2227 aic074, 2228 aic072, 2229 aic075, 2230 aic076, 2231 aic077, 2232 aic078, 2233 aic079, 2234 aae087, 2235 jfsm, 2236 cbzt, 2237 ccjfrq, 2238 zmjfrq, 2239 dqjfgz 2240 from v_bxgx_shbxcbqkcxjg_xgy_ 2241 where aac001 = pi_aac001 2242 group by bae001, 2243 aae001, 2244 aac001, 2245 cac002, 2246 aac003, 2247 aac004, 2248 aac002, 2249 csrq, 2250 cgrq, 2251 aae200, 2252 aab999, 2253 aab004, 2254 cic818, 2255 aic058, 2256 aic074, 2257 aic072, 2258 aic075, 2259 aic076, 2260 aic077, 2261 aic078, 2262 aic079, 2263 aae087, 2264 jfsm, 2265 cbzt, 2266 ccjfrq, 2267 zmjfrq, 2268 dqjfgz 2269 order by aae001; 2270 insert into T_BXGX_SHBXCBQKCXJG_WYL_ 2271 select * from T_BXGX_SHBXCBQKCXJG_WYL where aac001 = pi_aac001; 2272 -- 判断是否有多条，有多条就算是有转入的 2273 -- select count(1),aae001 into v_cnt from T_BXGX_SHBXCBQKCXJG_WYL where aac001 = pi_aac001 group by aae001 order by aae001; 2274 for v_cur in cur_t loop 2275 2276 if v_cur.cnt > 1 then 2277 select sum(nvl(cic818, 0)) cic818_hj 2278 into v_cic818 2279 from T_BXGX_SHBXCBQKCXJG_WYL 2280 where aae001 = v_cur.aae001; 2281 2282 select sum(nvl(cic819, 0)) cic819_hj 2283 into v_cic819 2284 from T_BXGX_SHBXCBQKCXJG_WYL 2285 where aae001 = v_cur.aae001; 2286 2287 update T_BXGX_SHBXCBQKCXJG_WYL_ a 2288 set a.cic818 = v_cic818 2289 where aac001 = pi_aac001 2290 and aae001 = v_cur.aae001; 2291 -- 因为有两条，一条为转入，一条为本地，所以要 删除一条，然后更新 2292 delete from T_BXGX_SHBXCBQKCXJG_WYL_ 2293 where aac001 = pi_aac001 2294 and aae001 = v_cur.aae001 2295 and aae087 = '1'; 2296 update T_BXGX_SHBXCBQKCXJG_WYL_ a 2297 set a.cic819 = v_cic819 2298 where aac001 = pi_aac001 2299 and aae001 = v_cur.aae001; 2300 end if; 2301 -- 修正 转入的月份 2302 select aae042 2303 into v_aae042_ac20 2304 from ac20 2305 where aac001 = pi_aac001 2306 and aae140 = '110' 2307 and substr(aae041, 1, 4) = v_cur.aae001; 2308 select aae041 2309 into v_aae041_ac20 2310 from ac20 2311 where aac001 = pi_aac001 2312 and aae140 = '110' 2313 and substr(aae041, 1, 4) = v_cur.aae001; 2314 2315 select aae042 2316 into v_aae042_sac14 2317 from sac14 2318 where aac001 = pi_aac001 2319 and aae140 = '110' 2320 and substr(aae041, 1, 4) = v_cur.aae001; 2321 select aae041 2322 into v_aae041_sac14 2323 from sac14 2324 where aac001 = pi_aac001 2325 and aae140 = '110' 2326 and substr(aae041, 1, 4) = v_cur.aae001; 2327 2328 if v_aae042_ac20 <= v_aae041_sac14 or v_aae042_sac14 <= v_aae041_ac20 then 2329 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 + v_aae042_sac14 - 2330 v_aae041_sac14 + 1; 2331 -- 有重合的情况 2332 Elsif v_aae042_ac20 > v_aae041_sac14 and v_aae042_ac20 < v_aae042_sac14 and 2333 v_aae041_ac20 <= v_aae041_sac14 Then 2334 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 + v_aae042_sac14 - 2335 v_aae041_sac14 + 1 - (v_aae042_ac20 - v_aae041_sac14 + 1); 2336 Elsif v_aae042_ac20 > v_aae041_sac14 and v_aae042_ac20 < v_aae042_sac14 and 2337 v_aae041_ac20 >= v_aae041_sac14 Then 2338 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 + v_aae042_sac14 - 2339 v_aae041_sac14 + 1 - (v_aae042_ac20 - v_aae041_ac20 + 1); 2340 Elsif v_aae042_ac20 > v_aae041_sac14 and v_aae042_ac20 < v_aae042_sac14 and 2341 v_aae041_ac20 >= v_aae041_sac14 Then 2342 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 + v_aae042_sac14 - 2343 v_aae041_sac14 + 1 - (v_aae042_ac20 - v_aae041_ac20 + 1); 2344 Elsif v_aae042_ac20 > v_aae041_sac14 and 2345 v_aae042_ac20 >= v_aae042_sac14 and 2346 v_aae041_ac20 <= v_aae041_sac14 Then 2347 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1; 2348 2349 Elsif v_aae042_ac20 >= v_aae042_sac14 and 2350 v_aae041_ac20 > v_aae041_sac14 Then 2351 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 + 2352 (v_aae042_sac14 - v_aae041_sac14 + 1) - 2353 (v_aae041_ac20 - v_aae041_sac14 + 1); 2354 end if; 2355 update T_BXGX_SHBXCBQKCXJG_WYL_ a 2356 set a.cic818 = v_yf 2357 where aac001 = pi_aac001 2358 and aae001 = v_cur.aae001; 2359 end loop; 2360 2361 exception 2362 when others then 2363 Po_Fhz := v_Prcname || 2364 '_91,重新统计月份出错'; 2365 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params); 2366 2367 end tongji_shbx; 2368 --- 统计部分 2369 /* 2370 1 统计宜宾市2015年的住院情况,开始日期，终止日期，病种，就诊医院等 2371 select temp_func_WYL('BAE001', bae001) BAE001, 2372 a.aac003, 2373 a.aac002, 2374 b.ckc546, 2375 substr(b.ckc537, 1, 8) ks, 2376 substr(b.ckc538, 1, 8) zz, 2377 b.ckb519 2378 from ac01 a, kc21 b 2379 where a.aac001 = b.aac001 2380 and b.ckc544 = '2' 2381 and substr(b.ckc538, 1, 4) = 2015 2382 -- AND A.AAB001 = 511500012810 2383 group by a.bae001, AAC003, AAC002, CKC546, b.ckc537, b.ckc538, CKB519 2384 order by a.bae001;*/ 2385 2386 2387 Begin 2388 Null; 2389 End Pkg_Weiyl; 2390 /