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

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