create or replace PROCEDURE JH_PRO_CHUANGKOUSHOULI1
AS
startdate date;--存放上次插入的是那条数据
maxdate date;--存放本次存放到那条数据
BEGIN
--获取上次插入记录
select viewdate into startdate from ABLINK WHERE viewname='CHUANGKOUSHOULI';
--获取此次要插入数据的流水号集合，插入到表中
INSERT INTO AB_TEMP(yxtywlsh) SELECT CASENO AS yxtywlsh FROM XZSP.T_CASEBASEINFO WHERE CASESTATUSID = 141 and "CASESTARTDATE"> startdate;
--插入窗口受理数据
insert into XZSP.T_CHUANGKOUSHOULI
    ("YWLSH",
    "SJBBH",
    "SPSXBH",
    "SPSXMC",
    "SPSXZXBH",
    "SPSXZXMC",
    "YXTYWLSH",
    "SPHJBM",
    "SFWZDXM",
    "FLGDGXD",
    "FSYWGXD",
    "SQDWHSQRMC",
    "SQDWJBRXM",
    "SQDWLXDH",
    "SQDWJBRSJ",
    "SQDWJBRYJ",
    "SLJGZZJGDM",
    "SLJGMC",
    "SLDWBLRXM",
    "SLDWBLRGH",
    "SLJTDD",
    "SLKSSJ",
    "SLJSSJ",
    "BJLX",
    "HZBH",
    "SLZLQD",
    "SLYF",
    "SLYJ",
    "TJFS",
    "BZ",
    "BYZDA",
    "BYZDB",
    "BYZDC",
    "BYZDD")
     SELECT
    '14011010000000X001'||'001'||"YXTYWLSH" AS "YWLSH",
     "SJBBH",
     case when "SPSXBH" is null then to_char(' ') else "SPSXBH" end AS "SPSXBH",
     case when "SPSXMC" is null then to_char(' ') else "SPSXMC" end AS "SPSXMC",
     "SPSXZXBH",
     case when "SPSXZXMC" is null then to_char(' ') else "SPSXZXMC" end AS "SPSXZXMC",
     case when "YXTYWLSH" is null then to_char(' ') else "YXTYWLSH" end AS "YXTYWLSH",
     "SPHJBM",
     "SFWZDXM",
     "FLGDGXD",
     case when "FSYWGXD" is null then to_char(' ') else "FSYWGXD" end AS "FSYWGXD",
     case when "SQDWHSQRMC" is null then to_char(' ') else "SQDWHSQRMC" end AS "SQDWHSQRMC",
     case when "SQDWJBRXM" is null then to_char(' ') else "SQDWJBRXM" end AS "SQDWJBRXM",
     "SQDWLXDH",
     "SQDWJBRSJ",
     "SQDWJBRYJ",
     case when "SLJGZZJGDM" is null then to_char(' ') else "SLJGZZJGDM" end AS "SLJGZZJGDM",
     case when "SLJGMC" is null then to_char(' ') else "SLJGMC" end AS "SLJGMC",
     case when "SLDWBLRXM" is null then to_char(' ') else "SLDWBLRXM" end AS "SLDWBLRXM",
     "SLDWBLRGH",
     "SLJTDD",
     case when "SLKSSJ" is null then to_date('2012-01-01','yyyy-MM-dd') else "SLKSSJ" end as "SLKSSJ",
     case when "SLJSSJ" is null then to_date('2012-01-01','yyyy-MM-dd') else "SLJSSJ" end as "SLJSSJ",
     case when "BJLX" is null then to_char(' ') else to_char("BJLX") end AS "BJLX",
     case when "HZBH" is null then to_char(' ') else "HZBH" end AS "HZBH",
     case when "SLZLQD" is null then to_char(' ') else "SLZLQD" end AS "SLZLQD",
     case when "SLYF" is null then to_char(' ') else to_char("SLYF") end AS "SLYF",
     case when "SLYJ" is null then to_char(' ') else "SLYJ" end AS "SLYJ",
     case when "TJFS" is null then to_char(' ') else to_char("TJFS") end AS "TJFS",
     "BZ",
     "BYZDA",
     "BYZDB",
     "BYZDC",
     "BYZDD"
     FROM XZSP.JH_VIEW_CHUANGKOUSHOULI WHERE YXTYWLSH IN ( SELECT * FROM  XZSP.AB_TEMP );
    
