CREATE OR REPLACE PACKAGE WFSDW.PKG_DATALOAD
AS
/*
	*****************************************************************************
	PACKAGE: PKG_DATALOAD
	[ETL Data Load]

        -- @version $Id: createWFSload_DW.sql,v 1.4 2007/12/03 21:53:01 cscherlacher Exp $

	*****************************************************************************
	This package contains stored procedures and functions that load our Data
	Warehouse tables.
	*****************************************************************************
	[USED BY]	DW load
	*****************************************************************************
	[By Chris Scherlacher - 04/4/2007]
	*****************************************************************************
	Change Log:
	*****************************************************************************
*/

PROCEDURE spSYNC_WorkOrderStatus (
	parent_etl_process				int DEFAULT NULL,
	parent_run_instance				int DEFAULT NULL,
	force_lock						int DEFAULT 0,
	error_status				OUT	int);

PROCEDURE spSYNC_Template (
	parent_etl_process				int DEFAULT NULL,
	parent_run_instance				int DEFAULT NULL,
	force_lock						int DEFAULT 0,
	error_status				OUT	int);

PROCEDURE spSYNC_Node (
	parent_etl_process				int DEFAULT NULL,
	parent_run_instance				int DEFAULT NULL,
	force_lock						int DEFAULT 0,
	error_status				OUT	int);

PROCEDURE spSYNC_Resource (
	parent_etl_process				int DEFAULT NULL,
	parent_run_instance				int DEFAULT NULL,
	force_lock						int DEFAULT 0,
	error_status				OUT	int);

PROCEDURE spSYNC_ResourceGroup (
	parent_etl_process				int DEFAULT NULL,
	parent_run_instance				int DEFAULT NULL,
	force_lock						int DEFAULT 0,
	error_status				OUT	int);

  PROCEDURE spSYNC_VariableInstance (
    parent_etl_process              int DEFAULT NULL,
    parent_run_instance             int DEFAULT NULL,
    force_lock                      int DEFAULT 0,
    error_status                OUT	int);

  PROCEDURE spSYNC_WorkOrder (
    parent_etl_process              int DEFAULT NULL,
    parent_run_instance             int DEFAULT NULL,
    force_lock                      int DEFAULT 0,
    error_status                OUT	int);

  PROCEDURE spSYNC_Log (
    parent_etl_process              int DEFAULT NULL,
    parent_run_instance             int DEFAULT NULL,
    force_lock                      int DEFAULT 0,
    error_status                OUT	int);

  PROCEDURE spSYNC_MonitoredDriveHistory (
    parent_etl_process              int DEFAULT NULL,
    parent_run_instance             int DEFAULT NULL,
    force_lock                      int DEFAULT 0,
    error_status                OUT	int);

END;
/


CREATE OR REPLACE PACKAGE BODY WFSDW.PKG_DATALOAD
AS
/*
	*****************************************************************************
	PROCEDURE: spSYNC_WorkOrderStatus
	[ETL - MD Compliant]
	*****************************************************************************
	Loads dimension table DIM_WorkOrderStatus. (Type 1)

	*****************************************************************************
	[USED BY]	ETL - DW Load process
	*****************************************************************************
	[By Chris Scherlacher - 04/4/2007]
	*****************************************************************************
	Change Log:

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

    PROCEDURE spSYNC_WorkOrderStatus (
        parent_etl_process	int DEFAULT NULL,
        parent_run_instance	int DEFAULT NULL,
        force_lock		int DEFAULT 0,
        error_status		OUT	int)
    AS
        etl_process		int;
        run_instance		int;
        returned_error_status	int;
        lockable_object		int;
        procname		varchar2 (100);
        objectname		varchar2 (100);
        last_sync_date		timestamp;
        new_sync_date		timestamp;
        error_message		varchar2 (500);
        affected_rows		int;
        v_sqlcode		int;

    BEGIN

    -- Initialize...
    error_status := -1;
    procname := 'spSYNC_WorkOrderStatus';
    affected_rows := 0;

    -----------------------------------------------------------------------------
    -- [ETL METADATA INFO]
    -- Unique identifier for this ETL Process, DO NOT CHANGE!
    etl_process := 1;
    -----------------------------------------------------------------------------

    -----------------------------------------------------------------------------
    -- [ETL METADATA INITIALIZATION]
    -- Obtain a RunInstance for this ETL Process

    PKG_METADATA.spMD_LogProcessStart (	etl_process,
    					parent_etl_process,
					parent_run_instance,
					NULL,
					run_instance,
                                       	returned_error_status);


    IF run_instance < 1 OR returned_error_status <> 0 THEN

        PKG_METADATA.spMD_WriteEventLog (procname,
					-1,
					'METADATA ERROR: error in call to spMD_LogProcessStart / could not obtain a RunInstance. Exiting procedure!');

        RETURN;

    END IF;

    BEGIN
        -- Get the object ID in order to lock it...
        SELECT	LockableObjectLoaded, o.Name
        INTO	lockable_object, objectname
        FROM	WFSDW.MD_ETLProcess p
        JOIN	WFSDW.MD_LockableObject o
        ON (	o.LockableObjectKey = p.LockableObjectLoaded)
        WHERE	ETLProcessKey = etl_process;

        -- Not null, etc.
        IF lockable_object IS NULL OR objectname IS NULL THEN

            PKG_METADATA.spMD_LogProcessFinishError (   etl_process,
                					run_instance,
							'Couldn''t determine the LockableObjectLoaded!',
							affected_rows,
							NULL,
							returned_error_status);

            RETURN;

        END IF;

   EXCEPTION
            WHEN OTHERS THEN -- No data found, for instance...

                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                    					run_instance,
							'Couldn''t determine the LockableObjectLoaded!',
							affected_rows,
							NULL,
							returned_error_status);

                RETURN;

    END;

    -- ETL Metadata: lock this ETL Object!
    PKG_METADATA.spMD_LockObject (lockable_object,
                                  etl_process,
                                  run_instance,
                                  force_lock,
                                  last_sync_date,
                                  0,
                                  returned_error_status);

    IF returned_error_status <> 0 OR last_sync_date IS NULL THEN

        PKG_METADATA.spMD_LogProcessFinishError (etl_process,
                                                 run_instance,
                                                 'Unable to lock object ' || objectname || '! Exiting procedure...',
                                                 affected_rows,
                                                 NULL,
                                                 returned_error_status);

        RETURN;

    END IF;

    -----------------------------------------------------------------------------
    ---------------------
    -- Here we go!
    ---------------------
    -- Get the new sync date...

    new_sync_date := systimestamp-1/1440;

    -- Fill up the DIFF table!
    BEGIN

        DELETE FROM WFSDW.STG1_DIFF_WorkOrderStatus;

        INSERT INTO WFSDW.STG1_DIFF_WorkOrderStatus (ROWID_,
                                                     ProcessStatusId,
                                                     Name,
                                                     LastProdUpdateDate,
                                                     DIFFETLProcess,
                                                     DIFFRunInstance,
                                                     DIFFDate,
                                                     WFSCreateDate)
        SELECT STG1_DIFF_WorkOrderSTatus_SEQ.nextval,
               ProcessStatusId,
               Name,
               UpdateDate,
               etl_process,
               run_instance,
               new_sync_date,
               CreateDate
        FROM	WFS.TTV_ProcessStatus@WFS_PRODUCTIONDB
        WHERE	UpdateDate BETWEEN last_sync_date AND new_sync_date;

        IF sql%rowcount = 0 THEN

            -- Source table has no new or updated records. No point in continuing...
            PKG_METADATA.spMD_ReleaseObject (lockable_object,
                                             etl_process,
                                             run_instance,
                                             new_sync_date,
                                             0,
                                             0,
                                             returned_error_status);

            PKG_METADATA.spMD_LogProcessFinishOK (etl_process,
                                                  run_instance,
                                                  NULL,
                                                  0,
                                                  returned_error_status);

            COMMIT; -- Yay!
            error_status := 0;

            RETURN;

        END IF;

    EXCEPTION
            WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                error_message := procname || ': DB error loading STG_DIFF_WorkOrderStatus!';

                ROLLBACK;

                -- Release the object indicating that there was an error
                PKG_METADATA.spMD_ReleaseObject (lockable_object,
                                                 etl_process,
                                                 run_instance,
                                                 NULL,
                                                 1,
                                                 0,
                                                 returned_error_status);

                -- Close the etl process.
                PKG_METADATA.spMD_LogProcessFinishError (etl_process,
                                                         run_instance,
                                                         error_message,
                                                         0,
                                                         v_sqlcode,
                                                         returned_error_status);

                RETURN;

    END;

    -- Commit changes to DIFF table to facilitate the debugging process if there was an error -- typically, errors
    -- occur due to problematic data in STG_DIFF.

    COMMIT;

    ---------------------
    -- Merge data!
    ---------------------
    BEGIN

        MERGE INTO WFSDW.DIM_WorkOrderStatus d
        USING (SELECT *
               FROM WFSDW.STG1_DIFF_WorkOrderStatus
               ORDER BY ProcessStatusId) s
        ON (d.ProcessStatusId = s.ProcessStatusId)
        WHEN MATCHED THEN
                UPDATE SET  d.Name = s.Name,
                            d.LastProdUpdateDate = s.LastProdUpdateDate,
                            d.UpdateDate = systimestamp
        WHEN NOT MATCHED THEN
                INSERT (d.WorkOrderStatusWK,
                        d.ProcessStatusId,
                        d.Name,
                        d.WFSCreateDate,
                        d.LastProdUpdateDate,
                        d.LastETLProcess,
                        d.LastRunInstance,
                        d.CreateDate,
                        d.UpdateDate)
                VALUES (DIM_WorkOrderStatus_SEQ.nextval,
                        s.ProcessStatusId,
                        s.Name,
                        s.WFSCreateDate,
                        s.LastProdUpdateDate,
                        etl_process,
                        run_instance,
                        systimestamp,
                        systimestamp);

        affected_rows := sql%rowcount;

    EXCEPTION
       WHEN OTHERS THEN
         v_sqlcode := SQLCODE;
         error_message := procname || ': DB error merging dimension DIM_WorkOrderStatus!';

         ROLLBACK;

         -- Release the object indicating that there was an error
         PKG_METADATA.spMD_ReleaseObject (lockable_object,
                                          etl_process,
                                          run_instance,
                                          NULL,
                                          1,
                                          0,
                                          returned_error_status);
         -- Close the etl process.
         PKG_METADATA.spMD_LogProcessFinishError (etl_process,
                                                  run_instance,
                                                  error_message,
                                                  0,
                                                  v_sqlcode,
                                                  returned_error_status);

         RETURN;

    END;

        PKG_METADATA.spMD_ReleaseObject (lockable_object,
                                         etl_process,
                                         run_instance,
                                         new_sync_date,
                                         0,
                                         0,
                                         returned_error_status);

        PKG_METADATA.spMD_LogProcessFinishOK (etl_process,
                                              run_instance,
                                              NULL,
                                              affected_rows,
                                              returned_error_status);


        COMMIT; -- Yay!

        error_status := 0;

        RETURN;

    EXCEPTION
        WHEN OTHERS THEN
            v_sqlcode := SQLCODE;
            error_message := procname || ': Global exception handler -> unknown error has occurred!';
            ROLLBACK;
            -- Release the object indicating that there was an error
            PKG_METADATA.spMD_ReleaseObject (lockable_object,
                                             etl_process,
                                             run_instance,
                                              NULL,
                                              1,
                                              0,
                                              returned_error_status);

            -- Close the etl process.
            PKG_METADATA.spMD_LogProcessFinishError (etl_process,
                                                     run_instance,
                                                     error_message,
                                                     0,
                                                     v_sqlcode,
                                                     returned_error_status);

    END spSYNC_WorkOrderStatus;


/*
	*****************************************************************************
	PROCEDURE: spSYNC_Template
	[ETL - MD Compliant]
	*****************************************************************************
	Loads dimension table DIM_Template. (Type 1)

	*****************************************************************************
	[USED BY]	ETL - DW Load process
	*****************************************************************************
	[By Chris Scherlacher - 4/6/2007]
	*****************************************************************************
	Change Log:

	*****************************************************************************
*/
  -- i.e., Template
  PROCEDURE spSYNC_Template (
        parent_etl_process				int DEFAULT NULL,
        parent_run_instance				int DEFAULT NULL,
        force_lock						int DEFAULT 0,
        error_status				OUT	int)
    AS
        etl_process						int;
        run_instance					int;
        returned_error_status			int;
        lockable_object					int;
        procname						varchar2 (100);
        objectname						varchar2 (100);
        last_sync_date					timestamp;
        new_sync_date					timestamp;
        error_message					varchar2 (500);
        affected_rows					int;
        v_sqlcode						int;
  BEGIN

    -- Initialize...
    error_status := -1;
    procname := 'spSYNC_Template';
    affected_rows := 0;

    -----------------------------------------------------------------------------
    -- [ETL METADATA INFO]
    -- Unique identifier for this ETL Process, DO NOT CHANGE!
    etl_process := 5;
    -----------------------------------------------------------------------------

    -----------------------------------------------------------------------------
    -- [ETL METADATA INITIALIZATION]
    -- Obtain a RunInstance for this ETL Process
    PKG_METADATA.spMD_LogProcessStart (etl_process,
                                       parent_etl_process,
                                       parent_run_instance,
                                       NULL,
                                       run_instance,
                                       returned_error_status);

    IF run_instance < 1 OR  returned_error_status <> 0 THEN

        PKG_METADATA.spMD_WriteEventLog (procname,
                                         -1,
                                         'METADATA ERROR: error in call to spMD_LogProcessStart / could not obtain a RunInstance. Exiting procedure!');

        RETURN;

    END IF;

    BEGIN
        -- Get the object ID in order to lock it...
        SELECT	LockableObjectLoaded, o.Name
        INTO	lockable_object, objectname
        FROM	MD_ETLProcess p
        JOIN	MD_LockableObject o
        ON (	o.LockableObjectKey = p.LockableObjectLoaded)
        WHERE	ETLProcessKey = etl_process;

        -- Not null, etc.
        IF lockable_object IS NULL OR objectname IS NULL THEN

            PKG_METADATA.spMD_LogProcessFinishError (etl_process,
                                                     run_instance,
                                                     'Couldn''t determine the LockableObjectLoaded!',
                                                     affected_rows,
                                                     NULL,
                                                     returned_error_status);

            RETURN;

        END IF;

        EXCEPTION
            WHEN OTHERS THEN -- No data found, for instance...

                PKG_METADATA.spMD_LogProcessFinishError (etl_process,
                                                         run_instance,
                                                         'Couldn''t determine the LockableObjectLoaded!',
                                                         affected_rows,
                                                         NULL,
                                                         returned_error_status);

                RETURN;

        END;

    -- ETL Metadata: lock this ETL Object!
    PKG_METADATA.spMD_LockObject (lockable_object,
                                  etl_process,
                                  run_instance,
                                  force_lock,
                                  last_sync_date,
                                  0,
                                  returned_error_status);

    IF returned_error_status <> 0 OR last_sync_date IS NULL THEN

        PKG_METADATA.spMD_LogProcessFinishError (etl_process,
                                                 run_instance,
                                                 'Unable to lock object ' || objectname || '! Exiting procedure...',
                                                 affected_rows,
                                                 NULL,
                                                 returned_error_status);

        RETURN;

    END IF;
    -----------------------------------------------------------------------------

    ---------------------
    -- Here we go!
    ---------------------

    -- Get the new sync date...
    --new_sync_date := dateadd ('N', -1, systimestamp); -- 1 minute ago, just to be sure.
    new_sync_date := systimestamp-1/1440;

    -- Fill up the DIFF table!
    BEGIN

        DELETE FROM WFSDW.STG1_DIFF_Template;

        INSERT INTO WFSDW.STG1_DIFF_Template (ROWID_,
                                              ProcessDefinitionId,
                                              Name,
                                              ProcessDefinitionTypeId,
                                              ProcessDefinitionTypeName,
                                              StartNodeId,
                                              LastProdUpdateDate,
                                              DIFFETLProcess,
                                              DIFFRunInstance,
                                              DIFFDate,
                                              WFSCreateDate)
        SELECT	STG1_DIFF_TEMPLATE_SEQ.nextval,
                a.Id_,
                a.Name_,
                b.ProcessDefinitionTypeId,
                b.Name,
                a.startstate_,
                a.UpdateDate,
                etl_process,
                run_instance,
                new_sync_date,
                a.CreateDate
          FROM	WFS.JBPM_Processdefinition@WFS_PRODUCTIONDB a, WFS.TTV_ProcessDefinitionType@WFS_PRODUCTIONDB b
         WHERE	b.ProcessDefinitionTypeId = a.ProcessDefinitionTypeId
           AND  a.UpdateDate BETWEEN last_sync_date AND new_sync_date;

        IF sql%rowcount = 0 THEN

            -- Source table has no new or updated records. No point in continuing...
            PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                            etl_process,
                                            run_instance,
                                            new_sync_date,
                                            0,
                                            0,
                                            returned_error_status);

            PKG_METADATA.spMD_LogProcessFinishOK(etl_process,
                                                 run_instance,
                                                 NULL,
                                                 0,
                                                 returned_error_status);

            COMMIT; -- Yay!
            error_status := 0;

            RETURN;

        END IF;

     EXCEPTION
            WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                error_message := procname || ': DB error loading STG_DIFF_Template!';

                ROLLBACK;
                -- Release the object indicating that there was an error

                PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                etl_process,
                                                run_instance,
                                                NULL,
                                                1,
                                                0,
                                                returned_error_status);

                -- Close the etl process.
                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        error_message,
                                                        0,
                                                        v_sqlcode,
                                                        returned_error_status);

                RETURN;

    END;

    -- Commit changes to DIFF table to facilitate the debugging process if there was an error -- typically, errors
    -- occur due to problematic data in STG_DIFF.
    COMMIT;

    ---------------------
    -- Merge data!
    ---------------------
    BEGIN
        MERGE INTO WFSDW.DIM_Template d
        USING	(SELECT *
                 FROM WFSDW.STG1_DIFF_Template
                 ORDER BY ProcessDefinitionId) s
        ON (d.ProcessDefinitionId = s.ProcessDefinitionId)
        WHEN MATCHED THEN
                UPDATE SET	d.Name = s.Name,
                            d.ProcessDefinitionTypeId = s.ProcessDefinitionTypeId,
                            d.ProcessDefinitionTypeName= s.ProcessDefinitionTypeName,
                            d.StartNodeId= s.StartNodeId,
                            d.LastProdUpdateDate = s.LastProdUpdateDate,
                            d.UpdateDate = systimestamp
        WHEN NOT MATCHED THEN
                INSERT (d.templatewk,
                        d.ProcessDefinitionId,
                        d.Name,
                        d.ProcessDefinitionTypeId,
                        d.ProcessDefinitionTypeName,
                        d.StartNodeId,
                        d.WFSCreateDate,
                        d.LastProdUpdateDate,
                        d.LastETLProcess,
                        d.LastRunInstance,
                        d.CreateDate,
                        d.UpdateDate)
                VALUES (DIM_TEMPLATE_SEQ.nextval,
                        s.ProcessDefinitionId,
                        s.Name,
                        s.ProcessDefinitionTypeId,
                        s.ProcessDefinitionTypeName,
                        s.StartNodeId,
                        s.WFSCreateDate,
                        s.LastProdUpdateDate,
                        etl_process,
                        run_instance,
                        systimestamp,
                        systimestamp);

        affected_rows := sql%rowcount;

     EXCEPTION
            WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                error_message := procname || ': DB error merging dimension DIM_Template!';

                ROLLBACK;

                -- Release the object indicating that there was an error
                PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                etl_process,
                                                run_instance,
                                                NULL,
                                                1,
                                                0,
                                                returned_error_status);

                -- Close the etl process.
                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        error_message,
                                                        0,
                                                        v_sqlcode,
                                                        returned_error_status);

                RETURN;

     END;

        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        new_sync_date,
                                        0,
                                        0,
                                        returned_error_status);

        PKG_METADATA.spMD_LogProcessFinishOK(etl_process,
                                             run_instance,
                                             NULL,
                                             affected_rows,
                                             returned_error_status);

        COMMIT; -- Yay!
        error_status := 0;

        RETURN;

  EXCEPTION
     WHEN OTHERS THEN
        v_sqlcode := SQLCODE;
        error_message := procname || ': Global exception handler -> unknown error has occurred!';

        ROLLBACK;

         -- Release the object indicating that there was an error
        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        NULL,
                                        1,
                                        0,
                                        returned_error_status);

        -- Close the etl process.
        PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                run_instance,
                                                error_message,
                                                0,
                                                v_sqlcode,
                                                returned_error_status);

  END spSYNC_Template;  -- i.e., Template

