/* Formatted on 2020/11/13 11:38:36 (QP5 v5.326) */
DECLARE
    /*以下定义开始日期，每次查询时请自行修改*/
    startdate                 CHAR (10) := '2020-10-01';


    /*  以下语句请切记不要改动！！！！！！！！！！！！！！！！！！ */
    v_dap_finindex            dap_finindex%ROWTYPE;
    v_dtjt_tradein            dtjt_tradein_h%ROWTYPE;
    endDate                   CHAR (10) := '2022-12-31';

    CURSOR c_dtjt_tradein IS
        SELECT H.*
          FROM dtjt_tradein_h  h
               INNER JOIN dtjt_tradein_b b ON h.ctradeinhid = b.ctradeinhid
--2020-11-15更新，增加dtjt_tradein_b
         WHERE     NVL (h.dr, 0) = 0
               AND NVL (b.dr, 0) = 0
               AND h.vbillstatus = 1
               AND h.vdef2 IS NULL
               AND H.dbilldate >= startdate
               AND H.dbilldate <= endDate
 for update of vdef2 nowait;

    num                       INT := 0;
    i                         INT := 0;
    v_dtjt_vdef2              dtjt_tradein_h.vdef2%TYPE;
    v_dtjt_vbillcode          dtjt_tradein_h.vbillcode%TYPE;
    v_dtjt_pk_corp            dtjt_tradein_h.pk_corp%TYPE;
    v_dap_finindex_billcode   DAP_FININDEX.BILLCODE%TYPE;
    v_dap_finindex_pk_corp    dap_finindex.pk_corp%TYPE;
    v_dtjt_dbilldate          dtjt_tradein_h.dbilldate%TYPE;
BEGIN
    SELECT TO_CHAR (SYSDATE, 'yyyy-mm-dd') INTO endDate FROM DUAL;

    DBMS_OUTPUT.put_line (
           '以旧换新工单传财务凭证验证，检查日期范围：'
        || startdate
        || '  --   '
        || endDate);

    IF NOT c_dtjt_tradein%ISOPEN
    THEN
        OPEN c_dtjt_tradein;
    END IF;

    NULL;

    LOOP
        FETCH c_dtjt_tradein INTO v_dtjt_tradein;

        EXIT WHEN c_dtjt_tradein%NOTFOUND;
        v_dtjt_vdef2 := v_dtjt_tradein.vdef2;
        v_dtjt_vbillcode := v_dtjt_tradein.vbillcode;
        v_dtjt_pk_corp := v_dtjt_tradein.pk_corp;
        v_dtjt_dbilldate := v_dtjt_tradein.dbilldate;
        v_dap_finindex_billcode := v_dtjt_vbillcode;
        v_dap_finindex_pk_corp := v_dtjt_pk_corp;


       SELECT COUNT (billcode)
          INTO num
          FROM dap_finindex
         WHERE     (billcode = v_dap_finindex_billcode)
               AND (pk_corp = v_dap_finindex_pk_corp)
               AND (billcode IS NOT NULL)
               AND (busidate >= startdate)
               AND (busidate <= endDate)
               AND (pk_sys = 'BSM' AND pk_proc = 'JT01')
               -- and (flag=2)   这一行需要验证一下再决定是否使用！！！！！！！！！
               AND (NVL (dr, 0) = 0);

        IF (num <> 1)
        THEN
            i := i + 1;
           
            UPDATE dtjt_tradein_h  SET vdef2=null,vdef3=v_dtjt_vdef2   WHERE    current of c_dtjt_tradein;

            IF (i < 10)
            THEN
                DBMS_OUTPUT.put_line (
                       '检查结果显示：No:0'
                    || i
                    || ',工单日期：'
                    || v_dtjt_dbilldate
                    || '   ，公司:'
                    || v_dtjt_pk_corp
                    || ',可能未传递单号：  '
                    || v_dtjt_vbillcode);
            ELSE
                DBMS_OUTPUT.put_line (
                       '检查结果显示：No:'
                    || i
                    || ',工单日期：'
                    || v_dtjt_dbilldate
                    || '   ，公司:'
                    || v_dtjt_pk_corp
                    || ',可能未传递单号：  '
                    || v_dtjt_vbillcode);
            END IF;
        END IF;
    END LOOP;

    IF c_dtjt_tradein%ISOPEN
    THEN
        CLOSE c_dtjt_tradein;
    END IF;

  

    IF (i < 1)
    THEN
        DBMS_OUTPUT.put_line (
            '以旧换新工单传财务凭证验证正常，放心使用');
    ELSE
        DBMS_OUTPUT.put_line (
               '以旧换新工单传财务凭证验证完毕，可能未传递总数量：'
            || i);
    END IF;
END;
/