CREATE OR REPLACE PACKAGE WFSDW.PKG_METADATA

/*
        *****************************************************************************
        PACKAGE: PKG_METADATA
        [ETL METADATA]

        -- @version $Id: createWFSmd_DW.sql,v 1.1 2007/10/29 19:39:26 dweiner Exp $

        *****************************************************************************
        This package contains stored procedures and functions that store information
        in and report on metadata tables.

        *****************************************************************************
        [USED BY]       Any ETL process / background process
        *****************************************************************************
        [By Laura Francheri     - 01/24/2002]
        [Oracle version         - 09/14/2006]
        *****************************************************************************
        Change Log:

        *****************************************************************************
*/

AS

PROCEDURE SPMD_LogProcessStart (
        etl_process                         NUMBER,
        parent_etl_process                  NUMBER    DEFAULT NULL,
        parent_etl_process_runinstance      NUMBER    DEFAULT NULL,
        comments                            VARCHAR2  DEFAULT NULL,
        new_run_instance                OUT NUMBER,
        error_status                    OUT NUMBER);

PROCEDURE spMD_LockObject (
        userobject                          NUMBER,
        etl_process                         NUMBER,
        run_instance                        NUMBER,
        force_lock                          NUMBER      DEFAULT 0, -- By default, do not force lock.
        last_sync_date_output           OUT TIMESTAMP,
        nonverbose                          NUMBER      DEFAULT 0,
        error_status                    OUT NUMBER);

PROCEDURE spMD_LogProcessFinishError (
                etl_process                 NUMBER,
                run_instance                NUMBER,
                user_comments               VARCHAR2,
                affected_rows               NUMBER      DEFAULT NULL,
                error                       NUMBER      DEFAULT NULL,
                error_status            OUT NUMBER);

PROCEDURE spMD_LogProcessFinishOK (
                etl_process                 NUMBER,
                run_instance                NUMBER,
                user_comments               VARCHAR2,
                affected_rows               NUMBER      DEFAULT NULL,
                error_status            OUT NUMBER);

PROCEDURE spMD_ReleaseObject (
                userobject                  NUMBER,
                etl_process                 NUMBER,
                run_instance                NUMBER,
                lastsyncdate                TIMESTAMP,
                has_error                   NUMBER, -- 0=OK, 1=ERROR
                nonverbose                  NUMBER      DEFAULT 0,
                error_status            OUT NUMBER);

PROCEDURE spMD_LogErrorMessage (
                        etl_process         NUMBER,
                        run_instance        NUMBER,
                        message             VARCHAR2);

PROCEDURE SPMD_LOGINFOMESSAGE (
             etl_process                    NUMBER,
             run_instance                   NUMBER,
             message                        VARCHAR2);

PROCEDURE SPMD_WRITEEVENTLOG (
        process_name                        varchar2,
        error_code                          number,
        error_message                       varchar2    DEFAULT '',
        section                             varchar2    DEFAULT ''
);

END PKG_METADATA;
/

CREATE OR REPLACE PACKAGE BODY WFSDW.PKG_METADATA
AS


/*
        *****************************************************************************
        PROCEDURE: spMD_LogProcessStart
        [ETL METADATA]
        *****************************************************************************
        Inserts an entry in the Metadata table MD_ETLProcessRunInstance, indicating
        that a process has started.

        [RETURNS]       A RunInstance (sequential number), which will be used to
                                log messages and any other references to this RunInstance.
                                ERROR: error_status will be -1 and new_run_instance will be -1
                                as well.

        [NOTE]          At this point I'm not raising application errors as this code
                                will only be called from within Oracle. If needed, just uncomment
                                the raise_application_error calls.

        *****************************************************************************
        [USED BY]       Should be used by every ETL process right at the beginning.
        *****************************************************************************
        [SQL Server version by Laura Francheri 01/24/2002]
        [Oracle version by Venkat - revised by Laura on 09/10/2006]
        *****************************************************************************
        Change Log: 1) Added the Proceducre spMD_ProcessReport on 10/02/2006 by
                   Suresh Neravati to print the Process Run Instance information.
        *****************************************************************************
*/

PROCEDURE spMD_LogProcessStart (
                etl_process                         NUMBER,
                parent_etl_process                  NUMBER    DEFAULT NULL,
                parent_etl_process_runinstance      NUMBER    DEFAULT NULL,
                comments                            VARCHAR2  DEFAULT NULL,
                new_run_instance                OUT NUMBER,
                error_status                    OUT NUMBER)
IS
                PRAGMA AUTONOMOUS_TRANSACTION;

                process_name               VARCHAR2 (50);
                lockableobject             NUMBER;
                lockableobject_name        VARCHAR2 (50);
                error_message              VARCHAR2 (500);
                count_records              NUMBER := 0;
                SPID                       NUMBER;
                comments_var               VARCHAR2 (500) := comments;
                v_sqlcode                  number;
BEGIN

-- Initialize...
error_status := -1;
new_run_instance := -1;

BEGIN
        -- Process name and object being loaded
        SELECT  Name, LockableObjectLoaded
        INTO    process_name, lockableobject
        FROM    MD_ETLProcess
        WHERE   ETLProcessKey = etl_process;

        EXCEPTION
                WHEN OTHERS THEN
                        v_sqlcode := SQLCODE;
                        -- Not raising application errors at this point!
                        -- raise_application_error(-20001, 'spMD_LogProcessStart ERROR: error inserting MD_ETLProcessMessage');
                        spMD_WriteEventLog ('spMD_LogProcessStart', v_sqlcode, 'Null arguments or ETL process does not exist!');
                        COMMIT;
                        RETURN;
END;

-- Check for errors
IF etl_process IS NULL OR process_name IS NULL THEN
        -- Process not found or NULL
        -- Not raising application errors at this point!
        -- raise_application_error(-20001, 'spMD_LogProcessStart ERROR: Null arguments or ETL process does not exist');
        spMD_WriteEventLog ('spMD_LogProcessStart', -1, 'Null arguments or ETL process does not exist!');
        COMMIT;
        RETURN;
END IF;