/*
	*****************************************************************************
	PROCEDURE: spSYNC_Node
	[ETL - MD Compliant]
	*****************************************************************************
	Loads dimension table DIM_Node. (Type 1)

	*****************************************************************************
	[USED BY]	ETL - DW Load process
	*****************************************************************************
	[By Suresh Neravati - 10/03/2006]
	*****************************************************************************
	Change Log:

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

  PROCEDURE spSYNC_Node (
        parent_etl_process				int DEFAULT NULL,
        parent_run_instance				int DEFAULT NULL,
        force_lock						int DEFAULT 0,
        error_status				OUT	int)
  AS
        etl_process						int;
        run_instance					int;
        returned_error_status			int;
        lockable_object					int;
        procname						varchar2 (100);
        objectname						varchar2 (100);
        last_sync_date					timestamp;
        new_sync_date					timestamp;
        error_message					varchar2 (500);
        affected_rows					int;
        v_sqlcode						int;
		recs_not_inserted				int;
		recs_to_insert					int;
  BEGIN
        -- Initialize...
        error_status := -1;
        procname := 'spSYNC_Node';
        affected_rows := 0;

        -----------------------------------------------------------------------------
        -- [ETL METADATA INFO]
        -- Unique identifier for this ETL Process, DO NOT CHANGE!
        etl_process := 6;
        -----------------------------------------------------------------------------

        -----------------------------------------------------------------------------
        -- [ETL METADATA INITIALIZATION]
        -- Obtain a RunInstance for this ETL Process
        PKG_METADATA.spMD_LogProcessStart(etl_process,
                                          parent_etl_process,
                                          parent_run_instance,
                                          NULL,
                                          run_instance,
                                          returned_error_status);

        IF run_instance < 1 OR  returned_error_status <> 0 THEN

            PKG_METADATA.spMD_WriteEventLog(procname,
                                            -1,
                                            'METADATA ERROR: error in call to spMD_LogProcessStart / could not obtain a RunInstance. Exiting procedure!');

            RETURN;

        END IF;

        BEGIN

            -- Get the object ID in order to lock it...
            SELECT	LockableObjectLoaded, o.Name
            INTO	lockable_object, objectname
            FROM	MD_ETLProcess p
            JOIN	MD_LockableObject o
            ON (	o.LockableObjectKey = p.LockableObjectLoaded)
            WHERE	ETLProcessKey = etl_process;

            -- Not null, etc.
            IF lockable_object IS NULL OR objectname IS NULL THEN

                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        'Couldn''t determine the LockableObjectLoaded!',
                                                        affected_rows,
                                                        NULL,
                                                        returned_error_status);

                RETURN;

            END IF;

        EXCEPTION
            WHEN OTHERS THEN -- No data found, for instance...

                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        'Couldn''t determine the LockableObjectLoaded!',
                                                        affected_rows,
                                                        NULL,
                                                        returned_error_status);

                RETURN;

        END;

        -- ETL Metadata: lock this ETL Object!
        PKG_METADATA.spMD_LockObject(lockable_object,
                                     etl_process,
                                     run_instance,
                                     force_lock,
                                     last_sync_date,
                                     0,
                                     returned_error_status);

        IF returned_error_status <> 0 OR last_sync_date IS NULL THEN

            PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                    run_instance,
                                                    'Unable to lock object ' || objectname || '! Exiting procedure...',
                                                    affected_rows,
                                                    NULL,
                                                    returned_error_status);

            RETURN;

        END IF;
        -----------------------------------------------------------------------------

        ---------------------
        -- Here we go!
        ---------------------

        -- Get the new sync date...
        --new_sync_date := dateadd ('N', -1, systimestamp); -- 1 minute ago, just to be sure.
        new_sync_date := systimestamp-1/1440;

        -- Fill up the DIFF table!
        BEGIN

            DELETE	FROM WFSDW.STG1_DIFF_Node;

              INSERT INTO WFSDW.STG1_DIFF_Node(ROWID_,
                                             NodeId,
                                             NodeClass,
                                             Name,
                                             NodeIndex,
                                             TaskId,
                                             TaskName,
                                             IsStartNode,
                                             TaskControllerId,
                                             LastProdUpdateDate,
                                             DIFFETLProcess,
                                             DIFFRunInstance,
                                             DIFFDate,
                                             WFSCreateDate)
            SELECT  STG1_DIFF_Node_SEQ.nextval,
                    n.ID_ as NodeId,
                    DECODE (n.CLASS_,'D', 'DECISION',
                                     'E', 'END',
                                     'R', 'START',
                                     'Y', 'MANUAL',
                                     'Z', 'AUTOMATED',
                                     'UNKNOWN') as NodeClass,
                    n.NAME_ as NodeName,
                    n.NODECOLLECTIONINDEX_ as NodeIndex,
                    t.ID_ as TaskId,
                    t.NAME_ as TaskName,
                    DECODE(NVL(t.STARTSTATE_,-1),-1,0,1) as IsStartNode,
                    t.TASKCONTROLLER_ as TaskControllerId,
                    n.UpdateDate,
                    etl_process,
                    run_instance,
                    new_sync_date,
                    n.CreateDate
              FROM  WFS.JBPM_NODE@WFS_PRODUCTIONDB n
                LEFT JOIN WFS.JBPM_TASK@WFS_PRODUCTIONDB t
                  ON (t.TASKNODE_ = n.ID_ OR
                      t.STARTSTATE_ = n.ID_) 
             WHERE  n.UpdateDate BETWEEN last_sync_date AND new_sync_date;

/*            INSERT INTO WFSDW.STG1_DIFF_Node(ROWID_,
                                             NodeId,
                                             NodeClass,
                                             Name,
                                             NodeIndex,
                                             TaskId,
                                             TaskName,
                                             IsStartNode,
                                             TaskControllerId,
                                             LastProdUpdateDate,
                                             DIFFETLProcess,
                                             DIFFRunInstance,
                                             DIFFDate,
                                             WFSCreateDate)
            SELECT	STG1_DIFF_Node_SEQ.nextval,
                    NodeId,
                    NodeClass,
                    NodeName,
                    NodeIndex,
                    TaskId,
                    TaskName,
                    IsStartNode,
                    TaskControllerId,
                    UpdateDate,
                    etl_process,
                    run_instance,
                    new_sync_date,
                    CreateDate
              FROM  WFS.vDW_Node@WFS_PRODUCTIONDB
             WHERE  UpdateDate BETWEEN last_sync_date AND new_sync_date;
*/
        EXCEPTION
            WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                error_message := procname || ': DB error loading STG_DIFF_Node!';

                ROLLBACK;

                -- Release the object indicating that there was an error
                PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                etl_process,
                                                run_instance,
                                                NULL,
                                                1,
                                                0,
                                                returned_error_status);

                -- Close the etl process.
                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        error_message,
                                                        0,
                                                        v_sqlcode,
                                                        returned_error_status);

                RETURN;

        END;

        -- Commit changes to DIFF table to facilitate the debugging process if there was an error -- typically, errors
        -- occur due to problematic data in STG_DIFF.
        COMMIT;

        ---------------------
        -- Merge data!
        ---------------------
        BEGIN
            MERGE INTO WFSDW.DIM_Node d
            USING (SELECT *
                   FROM WFSDW.STG1_DIFF_Node
                   ORDER BY NodeId) s
            ON (d.NodeId = s.NodeId)
            WHEN MATCHED THEN
                    UPDATE SET	d.NodeClass = s.NodeClass,
                                d.Name = s.Name,
                                d.NodeIndex = s.NodeIndex,
                                d.TaskId = s.TaskId,
                                d.TaskName = s.TaskName,
                                d.IsStartNode = s.IsStartNode,
                                d.TaskControllerId = s.TaskControllerId,
                                d.LastProdUpdateDate = s.LastProdUpdateDate,
                                d.UpdateDate = systimestamp
            WHEN NOT MATCHED THEN
                    INSERT (d.NodeWK,
                            d.NodeId,
                            d.NodeClass,
                            d.Name,
                            d.NodeIndex,
                            d.TaskId,
                            d.TaskName,
                            d.IsStartNode,
                            d.TaskControllerId,
                            d.WFSCreateDate,
                            d.LastProdUpdateDate,
                            d.LastETLProcess,
                            d.LastRunInstance,
                            d.CreateDate,
                            d.UpdateDate)
                    VALUES (DIM_NODE_SEQ.nextval,
                            s.NodeId,
                            s.NodeClass,
                            s.Name,
                            s.NodeIndex,
                            s.TaskId,
                            s.TaskName,
                            s.IsStartNode,
                            s.TaskControllerId,
                            s.WFSCreateDate,
                            s.LastProdUpdateDate,
                            etl_process,
                            run_instance,
                            systimestamp,
                            systimestamp);

            affected_rows := sql%rowcount;

            EXCEPTION
                WHEN OTHERS THEN
                    v_sqlcode := SQLCODE;
                    error_message := procname || ': DB error merging dimension DIM_Node!';

                    ROLLBACK;

                    -- Release the object indicating that there was an error
                    PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                    etl_process,
                                                    run_instance,
                                                    NULL,
                                                    1,
                                                    0,
                                                    returned_error_status);

                    -- Close the etl process.
                    PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                            run_instance,
                                                            error_message,
                                                            0,
                                                            v_sqlcode,
                                                            returned_error_status);

                    RETURN;

        END;

        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        new_sync_date,
                                        0,
                                        0,
                                        returned_error_status);

        PKG_METADATA.spMD_LogProcessFinishOK(etl_process,
                                             run_instance,
                                             NULL,
                                             affected_rows,
                                             returned_error_status);

        COMMIT; -- Yay!
        error_status := 0;

        RETURN;

  EXCEPTION
    WHEN OTHERS THEN
        v_sqlcode := SQLCODE;
        error_message := procname || ': Global exception handler -> unknown error has occurred!';

        ROLLBACK;

        -- Release the object indicating that there was an error
        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        NULL,
                                        1,
                                        0,
                                        returned_error_status);

        -- Close the etl process.
        PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                run_instance,
                                                error_message,
                                                0,
                                                v_sqlcode,
                                                returned_error_status);

  END spSYNC_Node;

