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

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

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

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

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

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

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

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

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

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

PROCEDURE spAGG_FactMDHistoryMonth (
	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_DATAAGG
AS
/*
	*****************************************************************************
	PROCEDURE: spAGG_FactWorkOrderDay
	[ETL - MD Compliant]
	*****************************************************************************
	Aggregates data from the FACT_WORKORDERDETAIL table.

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

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

    PROCEDURE spAGG_FactWorkOrderDay (
        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 := 'spAGG_FactWorkOrderDay';
    affected_rows := 0;

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

    -----------------------------------------------------------------------------
    -- [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...

    SELECT MAX(d.CALENDERDATE - 1)
    INTO new_sync_date
    FROM FACT_WORKORDERDETAIL f
      JOIN DIM_DATE d
        ON f.LOCALSTARTDATEWK = d.DATEWK;

    BEGIN

        Insert Into WFSDW.FACT_WORKORDERDAY(TemplateWK,
					    ProviderWK,
					    DateWK,
					    WorkOrderCount,
					    LASTETLPROCESS,
					    LASTRUNINSTANCE,
					    CREATEDATE,
					    UPDATEDATE)
        SELECT a.TemplateWK,
	       a.ProviderWK,
               a.DateWK,
               NVL(wo.WO_COUNT, 0),
	       etl_process,
	       run_instance,
	       systimestamp,
	       systimestamp
        FROM (SELECT TemplateWK, ProviderWK, DateWK
              FROM (SELECT DISTINCT TemplateWK, ProviderWK
                    FROM FACT_WORKORDERDETAIL
                    WHERE LOCALSTARTDATEWK BETWEEN TO_CHAR(last_sync_date,'YYYYMMDD') AND TO_CHAR(new_sync_date,'YYYYMMDD')) tp
              CROSS JOIN DIM_DATE d
              WHERE d.DateWK BETWEEN TO_CHAR(last_sync_date,'YYYYMMDD') AND TO_CHAR(new_sync_date,'YYYYMMDD')) a
        LEFT JOIN (SELECT TemplateWK, ProviderWK, LocalStartDateWK, COUNT(*) as WO_COUNT
                   FROM FACT_WORKORDERDETAIL
                   WHERE LOCALSTARTDATEWK BETWEEN TO_CHAR(last_sync_date,'YYYYMMDD') AND TO_CHAR(new_sync_date,'YYYYMMDD')
                   GROUP BY TemplateWK, ProviderWK, LocalStartDateWK) wo
          ON a.TemplateWK = wo.TemplateWK AND
             a.ProviderWK = wo.ProviderWK AND
             a.DateWK = wo.LOCALSTARTDATEWK;

        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 FACT_WorkOrderDay!';

                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;

        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 spAGG_FactWorkOrderDay;

/*
        *****************************************************************************
        PROCEDURE: spAGG_FactWorkOrderMonth
        [ETL - MD Compliant]
        *****************************************************************************
        Aggregates data from the FACT_WORKORDERDETAIL table.

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

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

    PROCEDURE spAGG_FactWorkOrderMonth (
        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;
        last_sync_month             number(6);
        new_sync_month_indicator    number(6);
        error_message               varchar2(500);
        affected_rows               int;
        v_sqlcode                   int;

    BEGIN

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

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

    -----------------------------------------------------------------------------
    -- [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...

    SELECT MAX(TO_CHAR((d.CALENDERDATE - 1),'YYYYMM'))
    INTO new_sync_month_indicator
    FROM FACT_WORKORDERDETAIL f
      JOIN DIM_DATE d
        ON f.LOCALSTARTDATEWK = d.DATEWK;
        
    If new_sync_month_indicator is NULL THEN
    
      -- the Fact table has no records. No point in continuing...
              PKG_METADATA.spMD_ReleaseObject (lockable_object,
                                               etl_process,
                                               run_instance,
                                               last_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;

    last_sync_month := TO_CHAR(last_sync_date,'YYYYMM');

    BEGIN

    If new_sync_month_indicator > last_sync_month THEN

        new_sync_date := (TO_DATE(new_sync_month_indicator,'YYYYMM') - 1/86400);

        Insert Into WFSDW.FACT_WORKORDERMONTH(TemplateWK,
                                            ProviderWK,
                                            MonthWK,
                                            WorkOrderCount,
                                            LASTETLPROCESS,
                                            LASTRUNINSTANCE,
                                            CREATEDATE,
                                            UPDATEDATE)
        SELECT a.TemplateWK, 
               a.ProviderWK, 
               a.MonthWK, 
               NVL(wo.WO_COUNT, 0),
               etl_process,
               run_instance,
               systimestamp,
               systimestamp
        FROM (SELECT TemplateWK, ProviderWK, MonthWK
              FROM (SELECT DISTINCT TemplateWK, ProviderWK
                    FROM FACT_WORKORDERDETAIL
                      JOIN DIM_DATE
                        ON LOCALSTARTDATEWK = DATEWK
                    WHERE CALENDERDATE BETWEEN last_sync_date AND new_sync_date) tp
                CROSS JOIN DIM_MONTH d
              WHERE d.MonthWK BETWEEN last_sync_month AND (new_sync_month_indicator - 1)) a
          LEFT JOIN (SELECT TemplateWK, ProviderWK, to_Char(CalenderDate,'YYYYMM') as MonthWK, COUNT(*) as WO_COUNT
                     FROM FACT_WORKORDERDETAIL
                       JOIN DIM_DATE
                         ON LOCALSTARTDATEWK = DATEWK
                     WHERE CALENDERDATE BETWEEN last_sync_date AND new_sync_date
                     GROUP BY TemplateWK, ProviderWK, to_Char(CalenderDate,'YYYYMM')) wo
            ON a.TemplateWK = wo.TemplateWK AND
               a.ProviderWK = wo.ProviderWK AND
               a.MonthWK = wo.MonthWK;

          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;

	END IF;

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

                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;

        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 spAGG_FactWorkOrderMonth;


/*
        *****************************************************************************
        PROCEDURE: spAGG_FactResourceQueueDay
        [ETL - MD Compliant]
        *****************************************************************************
        Aggregates data from the FACT_RESOURCEQUEUEDETAIL table.

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

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

    PROCEDURE spAGG_FactResourceQueueDay (
        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 := 'spAGG_FactResourceQueueDay';
    affected_rows := 0;

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

    -----------------------------------------------------------------------------
    -- [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...

    SELECT MAX(d.CALENDERDATE - 1)
    INTO new_sync_date
    FROM FACT_RESOURCEQUEUEDETAIL f
      JOIN DIM_DATE d
        ON f.LOCALENTERDATEWK = d.DATEWK;

    BEGIN

        Insert Into WFSDW.FACT_RESOURCEQUEUEDAY(TemplateWK,
                                                WorkOrderWK,
						NodeWK,
						ResourceWK,
						ResourceGroupWK,
                                                DateWK,
                                            	TotalDuration,
					        TotalQueued,
                                                LASTETLPROCESS,
                                                LASTRUNINSTANCE,
                                                CREATEDATE,
                                                UPDATEDATE)
         SELECT a.TemplateWK,
               a.WorkOrderWK,
               a.NodeWK,
               a.ResourceWK,
               a.ResourceGroupWK,
               a.DateWK,
	       NVL(rq.RQ_SUM, 0),
               NVL(rq.RQ_CNT, 0),
               etl_process,
               run_instance,	       
               systimestamp,
               systimestamp
        FROM (SELECT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, DateWK
              FROM (SELECT DISTINCT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK
                    FROM FACT_RESOURCEQUEUEDETAIL
                    WHERE LOCALENTERDATEWK BETWEEN TO_CHAR(last_sync_date,'YYYYMMDD') AND TO_CHAR(new_sync_date,'YYYYMMDD')) tp
              CROSS JOIN DIM_DATE d
              WHERE d.DateWK BETWEEN TO_CHAR(last_sync_date,'YYYYMMDD') AND TO_CHAR(new_sync_date,'YYYYMMDD')) a
        LEFT JOIN (SELECT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, LocalEnterDateWK, COUNT(*) as RQ_CNT, SUM(Duration) as RQ_SUM
                   FROM FACT_RESOURCEQUEUEDETAIL
                   WHERE LOCALENTERDATEWK BETWEEN TO_CHAR(last_sync_date,'YYYYMMDD') AND TO_CHAR(new_sync_date,'YYYYMMDD')
                   GROUP BY TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, LocalEnterDateWK) rq
          ON a.TemplateWK = rq.TemplateWK AND
             a.WorkOrderWK = rq.WorkOrderWK AND
             a.NodeWK = rq.NodeWK AND
             a.ResourceWK = rq.ResourceWK AND
             a.ResourceGroupWK = rq.ResourceGroupWK AND
             a.DateWK = rq.LOCALENTERDATEWK;


        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 FACT_ResourceQueueDay!';

                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;

        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 spAGG_FactResourceQueueDay;

/*
        *****************************************************************************
        PROCEDURE: spAGG_FactResourceQueueMonth
        [ETL - MD Compliant]
        *****************************************************************************
        Aggregates data from the FACT_RESOURCEQUEUEDETAIL table.

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

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

    PROCEDURE spAGG_FactResourceQueueMonth (
        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;
        last_sync_month             number(6);
        new_sync_month_indicator    number(6);
        error_message               varchar2(500);
        affected_rows               int;
        v_sqlcode                   int;

    BEGIN

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

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

    -----------------------------------------------------------------------------
    -- [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...

    SELECT MAX(TO_CHAR((d.CALENDERDATE - 1),'YYYYMM'))
    INTO new_sync_month_indicator
    FROM FACT_WORKORDERDETAIL f
      JOIN DIM_DATE d
        ON f.LOCALSTARTDATEWK = d.DATEWK;
        
    If new_sync_month_indicator is NULL THEN
    
      -- the Fact table has no records. No point in continuing...
              PKG_METADATA.spMD_ReleaseObject (lockable_object,
                                               etl_process,
                                               run_instance,
                                               last_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;

    last_sync_month := TO_CHAR(last_sync_date,'YYYYMM');

    BEGIN

    If new_sync_month_indicator > last_sync_month THEN

        new_sync_date := (TO_DATE(new_sync_month_indicator,'YYYYMM') - 1/86400);

        Insert Into WFSDW.FACT_RESOURCEQUEUEMONTH(TemplateWK,
                                              WorkOrderWK,
					      NodeWK,
					      ResourceWK,
					      ResourceGroupWK,
					      MonthWK,
                                              TotalDuration,
					      TotalQueued,
                                              LASTETLPROCESS,
                                              LASTRUNINSTANCE,
                                              CREATEDATE,
                                              UPDATEDATE)
        SELECT a.TemplateWK,
               a.WorkOrderWK,
	       a.NodeWK,
	       a.ResourceWK,
	       a.ResourceGroupWK,
	       a.MonthWK,
               NVL(rq.RQ_SUM, 0),
	       NVL(rq.RQ_CNT, 0),		
               etl_process,
               run_instance,
               systimestamp,
               systimestamp
        FROM (SELECT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, MonthWK
              FROM (SELECT DISTINCT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK
                    FROM FACT_RESOURCEQUEUEDETAIL
                      JOIN DIM_DATE
                        ON LOCALENTERDATEWK = DATEWK
                    WHERE CALENDERDATE BETWEEN last_sync_date AND new_sync_date) tp
                CROSS JOIN DIM_MONTH d
              WHERE d.MonthWK BETWEEN last_sync_month AND (new_sync_month_indicator - 1)) a
          LEFT JOIN (SELECT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, to_Char(CalenderDate,'YYYYMM') as MonthWK, COUNT(*) as RQ_CNT, SUM(Duration) as RQ_SUM
                     FROM FACT_RESOURCEQUEUEDETAIL
                       JOIN DIM_DATE
                         ON LOCALENTERDATEWK = DATEWK
                     WHERE CALENDERDATE BETWEEN last_sync_date AND new_sync_date
                     GROUP BY TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, to_Char(CalenderDate,'YYYYMM')) rq 
            ON a.TemplateWK = rq.TemplateWK AND
               a.WorkOrderWK = rq.WorkOrderWK AND
	       a.NodeWK = rq.NodeWK AND
	       a.ResourceWK = rq.ResourceWK AND
	       a.ResourceGroupWK = rq.ResourceGroupWK AND
               a.MonthWK = rq.MonthWK;

          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;

        END IF;

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

                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;

        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 spAGG_FactResourceQueueMonth;


/*
        *****************************************************************************
        PROCEDURE: spAGG_FactResourceTaskDay
        [ETL - MD Compliant]
        *****************************************************************************
        Aggregates data from the FACT_RESOURCETASKDETAIL table.

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

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

    PROCEDURE spAGG_FactResourceTaskDay (
        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 := 'spAGG_FactResourceTaskDay';
    affected_rows := 0;

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

    -----------------------------------------------------------------------------
    -- [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...

    SELECT MAX(d.CALENDERDATE - 1)
    INTO new_sync_date
    FROM FACT_RESOURCETASKDETAIL f
      JOIN DIM_DATE d
        ON f.LOCALENTERDATEWK = d.DATEWK;

    BEGIN

        Insert Into WFSDW.FACT_RESOURCETASKDAY(TemplateWK,
                                               WorkOrderWK,
                                               NodeWK,
                                               ResourceWK,
                                               ResourceGroupWK,
                                               DateWK,
                                               TotalDuration,
					       TotalTasks,
					       TotalErrors,
                                               LASTETLPROCESS,
                                               LASTRUNINSTANCE,
                                               CREATEDATE,
                                               UPDATEDATE)
         SELECT a.TemplateWK,
               a.WorkOrderWK,
               a.NodeWK,
               a.ResourceWK,
               a.ResourceGroupWK,
               a.DateWK,
               NVL(rt.RT_SUM,0),
               NVL(rt.RT_COUNT,0),
	       NVL(rte.RTFAIL_COUNT,0),
	       etl_process,
               run_instance,
               systimestamp,
               systimestamp
        FROM (SELECT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, DateWK
              FROM (SELECT DISTINCT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK
                    FROM FACT_RESOURCETASKDETAIL
                    WHERE LOCALENTERDATEWK BETWEEN TO_CHAR(last_sync_date,'YYYYMMDD') AND TO_CHAR(new_sync_date,'YYYYMMDD')) tp
              CROSS JOIN DIM_DATE d
              WHERE d.DateWK BETWEEN TO_CHAR(last_sync_date,'YYYYMMDD') AND TO_CHAR(new_sync_date,'YYYYMMDD')) a
        LEFT JOIN (SELECT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, LocalEnterDateWK, COUNT(*) as RT_COUNT, SUM(Duration) as RT_SUM
                   FROM FACT_RESOURCETASKDETAIL
                   WHERE LOCALENTERDATEWK BETWEEN TO_CHAR(last_sync_date,'YYYYMMDD') AND TO_CHAR(new_sync_date,'YYYYMMDD')
                   GROUP BY TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, LocalEnterDateWK) rt
          ON a.TemplateWK = rt.TemplateWK AND
             a.WorkOrderWK = rt.WorkOrderWK AND
             a.NodeWK = rt.NodeWK AND
             a.ResourceWK = rt.ResourceWK AND
             a.ResourceGroupWK = rt.ResourceGroupWK AND
             a.DateWK = rt.LOCALENTERDATEWK
        LEFT JOIN  (SELECT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, LocalEnterDateWK, COUNT(*) as RTFAIL_COUNT 
                    FROM FACT_RESOURCETASKDETAIL frt2
                      JOIN DIM_WORKORDERSTATUS dwos ON frt2.OPERSTATUSWK = dwos.WORKORDERSTATUSWK 
                    WHERE dwos.Name = 'ERROR' AND
                         LOCALENTERDATEWK BETWEEN TO_CHAR(last_sync_date,'YYYYMMDD') AND TO_CHAR(new_sync_date,'YYYYMMDD')
                    GROUP BY TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, LocalEnterDateWK) rte
           ON a.TemplateWK = rte.TemplateWK AND
             a.WorkOrderWK = rte.WorkOrderWK AND
             a.NodeWK = rte.NodeWK AND
             a.ResourceWK = rte.ResourceWK AND
             a.ResourceGroupWK = rte.ResourceGroupWK AND
             a.DateWK = rte.LOCALENTERDATEWK;

        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 FACT_ResourceTaskDay!';

                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;

        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 spAGG_FactResourceTaskDay;

/*
        *****************************************************************************
        PROCEDURE: spAGG_FactResourceTaskMonth
        [ETL - MD Compliant]
        *****************************************************************************
        Aggregates data from the FACT_RESOURCETASKDETAIL table.

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

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

    PROCEDURE spAGG_FactResourceTaskMonth (
        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;
        last_sync_month             number(6);
        new_sync_month_indicator    number(6);
        error_message               varchar2(500);
        affected_rows               int;
        v_sqlcode                   int;

    BEGIN

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

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

    -----------------------------------------------------------------------------
    -- [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...

    SELECT MAX(TO_CHAR((d.CALENDERDATE - 1),'YYYYMM'))
    INTO new_sync_month_indicator
    FROM FACT_WORKORDERDETAIL f
      JOIN DIM_DATE d
        ON f.LOCALSTARTDATEWK = d.DATEWK;
        
    If new_sync_month_indicator is NULL THEN
    
      -- the Fact table has no records. No point in continuing...
              PKG_METADATA.spMD_ReleaseObject (lockable_object,
                                               etl_process,
                                               run_instance,
                                               last_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;

    last_sync_month := TO_CHAR(last_sync_date,'YYYYMM');

    BEGIN

    If new_sync_month_indicator > last_sync_month THEN

        new_sync_date := (TO_DATE(new_sync_month_indicator,'YYYYMM') - 1/86400);

        Insert Into WFSDW.FACT_RESOURCETASKMONTH(TemplateWK,
                                                 WorkOrderWK,
                                                 NodeWK,
                                                 ResourceWK,
                                                 ResourceGroupWK,
                                                 MonthWK,
                                                 TotalDuration,
						 TotalTasks,
						 TotalErrors,
                                                 LASTETLPROCESS,
                                                 LASTRUNINSTANCE,
                                                 CREATEDATE,
                                                 UPDATEDATE)
        SELECT a.TemplateWK,
               a.WorkOrderWK,
               a.NodeWK,
               a.ResourceWK,
               a.ResourceGroupWK,
               a.MonthWK,
               NVL(rt.RT_SUM, 0),
	       NVL(rt.RT_COUNT, 0),
	       NVL(rte.RTFAIL_COUNT, 0),
               etl_process,
               run_instance,
               systimestamp,
               systimestamp
        FROM (SELECT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, MonthWK
              FROM (SELECT DISTINCT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK
                    FROM FACT_RESOURCETASKDETAIL
                      JOIN DIM_DATE
                        ON LOCALENTERDATEWK = DATEWK
                    WHERE CALENDERDATE BETWEEN last_sync_date AND new_sync_date) tp
                CROSS JOIN DIM_MONTH d
              WHERE d.MonthWK BETWEEN last_sync_month AND (new_sync_month_indicator - 1)) a
          LEFT JOIN (SELECT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, to_Char(CalenderDate,'YYYYMM') as MonthWK, COUNT(*) as RT_COUNT, SUM(Duration) as RT_SUM
                     FROM FACT_RESOURCETASKDETAIL
                       JOIN DIM_DATE
                         ON LOCALENTERDATEWK = DATEWK
                     WHERE CALENDERDATE BETWEEN last_sync_date AND new_sync_date
                     GROUP BY TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, to_Char(CalenderDate,'YYYYMM')) rt
            ON a.TemplateWK = rt.TemplateWK AND
               a.WorkOrderWK = rt.WorkOrderWK AND
               a.NodeWK = rt.NodeWK AND
               a.ResourceWK = rt.ResourceWK AND
               a.ResourceGroupWK = rt.ResourceGroupWK AND
               a.MonthWK = rt.MonthWK
           LEFT JOIN (SELECT TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, to_Char(CalenderDate,'YYYYMM') as MonthWK, COUNT(*) as RTFAIL_COUNT 
                     FROM FACT_RESOURCETASKDETAIL
                       JOIN DIM_DATE
                         ON LOCALENTERDATEWK = DATEWK
		       JOIN DIM_WORKORDERSTATUS
			 ON OPERSTATUSWK = WORKORDERSTATUSWK
                     WHERE CALENDERDATE BETWEEN last_sync_date AND new_sync_date AND
                           NAME = 'ERROR'
                     GROUP BY TemplateWK, WorkOrderWK, NodeWK, ResourceWK, ResourceGroupWK, to_Char(CalenderDate,'YYYYMM')) rte
            ON a.TemplateWK = rte.TemplateWK AND
               a.WorkOrderWK = rte.WorkOrderWK AND
               a.NodeWK = rte.NodeWK AND
               a.ResourceWK = rte.ResourceWK AND
               a.ResourceGroupWK = rte.ResourceGroupWK AND
               a.MonthWK = rte.MonthWK;

          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;

        END IF;

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

                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;

        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 spAGG_FactResourceTaskMonth;

/*
        *****************************************************************************
        PROCEDURE: spAGG_FactMDHistoryDay
        [ETL - MD Compliant]
        *****************************************************************************
        Aggregates data from the FACT_MDHISTORYDETAIL table.

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

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

    PROCEDURE spAGG_FactMDHistoryDay (
        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 := 'spAGG_FactMDHistoryDay';
    affected_rows := 0;

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

    -----------------------------------------------------------------------------
    -- [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...

    SELECT MAX(d.CALENDERDATE - 1)
    INTO new_sync_date
    FROM FACT_MDHISTORYDETAIL f
      JOIN DIM_DATE d
        ON f.LOCALDATEWK = d.DATEWK;

    BEGIN

	Insert Into WFSDW.FACT_MDHISTORYDAY(MONITOREDDRIVEWK,
                                            MONITOREDDRIVESTATUSWK,
                                            LOCALDATEWK,
                                            BYTESUSED,
					    PERCENTUSED,
                                            LASTETLPROCESS,
                                            LASTRUNINSTANCE,
                                            CREATEDATE,
                                            UPDATEDATE)
	SELECT a.MONITOREDDRIVEWK,
               MONITOREDDRIVESTATUSWK,
               LOCALDATEWK,
	       a.MDH_MAX,
	       NVL((a.MDH_MAX/d.totalcapacity)*100,0),
	       etl_process,
               run_instance,
               systimestamp,
               systimestamp
	FROM (SELECT MONITOREDDRIVEWK, 
                     MONITOREDDRIVESTATUSWK,
                     LOCALDATEWK,
                     MAX(CurrentBytesUsed) as MDH_MAX
	      FROM FACT_MDHISTORYDETAIL
	      WHERE LOCALDATEWK BETWEEN TO_CHAR(last_sync_date,'YYYYMMDD') AND TO_CHAR(new_sync_date,'YYYYMMDD')
              GROUP BY MONITOREDDRIVEWK, MONITOREDDRIVESTATUSWK, LOCALDATEWK) a
	  JOIN DIM_MONITOREDDRIVE d
	    ON a.MONITOREDDRIVEWK = d.MONITOREDDRIVEWK; 

        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 FACT_MDHistoryDay!';

                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;

        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 spAGG_FactMDHistoryDay;

/*
        *****************************************************************************
        PROCEDURE: spAGG_FactMDHistoryMonth
        [ETL - MD Compliant]
        *****************************************************************************
        Aggregates data from the FACT_MDHISTORYDETAIL table.

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

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

    PROCEDURE spAGG_FactMDHistoryMonth (
        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;
        last_sync_month             number(6);
        new_sync_month_indicator    number(6);
        error_message           varchar2 (500);
        affected_rows           int;
        v_sqlcode               int;

    BEGIN

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

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

    -----------------------------------------------------------------------------
    -- [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...

 SELECT MAX(TO_CHAR((d.CALENDERDATE - 1),'YYYYMM'))
    INTO new_sync_month_indicator
    FROM FACT_MDHISTORYDETAIL f
      JOIN DIM_DATE d
        ON f.LOCALDATEWK = d.DATEWK;
        
    If new_sync_month_indicator is NULL THEN
    
      -- the Fact table has no records. No point in continuing...
              PKG_METADATA.spMD_ReleaseObject (lockable_object,
                                               etl_process,
                                               run_instance,
                                               last_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;

    last_sync_month := TO_CHAR(last_sync_date,'YYYYMM');

    BEGIN

	If new_sync_month_indicator > last_sync_month THEN

       	   new_sync_date := (TO_DATE(new_sync_month_indicator,'YYYYMM') - 1/86400);

           Insert Into WFSDW.FACT_MDHISTORYMONTH(MONITOREDDRIVEWK,
                                                 MONTHWK,
                                                 MAXPERCENTUSED,
                                                 LASTETLPROCESS,
                                                 LASTRUNINSTANCE,
                                                 CREATEDATE,
                                                 UPDATEDATE)
           SELECT f.MONITOREDDRIVEWK, 
	          to_char(d.CALENDERDATE,'YYYYMM'), 
	          MAX(ROUND(NVL((f.currentbytesused/md.totalcapacity)*100,0),2)),
	          etl_process,
                  run_instance,
                  systimestamp,
                  systimestamp
                 FROM FACT_MDHISTORYDETAIL f
                   JOIN DIM_MONITOREDDRIVE md
                     ON f.MONITOREDDRIVEWK = md.MONITOREDDRIVEWK
	           JOIN DIM_DATE d
	             ON f.LOCALDATEWK = d.DATEWK
                 WHERE d.CALENDERDATE > last_sync_date AND 
                       d.CALENDERDATE <= new_sync_date
                 GROUP BY f.MONITOREDDRIVEWK, to_char(d.CALENDERDATE,'YYYYMM');

           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;

	END IF;

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

                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;

        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 spAGG_FactMDHistoryMonth;

END;
/