SELECT  count(*)
INTO    count_records
FROM    MD_ETLProcessRunInstance
WHERE   ETLProcess = parent_etl_process
AND             RunInstance = parent_etl_process_runinstance;

-- If a parent process is supplied, then a parentruninstance must be supplied
-- and they both must exist already in the MD_ETLProcessRunInstance table.
IF parent_etl_process IS NOT NULL THEN
        IF parent_etl_process_runinstance IS NULL OR count_records = 0 THEN
                -- Parent process not found
                -- Not raising application errors at this point!
        -- raise_application_error(-20001, 'spMD_LogProcessStart ERROR: Null parentprocess runinstance or parentprocess/runinstance does not exist!');
                spMD_WriteEventLog ('spMD_LogProcessStart', -1, 'Null parentprocess runinstance or parentprocess/runinstance does not exist!');
                COMMIT;
                RETURN;
    END IF;
END IF;

-- Set up message if not provided...
IF comments_var IS NULL OR trim(comments_var)= '' THEN
        IF lockableobject IS NULL THEN
                comments_var := process_name || ' started...';
        ELSE
                SELECT  process_name || ' (loads: ' || Name || ') started...' INTO comments_var
                FROM    MD_LockableObject
                WHERE   LockableObjectKey = lockableobject;
        END IF;
END IF;

-- Acquire a lock of this table so I can make sure nobody else does an insert until I do!
BEGIN
        LOCK TABLE MD_ETLPROCESSRUNINSTANCE IN EXCLUSIVE MODE;

        EXCEPTION
                WHEN OTHERS THEN
                        ROLLBACK;
                        v_sqlcode := SQLCODE;
                        -- Not raising application errors at this point!
                        -- raise_application_error(-20001, 'spMD_LogProcessStart ERROR: could not acquire lock of MD_ETLPROCESSRUNINSTANCE table.');
                        spMD_WriteEventLog ('spMD_LogProcessStart', v_sqlcode, section => 'Locking MD_ETLProcessRunInstance');
                        COMMIT;
                        RETURN;
END;

-- Look for the next run instance and lock tableMD_ETLProcessRunInstance
SELECT  MAX(RunInstance) + 1  INTO new_run_instance
FROM    MD_ETLProcessRunInstance
WHERE   ETLProcess = etl_process;

IF NVL (new_run_instance, -1) = -1 THEN
        new_run_instance := 1; --First time
END IF;

-- Let's do it!
SELECT USERENV('SESSIONID') INTO SPID FROM DUAL;

INSERT INTO MD_ETLProcessRunInstance VALUES (
        etl_process,
        new_run_instance,
        parent_etl_process,
        parent_etl_process_runinstance,
        1,
        comments_var,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        SPID,
        SYSTIMESTAMP,
        NULL);

COMMIT ;

new_run_instance := new_run_instance;
error_status := 0;

EXCEPTION
        WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                ROLLBACK;
                -- Not raising application errors at this point!
            -- raise_application_error(-20001, 'spMD_LogProcessStart ERROR: error inserting MD_ETLProcessMessage');
                spMD_WriteEventLog ('spMD_LogProcessStart', v_sqlcode, section => 'Inserting into MD_ETLProcessRunInstance');
                COMMIT;
                new_run_instance := -1;
                error_status := -1;

END spMD_LogProcessStart;



/*
        *****************************************************************************
        PROCEDURE: spMD_LockObject
        [ETL METADATA]
        *****************************************************************************
        Attempts to "lock" a LockableObject (hmmm, DUH)
        This procedure should be called prior to loading any LockableObject (standard
        fact tables and dimension tables), so that any other procedures that attempt
        to do the same thing can know there's somebody else updating that table.

        [RETURNS]       ERROR: Returns -1.

        [NOTE]          At this point I'm not raising application errors as this code
                                will only be called from within Oracle. If needed, just uncomment
                                the raise_application_error calls.

        *****************************************************************************
        [USED BY]       Should be used by every ETL process that performs an
                                incremental load of a standard fact table or a dimension
                                table.
        *****************************************************************************
        [SQL Server version by Laura Francheri 01/24/2002]
        [Oracle version by Venkat - revised by Laura on 09/10/2006]
        *****************************************************************************
        Change Log:

        *****************************************************************************
*/

PROCEDURE spMD_LockObject (
        userobject                  NUMBER,
        etl_process                 NUMBER,
        run_instance                NUMBER,
        force_lock                  NUMBER  DEFAULT 0, -- By default, do not force lock.
        last_sync_date_output   OUT TIMESTAMP,
        nonverbose                  NUMBER DEFAULT 0,
                error_status    OUT NUMBER)
AS
                PRAGMA AUTONOMOUS_TRANSACTION;

        error_message           VARCHAR2 (500);
        objectkey_control       NUMBER;
        object_name             VARCHAR2 (50);
        last_load_status        NUMBER;
        etlprocess_object       NUMBER;
        message                 VARCHAR2 (500);
        last_etl_process        NUMBER;
        last_run_instance       NUMBER;
        countRecords            NUMBER;
                last_sync_date  TIMESTAMP;
                v_sqlcode       NUMBER;
BEGIN

-- Initialize...
error_message := 'spMD_LockObject ERROR: ';
last_sync_date_output := NULL;
error_status := -1;

-- Check for parameter errors
IF userobject IS NULL OR etl_process IS NULL OR run_instance IS NULL THEN
        -- Process not found or NULL
        error_message := error_message || 'Null arguments';
        -- raise_application_error (-20001, error_message);
        spMD_WriteEventLog ('spMD_LockObject', -1, 'Null arguments!');
        COMMIT;
        RETURN;
END IF;

SELECT  COUNT(*)
INTO    countRecords
FROM    MD_ETLProcessRunInstance
WHERE   ETLProcess      = etl_process
AND             RunInstance     = run_instance;

-- Check that the process/runinstance exist in MD_ETLProcessRunInstance
IF countRecords = 0  THEN
        -- Invalid Process/RunInstance
        error_message := error_message || 'Invalid Process/RunInstance' ;
        --raise_application_error (-20001, error_message);
        spMD_WriteEventLog ('spMD_LockObject', -1, 'Invalid Process/RunInstance!');
        COMMIT;
        RETURN;