insert into XZSPJH.T_CHUANGKOUSHOULI@"SXTY"
    ("YWLSH",
    "SJBBH",
    "SPSXBH",
    "SPSXMC",
    "SPSXZXBH",
    "SPSXZXMC",
    "YXTYWLSH",
    "SPHJBM",
    "SFWZDXM",
    "FLGDGXD",
    "FSYWGXD",
    "SQDWHSQRMC",
    "SQDWJBRXM",
    "SQDWLXDH",
    "SQDWJBRSJ",
    "SQDWJBRYJ",
    "SLJGZZJGDM",
    "SLJGMC",
    "SLDWBLRXM",
    "SLDWBLRGH",
    "SLJTDD",
    "SLKSSJ",
    "SLJSSJ",
    "BJLX",
    "HZBH",
    "SLZLQD",
    "SLYF",
    "SLYJ",
    "TJFS",
    "BZ",
    "BYZDA",
    "BYZDB",
    "BYZDC",
    "BYZDD")
     SELECT
     "YWLSH",
     "SJBBH",
     "SPSXBH",
     "SPSXMC",
     "SPSXZXBH",
     "SPSXZXMC",
     "YXTYWLSH",
     "SPHJBM",
     "SFWZDXM",
     "FLGDGXD",
     "FSYWGXD",
     "SQDWHSQRMC",
     "SQDWJBRXM",
     "SQDWLXDH",
     "SQDWJBRSJ",
     "SQDWJBRYJ",
     "SLJGZZJGDM",
     "SLJGMC",
     "SLDWBLRXM",
     "SLDWBLRGH",
     "SLJTDD",
     "SLKSSJ",
     "SLJSSJ",
     "BJLX",
     "HZBH",
     "SLZLQD",
     "SLYF",
     "SLYJ",
     "TJFS",
     "BZ",
     "BYZDA",
     "BYZDB",
     "BYZDC",
     "BYZDD"
     FROM XZSP.T_CHUANGKOUSHOULI WHERE YXTYWLSH IN ( SELECT * FROM XZSP.AB_TEMP );
--依据上次任务“受理流水号集合”插入受理过程数据

insert into XZSP.T_SHENPIGUOCHENG
("YWLSH", "SPHJBM", "SJBBH", "SPSXBH", "SPSXZXBH", "YXTYWLSH", "SPRXM", "SPRZWDM", "SPRZWMC", "SPJG", "SPYJ", "HZBH", "SPSJ", "BZ", "BYZDA", "BYZDB", "BYZDC", "BYZDD")
SELECT
'14011010000000X001'||'001'||"YXTYWLSH" as "YWLSH",
"SPHJBM",
"SJBBH",
case when "SPSXBH" is null then to_char(' ') else "SPSXBH" end AS "SPSXBH",
"SPSXZXBH",
case when "YXTYWLSH" is null then to_char(' ') else "YXTYWLSH" end AS "YXTYWLSH",
case when "SPRXM" is null then to_char(' ') else "SPRXM" end AS "SPRXM",
case when "SPRZWDM" is null then to_char(' ') else "SPRZWDM" END AS "SPRZWDM",
case when "SPRZWMC" is null then to_char(' ') else "SPRZWMC" END AS "SPRZWMC",
case when "SPJG" is null then to_char(' ') else "SPJG" END AS "SPJG"  ,
case when "SPYJ" is null then to_char(' ') else "SPYJ" END AS "SPYJ",
"HZBH",
"SPSJ",
"BZ",
"BYZDA",
"BYZDB",
"BYZDC",
"BYZDD"
FROM XZSP.jh_view_shenpiguocheng_temp WHERE YXTYWLSH IN (  SELECT * FROM  XZSP.AB_TEMP);

