/* Formatted on 2020/12/08 17:49:34 (QP5 v5.326) */
DECLARE
CURSOR c_ic_onhandnum_withoutVLot IS
SELECT *
FROM IC_ONHANDNUM
WHERE pk_corp <> '1001'
AND nonhandnum > 0
AND (NVL (dr, 0) = 0)
AND (vlot IS NULL)
ORDER BY pk_corp;
CURSOR c_ic_onhandnum_withVLot IS
SELECT *
FROM IC_ONHANDNUM io
WHERE pk_corp <> '1001'
AND io.nonhandnum > 0
AND (NVL (io.dr, 0) = 0)
AND (io.vlot IS NOT NULL)
AND NOT EXISTS
(SELECT vbatchcode
FROM rmb_onhandnum ro
WHERE NVL (dr, 0) = 0
AND (NVL (ro.nonhandnum, 0) > 0)
AND (ro.cinvmanid = io.cinventoryid)
AND (ro.cwhid = io.cwarehouseid)
AND (ro.cinvbasid = io.Cinvbasid)
AND (ro.vbatchcode = io.vlot))
ORDER BY vlot;
TYPE productinfo IS RECORD
(
pkcorp CHAR (4),
invcode VARCHAR2 (40),
invname VARCHAR2 (200),
storname VARCHAR2 (200),
ccalbodyid CHAR (20),
cwarehouseid CHAR (20),
cinventoryid CHAR (20),
vbatchcode VARCHAR2 (30)
);
v_product productinfo;
v_product1 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_vbatchcode IC_ONHANDNUM.VLOT%TYPE;
v_ic_onhandnum_Cinvbasid IC_ONHANDNUM.CINVBASID%TYPE;
v_ic_onhandnum_cinventoryid IC_ONHANDNUM.CINVENTORYID%TYPE;
v_ic_onhandnum_ccalbodyid IC_ONHANDNUM.CCALBODYID%TYPE;
v_ic_onhandnum_cwarehouseid IC_ONHANDNUM.CWAREHOUSEID%TYPE;
v_ic_onhandnum_nonhandnum IC_ONHANDNUM.NONHANDNUM%TYPE;
v_rmb_onhandnum rmb_onhandnum%ROWTYPE;
v_rmb_onhandnum_nonhandnum rmb_onhandnum.nonhandnum%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_ic_onhandnum_withoutVLot%ISOPEN
THEN
OPEN c_ic_onhandnum_withoutVLot;
END IF;
NULL;
LOOP
FETCH c_ic_onhandnum_withoutVLot INTO v_ic_onhandnum;
EXIT WHEN c_ic_onhandnum_withoutVLot%NOTFOUND;
v_ic_onhandnum_nonhandnum := v_ic_onhandnum.nonhandnum;
IF (v_ic_onhandnum_nonhandnum > 0) --如果有现存量，就检查对应的店存是否有数据，而且数量是否一致！！！
THEN
v_ic_onhandnum_pk_corp := v_ic_onhandnum.pk_corp;
v_ic_onhandnum_vbatchcode := v_ic_onhandnum.vlot;
v_ic_onhandnum_Cinvbasid := v_ic_onhandnum.cinvbasid;
v_ic_onhandnum_cinventoryid := v_ic_onhandnum.cinventoryid;
v_ic_onhandnum_ccalbodyid := v_ic_onhandnum.ccalbodyid;
v_ic_onhandnum_cwarehouseid := v_ic_onhandnum.cwarehouseid;
IF (v_ic_onhandnum_vbatchcode IS NULL)
THEN --如果批次号为空，检查有无对 应数量
SELECT COUNT (*)
INTO num
FROM rmb_onhandnum
WHERE NVL (dr, 0) = 0
AND (nonhandnum <> 0 OR nretailnum <> 0)
AND (cinvmanid = v_ic_onhandnum_cinventoryid)
AND (cwhid = v_ic_onhandnum_cwarehouseid)
AND (cinvbasid = v_ic_onhandnum_Cinvbasid);
IF num <> 1
THEN
SELECT h.pk_corp pkcorp,
inv.invcode invcode,
inv.invname invname,
bs.STORNAME storname,
h.ccalbodyid ccalbodyid,
h.cwarehouseid cwarehouseid,
h.cinventoryid cinventoryid,
h.vlot vbatchcode
INTO v_product
FROM ic_onhandnum h
LEFT OUTER JOIN ic_onhandnum_b b
ON h.pk_onhandnum = b.pk_onhandnum
LEFT OUTER JOIN bd_invbasdoc inv
ON h.cinvbasid = inv.pk_invbasdoc
LEFT OUTER JOIN bd_invmandoc inm
ON inm.PK_INVBASDOC = h.CINVENTORYID
LEFT OUTER JOIN bd_stordoc bs
ON bs.PK_STORDOC = h.CWAREHOUSEID
WHERE (NVL (h.dr, 0) = 0)
AND (NVL (b.dr, 0) = 0)
AND (NVL (inm.dr, 0) = 0)
AND (NVL (bs.dr, 0) = 0)
AND (h.PK_CORP <> '1001')
AND h.CWAREHOUSEID = v_ic_onhandnum_cwarehouseid
AND h.CCALBODYID = v_ic_onhandnum_ccalbodyid
AND h.CINVBASID = v_ic_onhandnum_Cinvbasid
AND h.CINVENTORYID = v_ic_onhandnum_cinventoryid
AND h.VLOT IS NULL;
IF num <= 0
THEN
i := i + 1;
DBMS_OUTPUT.put_line (
'无批次检查结果显示:'
|| i
|| ' ,PK_CORP:'
|| v_product.pkcorp
|| ' ,库存组织:'
|| v_product.storname
|| ' ,商品名称:'
|| v_product.invname
|| ' ,INVCODE:'
|| v_product.invcode
|| ' ,现存数量为:'
|| v_ic_onhandnum_nonhandnum
|| '，在店存内查询不到对应信息无法判断！！！！！！');
ELSE
j := j + 1;
DBMS_OUTPUT.put_line (
'无批次检查结果显示:'
|| j
|| ' ,PK_CORP:'
|| v_product.pkcorp
|| ' ,库存组织:'
|| v_product.storname
|| ' ,商品名称:'
|| v_product.invname
|| ' ,INVCODE:'
|| v_product.invcode
|| ' ,现存数量为:'
|| v_ic_onhandnum_nonhandnum
|| ' ,在店存内查询到不是1条信息，无法判断，请检查两边数据是否一致！！！！！！');
END IF;
ELSE
SELECT *
INTO v_rmb_onhandnum
FROM rmb_onhandnum
WHERE NVL (dr, 0) = 0
AND (nonhandnum <> 0 OR nretailnum <> 0)
AND (cinvmanid = v_ic_onhandnum_cinventoryid)
AND (cwhid = v_ic_onhandnum_cwarehouseid)
AND (cinvbasid = v_ic_onhandnum_Cinvbasid);
v_rmb_onhandnum_nonhandnum := v_rmb_onhandnum.nonhandnum;
IF (v_ic_onhandnum_nonhandnum <>
v_rmb_onhandnum_nonhandnum)
THEN
SELECT h.pk_corp pkcorp,
inv.invcode invcode,
inv.invname invname,
bs.STORNAME storname,
h.ccalbodyid ccalbodyid,
h.cwarehouseid cwarehouseid,
h.cinventoryid cinventoryid,
h.vlot vbatchcode
INTO v_product1
FROM ic_onhandnum h
LEFT OUTER JOIN ic_onhandnum_b b
ON h.pk_onhandnum = b.pk_onhandnum
LEFT OUTER JOIN bd_invbasdoc inv
ON h.cinvbasid = inv.pk_invbasdoc
LEFT OUTER JOIN bd_invmandoc inm
ON inm.PK_INVBASDOC = h.CINVENTORYID
LEFT OUTER JOIN bd_stordoc bs
ON bs.PK_STORDOC = h.CWAREHOUSEID
WHERE (NVL (h.dr, 0) = 0)
AND (NVL (b.dr, 0) = 0)
AND (NVL (inm.dr, 0) = 0)
AND (NVL (bs.dr, 0) = 0)
AND (h.PK_CORP <> '1001')
AND h.CWAREHOUSEID =
v_ic_onhandnum_cwarehouseid
AND h.CCALBODYID = v_ic_onhandnum_ccalbodyid
AND h.CINVBASID = v_ic_onhandnum_Cinvbasid
AND h.CINVENTORYID =
v_ic_onhandnum_cinventoryid
AND h.VLOT IS NULL;
diffvalue := diffvalue + 1;
DBMS_OUTPUT.put_line (
'检查结果显示：No:'
|| diffvalue
|| ' ,现存量='
|| v_ic_onhandnum_nonhandnum
|| ' ,店存量='
|| v_rmb_onhandnum_nonhandnum
|| ' ,PK_CORP:'
|| v_product1.pkcorp
|| ' ,库存组织:'
|| 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_ic_onhandnum_withoutVLot%ISOPEN
THEN
CLOSE c_ic_onhandnum_withoutVLot;
END IF;
num := i + j + diffvalue;
IF (num < 1)
THEN
DBMS_OUTPUT.put_line ('以无批次号现存量验证正常!!!');
ELSE
DBMS_OUTPUT.put_line (
'以无批次号现存量验证完毕，错误数量：' || num);
END IF;
--下面是有批次号的检验！！！！！！！！！！！！！！！
i := 0;
j := 0;
diffvalue := 0;
num := 0;
DBMS_OUTPUT.put_line ('===============================================');
DBMS_OUTPUT.put_line (
'============以下查询有批次号现存/店存数据==========');
DBMS_OUTPUT.put_line ('===============================================');
IF NOT c_ic_onhandnum_withVLot%ISOPEN
THEN
OPEN c_ic_onhandnum_withVLot;
END IF;
NULL;
LOOP
FETCH c_ic_onhandnum_withVLot INTO v_ic_onhandnum;
EXIT WHEN c_ic_onhandnum_withVLot%NOTFOUND;
v_ic_onhandnum_nonhandnum := v_ic_onhandnum.nonhandnum;
IF (v_ic_onhandnum_nonhandnum > 0) --如果有现存量，就检查对应的店存是否有数据，而且数量是否一致！！！
THEN
v_ic_onhandnum_pk_corp := v_ic_onhandnum.pk_corp;
v_ic_onhandnum_vbatchcode := v_ic_onhandnum.vlot;
v_ic_onhandnum_Cinvbasid := v_ic_onhandnum.cinvbasid;
v_ic_onhandnum_cinventoryid := v_ic_onhandnum.cinventoryid;
v_ic_onhandnum_ccalbodyid := v_ic_onhandnum.ccalbodyid;
v_ic_onhandnum_cwarehouseid := v_ic_onhandnum.cwarehouseid;
IF (v_ic_onhandnum_vbatchcode IS NOT NULL)
THEN
SELECT h.pk_corp pkcorp,
inv.invcode invcode,
inv.invname invname,
bs.STORNAME storname,
h.ccalbodyid ccalbodyid,
h.cwarehouseid cwarehouseid,
h.cinventoryid cinventoryid,
h.vlot vbatchcode
INTO v_product
FROM ic_onhandnum h
LEFT OUTER JOIN ic_onhandnum_b b
ON h.pk_onhandnum = b.pk_onhandnum
LEFT OUTER JOIN bd_invbasdoc inv
ON h.cinvbasid = inv.pk_invbasdoc
LEFT OUTER JOIN bd_invmandoc inm
ON inm.PK_INVBASDOC = h.CINVENTORYID
LEFT OUTER JOIN bd_stordoc bs
ON bs.PK_STORDOC = h.CWAREHOUSEID
WHERE (NVL (h.dr, 0) = 0)
AND (NVL (b.dr, 0) = 0)
AND (NVL (inm.dr, 0) = 0)
AND (NVL (bs.dr, 0) = 0)
AND (h.PK_CORP <> '1001')
AND h.CWAREHOUSEID = v_ic_onhandnum_cwarehouseid
AND h.CCALBODYID = v_ic_onhandnum_ccalbodyid
AND h.CINVBASID = v_ic_onhandnum_Cinvbasid
AND h.CINVENTORYID = v_ic_onhandnum_cinventoryid
AND h.VLOT = v_ic_onhandnum_vbatchcode;
i := i + 1;
DBMS_OUTPUT.put_line (
'检查结果显示:'
|| i
|| ', 批次号:'
|| v_ic_onhandnum_vbatchcode
|| ' ,PK_CORP:'
|| v_product.pkcorp
|| ' ,库存组织:'
|| v_product.storname
|| ' ,商品名称:'
|| v_product.invname
|| ' ,INVCODE:'
|| v_product.invcode
|| ' ,现存数量为:'
|| v_ic_onhandnum_nonhandnum
|| '，在店存内查询不到对应信息无法判断！！！！！！');
END IF;
ELSE
--如果批次号为空，直接报异常！！！！！！
DBMS_OUTPUT.put_line (
'检查结果异常，批次号？？？？？？');
END IF;
--END IF;
END LOOP;
IF c_ic_onhandnum_withVLot%ISOPEN
THEN
CLOSE c_ic_onhandnum_withVLot;
END IF;
num := i + diffvalue;
IF (num < 1)
THEN
DBMS_OUTPUT.put_line ('以现存量有批次号验证正常！');
ELSE
DBMS_OUTPUT.put_line (
'以现存量有批次号验证完毕，错误数量：' || num);
END IF;
END;
浙公网安备 33010602011771号