END IF;


-- Lock MD_LockableObject for update!
BEGIN
        -- Look up last sync date and status
        SELECT  LockableObjectKey,   
                Name,          
                LastObjectStatus,       
                LASTSYNCDATE,       
                LastETLPRocess,     
                LastRunInstance
        INTO    objectkey_control,  
                object_name,    
                last_load_status,       
                last_sync_date,     
                last_etl_process,   
                last_run_instance
        FROM MD_LockableObject
        WHERE LockableObjectKey = userobject
        FOR UPDATE OF LastObjectStatus;

        EXCEPTION
                WHEN OTHERS THEN
                        v_sqlcode := SQLCODE;
                        ROLLBACK;
                        -- Not raising application errors at this point!
                        -- raise_application_error(-20001, 'spMD_LogProcessStart ERROR: could not acquire lock of MD_ETLPROCESSRUNINSTANCE table.');
                        spMD_WriteEventLog ('spMD_LockObject', v_sqlcode, section => 'SELECT from MD_LockableObject FOR UPDATE');
                        error_message := error_message || 'DB error when doing SELECT from MD_LockableObject FOR UPDATE';
                        spMD_LogErrorMessage (etl_process,  run_instance, error_message);
                        COMMIT;
                        RETURN;
END;


-- Check that the Object exists
IF objectkey_control IS NULL THEN
        -- Object not found or NULL
        ROLLBACK; -- release the lock!
        error_message := error_message || 'Object ' || TO_CHAR (userobject) || ' does not exist';
        spMD_LogErrorMessage (etl_process, run_instance, error_message);
        --raise_application_error (-20001, error_message);
        spMD_WriteEventLog ('spMD_LockObject', -1, 'Object ' || TO_CHAR (userobject) || ' does not exist');
        COMMIT;
        RETURN;
END IF;


-- Check if it's the same Object specified in MD_ETLProcess. The process won't fail if they're different, but it
-- will issue a warning.
SELECT  LockableObjectLoaded INTO etlprocess_object
FROM    MD_ETLProcess
WHERE   ETLProcessKey = etl_process;

IF NVL(nonverbose, 0) <> 1 THEN
        IF etlProcess_object IS NULL THEN
                spMD_LogInfoMessage(etl_process, 
                                    run_instance, 
                                    'Warning: MD_ETLProcess.LockableObjectLoaded is NULL. Please update this field for reporting purposes.');
        ELSIF etlProcess_object <> userobject THEN
                spMD_LogInfoMessage(etl_process, 
                                    run_instance, 
                                    'Warning: MD_ETLProcess.LockableObjectLoaded is different from object parameter. Please update this field for reporting purposes.');
        END IF;
END IF;


-- Check the status of the Object to determine if it can be locked.
IF last_load_status = 1 THEN -- Loading
        ROLLBACK;
        error_message := error_message || 'Attempt to lock Object [' || object_name || '] FAILED: object is currently locked by process ('
        || TO_CHAR(last_etl_process) || '/'
        || TO_CHAR (last_run_instance) || ')';
        spMD_LogErrorMessage(etl_process, run_instance, error_message);
        COMMIT;
        RETURN;
END IF;

IF last_load_status = 2 AND force_lock = 0  THEN  -- Failed and not set to force the lock
        ROLLBACK;
        error_message := error_message || 
                         'Attempt to lock Object [' || 
                         object_name || 
                         '] FAILED: object''s last status is FAILED. Use spMD_ReleaseObject or call procedure with @force_lock = 1.';
        spMD_LogErrorMessage(etl_process,  run_instance, error_message);
        COMMIT;
        RETURN;
END IF;

-- Force lock?
IF NVL(nonverbose, 0) <> 1 THEN
        IF last_load_status = 2 AND force_lock = 1  THEN -- Failed. Lock will be forced.
                message := 'spMD_LockObject: last load for Object [' || object_name || '] failed. Attempting to force lock...';
                spMD_LogInfoMessage(etl_process,  run_instance, message);
        END IF;
END IF;


-- Everything OK! Lock object!
-----------------------------------------------------------------------------
BEGIN
        UPDATE  MD_LockableObject
        SET     LastObjectStatus        =  1,   -- Loading
                        LastETLProcess          =  etl_process,
                        LastRunInstance         =  run_instance,
                        UpdateDate                  =  SYSTIMESTAMP
        WHERE   LockableObjectKey       =  userobject;

        EXCEPTION
                WHEN OTHERS THEN
                        v_sqlcode := SQLCODE;
                        ROLLBACK;
                        error_message := error_message || 
                                         'updating MD_LockableObject while locking Object [' || 
                                         object_name || 
                                         ']. Object could not be locked.';
                        spMD_LogErrorMessage (etl_process,  run_instance, error_message);
                        spMD_WriteEventLog ('spMD_LockObject', v_sqlcode, section => 'Locking object');
                        COMMIT;
                        RETURN;
END;

-- Done!
COMMIT;

-- Update the process/runinstance to indicate the new status.
UPDATE  MD_ETLProcessRunInstance
SET     LockableObjectLoaded =  userobject,
                LockableObjectStatus =  1,      -- Running
                PreviousSyncDate         =  last_sync_date
WHERE   ETLProcess  = etl_process
AND     RunInstance = run_instance;

-- Everything OK...
IF NVL (nonverbose, 0) <> 1 THEN
        message := 'spMD_LockObject: Object [' || object_name || '] has been locked.';
    spMD_LogInfoMessage(etl_process, run_instance, message);
END IF;

-- Done!
COMMIT;

-- Return last synchronization date...
last_sync_date_output := last_sync_date;
error_status := 0;

RETURN;

EXCEPTION
        WHEN OTHERS THEN
                ROLLBACK;
                error_message := error_message || 
                                 'Error updating MD_ETLProcessRunInstance AFTER locking Object [' || 
                                 object_name || 
                                 ']. Please check this table.';
        spMD_LogErrorMessage(etl_process, run_instance, error_message);
                COMMIT;
        RETURN;

END spMD_LockObject;