/*
	*****************************************************************************
	PROCEDURE: spSYNC_Resource
	[ETL - MD Compliant]
	*****************************************************************************
	Loads dimension table DIM_Resource.

	*****************************************************************************
	[USED BY]	ETL - DW Load process
	*****************************************************************************
	[By Suresh Neravati - 09/29/2006]
	*****************************************************************************
	Change Log:

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

  PROCEDURE spSYNC_Resource (
        parent_etl_process				int DEFAULT NULL,
        parent_run_instance				int DEFAULT NULL,
        force_lock						int DEFAULT 0,
        error_status				OUT	int)
  AS
        etl_process						int;
        run_instance					int;
        returned_error_status			int;
        lockable_object					int;
        procname						varchar2 (100);
        objectname						varchar2 (100);
        last_sync_date					timestamp;
        new_sync_date					timestamp;
        error_message					varchar2 (500);
        affected_rows					int;
        v_sqlcode						int;
		recs_not_inserted				int;
		recs_to_insert					int;
  BEGIN
        -- Initialize...
        error_status := -1;
        procname := 'spSYNC_Resource';
        affected_rows := 0;

        -----------------------------------------------------------------------------
        -- [ETL METADATA INFO]
        -- Unique identifier for this ETL Process, DO NOT CHANGE!
        etl_process := 3;
        -----------------------------------------------------------------------------

        -----------------------------------------------------------------------------
        -- [ETL METADATA INITIALIZATION]
        -- Obtain a RunInstance for this ETL Process
        PKG_METADATA.spMD_LogProcessStart(etl_process,
                                          parent_etl_process,
                                          parent_run_instance,
                                          NULL,
                                          run_instance,
                                          returned_error_status);

        IF run_instance < 1 OR  returned_error_status <> 0 THEN

            PKG_METADATA.spMD_WriteEventLog(procname,
                                            -1,
                                            'METADATA ERROR: error in call to spMD_LogProcessStart / could not obtain a RunInstance. Exiting procedure!');

            RETURN;

        END IF;

        BEGIN

            -- Get the object ID in order to lock it...
            SELECT	LockableObjectLoaded, o.Name
            INTO	lockable_object, objectname
            FROM	MD_ETLProcess p
            JOIN	MD_LockableObject o
            ON (	o.LockableObjectKey = p.LockableObjectLoaded)
            WHERE	ETLProcessKey = etl_process;

            -- Not null, etc.
            IF lockable_object IS NULL OR objectname IS NULL THEN

                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        'Couldn''t determine the LockableObjectLoaded!',
                                                        affected_rows,
                                                        NULL,
                                                        returned_error_status);

                RETURN;

            END IF;

        EXCEPTION
            WHEN OTHERS THEN -- No data found, for instance...

                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        'Couldn''t determine the LockableObjectLoaded!',
                                                        affected_rows,
                                                        NULL,
                                                        returned_error_status);

                RETURN;

        END;

        -- ETL Metadata: lock this ETL Object!
        PKG_METADATA.spMD_LockObject(lockable_object,
                                     etl_process,
                                     run_instance,
                                     force_lock,
                                     last_sync_date,
                                     0,
                                     returned_error_status);

        IF returned_error_status <> 0 OR last_sync_date IS NULL THEN

            PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                    run_instance,
                                                    'Unable to lock object ' || objectname || '! Exiting procedure...',
                                                    affected_rows,
                                                    NULL,
                                                    returned_error_status);

            RETURN;

        END IF;

        -----------------------------------------------------------------------------

        ---------------------
        -- Here we go!
        ---------------------

        -- Get the new sync date...
        --new_sync_date := dateadd ('N', -1, systimestamp); -- 1 minute ago, just to be sure.
        new_sync_date := systimestamp-1/1440;

        -- Fill up the DIFF table!
        BEGIN

            DELETE FROM WFSDW.STG1_DIFF_Resource;

            INSERT INTO WFSDW.STG1_DIFF_Resource(ROWID_,
                                                ResourceId,
                                                ResourceName,
                                                ResourceTypeId,
                                                ResourceTypeName,
                                                Isactive,
						WFSCreateDate,
                                                LastProdUpdateDate,
                                                DIFFETLProcess,
                                                DIFFRunInstance,
                                                DIFFDate)
	     SELECT  STG1_DIFF_RESOURCE_SEQ.nextval,
          	     v.ResourceId,
        	     v.ResourceName,
        	     v.ResourceTypeId,
        	     v.ResourceTypeName,
        	     v.Isactive,
        	     v.CreateDate,
        	     v.UpdateDate,
        	     etl_process,
        	     run_instance,
        	     new_sync_date
	     FROM (SELECT r.ResourceId,
             		  r.Name as ResourceName,
             		  rt.ResourceTypeId,
             		  rt.Name as ResourceTypeName,
             		  r.Isactive,
             		  r.CreateDate,
             		  r.UpdateDate
      		   FROM WFS.TTV_Resource@WFS_PRODUCTIONDB r
                     JOIN WFS.TTV_ResourceType@WFS_PRODUCTIONDB rt
                       ON r.ResourceTypeId = rt.ResourceTypeId) v
	     WHERE UpdateDate BETWEEN last_sync_date AND new_sync_date;

        EXCEPTION
            WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                error_message := procname || ': DB error loading STG_DIFF_Resource!';

                ROLLBACK;

                -- Release the object indicating that there was an error
                PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                etl_process,
                                                run_instance,
                                                NULL,
                                                1,
                                                0,
                                                returned_error_status);

                -- Close the etl process.
                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        error_message,
                                                        0,
                                                        v_sqlcode,
                                                        returned_error_status);

                RETURN;

        END;

        -- Commit changes to DIFF table to facilitate the debugging process if there was an error -- typically, errors
        -- occur due to problematic data in STG_DIFF.
        COMMIT;

        ---------------------
        -- Merge data!
        ---------------------
        BEGIN

            MERGE INTO WFSDW.DIM_Resource d
            USING (SELECT *
                   FROM WFSDW.STG1_DIFF_Resource
                   ORDER BY ResourceId) s
            ON      (d.ResourceId = s.ResourceId)
            WHEN MATCHED THEN
                    UPDATE SET	d.ResourceName = s.ResourceName,
                                d.ResourceTypeId=s.ResourceTypeId,
                                d.ResourceTypeName = s.ResourceTypeName,
                                d.Isactive=s.Isactive,
                                d.LastProdUpdateDate = s.LastProdUpdateDate,
                                d.UpdateDate = systimestamp
            WHEN NOT MATCHED THEN
                    INSERT (d.ResourceWK,
                            d.ResourceId,
                            d.ResourceName,
                            d.ResourceTypeId,
                            d.ResourceTypeName,
                            d.Isactive,
                            d.WFSCreateDate,
                            d.LastProdUpdateDate,
                            d.LastETLProcess,
                            d.LastRunInstance,
                            d.CreateDate,
                            d.UpdateDate)
                    VALUES (DIM_RESOURCE_SEQ.nextval,
                            s.ResourceId,
                            s.ResourceName,
                            s.ResourceTypeId,
                            s.ResourceTypeName,
                            s.Isactive,
                            s.WFSCreateDate,
                            s.LastProdUpdateDate,
                            etl_process,
                            run_instance,
                            systimestamp,
                            systimestamp);

            affected_rows := sql%rowcount;

            EXCEPTION
                WHEN OTHERS THEN
                    v_sqlcode := SQLCODE;
                    error_message := procname || ': DB error merging dimension DIM_Resource!';

                    ROLLBACK;

                    -- Release the object indicating that there was an error
                    PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                    etl_process,
                                                    run_instance,
                                                    NULL,
                                                    1,
                                                    0,
                                                    returned_error_status);

                    -- Close the etl process.
                    PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                            run_instance,
                                                            error_message,
                                                            0,
                                                            v_sqlcode,
                                                            returned_error_status);

                    RETURN;

        END;

        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        new_sync_date,
                                        0,
                                        0, returned_error_status);

        PKG_METADATA.spMD_LogProcessFinishOK(etl_process,
                                             run_instance,
                                             NULL,
                                             affected_rows,
                                             returned_error_status);

        COMMIT; -- Yay!
        error_status := 0;

        RETURN;

  EXCEPTION
    WHEN OTHERS THEN
        v_sqlcode := SQLCODE;
        error_message := procname || ': Global exception handler -> unknown error has occurred!';

        ROLLBACK;

        -- Release the object indicating that there was an error
        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        NULL,
                                        1,
                                        0,
                                        returned_error_status);

        -- Close the etl process.
        PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                run_instance,
                                                error_message,
                                                0,
                                                v_sqlcode,
                                                returned_error_status);

  END spSYNC_Resource;

/*
	*****************************************************************************
	PROCEDURE: spSYNC_ResourceGroup
	[ETL - MD Compliant]
	*****************************************************************************
	Loads dimension table DIM_ResourceGroup.

	*****************************************************************************
	[USED BY]	ETL - DW Load process
	*****************************************************************************
	[By Chris Scherlacher - 04/09/2007]
	*****************************************************************************
	Change Log:

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

  PROCEDURE spSYNC_ResourceGroup (
        parent_etl_process				int DEFAULT NULL,
        parent_run_instance				int DEFAULT NULL,
        force_lock						int DEFAULT 0,
        error_status				OUT	int)
    AS
        etl_process						int;
        run_instance					int;
        returned_error_status			int;
        lockable_object					int;
        procname						varchar2 (100);
        objectname						varchar2 (100);
        last_sync_date					timestamp;
        new_sync_date					timestamp;
        error_message					varchar2 (500);
        affected_rows					int;
        v_sqlcode						int;
		recs_not_inserted				int;
		recs_to_insert					int;
  BEGIN

    -- Initialize...
    error_status := -1;
    procname := 'spSYNC_ResourceGroup';
    affected_rows := 0;

    -----------------------------------------------------------------------------
    -- [ETL METADATA INFO]
    -- Unique identifier for this ETL Process, DO NOT CHANGE!
    etl_process := 4;
    -----------------------------------------------------------------------------

    -----------------------------------------------------------------------------
    -- [ETL METADATA INITIALIZATION]
    -- Obtain a RunInstance for this ETL Process
    PKG_METADATA.spMD_LogProcessStart(etl_process,
                                      parent_etl_process,
                                      parent_run_instance,
                                      NULL,
                                      run_instance,
                                      returned_error_status);

    IF run_instance < 1 OR  returned_error_status <> 0 THEN

        PKG_METADATA.spMD_WriteEventLog(procname,
                                        -1,
                                        'METADATA ERROR: error in call to spMD_LogProcessStart / could not obtain a RunInstance. Exiting procedure!');

        RETURN;

    END IF;

    BEGIN

        -- Get the object ID in order to lock it...
        SELECT	LockableObjectLoaded, o.Name
        INTO	lockable_object, objectname
        FROM	MD_ETLProcess p
        JOIN	MD_LockableObject o
        ON (	o.LockableObjectKey = p.LockableObjectLoaded)
        WHERE	ETLProcessKey = etl_process;

        -- Not null, etc.
        IF lockable_object IS NULL OR objectname IS NULL THEN

            PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                    run_instance,
                                                    'Couldn''t determine the LockableObjectLoaded!',
                                                    affected_rows,
                                                    NULL,
                                                    returned_error_status);

            RETURN;

        END IF;

        EXCEPTION
            WHEN OTHERS THEN -- No data found, for instance...

                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        'Couldn''t determine the LockableObjectLoaded!',
                                                        affected_rows,
                                                        NULL,
                                                        returned_error_status);

                RETURN;
    END;

    -- ETL Metadata: lock this ETL Object!
    PKG_METADATA.spMD_LockObject(lockable_object,
                                 etl_process,
                                 run_instance,
                                 force_lock,
                                 last_sync_date,
                                 0,
                                 returned_error_status);

    IF returned_error_status <> 0 OR last_sync_date IS NULL THEN

        PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                run_instance,
                                                'Unable to lock object ' || objectname || '! Exiting procedure...',
                                                affected_rows,
                                                NULL,
                                                returned_error_status);

        RETURN;

    END IF;

    -----------------------------------------------------------------------------
    -- Here we go!
    ---------------------
    -- Get the new sync date...
    --new_sync_date := dateadd ('N', -1, systimestamp); -- 1 minute ago, just to be sure.
    new_sync_date := systimestamp-1/1440;

    -- Fill up the DIFF table!

    BEGIN

        DELETE FROM WFSDW.STG1_DIFF_ResourceGroup;

        INSERT INTO WFSDW.STG1_DIFF_ResourceGroup(ROWID_,
                                                  ResourceGroupId,
                                                  ResourceGroupName,
                                                  ResourceTypeId,
                                                  ResourceTypeName,
                                                  AccessLevelId,
                                                  AccessLevelName,
                                                  WFSCreateDate,
                                                  LastProdUpdateDate,
                                                  DIFFETLProcess,
                                                  DIFFRunInstance,
                                                  DIFFDate)
        SELECT	STG1_DIFF_RESOURCEGROUP_SEQ.nextval,
                v.ResourceGroupId,
                v.ResourceGroupName,
                v.ResourceTypeId,
                v.ResourceTypeName,
                v.AccessLevelId,
                v.AccessLevelName,
                v.CreateDate,
                v.UpdateDate,
                etl_process,
                run_instance,
                new_sync_date
        FROM (SELECT rg.ResourceGroupId,
                     rg.Name as ResourceGroupName,
                     rt.ResourceTypeId,
                     rt.Name as ResourceTypeName,
                     a.AccessLevelId,
                     a.Name as AccessLevelName,
                     rg.CreateDate,
                     rg.UpdateDate
        FROM WFS.TTV_ResourceGroup@WFS_PRODUCTIONDB rg
          JOIN WFS.TTV_ResourceType@WFS_PRODUCTIONDB rt
            ON rg.ResourceTypeId = rt.ResourceTypeId
          JOIN WFS.TTV_AccessLevel@WFS_PRODUCTIONDB a
            ON rg.AccessLevelId = a.AccessLevelId) v
        WHERE	v.UpdateDate BETWEEN last_sync_date AND new_sync_date;

        EXCEPTION
            WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                error_message := procname || ': DB error loading STG_DIFF_ResourceGroup!';

                ROLLBACK;

                -- Release the object indicating that there was an error
                PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                etl_process,
                                                run_instance,
                                                NULL,
                                                1,
                                                0,
                                                returned_error_status);

                -- Close the etl process.
                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        error_message,
                                                        0,
                                                        v_sqlcode,
                                                        returned_error_status);

                RETURN;

    END;

    -- Commit changes to DIFF table to facilitate the debugging process if there was an error -- typically, errors
    -- occur due to problematic data in STG_DIFF.
    COMMIT;

    ---------------------
    -- Merge data!
    ---------------------
    BEGIN

        MERGE INTO WFSDW.DIM_ResourceGroup d
        USING (SELECT *
               FROM WFSDW.STG1_DIFF_ResourceGroup
               ORDER BY ResourceGroupId) s
        ON (d.ResourceGroupId = s.ResourceGroupId)
        WHEN MATCHED THEN
                UPDATE SET d.ResourceGroupName = s.ResourceGroupName,
                            d.ResourceTypeId = s.ResourceTypeId,
                            d.ResourceTypeName = s.ResourceTypeName,
                            d.AccesslevelId = s.AccesslevelId,
                            d.AccessLevelName = s.AccessLevelName,
                            d.LastProdUpdateDate = s.LastProdUpdateDate,
                            d.UpdateDate = systimestamp
        WHEN NOT MATCHED THEN
                INSERT (d.ResourceGroupWK,
                        d.ResourceGroupId,
                        d.ResourceGroupName,
                        d.ResourceTypeId,
                        d.ResourceTypeName,
                        d.AccesslevelId,
                        d.AccessLevelName,
                        d.WFSCreateDate,
                        d.LastProdUpdateDate,
                        d.LastETLProcess,
                        d.LastRunInstance,
                        d.CreateDate,
                        d.UpdateDate)
                VALUES (DIM_RESOURCEGROUP_SEQ.nextval,
                        s.ResourceGroupId,
                        s.ResourceGroupName,
                        s.ResourceTypeId,
                        s.ResourceTypeName,
                        s.AccesslevelId,
                        s.AccessLevelName,
                        s.WFSCreateDate,
                        s.LastProdUpdateDate,
                        etl_process,
                        run_instance,
                        systimestamp,
                        systimestamp);

        affected_rows := sql%rowcount;

        EXCEPTION
            WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                error_message := procname || ': DB error merging dimension DIM_ResourceGroup!';

                ROLLBACK;

                -- Release the object indicating that there was an error
                PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                etl_process,
                                                run_instance,
                                                NULL,
                                                1,
                                                0,
                                                returned_error_status);

                -- Close the etl process.
                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        error_message,
                                                        0,
                                                        v_sqlcode,
                                                        returned_error_status);

                RETURN;

    END;

    PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                    etl_process,
                                    run_instance,
                                    new_sync_date,
                                    0,
                                    0,
                                    returned_error_status);

    PKG_METADATA.spMD_LogProcessFinishOK(etl_process,
                                         run_instance,
                                         NULL,
                                         affected_rows,
                                         returned_error_status);

    COMMIT; -- Yay!
    error_status := 0;

    RETURN;

  EXCEPTION
    WHEN OTHERS THEN
        v_sqlcode := SQLCODE;
        error_message := procname || ': Global exception handler -> unknown error has occurred!';

        ROLLBACK;

        -- Release the object indicating that there was an error
        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        NULL,
                                        1,
                                        0,
                                        returned_error_status);

        -- Close the etl process.
        PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                run_instance,
                                                error_message,
                                                0,
                                                v_sqlcode,
                                                returned_error_status);

  END spSYNC_ResourceGroup;

/*
	*****************************************************************************
	PROCEDURE: spSYNC_VariableInstance
	[ETL - MD Compliant]
	*****************************************************************************
	Loads dimension table FACT_VariableInstance.

	*****************************************************************************
	[USED BY]	ETL - DW Load process
	*****************************************************************************
	[By Suresh Neravati - 10/05/2006]
	*****************************************************************************
	Change Log:

	*****************************************************************************
*/
  PROCEDURE spSYNC_VariableInstance (
        parent_etl_process				int DEFAULT NULL,
        parent_run_instance				int DEFAULT NULL,
        force_lock						int DEFAULT 0,
        error_status				OUT	int)
  AS
        etl_process						int;
        run_instance					int;
        returned_error_status			int;
        lockable_object					int;
        procname						varchar2 (100);
        objectname						varchar2 (100);
        last_sync_date					timestamp;
        new_sync_date					timestamp;
        error_message					varchar2 (500);
        affected_rows					int;
        v_sqlcode						int;
		recs_not_inserted				int;
		recs_to_insert					int;
  BEGIN
        -- Initialize...
        error_status := -1;
        procname := 'spSYNC_VariableInstance';
        affected_rows := 0;

        -----------------------------------------------------------------------------
        -- [ETL METADATA INFO]
        -- Unique identifier for this ETL Process, DO NOT CHANGE!
        etl_process := 9;
        -----------------------------------------------------------------------------

        -- [ETL METADATA INITIALIZATION]
        -- Obtain a RunInstance for this ETL Process
        PKG_METADATA.spMD_LogProcessStart(etl_process,
                                          parent_etl_process,
                                          parent_run_instance,
                                          NULL,
                                          run_instance,
                                          returned_error_status);

        IF run_instance < 1 OR  returned_error_status <> 0 THEN

            PKG_METADATA.spMD_WriteEventLog(procname,
                                            -1,
                                            'METADATA ERROR: error in call to spMD_LogProcessStart / could not obtain a RunInstance. Exiting procedure!');

            RETURN;

        END IF;

        BEGIN

            -- Get the object ID in order to lock it...
            SELECT	LockableObjectLoaded, o.Name
            INTO	lockable_object, objectname
            FROM	MD_ETLProcess p
            JOIN	MD_LockableObject o
            ON (	o.LockableObjectKey = p.LockableObjectLoaded)
            WHERE	ETLProcessKey = etl_process;

            -- Not null, etc.
            IF lockable_object IS NULL OR objectname IS NULL THEN

                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        'Couldn''t determine the LockableObjectLoaded!',
                                                        affected_rows,
                                                        NULL,
                                                        returned_error_status);

                RETURN;

            END IF;

        EXCEPTION
            WHEN OTHERS THEN -- No data found, for instance...

                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        'Couldn''t determine the LockableObjectLoaded!',
                                                        affected_rows,
                                                        NULL,
                                                        returned_error_status);

                RETURN;

        END;

        -- ETL Metadata: lock this ETL Object!
        PKG_METADATA.spMD_LockObject(lockable_object,
                                     etl_process,
                                     run_instance,
                                     force_lock,
                                     last_sync_date,
                                     0,
                                     returned_error_status);

        IF returned_error_status <> 0 OR last_sync_date IS NULL THEN

            PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                    run_instance,
                                                    'Unable to lock object ' || objectname || '! Exiting procedure...',
                                                    affected_rows,
                                                    NULL,
                                                    returned_error_status);

            RETURN;

        END IF;

        -----------------------------------------------------------------------------
        -- Get the new sync date...
        --new_sync_date := dateadd ('N', -1, systimestamp); -- 1 minute ago, just to be sure.
        new_sync_date := systimestamp-1/1440;

        -- Fill up the DIFF table!
        BEGIN

            DELETE FROM WFSDW.STG1_DIFF_VariableInstance;

            INSERT INTO WFSDW.STG1_DIFF_VariableInstance(ROWID_,
                                                         VariableInstanceName,
							 VariableInstanceValue,
							 WFSCreateDate,
                                                         DIFFETLProcess,
                                                         DIFFRunInstance,
                                                         DIFFDate)
	   SELECT STG1_DIFF_VariableInstance_SEQ.nextval,
		  VariableInstanceName,
		  VariableInstanceValue,
		  CREATEDATE,
		  etl_process,
                  run_instance,
                  new_sync_date
	   FROM (SELECT NAME_ AS VariableInstanceName,
       		   	DECODE (CLASS_, 'L', NULL, STRINGVALUE_) AS VariableInstanceValue,
       		   	MIN(CREATEDATE) AS CREATEDATE
	    	 FROM WFS.JBPM_VARIABLEINSTANCE@WFS_PRODUCTIONDB
	    	 WHERE NAME_ IN ('providerId','packageAssetId') AND
                       UpdateDate BETWEEN last_sync_date AND new_sync_date
	     	 GROUP BY NAME_, DECODE (CLASS_, 'L', NULL, STRINGVALUE_)
	    	 UNION
	    	 SELECT NAME_ AS VariableInstanceName,
       		 	to_char(DECODE (CLASS_, 'L', NVL (LONGVALUE_, DOUBLEVALUE_), NULL)) AS VariableInstanceValue,
       		   	MIN(CREATEDATE) AS CREATEDATE
	    	 FROM WFS.JBPM_VARIABLEINSTANCE@WFS_PRODUCTIONDB
	    	 WHERE NAME_ IN ('providerId','packageAssetId') AND
      		      (LONGVALUE_ IS NOT NULL OR
       		       DOUBLEVALUE_ IS NOT NULL) AND
                       UpdateDate BETWEEN last_sync_date AND new_sync_date
	    	 GROUP BY NAME_, DECODE (CLASS_, 'L', NVL (LONGVALUE_, DOUBLEVALUE_), NULL));

        EXCEPTION
            WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                error_message := procname || ': DB error loading STG_DIFF_VariableInstance!';

                ROLLBACK;

                -- Release the object indicating that there was an error
                PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                etl_process,
                                                run_instance,
                                                NULL,
                                                1,
                                                0,
                                                returned_error_status);

                -- Close the etl process.
                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        error_message,
                                                        0,
                                                        v_sqlcode,
                                                        returned_error_status);

                RETURN;

        END;

        -- Commit changes to DIFF table to facilitate the debugging process if there was an error -- typically, errors
        -- occur due to problematic data in STG_DIFF.
        COMMIT;

        ---------------------
        -- Merge data!
        ---------------------
        BEGIN

                    INSERT INTO DIM_VARIABLEINSTANCE(VariableInstanceWK,
                            	 		     VariableInstanceName,
                            	 	 	     VariableInstanceValue,
			    	 		     WFSCreateDate,
                            	 		     LastETLProcess,
                            	 		     LastRunInstance,
                            	 		     CreateDate,
						     UpdateDate)
                    SELECT  DIM_VARIABLEINSTANCE_SEQ.nextval,
                            VariableInstanceName,
                            VariableInstanceValue,
                            WFSCreateDate,
                            etl_process,
                            run_instance,
                            systimestamp,
                            systimestamp
		    FROM STG1_DIFF_VARIABLEINSTANCE
		    WHERE (VARIABLEINSTANCENAME,VARIABLEINSTANCEVALUE) NOT IN (SELECT VARIABLEINSTANCENAME,VARIABLEINSTANCEVALUE
                                                                               FROM DIM_VARIABLEINSTANCE);

            affected_rows := sql%rowcount;

            EXCEPTION
                WHEN OTHERS THEN
                    v_sqlcode := SQLCODE;
                    error_message := procname || ': DB error merging dimension DIM_VariableInstance!';

                    ROLLBACK;

                    -- Release the object indicating that there was an error
                    PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                    etl_process,
                                                    run_instance,
                                                    NULL,
                                                    1,
                                                    0,
                                                    returned_error_status);

                    -- Close the etl process.
                    PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                            run_instance,
                                                            error_message,
                                                            0,
                                                            v_sqlcode,
                                                            returned_error_status);

                    RETURN;

        END;

        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        new_sync_date,
                                        0,
                                        0,
                                        returned_error_status);

        PKG_METADATA.spMD_LogProcessFinishOK(etl_process,
                                             run_instance,
                                             NULL,
                                             affected_rows,
                                             returned_error_status);

        COMMIT; -- Yay!
        error_status := 0;

        RETURN;

  EXCEPTION
    WHEN OTHERS THEN
        v_sqlcode := SQLCODE;
        error_message := procname || ': Global exception handler -> unknown error has occurred!';

        ROLLBACK;

        -- Release the object indicating that there was an error
        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        NULL,
                                        1,
                                        0,
                                        returned_error_status);

        -- Close the etl process.
        PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                run_instance,
                                                error_message,
                                                0,
                                                v_sqlcode,
                                                returned_error_status);

  END spSYNC_VariableInstance;

