-- 设备产量夜间搬迁过程
-- 兼容：Oracle 11 / Oracle 19
-- 说明：
-- 1. 只做 RT -> DAY 搬迁，不再夜间重扫明细表。
-- 2. 搬迁完成后删除目标日期 RT 数据，等待新一天自然重建。

CREATE OR REPLACE PACKAGE PKG_DEVICE_PROD_CALC AS

    PROCEDURE FLUSH_RT_TO_DAY(
        P_PROD_DATE  IN DATE DEFAULT TRUNC(SYSDATE) - 1,
        P_PARAM_NAME IN VARCHAR2 DEFAULT '机台状态-实际产出数量'
    );

END PKG_DEVICE_PROD_CALC;
/

CREATE OR REPLACE PACKAGE BODY PKG_DEVICE_PROD_CALC AS

    PROCEDURE FLUSH_RT_TO_DAY(
        P_PROD_DATE  IN DATE DEFAULT TRUNC(SYSDATE) - 1,
        P_PARAM_NAME IN VARCHAR2 DEFAULT '机台状态-实际产出数量'
    ) IS
        V_TARGET_DATE DATE := TRUNC(P_PROD_DATE);
    BEGIN
        MERGE INTO DEVICE_DAILY_PRODUCTION D
        USING (
            SELECT PROD_DATE,
                   DEVICE_CODE,
                   PARAM_NAME,
                   CURRENT_TOTAL,
                   RESET_COUNT
              FROM RT_DAILY_PROD_STATE
             WHERE PROD_DATE = V_TARGET_DATE
               AND PARAM_NAME = P_PARAM_NAME
        ) S
        ON (
            D.PROD_DATE = S.PROD_DATE
            AND D.DEVICE_CODE = S.DEVICE_CODE
            AND D.PARAM_NAME = S.PARAM_NAME
        )
        WHEN MATCHED THEN
            UPDATE SET
                D.DAILY_PROD = S.CURRENT_TOTAL,
                D.RESET_COUNT = S.RESET_COUNT,
                D.CALC_TIME = SYSDATE
        WHEN NOT MATCHED THEN
            INSERT (
                PROD_DATE, DEVICE_CODE, PARAM_NAME, DAILY_PROD, RESET_COUNT, CALC_TIME
            ) VALUES (
                S.PROD_DATE, S.DEVICE_CODE, S.PARAM_NAME, S.CURRENT_TOTAL, S.RESET_COUNT, SYSDATE
            );

        -- 为什么这样做：搬迁完成后清掉昨日 RT，避免月累计在窗口期之外重复计入已入 DAY 的历史数据。
        DELETE FROM RT_DAILY_PROD_STATE
         WHERE PROD_DATE = V_TARGET_DATE
           AND PARAM_NAME = P_PARAM_NAME;

        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
    END FLUSH_RT_TO_DAY;

END PKG_DEVICE_PROD_CALC;
/

-- 如需数据库自动定时执行，可按现场窗口时间创建调度任务：
-- BEGIN
--   DBMS_SCHEDULER.CREATE_JOB(
--     JOB_NAME        => 'JOB_FLUSH_RT_TO_DAY',
--     JOB_TYPE        => 'STORED_PROCEDURE',
--     JOB_ACTION      => 'PKG_DEVICE_PROD_CALC.FLUSH_RT_TO_DAY',
--     START_DATE      => SYSTIMESTAMP,
--     REPEAT_INTERVAL => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=10;BYSECOND=0',
--     ENABLED         => TRUE,
--     COMMENTS        => '设备产量 RT->DAY 夜间搬迁'
--   );
-- END;
-- /
