Database/FAC/FAC_PACTSK.SRC

/*  $Revision$
 *  $Id$
 */

/*  FAC_PACTSK
 *
 *  Contains all functions for tasker.
 *
 */

/* Als tijdens een upgrade de PROCEDURE waittask loopt dan 'hangt' de package
   recreate totdat de timeout van waittask optreedt (max 60 seconde).
   Door hier even een willekeurige taak te starten eindigt waittask even
   en hebben wij de kans te upgraden.
   Van de PUTORDERS taak weten we dat het vrij onschuldig is als die een
   keer wordt gestart zonder dat er eigenlijk iets te doen is.
   In omgevingen waar de tasker niet draait zal geen fac_task record zijn
   en dan doet dit statement ook niets.
 */
UPDATE fac_task
   SET fac_task_nextrun = SYSDATE
 WHERE fac_task_key =
       (SELECT MIN (fac_task_key)
          FROM fac_task tt, fac_cust tsko
         WHERE     tt.fac_cust_key = tsko.fac_cust_key
               AND fac_cust_enabled > 0
               AND BITAND (fac_task_flags, 1) = 1
               AND fac_task_code = 'PUTORDERS');
--  Zodat de externe tasker het ziet:
COMMIT;

CREATE OR REPLACE PACKAGE tsk
AS
    PROCEDURE registercust (p_customerid VARCHAR2 DEFAULT NULL,
                            p_schema VARCHAR2 DEFAULT NULL);
    PROCEDURE registertask (p_taskcode VARCHAR2,
                            p_customerid VARCHAR2 DEFAULT NULL);
    PROCEDURE starttask (p_taskcode VARCHAR2,
                         p_nextrun DATE DEFAULT SYSDATE,
                         p_customerid VARCHAR2 DEFAULT NULL);
    PROCEDURE waittask  (pinterval NUMBER DEFAULT 5,
                         ptimeout NUMBER DEFAULT 300);
END tsk;
/