/*
        *****************************************************************************
        PROCEDURE: spMD_ReleaseObject
        [ETL METADATA]
        *****************************************************************************
        Releases a LockableObject.
        This procedure should be called after processing (succesfully or not) any
        LockableObject that was previously locked using spMD_LockObject.
        Only the process that acquired the lock will be able to unlock it.

        [RETURNS]       ERROR: Returns -1.

        [NOTE]          At this point I'm not raising application errors as this code
                                will only be called from within Oracle. If needed, just uncomment
                                the raise_application_error calls.

        *****************************************************************************
        [USED BY]       Should be used by every ETL process that performs an
                                incremental load of a standard fact table or a dimension
                                table.
        *****************************************************************************
        [SQL Server version by Laura Francheri 08/08/2002]
        [Oracle version 09/20/2006]
        *****************************************************************************
        Change Log:

        *****************************************************************************
*/

PROCEDURE spMD_ReleaseObject (
                userobject           NUMBER,
                etl_process          NUMBER,
                run_instance         NUMBER,
                lastsyncdate         TIMESTAMP,
                has_error            NUMBER, -- 0=OK, 1=ERROR
                nonverbose           NUMBER DEFAULT 0,
                error_status    OUT  NUMBER)
AS
                PRAGMA AUTONOMOUS_TRANSACTION;

                error_var               NUMBER;
                rowcount_var            NUMBER;
                error_message           VARCHAR2 (500);
                objectkey_control       NUMBER;
                object_name             VARCHAR2 (50);
                new_load_status         NUMBER;
                new_sync_date           TIMESTAMP;
                old_sync_date           TIMESTAMP;
                etlprocess_object       NUMBER;
                message                 VARCHAR2 (500);
                lastetlprocess          NUMBER;
                lastruninstance         NUMBER;
                countRecords            NUMBER;
                v_sqlcode               NUMBER;

BEGIN

-- Initialize...
error_status := -1;
error_message := 'spMD_ReleaseObject ERROR: ';

-- Check for parameter errors
IF userobject IS NULL
OR etl_process IS NULL
OR run_instance IS NULL
OR has_error IS NULL
OR (has_error = 0 AND lastsyncdate IS NULL)
OR has_error NOT IN (1, 0) THEN
        -- Invalid arguments!

        error_message := error_message || 'Null or invalid arguments';
        -- raise_application_error (-20001, error_message);
        spMD_WriteEventLog ('spMD_ReleaseObject', -1, 'Null or invalid arguments!');
        COMMIT;
        RETURN;
END IF;

SELECT  COUNT(*)
INTO    countRecords
FROM    MD_ETLProcessRunInstance
WHERE   ETLProcess      = etl_process
AND     RunInstance     = run_instance;

-- Check that the process/runinstance exist in MD_ETLProcessRunInstance
IF countRecords = 0  THEN
        -- Invalid Process/RunInstance
        error_message := error_message || 'Invalid Process/RunInstance' ;
        --raise_application_error (-20001, error_message);
        spMD_WriteEventLog ('spMD_ReleaseObject', -1, 'Invalid Process/RunInstance!');
        COMMIT;
        RETURN;
END IF;

-- Look up object name and old sync date
SELECT  LockableObjectKey,  Name, LastSyncDate,   LastETLProcess,LastRunInstance
INTO    objectkey_control, object_name ,old_sync_date,  lastetlprocess,lastruninstance
FROM    MD_LockableObject
WHERE   LockableObjectKey = userobject;

-- Check that the Object exists!
IF objectkey_control IS NULL THEN
        error_message := error_message || 'Object ' || TO_CHAR (userobject) || ' does not exist';
        spMD_LogErrorMessage (etl_process, run_instance, error_message);
        --raise_application_error (-20001, error_message);
        spMD_WriteEventLog ('spMD_ReleaseObject', -1, 'Object ' || TO_CHAR (userobject) || ' does not exist');
        COMMIT;
        RETURN;
END IF;

-- Check if it's the same Object specified in MD_ETLProcess. The process won't fail if they're different, but it
-- will issue a warning.
SELECT  LockableObjectLoaded INTO etlprocess_object
FROM    MD_ETLProcess
WHERE   ETLProcessKey = etl_process;

IF NVL(nonverbose, 0) <> 1 THEN
        IF etlProcess_object IS NULL THEN
                spMD_LogInfoMessage(etl_process, 
                                    run_instance, 
                                    'Warning: MD_ETLProcess.LockableObjectLoaded is NULL. Please update this field for reporting purposes.');
        ELSIF etlProcess_object <> userobject THEN
                spMD_LogInfoMessage(etl_process, 
                                    run_instance, 
                                    'Warning: MD_ETLProcess.LockableObjectLoaded is different from object parameter. Please updatethis field for reporting purposes.');
        END IF;
END IF;

-- Check if this process is the same one that acquired the lock...
IF lastetlprocess <> etl_process OR lastruninstance <> run_instance THEN
        error_message := error_message || 
                         'Object [' || 
                        object_name || 
                        ']' || 
                        ' was locked by Proc/Inst (' || 
                        TO_CHAR(lastetlprocess ) || 
                        '/' || 
                        TO_CHAR(lastruninstance) || 
                        '). Release not authorized.';
    spMD_LogErrorMessage (etl_process, run_instance, error_message);
        --raise_application_error(-20001, error_message);
        spMD_WriteEventLog ('spMD_ReleaseObject', -1, error_message);
        COMMIT;
        RETURN;
END IF;

IF has_error = 0 THEN
    new_load_status := 0;   -- OK!
    new_sync_date := lastsyncdate;
ELSE
    new_load_status := 2;   -- FAILED!
    new_sync_date := old_sync_date;--Preserve it!
END IF;
-- OK, release object!
-----------------------------------------------------------------------------
BEGIN
        UPDATE  MD_LockableObject
        SET         LastObjectStatus    = new_load_status,
                        LastSyncDate            = new_sync_date,
                        LastETLProcess          = etl_process,
                        LastRunInstance         = run_instance,
                        UpdateDate                  = SYSTIMESTAMP
        WHERE   LockableObjectKey       = userobject;

        EXCEPTION
                WHEN OTHERS THEN
                        v_sqlcode := SQLCODE;
                        ROLLBACK;
                        error_message := error_message || ' Error updating MD_LockableObject while releasing Object ['
                                                         || object_name || ']. Object could not be released.';
                        spMD_LogErrorMessage (etl_process,  run_instance, error_message);
                        spMD_WriteEventLog ('spMD_ReleaseObject', v_sqlcode, section => 'Releasing object');
                        COMMIT;
                        RETURN;