/*
	*****************************************************************************
	PROCEDURE: spSYNC_WorkOrder
	[ETL - MD Compliant]
	*****************************************************************************
	Loads dimension table FACT_WorkOrder.

	*****************************************************************************
	[USED BY]	ETL - DW Load process
	*****************************************************************************
	[By Chris Scherlacher - 04/10/2007]
	*****************************************************************************
	Change Log:

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

  PROCEDURE spSYNC_WorkOrder (
        parent_etl_process				int DEFAULT NULL,
        parent_run_instance				int DEFAULT NULL,
        force_lock						int DEFAULT 0,
        error_status				OUT	int)
  AS
        etl_process						int;
        run_instance					int;
        returned_error_status			int;
        lockable_object					int;
        procname						varchar2 (100);
        objectname						varchar2 (100);
        last_sync_date					timestamp;
        new_sync_date					timestamp;
        error_message					varchar2 (500);
        affected_rows					int;
        v_sqlcode						int;
		recs_not_inserted				int;
		recs_to_insert					int;
  BEGIN
        -- Initialize...
        error_status := -1;
        procname := 'spSYNC_WorkOrder';
        affected_rows := 0;

        -----------------------------------------------------------------------------
        -- [ETL METADATA INFO]
        -- Unique identifier for this ETL Process, DO NOT CHANGE!
        etl_process := 7;
        -----------------------------------------------------------------------------

        -- [ETL METADATA INITIALIZATION]
        -- Obtain a RunInstance for this ETL Process
        PKG_METADATA.spMD_LogProcessStart(etl_process,
                                          parent_etl_process,
                                          parent_run_instance,
                                          NULL,
                                          run_instance,
                                          returned_error_status);

        IF run_instance < 1 OR  returned_error_status <> 0 THEN

            PKG_METADATA.spMD_WriteEventLog(procname,
                                            -1,
                                            'METADATA ERROR: error in call to spMD_LogProcessStart / could not obtain a RunInstance. Exiting procedure!');

            RETURN;

        END IF;

        BEGIN

            -- Get the object ID in order to lock it...
            SELECT	LockableObjectLoaded, o.Name
            INTO	lockable_object, objectname
            FROM	MD_ETLProcess p
            JOIN	MD_LockableObject o
            ON (	o.LockableObjectKey = p.LockableObjectLoaded)
            WHERE	ETLProcessKey = etl_process;

            -- Not null, etc.
            IF lockable_object IS NULL OR objectname IS NULL THEN

                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        'Couldn''t determine the LockableObjectLoaded!',
                                                        affected_rows,
                                                        NULL,
                                                        returned_error_status);

                RETURN;

            END IF;

        EXCEPTION
            WHEN OTHERS THEN -- No data found, for instance...

                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        'Couldn''t determine the LockableObjectLoaded!',
                                                        affected_rows,
                                                        NULL,
                                                        returned_error_status);

                RETURN;

        END;

        -- ETL Metadata: lock this ETL Object!
        PKG_METADATA.spMD_LockObject(lockable_object,
                                     etl_process,
                                     run_instance,
                                     force_lock,
                                     last_sync_date,
                                     0,
                                     returned_error_status);

        IF returned_error_status <> 0 OR last_sync_date IS NULL THEN

            PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                    run_instance,
                                                    'Unable to lock object ' || objectname || '! Exiting procedure...',
                                                    affected_rows,
                                                    NULL,
                                                    returned_error_status);

            RETURN;

        END IF;

        -----------------------------------------------------------------------------
        -- Get the new sync date...
        --new_sync_date := dateadd ('N', -1, systimestamp); -- 1 minute ago, just to be sure.
        new_sync_date := systimestamp-1/1440;

        -- Fill up the DIFF table!
        BEGIN

            DELETE	FROM WFSDW.STG1_DIFF_WorkOrder;

            INSERT INTO WFSDW.STG1_DIFF_WorkOrder(ROWID_,
                                                  ProcessInstanceId,
                                                  LocalStartDate,
                                                  LocalEndDate,
                                                  IssuSpended,
                                                  ProcessDefinitionId,
                                                  AdminStatusId,
                                                  OperStatusId,
                                                  PriorityId,
                                                  Priority,
						  ProviderName,
						  PackageAssetID,
						  Title,
						  BriefTitle,
						  AssetID,
						  ObjectType,
						  HouseMaterialID,
						  BroadcastMaterialID,
						  LicenseStart,
						  LicenseEnd,
                                                  WFSCreateDate,
                                                  LastProdUpdateDate,
                                                  DIFFETLProcess,
                                                  DIFFRunInstance,
                                                  DIFFDate)
SELECT STG1_DIFF_WorkOrder_SEQ.nextval,
	PROCESSINSTANCEID,
        LOCALSTARTDATE,
	LOCALENDDATE,
	ISSUSPENDED,
	PROCESSDEFINITIONID,
	ADMINSTATUSID,
	OPERSTATUSID,
	PRIORITYID,
	PRIORITY,
	PROVIDERNAME,
	PACKAGEASSETID,
	TITLE,
	BRIEFTITLE,
	ASSETID,
	OBJECTTYPE,
        HOUSEMATERIALID,
	BROADCASTMATERIALID,
 	LICENSESTART,
	LICENSEEND,
	CREATEDATE,
	UPDATEDATE,
	etl_process,
	run_instance,
	new_sync_date
FROM ( SELECT I.ID_ AS PROCESSINSTANCEID,
                    START_ AS LOCALSTARTDATE,
                    END_ AS LOCALENDDATE,
                    ISSUSPENDED_ AS ISSUSPENDED,
                    PROCESSDEFINITION_ AS PROCESSDEFINITIONID,
                    ADMINISTRATIVEPROCESSSTATUSID AS ADMINSTATUSID,
                    OPERATIONALPROCESSSTATUSID AS OPERSTATUSID,
                    I.PROCESSPRIORITYID PRIORITYID,
                    NVL (P.NAME, '[N/A]') AS PRIORITY,
                    max(CASE WHEN vi.Name_ = 'providerId' THEN vi.STRINGVALUE_ ELSE NULL END) AS ProviderName,
                    max(CASE WHEN vi.Name_ = 'packageAssetId' THEN vi.STRINGVALUE_ ELSE NULL END) AS PackageAssetID,
                    max(CASE WHEN vi.Name_ = 'title' THEN vi.STRINGVALUE_ ELSE NULL END) AS Title,
                    max(CASE WHEN vi.Name_ = 'titleBrief' THEN vi.STRINGVALUE_ ELSE NULL END) AS BriefTitle,
                    max(CASE WHEN vi.Name_ = 'assetId' THEN vi.STRINGVALUE_ ELSE NULL END) AS AssetID,
                    max(CASE WHEN vi.Name_ = 'type' THEN vi.STRINGVALUE_ ELSE NULL END) AS ObjectType,
    		    max(CASE WHEN vi.Name_ = 'houseMaterialId' THEN vi.STRINGVALUE_ ELSE NULL END) AS HouseMaterialID,
		    max(CASE WHEN vi.Name_ = 'broadcastMaterialId' THEN vi.STRINGVALUE_ ELSE NULL END) AS BroadcastMaterialID,
                    max(CASE WHEN vi.Name_ = 'licStart' THEN vi.STRINGVALUE_ ELSE NULL END) AS LicenseStart,
                    max(CASE WHEN vi.Name_ = 'licEnd' THEN vi.STRINGVALUE_ ELSE NULL END) AS LicenseEnd,
		    I.CREATEDATE,
                    I.UPDATEDATE
             FROM WFS.JBPM_PROCESSINSTANCE@WFS_PRODUCTIONDB I
                  JOIN  WFS.JBPM_PROCESSDEFINITION@WFS_PRODUCTIONDB D
                    ON (        D.ID_ = PROCESSDEFINITION_)
                  JOIN  WFS.TTV_ProcessDefinitionType@WFS_PRODUCTIONDB dt
                    ON (        dt.ProcessDefinitionTypeId = d.ProcessDefinitionTypeId)
                  LEFT JOIN WFS.TTV_PROCESSPRIORITY@WFS_PRODUCTIONDB P
                    ON (        P.PROCESSPRIORITYID = I.PROCESSPRIORITYID)
                  JOIN  WFS.TTV_PROCESSSTATUS@WFS_PRODUCTIONDB ADS
                    ON (        ADS.PROCESSSTATUSID@WFS_PRODUCTIONDB = ADMINISTRATIVEPROCESSSTATUSID)
                  JOIN  WFS.TTV_PROCESSSTATUS@WFS_PRODUCTIONDB OPS
                    ON (        OPS.PROCESSSTATUSID = OPERATIONALPROCESSSTATUSID)
                  JOIN JBPM_VARIABLEINSTANCE@WFS_PRODUCTIONDB vi
                    ON ( I.ID_ = VI.PROCESSINSTANCE_) AND
                       (vi.NAME_ IN ('providerId',
				     'packageAssetId',
				     'title',
				     'titleBrief',
				     'assetId',
				     'type',
				     'houseMaterialId',
				     'broadcastMaterialId',
				     'licStart',
				     'licEnd')) AND
 		       (vi.TASKINSTANCE_ is null)
               WHERE PROCESSINSTANCETYPEID <> 1 AND
		     UpdateDate BETWEEN last_sync_date AND new_sync_date
               GROUP BY I.ID_,
                       START_,
                       END_,
                       ISSUSPENDED_,
                       PROCESSDEFINITION_,
                       ADS.NAME,
                       ADMINISTRATIVEPROCESSSTATUSID,
                       OPS.NAME,
                       OPERATIONALPROCESSSTATUSID,
                       I.PROCESSPRIORITYID,
                       NVL(P.NAME, '[N/A]'),
		       I.CREATEDATE,
		       I.UPDATEDATE
		ORDER BY I.ID_);


         -- Lookup the TemplateWK field from DIM_Template table data.
            UPDATE WFSDW.STG1_DIFF_WorkOrder a
            SET a.TemplateWK= (SELECT b.TemplateWK
                               FROM DIM_Template b
                               WHERE b.ProcessDefinitionId = a.ProcessDefinitionId)
            WHERE  WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Lookup the ProviderWK from DIM_VariableInstance table data.
            UPDATE WFSDW.STG1_DIFF_WORKORDER a
            SET a.ProviderWK = (SELECT b.VariableInstanceWK
                                FROM DIM_PROVIDER b
                                WHERE b.ProviderName = a.ProviderName)
            WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Set the LocalStartDateWK
            UPDATE WFSDW.STG1_DIFF_WORKORDER
            SET LocalStartDateWK = to_number(to_char(LocalStartDate,'YYYYMMDD'))
            WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Lookup the LocalStartTimeWK
            UPDATE WFSDW.STG1_DIFF_WORKORDER
            SET LocalStartTimeWK = to_number(to_char(LocalStartDate,'HH24MISS'))
            WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

	 -- Convert License Dates
            UPDATE WFSDW.STG1_DIFF_WORKORDER
	    SET LicenseStartDate = decode(substr(LicenseStart,5,1),'-',to_date(LicenseStart,'RRRR-MM-DD'),to_date(LicenseStart,'MM/DD/RRRR')),
		LicenseEndDate = decode(substr(LicenseEnd,5,1),'-',to_date(LicenseEnd,'RRRR-MM-DD'),to_date(LicenseEnd,'MM/DD/RRRR'));

	-- Lookup the AdminStatusWK
	   UPDATE WFSDW.STG1_DIFF_WORKORDER a
	   SET a.AdminStatusWK = (SELECT b.WorkOrderStatusWK
				  FROM DIM_WORKORDERSTATUS b
				  WHERE b.ProcessStatusID = a.AdminStatusID)
	   WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

	-- Lookup the OperStatusWK
	   UPDATE WFSDW.STG1_DIFF_WORKORDER a
	   SET a.OperStatusWK = (SELECT b.WorkOrderStatusWK
				 FROM DIM_WORKORDERSTATUS b
				 WHERE b.ProcessStatusID = a.OperStatusID)
	   WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

        EXCEPTION
            WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                error_message := procname || ': DB error loading STG_DIFF_WorkOrder!';

                ROLLBACK;

                -- Release the object indicating that there was an error
                PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                etl_process,
                                                run_instance,
                                                NULL,
                                                1,
                                                0,
                                                returned_error_status);

                -- Close the etl process.
                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        error_message,
                                                        0,
                                                        v_sqlcode,
                                                        returned_error_status);

                RETURN;

        END;

        -- Commit changes to DIFF table to facilitate the debugging process if there was an error -- typically, errors
        -- occur due to problematic data in STG_DIFF.
        COMMIT;

        BEGIN

        ---------------------
        -- Merge Dimension data!
        ---------------------

            MERGE INTO WFSDW.DIM_WorkOrder d
            USING	(SELECT *
                     FROM WFSDW.STG1_DIFF_WorkOrder
                     ORDER BY ProcessInstanceId) s
            ON (d.ProcessInstanceId = s.ProcessInstanceId)
            WHEN MATCHED THEN
                    UPDATE SET	d.Suspended = decode(s.IssuSpended,1,'YES','NO'),
                                d.Priority = s.Priority,
                                d.localstartdate = s.localstartdate,
                                d.localenddate = s.localenddate,
				d.PackageAssetID = s.PackageAssetID,
				d.AssetID = s.AssetID,
				d.AssetType = s.ObjectType,
				d.Title = s.Title,
				d.BriefTitle = s.BriefTitle,
				d.HouseMaterialID = s.HouseMaterialID,
				d.BroadcastMaterialID = s.BroadcastMaterialID,
				d.LicenseStartDate = s.LicenseStartDate,
				d.LicenseEndDate = s.LicenseEndDate,
                                d.LastProdUpdateDate = s.LastProdUpdateDate,
				d.LastETLProcess = etl_process,
				d.LastRunInstance = run_instance,
                                d.UpdateDate = systimestamp
            WHEN NOT MATCHED THEN
                    INSERT (d.WorkOrderWK,
                            d.ProcessInstanceId,
                            d.Suspended,
                            d.Priority,
                            d.localstartdate,
                            d.localenddate,
			    d.PackageAssetID,
			    d.AssetID,
			    d.AssetType,
			    d.Title,
			    d.BriefTitle,
			    d.HouseMaterialID,
			    d.BroadcastMaterialID,
			    d.LicenseStartDate,
			    d.LicenseEndDate,
                            d.WFSCreateDate,
                            d.LastProdUpdateDate,
                            d.LastETLProcess,
                            d.LastRunInstance,
                            d.CreateDate,
                            d.UpdateDate)
                    VALUES (DIM_WorkOrder_SEQ.nextval,
                            s.ProcessInstanceId,
                            decode(s.IssuSpended,1,'YES','NO'),
                            s.Priority,
                            s.localstartdate,
                            s.localenddate,
			    s.PackageAssetID,
			    s.AssetID,
			    s.ObjectType,
			    s.Title,
			    s.BriefTitle,
			    s.HouseMaterialID,
			    s.BroadcastMaterialID,
			    s.LicenseStartDate,
			    s.LicenseEndDate,
                            s.WFSCreateDate,
                            s.LastProdUpdateDate,
                            etl_process,
                            run_instance,
                            systimestamp,
                            systimestamp);

            affected_rows := sql%rowcount;

        ---------------------
        -- Load Fact data!
        ---------------------

            INSERT INTO FACT_WORKORDERDETAIL(WorkOrderWK,
                                             TemplateWK,
                                             ProviderWK,
                                             LocalStartDateWK,
                                             LocalStartTimeWK,
					     AdminStatusWK,
				             OperStatusWK,
                                             WFSCreateDate,
					     LastProdUpdateDate,
                                             LastETLProcess,
                                             LastRunInstance,
                                             CreateDate,
                                             UpdateDate)
            SELECT dim.WorkOrderWK,
                   stg.TemplateWK,
                   stg.ProviderWK,
                   stg.LocalStartDateWK,
                   stg.LocalStartTimeWK,
		   stg.AdminStatusWK,
		   stg.OperStatusWK,
                   stg.WFSCreateDate,
		   stg.LastProdUpdateDate,
		   etl_process,
                   run_instance,
                   systimestamp,
                   systimestamp
	    FROM WFSDW.STG1_DIFF_WORKORDER stg
                 JOIN WFSDW.DIM_WORKORDER dim
                   ON (dim.ProcessInstanceId = stg.ProcessInstanceId)
            WHERE stg.WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

            EXCEPTION
                WHEN OTHERS THEN
                    v_sqlcode := SQLCODE;
                    error_message := procname || ': DB error loading WorkOrder Data!';

                    ROLLBACK;

                    -- Release the object indicating that there was an error
                    PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                    etl_process,
                                                    run_instance,
                                                    NULL,
                                                    1,
                                                    0,
                                                    returned_error_status);

                    -- Close the etl process.
                    PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                            run_instance,
                                                            error_message,
                                                            0,
                                                            v_sqlcode,
                                                            returned_error_status);

                    RETURN;

        END;

        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        new_sync_date,
                                        0,
                                        0,
                                        returned_error_status);

        PKG_METADATA.spMD_LogProcessFinishOK(etl_process,
                                             run_instance,
                                             NULL,
                                             affected_rows,
                                             returned_error_status);

        COMMIT; -- Yay!
        error_status := 0;

  EXCEPTION
    WHEN OTHERS THEN
        v_sqlcode := SQLCODE;
        error_message := procname || ': Global exception handler -> unknown error has occurred!';

        ROLLBACK;

        -- Release the object indicating that there was an error
        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        NULL,
                                        1,
                                        0,
                                        returned_error_status);

        -- Close the etl process.
        PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                run_instance,
                                                error_message,
                                                0,
                                                v_sqlcode,
                                                returned_error_status);

  END spSYNC_WorkOrder;

/*
	*****************************************************************************
	PROCEDURE: spSYNC_Log
	[ETL - MD Compliant]
	*****************************************************************************
	Loads dimension table FACT_Log.

	*****************************************************************************
	[USED BY]	ETL - DW Load process
	*****************************************************************************
	[By Chris Scherlacher - 04/10/2007]
	*****************************************************************************
	Change Log:

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

  PROCEDURE spSYNC_Log (
        parent_etl_process				int DEFAULT NULL,
        parent_run_instance				int DEFAULT NULL,
        force_lock						int DEFAULT 0,
        error_status				OUT	int)
  AS
        etl_process						int;
        run_instance					int;
        returned_error_status			int;
        lockable_object					int;
        procname						varchar2 (100);
        objectname						varchar2 (100);
        last_sync_date					timestamp;
        new_sync_date					timestamp;
        error_message					varchar2 (500);
        affected_rows					int;
        v_sqlcode						int;
		recs_not_inserted				int;
		recs_to_insert					int;
  BEGIN
        -- Initialize...
        error_status := -1;
        procname := 'spSYNC_Log';
        affected_rows := 0;

        -----------------------------------------------------------------------------
        -- [ETL METADATA INFO]
        -- Unique identifier for this ETL Process, DO NOT CHANGE!
        etl_process := 10;
        -----------------------------------------------------------------------------

        -- [ETL METADATA INITIALIZATION]
        -- Obtain a RunInstance for this ETL Process
        PKG_METADATA.spMD_LogProcessStart(etl_process,
                                          parent_etl_process,
                                          parent_run_instance,
                                          NULL,
                                          run_instance,
                                          returned_error_status);

        IF run_instance < 1 OR  returned_error_status <> 0 THEN

            PKG_METADATA.spMD_WriteEventLog(procname,
                                            -1,
                                            'METADATA ERROR: error in call to spMD_LogProcessStart / could not obtain a RunInstance. Exiting procedure!');

            RETURN;

        END IF;

        BEGIN

            -- Get the object ID in order to lock it...
            SELECT	LockableObjectLoaded, o.Name
            INTO	lockable_object, objectname
            FROM	MD_ETLProcess p
            JOIN	MD_LockableObject o
            ON (	o.LockableObjectKey = p.LockableObjectLoaded)
            WHERE	ETLProcessKey = etl_process;

            -- Not null, etc.
            IF lockable_object IS NULL OR objectname IS NULL THEN

                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        'Couldn''t determine the LockableObjectLoaded!',
                                                        affected_rows,
                                                        NULL,
                                                        returned_error_status);

                RETURN;

            END IF;

        EXCEPTION
            WHEN OTHERS THEN -- No data found, for instance...

                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        'Couldn''t determine the LockableObjectLoaded!',
                                                        affected_rows,
                                                        NULL,
                                                        returned_error_status);

                RETURN;

        END;

        -- ETL Metadata: lock this ETL Object!
        PKG_METADATA.spMD_LockObject(lockable_object,
                                     etl_process,
                                     run_instance,
                                     force_lock,
                                     last_sync_date,
                                     0,
                                     returned_error_status);

        IF returned_error_status <> 0 OR last_sync_date IS NULL THEN

            PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                    run_instance,
                                                    'Unable to lock object ' || objectname || '! Exiting procedure...',
                                                    affected_rows,
                                                    NULL,
                                                    returned_error_status);

            RETURN;

        END IF;

        -----------------------------------------------------------------------------
        -- Get the new sync date...
        --new_sync_date := dateadd ('N', -1, systimestamp); -- 1 minute ago, just to be sure.
        new_sync_date := systimestamp-1/1440;

        -- Fill up the DIFF table!
        BEGIN

            DELETE	FROM WFSDW.STG1_DIFF_Log;

            INSERT INTO WFSDW.STG1_DIFF_Log(ROWID_,
                                            LOGID,
                                            LOGCLASS,
                                            LOGCLASSNAME,
                                            LOCALDATE,
                                            PROCESSDEFINITIONID,
                                            PROCESSINSTANCEID,
                                            NODEID,
                                            LOCALENTERDATE,
                                            LOCALLEAVEDATE,
                                            DURATION,
                                            RESOURCEGROUPID,
                                            RESOURCEID,
                                            ADMINSTATUSID,
                                            OPERSTATUSID,
                                            WFSCreateDate,
                                            LastProdUpdateDate,
                                            DIFFETLProcess,
                                            DIFFRunInstance,
                                            DIFFDate)
            SELECT STG1_DIFF_Log_SEQ.nextval,
                   l.ID_ as LOGID,
                   l.CLASS_ as LOGCLASS,
                   DECODE (l.CLASS_, 'N', 'NODE',
			             '7', 'MESSAGE',
                                     '8', 'USAGE',
				     '9', 'QUEUE',							
				     'UNKNOWN') as LOGCLASSNAME,
                   l.DATE_ as LOCALDATE,
                   n.PROCESSDEFINITION_,
                   p.ID_ as PROCESSINSTANCEID,
                   l.NODE_ as NODEID,
                   l.ENTER_ as LOCALENTERDATE,
                   l.LEAVE_ as LOCALLEAVEDATE,
                   l.DURATION_ as DURATION,
                   l.RESOURCEGROUPID,
                   l.RESOURCEID,
                   ADMINISTRATIVESTATUSID as ADMINSTATUSID,
                   OPERATIONALSTATUSID as OPERSTATUSID,
                   l.CreateDate,
                   l.UpdateDate,
                   etl_process,
                   run_instance,
                  new_sync_date
            FROM WFS.JBPM_LOG@WFS_PRODUCTIONDB l
              JOIN WFS.JBPM_NODE@WFS_PRODUCTIONDB n
                ON (n.ID_ = l.NODE_)
              JOIN WFS.JBPM_TOKEN@WFS_PRODUCTIONDB t
                ON (t.ID_ = l.TOKEN_)
              JOIN WFS.JBPM_PROCESSINSTANCE@WFS_PRODUCTIONDB p
                ON (p.ID_ = t.PROCESSINSTANCE_)
              JOIN WFS.JBPM_PROCESSDEFINITION@WFS_PRODUCTIONDB d
                ON (p.PROCESSDEFINITION_ = d.ID_)
	     WHERE d.PROCESSDEFINITIONTYPEID <> 1 AND
		   l.Class_ in ('8','9') AND
                   l.UpdateDate BETWEEN last_sync_date AND new_sync_date;

         -- Lookup the TemplateWK field from DIM_Template table data.
            UPDATE WFSDW.STG1_DIFF_Log a
            SET a.TemplateWK= (SELECT b.TemplateWK
                               FROM DIM_Template b
                               WHERE b.ProcessDefinitionId = a.ProcessDefinitionId)
            WHERE  WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Lookup the WorkOrderWK field from DIM_WorkOrder table data.
            UPDATE WFSDW.STG1_DIFF_Log a
            SET a.WorkOrderWK= (SELECT b.WorkOrderWK
                               FROM DIM_WorkOrder b
                               WHERE b.ProcessInstanceId = a.ProcessInstanceId)
            WHERE  WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

/*********************************************************************************
**********************************************************************************
***********  TEMPORARY FIX TO MAKE Data Warehouse NOT VOD SPECIFIC  *************
*********************************************************************************
*********************************************************************************/

            DELETE FROM WFSDW.STG1_DIFF_WORKORDER;

            INSERT INTO WFSDW.STG1_DIFF_WorkOrder(ROWID_,
                                                  ProcessInstanceId,
                                                  LocalStartDate,
                                                  LocalEndDate,
                                                  IssuSpended,
                                                  ProcessDefinitionId,
                                                  AdminStatusId,
                                                  OperStatusId,
                                                  PriorityId,
                                                  Priority,
						  ProviderName,
						  PackageAssetID,
						  Title,
						  BriefTitle,
						  AssetID,
						  ObjectType,
						  HouseMaterialID,
						  BroadcastMaterialID,
						  LicenseStart,
						  LicenseEnd,
                                                  WFSCreateDate,
                                                  LastProdUpdateDate,
                                                  DIFFETLProcess,
                                                  DIFFRunInstance,
                                                  DIFFDate)                
		SELECT STG1_DIFF_WorkOrder_SEQ.nextval,
			PROCESSINSTANCEID,
			LOCALSTARTDATE,
			LOCALENDDATE,
			ISSUSPENDED,
			PROCESSDEFINITIONID,
			ADMINSTATUSID,
			OPERSTATUSID,
			PRIORITYID,
			PRIORITY,
			'N/A',
			'N/A',
			TITLE,
			BRIEFTITLE,
			ASSETID,
			OBJECTTYPE,
       			HOUSEMATERIALID,
			BROADCASTMATERIALID,
		 	LICENSESTART,
			LICENSEEND,
			CREATEDATE,
			UPDATEDATE,
			etl_process,
			run_instance,
			new_sync_date
		FROM ( SELECT I.ID_ AS PROCESSINSTANCEID,
                	  START_ AS LOCALSTARTDATE,
                  	  END_ AS LOCALENDDATE,
                       	 ISSUSPENDED_ AS ISSUSPENDED,
                  	  PROCESSDEFINITION_ AS PROCESSDEFINITIONID,
                   	 ADMINISTRATIVEPROCESSSTATUSID AS ADMINSTATUSID,
   	                 OPERATIONALPROCESSSTATUSID AS OPERSTATUSID,
        	            I.PROCESSPRIORITYID PRIORITYID,
                	    NVL (P.NAME, '[N/A]') AS PRIORITY,
	                    max(CASE WHEN vi.Name_ = 'providerId' THEN vi.STRINGVALUE_ ELSE NULL END) AS ProviderName,
        	            max(CASE WHEN vi.Name_ = 'packageAssetId' THEN vi.STRINGVALUE_ ELSE NULL END) AS PackageAssetID,
                	    max(CASE WHEN vi.Name_ = 'title' THEN vi.STRINGVALUE_ ELSE NULL END) AS Title,
	                    max(CASE WHEN vi.Name_ = 'titleBrief' THEN vi.STRINGVALUE_ ELSE NULL END) AS BriefTitle,
        	            max(CASE WHEN vi.Name_ = 'assetId' THEN vi.STRINGVALUE_ ELSE NULL END) AS AssetID,
                	    max(CASE WHEN vi.Name_ = 'type' THEN vi.STRINGVALUE_ ELSE NULL END) AS ObjectType,
	    		    max(CASE WHEN vi.Name_ = 'houseMaterialId' THEN vi.STRINGVALUE_ ELSE NULL END) AS HouseMaterialID,
			    max(CASE WHEN vi.Name_ = 'broadcastMaterialId' THEN vi.STRINGVALUE_ ELSE NULL END) AS BroadcastMaterialID,
                	    max(CASE WHEN vi.Name_ = 'licStart' THEN vi.STRINGVALUE_ ELSE NULL END) AS LicenseStart,
	                    max(CASE WHEN vi.Name_ = 'licEnd' THEN vi.STRINGVALUE_ ELSE NULL END) AS LicenseEnd,
			    I.CREATEDATE,
                	    I.UPDATEDATE
             FROM WFS.JBPM_PROCESSINSTANCE@WFS_PRODUCTIONDB I
                  JOIN  WFS.JBPM_PROCESSDEFINITION@WFS_PRODUCTIONDB D
                    ON (        D.ID_ = PROCESSDEFINITION_)
                  JOIN  WFS.TTV_ProcessDefinitionType@WFS_PRODUCTIONDB dt
                    ON (        dt.ProcessDefinitionTypeId = d.ProcessDefinitionTypeId)
                  LEFT JOIN WFS.TTV_PROCESSPRIORITY@WFS_PRODUCTIONDB P
                    ON (        P.PROCESSPRIORITYID = I.PROCESSPRIORITYID)
                  JOIN  WFS.TTV_PROCESSSTATUS@WFS_PRODUCTIONDB ADS
                    ON (        ADS.PROCESSSTATUSID@WFS_PRODUCTIONDB = ADMINISTRATIVEPROCESSSTATUSID)
                  JOIN  WFS.TTV_PROCESSSTATUS@WFS_PRODUCTIONDB OPS
                    ON (        OPS.PROCESSSTATUSID = OPERATIONALPROCESSSTATUSID)
                  JOIN JBPM_VARIABLEINSTANCE@WFS_PRODUCTIONDB vi
                    ON ( I.ID_ = VI.PROCESSINSTANCE_) AND
 		       (vi.TASKINSTANCE_ is null)
                  JOIN WFSDW.STG1_DIFF_LOG sdl
                    ON ( I.ID_ = sdl.PROCESSINSTANCEID )
               WHERE PROCESSINSTANCETYPEID <> 1 AND
                     sdl.WORKORDERWK is NULL AND
		     UpdateDate BETWEEN last_sync_date AND new_sync_date
               GROUP BY I.ID_,
                       START_,
                       END_,
                       ISSUSPENDED_,
                       PROCESSDEFINITION_,
                       ADS.NAME,
                       ADMINISTRATIVEPROCESSSTATUSID,
                       OPS.NAME,
                       OPERATIONALPROCESSSTATUSID,
                       I.PROCESSPRIORITYID,
                       NVL(P.NAME, '[N/A]'),
		       I.CREATEDATE,
		       I.UPDATEDATE
		ORDER BY I.ID_);


            -- Lookup the TemplateWK field from DIM_Template table data.
            UPDATE WFSDW.STG1_DIFF_WorkOrder a
            SET a.TemplateWK= (SELECT b.TemplateWK
                               FROM DIM_Template b
                               WHERE b.ProcessDefinitionId = a.ProcessDefinitionId)
            WHERE  WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Lookup the ProviderWK from DIM_VariableInstance table data.
            UPDATE WFSDW.STG1_DIFF_WORKORDER a
            SET a.ProviderWK = (SELECT b.VariableInstanceWK
                                FROM DIM_PROVIDER b
                                WHERE b.ProviderName = a.ProviderName)
            WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Set the LocalStartDateWK
            UPDATE WFSDW.STG1_DIFF_WORKORDER
            SET LocalStartDateWK = to_number(to_char(LocalStartDate,'YYYYMMDD'))
            WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Lookup the LocalStartTimeWK
            UPDATE WFSDW.STG1_DIFF_WORKORDER
            SET LocalStartTimeWK = to_number(to_char(LocalStartDate,'HH24MISS'))
            WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Convert License Dates
            UPDATE WFSDW.STG1_DIFF_WORKORDER
            SET LicenseStartDate = decode(substr(LicenseStart,5,1),'-',to_date(LicenseStart,'RRRR-MM-DD'),to_date(LicenseStart,'MM/DD/RRRR')),
                LicenseEndDate = decode(substr(LicenseEnd,5,1),'-',to_date(LicenseEnd,'RRRR-MM-DD'),to_date(LicenseEnd,'MM/DD/RRRR'));

        -- Lookup the AdminStatusWK
           UPDATE WFSDW.STG1_DIFF_WORKORDER a
           SET a.AdminStatusWK = (SELECT b.WorkOrderStatusWK
                                  FROM DIM_WORKORDERSTATUS b
                                  WHERE b.ProcessStatusID = a.AdminStatusID)
           WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

        -- Lookup the OperStatusWK
           UPDATE WFSDW.STG1_DIFF_WORKORDER a
           SET a.OperStatusWK = (SELECT b.WorkOrderStatusWK
                                 FROM DIM_WORKORDERSTATUS b
                                 WHERE b.ProcessStatusID = a.OperStatusID)
           WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

            MERGE INTO WFSDW.DIM_WorkOrder d
            USING       (SELECT *
                     FROM WFSDW.STG1_DIFF_WorkOrder
                     ORDER BY ProcessInstanceId) s
            ON (d.ProcessInstanceId = s.ProcessInstanceId)
            WHEN MATCHED THEN
                    UPDATE SET  d.Suspended = decode(s.IssuSpended,1,'YES','NO'),
                                d.Priority = s.Priority,
                                d.localstartdate = s.localstartdate,
                                d.localenddate = s.localenddate,
                                d.PackageAssetID = s.PackageAssetID,
                                d.AssetID = s.AssetID,
                                d.AssetType = s.ObjectType,
                                d.Title = s.Title,
                                d.BriefTitle = s.BriefTitle,
                                d.HouseMaterialID = s.HouseMaterialID,
                                d.BroadcastMaterialID = s.BroadcastMaterialID,
                                d.LicenseStartDate = s.LicenseStartDate,
                                d.LicenseEndDate = s.LicenseEndDate,
                                d.LastProdUpdateDate = s.LastProdUpdateDate,
                                d.LastETLProcess = etl_process,
                                d.LastRunInstance = run_instance,
                                d.UpdateDate = systimestamp
            WHEN NOT MATCHED THEN
                    INSERT (d.WorkOrderWK,
                            d.ProcessInstanceId,
                            d.Suspended,
                            d.Priority,
                            d.localstartdate,
                            d.localenddate,
                            d.PackageAssetID,
                            d.AssetID,
                            d.AssetType,
                            d.Title,
                            d.BriefTitle,
                            d.HouseMaterialID,
                            d.BroadcastMaterialID,
                            d.LicenseStartDate,
                            d.LicenseEndDate,
                            d.WFSCreateDate,
                            d.LastProdUpdateDate,
                            d.LastETLProcess,
                            d.LastRunInstance,
                            d.CreateDate,
                            d.UpdateDate)
                    VALUES (DIM_WorkOrder_SEQ.nextval,
                            s.ProcessInstanceId,
                            decode(s.IssuSpended,1,'YES','NO'),
                            s.Priority,
                            s.localstartdate,
                            s.localenddate,
                            s.PackageAssetID,
                            s.AssetID,
                            s.ObjectType,
                            s.Title,
                            s.BriefTitle,
                            s.HouseMaterialID,
                            s.BroadcastMaterialID,
                            s.LicenseStartDate,
                            s.LicenseEndDate,
                            s.WFSCreateDate,
                            s.LastProdUpdateDate,
                            etl_process,
                            run_instance,
                            systimestamp,
                            systimestamp);

            INSERT INTO FACT_WORKORDERDETAIL(WorkOrderWK,
                                             TemplateWK,
                                             ProviderWK,
                                             LocalStartDateWK,
                                             LocalStartTimeWK,
                                             AdminStatusWK,
                                             OperStatusWK,
                                             WFSCreateDate,
                                             LastProdUpdateDate,
                                             LastETLProcess,
                                             LastRunInstance,
                                             CreateDate,
                                             UpdateDate)
            SELECT dim.WorkOrderWK,
                   stg.TemplateWK,
                   stg.ProviderWK,
                   stg.LocalStartDateWK,
                   stg.LocalStartTimeWK,
                   stg.AdminStatusWK,
                   stg.OperStatusWK,
                   stg.WFSCreateDate,
                   stg.LastProdUpdateDate,
                   etl_process,
                   run_instance,
                   systimestamp,
                   systimestamp
            FROM WFSDW.STG1_DIFF_WORKORDER stg
                 JOIN WFSDW.DIM_WORKORDER dim
                   ON (dim.ProcessInstanceId = stg.ProcessInstanceId)
            WHERE stg.WFSCreateDate BETWEEN last_sync_date AND new_sync_date;


        /**** RERUN THE WO LOOKUP  *********************************************/
 
            UPDATE WFSDW.STG1_DIFF_Log a
            SET a.WorkOrderWK= (SELECT b.WorkOrderWK
                               FROM DIM_WorkOrder b
                               WHERE b.ProcessInstanceId = a.ProcessInstanceId)
            WHERE  WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