CREATE OR REPLACE PACKAGE BODY tsk
AS
    PROCEDURE registercust (p_customerid VARCHAR2 DEFAULT NULL,
                            p_schema VARCHAR2 DEFAULT NULL)
    AS
        tsk_master_schema VARCHAR(32);
        custid            fac_version.fac_version_cust%TYPE;
        selfid            fac_version.fac_version_cust%TYPE;
        fac_schema        fac_version.fac_version_schema%TYPE;
        currentdb_schema  VARCHAR2 (64);
        tsksql            VARCHAR2 (4000);
    BEGIN
        SELECT fac_version_cust,
               fac_version_schema
          INTO selfid,
               fac_schema
          FROM fac_version;
        custid := COALESCE(p_customerid, selfid);
        tsk_master_schema := fac.getsetting ('tsk_master_schema');
        currentdb_schema := SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA');

        IF tsk_master_schema IS NULL OR tsk_master_schema = '*SELF*' OR tsk_master_schema = currentdb_schema
        THEN    -- zelf oplossen
          BEGIN
            INSERT INTO fac_cust(fac_cust_customerid, fac_cust_schema)
               VALUES(custid, COALESCE(p_schema, fac_schema));
           EXCEPTION
           WHEN DUP_VAL_ON_INDEX
           THEN
               NULL;
          END;
          -- Schema bijwerken
          UPDATE fac_cust
             SET fac_cust_schema = COALESCE(p_schema, fac_schema)
           WHERE fac_cust_customerid = custid;
        ELSE    -- Remote register
            tsksql := 'BEGIN ' || tsk_master_schema || '.tsk.registercust(:custid, :schema); END;';
            EXECUTE IMMEDIATE tsksql USING selfid, fac_schema;
        END IF;
    END;

    PROCEDURE registertask (p_taskcode VARCHAR2,
                            p_customerid VARCHAR2 DEFAULT NULL)
    AS
        tsk_master_schema VARCHAR(32);
        selfid            fac_version.fac_version_cust%TYPE;
        tsksql            VARCHAR2 (4000);
        currentdb_schema  VARCHAR2 (64);
    BEGIN
        SELECT fac_version_cust INTO selfid FROM fac_version;
        tsk_master_schema := fac.getsetting ('tsk_master_schema');
        currentdb_schema := SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA');

        IF tsk_master_schema IS NULL OR tsk_master_schema = '*SELF*' OR tsk_master_schema = currentdb_schema
        THEN    -- zelf oplossen
            BEGIN
                INSERT INTO fac_task(fac_cust_key, fac_task_code)
                    SELECT fac_cust_key, p_taskcode
                      FROM fac_cust
                     WHERE fac_cust_customerid = COALESCE(p_customerid, selfid);
            EXCEPTION
            WHEN DUP_VAL_ON_INDEX
            THEN
                NULL;
            END;
        ELSE    -- start remote tasker
            tsksql := 'BEGIN ' || tsk_master_schema || '.tsk.registertask(:taskcode, :custid); END;';
            EXECUTE IMMEDIATE tsksql USING p_taskcode, selfid;
        END IF;
    END;

    -- veronderstelt dat taak al correct is geregistreerd
    PROCEDURE starttask (p_taskcode VARCHAR2,
                         p_nextrun DATE DEFAULT SYSDATE,
                         p_customerid VARCHAR2 DEFAULT NULL)
    AS
        tsk_master_schema VARCHAR(32);
        selfid            fac_version.fac_version_cust%TYPE;
        tsksql            VARCHAR2 (4000);
        currentdb_schema  VARCHAR2 (64);
    BEGIN
        SELECT fac_version_cust INTO selfid FROM fac_version;
        tsk_master_schema := fac.getsetting ('tsk_master_schema');
        currentdb_schema := SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA');

        IF tsk_master_schema IS NULL OR tsk_master_schema = '*SELF*' OR tsk_master_schema = currentdb_schema
        THEN    -- zelf oplossen
            UPDATE fac_task
               SET fac_task_nextrun = fac.safe_LEAST (p_nextrun, fac_task_nextrun)
             WHERE fac_task_code = p_taskcode
               AND fac_cust_key = (SELECT fac_cust_key
                                    FROM fac_cust
                                   WHERE fac_cust_customerid = COALESCE(p_customerid, selfid));
        ELSE    -- start remote tasker
            tsksql := 'BEGIN ' || tsk_master_schema || '.tsk.starttask(:taskcode, :nextrun, :custid); END;';
            EXECUTE IMMEDIATE tsksql USING p_taskcode, p_nextrun, selfid;
        END IF;
    END;

    -- Keert terug als er iets interessants in fac_task staat
    -- of als timeout is verstreken
    PROCEDURE waittask (pinterval NUMBER DEFAULT 5,
                        ptimeout NUMBER DEFAULT 300)
    AS
        timeend   DATE;
        firsttask DATE;
    BEGIN
        timeend := SYSDATE + ptimeout / 60/60/24;
        WHILE timeend > SYSDATE
        LOOP
            SELECT MIN (fac_task_nextrun)
              INTO firsttask
              FROM fac_task tt, fac_cust tsko
            WHERE   tt.fac_cust_key = tsko.fac_cust_key
                AND fac_cust_enabled > 0
                AND fac_task_nextrun <= SYSDATE
                AND BITAND (fac_task_flags, 1) = 1;

            IF firsttask <= SYSDATE
            THEN
                RETURN;
            END IF;

            DBMS_SESSION.SLEEP(pinterval); -- Dit maakt dat in de Oracle statistics allerlei timer events
                                           -- hoog scoren in 'Top 5 Timed Events' Dat is verder niet erg.
        END LOOP;
    END;
END tsk;
/
-- tsk_master_schema needs:
-- GRANT EXECUTE ON tsk TO PUBLIC

REGISTERRUN('$Id$')