END;
-- Done!
COMMIT;
-- Update the process/runinstance to indicate the new status.
UPDATE  MD_ETLProcessRunInstance
SET         LockableObjectLoaded        = userobject,
                LockableObjectStatus    = new_load_status,
                NewSyncDate                     = new_sync_date
WHERE   ETLProcess                  = etl_process
AND         RunInstance                 = run_instance;

-- Everything OK...
IF NVL (nonverbose, 0) <> 1 THEN
        message := 'spMD_ReleaseObject: Object [' || object_name || '] has been released.';
        spMD_LogInfoMessage(etl_process,run_instance,message);
END IF;
-- Done!
COMMIT;
-- Return last synchronization date...
error_status := 0;
RETURN;

EXCEPTION
        WHEN OTHERS THEN
                ROLLBACK;
                error_message := 'sqlcode : '||
                                 sqlcode||
                                 '--'||
                                 error_message || 
                                 'Error updating MD_ETLProcessRunInstance AFTER releasing Object [' || 
                                 object_name || 
                                 ']. Please check this table.';
        spMD_LogErrorMessage(etl_process, run_instance, error_message);
                COMMIT;
        RETURN;

END spMD_ReleaseObject;

/*
        *****************************************************************************
        PROCEDURE: spMD_LogProcessFinishError
        [ETL METADATA]
        *****************************************************************************
        Updates an entry in the Metadata table MD_ETLProcessRunInstance, indicating
        that the current RunInstance for this process has finished with an Error.

        [RETURNS]       ERROR: Returns -1.

        [NOTE]          At this point I'm not raising application errors as this code
                                will only be called from within Oracle. If needed, just uncomment
                                the raise_application_error calls.
                                The email code is commented out as we currently don't have email
                                support. If uncommented, it would need to be tested as well.

        *****************************************************************************
        [USED BY]       Should be used by every ETL process if there's an error.
        *****************************************************************************
        [SQL Server version by Laura Francheri 01/24/2002]
        [Oracle version by Venkat - revised by Laura on 09/15/2006]
        *****************************************************************************
        Change Log:

        *****************************************************************************
*/

PROCEDURE spMD_LogProcessFinishError (
                etl_process                        NUMBER,
                run_instance                       NUMBER,
                user_comments                      VARCHAR2,
                affected_rows                      NUMBER     DEFAULT NULL,
                error                              NUMBER     DEFAULT NULL,
                error_status                  OUT  NUMBER)
AS
                PRAGMA AUTONOMOUS_TRANSACTION;

                process_name                       VARCHAR2 (50);
                object_loaded                      NUMBER;
                object_name                        VARCHAR2 (50);
                comments_var                       VARCHAR2 (500);
                v_sqlcode                          NUMBER;
                sendemailonerror                   NUMBER;
                email_recipients                   VARCHAR2 (255);
                email_subject                      VARCHAR2 (255);
                msg                                VARCHAR2 (5000);
                control_etlprocess                 NUMBER;
                start_date                         TIMESTAMP;
                email_ret_val                      NUMBER;
BEGIN
-- Initialize...
error_status := -1;
BEGIN
        -- Process name and object being loaded
        SELECT  Name, LockableObjectLoaded, SendEmailOnError, EmailRecipients
        INTO    process_name, object_loaded, sendemailonerror, email_recipients
        FROM    MD_ETLProcess
        WHERE   ETLProcessKey = etl_process;

        -- StartDate and ETLProcess for control
        SELECT  etlprocess, startdate
        INTO    control_etlprocess, start_date
        FROM    MD_ETLProcessRunInstance
        WHERE   ETLProcess = etl_process
        AND             RunInstance = run_instance;

        EXCEPTION
                WHEN OTHERS THEN
                        v_sqlcode := SQLCODE;
                        spMD_WriteEventLog ('spMD_LogProcessFinishError', 
                                            v_sqlcode, 
                                            '(OTHERS) Null parameters or process/runinstance doesn''t exist!');
                        COMMIT;
                        RETURN;
END;

-- Check for errors
IF etl_process IS NULL
OR run_instance IS NULL
OR process_name IS NULL
OR control_etlprocess IS NULL  THEN
        -- Process not found or NULL
        -- raise_application_error(-20001, 'spMD_LogProcessFinishError ERROR: Null arguments or process/runinstance does not exist')
        spMD_WriteEventLog ('spMD_LogProcessFinishError', -1, 'Null parameters or process/runinstance doesn''t exist!');
        COMMIT;
    RETURN;
END IF;

IF user_comments IS NULL OR user_comments = '' THEN
     IF object_loaded IS NULL THEN
                        comments_var := process_name || ' FAILED!';
     ELSE
         -- Retrieve object name
           SELECT       NAME
           INTO         object_name
           FROM         MD_LockableObject
           WHERE        LockableObjectKey = object_loaded;

               comments_var := process_name || ' (loads: ' || object_name || ') FAILED!';
     END IF;
ELSE
         comments_var := user_comments;
END IF;

-- Update run instance!
BEGIN
        UPDATE  MD_ETLProcessRunInstance
        SET             status = 2, -- Error
                        comments = comments_var,
                        affectedrows = affected_rows,
                        enddate = SYSTIMESTAMP,
                        syserrorcode = error
        WHERE   etlprocess = etl_process
        AND             runinstance = run_instance;

        EXCEPTION
        WHEN OTHERS THEN
                        v_sqlcode := SQLCODE;
                        ROLLBACK;
                        -- Not raising application errors at this point!
                        -- raise_application_error(-20001, 'spMD_LogProcessFinishError ERROR: error updating MD_ETLProcessRunInstance');
                        spMD_WriteEventLog ('spMD_LogProcessFinishError', v_sqlcode, section => 'Updating process status in MD_ETLProcessRunInstance');
                        COMMIT;
                        RETURN;
