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 /*
58 added 20160801
59 获取月份差值，主要针对于转移统计的
60 */
61 function getMonthNum(pi_ksyf in number,
62 pi_zzyf in number,
63 pi_aae180 in number) return number;
64
65 Procedure Xjyyzf(Pi_Bae007 In Varchar2,
66 Pi_Operid In Varchar2,
67 Pi_Aae037 In Varchar2,
68 Pi_Aae038 In Varchar2,
69 Po_Fhz Out Varchar2,
70 Po_Msg Out Varchar2);
71 --- 循环调用的过程，解决每次都要自己写一个declare plsql代码块，
72 --入参：
73 Procedure Prc_Xunhuan(Pi_Prcname In Varchar2, --要调用的过程名
74 Pi_Tabname In Varchar2, --cursor取值的表
75 Po_Fhz Out Varchar2,
76 Po_Msg Out Varchar2);
77 Procedure Xjyyzf_Callback(Pi_Bae007 In Varchar2,
78 Pi_Operid In Varchar2,
79 Po_Fhz Out Varchar2,
80 Po_Msg Out Varchar2);
81 Procedure Xjyyzfqx(Pi_Bae007 In Varchar2,
82 Po_Fhz Out Varchar2,
83 Po_Msg Out Varchar2);
84 -- 添加自治事务
85 Procedure Autonomous_Tran(Pi_Aac001 In Varchar2,
86 Po_Fhz Out Varchar2,
87 Po_Msg Out Varchar2);
88 /*手工添加表级锁，使用场景，如果要操作一张大表的大部分数据，
89 如果不加表级锁，那么就会耗费大量的资源，这种情况下可以使用
90 手工给表加锁，释放方式 rollback，或者commit
91 */
92 Procedure Update_Ac02_Aae140(Pi_Aae140 In Varchar2,
93 Pi_Fhz Out Varchar2,
94 Po_Msg Out Varchar2);
95
96 Procedure Querycheck(Pi_Aac002 In Varchar2,
97 Pi_Aac003 In Varchar2,
98 Po_Fhz Out Varchar2,
99 Po_Msg Out Varchar2);
100
101 Procedure Check_Ac02(Pi_Aac002 In Varchar2,
102 Po_Aac001 Out Number,
103 Po_Cac012 Out Varchar2,
104 Po_Aab001 Out Varchar2,
105 Po_Fhz Out Varchar2,
106 Po_Msg Out Varchar2);
107 Procedure Check_Skc84(Pi_Aac001 In Varchar2,
108 Po_Fhz Out Varchar2,
109 Po_Msg Out Varchar2);
110 Procedure Check_Sdnmdcc(Pi_Aab999 In Varchar2,
111 PI_PCH in varchar2,
112 Po_Fhz Out Varchar2,
113 Po_Msg Out Varchar2);
114 Procedure Check_Sdnmdc(Pi_Bae001 In Varchar2,
115 Pi_Aab999 In Varchar2,
116 Pi_Pch In Varchar2,
117 /* PI_GLT in varchar2,*/
118 Pi_Aae001 In Varchar2,
119 Pi_Aae036 In Varchar2,
120 Pi_Aac027 In Varchar2,
121 Pi_Bzw In Varchar2,
122 Pi_Oper In Varchar2,
123 Po_Fhz Out Varchar2,
124 Po_Msg Out Varchar2);
125 Procedure Check_Sdnmdc_Multi(Pi_Bae001 In Varchar2,
126 Pi_Aab999 In Varchar2,
127 Pi_Pch In Varchar2,
128 /* PI_GLT in varchar2,*/
129 Pi_Aae001 In Varchar2,
130 Pi_Aae036 In Varchar2,
131 Pi_Aac027 In Varchar2,
132 Pi_Bzw In Varchar2,
133 Pi_Oper In Varchar2,
134 Po_Fhz Out Varchar2,
135 Po_Msg Out Varchar2);
136 Procedure Updatekbb5(Pi_Bae007 In Varchar2,
137 Pi_Ckz545 In Varchar2,
138 Pi_Ckb626 In Varchar2,
139 Pi_Ckb627 In Varchar2,
140 Pi_Ckb629 In Varchar2,
141 Pi_Ckb630 In Varchar2,
142 Po_Fhz Out Varchar2,
143 Po_Msg Out Varchar2);
144 Procedure Getaaz601(Pi_Rc In Varchar2,
145 po_aaz601 out number,
146 Po_Fhz Out Varchar2,
147 Po_Msg Out Varchar2);
148 Procedure Insertfw_Zsk(PI_AAA200 in varchar2,
149 Pi_Aae202 In Varchar2,
150 Pi_Aaa203 In Varchar2,
151 Pi_Aae008 In Varchar2,
152 PI_AAE011 IN VARCHAR2,
153 PI_AAE906 IN VARCHAR2,
154 PI_BZ IN VARCHAR2,
155 Po_Fhz Out Varchar2,
156 Po_Msg Out Varchar2);
157 /*拼接两个字符串,练手嵌套存储过程*/
158 procedure testNestedPro(pi_xing in varchar2,
159 pi_ming in varchar2,
160 pi_aab001 in number,
161 po_fhz out varchar2,
162 po_msg out varchar2);
163 /*触摸屏查询标记*/
164 procedure cancelCmp(PI_SERIALNUM in varchar2,
165 po_fhz out varchar2,
166 po_msg out varchar2);
167 /*取消征集通知单*/
168 procedure cancelAaz288(PI_OPERID in varchar2,
169 PI_AAZ288 in varchar2,
170 po_fhz out varchar2,
171 po_msg out varchar2);
172 /*删除知识库核销的附件内容，否则数据里的不必要的附件会越来越多*/
173 procedure deleteZskFile(PI_CAE232 in varchar2,
174 po_fhz out varchar2,
175 po_msg out varchar2);
176 procedure generatexmmx(pi_ksrq in varchar2,
177 pi_zzrq in varchar2,
178 po_fhz out varchar2,
179 po_msg out varchar2);
180 procedure rebuild_sic86(pi_aac001 in varchar2,
181 po_fhz out varchar2,
182 po_msg out varchar2);
183 procedure rebuild_ab07(pi_aab001 in varchar2,
184 pi_ksny in varchar2,
185 pi_zzny in varchar2,
186 pi_aae140 in varchar2,
187 po_fhz out varchar2,
188 po_msg out varchar2);
189
190 /*Pkg_Ryhb_Pl_New
191 --批量合并，初始数据生成
192 Procedure Plhb_Start(Pi_Bae001 In Varchar2,
193 Pi_Aab001 In Number,
194 Pi_Jbr In Varchar2,
195 Po_Fhz Out Varchar2,
196 Po_Msg Out Varchar2)
197 */
198 procedure generate_plhb_data(pi_bae001 in varchar2,
199 po_fhz out varchar2,
200 po_msg out varchar2);
201 procedure queryZSK(PI_AAE906 in varchar2,
202 PO_AAE202 out varchar2,
203 PO_AAE008 out varchar2,
204 po_fhz out varchar2,
205 po_msg out varchar2);
206 /*
207 生成失地农民汇总数据
208 by weiyongel 20160519
209 */
210 procedure generate_sdnmhzsj(PI_BAE001 in varchar2,
211 po_fhz out varchar2,
212 po_msg out varchar2);
213 /*
214 生成失地农民清理数据
215 by weiyongel 20160519
216 */
217 procedure generate_sdnmqlsj(PI_BAE001 in varchar2,
218 po_fhz out varchar2,
219 po_msg out varchar2);
220 /*生成失地农民数据清理后的变化字段，用于查询失地农民数据清理模块*/
221 procedure generate_sdnmdatachange(PI_AAC001 in varchar2,
222 PI_AAZ288 in varchar2,
223 PO_FHZ out varchar2,
224 PO_MSG out varchar2);
225 /*检查ac35时间 ，增减员时用*/
226 procedure checkAC35Tim(PI_AAC002 in varchar2,
227 pi_aab999 in varchar2,
228 Po_AAE042 OUT varchar2,
229 PO_FHZ out varchar2,
230 PO_MSG out varchar2);
231 /* 重新统计ac43 aae002 ,20160526 */
232 procedure cxtj_ac43(PI_AAB001 in varchar2,
233 PO_FHZ out varchar2,
234 PO_MSG out varchar2);
235 procedure getAAC027(PI_AAC002 in varchar2,
236 PO_AAC027 OUT varchar2,
237 PO_FHZ out varchar2,
238 PO_MSG out varchar2);
239 /* for test ,20160530 */
240 procedure myInsert(PI_AAC002 in varchar2,
241 PO_FHZ out varchar2,
242 PO_MSG out varchar2);
243 /*统计 社会保险参保情况查询 */
244 procedure tongji_shbx_old(PI_AAC001 in varchar2,
245 PO_FHZ out varchar2,
246 PO_MSG out varchar2);
247
248 /*统计 社会保险参保情况查询 2016081 */
249 procedure tongji_shbx(PI_AAC001 in varchar2,
250 PO_FHZ out varchar2,
251 PO_MSG out varchar2);
252
253 /*批量赋权限,我自己的权限放在表 fw_operator2right_wyl_ 中 */
254 procedure prc_right(pi_loginid in varchar2,
255 pi_loginid_other in varchar2,
256 po_fhz out varchar2,
257 po_msg out varchar2);
258
259 /* 测试goto 的用法 */
260 procedure test_loop_go(pi_aab001 in number,
261 po_fhz out varchar2,
262 po_msg out varchar2);
263 /*
264 集合变量
265 */
266 procedure record_practice(pi_aac001 in number,
267 po_fhz out varchar2,
268 po_msg out varchar2);
269
270 End Pkg_Weiyl;
271 /
272 CREATE OR REPLACE Package Body Pkg_Weiyl Is
273 c_Pkg_Name Constant Varchar2(20) := 'PKG_WEIYL';
274 --修改险种
275 Procedure Updateaae140(Pi_Aac002 In Varchar2,
276 Pi_Aae140 In Varchar2,
277 Po_Fhz Out Varchar2,
278 Po_Msg Out Varchar2) Is
279 v_Count Number(2);
280 Begin
281 Po_Fhz := '1';
282 Po_Msg := '成功';
283 Select Count(*) Into v_Count From Sab11 Where Bcc347 = Pi_Aac002;
284 If v_Count > 0 Then
285 Update Ac02
286 Set Aac008 = '2'
287 Where Aae140 = '342'
288 And Aac001 = (Select Aac001
289 From Ac01
290 Where Aac002 = Pi_Aac002
291 And Aae140 = Pi_Aae140);
292 End If;
293 Exception
294 When No_Data_Found Then
295 Po_Fhz := '0';
296 Po_Msg := '失败';
297 End Updateaae140;
298
299 --修改身份证号
300 Procedure Updateidcard(Pi_Bae007 In Varchar2,
301 Pi_Flag In Varchar2,
302 Pi_Aac002 In Varchar2,
303 Pi_Aac002_New In Varchar2,
304 Pi_Aae013 In Varchar2,
305 Pi_Aae011 In Varchar2,
306 Pi_Bae001 In Varchar2,
307 Po_Fhz Out Varchar2,
308 Po_Msg Out Varchar2) Is
309 v_Aac001 Ac02.Aac001%Type;
310 v_Prc Varchar2(20);
311 v_Aab001 Ac02.Aab001%Type;
312 v_Yl_Count Number(2); --养老待遇记录数
313 v_Msg Varchar2(200);
314 v_Aaa076 Ac60.Aaa076%Type;
315 v_Prcname Varchar2(200);
316 v_Params Varchar2(500);
317 v_Sqlerrm Varchar2(500);
318 v_Aac003 Ac01.Aac003%Type;
319 v_Aac002_Tmp Ac01.Aac002%Type;
320 Begin
321 -- 初始化返回值
322 Po_Fhz := '1';
323 Po_Msg := '';
324 v_Prc := '.updateIDCard';
325 v_Prcname := c_Pkg_Name || v_Prc;
326 v_Params := ',传入参数为:pi_aac002=' || Pi_Aac002 || ',pi_aac002_new=' ||
327 Pi_Aac002_New || ',pi_aae013=' || Pi_Aae013 ||
328 ',pi_aae011=' || Pi_Aae011 || ',pi_bae001=' || Pi_Bae001;
329 Select Aac001, Aab001
330 Into v_Aac001, v_Aab001
331 From Ac01
332 Where Aac002 = Pi_Aac002;
333 -- 调用校验过程进行判断
334 Validateidcard(v_Aac001, Pi_Aac002_New, Po_Fhz, Po_Msg);
335 If Po_Fhz <> '1' Then
336 Return;
337 End If;
338
339 -- 先做 是否有养老待遇的判断,如果有就直接返回，不更新ac01.aac002
340 Select Count(1) Into v_Yl_Count From Ac60 Where Aac001 = v_Aac001;
341 If v_Yl_Count > 0 Then
342 -- 如果有养老待遇，那么抛出更详细的结果，以便于前台更容易理解
343 -- 只取第一条
344 Select Aaa076
345 Into v_Aaa076
346 From Ac60
347 Where Aac001 = v_Aac001
348 And Rownum = 1;
349 Select Func_Wyl('AAA076', v_Aaa076) Into v_Msg From Dual;
350 Po_Fhz := '-2';
351 Po_Msg := v_Prcname || '执行失败，该人员存在养老待遇类型为 "' || v_Msg ||
352 '" 的养老待遇，且待遇状态正常，因此不能更新身份证';
353 Return;
354 End If;
355 -- 更新ac01.AAC002
356 Begin
357 -- 根据传入的标志来判断是该笔业务是改成正确身份证还是改成错误身份证，
358 If (Pi_Flag = '0') Then
359 /*v_aac002_tmp := BXGX_SEQ_aac002_tmp.Nextval||substr(pi_aac002,7,length(pi_aac002)-6);*/
360 v_Aac002_Tmp := Pi_Aac002_New;
361 Else
362 v_Aac002_Tmp := Pi_Aac002_New;
363 End If;
364 Update Ac01 Set Aac002 = v_Aac002_Tmp Where Aac001 = v_Aac001;
365 Exception
366 When Others Then
367 v_Sqlerrm := Substr(Sqlerrm, 1, 9);
368 /* 捕获,唯一性约束冲突*/
369 If v_Sqlerrm = 'ORA-00001' Then
370 Select Aac003
371 Into v_Aac003
372 From Ac01
373 Where Aac002 = Pi_Aac002_New;
374 Po_Fhz := v_Prcname || '_-3';
375 Po_Msg := '系统里已经存在身份证为' || Pi_Aac002_New || '的参保人了,姓名:' ||
376 v_Aac003 || ',因此不能修改';
377 Elsif v_Sqlerrm <> 'ORA-00001' Then
378 /*SQLERRM=ORA-00001*/
379 /*没法成功捕捉到sqlerrm*/
380 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
381 Po_Fhz := v_Prcname || '_94';
382 End If;
383
384 --po_msg := pkg_fun.F_ERRMSG(v_prcName,sqlcode,sqlerrm,v_params);
385 --po_fhz := v_prcName||'_91';
386 Return;
387 End;
388 Insert Into Ac25
389 (Aaz163,
390 Bae001,
391 Aab001,
392 Aac001,
393 Cae129,
394 Aac050,
395 Aae160,
396 Cac038,
397 Bae007,
398 Aae011,
399 Aae036,
400 Bce326,
401 Aae012,
402 Bhe949,
403 Cae030,
404 Cae031,
405 Aae013,
406 Aac002,
407 Aac002_New)
408 Values
409 (Seq_Bxgx_Aaz163.Nextval, --使用原来的序列号
410 Pi_Bae001,
411 v_Aab001,
412 v_Aac001,
413 To_Char(Sysdate, 'yyyymm'),
414 '50', -- 50,变更类型为 修改资料
415 '1933', -- 1933,变更原因, 其它
416 '',
417 /*seq_ac25_bae007.nextval,*/ -- 业务流水号
418 Pi_Bae007,
419 Pi_Aae011,
420 To_Char(Sysdate, 'yyyymmddhh24miss'),
421 '0',
422 '',
423 To_Char(Sysdate, 'yyyymmddhh24miss'),
424 '',
425 '',
426 Pi_Aae013,
427 Pi_Aac002,
428 v_Aac002_Tmp);
429 Update Ac01 Set Aac002 = Pi_Aac002 Where Aac002 = v_Aac002_Tmp;
430 Exception
431 When No_Data_Found Then
432 -- Sqlcode, Sqlerrm
433 Po_Fhz := '-1';
434 Po_Msg := '过程' || Pkg_Name || v_Prc || '报错,ac01表里没有找到该人员';
435 End Updateidcard;
436
437 Procedure Cancelupdate(Pi_Bae007 In Varchar2,
438 Po_Fhz Out Varchar2,
439 Po_Msg Out Varchar2) Is
440 v_Aac002 Ac01.Aac002%Type;
441 v_Aac002y Ac25.Aac002%Type;
442 Begin
443 -- 初始化返回值
444
445 Po_Fhz := '-1';
446 Po_Msg := '退单失败';
447 Select a.Aac002_New
448 Into v_Aac002
449 From Ac25 a
450 Where a.Bae007 = Pi_Bae007;
451 Select a.Aac002 Into v_Aac002y From Ac25 a Where a.Bae007 = Pi_Bae007;
452 -- 更新复核标志
453 update ac25 set bce326 = '9' where bae007 = Pi_Bae007;
454 Begin
455 Update Ac01 Set Aac002 = v_Aac002y Where Aac002 = v_Aac002;
456 Po_Fhz := '1';
457 Po_Msg := '成功';
458 Exception
459 When Others Then
460 Po_Fhz := '-2';
461 Po_Msg := '退单失败2';
462 End;
463
464 End;
465
466 Procedure Updateidfh(Pi_Bae007 In Varchar2,
467 Pi_Aae012 In Varchar2,
468 Po_Fhz Out Varchar2,
469 Po_Msg Out Varchar2) Is
470 v_aac002 ac01.aac002%type;
471 v_cnt number(2);
472 Begin
473 -- 初始化返回值
474 Po_Fhz := '-1';
475 Po_Msg := '添加复核人';
476 Begin
477 Update Ac25
478 Set Aae012 = Pi_Aae012, Bce326 = '1'
479 Where Bae007 = Pi_Bae007;
480 select count(1)
481 into v_cnt
482 from ac01
483 where aac002 in
484 (Select Aac002_New From Ac25 Where Bae007 = Pi_Bae007);
485 if v_cnt > 0 then
486 Select Aac002_New into v_aac002 From Ac25 Where Bae007 = Pi_Bae007;
487 Po_Fhz := '-2';
488 Po_Msg := '修改后的新身份证号' || v_aac002 ||
489 ',在新系统已经存在,请回退重新办理!pkg_weiyl.Updateidfh ,Pi_Bae007:' ||
490 Pi_Bae007 || ',Pi_Aae012:' || Pi_Aae012;
491 return;
492 else
493 Update Ac01
494 Set Aac002 =
495 (Select b.Aac002_New From Ac25 b Where Bae007 = Pi_Bae007)
496 Where Aac002 = (Select Aac002 From Ac25 Where Bae007 = Pi_Bae007);
497 end if;
498
499 Po_Fhz := '1';
500 Po_Msg := '添加复核人成功';
501 End;
502 Exception
503 When Others Then
504 Po_Fhz := '-1';
505 Po_Msg := '添加复核人失败,pkg_weiyl.Updateidfh ,Pi_Bae007:' || Pi_Bae007 ||
506 ',Pi_Aae012:' || Pi_Aae012;
507 End Updateidfh;
508 --身份证校验，把医保局，信息中心提出的身份证修改的条件 分出来，单独校验
509 --身份证校验，把医保局，信息中心提出的身份证修改的条件 分出来，单独校验
510 Procedure Validateidcard(Pi_Aac001 In Number,
511 Pi_Aac002_New In Varchar2,
512 Po_Fhz Out Varchar2,
513 Po_Msg Out Varchar2) Is
514 v_Prcname Varchar2(200) := c_Pkg_Name || '.validateIDCARD';
515 v_Params Varchar2(500) := 'pi_aac001=' || Pi_Aac001;
516 v_Aae240 Skc81.Aae240%Type;
517 v_Lc31_Count Number(2);
518 v_Count_Skc81 Number(2);
519 v_Aac002_Tmp Ac25.Aac002_New%Type;
520 v_Count_Cardinfo Number(2);
521 v_Count_Ac60 Number(2);
522 v_Count_Kc21 Number(2);
523 /*function validate_aac002 return varchar2 is
524 v_aac002_new ac25.aac002_new%type;
525 v_aac002_after number(20);
526 begin
527 select a.aac002_new into v_aac002_new from ac25 a where a.aac001 = pi_aac001;
528 v_aac002_after := to_number(v_aac002_new);
529 return '1';
530 exception
531 when others then
532 return '-1';
533 end;*/
534 Begin
535 -- 初始化
536 Po_Fhz := '1';
537 -- 对修改后的身份证进行校验，防止不是纯数字，长度已经在前台进行了校验
538 Vali_Idcard_Lenandchar(Pi_Aac002_New, Po_Fhz, Po_Msg);
539 If Po_Fhz <> '1' Then
540 Return;
541 End If;
542
543 -- 1 医保局提出的要求，如果skc81.aae240>0 ，余额大于0 就不让改身份证。
544 Begin
545 -- 先要判断是否 skc81 是否有数据，有的话在判断 skc81.aae240是否大于0 ，如果skc81 没有数据的话 就不查 余额
546 Select Count(*)
547 Into v_Count_Skc81
548 From Skc81 a
549 Where a.Aac001 = Pi_Aac001;
550 If v_Count_Skc81 > 0 Then
551 Select Nvl(a.Aae240, 0)
552 Into v_Aae240
553 From Skc81 a
554 Where a.Aac001 = Pi_Aac001;
555 If v_Aae240 > 0 Then
556 /*po_fhz := v_prcName || '_91,医疗账户余额为' || v_aae240 || '元 ,因此不允许修改';*/
557 Po_Fhz := v_Prcname ||
558 '_91,职工医疗账户不为0，此模块不允许修改身份证号，请到本人参保地核实身份证号在其他业务模块办理！';
559 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
560 Return;
561 /*享受五险待遇的不能修改，所以包括了失业 享受五险待遇的不能修改，
562 所以包括了失业 没办卡，又享受了失业待遇的 而且个人医保账户为0的*/
563 --如果账户小于0，且
564 Elsif v_Aae240 <= 0 Then
565 Begin
566 Select Aac002
567 Into v_Aac002_Tmp
568 From Ac01
569 Where Aac001 = Pi_Aac001;
570 Select Count(*)
571 Into v_Count_Cardinfo
572 From Card_Info a
573 Where a.Idcard = v_Aac002_Tmp;
574 If v_Count_Cardinfo = 0 Then
575 --如果没有卡信息，然后再对五险进行判断，
576 --add 20150224 有卡也要进行判断
577 Select Count(*)
578 Into v_Count_Ac60
579 From Ac60
580 Where Aaa076 In ('0401',
581 '0403',
582 '0404',
583 '0411',
584 '0421',
585 '0702',
586 '0810',
587 '0821')
588 And Aae116 <> 4
589 And Aac001 = Pi_Aac001;
590 If v_Count_Ac60 > 0 Then
591 Po_Fhz := v_Prcname || '_-92,' ||
592 '该人员没有卡信息，但是有享受待遇，不能修改身份证,请到本人参保地核实身份证号在其他业务模块办理！！';
593 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname,
594 Sqlcode,
595 Sqlerrm,
596 v_Params);
597 Return;
598 End If;
599 --add 20150224 有卡也要进行判断
600 Elsif v_Count_Cardinfo > 0 Then
601 Select Count(*)
602 Into v_Count_Ac60
603 From Ac60
604 Where Aaa076 In ('0401',
605 '0403',
606 '0404',
607 '0411',
608 '0421',
609 '0702',
610 '0810',
611 '0821')
612 And Aae116 <> 4
613 And Aac001 = Pi_Aac001;
614 If v_Count_Ac60 > 0 Then
615 Po_Fhz := v_Prcname || '_-92,' ||
616 '该人员有卡信息，且有享受待遇，不能修改身份证,请到本人参保地核实身份证号在其他业务模块办理！';
617 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname,
618 Sqlcode,
619 Sqlerrm,
620 v_Params);
621 Return;
622 End If;
623 End If;
624 Exception
625 When No_Data_Found Then
626 Po_Fhz := v_Prcname || '_-91,没有找到该人员的基本信息';
627 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname,
628 Sqlcode,
629 Sqlerrm,
630 v_Params);
631 End;
632 End If;
633 End If;
634
635 End;
636 -- 2 信息中心提出的要求，有工伤认定的，也不允许修改
637 Begin
638 Select Count(*) Into v_Lc31_Count From Lc31 Where Aac001 = Pi_Aac001;
639 If v_Lc31_Count > 0 Then
640 Po_Fhz := v_Prcname ||
641 '_92,该人员存在工伤认定记录，不能修改身份证,请到本人参保地核实身份证号在其他业务模块办理！';
642 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
643 Return;
644 End If;
645 End;
646
647 -- 3 住院的不能修改身份证，
648 Begin
649 Select Count(1)
650 Into v_Count_Kc21
651 From Kc21
652 Where Aac001 = Pi_Aac001
653 And Ckc544 = '1';
654 If v_Count_Kc21 > 0 Then
655 /*po_fhz := v_prcName||'_-93,该人员存在在院记录，不允许修改身份证！';*/
656 Po_Fhz := '_-93,该人员存在在院记录，不能修改身份证,请到本人参保地核实身份证号在其他业务模块办理！';
657 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
658 Rollback;
659 Return;
660 End If;
661 End;
662
663 Exception
664 When Others Then
665 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
666 Po_Fhz := '-1';
667 End Validateidcard;
668 /*校验身份证是否合法，包括长度，身份证字母*/
669 Procedure Vali_Idcard_Lenandchar(Pi_Aac002 In Varchar2,
670 Po_Fhz Out Varchar2,
671 Po_Msg Out Varchar2) Is
672 v_Aac002 Ac01.Aac002%Type;
673 v_Count Number(2);
674 v_Params Varchar2(500) := ',传入参数 pi_aac002 = ' || Pi_Aac002;
675 v_Procname Varchar2(50) := c_Pkg_Name || '.vali_IdCard_lenAndChar';
676 v_Aac002_n Number(20); -- 数值型，用于接收转换后的身份证号
677 v_Char Varchar2(2); -- 用于接收字符
678 v_Char2 Varchar2(2);
679 Begin
680 -- 初始化返回值
681 Po_Fhz := '1';
682 Po_Msg := Pkg_Fun.f_Errmsg(v_Procname, Sqlcode, Sqlerrm, v_Params);
683 Select Count(*) Into v_Count From Ac01 Where Aac002 = Pi_Aac002;
684 If v_Count > 0 Then
685 Select Trim(Pi_Aac002) Into v_Aac002 From Dual;
686 -- 检查是否为18位
687 If Length(v_Aac002) <> 18 Then
688 Po_Fhz := v_Procname || '_-91,身份证长度不为18位';
689 Po_Msg := Pkg_Fun.f_Errmsg(v_Procname, Sqlcode, Sqlerrm, v_Params);
690 Return;
691 End If;
692 Begin
693 -- 检查前17位是否有字母
694 v_Aac002 := Substr(v_Aac002, 1, Length(v_Aac002) - 1);
695 v_Aac002_n := To_Number(v_Aac002);
696 Exception
697 When Value_Error Then
698 Po_Fhz := v_Procname || '_-92,身份证的前17位中含有非数值型字符';
699 Po_Msg := Pkg_Fun.f_Errmsg(v_Procname, Sqlcode, Sqlerrm, v_Params);
700 Return;
701 End;
702 -- 如果最后一位是字母，;
703 Begin
704 -- 检查前17位是否有字母
705 Select Trim(Aac002)
706 Into v_Aac002
707 From Ac01
708 Where Aac002 = Pi_Aac002;
709 v_Aac002 := Substr(v_Aac002, Length(v_Aac002), 1);
710 v_Aac002_n := To_Number(v_Aac002);
711 Exception
712 When Value_Error Then
713 -- 说明最后一位是字母
714 -- 如果最后一位是字母，检查最后一位数是否为x;
715 Select Chr(88) Into v_Char From Dual;
716 Select Upper((Substr(v_Aac002, Length(v_Aac002), 1)))
717 Into v_Char2
718 From Dual; --存放截取的最后一位字符
719 If v_Char2 <> v_Char Then
720 Po_Fhz := v_Procname || '_-93,身份证的最后一位不是大写的X';
721 Po_Msg := Pkg_Fun.f_Errmsg(v_Procname,
722 Sqlcode,
723 Sqlerrm,
724 v_Params);
725 Return;
726 End If;
727 End;
728 End If;
729 Exception
730 When Others Then
731 Po_Fhz := v_Procname || '_-94,未知错误';
732 Po_Msg := Pkg_Fun.f_Errmsg(v_Procname, Sqlcode, Sqlerrm, v_Params);
733 End Vali_Idcard_Lenandchar;
734
735 --修改身份证号
736 Procedure Updateidcard_Fortest(Pi_Aac002 In Varchar2,
737 Pi_Aac002_New In Varchar2,
738 Pi_Aae013 In Varchar2,
739 Pi_Aae011 In Varchar2,
740 Pi_Bae001 In Varchar2,
741 Po_Fhz Out Varchar2,
742 Po_Msg Out Varchar2) Is
743 v_Aac001 Ac02.Aac001%Type;
744 v_Prc Varchar2(200);
745 v_Aab001 Ac02.Aab001%Type;
746 v_Yl_Count Number(2); --养老待遇记录数
747 v_Msg Varchar2(200);
748 v_Aaa076 Ac60.Aaa076%Type;
749 v_Prcname Varchar2(200);
750 v_Params Varchar2(500);
751 Begin
752 -- 初始化返回值
753 Po_Fhz := '1';
754 Po_Msg := '';
755 v_Prc := '.updateIDCard_fortest';
756 v_Prcname := c_Pkg_Name || v_Prc;
757 v_Params := ',传入参数为:pi_aac002=' || Pi_Aac002 || ',pi_aac002_new=' ||
758 Pi_Aac002_New || ',pi_aae013' || Pi_Aae013 ||
759 ',pi_aae011=' || Pi_Aae011 || ',pi_bae001=' || Pi_Bae001;
760 Select Aac001, Aab001
761 Into v_Aac001, v_Aab001
762 From Ac01
763 Where Aac002 = Pi_Aac002;
764 -- 先做 是否有养老待遇的判断,如果有就直接返回，不更新ac01.aac002
765 Select Count(1) Into v_Yl_Count From Ac60 Where Aac001 = v_Aac001;
766 If v_Yl_Count > 0 Then
767 -- 如果有养老待遇，那么抛出更详细的结果，以便于前台更容易理解
768 -- 只取第一条
769 Select Aaa076
770 Into v_Aaa076
771 From Ac60
772 Where Aac001 = v_Aac001
773 And Rownum = 1;
774 Select Func_Wyl('AAA076', v_Aaa076) Into v_Msg From Dual;
775 Po_Fhz := '-1';
776 Po_Msg := Pkg_Name || v_Prc ||
777 '执行失败，该人员有待遇享受信息，此模块不允许修改身份证号!养老待遇类型为 "' || v_Msg ||
778 '" 的养老待遇，且待遇状态正常';
779 Return;
780 End If;
781 -- 更新ac01.AAC002
782 Update Ac01 Set Aac002 = Pi_Aac002_New Where Aac001 = v_Aac001;
783 Insert Into Ac25
784 (Aaz163,
785 Bae001,
786 Aab001,
787 Aac001,
788 Cae129,
789 Aac050,
790 Aae160,
791 Cac038,
792 Bae007,
793 Aae011,
794 Aae036,
795 Bce326,
796 Aae012,
797 Bhe949,
798 Cae030,
799 Cae031,
800 Aae013)
801 Values
802 (Seq_Bxgx_Aaz163.Nextval, --使用原来的序列号
803 Pi_Bae001,
804 v_Aab001,
805 v_Aac001,
806 '201310',
807 '50', -- 50,变更类型为 修改资料
808 '1933', -- 1933,变更原因, 其它
809 '',
810 '99999999', -- 业务流水号
811 Pi_Aae011,
812 To_Char(Sysdate, 'yyyymmddhh24miss'),
813 '1',
814 Pi_Aae011,
815 To_Char(Sysdate, 'yyyymmddhh24miss'),
816 '',
817 '',
818 Pi_Aae013);
819
820 Exception
821 When No_Data_Found Then
822 -- Sqlcode, Sqlerrm
823 Po_Fhz := v_Prcname || '_01';
824 Po_Msg := '过程' || Pkg_Name || v_Prc || '报错,ac01表里没有找到该人员,sqlcode:' ||
825 Sqlcode || ',sqlerrm:' || Sqlerrm;
826 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
827 Return;
828 End Updateidcard_Fortest;
829
830 -- 截取字符串
831 -- create or replace type split_type is table of varchar2(4000)
832 -- 取出字符串的方法 ： select column_value from table (pkg_weiyl.split_dh('dd,aa,134'));
833 Function Split_Dh(p_Str In Varchar2,
834 p_Delimiter In Varchar2 Default (',') --分隔符，默认逗号
835 ) Return Split_Type Is
836 j Int := 0;
837 i Int := 1;
838 Len Int := 0;
839 Len1 Int := 0;
840 Str Varchar2(4000);
841 My_Split Split_Type := Split_Type();
842 Begin
843 -- 要分割的字符串的长度
844 Len := Length(p_Str);
845 -- 分隔符的长度
846 Len1 := Length(p_Delimiter);
847
848 While j < Len Loop
849 j := Instr(p_Str, p_Delimiter, i);
850
851 If j = 0 Then
852 j := Len;
853 Str := Substr(p_Str, i);
854 My_Split.Extend;
855 My_Split(My_Split.Count) := Str;
856
857 If i >= Len Then
858 Exit;
859 End If;
860 Else
861 Str := Substr(p_Str, i, j - i);
862 i := j + Len1;
863 My_Split.Extend;
864 My_Split(My_Split.Count) := Str;
865 End If;
866 End Loop;
867
868 Return My_Split;
869 End Split_Dh;
870
871 --字典转换，把代码值转换成对应的可理解的中文,卫永乐,20141105
872 Function Func_Wyl(Pi_Aaa100 Varchar2, Pi_Aaa102 Varchar2) Return Varchar2 Is
873 v_Aaa103 Varchar2(1000);
874 Begin
875 Select Aaa103
876 Into v_Aaa103
877 From Aa10 a
878 Where a.Aaa100 = Pi_Aaa100
879 And a.Aaa102 = Pi_Aaa102;
880 Return v_Aaa103;
881 Exception
882 When No_Data_Found Then
883 Dbms_Output.Put_Line('没有找到数据');
884 End;
885
886 Function Func_Check_Para(Pi_Aab999 In Varchar2, --单位编号
887 Pi_Pch In Varchar2, --批次号
888 Pi_Aae001 In Number,
889 Pi_Aae036 In Varchar2,
890 Pi_Aac027 In Number)
891 /*拼接where条件 过程 check_sdnmdc 的游标的where条件*/
892 Return Varchar2 Is
893 v_where varchar2(500);
894 Begin
895 /*v_where := ' where 1=1 and ';*/
896 v_where := ' null or 1=1 ';
897 /*Select * From v_sdnm_sjql_dc Where Aab999 = To_Char(Pi_Aab999);*/
898
899 if pi_aab999 <> '0' then
900 v_where := v_where || ' and aab999=''' || pi_aab999 || '''';
901 elsif Pi_Aab999 = '0' then
902 v_where := ' null or 1=1 ';
903 end if;
904 if Pi_Aae001 <> '0' then
905 v_where := v_where || ' and aae001=' || pi_aae001;
906 /*elsif Pi_Aae001 = '0' then
907 v_where := v_where||' and aae001='||pi_aae001;*/
908 end if;
909 if Pi_Aae036 <> '0' then
910 v_where := v_where || ' and substr(aae036,1,6)=''' || Pi_Aae036 || '''';
911 /*elsif Pi_Aae036 = '0' then
912 v_where := v_where||' and substr(aae036,1,6)='''||Pi_Aae036||'''';*/
913 end if;
914 if Pi_Aac027 <> '0' then
915 v_where := v_where || ' and aac027=' || Pi_Aac027 || ' ';
916 end if;
917 Return v_where;
918 End Func_Check_Para;
919
920 /*
921 added 20160801
922 获取月份差值，主要针对于转移统计的
923 */
924 function getMonthNum(pi_ksyf in number,
925 pi_zzyf in number,
926 pi_aae180 in number) return number is
927 v_mon number(3);
928
929 begin
930 /*
931 如果 sac14 的 aae041 201601 ,aae042 201602 ,aae180 0 ,这种情况总的月份 就算是0 ，(201602-201601+1)*aae180
932 否则 就算是201602-201601+1 = 2 ;
933 */
934 if nvl(pi_aae180, 0) != 0 and pi_zzyf >= pi_ksyf then
935 v_mon := pi_zzyf - pi_ksyf + 1;
936 else
937 v_mon := 0;
938 end if;
939 return v_mon;
940 end getMonthNum;
941
942 --县级公立医院支付
943 Procedure Xjyyzf(Pi_Bae007 In Varchar2,
944 Pi_Operid In Varchar2,
945 Pi_Aae037 In Varchar2,
946 Pi_Aae038 In Varchar2,
947 Po_Fhz Out Varchar2,
948 Po_Msg Out Varchar2) Is
949 v_Bae007 Skc70.Bae007%Type;
950 v_Time Skc70.Aae015%Type;
951 Cursor c_Bae007 Is
952 Select Column_Value From Table(Pkg_Weiyl.Split_Dh(Pi_Bae007));
953 Begin
954 Po_Msg := '成功';
955 Po_Fhz := '1';
956 Select To_Char(Sysdate, 'yyyymmddhh24miss') Into v_Time From Dual;
957
958 For v_Bae007 In c_Bae007 Loop
959 -- 清空改经办人的临时表
960 Delete From t_Skc70 a
961 Where a.Bae007 = Pi_Bae007
962 And a.Aae011 = Pi_Operid;
963 Insert Into t_Skc70
964 (Bae007, Aae011)
965 Values
966 (v_Bae007.Column_Value, Pi_Operid);
967 Update Skc70
968 Set Aae117 = '1',
969 Cae295 = Pi_Operid,
970 Aae015 = v_Time,
971 Aae037 = Substr(Pi_Aae037, 1, 8),
972 Aae038 = Substr(Pi_Aae038, 1, 8)
973 Where Bae007 = v_Bae007.Column_Value;
974 End Loop;
975 Exception
976 When Others Then
977 Rollback;
978 Po_Msg := '失败';
979 Po_Fhz := '-1';
980 End Xjyyzf;
981
982 --- 循环调用的过程，解决每次都要自己写一个declare plsql代码块，
983 --入参：还没写完
984
985 Procedure Prc_Xunhuan(Pi_Prcname In Varchar2, --要调用的过程名
986 Pi_Tabname In Varchar2, --cursor取值的表
987 Po_Fhz Out Varchar2,
988 Po_Msg Out Varchar2) Is
989 v_Prcname Varchar2(400) := '.prc_xunhuan';
990 v_Params Varchar2(200) := 'pi_prcName=' || Pi_Prcname ||
991 ',pi_tabName:' || Pi_Tabname;
992 /*cursor cur_xunhuan is
993 select * from pi_tabName where aac001 = '';*/
994 Begin
995
996 Null;
997 Exception
998 When Others Then
999 Po_Fhz := '';
1000 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
1001 End;
1002 Procedure Xjyyzf_Callback(Pi_Bae007 In Varchar2,
1003 Pi_Operid In Varchar2,
1004 Po_Fhz Out Varchar2,
1005 Po_Msg Out Varchar2) Is
1006 v_Bae007 Skc70.Bae007%Type;
1007 v_Time Skc70.Aae015%Type;
1008 Cursor c_Bae007 Is
1009 Select Column_Value From Table(Pkg_Weiyl.Split_Dh(Pi_Bae007));
1010 Begin
1011 Po_Msg := '成功';
1012 Po_Fhz := '1';
1013 For v_Bae007 In c_Bae007 Loop
1014 -- 清空改经办人的临时表
1015 Delete From t_Skc70 a
1016 Where a.Bae007 = v_Bae007.Column_Value
1017 And a.Aae011 = Pi_Operid;
1018 End Loop;
1019 Exception
1020 When Others Then
1021 --rollback;
1022 Po_Msg := '失败';
1023 Po_Fhz := '-1';
1024 End Xjyyzf_Callback;
1025
1026 --县级公立医院支付取消
1027 Procedure Xjyyzfqx(Pi_Bae007 In Varchar2,
1028 Po_Fhz Out Varchar2,
1029 Po_Msg Out Varchar2) Is
1030 v_Bae007 Skc70.Bae007%Type;
1031 Cursor c_Bae007 Is
1032 Select Column_Value From Table(Pkg_Weiyl.Split_Dh(Pi_Bae007));
1033 Begin
1034 Po_Msg := '成功';
1035 Po_Fhz := '1';
1036 For v_Bae007 In c_Bae007 Loop
1037 --清空临时表
1038 Delete From t_Skc70 Where Bae007 = v_Bae007.Column_Value;
1039 Update Skc70 Set Aae117 = '0' Where Bae007 = v_Bae007.Column_Value;
1040 End Loop;
1041 Exception
1042 When Others Then
1043 Po_Msg := '失败';
1044 Po_Fhz := '-1';
1045 End Xjyyzfqx;
1046 /*自治事务*/
1047 Procedure Autonomous_Tran(Pi_Aac001 In Varchar2,
1048 Po_Fhz Out Varchar2,
1049 Po_Msg Out Varchar2) Is
1050 Pragma Autonomous_Transaction; --自治事务关键字
1051 v_Proname Varchar2(400);
1052 v_Aab001 Number(20);
1053 Begin
1054 Po_Fhz := '1';
1055 Po_Msg := '成功！';
1056 v_Aab001 := Seq_Bxgx_Aab001.Nextval;
1057 Insert Into Xzl_One (Aac002) Values (v_Aab001); -- v_aab001 是number,竟然可以插入varchar2类型字段
1058 --commit;
1059 Update Xzl_One
1060 Set Aac003 =
1061 (v_Aab001 || '姓名')
1062 Where Aac002 = v_Aab001 || ''; --但是查却没法查到
1063 Commit;
1064 Exception
1065 When Others Then
1066 Rollback;
1067 Po_Fhz := '-1';
1068 Po_Msg := '失败,' || Sqlcode || ',sqlerrm:' || Sqlerrm;
1069 Return;
1070 End Autonomous_Tran;
1071 /*手工添加表级锁，使用场景，如果要操作一张大表的大部分数据，
1072 如果不加表级锁，那么就会耗费大量的资源，这种情况下可以使用
1073 手工给表加锁，释放方式 rollback，或者commit
1074 使用目的：更改 ac02_tmp表 的险种状态，ac02_tmp 表结构和数据
1075 和ac02的一样
1076 */
1077 Procedure Update_Ac02_Aae140(Pi_Aae140 In Varchar2,
1078 Pi_Fhz Out Varchar2,
1079 Po_Msg Out Varchar2) Is
1080 Cursor Cur_Ac02 Return Ac02_Tmp%Rowtype Is
1081 Select * From Ac02_Tmp Where Aae140 = Pi_Aae140;
1082 v_Cur_Ac02 Ac02_Tmp%Rowtype;
1083 Begin
1084 --sys.dbms_profiler.start_profiler;
1085 Lock Table Ac02_Tmp In Exclusive Mode Nowait;
1086 Open Cur_Ac02;
1087 Loop
1088 Fetch Cur_Ac02
1089 Into v_Cur_Ac02;
1090 Exit When Cur_Ac02%Notfound; --检查是否找到，
1091 -- 找到的话就就更新
1092 Update Ac02_Tmp
1093 Set Aac008 = '8'
1094 Where Aae140 = Pi_Aae140
1095 And Aac008 = '2';
1096 Null;
1097 End Loop;
1098 Close Cur_Ac02;
1099 Commit; --释放表级锁
1100 --sys.dbms_profiler.stop_profiler;
1101 End Update_Ac02_Aae140;
1102
1103 Procedure Querycheck(Pi_Aac002 In Varchar2,
1104 Pi_Aac003 In Varchar2,
1105 Po_Fhz Out Varchar2,
1106 Po_Msg Out Varchar2) Is
1107 v_Count Number(2);
1108 Begin
1109 --初始化返回值
1110 Po_Fhz := '-1';
1111 Po_Msg := '失败,该人员没有卡信息';
1112 If Pi_Aac002 = '0' Then
1113 Select Count(*) Into v_Count From Card_Info Where Name = Pi_Aac003;
1114 If v_Count > 0 Then
1115 Po_Fhz := '1';
1116 Po_Msg := '成功';
1117 End If;
1118 End If;
1119 If Pi_Aac003 = '0' Then
1120 Select Count(*) Into v_Count From Card_Info Where Idcard = Pi_Aac002;
1121 If v_Count > 0 Then
1122 Po_Fhz := '1';
1123 Po_Msg := '成功';
1124 End If;
1125 End If;
1126 End;
1127 Procedure Check_Ac02(Pi_Aac002 In Varchar2,
1128 Po_Aac001 Out Number,
1129 Po_Cac012 Out Varchar2,
1130 Po_Aab001 Out Varchar2,
1131 Po_Fhz Out Varchar2,
1132 Po_Msg Out Varchar2) Is
1133 v_Count1 Number(2);
1134 v_Count2 Number(2);
1135 v_Count3 Number(2);
1136 v_Aab001 Ac02.Aab001%Type;
1137 v_Aac001 Ac01.Aac001%Type;
1138 v_Cac012 Sac01.Cac012%Type;
1139 Begin
1140 -- 初始化返回值
1141 Po_Fhz := '1';
1142 Select Count(1) Into v_Count1 From Ac01 Where Aac002 = Pi_Aac002;
1143 If v_Count1 <= 0 Then
1144 Po_Msg := '该人员不存在';
1145 Po_Fhz := '-1';
1146 Return;
1147 End If;
1148 Select Aac001 Into v_Aac001 From Ac01 Where Aac002 = Pi_Aac002;
1149 Select Count(1) Into v_Count2 From Sac01 Where Aac001 = v_Aac001;
1150 If v_Count2 <= 0 Then
1151 Po_Msg := '该人员缺少对应的sac01的信息,';
1152 Po_Fhz := '-1';
1153 Return;
1154 End If;
1155 Select a.Cac012 Into v_Cac012 From Sac01 a Where Aac001 = v_Aac001;
1156 Select Count(1)
1157 Into v_Count3
1158 From Ac02
1159 Where Aae140 = '342'
1160 And Aac001 = v_Aac001
1161 And Aac008 = '1';
1162 If v_Count3 <= 0 Then
1163 Po_Msg := '该人员没有正常参保';
1164 Po_Fhz := '-1';
1165 Return;
1166 End If;
1167 Select Distinct (Aab001)
1168 Into v_Aab001
1169 From Ac02
1170 Where Aac001 = v_Aac001
1171 And Aae140 = '342'
1172 And Aac008 = '1';
1173 Po_Aab001 := v_Aab001;
1174 Po_Aac001 := v_Aac001;
1175 Po_Cac012 := v_Cac012;
1176 End;
1177
1178 Procedure Check_Skc84(Pi_Aac001 In Varchar2,
1179 Po_Fhz Out Varchar2,
1180 Po_Msg Out Varchar2) Is
1181 v_Count1 Number(2);
1182 v_Count2 Number(2);
1183 v_Aac001 Ac01.Aac001%Type;
1184 Begin
1185 -- 初始化返回值
1186 Po_Fhz := '1';
1187 Select Count(1)
1188 Into v_Count1
1189 From Skc84
1190 Where Aac001 = Pi_Aac001
1191 And Aae140 = '342';
1192 If v_Count1 <= 0 Then
1193 Po_Msg := '该人员不存在当年的医保缴费';
1194 Po_Fhz := '-1';
1195 Return;
1196 End If;
1197 End;
1198 /*失地农民数据清理导出前，
1199 把导出的数据保存到ic58里，防止再次导出
1200 */
1201 Procedure Check_Sdnmdcc(Pi_Aab999 In Varchar2,
1202 PI_PCH in varchar2,
1203 Po_Fhz Out Varchar2,
1204 Po_Msg Out Varchar2) Is
1205 v_Aac001 Ac01.Aac001%Type;
1206 v_Prcname Varchar2(200);
1207 v_Params Varchar2(200);
1208 v_Ic58_Count Number(5);
1209 Cursor c_Ydcry Is
1210 Select * From v_Sdnm_Sjql_Dc Where Aab999 = To_Char(Pi_Aab999);
1211 Begin
1212 v_Prcname := 'pkg_weiyl.check_sdnmdc';
1213 v_Params := 'pi_aab999:' || Pi_Aab999;
1214 Po_Fhz := '1';
1215 Po_Msg := '成功';
1216 /*清空ic61*/
1217 For c_Tmp In c_Ydcry Loop
1218 Insert Into Ic58 (Aac001, Aab999) Values (c_Tmp.Aac001, Pi_Aab999);
1219 /*导出用这个表*/
1220 Insert Into Ic61 (Aac001, Aab999) Values (c_Tmp.Aac001, Pi_Aab999);
1221 /*加入批次号 */
1222 insert into ic58_dc
1223 (aac001, aab999, aaz601)
1224 values
1225 (c_Tmp.Aac001, Pi_Aab999, PI_PCH);
1226 End Loop;
1227 Select Count(1) Into v_Ic58_Count From Ic58 Where Aab999 = Pi_Aab999;
1228 If v_Ic58_Count > 0 Then
1229 Po_Fhz := '2';
1230 Po_Msg := '该单位已经进行过导出业务的操作，此次导出将导出该单位下剩余部分的人员，如果之前已经全部导出，那么此次将不导出数据';
1231 End If;
1232 Exception
1233 When Others Then
1234 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
1235 Po_Fhz := '-1';
1236 End Check_Sdnmdcc;
1237 /*
1238 现在正在使用的失地农民数据导出
1239 这里使用了动态sql，
1240 注意点：在存储过程里的增删改查语句如果有拼接，
1241 那么一定要用动态sql,否则可能能够编译通过，但是
1242 执行的时候没有达到预期。因为：拼接的where条件很可能被
1243 引擎当作普通字符串处理，也就什么都没查得到，自然更新数据就没法更新成功
1244 */
1245 Procedure Check_Sdnmdc(Pi_Bae001 In Varchar2,
1246 Pi_Aab999 In Varchar2,
1247 Pi_Pch In Varchar2,
1248 /* PI_GLT in varchar2,*/
1249 Pi_Aae001 In Varchar2,
1250 Pi_Aae036 In Varchar2,
1251 Pi_Aac027 In Varchar2,
1252 Pi_Bzw In Varchar2,
1253 Pi_Oper In Varchar2,
1254 Po_Fhz Out Varchar2,
1255 Po_Msg Out Varchar2) Is
1256 v_Aac001 Ac01.Aac001%Type;
1257 v_Prcname Varchar2(200);
1258 v_Params Varchar2(200);
1259 v_Ic58_Count Number(5);
1260 v_Tmp Varchar2(400);
1261 v_Sql Varchar2(1000);
1262 Cursor c_Ydcry Is
1263 Select *
1264 From v_Sdnm_Sjql_Dc_Multi
1265 Where Aab999 = Func_Check_Para(Pi_Aab999,
1266 Pi_Pch,
1267 Pi_Aae001,
1268 Pi_Aae036,
1269 Pi_Aac027);
1270 Begin
1271
1272 v_Prcname := 'pkg_weiyl.check_sdnmdc';
1273 v_Params := 'pi_aab999:' || Pi_Aab999;
1274 Po_Fhz := '1';
1275 Po_Msg := '成功';
1276 /*拼接where条件*/
1277 Select Func_Check_Para(Pi_Aab999,
1278 Pi_Pch,
1279 Pi_Aae001,
1280 Pi_Aae036,
1281 Pi_Aac027)
1282 Into v_Tmp
1283 From Dual;
1284 /*insert into ic58_dcsj() values (select * from v_sdnm_sjql_dc a where 1=1 and aab999 = '02002489');*/
1285 /*v_Sql := 'Insert Into Ic58_Dcsj
1286 Select a.*, ' || To_Char(Sysdate, 'yyyymmddhh24miss') || ',' ||
1287 Pi_Pch || ',' || '1' || '
1288 From v_Sdnm_Sjql_Dc a
1289 Where a.bae001 = '''||Pi_Bae001||''' and Aab999 = ' || v_Tmp ||
1290 ' And Not Exists (Select *
1291 From Ic58_Dcsj z
1292 Where z.Aaz288 = a.Aaz288
1293 And z.Aac001 = a.Aac001) ';*/
1294 -- 20160505 altered
1295 /* v_Sql := 'Insert Into Ic58_Dcsj
1296 Select a.*, ' || To_Char(Sysdate, 'yyyymmddhh24miss') || ',' ||
1297 Pi_Pch || ',' || '''' || Pi_Bzw || '''' || ',''' || Pi_Oper || '''
1298 From v_Sdnm_Sjql_Dc a
1299 Where Aab999 = ' || v_Tmp || ' and bae001 = ''' ||
1300 pi_bae001 || ''' And Not Exists (Select *
1301 From Ic58_Dcsj z
1302 Where z.Aaz288 = a.Aaz288
1303 And z.Aac001 = a.Aac001) ';*/
1304 v_Sql := 'Insert Into Ic58_Dcsj
1305 Select a.*, ' || '1,' ||
1306 To_Char(Sysdate, 'yyyymmddhh24miss') || ',' || Pi_Pch || ',' || '''' ||
1307 Pi_Bzw || '''' || ',''' || Pi_Oper || '''
1308 From v_Sdnm_Sjql_Dc a
1309 Where Aab999 = ' || v_Tmp || ' and bae001 = ''' ||
1310 pi_bae001 || ''' And Not Exists (Select *
1311 From Ic58_Dcsj z
1312 Where z.Aaz288 = a.Aaz288
1313 And z.Aac001 = a.Aac001) ';
1314 Execute Immediate v_Sql;
1315 /*清空ic61*/
1316 /*For c_Tmp In c_Ydcry Loop
1317 Insert Into Ic58 (Aac001, Aab999) Values (c_Tmp.Aac001, Pi_Aab999);
1318 导出用这个表
1319 Insert Into Ic61 (Aac001, Aab999) Values (c_Tmp.Aac001, Pi_Aab999);
1320 加入批次号
1321 Insert Into Ic58_Dc
1322 (Aac001, Aab999, Aaz601, Bzw)
1323 Values
1324 (c_Tmp.Aac001, Pi_Aab999, Pi_Pch, '1');
1325 备份失地农民数据
1326 update ic58_dcsj q set q.dcsj=To_Char(Sysdate, 'yyyymmddhh24miss'),q.aaz601=PI_PCH;
1327 End Loop;*/
1328 /*Select Count(1) Into v_Ic58_Count From Ic58 Where Aab999 = Pi_Aab999;*/
1329 Select Count(1)
1330 Into v_Ic58_Count
1331 From Ic58_Dcsj
1332 Where Aaz601 <> Pi_Pch
1333 And Aab999 = Pi_Aab999;
1334 If v_Ic58_Count > 0 Then
1335 Po_Fhz := '2';
1336 Po_Msg := '该单位已经进行过导出业务的操作，此次导出将导出该单位下剩余部分的人员，如果之前已经全部导出，那么此次将不导出数据';
1337 End If;
1338 Exception
1339 When Others Then
1340 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
1341 Po_Fhz := '-1';
1342 End Check_Sdnmdc;
1343
1344 /*失地农民数据清理导出前，
1345 把导出的数据保存到ic58里，防止再次导出
1346 导出单条的check
1347 02002489
1348 */
1349 Procedure Check_Sdnmdc_Multi(Pi_Bae001 In Varchar2,
1350 Pi_Aab999 In Varchar2,
1351 Pi_Pch In Varchar2,
1352 /* PI_GLT in varchar2,*/
1353 Pi_Aae001 In Varchar2,
1354 Pi_Aae036 In Varchar2,
1355 Pi_Aac027 In Varchar2,
1356 Pi_Bzw In Varchar2,
1357 Pi_Oper In Varchar2,
1358 Po_Fhz Out Varchar2,
1359 Po_Msg Out Varchar2) Is
1360 v_Aac001 Ac01.Aac001%Type;
1361 v_Prcname Varchar2(200);
1362 v_Params Varchar2(200);
1363 v_Ic58_Count Number(5);
1364 v_Tmp Varchar2(400);
1365 v_Sql Varchar2(1000);
1366 Cursor c_Ydcry Is
1367 Select *
1368 From v_Sdnm_Sjql_Dc
1369 Where Aab999 = Func_Check_Para(Pi_Aab999,
1370 Pi_Pch,
1371 Pi_Aae001,
1372 Pi_Aae036,
1373 Pi_Aac027);
1374 Begin
1375
1376 v_Prcname := 'pkg_weiyl.Check_Sdnmdc_Multi';
1377 v_Params := 'pi_aab999:' || Pi_Aab999;
1378 Po_Fhz := '1';
1379 Po_Msg := '成功';
1380 /*拼接where条件*/
1381 Select Func_Check_Para(Pi_Aab999,
1382 Pi_Pch,
1383 Pi_Aae001,
1384 Pi_Aae036,
1385 Pi_Aac027)
1386 Into v_Tmp
1387 From Dual;
1388 /*insert into ic58_dcsj() values (select * from v_sdnm_sjql_dc a where 1=1 and aab999 = '02002489');*/
1389 /*v_Sql := 'Insert Into Ic58_Dcsj
1390 Select a.*, ' || To_Char(Sysdate, 'yyyymmddhh24miss') || ',' ||
1391 Pi_Pch || ',' || ''''||Pi_Bzw||''''||','''|| Pi_Oper|| '''
1392 From v_Sdnm_Sjql_Dc_multi a
1393 Where Aab999 = ' || v_Tmp ||
1394 ' and bae001 = '''||pi_bae001||''' And Not Exists (Select *
1395 From Ic58_Dcsj z
1396 Where z.Aaz288 = a.Aaz288
1397 And z.Aac001 = a.Aac001) ';*/
1398 v_Sql := 'Insert Into Ic58_Dcsj
1399 Select a.*, ' || To_Char(Sysdate, 'yyyymmddhh24miss') || ',' ||
1400 Pi_Pch || ',' || '''' || Pi_Bzw || '''' || ',''' || Pi_Oper || '''
1401 From v_Sdnm_Sjql_Dc_multi a
1402 Where Aab999 = ' || v_Tmp || ' and bae001 = ''' ||
1403 pi_bae001 || ''' And Not Exists (Select *
1404 From Ic58_Dcsj z
1405 Where z.Aaz288 = a.Aaz288
1406 And z.Aac001 = a.Aac001) ';
1407 Execute Immediate v_Sql;
1408 /*清空ic61*/
1409 /*For c_Tmp In c_Ydcry Loop
1410 Insert Into Ic58 (Aac001, Aab999) Values (c_Tmp.Aac001, Pi_Aab999);
1411 导出用这个表
1412 Insert Into Ic61 (Aac001, Aab999) Values (c_Tmp.Aac001, Pi_Aab999);
1413 加入批次号
1414 Insert Into Ic58_Dc
1415 (Aac001, Aab999, Aaz601, Bzw)
1416 Values
1417 (c_Tmp.Aac001, Pi_Aab999, Pi_Pch, '1');
1418 备份失地农民数据
1419 update ic58_dcsj q set q.dcsj=To_Char(Sysdate, 'yyyymmddhh24miss'),q.aaz601=PI_PCH;
1420 End Loop;*/
1421 /*Select Count(1) Into v_Ic58_Count From Ic58 Where Aab999 = Pi_Aab999;*/
1422 Select Count(1)
1423 Into v_Ic58_Count
1424 From Ic58_Dcsj
1425 Where Aaz601 <> Pi_Pch
1426 And Aab999 = Pi_Aab999;
1427 If v_Ic58_Count > 0 Then
1428 Po_Fhz := '2';
1429 Po_Msg := '该单位已经进行过导出业务的操作，此次导出将导出该单位下剩余部分的人员，如果之前已经全部导出，那么此次将不导出数据';
1430 End If;
1431 Exception
1432 When Others Then
1433 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
1434 Po_Fhz := '-1';
1435 End Check_Sdnmdc_Multi;
1436
1437 Procedure Updatekbb5(Pi_Bae007 In Varchar2,
1438 Pi_Ckz545 In Varchar2,
1439 Pi_Ckb626 In Varchar2,
1440 Pi_Ckb627 In Varchar2,
1441 Pi_Ckb629 In Varchar2,
1442 Pi_Ckb630 In Varchar2,
1443 Po_Fhz Out Varchar2,
1444 Po_Msg Out Varchar2) Is
1445 Begin
1446 Po_Fhz := '1';
1447 Po_Msg := '成功';
1448 Update Kbb5
1449 Set Ckz545 = Pi_Ckz545,
1450 Ckb626 = Pi_Ckb626,
1451 Ckb627 = Pi_Ckb627,
1452 Ckb629 = Pi_Ckb629,
1453 Ckb630 = Pi_Ckb630
1454 Where Bae007 = Pi_Bae007;
1455 End;
1456 Procedure Getaaz601(Pi_Rc In Varchar2,
1457 po_aaz601 out number,
1458 Po_Fhz Out Varchar2,
1459 Po_Msg Out Varchar2) Is
1460 v_aaz601 ic58_dc.aaz601%type;
1461 Begin
1462 Po_Fhz := '1';
1463 Po_Msg := '成功';
1464 Select Seq_Ggyw_Aaz601.Nextval into po_aaz601 From Dual;
1465 exception
1466 when others then
1467 po_msg := 'pkg_weiyl.Getaaz601 获取批次号失败';
1468 Po_Fhz := '-1';
1469 End;
1470 /*
1471 知识库相关过程
1472 20160323 by weiyongle
1473 */
1474 Procedure Insertfw_Zsk(PI_AAA200 in varchar2,
1475 Pi_Aae202 In Varchar2,
1476 Pi_Aaa203 In Varchar2,
1477 Pi_Aae008 In Varchar2,
1478 PI_AAE011 IN VARCHAR2,
1479 PI_AAE906 IN VARCHAR2,
1480 PI_BZ IN VARCHAR2,
1481 Po_Fhz Out Varchar2,
1482 Po_Msg Out Varchar2) is
1483 begin
1484 po_fhz := '1';
1485 po_msg := '成功';
1486 null;
1487 /*如果是'1'，那么说明是修改*/
1488 if pi_bz = '1' then
1489 update fw_zsk
1490 set aae202 = Pi_Aae202,
1491 aaa203 = Pi_Aaa203,
1492 aae008 = pi_aae008,
1493 aae011 = PI_AAE011
1494 where aae906 = PI_AAE906;
1495 elsif pi_bz = '0' then
1496 insert into fw_zsk
1497 (aaa200, aae202, aaa203, aae008, aae011, aae036, aae906)
1498 values
1499 (PI_AAA200,
1500 Pi_Aae202,
1501 Pi_Aaa203,
1502 pi_aae008,
1503 PI_AAE011,
1504 To_Char(Sysdate, 'yyyymmddhh24miss'),
1505 SEQ_FW_AAE906.Nextval);
1506 end if;
1507 exception
1508 when others then
1509 po_fhz := '-1';
1510 po_msg := '失败';
1511 end Insertfw_Zsk;
1512 /*拼接两个字符串,
1513 练手嵌套存储过程
1514 注意点：使用嵌套存储过程时，在declaration中可以同时定义变量
1515 但是 定义的变量只能够写在嵌套存储过程的前面，否则没法编译通过
1516 20160407
1517 */
1518 procedure testNestedPro(pi_xing in varchar2,
1519 pi_ming in varchar2,
1520 pi_aab001 in number,
1521 po_fhz out varchar2,
1522 po_msg out varchar2) is
1523 v_name varchar2(200);
1524 v_ac01_row ac01%rowtype;
1525 v_fhz varchar2(200);
1526 v_msg varchar2(200);
1527 v_procName varchar2(200);
1528 v_params varchar2(400);
1529 procedure print_name(pi_para1 in varchar2,
1530 pi_para2 in varchar2,
1531 po_name out varchar2) is
1532 begin
1533 po_name := pi_para1 || pi_para2;
1534 exception
1535 when others then
1536 po_name := '没有成功拼接两个入参';
1537 end print_name;
1538
1539 procedure getAc01(pi_aab001 in number,
1540 po_ac01 out ac01%rowtype,
1541 po_fhz out varchar2,
1542 po_msg out varchar2) is
1543 v_procName varchar2(200);
1544 v_params varchar2(400);
1545 v_sqlcode varchar2(500);
1546 v_sqlerrm varchar2(500);
1547 begin
1548 --初始化
1549 v_procName := 'pkg_weiyl.getAc01';
1550 v_params := 'pi_aab001=' || pi_aab001;
1551 -- 这里是因为模拟 rowtype数据类型的情况，所以条件中加了 rownum=1 ,以确保select into 的时候只有一条数据 ，
1552 select a.*
1553 into po_ac01
1554 from ac01 a
1555 where a.aab001 = pi_aab001
1556 and rownum = 1;
1557 exception
1558 when others then
1559 v_sqlcode := sqlcode;
1560 v_sqlerrm := sqlerrm;
1561 po_fhz := v_procName || '_001';
1562 /* PKG_FUN.F_ERRMSG(V_PROCNAME, SQLCODE, SQLERRM, V_PARAMS);*/
1563 po_msg := pkg_fun.F_ERRMSG(v_procName, sqlcode, sqlerrm, v_params);
1564 pkg_weiyltools.get_e_msg(sqlerrm,
1565 'po_ac01',
1566 'AAB001',
1567 pi_aab001,
1568 ' where 条件',
1569 po_fhz,
1570 po_msg);
1571 return;
1572 end getAc01;
1573
1574 begin
1575 --初始化 返回值
1576
1577 po_fhz := '-1';
1578 po_msg := '失败';
1579 --初始化
1580 v_procName := 'pkg_weiyl.testNestedPro';
1581 v_params := 'pi_aab001=' || pi_aab001;
1582 print_name(pi_xing, pi_ming, v_name);
1583 if (length(v_name) > 100) then
1584 po_msg := '拼接的字符串太长！';
1585 else
1586 po_msg := '成功拼接,拼接好的字符串为：' || v_name;
1587 end if;
1588 begin
1589 /*select aac002 into v_test from ac01 where aac001 = 1234;*/
1590 -- 20160801 注释
1591 /*update ac01
1592 set aac001 = 1000035129
1593 where aac002 = '511011198604126824';*/
1594 begin
1595 getAc01(pi_aab001, v_ac01_row, v_fhz, v_msg);
1596 if v_fhz != '1' then
1597 po_fhz := po_fhz || v_fhz;
1598 po_msg := po_msg || v_msg;
1599 return;
1600 else
1601 insert into ac01_temp
1602 values
1603 (v_ac01_row.BAE001,
1604 v_ac01_row.AAB001,
1605 v_ac01_row.AAC001,
1606 v_ac01_row.CAC002,
1607 v_ac01_row.AAC058,
1608 v_ac01_row.AAC002,
1609 v_ac01_row.AAC003,
1610 v_ac01_row.AAC004,
1611 v_ac01_row.AAC006,
1612 v_ac01_row.AAC007,
1613 v_ac01_row.CAC089,
1614 v_ac01_row.CAC090,
1615 v_ac01_row.AAC027,
1616 v_ac01_row.CAC005,
1617 v_ac01_row.AAC009,
1618 v_ac01_row.AAC013,
1619 v_ac01_row.AAE473,
1620 v_ac01_row.AAC020,
1621 v_ac01_row.CAC007,
1622 v_ac01_row.CAC008,
1623 v_ac01_row.CAC009,
1624 v_ac01_row.AAE200,
1625 v_ac01_row.CAC010,
1626 v_ac01_row.CAC011,
1627 v_ac01_row.CAC012,
1628 v_ac01_row.AAZ099,
1629 v_ac01_row.CAC121,
1630 v_ac01_row.CAE383,
1631 v_ac01_row.CAC161,
1632 v_ac01_row.AAE341,
1633 v_ac01_row.CAC201,
1634 v_ac01_row.CAC202,
1635 v_ac01_row.AAC015,
1636 v_ac01_row.AAC016,
1637 v_ac01_row.AAE013,
1638 v_ac01_row.CIE500,
1639 v_ac01_row.CIE501,
1640 v_ac01_row.AAC109,
1641 v_ac01_row.CIE503,
1642 v_ac01_row.CAC542,
1643 v_ac01_row.CAC546,
1644 v_ac01_row.CAC547,
1645 v_ac01_row.CAC549,
1646 v_ac01_row.AAB401,
1647 v_ac01_row.CKC695,
1648 v_ac01_row.CKE814,
1649 v_ac01_row.CAC560,
1650 v_ac01_row.CAC563,
1651 v_ac01_row.CAC564,
1652 v_ac01_row.JB_AAE200,
1653 v_ac01_row.CAC561_DISUSE);
1654 end if;
1655 exception
1656 when others then
1657 po_fhz := v_procName || '_02';
1658 po_msg := pkg_fun.F_ERRMSG(v_procName, sqlcode, sqlerrm, v_params);
1659 return;
1660 end;
1661 exception
1662 when others then
1663 pkg_weiyltools.get_e_msg(sqlerrm,
1664 'AC01',
1665 'aac001',
1666 '1000035129',
1667 ' where aac002 = 511011198604126824',
1668 po_fhz,
1669 po_msg);
1670 return;
1671 end;
1672
1673 exception
1674 when others then
1675 po_fhz := '-1';
1676 po_msg := '失败';
1677 end testNestedPro;
1678 /*触摸屏查询标记*/
1679 procedure cancelCmp(PI_SERIALNUM in varchar2,
1680 po_fhz out varchar2,
1681 po_msg out varchar2) is
1682 v_Prc varchar2(40);
1683 v_Prcname varchar2(50);
1684 v_Params varchar2(400);
1685 v_Sqlerrm varchar2(100);
1686 begin
1687 --初始化
1688 -- 初始化返回值
1689 Po_Fhz := '1';
1690 Po_Msg := '';
1691 v_Prc := '.updateIDCard';
1692 v_Prcname := c_Pkg_Name || v_Prc;
1693 v_Params := ',传入参数为:PI_SERIALNUM=' || PI_SERIALNUM;
1694 update printserialnum
1695 set bce326 = '1'
1696 where SERIALNUM = PI_SERIALNUM
1697 and bce326 <> '1';
1698 exception
1699 when others then
1700 v_Sqlerrm := Substr(Sqlerrm, 1, 9);
1701 /* 捕获,无效数字*/
1702 If v_Sqlerrm = 'ORA-01722' Then
1703 Po_Fhz := v_Prcname || '_3';
1704 Po_Msg := '数据库报错,提示无效数字,可能的原因是：入参：' || PI_SERIALNUM ||
1705 '可能是数值型，应该为字符串类型';
1706 return;
1707 else
1708 po_fhz := v_Prcname || '_1';
1709 po_msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
1710 return;
1711 end if;
1712 end cancelCmp;
1713
1714 /*取消征集通知单*/
1715 procedure cancelAaz288(PI_OPERID in varchar2,
1716 PI_AAZ288 in varchar2,
1717 po_fhz out varchar2,
1718 po_msg out varchar2) is
1719 v_cnt_ab07 number(3);
1720 v_cnt_ad21 number(2);
1721 v_cnt_ac43 number(4);
1722 v_Prc varchar2(400);
1723 v_aae111 ad21.aae111%type;
1724 begin
1725 -- 初始化返回值
1726 Po_Fhz := '1';
1727 Po_Msg := '成功';
1728 v_Prc := '.cancelAaz288';
1729 /*校验*/
1730 select distinct (aae111)
1731 into v_aae111
1732 from V_BXGX_JJGL_ZZZJ_DWZSJHMX
1733 where aaz288 = PI_AAZ288;
1734 if v_aae111 <> '0' then
1735 Po_Fhz := '-11';
1736 Po_Msg := '征收计划的到账标识不为【未到账】';
1737 return;
1738 end if;
1739 /*更新ab07*/
1740 select count(1) into v_cnt_ab07 from ab07 where aaz288 = PI_AAZ288;
1741 if v_cnt_ab07 > 0 then
1742 update ab07 set aaz288 = null where aaz288 = PI_AAZ288;
1743 end if;
1744 /*更新ad21*/
1745 select count(1) into v_cnt_ad21 from ad21 where aaz288 = PI_AAZ288;
1746 if v_cnt_ad21 > 0 then
1747 update ad21
1748 set cae033 = 2,
1749 cae030 = PI_OPERID,
1750 cae031 = to_char(sysdate, 'yyyymmddhh24miss')
1751 where aaz288 = PI_AAZ288;
1752 end if;
1753 /*更新ab43*/
1754 select count(1) into v_cnt_ac43 from ac43 where aaz288 = PI_AAZ288;
1755 if v_cnt_ac43 > 0 then
1756 update ac43 set aaz288 = '' where aaz288 = PI_AAZ288;
1757 end if;
1758 exception
1759 when others then
1760 -- Sqlcode, Sqlerrm
1761 Po_Fhz := '-1';
1762 Po_Msg := '过程' || Pkg_Name || v_Prc || '报错,ac01表里没有找到该人员';
1763 end cancelAaz288;
1764 /*删除知识库核销的附件内容，否则数据里的不必要的附件会越来越多*/
1765 procedure deleteZskFile(PI_CAE232 in varchar2,
1766 po_fhz out varchar2,
1767 po_msg out varchar2) IS
1768 v_cae232_cnt number(4);
1769 v_Prc varchar2(100);
1770 v_Prcname varchar2(100);
1771 v_Params varchar2(400);
1772 begin
1773 --初始化
1774 Po_Fhz := '1';
1775 Po_Msg := '成功';
1776 v_Prc := '.updateIDCard';
1777 v_Prcname := c_Pkg_Name || v_Prc;
1778 v_Params := ',传入参数为:PI_CAE232=' || PI_CAE232;
1779 select count(1)
1780 into v_cae232_cnt
1781 from wyl_file a
1782 where a.cae232 = PI_CAE232;
1783 if v_cae232_cnt > 0 then
1784 delete from wyl_file where cae232 = PI_CAE232;
1785 end if;
1786 --异常
1787 exception
1788 when others then
1789 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
1790 Po_Fhz := '-1';
1791 end deleteZskFile;
1792 /*
1793 生成项目明细统计报表数据
1794 */
1795 procedure generatexmmx(pi_ksrq in varchar2,
1796 pi_zzrq in varchar2,
1797 po_fhz out varchar2,
1798 po_msg out varchar2) is
1799 v_count number(3);
1800 v_tongji fw_xmmx%rowtype;
1801 cursor cur_fw_xmmx is
1802 select *
1803 from fw_xmmx
1804 where aae008 >= pi_ksrq
1805 and aae008 <= pi_zzrq;
1806 cursor cur_jhnywc is
1807 --计划内完成验证的
1808 select a.bae010, count(1) num1
1809 from fw_xmmx a
1810 where a.aae008 >= pi_ksrq
1811 and a.aae008 <= pi_zzrq
1812 and a.aae022 = '01'
1813 and a.aae026 = '1'
1814 and cae011 = '04'
1815 group by a.bae010;
1816
1817 cursor cur_jhwywc is
1818 --计划外完成验证的
1819 select a.bae010, count(1) num1
1820 from fw_xmmx a
1821 where a.aae008 >= pi_ksrq
1822 and a.aae008 <= pi_zzrq
1823 and a.aae022 = '02'
1824 and a.aae026 = '1'
1825 and cae011 = '04'
1826 group by a.bae010;
1827
1828 cursor cur_jhnwwc is
1829 --计划内未完成验证的 3
1830 select a.bae010, count(1) num2
1831 from fw_xmmx a
1832 where a.aae008 >= pi_ksrq
1833 and a.aae008 <= pi_zzrq
1834 and a.aae022 = '01'
1835 and a.aae026 is null
1836 and cae011 <> '04'
1837 and cae011 <> '05'
1838 group by a.bae010;
1839
1840 cursor cur_jhwwwc is
1841 --计划外未完成验证的
1842 select a.bae010, count(1) num2
1843 from fw_xmmx a
1844 where a.aae008 >= pi_ksrq
1845 and a.aae008 <= pi_zzrq
1846 and a.aae022 = '02'
1847 and a.aae026 is null
1848 and cae011 <> '04'
1849 and cae011 <> '05'
1850 group by a.bae010;
1851
1852 cursor cur_jhnywc_gs is
1853 --计划内完成工时 4
1854 select a.bae010, sum(a.aae018) jhgs, sum(a.aae019) sjgs
1855 from fw_xmmx a
1856 where a.aae008 >= pi_ksrq
1857 and a.aae008 <= pi_zzrq
1858 and a.aae022 = '01'
1859 and a.aae026 = '1'
1860 and cae011 = '04'
1861 group by a.bae010;
1862
1863 cursor cur_jhwywc_gs is
1864 --计划外完成工时
1865 select a.bae010, sum(a.aae018) jhgs, sum(a.aae019) sjgs
1866 from fw_xmmx a
1867 where a.aae008 >= pi_ksrq
1868 and a.aae008 <= pi_zzrq
1869 and a.aae022 = '02'
1870 and a.aae026 = '1'
1871 and cae011 = '04'
1872 group by a.bae010;
1873
1874 cursor cur_jhnwwc_gs is
1875 --计划内未完成工时
1876 select a.bae010, sum(a.aae018) jhgs, sum(a.aae019) sjgs
1877 from fw_xmmx a
1878 where a.aae008 >= pi_ksrq
1879 and a.aae008 <= pi_zzrq
1880 and a.aae022 = '01'
1881 and a.aae026 is null
1882 and cae011 <> '04'
1883 and cae011 <> '05'
1884 group by a.bae010;
1885
1886 cursor cur_jhwwwc_gs is
1887 --计划外未完成工时
1888 select a.bae010, sum(a.aae018) jhgs, sum(a.aae019) sjgs
1889 from fw_xmmx a
1890 where a.aae008 >= pi_ksrq
1891 and a.aae008 <= pi_zzrq
1892 and a.aae022 = '02'
1893 and a.aae026 is null
1894 and cae011 <> '04'
1895 and cae011 <> '05'
1896 group by a.bae010;
1897
1898 begin
1899 po_fhz := '1';
1900 po_msg := '成功';
1901 delete from fw_xmmx_tongji;
1902 insert into fw_xmmx_tongji
1903 (bae010)
1904 select AAA102 from aa10 where aaa100 = 'BAE010';
1905 /*循环插入计划内 已经 完成验证的*/
1906 for v_cur in cur_jhnywc loop
1907 update fw_xmmx_tongji
1908 set jhn_ywc = v_cur.num1, ksrq = pi_ksrq, zzrq = pi_zzrq
1909 where bae010 = v_cur.bae010;
1910 null;
1911 end loop;
1912
1913 /*循环插入计划外 已经 完成验证的*/
1914 for v_cur_ in cur_jhwywc loop
1915 update fw_xmmx_tongji
1916 set jhw_ywc = v_cur_.num1, ksrq = pi_ksrq, zzrq = pi_zzrq
1917 where bae010 = v_cur_.bae010;
1918 null;
1919 end loop;
1920
1921 /*循环更新计划内 未 完成验证的*/
1922 for v_cur2 in cur_jhnwwc loop
1923 update fw_xmmx_tongji k
1924 set jhn_wwc = v_cur2.num2
1925 where k.bae010 = v_cur2.bae010;
1926 null;
1927 end loop;
1928
1929 /*循环更新计划外 未 完成验证的*/
1930 for v_cur2_ in cur_jhwwwc loop
1931 update fw_xmmx_tongji k
1932 set jhw_wwc = v_cur2_.num2
1933 where k.bae010 = v_cur2_.bae010;
1934 null;
1935 end loop;
1936
1937 /*计划内完成工时 3 */
1938 for v_cur3 in cur_jhnywc_gs loop
1939 /*update fw_xmmx_tongji k set jhn_ywcgs= v_cur3.jhgs,jhn_ where k.bae010 = v_cur2.bae010;*/
1940 update fw_xmmx_tongji k
1941 set jhn_ywcgs = v_cur3.jhgs, jhn_ywcgs_sj = v_cur3.sjgs
1942 where k.bae010 = v_cur3.bae010;
1943 null;
1944 end loop;
1945
1946 /*计划外完成工时 3 */
1947 for v_cur3_ in cur_jhwywc_gs loop
1948 /*update fw_xmmx_tongji k set jhn_ywcgs= v_cur3.jhgs,jhn_ where k.bae010 = v_cur2.bae010;*/
1949 update fw_xmmx_tongji k
1950 set jhw_ywcgs = v_cur3_.jhgs, jhw_ywcgs_sj = v_cur3_.sjgs
1951 where k.bae010 = v_cur3_.bae010;
1952 null;
1953 end loop;
1954
1955 /*计划内未完成工时 4*/
1956 for v_cur4 in cur_jhnwwc_gs loop
1957 update fw_xmmx_tongji k
1958 set jhn_ywcgs = v_cur4.jhgs, jhn_ywcgs_sj = v_cur4.sjgs
1959 where k.bae010 = v_cur4.bae010;
1960 null;
1961 end loop;
1962
1963 /*计划外未完成工时 4*/
1964 for v_cur4_ in cur_jhwwwc_gs loop
1965 update fw_xmmx_tongji k
1966 set jhw_ywcgs = v_cur4_.jhgs, jhw_ywcgs_sj = v_cur4_.sjgs
1967 where k.bae010 = v_cur4_.bae010;
1968 null;
1969 end loop;
1970
1971 null;
1972 end;
1973 /*
1974 修正sic86 的单位编号，以sic84.aab001为依据, 用于pkg_zhgl.Ylgrzh_Cxtj，以弥补重新统计过程的缺陷
1975 add by weiyongle 20160506
1976 */
1977 procedure rebuild_sic86(pi_aac001 in varchar2,
1978 po_fhz out varchar2,
1979 po_msg out varchar2) is
1980 v_cae121 sic84.aae002%type;
1981 v_cnt_sic86 number(2);
1982 v_cnt_ac20 number(2);
1983 v_aab001 sic86.aab001%type;
1984 v_aae041 ac20.aae041%type;
1985 v_ksny sic86.aae001%type;
1986 v_zzny sic86.aae001%type;
1987 cursor c_aae002_sic84 is
1988 select max(cae121) cae121, aab001
1989 from sic84
1990 where aac001 = pi_aac001
1991 group by substr(aae002, 1, 4), aab001
1992 order by cae121;
1993 begin
1994 --初始化参数
1995 po_fhz := '-1';
1996 po_msg := '成功';
1997 for v_cur in c_aae002_sic84 loop
1998 v_cae121 := v_cur.cae121;
1999 select count(1)
2000 into v_cnt_sic86
2001 from sic86
2002 where aac001 = pi_aac001
2003 and aae001 = substr(v_cae121, 1, 4);
2004 begin
2005 if v_cnt_sic86 != 1 then
2006 po_fhz := '-1';
2007 po_msg := '该人员' || pi_aac001 || '在 ' || substr(v_cae121, 1, 4) ||
2008 '年度有多条个人养老账户,请核查!';
2009 return;
2010 end if;
2011 /*update sic86
2012 set aab001 = v_cur.aab001
2013 where aac001 = pi_aac001
2014 and aae001 = substr(v_aae002, 1, 4);*/
2015 update sic86
2016 set aab001 =
2017 (select aab001
2018 from sic84
2019 where aac001 = pi_aac001
2020 /* 必须按照cae121来 判断，而不能用 aae002来判断 ，否则有些情况会出问题 */
2021 and cae121 = v_cur.cae121)
2022 where aac001 = pi_aac001
2023 and aae001 = substr(v_cae121, 1, 4);
2024 exception
2025 when others then
2026 po_fhz := '-2';
2027 po_msg := '更新人员' || pi_aac001 || ',' || substr(v_cae121, 1, 4) ||
2028 '年度的养老账户时发生系统错误!';
2029 return;
2030 end;
2031 end loop;
2032 /*select min(substr(aae041,1,4)) from ac20 where aac001 = 1000194637;-- ksny
2033 select min(substr(cae121,1,4))-1 from sic84 where aac001 = 1000194637; -- zzny */
2034 -- add by weiyongle 20160527
2035 begin
2036 select min(substr(aae041, 1, 4))
2037 into v_ksny
2038 from ac20
2039 where aac001 = pi_aac001;
2040 select min(substr(cae121, 1, 4))
2041 into v_zzny
2042 from sic84
2043 where aac001 = pi_aac001;
2044 /*
2045 如果ac20的最小年份小于sic84的最小年份，
2046 那么从ac20的最小年份到sic84最小年份的上一年开始循环
2047 */
2048 if v_ksny < v_zzny then
2049 v_zzny := v_zzny - 1;
2050 for i in v_ksny .. v_zzny loop
2051 begin
2052 select max(aae041)
2053 into v_aae041
2054 from ac20
2055 where aac001 = pi_aac001
2056 and substr(aae041, 1, 4) = i;
2057 select count(1)
2058 into v_cnt_ac20
2059 from ac20
2060 where aac001 = pi_aac001
2061 and aae041 = v_aae041;
2062 /*如果最小年份有多条开始月份相同的的ac20记录，那么报错*/
2063 if v_cnt_ac20 > 1 then
2064 po_fhz := '-2';
2065 po_msg := '改人员的养老历史账户在' || i || '年度有多条以' || v_aae041 ||
2066 '作为开始年月的记录,不符合常理，请先到历史维护模块进行数据修正';
2067 return;
2068 else
2069 select aab001
2070 into v_aab001
2071 from ac20 b
2072 where aac001 = pi_aac001
2073 and b.aae041 = v_aae041;
2074 end if;
2075 end;
2076
2077 /*update sic86
2078 set aab001 =
2079 (select aab001
2080 from ac20 b
2081 where aac001 = pi_aac001 and substr(b.aae041, 1, 4) = v_ksny)
2082 where aac001 = pi_aac001
2083 and aae001 = i;*/
2084 update sic86
2085 set aab001 = v_aab001
2086 where aac001 = pi_aac001
2087 and aae001 = i;
2088 end loop;
2089 end if;
2090 end;
2091
2092 end rebuild_sic86;
2093
2094 procedure rebuild_ab07(pi_aab001 in varchar2,
2095 pi_ksny in varchar2,
2096 pi_zzny in varchar2,
2097 pi_aae140 in varchar2,
2098 po_fhz out varchar2,
2099 po_msg out varchar2) is
2100
2101 cursor c_ac43 is
2102 select *
2103 from ac43
2104 where aab001 = pi_aab001
2105 and aaz288 is null;
2106 begin
2107
2108 /*for i in ksny .. zzny loop
2109 null;
2110 pkg_yuejie.dwzjjh_cxtj_aae140(pi_aab001,
2111 pi_aae002 => :pi_aae002,
2112 pi_aae140 => :pi_aae140,
2113 po_fhz => :po_fhz,
2114 po_msg => :po_msg);
2115 end loop;*/
2116 --初始化参数
2117 po_fhz := '1';
2118 po_msg := '成功';
2119 /*for v_cur in c_ac43 loop
2120 pkg_yuejie.dwzjjh_cxtj_aae140(pi_aab001,
2121 v_cur.aae002,
2122 '110',
2123 po_fhz,
2124 po_msg);
2125 end loop;*/
2126
2127 for i in pi_ksny .. pi_zzny loop
2128 pkg_yuejie.dwzjjh_cxtj_aae140(pi_aab001,
2129 i,
2130 pi_aae140,
2131 po_fhz,
2132 po_msg);
2133 end loop;
2134
2135 exception
2136 when others then
2137 po_fhz := '-1';
2138 po_msg := '失败';
2139 return;
2140 end rebuild_ab07;
2141
2142 /*Pkg_Ryhb_Pl_New
2143 --批量合并，初始数据生成
2144 Procedure Plhb_Start(Pi_Bae001 In Varchar2,
2145 Pi_Aab001 In Number,
2146 Pi_Jbr In Varchar2, --- 10000641
2147 Po_Fhz Out Varchar2,
2148 Po_Msg Out Varchar2)
2149 */
2150 procedure generate_plhb_data(pi_bae001 in varchar2,
2151 po_fhz out varchar2,
2152 po_msg out varchar2) is
2153 cursor cur_dwxx is
2154 select * from ae01 where bae001 = pi_bae001;
2155 begin
2156 -- 初始化
2157 po_msg := '成功';
2158 po_fhz := '1';
2159 for v_cur in cur_dwxx loop
2160 Pkg_Ryhb_Pl_New.Plhb_Start(pi_bae001,
2161 v_cur.aab001,
2162 '10000641',
2163 po_fhz,
2164 po_msg);
2165 end loop;
2166 exception
2167 when others then
2168 po_msg := '失败';
2169 po_fhz := '-1';
2170 end;
2171 /*查询知识库数据*/
2172 procedure queryZSK(PI_AAE906 in varchar2,
2173 PO_AAE202 out varchar2,
2174 PO_AAE008 out varchar2,
2175 po_fhz out varchar2,
2176 po_msg out varchar2) is
2177 v_cnt number(2);
2178 cursor c_zsk is
2179 select * from fw_zsk where aae906 = pi_aae906;
2180 begin
2181 -- 初始化参数
2182 po_fhz := '1';
2183 po_msg := '获取数据成功！';
2184 select count(1) into v_cnt from fw_zsk where aae906 = pi_aae906;
2185 if v_cnt = 1 then
2186 for v_zsk in c_zsk loop
2187 po_aae202 := v_zsk.aae202;
2188 po_aae008 := v_zsk.aae008;
2189 end loop;
2190 else
2191 po_fhz := '-1';
2192 po_msg := '_9,返回值过多';
2193 end if;
2194 end queryZSK;
2195 /*
2196 生成失地农民汇总数据
2197 by weiyongel 20160519
2198 */
2199 procedure generate_sdnmhzsj(PI_BAE001 in varchar2,
2200 po_fhz out varchar2,
2201 po_msg out varchar2) is
2202 v_aab383 ic51.aab383%type;
2203 v_aic452 ic51.aic452%type;
2204 v_aic453 ic51.aic453%type;
2205 v_rs ic5152_hzb.rs%type;
2206 v_cnt number(2);
2207 cursor c_Ic5152_Temp is
2208 select *
2209 from Ic5152_Temp
2210 where bae001 = pi_bae001
2211 and imp_flag = '1'
2212 and zfbtfs <> null;
2213 begin
2214 -- 初始化参数
2215 po_fhz := '1';
2216 po_msg := '数据汇总成功！';
2217 /*for v_c_Ic5152_Temp in c_Ic5152_Temp loop
2218
2219 null;
2220 end loop;*/
2221 begin
2222 select count(1) into v_cnt from ic5152_hzb where bae001 = pi_bae001;
2223 /*如果大于0,那么就先清除这个汇总表*/
2224 if v_cnt > 0 then
2225 delete from ic5152_hzb where bae001 = pi_bae001;
2226 end if;
2227 end;
2228 select sum(nvl(aab383, 0))
2229 into v_aab383
2230 from Ic5152_Temp
2231 where bae001 = pi_bae001
2232 and imp_flag = '1'
2233 and zfbtfs is not null;
2234 select count(distinct(aac002))
2235 into v_rs
2236 from Ic5152_Temp
2237 where bae001 = pi_bae001
2238 and imp_flag = '1'
2239 and zfbtfs is not null;
2240 select sum(nvl(aic452, 0))
2241 into v_aic452
2242 from Ic5152_Temp
2243 where bae001 = pi_bae001
2244 and imp_flag = '1'
2245 and zfbtfs is not null;
2246 select sum(nvl(aic453, 0))
2247 into v_aic453
2248 from Ic5152_Temp
2249 where bae001 = pi_bae001
2250 and imp_flag = '1'
2251 and zfbtfs is not null;
2252 insert into ic5152_hzb
2253 (bae001, rs, aab383, aic452, aic453)
2254 values
2255 (PI_BAE001, v_rs, v_aab383, v_aic452, v_aic453);
2256 -- 调用生成清理数据的过程
2257 generate_sdnmqlsj(PI_BAE001, po_fhz, po_msg);
2258 exception
2259 when others then
2260 po_fhz := '-1';
2261 po_msg := 'pkg_weiyl.generate_sdnmhzsj 数据汇总失败！';
2262 return;
2263 end generate_sdnmhzsj;
2264
2265 /*
2266 生成失地农民清理数据
2267 by weiyongel 20160519
2268 */
2269 procedure generate_sdnmqlsj(PI_BAE001 in varchar2,
2270 po_fhz out varchar2,
2271 po_msg out varchar2) is
2272 v_aab383 ic51.aab383%type;
2273 v_aic452 ic51.aic452%type;
2274 v_aic453 ic51.aic453%type;
2275 v_rs ic5152_qlb .rs%type;
2276 v_cnt number(2);
2277 cursor c_Ic5152_Temp is
2278 select sum(nvl(a.aab383, 0)) aab383,
2279 sum(nvl(a.aic453, 0)) aic453,
2280 sum(nvl(a.aic452, 0)) aic452,
2281 count(distinct(a.aac002)) rs,
2282 a.aae001 aae001
2283 from Ic5152_Temp a
2284 where bae001 = PI_BAE001
2285 and imp_flag = '1'
2286 and zfbtfs is not null
2287 group by aae001;
2288 begin
2289 -- 初始化参数
2290 po_fhz := '1';
2291 po_msg := '生成失地农民清理数据成功！';
2292 begin
2293 select count(1) into v_cnt from ic5152_qlb where bae001 = pi_bae001;
2294 /*如果大于0,那么就先删除这个汇总表*/
2295 if v_cnt > 0 then
2296 delete from ic5152_qlb where bae001 = pi_bae001;
2297 end if;
2298 end;
2299 for v_c_Ic5152_Temp in c_Ic5152_Temp loop
2300 /*插入清理数据表 ，用于报表数据提取 */
2301 insert into ic5152_qlb
2302 (bae001, aae001, rs, aab383, aic452, aic453)
2303 values
2304 (PI_BAE001,
2305 v_c_Ic5152_Temp.aae001,
2306 v_c_Ic5152_Temp.rs,
2307 v_c_Ic5152_Temp.Aab383,
2308 v_c_Ic5152_Temp.Aic452,
2309 v_c_Ic5152_Temp.Aic453);
2310 null;
2311 end loop;
2312
2313 exception
2314 when others then
2315 po_fhz := '-1';
2316 po_msg := 'pkg_weiyl.generate_sdnmhzsj 数据汇总失败！';
2317 return;
2318 end generate_sdnmqlsj;
2319
2320 /*生成失地农民数据清理后的变化字段，用于查询失地农民数据清理模块*/
2321 procedure generate_sdnmdatachange(PI_AAC001 in varchar2,
2322 PI_AAZ288 in varchar2,
2323 PO_FHZ out varchar2,
2324 PO_MSG out varchar2) is
2325 cursor c_ic51_bf is
2326 select *
2327 from ic51_bf
2328 where aac001 = pi_aac001
2329 and aaz288 = pi_aaz288;
2330 cursor c_ic51 is
2331 select *
2332 from ic51
2333 where aac001 = pi_aac001
2334 and aaz288 = pi_aaz288;
2335 v_cnt_bf number(2);
2336 v_aic443_tmp ic51_bf.aic443%type;
2337 begin
2338 /*--正常或暂停参保并且是老系统导入的可以修改个人缴费金额
2339 Update Ic51
2340 Set Aic443 = v_Ic5152_Temp.Aic443,
2341 Aic444 = v_Ic5152_Temp.Aic444,
2342 Aic020 = v_Ic5152_Temp.Aic020,
2343 Aab383 = v_Ic5152_Temp.Aab383,
2344 Aic446 = v_Ic5152_Temp.Aic446,
2345 Aic452 = v_Ic5152_Temp.Aic452,
2346 Aic448 = v_Ic5152_Temp.Aic448,
2347 Aic453 = v_Ic5152_Temp.Aic453,
2348 Aic021 = v_Ic5152_Temp.Aic021,
2349 Aaa041 = v_Ic5152_Temp.Aaa041,
2350 Aae002 = v_Ic5152_Temp.Aae002
2351 Where Aac001 = v_Aac001
2352 And Aaz288 = v_Ic5152_Temp.Aaz288
2353 And Bce094 = '1'
2354 And Aae016 = '1';*/
2355 -- 初始化参数
2356 po_fhz := '1';
2357 po_msg := '生成变更记录数据成功！';
2358 -- 1. 先做判断，如果已经生成了相应的变更记录，那么就先删除相应的变更记录表，然后重新生成最新的数据
2359 begin
2360 select count(1)
2361 into v_cnt_bf
2362 from ic5152_bg
2363 where aac001 = pi_aac001
2364 and aaz288 = pi_aaz288;
2365 if v_cnt_bf > 0 then
2366 delete from ic5152_bg
2367 where aaz288 = pi_aaz288
2368 and aac001 = pi_aac001;
2369 end if;
2370 end;
2371 -- 2. 生成最新的变更记录表
2372 for v_bf in c_ic51_bf loop
2373 begin
2374 -- 1 先插入aaz288,aac001,aae001,aae002等信息
2375 insert into ic5152_bg
2376 (aaz288, bae001, aac001, aae002, aae001)
2377 values
2378 (PI_AAZ288, v_bf.bae001, v_bf.aac001, v_bf.aae002, v_bf.aae001);
2379 for v_ic51 in c_ic51 loop
2380 -- a. 先直接更新变更记录表
2381 update ic5152_bg a
2382 set a.aic020 = v_ic51.aic020,
2383 a.aic020_ = v_bf.aic020,
2384 a.aic443 = v_ic51.aic443,
2385 a.aic443_ = v_bf.aic443,
2386 a.aic444 = v_ic51.aic444,
2387 a.aic444_ = v_bf.aic444,
2388 a.aab383 = v_ic51.aab383,
2389 a.aab383_ = v_bf.aab383,
2390 a.aic446 = v_ic51.aic446,
2391 a.aic446_ = v_bf.aic446,
2392 a.aic452 = v_ic51.aic452,
2393 a.aic452_ = v_bf.aic452,
2394 a.aic448 = v_ic51.aic448,
2395 a.aic448_ = v_bf.aic448,
2396 a.aic453 = v_ic51.aic453,
2397 a.aic453_ = v_bf.aic453,
2398 a.aic021 = v_ic51.aic021,
2399 a.aic021_ = v_bf.aic021,
2400 a.aaa041 = v_ic51.aaa041,
2401 a.aaa041_ = v_bf.aaa041
2402 where aaz288 = pi_aaz288;
2403
2404 if v_ic51.aic443 != v_bf.aic443 then
2405 update ic5152_bg
2406 set aic443 = v_ic51.aic443, aic443_ = v_bf.aic443
2407 where aaz288 = pi_aaz288;
2408 end if;
2409
2410 if v_ic51.aic444 != v_bf.aic444 then
2411 update ic5152_bg
2412 set aic444 = v_ic51.aic444, aic444_ = v_bf.aic444
2413 where aaz288 = pi_aaz288;
2414 end if;
2415
2416 if v_ic51.aab383 != v_bf.aab383 then
2417 update ic5152_bg
2418 set aab383 = v_ic51.aab383, aab383_ = v_bf.aab383
2419 where aaz288 = pi_aaz288;
2420 end if;
2421
2422 /*if v_ic51.aic443 != v_bf.aic443 then
2423 update ic5152_bg aic443,aic443_ set v_ic51.aic433,v_bf.aic443;
2424 end if;
2425
2426 if v_ic51.aic443 != v_bf.aic443 then
2427 update ic5152_bg aic443,aic443_ set v_ic51.aic433,v_bf.aic443;
2428 end if;
2429
2430 if v_ic51.aic443 != v_bf.aic443 then
2431 update ic5152_bg aic443,aic443_ set v_ic51.aic433,v_bf.aic443;
2432 end if;*/
2433 end loop;
2434 exception
2435 when others then
2436 po_fhz := '-11';
2437 po_msg := '生成变更记录数据失败！';
2438 end;
2439 end loop;
2440 exception
2441 when others then
2442 po_fhz := '-1';
2443 po_msg := '生成变更记录数据失败！';
2444 end generate_sdnmdatachange;
2445 /*检查ac35时间 ，增减员时用*/
2446 procedure checkAC35Tim(PI_AAC002 in varchar2,
2447 pi_aab999 in varchar2,
2448 Po_AAE042 OUT varchar2,
2449 PO_FHZ out varchar2,
2450 PO_MSG out varchar2) is
2451 v_cnt number(3);
2452 v_cnt2 number(12);
2453 v_cnt3 number(12);
2454 v_aac001 ac01.aac001%type;
2455 v_aae042 ac35.aae042%type;
2456 n_aab001 ae01.aab001%type;
2457 begin
2458 --初始化
2459 -- 初始化参数
2460 po_fhz := '1';
2461 po_msg := 'success';
2462 select count(1) into v_cnt from ac01 where aac002 = pi_aac002;
2463
2464 if v_cnt > 0 then
2465
2466 select aab001 into n_aab001 from ae01 where aab999 = pi_aab999;
2467
2468 select nvl(max(aae042), '111111')
2469 into Po_AAE042
2470 from ac35
2471 where aac001 in (select aac001 from ac01 where aac002 = pi_aac002)
2472 and aab001 = n_aab001;
2473
2474 else
2475 Po_AAE042 := '111111';
2476 end if;
2477 exception
2478 when others then
2479 po_fhz := '-1';
2480 po_msg := '获取ac35.aae042出错，success';
2481 end checkAC35Tim;
2482
2483 /* 重新统计ac43 aae002 ,20160526 */
2484 procedure cxtj_ac43(PI_AAB001 in varchar2,
2485 PO_FHZ out varchar2,
2486 PO_MSG out varchar2) is
2487 v_cnt number(5);
2488 v_cae122 ac43.cae122%type;
2489 v_aae002 ac43.aae002%type;
2490 cursor c_ac43 is
2491 select *
2492 from ac43
2493 where aab001 = pi_aab001
2494 and aaz288 is null;
2495 begin
2496 --初始化
2497 -- 初始化参数
2498 po_fhz := '1';
2499 po_msg := 'success';
2500 begin
2501 select count(1)
2502 into v_cnt
2503 from ac43
2504 where aab001 = pi_aab001
2505 and aaz288 is null;
2506 if v_cnt > 0 then
2507 for v_cur_ac43 in c_ac43 loop
2508 /*if v_cur_ac43.aae002 != v_cur_ac43.cae122 then
2509 update ac43
2510 set aae002 = v_cur_ac43.cae122
2511 where aab001 = pi_aab001
2512 and aaz288 is null
2513 and cae122 = v_cur_ac43.cae122;
2514 end if;*/
2515 update ac43
2516 set aae002 = v_cur_ac43.cae122
2517 where aab001 = pi_aab001
2518 and aaz288 is null
2519 and cae122 = v_cur_ac43.cae122;
2520 end loop;
2521 end if;
2522 end;
2523 end cxtj_ac43;
2524
2525 procedure getAAC027(PI_AAC002 in varchar2,
2526 PO_AAC027 OUT varchar2,
2527 PO_FHZ out varchar2,
2528 PO_MSG out varchar2) IS
2529 v_cnt number(1);
2530 v_aac027 ac01.aac027%type;
2531 begin
2532 --初始化
2533 -- 初始化参数
2534 po_fhz := '1';
2535 po_msg := 'success';
2536 select count(1) into v_cnt from ac01 where aac002 = pi_aac002;
2537 if v_cnt > 0 then
2538 select aac027 into v_aac027 from ac01 where aac002 = pi_aac002;
2539 PO_AAC027 := v_aac027;
2540 end if;
2541 exception
2542 when others then
2543 po_fhz := '-1';
2544 po_msg := 'error';
2545 end getAAC027;
2546
2547 /* for test ,20160530 */
2548 procedure myInsert(PI_AAC002 in varchar2,
2549 PO_FHZ out varchar2,
2550 PO_MSG out varchar2) is
2551 begin
2552 --初始化
2553 -- 初始化参数
2554 po_fhz := '1';
2555 po_msg := 'success';
2556 insert into xzl_one (aac002) values (pi_aac002);
2557 exception
2558 when others then
2559 po_fhz := '-1';
2560 po_msg := 'error';
2561 end myInsert;
2562 /*统计 社会保险参保情况查询 */
2563 procedure tongji_shbx_old(PI_AAC001 in varchar2,
2564 PO_FHZ out varchar2,
2565 PO_MSG out varchar2) is
2566 v_cnt number(2);
2567 v_cic818 T_BXGX_SHBXCBQKCXJG_WYL.Cic818%type;
2568 v_cic819 T_BXGX_SHBXCBQKCXJG_WYL.Cic819%type;
2569 v_aae042_ac20 ac20.aae042%type;
2570 v_aae041_ac20 ac20.aae041%type;
2571 v_aae041_sac14 sac14.aae041%type;
2572 v_aae042_sac14 sac14.aae042%type;
2573 v_yf number(2);
2574 v_Prc Varchar2(20);
2575 v_Prcname Varchar2(200);
2576 v_Params Varchar2(500);
2577 cursor cur_t is
2578 select count(1) cnt, aae001
2579 from T_BXGX_SHBXCBQKCXJG_WYL
2580 where aac001 = pi_aac001
2581 group by aae001
2582 order by aae001;
2583 begin
2584 -- 初始化返回值
2585 Po_Fhz := '1';
2586 Po_Msg := '';
2587 v_Prc := '.tongji_shbx';
2588 v_Prcname := c_Pkg_Name || v_Prc;
2589 v_Params := ',传入参数为:pi_aac001=' || Pi_Aac001;
2590 -- 自建表 T_BXGX_SHBXCBQKCXJG_WYL 1000745288
2591 /*
2592 从这里取值，可以直接复制到 表 T_BXGX_SHBXCBQKCXJG_WYL 中
2593 select bae001,
2594 aae001,
2595 aac001,
2596 cac002,
2597 aac003,
2598 aac004,
2599 aac002,
2600 csrq,
2601 cgrq,
2602 aae200,
2603 aab999,
2604 aab004,
2605 cic818,
2606 sum(cic819) cic819,
2607 aic058,
2608 aic074,
2609 aic072,
2610 aic075,
2611 aic076,
2612 aic077,
2613 aic078,
2614 aic079,
2615 aae087,
2616 jfsm,
2617 cbzt,
2618 ccjfrq,
2619 zmjfrq,
2620 dqjfgz
2621 from v_bxgx_shbxcbqkcxjg_xgy_
2622 where aac001 = 1000745288
2623 group by bae001,
2624 aae001,
2625 aac001,
2626 cac002,
2627 aac003,
2628 aac004,
2629 aac002,
2630 csrq,
2631 cgrq,
2632 aae200,
2633 aab999,
2634 aab004,
2635 cic818,
2636 aic058,
2637 aic074,
2638 aic072,
2639 aic075,
2640 aic076,
2641 aic077,
2642 aic078,
2643 aic079,
2644 aae087,
2645 jfsm,
2646 cbzt,
2647 ccjfrq,
2648 zmjfrq,
2649 dqjfgz order by aae001;*/
2650 -- 清空临时表
2651 delete from T_BXGX_SHBXCBQKCXJG_WYL where aac001 = pi_aac001;
2652 delete from T_BXGX_SHBXCBQKCXJG_WYL_ where aac001 = pi_aac001;
2653 /*插入数据*/
2654
2655 insert into T_BXGX_SHBXCBQKCXJG_WYL
2656 select bae001,
2657 aac001,
2658 cac002,
2659 aac003,
2660 aac004,
2661 aac002,
2662 csrq,
2663 cgrq,
2664 aae200,
2665 aab999,
2666 aab004,
2667 aae001,
2668 cic818,
2669 sum(nvl(cic819, 0)) cic819,
2670 aic058,
2671 aic074,
2672 aic072,
2673 aic075,
2674 aic076,
2675 aic077,
2676 aic078,
2677 aic079,
2678 aae087,
2679 jfsm,
2680 cbzt,
2681 ccjfrq,
2682 zmjfrq,
2683 dqjfgz
2684 from v_bxgx_shbxcbqkcxjg_xgy_
2685 where aac001 = pi_aac001
2686 group by bae001,
2687 aae001,
2688 aac001,
2689 cac002,
2690 aac003,
2691 aac004,
2692 aac002,
2693 csrq,
2694 cgrq,
2695 aae200,
2696 aab999,
2697 aab004,
2698 cic818,
2699 aic058,
2700 aic074,
2701 aic072,
2702 aic075,
2703 aic076,
2704 aic077,
2705 aic078,
2706 aic079,
2707 aae087,
2708 jfsm,
2709 cbzt,
2710 ccjfrq,
2711 zmjfrq,
2712 dqjfgz
2713 order by aae001;
2714 insert into T_BXGX_SHBXCBQKCXJG_WYL_
2715 select * from T_BXGX_SHBXCBQKCXJG_WYL where aac001 = pi_aac001;
2716 -- 判断是否有多条，有多条就算是有转入的
2717 -- select count(1),aae001 into v_cnt from T_BXGX_SHBXCBQKCXJG_WYL where aac001 = pi_aac001 group by aae001 order by aae001;
2718 for v_cur in cur_t loop
2719
2720 if v_cur.cnt > 1 then
2721 select sum(nvl(cic818, 0)) cic818_hj
2722 into v_cic818
2723 from T_BXGX_SHBXCBQKCXJG_WYL
2724 where aae001 = v_cur.aae001;
2725
2726 select sum(nvl(cic819, 0)) cic819_hj
2727 into v_cic819
2728 from T_BXGX_SHBXCBQKCXJG_WYL
2729 where aae001 = v_cur.aae001;
2730
2731 update T_BXGX_SHBXCBQKCXJG_WYL_ a
2732 set a.cic818 = v_cic818
2733 where aac001 = pi_aac001
2734 and aae001 = v_cur.aae001;
2735 -- 因为有两条，一条为转入，一条为本地，所以要 删除一条，然后更新
2736 delete from T_BXGX_SHBXCBQKCXJG_WYL_
2737 where aac001 = pi_aac001
2738 and aae001 = v_cur.aae001
2739 and aae087 = '1';
2740 update T_BXGX_SHBXCBQKCXJG_WYL_ a
2741 set a.cic819 = v_cic819
2742 where aac001 = pi_aac001
2743 and aae001 = v_cur.aae001;
2744 end if;
2745 -- 修正 转入的月份
2746 select aae042
2747 into v_aae042_ac20
2748 from ac20
2749 where aac001 = pi_aac001
2750 and aae140 = '110'
2751 and substr(aae041, 1, 4) = v_cur.aae001;
2752 select aae041
2753 into v_aae041_ac20
2754 from ac20
2755 where aac001 = pi_aac001
2756 and aae140 = '110'
2757 and substr(aae041, 1, 4) = v_cur.aae001;
2758
2759 select aae042
2760 into v_aae042_sac14
2761 from sac14
2762 where aac001 = pi_aac001
2763 and aae140 = '110'
2764 and substr(aae041, 1, 4) = v_cur.aae001;
2765 select aae041
2766 into v_aae041_sac14
2767 from sac14
2768 where aac001 = pi_aac001
2769 and aae140 = '110'
2770 and substr(aae041, 1, 4) = v_cur.aae001;
2771
2772 if v_aae042_ac20 <= v_aae041_sac14 or v_aae042_sac14 <= v_aae041_ac20 then
2773 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 + v_aae042_sac14 -
2774 v_aae041_sac14 + 1;
2775 -- 有重合的情况
2776 Elsif v_aae042_ac20 > v_aae041_sac14 and
2777 v_aae042_ac20 < v_aae042_sac14 and
2778 v_aae041_ac20 <= v_aae041_sac14 Then
2779 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 + v_aae042_sac14 -
2780 v_aae041_sac14 + 1 - (v_aae042_ac20 - v_aae041_sac14 + 1);
2781 Elsif v_aae042_ac20 > v_aae041_sac14 and
2782 v_aae042_ac20 < v_aae042_sac14 and
2783 v_aae041_ac20 >= v_aae041_sac14 Then
2784 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 + v_aae042_sac14 -
2785 v_aae041_sac14 + 1 - (v_aae042_ac20 - v_aae041_ac20 + 1);
2786 Elsif v_aae042_ac20 > v_aae041_sac14 and
2787 v_aae042_ac20 < v_aae042_sac14 and
2788 v_aae041_ac20 >= v_aae041_sac14 Then
2789 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 + v_aae042_sac14 -
2790 v_aae041_sac14 + 1 - (v_aae042_ac20 - v_aae041_ac20 + 1);
2791 Elsif v_aae042_ac20 > v_aae041_sac14 and
2792 v_aae042_ac20 >= v_aae042_sac14 and
2793 v_aae041_ac20 <= v_aae041_sac14 Then
2794 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1;
2795
2796 Elsif v_aae042_ac20 >= v_aae042_sac14 and
2797 v_aae041_ac20 > v_aae041_sac14 Then
2798 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 +
2799 (v_aae042_sac14 - v_aae041_sac14 + 1) -
2800 (v_aae041_ac20 - v_aae041_sac14 + 1);
2801 end if;
2802 update T_BXGX_SHBXCBQKCXJG_WYL_ a
2803 set a.cic818 = v_yf
2804 where aac001 = pi_aac001
2805 and aae001 = v_cur.aae001;
2806 end loop;
2807
2808 exception
2809 when others then
2810 Po_Fhz := v_Prcname || '_91,重新统计月份出错';
2811 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
2812
2813 end tongji_shbx_old;
2814
2815 /*
2816 20160801 修改
2817 统计 社会保险参保情况查询 ,对应于 眉山市的养老对账单打印 模块 ,卫永乐*/
2818 procedure tongji_shbx(PI_AAC001 in varchar2,
2819 PO_FHZ out varchar2,
2820 PO_MSG out varchar2) is
2821 v_cnt number(2);
2822 v_cnt_sd number(2);
2823 v_cic818 T_BXGX_SHBXCBQKCXJG_WYL.Cic818%type;
2824 v_cic818_sd T_BXGX_SHBXCBQKCXJG_WYL.Cic818%type;
2825 v_cic819 T_BXGX_SHBXCBQKCXJG_WYL.Cic819%type;
2826 v_aae042_ac20 ac20.aae042%type;
2827 v_aae041_ac20 ac20.aae041%type;
2828 v_aae041_sac14 sac14.aae041%type;
2829 v_aae042_sac14 sac14.aae042%type;
2830 v_aae180_sac14 sac14.aae180%type;
2831 v_yf number(2);
2832 v_Prc Varchar2(20);
2833 v_Prcname Varchar2(200);
2834 v_Params Varchar2(500);
2835 cursor cur_t is
2836 select count(1) cnt, aae001
2837 from T_BXGX_SHBXCBQKCXJG_WYL
2838 where aac001 = pi_aac001
2839 and aae087 in ('0', '1')
2840 group by aae001
2841 order by aae001;
2842 begin
2843 -- 初始化返回值
2844 Po_Fhz := '1';
2845 Po_Msg := '';
2846 v_Prc := '.tongji_shbx';
2847 v_Prcname := c_Pkg_Name || v_Prc;
2848 v_Params := ',传入参数为:pi_aac001=' || Pi_Aac001;
2849 -- 自建表 T_BXGX_SHBXCBQKCXJG_WYL 1000745288
2850 /*
2851 从这里取值，可以直接复制到 表 T_BXGX_SHBXCBQKCXJG_WYL 中
2852 select bae001,
2853 aae001,
2854 aac001,
2855 cac002,
2856 aac003,
2857 aac004,
2858 aac002,
2859 csrq,
2860 cgrq,
2861 aae200,
2862 aab999,
2863 aab004,
2864 cic818,
2865 sum(cic819) cic819,
2866 aic058,
2867 aic074,
2868 aic072,
2869 aic075,
2870 aic076,
2871 aic077,
2872 aic078,
2873 aic079,
2874 aae087,
2875 jfsm,
2876 cbzt,
2877 ccjfrq,
2878 zmjfrq,
2879 dqjfgz
2880 from v_bxgx_shbxcbqkcxjg_xgy_
2881 where aac001 = 1000745288
2882 group by bae001,
2883 aae001,
2884 aac001,
2885 cac002,
2886 aac003,
2887 aac004,
2888 aac002,
2889 csrq,
2890 cgrq,
2891 aae200,
2892 aab999,
2893 aab004,
2894 cic818,
2895 aic058,
2896 aic074,
2897 aic072,
2898 aic075,
2899 aic076,
2900 aic077,
2901 aic078,
2902 aic079,
2903 aae087,
2904 jfsm,
2905 cbzt,
2906 ccjfrq,
2907 zmjfrq,
2908 dqjfgz order by aae001;*/
2909 -- 清空临时表
2910 delete from T_BXGX_SHBXCBQKCXJG_WYL where aac001 = pi_aac001;
2911 delete from T_BXGX_SHBXCBQKCXJG_WYL_ where aac001 = pi_aac001;
2912 /*插入数据*/
2913
2914 insert into T_BXGX_SHBXCBQKCXJG_WYL
2915 select bae001,
2916 aac001,
2917 cac002,
2918 aac003,
2919 aac004,
2920 aac002,
2921 csrq,
2922 cgrq,
2923 aae200,
2924 aab999,
2925 aab004,
2926 aae001,
2927 cic818,
2928 sum(nvl(cic819, 0)) cic819,
2929 aic058,
2930 aic074,
2931 aic072,
2932 aic075,
2933 aic076,
2934 aic077,
2935 aic078,
2936 aic079,
2937 aae087,
2938 jfsm,
2939 cbzt,
2940 ccjfrq,
2941 zmjfrq,
2942 dqjfgz
2943 from v_bxgx_shbxcbqkcxjg_xgy_
2944 where aac001 = pi_aac001
2945 group by bae001,
2946 aae001,
2947 aac001,
2948 cac002,
2949 aac003,
2950 aac004,
2951 aac002,
2952 csrq,
2953 cgrq,
2954 aae200,
2955 aab999,
2956 aab004,
2957 cic818,
2958 aic058,
2959 aic074,
2960 aic072,
2961 aic075,
2962 aic076,
2963 aic077,
2964 aic078,
2965 aic079,
2966 aae087,
2967 jfsm,
2968 cbzt,
2969 ccjfrq,
2970 zmjfrq,
2971 dqjfgz
2972 order by aae001;
2973 insert into T_BXGX_SHBXCBQKCXJG_WYL_
2974 select * from T_BXGX_SHBXCBQKCXJG_WYL where aac001 = pi_aac001;
2975 -- 判断是否有多条，有多条就算是有转入的
2976 -- select count(1),aae001 into v_cnt from T_BXGX_SHBXCBQKCXJG_WYL where aac001 = pi_aac001 group by aae001 order by aae001;
2977 for v_cur in cur_t loop
2978
2979 if v_cur.cnt > 1 then
2980 select sum(nvl(cic818, 0)) cic818_hj
2981 into v_cic818
2982 from T_BXGX_SHBXCBQKCXJG_WYL
2983 -- altered by weiyongle
2984 where aae001 = v_cur.aae001
2985 and aac001 = pi_aac001;
2986
2987 select sum(nvl(cic819, 0)) cic819_hj
2988 into v_cic819
2989 from T_BXGX_SHBXCBQKCXJG_WYL
2990 -- altered by weiyongle 20160707
2991 where aae001 = v_cur.aae001
2992 and aac001 = pi_aac001;
2993
2994 update T_BXGX_SHBXCBQKCXJG_WYL_ a
2995 set a.cic818 = v_cic818
2996 where aac001 = pi_aac001
2997 and aae001 = v_cur.aae001;
2998 -- 因为有两条，一条为转入，一条为本地，所以要 删除一条，然后更新
2999 delete from T_BXGX_SHBXCBQKCXJG_WYL_
3000 where aac001 = pi_aac001
3001 and aae001 = v_cur.aae001
3002 and aae087 = '1';
3003 update T_BXGX_SHBXCBQKCXJG_WYL_ a
3004 set a.cic819 = v_cic819
3005 where aac001 = pi_aac001
3006 and aae001 = v_cur.aae001;
3007
3008 -- 修正 转入的月份
3009 select max(aae042)
3010 into v_aae042_ac20
3011 from ac20
3012 where aac001 = pi_aac001
3013 and aae140 = '110'
3014 and substr(aae041, 1, 4) = v_cur.aae001
3015 /*and aac402 = '0';*/
3016 and aac402 in ('0', '1');
3017 select min(aae041)
3018 into v_aae041_ac20
3019 from ac20
3020 where aac001 = pi_aac001
3021 and aae140 = '110'
3022 and substr(aae041, 1, 4) = v_cur.aae001
3023 /*and aac402 = '0';*/
3024 and aac402 in ('0', '1');
3025 select max(aae042)
3026 into v_aae042_sac14
3027 from sac14
3028 where aac001 = pi_aac001
3029 and aae140 = '110'
3030 and substr(aae041, 1, 4) = v_cur.aae001;
3031 select min(aae041)
3032 into v_aae041_sac14
3033 from sac14
3034 where aac001 = pi_aac001
3035 and aae140 = '110'
3036 and substr(aae041, 1, 4) = v_cur.aae001;
3037 -- 20160801 增加 sac14.aae180为空的 判断
3038 select nvl(aae180, 0)
3039 into v_aae180_sac14
3040 from sac14
3041 where aac001 = pi_aac001
3042 and aae140 = '110'
3043 and substr(aae041, 1, 4) = v_cur.aae001;
3044
3045 begin
3046 /* add by weiyongle 20160719
3047 初始化v_yf,循环的时候 ,如果不初始化 变量那么在下一次循环的时候变量
3048 还会保留上一次的值，导致在这一次的循环的数值不正确*/
3049 v_yf := 0;
3050 -- add by weiyongle 20160719 如果某一个变量为空，那么就不执行
3051 if v_aae042_ac20 = null or v_aae041_sac14 = null or
3052 v_aae042_ac20 = null or v_aae041_ac20 = null then
3053 return;
3054 else
3055 if v_aae042_ac20 <= v_aae041_sac14 or
3056 v_aae042_sac14 <= v_aae041_ac20 then
3057
3058 /*decode((v_aae042_sac14 - v_aae041_sac14 + 1) * nvl(a.aae180, 0),
3059 0,
3060 0,
3061 (v_aae042_sac14 - v_aae041_sac14 + 1))
3062 decode 函数在存储过程中不能使用
3063 pkg_weiyl.getMonthNum(v_aae041_sac14,v_aae042_sac14,v_aae180_sac14)
3064 */
3065
3066 /*v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 + v_aae042_sac14 -
3067 v_aae041_sac14 + 1;*/
3068 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 +
3069 pkg_weiyl.getMonthNum(v_aae041_sac14,
3070 v_aae042_sac14,
3071 v_aae180_sac14);
3072
3073 -- 有重合的情况
3074 Elsif v_aae042_ac20 > v_aae041_sac14 and
3075 v_aae042_ac20 < v_aae042_sac14 and
3076 v_aae041_ac20 <= v_aae041_sac14 Then
3077 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 +
3078 pkg_weiyl.getMonthNum(v_aae041_sac14,
3079 v_aae042_sac14,
3080 v_aae180_sac14) -
3081 (v_aae042_ac20 - v_aae041_sac14 + 1);
3082 Elsif v_aae042_ac20 > v_aae041_sac14 and
3083 v_aae042_ac20 < v_aae042_sac14 and
3084 v_aae041_ac20 >= v_aae041_sac14 Then
3085 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 +
3086 pkg_weiyl.getMonthNum(v_aae041_sac14,
3087 v_aae042_sac14,
3088 v_aae180_sac14) -
3089 (v_aae042_ac20 - v_aae041_ac20 + 1);
3090 Elsif v_aae042_ac20 > v_aae041_sac14 and
3091 v_aae042_ac20 < v_aae042_sac14 and
3092 v_aae041_ac20 >= v_aae041_sac14 Then
3093 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 +
3094 pkg_weiyl.getMonthNum(v_aae041_sac14,
3095 v_aae042_sac14,
3096 v_aae180_sac14) -
3097 (v_aae042_ac20 - v_aae041_ac20 + 1);
3098 Elsif v_aae042_ac20 > v_aae041_sac14 and
3099 v_aae042_ac20 >= v_aae042_sac14 and
3100 v_aae041_ac20 <= v_aae041_sac14 Then
3101 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1;
3102
3103 Elsif v_aae042_ac20 >= v_aae042_sac14 and
3104 v_aae041_ac20 > v_aae041_sac14 Then
3105 v_yf := v_aae042_ac20 - v_aae041_ac20 + 1 +
3106 (pkg_weiyl.getMonthNum(v_aae041_sac14,
3107 v_aae042_sac14,
3108 v_aae180_sac14)) -
3109 (v_aae041_ac20 - v_aae041_sac14 + 1);
3110 end if;
3111 end if;
3112 end;
3113
3114 -- 增加 失地农民账户类别的判断 add by weiyongle 20160728
3115 --先清空 变量
3116 begin
3117 v_cic818_sd := 0;
3118 v_cnt_sd := 0;
3119 select count(aac001)
3120 into v_cnt_sd
3121 from sic86
3122 where aac001 = pi_aac001
3123 and aae001 = v_cur.aae001
3124 and aae087 = '2';
3125 if v_cnt_sd > 0 then
3126 select cic818
3127 into v_cic818_sd
3128 from sic86
3129 where aac001 = pi_aac001
3130 and aae001 = v_cur.aae001
3131 and aae087 = '2';
3132 v_yf := v_yf + v_cic818_sd;
3133 end if;
3134 end;
3135
3136 -- 如果大于12，那么就设置为12
3137 if v_yf > 12 then
3138 v_yf := 12;
3139 end if;
3140 update T_BXGX_SHBXCBQKCXJG_WYL_ a
3141 set a.cic818 = v_yf
3142 where aac001 = pi_aac001
3143 and aae001 = v_cur.aae001;
3144 update T_BXGX_SHBXCBQKCXJG_WYL_
3145 set jfsm = cic818
3146 where aac001 = pi_aac001;
3147
3148 -- 针对 失地农民账户类别的判断 add by weiyongle 20160728
3149 begin
3150 v_cic818_sd := 0;
3151 v_cnt_sd := 0;
3152 select count(aac001)
3153 into v_cnt_sd
3154 from sic86
3155 where aac001 = pi_aac001
3156 and aae001 = v_cur.aae001
3157 and aae087 = '2';
3158 if v_cnt_sd > 0 then
3159 delete from T_BXGX_SHBXCBQKCXJG_WYL_ a
3160 where aac001 = pi_aac001
3161 and a.aae001 = v_cur.aae001
3162 and a.aae087 = '2';
3163 end if;
3164 end;
3165
3166 end if;
3167
3168 end loop;
3169
3170 exception
3171 when others then
3172 Po_Fhz := v_Prcname || '_91,重新统计月份出错';
3173 Po_Msg := Pkg_Fun.f_Errmsg(v_Prcname, Sqlcode, Sqlerrm, v_Params);
3174
3175 end tongji_shbx;
3176
3177 /*批量赋权限,我自己的权限放在表 fw_operator2right_wyl_ 中 */
3178 procedure prc_right(pi_loginid in varchar2,
3179 pi_loginid_other in varchar2,
3180 po_fhz out varchar2,
3181 po_msg out varchar2) is
3182 v_bae001 fw_operator.bae001%type;
3183 v_procname varchar2(200);
3184 v_operid fw_operator.operid%type;
3185 -- 所有权限
3186 /*cursor cur_fw_right is
3187 select * from fw_right;*/
3188 --赋予相同的权限
3189 cursor cur_fw_right is
3190 select *
3191 from fw_operator2right a
3192 where a.operid = (select operid
3193 from fw_operator k
3194 where k.loginid = pi_loginid_other);
3195 begin
3196 v_procname := 'pkg_weiyongle.prc_right';
3197 po_fhz := '1';
3198 po_msg := v_procname || '赋权成功！';
3199 select bae001, a.operid
3200 into v_bae001, v_operid
3201 from fw_operator a
3202 where a.loginid = pi_loginid;
3203 -- 删除
3204 delete from fw_operator2right a where a.operid = v_operid;
3205 for v_cur_right in cur_fw_right loop
3206 /*return;*/
3207 --增加
3208 insert into fw_operator2right
3209 (ID,
3210 operid,
3211 RIGHTID,
3212 AUTHTYPE,
3213 AAE100,
3214 VALIDFROM,
3215 VALIDTO,
3216 BAE002,
3217 BAE004)
3218 values
3219 (seq_fw_operator2right.nextval,
3220 v_operid,
3221 v_cur_right.rightid,
3222 '1',
3223 v_cur_right.aae100,
3224 20160522161615,
3225 20160712160146,
3226 '20004390',
3227 '20004390');
3228 end loop;
3229 EXCEPTION
3230 WHEN OTHERS THEN
3231 po_fhz := '_999';
3232 po_msg := '增加权限失败,sqlerrm：' || sqlerrm;
3233 return;
3234 end prc_right;
3235
3236 /* 测试goto 的用法,
3237
3238 */
3239 procedure test_loop_go(pi_aab001 in number,
3240 po_fhz out varchar2,
3241 po_msg out varchar2) is
3242 cursor cur_ac02 is
3243 select * from ac02 where aab001 = pi_aab001;
3244 v_ctk001 stk03.ctk001%type;
3245 v_cnt number(6);
3246 v_nnd number(4);
3247 begin
3248 /* SELECT count(c.nn),nn*5
3249 into v_ctk001,v_nnd
3250 FROM SKC04 A, AC02 B, v_ac01_groupbyNNd C
3251 WHERE A.AAC001 = B.AAC001
3252 AND B.AAC001 = C.AAC001
3253 AND B.AAE140 = '310'
3254 AND A.CKA549 = '1'
3255 \* AND substr(A.AAE036, 1, 6) = PI_AAE043*\
3256 AND A.AAE100 = '1'
3257 \*AND B.BAE001 = NVL(PI_BAE001, B.BAE001)*\
3258 group by c.nn; */
3259
3260 -- aab001 = 511500009511 ,
3261 -- aac001 = 1000687490 ,如果是 1000687490 这个人，那么就不更新
3262 -- aac001 = 1000687529 ,如果是 1000687529 这个人，那么就不更新
3263 set transaction name 'tran_1';
3264 for v_cur in cur_ac02 loop
3265
3266 if v_cur.aac001 = 1000687490 then
3267 goto the_next;
3268 elsif v_cur.aac008 = 1 then
3269 update ac02 a
3270 set a.aac008 = 6
3271 where aac001 = v_cur.aac001
3272 and aae140 = v_cur.aae140;
3273 end if;
3274
3275 <<the_next>>
3276 null;
3277 end loop;
3278 commit;
3279 end test_loop_go;
3280
3281 /*
3282 集合变量以及自定义异常的练习
3283 */
3284 procedure record_practice(pi_aac001 in number,
3285 po_fhz out varchar2,
3286 po_msg out varchar2) is
3287 v_cur_sac14 sac14%rowtype;
3288 -- 定义一个集合类型 方式 1
3289 type table_sac14 is table of sac14%rowtype index by pls_integer;
3290 --定义一个集合类型的变量
3291 sac14_tab table_sac14;
3292
3293 -- 定义一个集合类型 方式2
3294 type table_ac01_ae01 is record(
3295 aac001 ac01.aac001%type,
3296 aab001 ac01.aab001%type,
3297 aab999 ae01.aab999%type);
3298 --定义一个集合类型的变量
3299 ac01_ae01_tab table_ac01_ae01;
3300
3301 -- 自定义一个异常
3302 aae180_is_null_exp exception;
3303 --给自定义异常赋错误代码，必须 在 -20000到 -20999之间
3304 pragma exception_init(aae180_is_null_exp, -20001);
3305
3306 v_aae180 number(5);
3307 v_sqlerrm varchar2(200);
3308 v_proc varchar2(200) := c_Pkg_Name || '.record_practice';
3309 v_params varchar2(200) := 'pi_aac001=' || pi_aac001;
3310 v_cnt_sac14 number(3);
3311 -- 嵌套子过程
3312 procedure validate_aae180 is
3313 begin
3314 select count(1) into v_cnt_sac14 from sac14 where aac001 = pi_aac001;
3315 if v_cnt_sac14 = 0 then
3316 raise_application_error(-20002,
3317 'the person has not sac14,please confirm!');
3318 return;
3319 end if;
3320 select a.* bulk collect
3321 into sac14_tab
3322 from sac14 a
3323 where a.aac001 = pi_aac001;
3324 for v_index in sac14_tab.first .. sac14_tab.last loop
3325 -- 把集合类型变量赋值给一个 rowtype行类型变量
3326 v_cur_sac14 := sac14_tab(v_index);
3327 /*select nvl(v_cur_sac14.aae180, -99) into v_aae180 from dual;
3328 \*if nvl(v_cur_sac14.aae180, -99) = -99 then*\
3329 if v_aae180 = -99 then*/
3330 if v_cur_sac14.aae180 is null then
3331 raise aae180_is_null_exp;
3332 /* raise_application_error(-20001, 'aae180 can not be null');*/
3333 return;
3334 end if;
3335 end loop;
3336 exception
3337
3338 when others then
3339 po_fhz := '-98';
3340 po_msg := '未知错误98,sqlerrm:' || sqlerrm;
3341 -- 增加自定义过程的判断
3342 v_sqlerrm := substr(sqlerrm, 1, 9);
3343 if v_sqlerrm = 'ORA-20001' then
3344 po_fhz := '_001';
3345 po_msg := v_params || ',该人员' || v_cur_sac14.aae041 ||
3346 '的基数为空,请检查!,sqlerrm=' || sqlerrm;
3347 end if;
3348 return;
3349 end validate_aae180;
3350 begin
3351 -- 初始化返回值
3352 po_fhz := '-1';
3353 po_msg := 'the init state';
3354 --调用用于校验aae180的嵌套子过程 validate_aae180
3355 validate_aae180;
3356 exception
3357 when others then
3358 po_fhz := '-99';
3359 po_msg := '未知错误,sqlerrm:' || sqlerrm;
3360 return;
3361 end record_practice;
3362
3363 --- 统计部分
3364 /*
3365 1 统计全市2015年的住院情况,开始日期，终止日期，病种，就诊医院等
3366 select temp_func_WYL('BAE001', bae001) BAE001,
3367 a.aac003,
3368 a.aac002,
3369 b.ckc546,
3370 substr(b.ckc537, 1, 8) ks,
3371 substr(b.ckc538, 1, 8) zz,
3372 b.ckb519
3373 from ac01 a, kc21 b
3374 where a.aac001 = b.aac001
3375 and b.ckc544 = '2'
3376 and substr(b.ckc538, 1, 4) = 2015
3377 -- AND A.AAB001 = 511500012810
3378 group by a.bae001, AAC003, AAC002, CKC546, b.ckc537, b.ckc538, CKB519
3379 order by a.bae001;*/
3380
3381 Begin
3382 Null;
3383 End Pkg_Weiyl;
3384 /
|手机版|小黑屋|Java自学者论坛
( 声明：本站文章及资料整理自互联网，用于Java自学者交流学习使用，对资料版权不负任何法律责任，若有侵权请及时联系客服屏蔽删除 )
发表于 2021-8-30 15:03:06