存储过程之rowtype 使用
CREATE OR REPLACE PROCEDURE "DYLYLQX_SC_BA_1" (YWID IN VARCHAR2,
FLAG OUT VARCHAR2) IS
V_RAISE EXCEPTION; --异常类型
V_YWLX CHAR(1); --业务类型
V_BABH VARCHAR2(50); --备案编号
V_XXQBZJ VARCHAR2(32); --信息表主键
V_PZBID VARCHAR2(32); --备案凭证主键
V_GG_XKZ_YLYLQXSC GG_XKZ_YLYLQXSC%ROWTYPE; --凭证表主表
V_XZXK_YLYLQXSC_BA_SQ XZXK_YLYLQXSC_BA_SQ%ROWTYPE; --申请表
V_XZXK_ZLFZR XZXK_YLYLQXSC_BA_RY%ROWTYPE; --质量负责人
V_XZXK_SCFZR XZXK_YLYLQXSC_BA_RY%ROWTYPE; --生产负责人
V_XZXK_JSFZR XZXK_YLYLQXSC_BA_RY%ROWTYPE; --技术负责人
BGSX XZXK_YLYLQXSC_BA_BGSX%ROWTYPE; --声明类型变量
CURSOR BGSX_LIST IS
SELECT * FROM XZXK_YLYLQXSC_BA_BGSX T WHERE T.SQID = YWID; --声明游标 根据id查询变更事项list
--查询凭证产品子表
V_GG_SC_SCCP GG_XKZ_YLYLQXSC_SCCP%ROWTYPE;
CURSOR V_GG_SC_SCCPS IS
SELECT T.*
FROM GG_XKZ_YLYLQXSC_SCCP T
WHERE T.BAPZID IN
(SELECT X.PZBID FROM GG_XKZ_YLYLQXSC X WHERE X.SQID = YWID);
V_GG_SC_SCDZ GG_XKZ_YLYLQXSC_SCDZ%ROWTYPE;
CURSOR V_GG_SC_SCDZS IS
SELECT T.*
FROM GG_XKZ_YLYLQXSC_SCDZ T
WHERE T.BAPZID IN
(SELECT X.PZBID FROM GG_XKZ_YLYLQXSC X WHERE X.SQID = YWID);
V_GG_SC_SCFW GG_XKZ_YLYLQXSC_SCFW%ROWTYPE;
CURSOR V_GG_SC_SCFWS IS
SELECT T.*
FROM GG_XKZ_YLYLQXSC_SCFW T
WHERE T.BAPZID IN
(SELECT X.PZBID FROM GG_XKZ_YLYLQXSC X WHERE X.SQID = YWID);
BEGIN
--根据申请id查询业务类型
SELECT T.YWLX INTO V_YWLX FROM XZXK_YLYLQXSC_BA_SQ T WHERE T.SQID = YWID;
--根据申请id查询备案编号
SELECT T.BAH INTO V_BABH FROM GG_XKZ_YLYLQXSC T WHERE T.SQID = YWID;
--根据不同的业务类型做相应处理
--业务类型 A-备案 G-变更 Z-补证
IF (V_YWLX = 'A') THEN
--备案
--更新凭证表信息
UPDATE GG_XKZ_YLYLQXSC T
SET T.DYBS = '000',
T.GSBS = 'Y',
T.CZZT = 'V',
T.BAZT = 'V',
T.INSTNUM = F_GETSLBS,
T.LASTDATE = SYSDATE,
T.DATEXC = '11'
WHERE T.SQID = YWID;
--向结果表中插入数据从业务表
INSERT INTO XZXK_YLYLQXSC_XX_QB
(BZJ,
QYID,
QYMC,
YYZZZCH,
YYZZJZRQ,
ZZJGDM,
QYLX,
CLRQ,
ZCZB,
ZCDZ,
ZCDSZSF,
ZCDSZQX,
ZCDZYB,
ZCDZDH,
SCDZ,
SCDZYB,
SCDZDH,
SCFW,
LXR,
LXRSFZH,
LXDH,
CZ,
DZYX,
ZGZS,
SCGLRYS,
ZLGLRYS,
ZYJSRYS,
QYCSJZMJ,
SCMJ,
JHMJ,
JYMJ,
CCMJ,
JYJGZRS,
JYJGJSRYS,
BABH,
BADW,
BARQ,
INSTNUM,
LASTDATE,
DATEXC)
SELECT T.SQID,
T.QYID,
T.QYMC,
T.YYZZZCH,
T.YYZZJZRQ,
T.ZZJGDM,
T.QYLX,
T.CLRQ,
T.ZCZB,
T.ZCDZ,
T.ZCDSZSF,
T.ZCDSZQX,
T.ZCDZYB,
T.ZCDZDH,
T.SCDZ,
T.SCDZYB,
T.SCDZDH,
T.SCFW,
T.LXR,
T.LXRSFZH,
T.LXDH,
T.CZ,
T.DZYX,
T.ZGZS,
T.SCGLRYS,
T.ZLGLRYS,
T.ZYJSRYS,
T.QYCSJZMJ,
T.SCMJ,
T.JHMJ,
T.JYMJ,
T.CCMJ,
T.JYJGZRS,
T.JYJGJSRYS,
T.BABH,
T.BADW,
T.BARQ,
F_GETSLBS,
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_BA_SQ T
WHERE T.SQID = YWID;
-- 插入人员信息表
--先将人员列表清空
INSERT INTO PUB_TABLE_DATA_DELEXC
(ID,
TABLENAME,
TABLEPKNAME,
TABLEPK,
DELFLAG,
INSTNUM,
LASTDATE,
DATEXC)
SELECT SYS_GUID(),
'XZXK_YLYLQXSC_XX_RY',
'BZJ',
BZJ AS TABLEPK,
'N',
F_GETSLBS(),
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_XX_RY
WHERE XXQBZJ = YWID;
DELETE FROM XZXK_YLYLQXSC_XX_RY T WHERE T.XXQBZJ = YWID;
--插入人员信息从业务人员表到信息全表人员表
INSERT INTO XZXK_YLYLQXSC_XX_RY
(BZJ,
XXQBZJ,
RYLX,
XM,
ZJLX,
ZJHM,
ZC,
SXZY,
XL,
INSTNUM,
LASTDATE,
DATEXC)
SELECT T.BZJ,
T.SQID,
T.RYLX,
T.XM,
T.ZJLX,
T.ZJHM,
T.ZC,
T.SXZY,
T.XL,
F_GETSLBS,
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_BA_RY T
WHERE T.SQID = YWID;
--插入生产范围表
--先将生产范围表清空
INSERT INTO PUB_TABLE_DATA_DELEXC
(ID,
TABLENAME,
TABLEPKNAME,
TABLEPK,
DELFLAG,
INSTNUM,
LASTDATE,
DATEXC)
SELECT SYS_GUID(),
'XZXK_YLYLQXSC_XX_SCFW',
'BZJ',
BZJ AS TABLEPK,
'N',
F_GETSLBS(),
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_XX_SCFW
WHERE XXQBZJ = YWID;
DELETE FROM XZXK_YLYLQXSC_XX_SCFW T WHERE T.XXQBZJ = YWID;
--插入生产范围信息从业务表生产范围子表
INSERT INTO XZXK_YLYLQXSC_XX_SCFW
(BZJ, XXQBZJ, CPFL, CPLDH, CPLMC, INSTNUM, LASTDATE, DATEXC)
SELECT T.BZJ,
T.SQID,
T.CPFL,
T.CPLDH,
T.CPLMC,
F_GETSLBS,
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_BA_SCFW T
WHERE T.SQID = YWID;
--插入生产地址信息表
--先将生产地址信息清空
INSERT INTO PUB_TABLE_DATA_DELEXC
(ID,
TABLENAME,
TABLEPKNAME,
TABLEPK,
DELFLAG,
INSTNUM,
LASTDATE,
DATEXC)
SELECT SYS_GUID(),
'XZXK_YLYLQXSC_XX_SCDZ',
'BZJ',
BZJ AS TABLEPK,
'N',
F_GETSLBS(),
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_XX_SCDZ
WHERE XXQBZJ = YWID;
DELETE FROM XZXK_YLYLQXSC_XX_SCDZ T WHERE T.XXQBZJ = YWID;
--插入生产地址信息
INSERT INTO XZXK_YLYLQXSC_XX_SCDZ
(BZJ,
XXQBZJ,
JTDZ,
SZSF,
SZQX,
SZJD,
LXDH,
YB,
INSTNUM,
LASTDATE,
DATEXC)
SELECT T.BZJ,
T.SQID,
T.JTDZ,
T.SZSF,
T.SZQX,
T.SZJD,
T.LXDH,
T.YB,
F_GETSLBS,
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_BA_SCDZ T
WHERE T.SQID = YWID;
--通过凭证更新信息主表
--查询信息凭证表信息根据id
SELECT T.*
INTO V_GG_XKZ_YLYLQXSC
FROM GG_XKZ_YLYLQXSC T
WHERE T.SQID = YWID;
--更新信息主表
UPDATE XZXK_YLYLQXSC_XX_QB T
SET T.BABH = V_GG_XKZ_YLYLQXSC.BAH,
T.BADW = V_GG_XKZ_YLYLQXSC.BADW,
T.BARQ = V_GG_XKZ_YLYLQXSC.BARQ,
T.BAZT = V_GG_XKZ_YLYLQXSC.BAZT,
T.QYID = V_GG_XKZ_YLYLQXSC.QYID,
T.QYMC = V_GG_XKZ_YLYLQXSC.QYMC,
T.ZCDZ = V_GG_XKZ_YLYLQXSC.QYZCDZ,
T.SCDZ = V_GG_XKZ_YLYLQXSC.QYSCDZ,
T.SCFW = V_GG_XKZ_YLYLQXSC.SCFW,
T.INSTNUM = F_GETSLBS, --实例标识
T.LASTDATE = SYSDATE, --时间戳
T.DATEXC = '11' --数据交换位
WHERE T.BZJ = YWID;
--通过凭证更新变更后的生产范围，生产信息，生产地址 表信息
--修改人员信息表的法定代表人和企业负责人
--法定代表人
UPDATE XZXK_YLYLQXSC_XX_RY T
SET T.XM = V_GG_XKZ_YLYLQXSC.FDDBR,
T.INSTNUM = F_GETSLBS, --实例标识
T.LASTDATE = SYSDATE, --时间戳
T.DATEXC = '11' --数据交换位
WHERE T.RYLX = '0'
AND T.XXQBZJ = YWID;
--企业负责人
UPDATE XZXK_YLYLQXSC_XX_RY T
SET T.XM = V_GG_XKZ_YLYLQXSC.QYFZR,
T.INSTNUM = F_GETSLBS, --实例标识
T.LASTDATE = SYSDATE, --时间戳
T.DATEXC = '11' --数据交换位
WHERE T.RYLX = '1'
AND T.XXQBZJ = YWID;
INSERT INTO PUB_TABLE_DATA_DELEXC
(ID,
TABLENAME,
TABLEPKNAME,
TABLEPK,
DELFLAG,
INSTNUM,
LASTDATE,
DATEXC)
SELECT SYS_GUID(),
'XZXK_YLYLQXSC_XX_SCCP',
'BZJ',
BZJ AS TABLEPK,
'N',
F_GETSLBS(),
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_XX_SCCP
WHERE XXQBZJ = YWID;
DELETE FROM XZXK_YLYLQXSC_XX_SCCP T WHERE T.XXQBZJ = YWID; --生产产品
OPEN V_GG_SC_SCCPS;
LOOP
FETCH V_GG_SC_SCCPS
INTO V_GG_SC_SCCP;
EXIT WHEN V_GG_SC_SCCPS%NOTFOUND;
--插入生产产品
INSERT INTO XZXK_YLYLQXSC_XX_SCCP
(BZJ,
XXQBZJ,
BAH,
CPMC,
SFSTSC,
BARQ,
BZ,
INSTNUM,
LASTDATE,
DATEXC)
VALUES
(V_GG_SC_SCCP.BZJ,
YWID,
V_GG_SC_SCCP.BAH,
V_GG_SC_SCCP.CPMC,
V_GG_SC_SCCP.SFSTSC,
V_GG_SC_SCCP.BARQ,
V_GG_SC_SCCP.BZ,
F_GETSLBS,
SYSDATE,
'11');
END LOOP;
close V_GG_SC_SCCPS;
ELSIF (V_YWLX = 'G') THEN
--根据申请表 备案编号 将状态为有效地改为无效
UPDATE GG_XKZ_YLYLQXSC T
SET T.DYBS = '000', --打印标识
T.CZZT = 'I', --操作状态
T.GSBS = 'N', --公共标识
T.BAZT = 'I',
T.INSTNUM = F_GETSLBS, --实例标识
T.LASTDATE = SYSDATE, --时间戳
T.DATEXC = '11' --数据交换位
WHERE T.BAH = V_BABH
AND T.CZZT = 'V';
--将状态为临时的改为有效
UPDATE GG_XKZ_YLYLQXSC T
SET T.DYBS = '000', --打印标识
T.CZZT = 'V', --操作状态
T.GSBS = 'Y', --公共标识
T.BAZT = 'V',
T.INSTNUM = F_GETSLBS, --实例标识
T.LASTDATE = SYSDATE, --时间戳
T.DATEXC = '11' --数据交换位
WHERE T.SQID = YWID
AND T.CZZT = 'T';
--变更
--根据备案编号 查询信息表主键
SELECT BZJ
INTO V_XXQBZJ
FROM XZXK_YLYLQXSC_XX_QB T
WHERE T.BABH = V_BABH and rownum < 2;
-- 查询申请表信息
SELECT *
INTO V_XZXK_YLYLQXSC_BA_SQ
FROM XZXK_YLYLQXSC_BA_SQ
WHERE SQID = YWID and rownum < 2;
--根据备案编号查询备案凭证id
SELECT PZBID INTO V_PZBID FROM GG_XKZ_YLYLQXSC WHERE SQID = YWID and rownum < 2;
-- 查询凭证表信息
SELECT * INTO V_GG_XKZ_YLYLQXSC FROM GG_XKZ_YLYLQXSC WHERE SQID = YWID and rownum < 2;
-- 查询质量负责人
SELECT *
INTO V_XZXK_ZLFZR
FROM XZXK_YLYLQXSC_BA_RY
WHERE SQID = YWID
AND RYLX = '2' and rownum < 2;
-- 查询产品负责人
SELECT *
INTO V_XZXK_SCFZR
FROM XZXK_YLYLQXSC_BA_RY
WHERE SQID = YWID
AND RYLX = '3' and rownum < 2;
-- 查询技术负责人
SELECT *
INTO V_XZXK_JSFZR
FROM XZXK_YLYLQXSC_BA_RY
WHERE SQID = YWID
AND RYLX = '5' and rownum < 2;
--信息表
UPDATE XZXK_YLYLQXSC_XX_QB T
SET T.QYMC = V_XZXK_YLYLQXSC_BA_SQ.QYMC, --企业名称
T.YYZZZCH = V_XZXK_YLYLQXSC_BA_SQ.YYZZZCH, --营业执照注册码
T.YYZZJZRQ = V_XZXK_YLYLQXSC_BA_SQ.YYZZJZRQ, --营业执照戒指日期
T.ZZJGDM = V_XZXK_YLYLQXSC_BA_SQ.ZZJGDM, --组织机构代码
T.QYLX = V_XZXK_YLYLQXSC_BA_SQ.QYLX, --企业类型
T.CLRQ = V_XZXK_YLYLQXSC_BA_SQ.CLRQ, --成立日期
T.ZCZB = V_XZXK_YLYLQXSC_BA_SQ.ZCZB, --注册资本
T.ZCDZ = V_XZXK_YLYLQXSC_BA_SQ.ZCDZ, --注册地址
T.ZCDSZSF = V_XZXK_YLYLQXSC_BA_SQ.ZCDSZSF, --注册地所在省份
T.ZCDSZQX = V_XZXK_YLYLQXSC_BA_SQ.ZCDSZQX, --注册地所在区县
T.ZCDZYB = V_XZXK_YLYLQXSC_BA_SQ.ZCDZYB, --注册地址邮编
T.ZCDZDH = V_XZXK_YLYLQXSC_BA_SQ.ZCDZDH, --注册地址电话
T.SCFW = V_XZXK_YLYLQXSC_BA_SQ.SCFW, --生产范围
T.LXR = V_XZXK_YLYLQXSC_BA_SQ.LXR, --联系人
T.LXRSFZH = V_XZXK_YLYLQXSC_BA_SQ.LXRSFZH, --联系人身份证号
T.LXDH = V_XZXK_YLYLQXSC_BA_SQ.LXDH, --联系电话
T.CZ = V_XZXK_YLYLQXSC_BA_SQ.CZ, --传真
T.DZYX = V_XZXK_YLYLQXSC_BA_SQ.DZYX, --电子邮箱
T.ZGZS = V_XZXK_YLYLQXSC_BA_SQ.ZGZS, --职工总数
T.SCGLRYS = V_XZXK_YLYLQXSC_BA_SQ.SCGLRYS, --生产管理人员数
T.ZLGLRYS = V_XZXK_YLYLQXSC_BA_SQ.ZLGLRYS, --质量管理人员数
T.ZYJSRYS = V_XZXK_YLYLQXSC_BA_SQ.ZYJSRYS, --专业技术人员数
T.QYCSJZMJ = V_XZXK_YLYLQXSC_BA_SQ.QYCSJZMJ, --企业场所建筑面积
T.SCMJ = V_XZXK_YLYLQXSC_BA_SQ.SCMJ, --生产面积
T.JHMJ = V_XZXK_YLYLQXSC_BA_SQ.JHMJ, --净化面积
T.JYMJ = V_XZXK_YLYLQXSC_BA_SQ.JYMJ, --检验面积
T.CCMJ = V_XZXK_YLYLQXSC_BA_SQ.CCMJ, --仓储面积
T.JYJGZRS = V_XZXK_YLYLQXSC_BA_SQ.JYJGZRS, --检验机构总人数
T.JYJGJSRYS = V_XZXK_YLYLQXSC_BA_SQ.JYJGJSRYS, --检验机构技术人员数
T.BABH = V_XZXK_YLYLQXSC_BA_SQ.BABH, --备案编号
T.BADW = V_XZXK_YLYLQXSC_BA_SQ.BADW, --备案单位
T.BARQ = V_XZXK_YLYLQXSC_BA_SQ.BARQ, --备案日期
T.INSTNUM = F_GETSLBS,
T.LASTDATE = SYSDATE,
T.DATEXC = '11'
WHERE T.BZJ = V_XXQBZJ;
--做三个人员的更新
--质量负责人
UPDATE XZXK_YLYLQXSC_XX_RY T
SET T.XM = V_XZXK_ZLFZR.XM,
T.ZJLX = V_XZXK_ZLFZR.ZJLX,
T.ZJHM = V_XZXK_ZLFZR.ZJHM,
T.ZC = V_XZXK_ZLFZR.ZC,
T.SXZY = V_XZXK_ZLFZR.SXZY,
T.XL = V_XZXK_ZLFZR.XL,
T.INSTNUM = F_GETSLBS,
T.LASTDATE = SYSDATE,
T.DATEXC = '11'
WHERE T.XXQBZJ = V_XXQBZJ
AND RYLX = '2';
--产品负责人
UPDATE XZXK_YLYLQXSC_XX_RY T
SET T.XM = V_XZXK_SCFZR.XM,
T.ZJLX = V_XZXK_SCFZR.ZJLX,
T.ZJHM = V_XZXK_SCFZR.ZJHM,
T.ZC = V_XZXK_SCFZR.ZC,
T.SXZY = V_XZXK_SCFZR.SXZY,
T.XL = V_XZXK_SCFZR.XL,
T.INSTNUM = F_GETSLBS,
T.LASTDATE = SYSDATE,
T.DATEXC = '11'
WHERE T.XXQBZJ = V_XXQBZJ
AND RYLX = '3';
--技术负责人
UPDATE XZXK_YLYLQXSC_XX_RY T
SET T.XM = V_XZXK_JSFZR.XM,
T.ZJLX = V_XZXK_JSFZR.ZJLX,
T.ZJHM = V_XZXK_JSFZR.ZJHM,
T.ZC = V_XZXK_JSFZR.ZC,
T.SXZY = V_XZXK_JSFZR.SXZY,
T.XL = V_XZXK_JSFZR.XL,
T.INSTNUM = F_GETSLBS,
T.LASTDATE = SYSDATE,
T.DATEXC = '11'
WHERE T.XXQBZJ = V_XXQBZJ
AND RYLX = '5';
-- 进行遍历游标判断
OPEN BGSX_LIST;
LOOP
FETCH BGSX_LIST
INTO BGSX;
EXIT WHEN BGSX_LIST%NOTFOUND;
IF UPPER(BGSX.SQBGSXDM) = 'FDDBR' THEN
--将人员结果表的法定代表人删掉
INSERT INTO PUB_TABLE_DATA_DELEXC
(ID,
TABLENAME,
TABLEPKNAME,
TABLEPK,
DELFLAG,
INSTNUM,
LASTDATE,
DATEXC)
SELECT SYS_GUID(),
'XZXK_YLYLQXSC_XX_RY',
'BZJ',
BZJ AS TABLEPK,
'N',
F_GETSLBS(),
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_XX_RY
WHERE RYLX = '0'
AND XXQBZJ = V_XXQBZJ;
DELETE FROM XZXK_YLYLQXSC_XX_RY T
WHERE T.RYLX = '0'
AND T.XXQBZJ = V_XXQBZJ;
--将修改后的法定代表人添加到人员表
INSERT INTO XZXK_YLYLQXSC_XX_RY
(BZJ,
XXQBZJ,
RYLX,
XM,
ZJLX,
ZJHM,
ZC,
SXZY,
XL,
INSTNUM,
LASTDATE,
DATEXC)
SELECT T.BZJ,
V_XXQBZJ,
T.RYLX,
T.XM,
T.ZJLX,
T.ZJHM,
T.ZC,
T.SXZY,
T.XL,
F_GETSLBS,
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_BA_RY T
WHERE T.SQID = YWID
AND T.BGQHBS = '2'
AND T.RYLX = '0';
ELSIF UPPER(BGSX.SQBGSXDM) = 'QYFZR' THEN
--将人员结果表的企业负责人删掉
INSERT INTO PUB_TABLE_DATA_DELEXC
(ID,
TABLENAME,
TABLEPKNAME,
TABLEPK,
DELFLAG,
INSTNUM,
LASTDATE,
DATEXC)
SELECT SYS_GUID(),
'XZXK_YLYLQXSC_XX_RY',
'BZJ',
BZJ AS TABLEPK,
'N',
F_GETSLBS(),
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_XX_RY
WHERE RYLX = '1'
AND XXQBZJ = V_XXQBZJ;
DELETE FROM XZXK_YLYLQXSC_XX_RY T
WHERE T.RYLX = '1'
AND T.XXQBZJ = V_XXQBZJ;
--将修改后的法定企业负责添加到人员表
INSERT INTO XZXK_YLYLQXSC_XX_RY
(BZJ,
XXQBZJ,
RYLX,
XM,
ZJLX,
ZJHM,
ZC,
SXZY,
XL,
INSTNUM,
LASTDATE,
DATEXC)
SELECT T.BZJ,
V_XXQBZJ,
T.RYLX,
T.XM,
T.ZJLX,
T.ZJHM,
T.ZC,
T.SXZY,
T.XL,
F_GETSLBS,
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_BA_RY T
WHERE T.SQID = YWID
AND T.BGQHBS = '2'
AND T.RYLX = '1';
ELSIF UPPER(BGSX.SQBGSXDM) = 'XZXK_YLYLQXSC_BA_SCFW' THEN
--将原来的生产范围表清空
INSERT INTO PUB_TABLE_DATA_DELEXC
(ID,
TABLENAME,
TABLEPKNAME,
TABLEPK,
DELFLAG,
INSTNUM,
LASTDATE,
DATEXC)
SELECT SYS_GUID(),
'XZXK_YLYLQXSC_XX_SCFW',
'BZJ',
BZJ AS TABLEPK,
'N',
F_GETSLBS(),
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_XX_SCFW
WHERE XXQBZJ = V_XXQBZJ;
DELETE FROM XZXK_YLYLQXSC_XX_SCFW T WHERE T.XXQBZJ = V_XXQBZJ;
--将变更后的生产范围更新
OPEN V_GG_SC_SCFWS;
LOOP
FETCH V_GG_SC_SCFWS
INTO V_GG_SC_SCFW;
EXIT WHEN V_GG_SC_SCFWS%NOTFOUND;
--插入生产产品
INSERT INTO XZXK_YLYLQXSC_XX_SCFW
(BZJ, XXQBZJ, CPFL, CPLDH, CPLMC, INSTNUM, LASTDATE, DATEXC)
VALUES
(V_GG_SC_SCFW.BZJ,
V_XXQBZJ,
V_GG_SC_SCFW.CPFL,
V_GG_SC_SCFW.CPLDH,
V_GG_SC_SCFW.CPLMC,
F_GETSLBS,
SYSDATE,
'11');
END LOOP;
close V_GG_SC_SCFWS;
ELSIF UPPER(BGSX.SQBGSXDM) = 'XZXK_YLYLQXSC_BA_SCDZ' THEN
-- 将原来生产地址表清空
INSERT INTO PUB_TABLE_DATA_DELEXC
(ID,
TABLENAME,
TABLEPKNAME,
TABLEPK,
DELFLAG,
INSTNUM,
LASTDATE,
DATEXC)
SELECT SYS_GUID(),
'XZXK_YLYLQXSC_XX_SCDZ',
'BZJ',
BZJ AS TABLEPK,
'N',
F_GETSLBS(),
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_XX_SCDZ
WHERE XXQBZJ = V_XXQBZJ;
DELETE FROM XZXK_YLYLQXSC_XX_SCDZ T WHERE T.XXQBZJ = V_XXQBZJ;
--将变更后的生产地址更新
OPEN V_GG_SC_SCDZS;
LOOP
FETCH V_GG_SC_SCDZS
INTO V_GG_SC_SCDZ;
EXIT WHEN V_GG_SC_SCDZS%NOTFOUND;
--插入生产地址
INSERT INTO XZXK_YLYLQXSC_XX_SCDZ
(BZJ,
XXQBZJ,
JTDZ,
SZSF,
SZQX,
SZJD,
LXDH,
YB,
INSTNUM,
LASTDATE,
DATEXC)
VALUES
(V_GG_SC_SCDZ.BZJ,
V_XXQBZJ,
V_GG_SC_SCDZ.JTDZ,
V_GG_SC_SCDZ.SZSF,
V_GG_SC_SCDZ.SZQX,
V_GG_SC_SCDZ.SZJD,
V_GG_SC_SCDZ.LXDH,
V_GG_SC_SCDZ.YB,
F_GETSLBS,
SYSDATE,
'11');
END LOOP;
close V_GG_SC_SCDZS;
ELSIF UPPER(BGSX.SQBGSXDM) = 'XZXK_YLYLQXSC_BA_SCCP' THEN
--将原来生产产品表清空
INSERT INTO PUB_TABLE_DATA_DELEXC
(ID,
TABLENAME,
TABLEPKNAME,
TABLEPK,
DELFLAG,
INSTNUM,
LASTDATE,
DATEXC)
SELECT SYS_GUID(),
'XZXK_YLYLQXSC_XX_SCCP',
'BZJ',
BZJ AS TABLEPK,
'N',
F_GETSLBS(),
SYSDATE,
'11'
FROM XZXK_YLYLQXSC_XX_SCCP
WHERE XXQBZJ = V_XXQBZJ;
DELETE FROM XZXK_YLYLQXSC_XX_SCCP T WHERE T.XXQBZJ = V_XXQBZJ;
--将变更后的生产产品表更新
OPEN V_GG_SC_SCCPS;
LOOP
FETCH V_GG_SC_SCCPS
INTO V_GG_SC_SCCP;
EXIT WHEN V_GG_SC_SCCPS%NOTFOUND;
--插入生产产品
INSERT INTO XZXK_YLYLQXSC_XX_SCCP
(BZJ,
XXQBZJ,
BAH,
CPMC,
SFSTSC,
BARQ,
BZ,
INSTNUM,
LASTDATE,
DATEXC)
VALUES
(V_GG_SC_SCCP.BZJ,
V_XXQBZJ,
V_GG_SC_SCCP.BAH,
V_GG_SC_SCCP.CPMC,
V_GG_SC_SCCP.SFSTSC,
V_GG_SC_SCCP.BARQ,
V_GG_SC_SCCP.BZ,
F_GETSLBS,
SYSDATE,
'11');
END LOOP;
close V_GG_SC_SCCPS;
ELSIF UPPER(BGSX.SQBGSXDM) = 'QYMC' THEN
--将结果表企业名称做修改
UPDATE XZXK_YLYLQXSC_XX_QB T
SET T.QYMC = BGSX.BGH,
T.INSTNUM = F_GETSLBS,
T.LASTDATE = SYSDATE,
T.DATEXC = '11'
WHERE T.BZJ = V_XXQBZJ;
ELSIF UPPER(BGSX.SQBGSXDM) = 'ZCDZ' THEN
--将结果表的注册地址做修改
UPDATE XZXK_YLYLQXSC_XX_QB T
SET T.ZCDZ = BGSX.BGH,
T.INSTNUM = F_GETSLBS,
T.LASTDATE = SYSDATE,
T.DATEXC = '11'
WHERE T.BZJ = V_XXQBZJ;
END IF;
END LOOP;
CLOSE BGSX_LIST;
--通过凭证更新信息主表
--查询信息凭证表信息根据id
SELECT T.*
INTO V_GG_XKZ_YLYLQXSC
FROM GG_XKZ_YLYLQXSC T
WHERE T.SQID = YWID and rownum < 2;
--更新信息主表
UPDATE XZXK_YLYLQXSC_XX_QB T
SET T.QYMC = V_GG_XKZ_YLYLQXSC.QYMC,
T.ZCDZ = V_GG_XKZ_YLYLQXSC.QYZCDZ,
T.BAZT = V_GG_XKZ_YLYLQXSC.BAZT,
T.SCDZ = V_GG_XKZ_YLYLQXSC.QYSCDZ,
T.SCFW = V_GG_XKZ_YLYLQXSC.SCFW,
T.BABH = V_GG_XKZ_YLYLQXSC.BAH, --备案编号
T.BADW = V_GG_XKZ_YLYLQXSC.BADW, --备案单位
T.BARQ = V_GG_XKZ_YLYLQXSC.BARQ, --备案日期
T.INSTNUM = F_GETSLBS,
T.LASTDATE = SYSDATE,
T.DATEXC = '11'
WHERE T.BZJ = V_XXQBZJ;
--修改人员信息表的法定代表人和企业负责人
--法定代表人
UPDATE XZXK_YLYLQXSC_XX_RY T
SET T.XM = V_GG_XKZ_YLYLQXSC.FDDBR,
T.INSTNUM = F_GETSLBS,
T.LASTDATE = SYSDATE,
T.DATEXC = '11'
WHERE T.RYLX = '0'
AND T.XXQBZJ = V_XXQBZJ;
--企业负责人
UPDATE XZXK_YLYLQXSC_XX_RY T
SET T.XM = V_GG_XKZ_YLYLQXSC.QYFZR,
T.INSTNUM = F_GETSLBS,
T.LASTDATE = SYSDATE,
T.DATEXC = '11'
WHERE T.RYLX = '1'
AND T.XXQBZJ = V_XXQBZJ;
ELSIF (V_YWLX = 'Z') THEN
--补办
--根据申请表 备案编号 将状态为有效地改为无效
UPDATE GG_XKZ_YLYLQXSC T
SET T.DYBS = '000', --打印标识
T.CZZT = 'I', --操作状态
T.GSBS = 'N', --公共标识
T.BAZT = 'I',
T.INSTNUM = F_GETSLBS, --实例标识
T.LASTDATE = SYSDATE, --时间戳
T.DATEXC = '11' --数据交换位
WHERE T.BAH = V_BABH
AND T.CZZT = 'V';
--将状态为临时的改为有效
UPDATE GG_XKZ_YLYLQXSC T
SET T.DYBS = '000', --打印标识
T.CZZT = 'V', --操作状态
T.GSBS = 'Y', --公共标识
T.BAZT = 'V',
T.INSTNUM = F_GETSLBS, --实例标识
T.LASTDATE = SYSDATE, --时间戳
T.DATEXC = '11' --数据交换位
WHERE T.SQID = YWID
AND T.CZZT = 'T';
END IF;
FLAG := 'Y';
COMMIT;
EXCEPTION
WHEN V_RAISE THEN
FLAG := 'N';
ROLLBACK;
WHEN NO_DATA_FOUND THEN
FLAG := 'N';
ROLLBACK;
END DYLYLQX_SC_BA_1;