END;

-- Okay!
COMMIT;
error_status := 0;

-- If requested, send email message
-----------------------------------------------------------------------------
   /*  IF sendemailonerror = 1  THEN
      -- Look for the Block Id, if available
        SELECT blockzk  INTO v_blockid
        FROM md_etlprocessruninstance
        WHERE etlprocess = etl_process AND runinstance = run_instance;

       blockid_msg := CASE NVL (v_blockid, 0)
            WHEN 0
               THEN ''
            ELSE '(Blockzk: ' || TO_CHAR (v_blockid) || ')'
         END;
   END IF;

   IF OBJECTS IS NULL
   THEN
      email_subject :=
                'ERROR: ' || process_name ||  ' FAILED!';
   ELSE
      -- Retrieve OBJECTS name
        SELECT NAME
        INTO email_subject
        FROM md_lockableOBJECT
        WHERE lockableOBJECTkey = OBJECTS;

      email_subject :=
            'ERROR: '
         || process_name
         || ' (loads: '
         || email_subject
         || ') '
         || blockid_msg
         || ' FAILED!';
   END IF;

   msg :=
         'This is an automatic message. Please do not reply.'
      || CHR (13)
      || CHR (13)
      || 'TheZone.spMD_LogProcessFinishError says:'
      || CHR (13)
      || CHR (13)
      || CHR (9)
      || usercomments
      || CHR (13)
      || CHR (13)
      || CHR (9)
      || '(*) '
      || TO_CHAR (NVL (affected_rows, 0))
      ||  ' rows were affected.'
      || CHR (13)
      || CHR (9)
      || '(*) '
      || 'StartDate: '
      || TO_CHAR (start_date)
      || ' - EndDate: '
      || TO_CHAR (start_date)
      || CHR (13)
      || CHR (9)
      || '(*) '
      || 'ETLProcess: '
      || TO_CHAR (etl_process)
      || ' - RunInstance: '
      || TO_CHAR (run_instance)
      || CASE blockid_msg
            WHEN ''
               THEN ''
            ELSE CHR (13) + CHR (9) + '(*) ' + TRIM (blockid_msg)
         END
      || CHR (13)
      || CHR (13)
      || CHR (9)
      || 'For details, run EXEC TheZone.dbo.spMD_ProcessReport '
      || TO_CHAR (etl_process)
      || ', '
      || TO_CHAR (run_instance)
      || CHR (13)
      || CHR (13)
      || 'Sorry! :(';

  -- EXEC @email_ret_val = master..xp_sendmail
  --          @recipients    = @email_recipients,
  --          @subject    = @email_subject,
  --          @message    = @msg,
  --          @no_output  = TRUE
   */

   -- Code for new Mail Service
   /*
   email_ret_val := spetl_aux_sendmail (sendmailto => email_recipients,
                          sendmailsubject      => email_subject,
                          sendmailmessage      => msg
                         );

   IF email_ret_val <> 0
   THEN
      spMD_LogErrorMessage(etl_process,run_instance,
                          'Warning: spETL_AUX_SendMail returned an error. This process is set to send mail on error.');
   END IF;
   */

END spMD_LogProcessFinishError;



/*
        *****************************************************************************
        PROCEDURE: spMD_LogProcessFinishOK
        [ETL METADATA]
        *****************************************************************************
        Updates an entry in the Metadata table MD_ETLProcessRunInstance, indicating
        that the current RunInstance for this process has finished OK.

        [RETURNS]       ERROR: Returns -1.

        [NOTE]          At this point I'm not raising application errors as this code
                                will only be called from within Oracle. If needed, just uncomment
                                the raise_application_error calls.
                                The email code is commented out as we currently don't have email
                                support. If uncommented, it would need to be tested as well.

        *****************************************************************************
        [USED BY]       Should be used by every ETL process upon succesful completion.
        *****************************************************************************
        [SQL Server version by Laura Francheri 01/24/2002]
        [Oracle version by Venkat - revised by Laura on 09/15/2006]
        *****************************************************************************
        Change Log:

        *****************************************************************************
*/

PROCEDURE spMD_LogProcessFinishOK (
                etl_process                          NUMBER,
                run_instance                         NUMBER,
                user_comments                        VARCHAR2,
                affected_rows                        NUMBER     DEFAULT NULL,
                error_status                    OUT  NUMBER)
AS
                PRAGMA AUTONOMOUS_TRANSACTION;

        process_name                                 VARCHAR2 (50);
        object_loaded                        NUMBER;
        object_name                          VARCHAR2 (50);
        comments_var                         VARCHAR2 (500);
        v_sqlcode                            NUMBER;
        sendemailonsuccess                   NUMBER;
        email_recipients                     VARCHAR2(255);
        email_subject                        VARCHAR2(255);
        msg                                  VARCHAR2(5000);
        control_etlprocess                   NUMBER;
        start_date                           TIMESTAMP;
        email_ret_val                        NUMBER;
BEGIN

-- Initialize...
error_status := -1;

BEGIN
        -- Process name and object being loaded
        SELECT  Name, LockableObjectLoaded, SendEmailOnSuccess, EmailRecipients
        INTO    process_name, object_loaded, sendemailonsuccess, email_recipients
        FROM    MD_ETLProcess
        WHERE   ETLProcessKey = etl_process;

        -- StartDate and ETLProcess for control
        SELECT  etlprocess, startdate
        INTO    control_etlprocess, start_date
        FROM    MD_ETLProcessRunInstance
        WHERE   ETLProcess = etl_process
        AND             RunInstance = run_instance;

        EXCEPTION
                WHEN OTHERS THEN
                        v_sqlcode := SQLCODE;
                        spMD_WriteEventLog ('spMD_LogProcessFinishOK', v_sqlcode, '(OTHERS) Null parameters or process/runinstance doesn''t exist!');
                        COMMIT;
                        RETURN;
END;


-- Check for errors
IF etl_process IS NULL
OR run_instance IS NULL
OR process_name IS NULL
OR control_etlprocess IS NULL  THEN
        -- Process not found or NULL
        -- raise_application_error(-20001, 'spMD_LogProcessFinishError ERROR: Null arguments or process/runinstance does not exist')
        spMD_WriteEventLog ('spMD_LogProcessFinishOK', -1, 'Null parameters or process/runinstance doesn''t exist!');
        COMMIT;
    RETURN;