/*************************************************************************************
**************************************************************************************
*******************  END Of FIX ******************************************************
**************************************************************************************
**************************************************************************************/

         -- Lookup the NodeWK field from DIM_Node table data.
            UPDATE WFSDW.STG1_DIFF_Log a
            SET a.NodeWK= (SELECT b.NodeWK
                               FROM DIM_Node b
                               WHERE b.NodeId = a.NodeId)
            WHERE  WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Lookup the ResourceGroupWK field from DIM_ResourceGroup table data.
            UPDATE WFSDW.STG1_DIFF_Log a
            SET a.ResourceGroupWK= (SELECT b.ResourceGroupWK
                               FROM DIM_ResourceGroup b
                               WHERE b.ResourceGroupId = a.ResourceGroupId)
            WHERE  WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Lookup the ResourceWK field from DIM_Resource table data.
            UPDATE WFSDW.STG1_DIFF_Log a
            SET a.ResourceWK= (SELECT b.ResourceWK
                               FROM DIM_Resource b
                               WHERE b.ResourceId = a.ResourceId)
            WHERE  WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Set the LocalStartDateWK
            UPDATE WFSDW.STG1_DIFF_LOG
            SET LocalEnterDateWK = to_number(to_char(LocalEnterDate,'YYYYMMDD'))
            WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Lookup the LocalStartTimeWK
            UPDATE WFSDW.STG1_DIFF_LOG
            SET LocalEnterTimeWK = to_number(to_char(LocalEnterDate,'HH24MISS'))
            WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Lookup the LocalLeaveDateWK
            UPDATE WFSDW.STG1_DIFF_LOG
            SET LocalLeaveDateWK = to_number(to_char(LocalLeaveDate,'YYYYMMDD'))
            WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Lookup the LocalLeaveTimeWK
            UPDATE WFSDW.STG1_DIFF_LOG
            SET LocalLeaveTimeWK = to_number(to_char(LocalLeaveDate,'HH24MISS'))
            WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

	-- Lookup the AdminStatusWK
	   UPDATE WFSDW.STG1_DIFF_LOG a
	   SET a.AdminStatusWK = (SELECT b.WorkOrderStatusWK
				 FROM DIM_WorkOrderStatus b
				 WHERE b.ProcessStatusID = a.AdminStatusID)
	   WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

	-- Lookup the OperStatusWK
	   UPDATE WFSDW.STG1_DIFF_LOG a
	   SET a.OperStatusWK = (SELECT b.WorkOrderStatusWK
	 			 FROM DIM_WorkOrderStatus b
				 WHERE b.ProcessStatusID = a.OperStatusID)
	   WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

        EXCEPTION
            WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                error_message := procname || ': DB error loading STG1_DIFF_Log!';

                ROLLBACK;

                -- Release the object indicating that there was an error
                PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                etl_process,
                                                run_instance,
                                                NULL,
                                                1,
                                                0,
                                                returned_error_status);

                -- Close the etl process.
                PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                        run_instance,
                                                        error_message,
                                                        0,
                                                        v_sqlcode,
                                                        returned_error_status);

                RETURN;

        END;

        -- Commit changes to DIFF table to facilitate the debugging process if there was an error -- typically, errors
        -- occur due to problematic data in STG_DIFF.
        COMMIT;


        -----------------------------------------
        -- Load Fact_ResourceQueueDetail data! --
        -----------------------------------------

        BEGIN

	    -- Loading duration in seconds

            INSERT INTO FACT_RESOURCEQUEUEDETAIL(TemplateWK,
						 WorkOrderWK,
						 NodeWK,
						 LocalEnterDateWK,
						 LocalEnterTimeWK,
					 	 ResourceWK,
						 ResourceGroupWK,
						 AdminStatusWK,
						 OperStatusWK,
						 Duration,
                                                 WFSCreateDate,
					         LastProdUpdateDate,
                                                 LastETLProcess,
                                                 LastRunInstance,
                                                 CreateDate,
                                                 UpdateDate)
            SELECT stg.TemplateWK,
		   stg.WorkOrderWK,
		   stg.NodeWK,
		   stg.LocalEnterDateWK,
		   stg.LocalEnterTimeWK,
		   stg.ResourceWK,
		   stg.ResourceGroupWK,
		   stg.AdminStatusWK,
		   stg.OperStatusWK,
		   extract(second from (stg.LocalLeaveDate - stg.LocalEnterDate)) +
		   (extract(minute from (stg.LocalLeaveDate - stg.LocalEnterDate)) * 60) +
		   (extract(hour from (stg.LocalLeaveDate - stg.LocalEnterDate)) * 3600) +
		   (extract(day from (stg.LocalLeaveDate - stg.LocalEnterDate)) * 86400) as Duration,
                   stg.WFSCreateDate,
		   stg.LastProdUpdateDate,
		   etl_process,
                   run_instance,
                   systimestamp,
                   systimestamp
	    FROM WFSDW.STG1_DIFF_LOG stg
            WHERE stg.LOGCLASS = '9' AND
		  stg.AdminStatusID = 4 AND
		  stg.OperStatusID = 4 AND
		  stg.WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

            EXCEPTION
                WHEN OTHERS THEN
                    v_sqlcode := SQLCODE;
                    error_message := procname || ': DB error loading Fact_ResourceQueueDetail Data!';

                    ROLLBACK;

                    -- Release the object indicating that there was an error
                    PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                    etl_process,
                                                    run_instance,
                                                    NULL,
                                                    1,
                                                    0,
                                                    returned_error_status);

                    -- Close the etl process.
                    PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                            run_instance,
                                                            error_message,
                                                            0,
                                                            v_sqlcode,
                                                            returned_error_status);

                    RETURN;

        END;

        -----------------------------------------
        -- Load Fact_ResourceTaskDetail data! --
        -----------------------------------------

        BEGIN

            -- Loading duration in seconds

            INSERT INTO FACT_RESOURCETASKDETAIL(TemplateWK,
                                                  WorkOrderWK,
                                                  NodeWK,
                                                  LocalEnterDateWK,
                                                  LocalEnterTimeWK,
						  LocalLeaveDateWK,
						  LocalLeaveTimeWK,
                                                  ResourceWK,
                                                  ResourceGroupWK,
						  AdminStatusWK,
						  OperStatusWK,
                                                  Duration,
                                                  WFSCreateDate,
                                                  LastProdUpdateDate,
                                                  LastETLProcess,
                                                  LastRunInstance,
                                                  CreateDate,
                                                  UpdateDate)
            SELECT stg.TemplateWK,
                   stg.WorkOrderWK,
                   stg.NodeWK,
                   stg.LocalEnterDateWK,
                   stg.LocalEnterTimeWK,
                   stg.LocalLeaveDateWK,
		   stg.LocalLeaveTimeWK,
                   stg.ResourceWK,
                   stg.ResourceGroupWK,
		   stg.AdminStatusWK,
		   stg.OperStatusWK,
		   extract(second from (stg.LocalLeaveDate - stg.LocalEnterDate)) +
                   (extract(minute from (stg.LocalLeaveDate - stg.LocalEnterDate)) * 60) +
                   (extract(hour from (stg.LocalLeaveDate - stg.LocalEnterDate)) * 3600) +
                   (extract(day from (stg.LocalLeaveDate - stg.LocalEnterDate)) * 86400) as Duration,
                   stg.WFSCreateDate,
                   stg.LastProdUpdateDate,
                   etl_process,
                   run_instance,
                   systimestamp,
                   systimestamp
            FROM WFSDW.STG1_DIFF_LOG stg
            WHERE stg.LOGCLASS = '8' AND
                  stg.WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

            EXCEPTION
                WHEN OTHERS THEN
                    v_sqlcode := SQLCODE;
                    error_message := procname || ': DB error loading Fact_ResourceTaskDetail Data!';

                    ROLLBACK;

                    -- Release the object indicating that there was an error
                    PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                                    etl_process,
                                                    run_instance,
                                                    NULL,
                                                    1,
                                                    0,
                                                    returned_error_status);

                    -- Close the etl process.
                    PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                            run_instance,
                                                            error_message,
                                                            0,
                                                            v_sqlcode,
                                                            returned_error_status);

                    RETURN;

        END;


        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        new_sync_date,
                                        0,
                                        0,
                                        returned_error_status);

        PKG_METADATA.spMD_LogProcessFinishOK(etl_process,
                                             run_instance,
                                             NULL,
                                             affected_rows,
                                             returned_error_status);

        COMMIT; -- Yay!
        error_status := 0;

  EXCEPTION
    WHEN OTHERS THEN
        v_sqlcode := SQLCODE;
        error_message := procname || ': Global exception handler -> unknown error has occurred!';

        ROLLBACK;

        -- Release the object indicating that there was an error
        PKG_METADATA.spMD_ReleaseObject(lockable_object,
                                        etl_process,
                                        run_instance,
                                        NULL,
                                        1,
                                        0,
                                        returned_error_status);

        -- Close the etl process.
        PKG_METADATA.spMD_LogProcessFinishError(etl_process,
                                                run_instance,
                                                error_message,
                                                0,
                                                v_sqlcode,
                                                returned_error_status);

  END spSYNC_Log;

