SQL-007以现存量提取数据验证店存量

/* 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;