END IF;

IF user_comments IS NULL OR user_comments = '' THEN
     IF object_loaded IS NULL THEN
                        comments_var := process_name || ' finished OK';
     ELSE
         -- Retrieve object name
           SELECT       NAME
           INTO         object_name
           FROM         MD_LockableObject
           WHERE        LockableObjectKey = object_loaded;

               comments_var := process_name || ' (loads: ' || object_name || ') finished OK';
     END IF;
ELSE
         comments_var := user_comments;
END IF;

-- Update run instance!
BEGIN
        UPDATE  MD_ETLProcessRunInstance
        SET             status = 0, -- Finished OK
                        comments = comments_var,
                        affectedrows = affected_rows,
                        enddate = SYSTIMESTAMP
        WHERE   etlprocess = etl_process
        AND             runinstance = run_instance;

        EXCEPTION
        WHEN OTHERS THEN
                        v_sqlcode := SQLCODE;
                        ROLLBACK;
                        -- Not raising application errors at this point!
                        -- raise_application_error(-20001, 'spMD_LogProcessFinishError ERROR: error updating MD_ETLProcessRunInstance');
                        spMD_WriteEventLog ('spMD_LogProcessFinishOK', v_sqlcode, section => 'Updating process status in MD_ETLProcessRunInstance');
                        COMMIT;
                        RETURN;
END;

-- Okay!
COMMIT;
error_status := 0;

-- If requested, send email message
-----------------------------------------------------------------------------
/*
      IF sendemailonsuccess = 1  THEN

        -- Look for the Block Id, if available


       SELECT   Blockzk INTO  block_id  FROM    MD_ETLProcessRunInstance
        WHERE   ETLProcess      = etl_process
        AND     RunInstance     = run_instance;

        blockid_msg :=  CASE nvl(block_id, 0)
                                WHEN 0  THEN    ''
                                ELSE            'BlockId: ' || to_char (block_id)
                                END;
        email_subject := 'Success: ' || usercomments ;

        msg := 'This is an automatic message. Please do not reply.'|| chr(13) || chr(13) ||
               'TheZone.spMD_LogProcessFinishOK says:'|| chr(13) || chr(13) || chr(9) || usercomments || '.'
            || chr(13) || chr(13) || chr(9) || '(*) ' || to_char (nvl(affected_rows, 0) ) || ' rows were affected.'
                        || chr(13) || chr(9) || '(*) ' || 'StartDate: ' || to_char (start_date)
            || ' - EndDate: ' || to_char (start_date)
                        || chr(13) || chr(9) || '(*) ' || 'ETLProcess: '
            || to_char (etl_process)
            || ' - RunInstance: ' || to_char (run_instance )
            || CASE blockid_msg         WHEN '' THEN ''
                           ELSE chr(13) || chr(9) || '(*) ' || blockid_msg
                           END  || chr(13) || chr(13) ||')' ;

END IF;


/*      EXEC @email_ret_val = master..xp_sendmail@recipients    = @email_recipients,@subject = @email_subject,
                        --@message= @msg,       @no_output      = TRUE



*/
/*
        email_ret_val := spETL_AUX_SendMail (sendmailto => email_recipients,
                                         sendmailsubject => email_subject,
                                         sendmailmessage => msg);

          IF email_ret_val <> 0 THEN
      spMD_LogErrorMessage (etl_process,run_instance, 'Warning: spETL_AUX_SendMail returned an error.
                         This process is set to send mail on success.');
             END IF;
*/

END spMD_LogProcessFinishOK;



/*
        *****************************************************************************
        PROCEDURE: spMD_LogErrorMessage
        [ETL METADATA]
        *****************************************************************************
        Inserts an Error Message in the Metadata table MD_ETLProcessMessage for a
        certain RunInstance.

        [RETURNS]       Nothing. Will save an EventLog if an error is found.

        [NOTE]          At this point I'm not raising application errors as this code
                                will only be called from within Oracle. If needed, just uncomment
                                the raise_application_error calls.

        *****************************************************************************
        [USED BY]       Any ETL processes, for logging purposes.
        *****************************************************************************
        [SQL Server version by Laura Francheri 01/24/2002]
        [Oracle version by Venkat - revised by Laura on 09/20/2006]
        *****************************************************************************
        Change Log:

        *****************************************************************************
*/

PROCEDURE spMD_LogErrorMessage (
                etl_process                     NUMBER,
                run_instance                    NUMBER,
                message                         VARCHAR2)
AS
                PRAGMA AUTONOMOUS_TRANSACTION;

        new_message_number              NUMBER;
        countRecords                    NUMBER;
        v_sqlcode                       NUMBER;

BEGIN

-- Check parameters
SELECT  count(*)
INTO    countRecords
FROM    MD_ETLProcessRunInstance
WHERE   ETLProcess = etl_process
AND             RunInstance = run_instance;

IF      etl_process IS NULL
OR      run_instance IS NULL
OR      message IS NULL
OR      countRecords = 0 THEN
        -- Process not found or NULL
        -- raise_application_error(-20001, 'spMD_LogErrorMessage ERROR: Null arguments or process/runinstance does not exist')
        spMD_WriteEventLog ('spMD_LogErrorMessage', -1, 'Null parameters or process/runinstance doesn''t exist!');
        COMMIT;
    RETURN;
END IF;

-- Acquire a lock of this table so I can make sure nobody else does an insert until I do!
BEGIN
        LOCK TABLE MD_ETLProcessMessage IN EXCLUSIVE MODE;

        EXCEPTION
                WHEN OTHERS THEN
                        v_sqlcode := SQLCODE;
                        ROLLBACK;
                        -- Not raising application errors at this point!
                        -- raise_application_error(-20001, 'spMD_LogErrorMessage ERROR: could not acquire lock of MD_ETLPROCESSRUNINSTANCE table.');
                        spMD_WriteEventLog ('spMD_LogErrorMessage', v_sqlcode, section => 'Locking MD_ETLProcessMessage');
                        COMMIT;
                        RETURN;