insert into XZSPJH.T_SHENPIGUOCHENG@"SXTY"
("YWLSH", "SPHJBM", "SJBBH", "SPSXBH", "SPSXZXBH", "YXTYWLSH", "SPRXM", "SPRZWDM", "SPRZWMC", "SPJG", "SPYJ", "HZBH", "SPSJ", "BZ", "BYZDA", "BYZDB", "BYZDC", "BYZDD")
SELECT
"YWLSH",
"SPHJBM",
"SJBBH",
"SPSXBH",
"SPSXZXBH",
"YXTYWLSH",
"SPRXM",
"SPRZWDM",
"SPRZWMC",
"SPJG"  ,
"SPYJ",
"HZBH",
"SPSJ",
"BZ",
"BYZDA",
"BYZDB",
"BYZDC",
"BYZDD"
FROM XZSP.T_SHENPIGUOCHENG WHERE YXTYWLSH IN (  SELECT * FROM  XZSP.AB_TEMP);
--依据上次任务“受理流水号集合”插入办结数据
INSERT into XZSP.T_BANJIE
(
"YWLSH",
"SJBBH",
"SPSXBH",
"SPSXZXBH",
"YXTYWLSH",
"SPHJBM",
"BLHJBM",
"BJBMZZJGDM",
"BJBMMC",
"BJSJ",
"BJJG",
"BLJGMS",
"ZJMC",
"ZJBH",
"ZJYXQX",
"ZJSM",
"FZDW",
"YSFZE",
"SJSFZE",
"SFJMZE",
"SFQKMX",
"HZBH",
"BZ",
"BYZDA",
"BYZDB",
"BYZDC",
"BYZDD"
)
SELECT
"YWLSH",
to_number("SJBBH") as "SJBBH",------
case when "SPSXBH" is null then to_char('无') else "SPSXBH" end AS"SPSXBH",
case when "SPSXZXBH" is null then to_char('无') else "SPSXZXBH" end AS"SPSXZXBH",
case when "YXTYWLSH" is null then to_char('无') else "YXTYWLSH" end AS"YXTYWLSH",
to_number("SPHJBM") as "SPHJBM",--------
case when "BLHJBM" is null then 0 else to_number("BLHJBM") end as "BLHJBM",--------
case when "BJBMZZJGDM" is null then to_char('无') else "BJBMZZJGDM" end AS"BJBMZZJGDM",
case when "BJBMMC" is null then to_char('无') else "BJBMMC" end AS"BJBMMC",
case when "BJSJ" is null then sysdate else "BJSJ" end AS"BJSJ",--这里存在问题
case when "BJJG" is null then to_char('无') else to_char("BJJG") end AS "BJJG",
case when "BLJGMS" is null then to_char('无') else "BLJGMS" end AS"BLJGMS",
case when "ZJMC" is null then to_char('无') else "ZJMC" end AS"ZJMC",
case when "ZJBH" is null then to_char('无') else "ZJBH" end AS"ZJBH",
case when "ZJYXQX" is null then to_char('无') else "ZJYXQX" end AS"ZJYXQX",
"ZJSM",
"FZDW",
0 as "YSFZE",--------
0 as "SPSXBH", --------
0 as "SFJMZE",--------
"SFQKMX",
"HZBH",
"BZ",
"BYZDA",
"BYZDB",
"BYZDC",
"BYZDD"
FROM XZSP.JH_VIEW_BANJIE WHERE YXTYWLSH IN  (SELECT * FROM  XZSP.AB_TEMP);

INSERT into XZSPJH.T_BANJIE@"SXTY"
(
"YWLSH",
"SJBBH",
"SPSXBH",
"SPSXZXBH",
"YXTYWLSH",
"SPHJBM",
"BLHJBM",
"BJBMZZJGDM",
"BJBMMC",
"BJSJ",
"BJJG",
"BLJGMS",
"ZJMC",
"ZJBH",
"ZJYXQX",
"ZJSM",
"FZDW",
"YSFZE",
"SJSFZE",
"SFJMZE",
"SFQKMX",
"HZBH",
"BZ",
"BYZDA",
"BYZDB",
"BYZDC",
"BYZDD"
)
SELECT
"YWLSH",
1,------
"SPSXBH",
"SPSXZXBH",
"YXTYWLSH",
5,--------
0,--------
"BJBMZZJGDM",
"BJBMMC",
"BJSJ",
"BJJG",
"BLJGMS",
"ZJMC",
"ZJBH",
"ZJYXQX",
"ZJSM",
"FZDW",
0,--------
0, --------
0,--------
"SFQKMX",
"HZBH",
"BZ",
"BYZDA",
"BYZDB",
"BYZDC",
"BYZDD"
FROM XZSP.T_BANJIE WHERE YXTYWLSH IN  (SELECT * FROM  XZSP.AB_TEMP);
--依据上次任务“受理流水号集合”中插入固定资产数据
insert into XZSPJH.T_GDZCYGZXDYB@"SXTY"
("GDZCYWLSH","GDZCZXYWLSH")
SELECT
"GDZCYWLSH",
"GDZCZXYWLSH"
FROM JH_VIEW_GDZCYGZXDYB where substr(GDZCZXYWLSH,22) IN(SELECT * FROM  XZSP.AB_TEMP);
--更新这次的插入记录  
     select max(slkssj) into maxdate from jh_view_chuangkoushouli WHERE YXTYWLSH IN  (SELECT * FROM  XZSP.AB_TEMP);
     if maxdate  is not null then
        UPDATE  XZSP.ABLINK SET viewdate = maxdate WHERE viewname = 'CHUANGKOUSHOULI';
     end if;
DELETE FROM  XZSP.AB_TEMP;
COMMIT;

END JH_PRO_CHUANGKOUSHOULI1;