/* Formatted on 2020/12/08 17:46:58 (QP5 v5.326) */
DECLARE
CURSOR c_rmb_onhandnum_withnocode IS
SELECT *
FROM rmb_onhandnum
WHERE (nonhandnum > 0)
AND (NVL (dr, 0) = 0)
AND (vbatchcode IS NULL);
CURSOR c_rmb_onhandnum_withcode IS
SELECT *
FROM rmb_onhandnum
WHERE (nonhandnum > 0)
AND (NVL (dr, 0) = 0)
AND (vbatchcode IS NOT NULL);
TYPE productinfo IS RECORD
(
invclassname VARCHAR2 (240),
invcode VARCHAR2 (40),
invname VARCHAR2 (200),
measname VARCHAR2 (200),
num NUMBER (20, 8),
storname VARCHAR2 (200)
);
v_product productinfo;
v_product1 productinfo;
v_product2 productinfo;
v_product3 productinfo;
num INT := 0;
i INT := 0;
j INT := 0;
diffvalue INT := 0;
startDate CHAR (30) := '2020-12-31';
endDate CHAR (30) := '2022-12-31';
v_ic_onhandnum ic_onhandnum%ROWTYPE;
v_ic_onhandnum_pk_corp ic_onhandnum.pk_corp%TYPE;
v_ic_onhandnum_nonhandnum IC_ONHANDNUM.NONHANDNUM%TYPE;
v_rmb_onhandnum rmb_onhandnum%ROWTYPE;
v_rmb_onhandnum_nonhandnum rmb_onhandnum.nonhandnum%TYPE;
v_rmb_onhandnum_cinvmanid rmb_onhandnum.cinvmanid%TYPE;
v_rmb_onhandnum_cwhid rmb_onhandnum.cwhid%TYPE;
v_rmb_onhandnum_cinvbasid rmb_onhandnum.cinvbasid%TYPE;
v_rmb_onhandnum_vbatchcode rmb_onhandnum.vbatchcode%TYPE;
BEGIN
SELECT TO_CHAR (SYSDATE, 'yyyy-mm-dd hh:mm:ss') INTO endDate FROM DUAL;
DBMS_OUTPUT.put_line (
'以店存量提取数据验证现存量，检查开始时间--无批次号检验：'
|| endDate);
DBMS_OUTPUT.put_line ('===============================================');
DBMS_OUTPUT.put_line (
'===========以下查询无批次号现存/店存数据===========');
DBMS_OUTPUT.put_line ('===============================================');
IF NOT c_rmb_onhandnum_withnocode%ISOPEN
THEN
OPEN c_rmb_onhandnum_withnocode;
END IF;
NULL;
LOOP
FETCH c_rmb_onhandnum_withnocode INTO v_rmb_onhandnum;
EXIT WHEN c_rmb_onhandnum_withnocode%NOTFOUND;
v_rmb_onhandnum_nonhandnum := v_rmb_onhandnum.nonhandnum;
IF (v_rmb_onhandnum_nonhandnum > 0) --如果有店存量，就检查对应的现存是否有数据，而且数量是否一致！！！
THEN
v_rmb_onhandnum_cinvmanid := v_rmb_onhandnum.cinvmanid;
v_rmb_onhandnum_cwhid := v_rmb_onhandnum.cwhid;
v_rmb_onhandnum_cinvbasid := v_rmb_onhandnum.cinvbasid;
v_rmb_onhandnum_vbatchcode := v_rmb_onhandnum.vbatchcode;
IF (v_rmb_onhandnum_vbatchcode IS NULL)
THEN --如果批次号为空，检查有无对 应数量
SELECT COUNT (*)
INTO num
FROM ic_onhandnum
WHERE NVL (dr, 0) = 0
AND (nonhandnum <> 0)
AND (cinventoryid = v_rmb_onhandnum_cinvmanid)
AND (cwarehouseid = v_rmb_onhandnum_cwhid)
AND (Cinvbasid = v_rmb_onhandnum_cinvbasid)
AND (pk_corp <> '1001');
IF num <> 1
THEN
IF num <= 0
THEN
i := i + 1;
--此处，再次增加查询，打到店存商品的数量及信息显示！！！！
SELECT invclassname invclass,
invcode,
invname,
measname measure,
num,
storname
INTO v_product
FROM (SELECT cls.invclassname invclassname,
inv.invcode invcode,
inv.invname invname,
meas.measname measname,
oh.nonhandnum num,
bs.STORNAME storname
FROM rmb_onhandnum oh
LEFT OUTER JOIN dtjt_invcheck t
ON oh.conhandnumid =
t.conhandnumid
LEFT OUTER JOIN rmb_onhandfreeze f
ON oh.conhandnumid =
f.conhandnumid
LEFT OUTER JOIN bd_invbasdoc inv
ON oh.cinvbasid = inv.pk_invbasdoc
LEFT OUTER JOIN bd_invcl cls
ON inv.pk_invcl = cls.pk_invcl
LEFT OUTER JOIN bd_measdoc meas
ON inv.pk_measdoc =
meas.pk_measdoc
LEFT OUTER JOIN bd_invmandoc inm
ON inm.PK_INVBASDOC =
inv.PK_INVBASDOC
LEFT OUTER JOIN bd_stordoc bs
ON bs.PK_STORDOC = oh.CWHID
WHERE 1 = 1
AND NVL (oh.dr, 0) = 0
AND NVL (t.dr, 0) = 0
AND NVL (cls.dr, 0) = 0
AND NVL (inv.dr, 0) = 0
AND NVL (meas.dr, 0) = 0
AND NVL (inm.dr, 0) = 0
AND NVL (bs.dr, 0) = 0
AND inv.PK_INVBASDOC =
v_rmb_onhandnum_cinvbasid
AND inm.PK_INVMANDOC =
v_rmb_onhandnum_cinvmanid
AND oh.nonhandnum > 0
AND (oh.cinvmanid =
v_rmb_onhandnum_cinvmanid)
AND (oh.cwhid = v_rmb_onhandnum_cwhid)
AND (oh.cinvbasid =
v_rmb_onhandnum_cinvbasid));
DBMS_OUTPUT.put_line (
'无批次检查异常结果显示:'
|| i
|| ',店铺名:'
|| v_product.storname
|| ' ,商品名称:'
|| v_product.invname
|| ' ,INVCODE:'
|| v_product.invcode
|| ' ,店存数量:'
|| v_rmb_onhandnum_nonhandnum
|| ',注意：该商品在现存内查询不到对应信息,请确认！！！！！！');
ELSE
i := i + 1;
--此处，再次增加查询，打到店存商品的数量及信息显示！！！！
DBMS_OUTPUT.put_line (
'检查结果显示：NoVbatchocde:'
|| i
|| ',无批次号!!!!!!，店存数量为：'
|| v_rmb_onhandnum_nonhandnum
|| ',仓库:'
|| v_rmb_onhandnum_cwhid
|| ',存货基本档案： '
|| v_rmb_onhandnum_cinvbasid
|| ' ,存货管理ID： '
|| v_rmb_onhandnum_cinvmanid
|| '，在现存内查询到不是1条信息，无法判断，请检查两边数据是否一致！！！！！！');
END IF;
ELSE
SELECT *
INTO v_ic_onhandnum
FROM ic_onhandnum
WHERE NVL (dr, 0) = 0
AND (nonhandnum <> 0)
AND (cinventoryid = v_rmb_onhandnum_cinvmanid)
AND (cwarehouseid = v_rmb_onhandnum_cwhid)
AND (Cinvbasid = v_rmb_onhandnum_cinvbasid)
AND (pk_corp <> '1001');
v_ic_onhandnum_nonhandnum := v_ic_onhandnum.nonhandnum;
IF (v_ic_onhandnum_nonhandnum <>
v_rmb_onhandnum_nonhandnum)
THEN
diffvalue := diffvalue + 1;
--此处，再次增加查询，打到店存商品的数量及信息显示！！！！
SELECT invclassname invclass,
invcode,
invname,
measname measure,
num,
storname
INTO v_product1
FROM (SELECT cls.invclassname invclassname,
inv.invcode invcode,
inv.invname invname,
meas.measname measname,
oh.nonhandnum num,
bs.STORNAME storname
FROM rmb_onhandnum oh
LEFT OUTER JOIN dtjt_invcheck t
ON oh.conhandnumid =
t.conhandnumid
LEFT OUTER JOIN rmb_onhandfreeze f
ON oh.conhandnumid =
f.conhandnumid
LEFT OUTER JOIN bd_invbasdoc inv
ON oh.cinvbasid = inv.pk_invbasdoc
LEFT OUTER JOIN bd_invcl cls
ON inv.pk_invcl = cls.pk_invcl
LEFT OUTER JOIN bd_measdoc meas
ON inv.pk_measdoc =
meas.pk_measdoc
LEFT OUTER JOIN bd_invmandoc inm
ON inm.PK_INVBASDOC =
inv.PK_INVBASDOC
LEFT OUTER JOIN bd_stordoc bs
ON bs.PK_STORDOC = oh.CWHID
WHERE 1 = 1
AND NVL (oh.dr, 0) = 0
AND NVL (t.dr, 0) = 0
AND NVL (cls.dr, 0) = 0
AND NVL (inv.dr, 0) = 0
AND NVL (meas.dr, 0) = 0
AND NVL (inm.dr, 0) = 0
AND NVL (bs.dr, 0) = 0
AND inv.PK_INVBASDOC =
v_rmb_onhandnum_cinvbasid
AND inm.PK_INVMANDOC =
v_rmb_onhandnum_cinvmanid
AND oh.nonhandnum > 0
AND oh.nonhandnum =
v_rmb_onhandnum_nonhandnum
AND (oh.cinvmanid =
v_rmb_onhandnum_cinvmanid)
AND (oh.cwhid = v_rmb_onhandnum_cwhid)
AND (oh.cinvbasid =
v_rmb_onhandnum_cinvbasid));
DBMS_OUTPUT.put_line (
'无批次数量不一致检查结果No:'
|| diffvalue
|| ',店存量:'
|| v_ic_onhandnum_nonhandnum
|| ' ,现存量:'
|| v_rmb_onhandnum_nonhandnum
|| ' ,店铺名:'
|| v_product1.storname
|| ' ,商品名称:'
|| v_product1.invname
|| ' ,INVCODE:'
|| v_product1.invcode
|| ' ,店存/现存数量不一致，请检查！！！！！！');
END IF;
END IF;
ELSE --如果批次号不为空，
DBMS_OUTPUT.put_line (
'怎么搞的，会有批次号呢，检查SQL语句！！！');
END IF;
END IF;
END LOOP;
IF c_rmb_onhandnum_withnocode%ISOPEN
THEN
CLOSE c_rmb_onhandnum_withnocode;
END IF;
num := i + diffvalue;
IF (num < 1)
THEN
DBMS_OUTPUT.put_line (
'以店存量验证无批次号产品正常！');
ELSE
DBMS_OUTPUT.put_line (
'以店存量验证无批次号产品完毕，错误数量：'
|| num);
END IF;
i := 0;
j := 0;
diffvalue := 0;
DBMS_OUTPUT.put_line ('===============================================');
DBMS_OUTPUT.put_line (
'============以下查询有批次号现存/店存数据==========');
DBMS_OUTPUT.put_line ('===============================================');
IF NOT c_rmb_onhandnum_withcode%ISOPEN
THEN
OPEN c_rmb_onhandnum_withcode;
END IF;
NULL;
LOOP
FETCH c_rmb_onhandnum_withcode INTO v_rmb_onhandnum;
EXIT WHEN c_rmb_onhandnum_withcode%NOTFOUND;
v_rmb_onhandnum_nonhandnum := v_rmb_onhandnum.nonhandnum;
IF (v_rmb_onhandnum_nonhandnum > 0) --如果有店存量，就检查对应的现存是否有数据，而且数量是否一致！！！
THEN
v_rmb_onhandnum_cinvmanid := v_rmb_onhandnum.cinvmanid;
v_rmb_onhandnum_cwhid := v_rmb_onhandnum.cwhid;
v_rmb_onhandnum_cinvbasid := v_rmb_onhandnum.cinvbasid;
v_rmb_onhandnum_vbatchcode := v_rmb_onhandnum.vbatchcode;
IF (v_rmb_onhandnum_vbatchcode IS NULL)
THEN --如果批次号为空，报怎么回事？？？
DBMS_OUTPUT.put_line (
'============怎么回事，批次号为空呢？？？？？？==========');
/* SELECT COUNT (*)
INTO num
FROM ic_onhandnum
WHERE NVL (dr, 0) = 0
AND (nonhandnum <> 0)
AND (cinventoryid = v_rmb_onhandnum_cinvmanid)
AND (cwarehouseid = v_rmb_onhandnum_cwhid)
AND (Cinvbasid = v_rmb_onhandnum_cinvbasid)
AND (vlot = v_rmb_onhandnum_vbatchcode)
AND (pk_corp <> '1001');
IF num <> 1
THEN
i := i + 1;
DBMS_OUTPUT.put_line (
'检查结果显示：NoVbatchocde:'
|| i
|| ',无批次号!!!!!!，仓库:'
|| v_rmb_onhandnum_cwhid
|| ',存货基本档案： '
|| v_rmb_onhandnum_cinvbasid
|| ' ,存货管理ID： '
|| v_rmb_onhandnum_cinvmanid
|| '，在现存内查询不到对应信息或是查询到不是1条信息，无法判断，请检查两边数据是否一致！！！！！！');
ELSE
SELECT *
INTO v_ic_onhandnum
FROM ic_onhandnum
WHERE NVL (dr, 0) = 0
AND (nonhandnum <> 0)
AND (cinventoryid = v_rmb_onhandnum_cinvmanid)
AND (cwarehouseid = v_rmb_onhandnum_cwhid)
AND (Cinvbasid = v_rmb_onhandnum_cinvbasid)
AND (vlot = v_rmb_onhandnum_vbatchcode)
AND (pk_corp <> '1001');
v_ic_onhandnum_nonhandnum := v_ic_onhandnum.nonhandnum;
IF (v_ic_onhandnum_nonhandnum <>
v_rmb_onhandnum_nonhandnum)
THEN
diffvalue := diffvalue + 1;
DBMS_OUTPUT.put_line (
'检查结果显示：No:'
|| diffvalue
|| ',批次号！！！！！！'
|| v_rmb_onhandnum_vbatchcode
|| ' ,现存量:'
|| v_ic_onhandnum_nonhandnum
|| ',店存量： '
|| v_rmb_onhandnum_nonhandnum
|| ' ,仓库:'
|| v_rmb_onhandnum_cwhid
|| ',存货基本档案： '
|| v_rmb_onhandnum_cinvbasid
|| ' ,存货管理ID： '
|| v_rmb_onhandnum_cinvmanid
|| ',在库存数量不一致，请检查！！！！！！');
END IF;*/
-- END IF;
ELSE --如果批次号不为空，检查有无对 应数量
SELECT COUNT (*)
INTO num
FROM ic_onhandnum
WHERE NVL (dr, 0) = 0
AND (nonhandnum <> 0)
AND (cinventoryid = v_rmb_onhandnum_cinvmanid)
AND (cwarehouseid = v_rmb_onhandnum_cwhid)
AND (Cinvbasid = v_rmb_onhandnum_cinvbasid)
AND (vlot = v_rmb_onhandnum_vbatchcode)
AND (pk_corp <> '1001');
IF (num <> 1)
THEN
IF num <= 0
THEN
SELECT invclassname invclass,
invcode,
invname,
measname measure,
num,
storname
INTO v_product2
FROM (SELECT cls.invclassname invclassname,
inv.invcode invcode,
inv.invname invname,
meas.measname measname,
oh.nonhandnum num,
bs.STORNAME storname
FROM rmb_onhandnum oh
LEFT OUTER JOIN dtjt_invcheck t
ON oh.conhandnumid =
t.conhandnumid
LEFT OUTER JOIN rmb_onhandfreeze f
ON oh.conhandnumid =
f.conhandnumid
LEFT OUTER JOIN bd_invbasdoc inv
ON oh.cinvbasid = inv.pk_invbasdoc
LEFT OUTER JOIN bd_invcl cls
ON inv.pk_invcl = cls.pk_invcl
LEFT OUTER JOIN bd_measdoc meas
ON inv.pk_measdoc =
meas.pk_measdoc
LEFT OUTER JOIN bd_invmandoc inm
ON inm.PK_INVBASDOC =
inv.PK_INVBASDOC
LEFT OUTER JOIN bd_stordoc bs
ON bs.PK_STORDOC = oh.CWHID
WHERE 1 = 1
AND NVL (oh.dr, 0) = 0
AND NVL (t.dr, 0) = 0
AND NVL (cls.dr, 0) = 0
AND NVL (inv.dr, 0) = 0
AND NVL (meas.dr, 0) = 0
AND NVL (inm.dr, 0) = 0
AND NVL (bs.dr, 0) = 0
AND inv.PK_INVBASDOC =
v_rmb_onhandnum_cinvbasid
AND inm.PK_INVMANDOC =
v_rmb_onhandnum_cinvmanid
AND oh.vbatchcode =
v_rmb_onhandnum_vbatchcode
AND oh.nonhandnum > 0
AND (oh.cinvmanid =
v_rmb_onhandnum_cinvmanid)
AND (oh.cwhid = v_rmb_onhandnum_cwhid)
AND (oh.cinvbasid =
v_rmb_onhandnum_cinvbasid));
i := i + 1;
DBMS_OUTPUT.put_line (
'检查结果显示：No:'
|| i
|| ',批次号：'
|| v_rmb_onhandnum_vbatchcode
|| ' ,店存数量:'
|| v_rmb_onhandnum_nonhandnum
|| ' ,店铺名称:'
|| v_product2.storname
|| ' ,商品名称:'
|| v_product2.invname
|| ' ,INVCODE:'
|| v_product2.invcode
|| ',在现存内查询不到对应信息，无法判断，请检查两边数据是否一致！！！！！！');
ELSE
i := i + 1;
DBMS_OUTPUT.put_line (
'检查结果显示：No:'
|| i
|| ',批次号：'
|| v_rmb_onhandnum_vbatchcode
|| ',店存数量：'
|| v_rmb_onhandnum_nonhandnum
|| ' ,仓库:'
|| v_rmb_onhandnum_cwhid
|| ',存货基本档案： '
|| v_rmb_onhandnum_Cinvbasid
|| ' ,存货管理ID： '
|| v_rmb_onhandnum_cinvmanid
|| ',在现存内查询到不是1条信息，无法判断，请检查两边数据是否一致！！！！！！');
END IF;
ELSE
SELECT *
INTO v_ic_onhandnum
FROM ic_onhandnum
WHERE NVL (dr, 0) = 0
AND (nonhandnum <> 0)
AND (cinventoryid = v_rmb_onhandnum_cinvmanid)
AND (cwarehouseid = v_rmb_onhandnum_cwhid)
AND (Cinvbasid = v_rmb_onhandnum_cinvbasid)
AND (vlot = v_rmb_onhandnum_vbatchcode)
AND (vlot = v_rmb_onhandnum_vbatchcode)
AND (pk_corp <> '1001');
v_ic_onhandnum_nonhandnum := v_ic_onhandnum.nonhandnum;
IF (v_ic_onhandnum_nonhandnum <>
v_rmb_onhandnum_nonhandnum)
THEN
diffvalue := diffvalue + 1;
DBMS_OUTPUT.put_line (
'检查结果显示：No:'
|| diffvalue
|| ',批次号：'
|| v_rmb_onhandnum_vbatchcode
|| ' ,现存量:'
|| v_ic_onhandnum_nonhandnum
|| ',店存量： '
|| v_rmb_onhandnum_nonhandnum
|| ' ,仓库:'
|| v_rmb_onhandnum_cwhid
|| ',存货基本档案： '
|| v_rmb_onhandnum_Cinvbasid
|| ' ,存货管理ID： '
|| v_rmb_onhandnum_cinvmanid
|| ',在库存数量不一致，请检查！！！！！！');
END IF;
END IF;
END IF;
END IF;
END LOOP;
IF c_rmb_onhandnum_withcode%ISOPEN
THEN
CLOSE c_rmb_onhandnum_withcode;
END IF;
num := i + diffvalue;
IF (num < 1)
THEN
DBMS_OUTPUT.put_line (
'以店存量验证有批次与产品正常！');
ELSE
DBMS_OUTPUT.put_line (
'以店存量验证有批次号产品完毕，错误数量：'
|| num);
END IF;
END;
/
浙公网安备 33010602011771号