按照商品信息更新证照效期

INSERT INTO t_ware_certificate
  (compid, wareid, certificateid, lastmodify, lasttime, status)
  SELECT 2, w.wareid, c.certificateid, 168, SYSDATE, -1
    FROM t_ware w
   CROSS JOIN (SELECT LEVEL AS certificateid
                 FROM dual
               CONNECT BY LEVEL <=
                          (SELECT COUNT(1) FROM t_ware_certificate_set)) c
    LEFT JOIN t_ware_certificate wc
      ON w.wareid = wc.wareid
     AND c.certificateid = wc.certificateid
   WHERE wc.wareid IS NULL;

INSERT INTO t_ware_certificate_image
  (compid, wareid, filetype  , lastmodify, lasttime,scanno)
  SELECT 2, w.wareid, c.certificateid, 168, SYSDATE,250220000001+ROWNUM
    FROM t_ware w
   CROSS JOIN (SELECT LEVEL AS certificateid
                 FROM dual
               CONNECT BY LEVEL <=
                          (SELECT COUNT(1) FROM t_ware_certificate_set)) c
    LEFT JOIN t_ware_certificate_image wc
      ON w.wareid = wc.wareid
     AND c.certificateid = wc.filetype 
   WHERE wc.wareid IS NULL;

update t_ware_certificate_image set compid = 2  where lastmodify = 168

---更新证照效期  批准文号效期

update t_ware_certificate a set INVALIDATE  = 
( select fileno_invalidate   from t_ware_ext b  where  a.compid = b.compid and a.wareid = b.wareid )
where a.compid = 2 and exists( select 1   from t_ware_ext b  where  a.compid = b.compid and a.wareid = b.wareid ) 
and a.certificateid = 9

---更新证照效期  生产批文

update t_ware_certificate a set INVALIDATE  = 
( select production_invalidate   from t_ware_ext b  where  a.compid = b.compid and a.wareid = b.wareid )
where a.compid = 2 and exists( select 1   from t_ware_ext b  where  a.compid = b.compid and a.wareid = b.wareid ) 
and a.certificateid = 1

往来单位转首营

---select f_regular_vencus2chk(1,10001,8888) from dual;

/*
drop table d_execution_log;
CREATE TABLE d_execution_log (
  successful_count NUMBER,
  failed_count NUMBER,
  operation_time TIMESTAMP
);
*/
DECLARE
  v_result        VARCHAR2(100);
  v_result_count  NUMBER;
  v_success_count NUMBER := 0; -- 记录成功次数
  v_failure_count NUMBER := 0; -- 记录失败次数
BEGIN
  FOR rec IN (SELECT DISTINCT vencusno
                FROM t_vencus 
               WHERE vencusno NOT IN (SELECT vencusno FROM (SELECT customerid  AS vencusno FROM t_chk_customer UNION ALL SELECT vendorno  FROM t_chk_vendor) )
                             ) LOOP

    SELECT COUNT(*)
    INTO v_result_count
      FROM (SELECT customerid  AS vencusno FROM t_chk_customer UNION ALL SELECT vendorno  FROM t_chk_vendor) 
     WHERE vencusno = rec.vencusno;

    IF v_result_count = 0 THEN
      v_result        := f_regular_vencus2chk(p_compid => 1,p_vencusno =>  rec.vencusno,
                                            p_userid => 168);
      v_success_count := v_success_count + 1; -- 增加成功计数
    ELSE
      v_failure_count := v_failure_count + 1; -- 增加失败计数
    END IF;
  END LOOP;

  -- 记录操作完成时间
  INSERT INTO d_execution_log
    (successful_count, failed_count, operation_time)
  VALUES
    (v_success_count, v_failure_count, SYSDATE);

  COMMIT; -- 提交事务
END;

/*
delete from d_execution_log;
SELECT * FROM d_execution_log;

SELECT * FROM t_chk_customer
*/
/*
UPDATE t_chk_vendor 
SET 
  buyer_chk = '市场需求',
  bus_chk = '同意',
  qc_technician_comment = '同意',
  checkbit1 = 1,
  checkbit2 = 1,
  checkbit3 = 1,
  checker1 = 1014,
  checker2 = 1005,
  checker3 = 1003,
  status = 1,
  checkdate1 = TO_DATE('2024-07-01 19:01:20', 'YYYY-MM-DD HH24:MI:SS'),
  checkdate2 = TO_DATE('2024-07-01 19:01:20', 'YYYY-MM-DD HH24:MI:SS'),
  checkdate3 = TO_DATE('2024-07-01 19:01:20', 'YYYY-MM-DD HH24:MI:SS')
WHERE status = 0;

*/

商品信息转首营

DECLARE
  v_result       VARCHAR2(100);
  v_result_count NUMBER;
BEGIN
  FOR rec IN (SELECT DISTINCT wareid
                FROM t_ware
               WHERE wareid BETWEEN 0 AND 118701) LOOP
    BEGIN
      -- 检查 t_chk_ware 表中是否已经存在相应的 wareid
      SELECT COUNT(*)
        INTO v_result_count
        FROM t_chk_ware
       WHERE wareid = rec.wareid;

      -- 如果 wareid 不存在于 t_chk_ware 表中，则调用存储过程并传递 wareid 作为参数
      IF v_result_count = 0 THEN
        v_result := f_regular_ware2chk(p_compid => 1, p_wareid => rec.wareid,
                                       p_userid => 168);
        /*      DBMS_OUTPUT.PUT_LINE('Result for wareid ' || rec.wareid || ': ' || v_result);
        ELSE
          DBMS_OUTPUT.PUT_LINE('Wareid ' || rec.wareid || ' already exists in t_chk_ware. Skipping...');*/
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        -- 记录错误日志，或者只是继续下一个循环
        -- DBMS_OUTPUT.PUT_LINE('Error processing wareid ' || rec.wareid || ': ' || SQLERRM);
        NULL; -- 跳过错误记录
    END;
  END LOOP;
END;