END;

-- Get the next message number
SELECT  MAX(MessageNumber) + 1
INTO    new_message_number
FROM    MD_ETLProcessMessage
WHERE   ETLProcess = etl_process
AND             RunInstance = run_instance;

IF      new_message_number IS NULL THEN
        new_message_number := 1;
END IF;

-- Insert the message!
INSERT INTO MD_ETLProcessMessage
VALUES (etl_process,
                run_instance,
                new_message_number,
                'ERROR',
                message,
                SYSTIMESTAMP);

COMMIT ;

EXCEPTION
        WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                ROLLBACK;
                -- Not raising application errors at this point!
            -- raise_application_error(-20001, 'spMD_LogErrorMessage ERROR: error inserting MD_ETLProcessMessage');
                spMD_WriteEventLog ('spMD_LogErrorMessage', v_sqlcode, section => 'Inserting into MD_ETLProcessMessage');
                COMMIT;

END spMD_LogErrorMessage;



/*
        *****************************************************************************
        PROCEDURE: spMD_LogInfoMessage
        [ETL METADATA]
        *****************************************************************************
        Inserts an info message in the Metadata table MD_ETLProcessMessage for a
        certain RunInstance.

        [RETURNS]       Nothing. Will save an EventLog if an error is found.

        [NOTE]          At this point I'm not raising application errors as this code
                                will only be called from within Oracle. If needed, just uncomment
                                the raise_application_error calls.

        *****************************************************************************
        [USED BY]       Any ETL processes, for logging purposes.
        *****************************************************************************
        [SQL Server version by Laura Francheri 01/24/2002]
        [Oracle version by Venkat - revised by Laura on 09/20/2006]
        *****************************************************************************
        Change Log:

        *****************************************************************************
*/

PROCEDURE spMD_LogInfoMessage (
             etl_process        NUMBER,
             run_instance       NUMBER,
             message            VARCHAR2)
AS
                PRAGMA AUTONOMOUS_TRANSACTION;

        new_message_number              NUMBER;
        countRecords                    NUMBER;
        v_sqlcode                       NUMBER;

BEGIN

-- Check parameters
SELECT  count(*)
INTO    countRecords
FROM    MD_ETLProcessRunInstance
WHERE   ETLProcess = etl_process
AND             RunInstance = run_instance;

IF      etl_process IS NULL
OR      run_instance IS NULL
OR      message IS NULL
OR      countRecords = 0 THEN
        -- Process not found or NULL
        -- raise_application_error(-20001, 'spMD_LogInfoMessage ERROR: Null arguments or process/runinstance does not exist')
        spMD_WriteEventLog ('spMD_LogInfoMessage', -1, 'Null parameters or process/runinstance doesn''t exist!');
        COMMIT;
    RETURN;
END IF;

-- Acquire a lock of this table so I can make sure nobody else does an insert until I do!
BEGIN
        LOCK TABLE MD_ETLProcessMessage IN EXCLUSIVE MODE;

        EXCEPTION
                WHEN OTHERS THEN
                        v_sqlcode := SQLCODE;
                        ROLLBACK;
                        -- Not raising application errors at this point!
                        -- raise_application_error(-20001, 'spMD_LogInfoMessage ERROR: could not acquire lock of MD_ETLPROCESSRUNINSTANCE table.');
                        spMD_WriteEventLog ('spMD_LogInfoMessage', v_sqlcode, section => 'Locking MD_ETLProcessMessage');
                        COMMIT;
                        RETURN;
END;

-- Get the next message number
SELECT  MAX(MessageNumber) + 1
INTO    new_message_number
FROM    MD_ETLProcessMessage
WHERE   ETLProcess = etl_process
AND             RunInstance = run_instance;

IF      new_message_number IS NULL THEN
        new_message_number := 1;
END IF;

-- Insert the message!
INSERT INTO MD_ETLProcessMessage
VALUES (etl_process,
                run_instance,
                new_message_number,
                'INFO',
                message,
                SYSTIMESTAMP);

COMMIT;

EXCEPTION
        WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                ROLLBACK;
                -- Not raising application errors at this point!
            -- raise_application_error(-20001, 'spMD_LogInfoMessage ERROR: error inserting MD_ETLProcessMessage');
                spMD_WriteEventLog ('spMD_LogInfoMessage', v_sqlcode, section => 'Inserting into MD_ETLProcessMessage');
                COMMIT;

END spMD_LogInfoMessage;



/*
        *****************************************************************************
        PROCEDURE: spMD_WriteEventLog
        [ETL METADATA]
        *****************************************************************************
        Inserts a record in MD_EventLog. This table is the "last resort" for metadata
        errors -- used only when metadata information cannot be saved in the usual
        MD tables. In most cases, this will be related to coding errors.

        [RETURNS]       Nothing.

        *****************************************************************************
        [USED BY]       Only Metadata procedures.
        *****************************************************************************
        [By Laura Francheri 09/19/2006]
        *****************************************************************************
        Change Log:

        *****************************************************************************
*/

PROCEDURE spMD_WriteEventLog (
        process_name    varchar2,
        error_code      number,
        error_message   varchar2 DEFAULT '',
        section         varchar2 DEFAULT ''
)

AS
        v_process_name  varchar2 (100);
        v_error_message varchar2 (2000);
        v_section       varchar2 (500);
BEGIN

    -- Get the error message, if not provided
    IF error_message IS NULL OR error_message = '' THEN
        v_error_message := SUBSTR (SQLERRM (error_code), 1, 2000);
    ELSE
        v_error_message := error_message;
    END IF;

    -- Process name:
    v_process_name := NVL (process_name, '[UNKNOWN PROCESS]');

    -- Insert!
    INSERT      INTO WFSDW.MD_EVENTLOG (
            ProcessName,                
            ErrorCode,                      
            ErrorMessage,                   
            Section, CreateDate)
    VALUES (v_process_name,             
            error_code,                     
            v_error_message,                
            section, 
            systimestamp);

EXCEPTION
        WHEN OTHERS THEN
        NULL;

END SPMD_WRITEEVENTLOG;

END;

/