/*
	*****************************************************************************
	PROCEDURE: spSYNC_MonitoredDriveHistory
	[ETL - MD Compliant]
	*****************************************************************************
	Loads dimension table FACT_MonitoredDriveHistory.

	*****************************************************************************
	[USED BY]	ETL - DW Load process
	*****************************************************************************
	[By Chris Scherlacher - 07/09/2007]
	*****************************************************************************
	Change Log: 

	*****************************************************************************
*/ 
  PROCEDURE spSYNC_MonitoredDriveHistory (
        parent_etl_process				int DEFAULT NULL,
        parent_run_instance				int DEFAULT NULL,
        force_lock						int DEFAULT 0,
        error_status				OUT	int)
  AS
        etl_process						int;
        run_instance					int;
        returned_error_status			int;
        lockable_object					int;
        procname						varchar2 (100);
        objectname						varchar2 (100);
        last_sync_date					timestamp;
        new_sync_date					timestamp;
        error_message					varchar2 (500);
        affected_rows					int;
        v_sqlcode						int;
		recs_not_inserted				int;
		recs_to_insert					int;
	v_exists					char(1);
        v_MonitoredDrive				STG1_DIFF_MDRIVEHISTORY%ROWTYPE;
	v_MonitoredDriveWK				Number;

        CURSOR MonitoredDriveCursor IS
          select MONITOREDDRIVEID, 
       		 CURRENTDRIVENAME, 
       		 CURRENTDESCRIPTION,
       		 CURRENTPATH,
       		 CURRENTINTERNALDRIVEID,
       		 CURRENTTOTALCAPACITY,
       		 CURRENTWARNINGTHRESHOLDPERCENT,
       		 CURRENTERRORTHRESHOLDPERCENT,
       		 MIN(WFSCreateDate)
	  From STG1_DIFF_MDRIVEHISTORY
	  Group by MONITOREDDRIVEID, 
       		   CURRENTDRIVENAME, 
       		   CURRENTDESCRIPTION,
       		   CURRENTPATH,
       		   CURRENTINTERNALDRIVEID,
       		   CURRENTTOTALCAPACITY,
       		   CURRENTWARNINGTHRESHOLDPERCENT,
       		   CURRENTERRORTHRESHOLDPERCENT
	  Order by MIN(WFSCreateDate);

  BEGIN

        -- Initialize...
        error_status := -1;
        procname := 'spSYNC_MonitoredDriveHistory';
        affected_rows := 0;
        
        -----------------------------------------------------------------------------
        -- [ETL METADATA INFO]
        -- Unique identifier for this ETL Process, DO NOT CHANGE!
        etl_process := 12;
        -----------------------------------------------------------------------------

        -- [ETL METADATA INITIALIZATION]
        -- Obtain a RunInstance for this ETL Process
        PKG_METADATA.spMD_LogProcessStart (etl_process, parent_etl_process, parent_run_instance, NULL, run_instance,
                                           returned_error_status);

        IF run_instance < 1 OR  returned_error_status <> 0 THEN
            PKG_METADATA.spMD_WriteEventLog (procname, -1, 'METADATA ERROR: error in call to spSYNC_MonitoredDriveHistory ProcessStart / could not obtain a RunInstance. Exiting procedure!');
            RETURN;
        END IF;

        BEGIN
            -- Get the object ID in order to lock it...
            SELECT	LockableObjectLoaded, o.Name
            INTO	lockable_object, objectname
            FROM	MD_ETLProcess p
            JOIN	MD_LockableObject o
            ON (	o.LockableObjectKey = p.LockableObjectLoaded)
            WHERE	ETLProcessKey = etl_process;

            -- Not null, etc.
            IF lockable_object IS NULL OR objectname IS NULL THEN
                PKG_METADATA.spMD_LogProcessFinishError (
                    etl_process, run_instance, 'Couldn''t determine the LockableObjectLoaded!', affected_rows, NULL, returned_error_status);
                RETURN;
            END IF;

        EXCEPTION
            WHEN OTHERS THEN -- No data found, for instance...
                PKG_METADATA.spMD_LogProcessFinishError (
                    etl_process, run_instance, 'Couldn''t determine the LockableObjectLoaded!', affected_rows, NULL, returned_error_status);
                RETURN;
        END;		

        -- ETL Metadata: lock this ETL Object!
        PKG_METADATA.spMD_LockObject (
            lockable_object, etl_process, run_instance, force_lock, last_sync_date, 0, returned_error_status);

        IF returned_error_status <> 0 OR last_sync_date IS NULL THEN

            PKG_METADATA.spMD_LogProcessFinishError (
                etl_process, run_instance, 'Unable to lock object ' || objectname || '! Exiting procedure...', affected_rows, NULL,
                returned_error_status);

            RETURN;

        END IF;

        -----------------------------------------------------------------------------
        -- Get the new sync date...
        --new_sync_date := dateadd ('N', -1, systimestamp); -- 1 minute ago, just to be sure.
        new_sync_date := systimestamp-1/1440;

        -- Fill up the DIFF table!
        BEGIN

            DELETE FROM WFSDW.STG1_DIFF_MDriveHistory;

            INSERT INTO WFSDW.STG1_DIFF_MDriveHistory (ROWID_,
					          MonitoredDriveHistoryId, 
					          MonitoredDriveId,
						  CurrentDriveName,
						  CurrentDescription,
						  CurrentPath,
						  CurrentInternalDriveID,
						  CurrentTotalCapacity,
						  CurrentWarningThresholdPercent,
						  CurrentErrorThresholdPercent,
					          CurrentBytesUsed,
                    			          CurrentDriveStatusId, 
					          CurrentDriveStatusName, 
					          SampleDateTime,
                    			          LastProdUpdateDate, 	
					          DIFFETLProcess, 
					          DIFFRunInstance, 	
					          DIFFDate, 	
					          WFSCreateDate)
            SELECT STG1_DIFF_MDriveHistory_SEQ.nextval,
		   mdh.MonitoredDriveHistoryId, 
		   mdh.MonitoredDriveId,
		   md.Name as DriveName,
		   md.DESCRIPTION,
        	   md.PATH,
       		   md.INTERNALDRIVEID,
       		   mdh.TOTALCAPACITY,
       		   mdh.WARNINGTHRESHOLDPERCENT,
       		   mdh.ERRORTHRESHOLDPERCENT,
		   mdh.CurrentBytesUsed, 
                   mdh.CurrentDriveStatusId, 
		   mds.Name as DriveStatusName, 
		   mdh.SampleDateTime,
                   mdh.CreateDate, 
		   etl_process, 
		   run_instance, 
		   new_sync_date, 
		   mdh.CreateDate
            FROM  WFS.TTV_MONITOREDDRIVEHISTORY@WFS_PRODUCTIONDB mdh
              JOIN WFS.TTV_MONITOREDDRIVESTATUS@WFS_PRODUCTIONDB mds
                ON mds.MonitoredDriveStatusId = mdh.CurrentDriveStatusId
              JOIN WFS.TTV_MONITOREDDRIVE@WFS_PRODUCTIONDB md
                ON md.MONITOREDDRIVEID = mdh.MONITOREDDRIVEID
            WHERE  mdh.CreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Set the LocalStartDateWK
            UPDATE WFSDW.STG1_DIFF_MDRIVEHISTORY
            SET LocalDateWK = to_number(to_char(SampleDateTime,'YYYYMMDD'))
            WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;

         -- Lookup the LocalStartTimeWK
            UPDATE WFSDW.STG1_DIFF_MDRIVEHISTORY
            SET LocalTimeWK = to_number(to_char(SampleDateTime,'HH24MISS'))
            WHERE WFSCreateDate BETWEEN last_sync_date AND new_sync_date;
 
         
        EXCEPTION
            WHEN OTHERS THEN
                v_sqlcode := SQLCODE;
                error_message := procname || ': DB error loading STG_DIFF_MonitoredDriveHistory!';

                ROLLBACK;

                -- Release the object indicating that there was an error
                PKG_METADATA.spMD_ReleaseObject (
                    lockable_object, etl_process, run_instance, NULL, 1, 0, returned_error_status);
                -- Close the etl process.
                PKG_METADATA.spMD_LogProcessFinishError (
                    etl_process, run_instance, error_message, 0, v_sqlcode, returned_error_status);

                RETURN;

        END;
              
        -- Commit changes to DIFF table to facilitate the debugging process if there was an error -- typically, errors
        -- occur due to problematic data in STG_DIFF.
        COMMIT;

        -----------------------------------
        -- Load DIM_MonitoredDriveStatus!!
        -----------------------------------

        BEGIN

	   MERGE INTO WFSDW.DIM_MonitoredDriveStatus d
            USING       (SELECT a.CURRENTDRIVESTATUSID, a.CURRENTDRIVESTATUSNAME, b.WFSCREATEDATE, c.LASTPRODUPDATEDATE
		         FROM (SELECT DISTINCT CURRENTDRIVESTATUSID, CURRENTDRIVESTATUSNAME
      			       FROM WFSDW.STG1_DIFF_MDRIVEHISTORY) a 
  			   JOIN (SELECT CurrentDriveStatusId, MIN(WFSCREATEDATE) as WFSCREATEDATE
        	                 FROM WFSDW.STG1_DIFF_MDriveHistory
        		        GROUP BY CURRENTDRIVESTATUSID) b
    			     ON a.CURRENTDRIVESTATUSID = b.CURRENTDRIVESTATUSID
  			   JOIN (SELECT CURRENTDRIVESTATUSID, MAX(LASTPRODUPDATEDATE) as LASTPRODUPDATEDATE
        			 FROM WFSDW.STG1_DIFF_MDriveHistory
        			 GROUP BY CURRENTDRIVESTATUSID) c
    			     ON a.CURRENTDRIVESTATUSID = c.CURRENTDRIVESTATUSID
			 ORDER BY a.CURRENTDRIVESTATUSID) s
            ON (d.DriveStatusId = s.CurrentDriveStatusId)
            WHEN MATCHED THEN
                    UPDATE SET  d.Name = CurrentDriveStatusName,
                                d.LastProdUpdateDate = s.LastProdUpdateDate,
                                d.LastETLProcess = etl_process,
                                d.LastRunInstance = run_instance,
                                d.UpdateDate = systimestamp
            WHEN NOT MATCHED THEN
                    INSERT (d.MonitoredDriveStatusWK,
                            d.DriveStatusId,
                            d.Name,
                            d.WFSCreateDate,
			    d.LastProdUpdateDate,
                            d.LastETLProcess,
                            d.LastRunInstance,
                            d.CreateDate,
                            d.UpdateDate)
	            VALUES( WFSDW.DIM_MONITOREDDRIVESTATUS_SEQ.nextval,
			    s.CurrentDriveStatusId,
			    s.CurrentDriveStatusName,
			    s.WFSCREATEDATE,
			    s.WFSCREATEDATE,
			    etl_process,
			    run_instance,
			    systimestamp,
			    systimestamp);

        COMMIT; -- Yay!

        error_status := 0;

    EXCEPTION
    WHEN OTHERS THEN
        v_sqlcode := SQLCODE;
        error_message := procname || ': DB error loading DIM_MonitoredDriveStatus!!!';

        ROLLBACK;

        -- Release the object indicating that there was an error
        PKG_METADATA.spMD_ReleaseObject (
            lockable_object, etl_process, run_instance, NULL, 1, 0, returned_error_status);

        -- Close the etl process.
        PKG_METADATA.spMD_LogProcessFinishError (
            etl_process, run_instance, error_message, 0, v_sqlcode, returned_error_status);

    END;

    ------------------------------
    -- Load Dim_MonitoredDrive  --
    --    Type 2 Dimension     --
    ------------------------------

    BEGIN

      -- Open Cursor
      OPEN MonitoredDriveCursor;

      -- Loop through cursor and fetch records into variables.
      LOOP
        FETCH MonitoredDriveCursor into v_MonitoredDrive.MONITOREDDRIVEID,
                 			v_MonitoredDrive.CURRENTDRIVENAME,
                 			v_MonitoredDrive.CURRENTDESCRIPTION,
                 			v_MonitoredDrive.CURRENTPATH,
                 			v_MonitoredDrive.CURRENTINTERNALDRIVEID,
                 			v_MonitoredDrive.CURRENTTOTALCAPACITY,
                 			v_MonitoredDrive.CURRENTWARNINGTHRESHOLDPERCENT,
                 			v_MonitoredDrive.CURRENTERRORTHRESHOLDPERCENT,
                 			v_MonitoredDrive.WFSCreateDate;

        -- Exit loop when cursor is not found
        EXIT WHEN MonitoredDriveCursor%NOTFOUND;

      -- Check if a current DIM record exists for the current variables.	
      BEGIN

        -- Does it exist??
	SELECT 'x'
        into v_exists
	FROM DIM_MONITOREDDRIVE
	WHERE v_MonitoredDrive.MONITOREDDRIVEID = MONITOREDDRIVEID AND
	      v_MonitoredDrive.CURRENTDRIVENAME = NAME_ AND
	      v_MonitoredDrive.CURRENTDESCRIPTION = DESCRIPTION AND
	      v_MonitoredDrive.CURRENTPATH = PATH_ AND
	      v_MonitoredDrive.CURRENTINTERNALDRIVEID = INTERNALDRIVEID AND
	      v_MonitoredDrive.CURRENTTOTALCAPACITY = TOTALCAPACITY AND
	      v_MonitoredDrive.CURRENTWARNINGTHRESHOLDPERCENT = WARNINGTHRESHOLDPERCENT AND
	      v_MonitoredDrive.CURRENTERRORTHRESHOLDPERCENT = ERRORTHRESHOLDPERCENT AND
	      WFSENDDATE is NULL;
     
      -- No, the record does not exists an new record or version is needed.
      EXCEPTION 
      WHEN NO_DATA_FOUND THEN

        -- Check if a new version of and existing DIM record is needed.
        BEGIN

          -- Is there a current version??
          SELECT MONITOREDDRIVEWK
          INTO v_MonitoredDriveWK
          FROM DIM_MONITOREDDRIVE
          WHERE v_MonitoredDrive.MONITOREDDRIVEID = MONITOREDDRIVEID AND
                WFSENDDATE is NULL;

          -- Yes, there is a current version need to set the end date for the version.
          UPDATE WFSDW.DIM_MONITOREDDRIVE
	  SET WFSENDDATE = v_MonitoredDrive.WFSCreateDate
	  WHERE MONITOREDDRIVEWK = v_MonitoredDriveWK;

        -- No, there is not a current verion.  Only a new insert is needed. 
        EXCEPTION 
        WHEN NO_DATA_FOUND THEN

          NULL;

        END;

          -- Insert New DIM Record
          INSERT INTO WFSDW.DIM_MONITOREDDRIVE(MONITOREDDRIVEWK,
                                               MONITOREDDRIVEID,
                                               NAME_,
                                               DESCRIPTION,
                                               PATH_,
                                               INTERNALDRIVEID,
                                               TOTALCAPACITY,
                                               WARNINGTHRESHOLDPERCENT,
                                               ERRORTHRESHOLDPERCENT,
                                               WFSCREATEDATE,
                                               LASTETLPROCESS,
                                               LASTRUNINSTANCE,
                                               CREATEDATE,
                                               UPDATEDATE)
          VALUES(WFSDW.DIM_MONITOREDDRIVE_SEQ.NEXTVAL,
                 v_MonitoredDrive.MONITOREDDRIVEID,
	         v_MonitoredDrive.CURRENTDRIVENAME,
	         v_MonitoredDrive.CURRENTDESCRIPTION,
	         v_MonitoredDrive.CURRENTPATH,
	         v_MonitoredDrive.CURRENTINTERNALDRIVEID,
	         v_MonitoredDrive.CURRENTTOTALCAPACITY,
	         v_MonitoredDrive.CURRENTWARNINGTHRESHOLDPERCENT,
	         v_MonitoredDrive.CURRENTERRORTHRESHOLDPERCENT,
	         v_MonitoredDrive.WFSCreateDate,
	         etl_process,
                 run_instance,
                 systimestamp,
                 systimestamp);

      END;

      END LOOP;

      CLOSE MonitoredDriveCursor;

      COMMIT; -- Yay!

      error_status := 0;

    EXCEPTION
    WHEN OTHERS THEN
        v_sqlcode := SQLCODE;
        error_message := procname || ': DB error loading DIM_MonitoredDrive!!!';

        ROLLBACK;

        -- Release the object indicating that there was an error
        PKG_METADATA.spMD_ReleaseObject (
            lockable_object, etl_process, run_instance, NULL, 1, 0, returned_error_status);

        -- Close the etl process.
        PKG_METADATA.spMD_LogProcessFinishError (
            etl_process, run_instance, error_message, 0, v_sqlcode, returned_error_status);

    END;

    -------------------------------------
    -- Load Fact_MonitoredDriveHistory --
    -------------------------------------

    BEGIN

      INSERT INTO WFSDW.FACT_MDHISTORYDETAIL(MONITOREDDRIVEWK,
						  MONITOREDDRIVESTATUSWK,
						  LOCALDATEWK,
						  LOCALTIMEWK,
						  CURRENTBYTESUSED,
						  LASTETLPROCESS,
						  LASTRUNINSTANCE,
						  CREATEDATE,
						  UPDATEDATE)
       SELECT md.MONITOREDDRIVEWK,
	      d.MONITOREDDRIVESTATUSWK,
	      stg.LOCALDATEWK,
	      stg.LOCALTIMEWK,
	      MAX(stg.CurrentBytesUsed),
	      etl_process,
	      run_instance,
	      systimestamp,
	      systimestamp
	FROM WFSDW.STG1_DIFF_MDriveHistory stg
	  JOIN WFSDW.DIM_MONITOREDDRIVESTATUS d
	    ON stg.CurrentDriveStatusID = d.DriveStatusID
          JOIN WFSDW.DIM_MONITOREDDRIVE md
            ON stg.MonitoredDriveID = md.MonitoredDriveID AND
               stg.CurrentDriveName = md.Name_ AND
               stg.CurrentDescription = md.Description AND
               stg.CurrentPath = md.Path_ AND
               stg.CurrentInternalDriveID = md.InternalDriveID AND
               stg.CurrentTotalCapacity = md.TotalCapacity AND
               stg.CurrentWarningThresholdPercent = md.WarningThresholdPercent AND
               stg.CurrentErrorThresholdPercent = md.ErrorThresholdPercent AND
               md.WFSEndDate is NULL
	WHERE stg.WFSCreateDate BETWEEN last_sync_date AND new_sync_date
	GROUP BY md.MONITOREDDRIVEWK,d.MONITOREDDRIVESTATUSWK,stg.LOCALDATEWK,stg.LOCALTIMEWK;

            PKG_METADATA.spMD_ReleaseObject (
               lockable_object, etl_process, run_instance, new_sync_date, 0, 0, returned_error_status);

            PKG_METADATA.spMD_LogProcessFinishOK (
               etl_process, run_instance, NULL, affected_rows, returned_error_status);

        COMMIT; -- Yay!

        error_status := 0;

    EXCEPTION
    WHEN OTHERS THEN
        v_sqlcode := SQLCODE;
        error_message := procname || ': DB error loading FACT_MonitoredDriveHistory!!!';

        ROLLBACK;

        -- Release the object indicating that there was an error
        PKG_METADATA.spMD_ReleaseObject (
            lockable_object, etl_process, run_instance, NULL, 1, 0, returned_error_status);

        -- Close the etl process.
        PKG_METADATA.spMD_LogProcessFinishError (
            etl_process, run_instance, error_message, 0, v_sqlcode, returned_error_status);

    END;


  EXCEPTION
    WHEN OTHERS THEN
        v_sqlcode := SQLCODE;
        error_message := procname || ': Global exception handler -> unknown error has occurred!';

        ROLLBACK;

        -- Release the object indicating that there was an error
        PKG_METADATA.spMD_ReleaseObject (
            lockable_object, etl_process, run_instance, NULL, 1, 0, returned_error_status);

        -- Close the etl process.
        PKG_METADATA.spMD_LogProcessFinishError (
            etl_process, run_instance, error_message, 0, v_sqlcode, returned_error_status);
  
  END spSYNC_MonitoredDriveHistory;

END;
/

