CREATE OR REPLACE PACKAGE WFSDW.PKG_ADMIN
AS
  /*
        *****************************************************************************
        PROCEDURE:  spMD_LoadData
        Purpose  :  This is used to Load data from Prod Schema to Data Warehouse.

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

        *****************************************************************************
        [USED BY]
        *****************************************************************************
        [By Suresh Neravati     - 10/10/2006]
        [Oracle version         - 10/10/2006]
        *****************************************************************************
        Change Log:

        *****************************************************************************
*/
PROCEDURE spMD_LoadData;

PROCEDURE Change_LastSyncDate (LocableObjId IN number);

PROCEDURE Initial_SetUp (AcceptSetUp IN BOOLEAN);

PROCEDURE SP_ReInitializeSequence (tablename IN varchar2 , idcolumn IN varchar2);

END;
/


CREATE OR REPLACE PACKAGE BODY WFSDW.PKG_ADMIN
AS
 /*
        *****************************************************************************
        PROCEDURE:  spMD_LoadData
        Purpose  :  This is used to Load data from Prod Schema to Data Warehouse.
        *****************************************************************************
        [USED BY]
        *****************************************************************************
        [By Suresh Neravati     - 10/10/2006]
        [Oracle version         - 10/10/2006]
        *****************************************************************************
        Change Log:
            1)  Added the Procedure spMD_LoadData on 10/10/2006
                by Suresh Neravati.
            2)  Added the Procedure Change_LastSyncDate on 10/10/2006
                by Suresh Neravati.
            3)  Added the Procedure Initial_SetUp on 10/10/2006
                by Suresh Neravati.
            4)  Added the Procedure SP_ReInitializeSequence on 10/11/2006
                by Laura Francheri.

        *****************************************************************************
*/
    PROCEDURE spMD_LoadData AS
        v_sqlcode   NUMBER;
        etl_process     INT;
        run_instance INT;
        error_status INT;
        failedProcs varchar2(500);
        DataLoadException EXCEPTION;
        etlProcessName VARCHAR2(50);
        v_ok_procs int;
    BEGIN
        dbms_output.enable(1000000);
        -- Detect and fix WEIRD BUG
        SELECT  COUNT(*) INTO v_ok_procs
        FROM    MD_LockableObject
        WHERE   LastObjectStatus = 0;

        IF v_ok_procs = 0 THEN
            dbms_output.put_line('******* At if loop *******');
            UPDATE MD_LockableObject SET LastObjectStatus = 0;
            commit;
        END IF;

        PKG_DATALOAD.SPSYNC_WorkOrderStatus(null,null,1,error_status);
        IF error_status!=0 THEN
           failedProcs:='SPSYNC_WorkOrderStatus,';
        END IF;

        PKG_DATALOAD.SPSYNC_Template(null,null,1,error_status);
        IF error_status!=0 THEN
            failedProcs:='SPSYNC_Template,';
        END IF;

/*        PKG_DATALOAD.SPSYNC_MonitoredDrive(null,null,1,error_status);
        IF error_status!=0 THEN
            failedProcs:='SPSYNC_MonitoredDrive,';
        END IF;
*/
        PKG_DATALOAD.SPSYNC_Node(null,null,1,error_status);
        IF error_status!=0 THEN
            failedProcs:='SPSYNC_Node,';
        END IF;

        PKG_DATALOAD.SPSYNC_Resource(null,null,1,error_status);
        IF error_status!=0 THEN
            failedProcs:=failedProcs||'SPSYNC_Resource,';
        END IF;

        PKG_DATALOAD.SPSYNC_ResourceGroup(null,null,1,error_status);
        IF error_status!=0 THEN
            failedProcs:=failedProcs||'SPSYNC_ResourceGroup,';
        END IF;

        PKG_DATALOAD.SPSYNC_VariableInstance(null,null,1,error_status);
        IF error_status!=0 THEN
            failedProcs:=failedProcs||'SPSYNC_VariableInstance,';
        END IF;

        PKG_DATALOAD.SPSYNC_WorkOrder(null,null,1,error_status);
        IF error_status!=0 THEN
            failedProcs:=failedProcs||'SPSYNC_WorkOrder,';
        END IF;

/*      PKG_DATAAGG.SPAGG_FactWorkOrderDay(null,null,1,error_status);
        IF error_status!=0 THEN
           failedProcs:=failedProcs||'SPAGG_FactWorkOrderDay,';
        END IF;
*/
        PKG_DATAAGG.SPAGG_FactWorkOrderMonth(null,null,1,error_status);
        IF error_status!=0 THEN
           failedProcs:=failedProcs||'SPAGG_FactWorkOrderMonth,';
        END IF;

        PKG_DATALOAD.SPSYNC_Log(null,null,1,error_status);
        IF error_status!=0 THEN
            failedProcs:=failedProcs||'SPSYNC_Log,';
        END IF;

/*      PKG_DATAAGG.SPAGG_FactResourceQueueDay(null,null,1,error_status);
        IF error_status!=0 THEN
           failedProcs:=failedProcs||'SPAGG_FactResourceQueueDay,';
        END IF;
*/
        PKG_DATAAGG.SPAGG_FactResourceQueueMonth(null,null,1,error_status);
        IF error_status!=0 THEN
           failedProcs:=failedProcs||'SPAGG_FactResourceQueueMonth,';
        END IF;

/*      PKG_DATAAGG.SPAGG_FactResourceTaskDay(null,null,1,error_status);
        IF error_status!=0 THEN
           failedProcs:=failedProcs||'SPAGG_FactResourceTaskDay,';
        END IF;
*/
        PKG_DATAAGG.SPAGG_FactResourceTaskMonth(null,null,1,error_status);
        IF error_status!=0 THEN
           failedProcs:=failedProcs||'SPAGG_FactResourceTaskMonth,';
        END IF;

        PKG_DATALOAD.SPSYNC_MonitoredDriveHistory(null,null,1,error_status);
        IF error_status!=0 THEN
            failedProcs:=failedProcs||'SPSYNC_MonitoredDriveHistory,';
        END IF;

/*      PKG_DATAAGG.spAGG_FactMDHistoryDay(null,null,1,error_status);
        IF error_status!=0 THEN
           failedProcs:=failedProcs||'SPAGG_FactMDHistoryDay,';
        END IF;
*/
        PKG_DATAAGG.spAGG_FactMDHistoryMonth(null,null,1,error_status);
        IF error_status!=0 THEN
           failedProcs:=failedProcs||'SPAGG_FactMDHistoryMonth,';
        END IF;

        IF LENGTH(trim(failedProcs))>0 THEN
            DBMS_OUTPUT.PUT_LINE('Data loading error in '||substr(failedProcs,1,length(failedProcs)-1));
            RAISE DataLoadException;
        ELSE
            DBMS_OUTPUT.PUT_LINE('******   Data loaded successfully   *********');
        END IF;
    EXCEPTION
        WHEN DataLoadException THEN
            PKG_METADATA.spMD_WriteEventLog ('spMD_LoadData', v_sqlcode, section => 'Data loading error in '||substr(failedProcs,1,length(failedProcs)-1));
        WHEN OTHERS THEN
            v_sqlcode := SQLCODE;
            ROLLBACK;
            -- Not raising application errors at this point!
            -- raise_application_error(-20001, 'spMD_LogInfoMessage ERROR: error inserting MD_ETLProcessMessage');
            PKG_METADATA.spMD_WriteEventLog ('spMD_LoadData', v_sqlcode, section => 'Error in loading the data');
            COMMIT;

    END spMD_LoadData;
/*
        *****************************************************************************
        PROCEDURE:  Change_LastSyncDate
        Purpose  :  This is used by Load data while loading data to Warehouse.
        *****************************************************************************
        [USED BY]
        *****************************************************************************
        [By Suresh Neravati     - 10/11/2006]
        [Oracle version         - 10/11/2006]
        *****************************************************************************
        Change Log:

        *****************************************************************************
*/
    PROCEDURE Change_LastSyncDate (LocableObjId IN number) IS
      rowcnt number(10);
      newdate timestamp;
    BEGIN
      UPDATE md_lockableobject SET lastsyncdate=lastsyncdate-365,LastObjectStatus=0 WHERE lockableobjectkey = LocableObjId;
      rowcnt:=sql%rowcount;
      IF rowcnt=1 THEN
        COMMIT;
        SELECT lastsyncdate INTO newdate FROM MD_LOCKABLEOBJECT where lockableobjectkey = LocableObjId;
        DBMS_OUTPUT.PUT_LINE('object '||LocableObjId||' last sync dated updated to '||newdate);
      END IF;
    EXCEPTION
       WHEN OTHERS THEN
         NULL;
    END Change_LastSyncDate;

/*
        *****************************************************************************
        PROCEDURE:  Initial_SetUp
        Purpose  :  This is used by Load data while loading data to Warehouse.
        *****************************************************************************
        [USED BY]
        *****************************************************************************
        [By Suresh Neravati     - 10/10/2006]
        [Oracle version         - 10/10/2006]
        *****************************************************************************
        Change Log:

        *****************************************************************************
*/
    PROCEDURE Initial_SetUp (AcceptSetUp IN BOOLEAN) IS
    BEGIN
      IF AcceptSetUp THEN
        UPDATE MD_LockableObject SET LastETLProcess=999999, LastRunInstance=1;
        COMMIT;
        dbms_output.put_line('Process and instance set in the LocableObject table');
      END IF;

    END Initial_SetUp;

/*
        *****************************************************************************
        PROCEDURE:  SP_ReInitializeSequence
        Purpose  :  This is used by Load data while loading data to Warehouse.
        *****************************************************************************
        [USED BY]
        *****************************************************************************
        [By  - 10/10/2006]
        [Oracle version         - 10/10/2006]
        *****************************************************************************
        Change Log:

        *****************************************************************************
*/
    PROCEDURE SP_ReInitializeSequence (tablename IN varchar2 , idcolumn IN varchar2)
    IS
        sequencename    varchar2 (100);
        maxsequence     INTEGER;
        sql_max         varchar2 (500);
        sql_createseq   varchar2 (500);
        sql_dropseq     varchar2 (500);
        varcount        INTEGER;

    BEGIN

        -- DBMS_OUTPUT.PUT_LINE('Working on: ' || tablename);

        sequencename := 'WFSDW.' || tablename || '_seq';

        -- DBMS_OUTPUT.PUT_LINE('Sequence:' || sequencename);

        sql_max  := 'SELECT MAX(';
        sql_max  := sql_max || idcolumn;
        sql_max   := sql_max || ') FROM ' || tablename;

        EXECUTE IMMEDIATE sql_max INTO varcount;

        -- DBMS_OUTPUT.PUT_LINE('Current Sequence Number:');

        varcount := NVL(varcount,0) + 1;
        -- DBMS_OUTPUT.PUT_LINE (varcount);

        -- Drop the sequence
        sql_dropseq  := 'DROP SEQUENCE ' || sequencename;
        -- DBMS_OUTPUT.PUT_LINE (sql_dropseq);
        BEGIN
        EXECUTE IMMEDIATE sql_dropseq;

            EXCEPTION
               WHEN OTHERS THEN
                    null;
       END;

        -- Recreate the sequence
        sql_createseq   := 'CREATE SEQUENCE ' || sequencename;
        sql_createseq   := sql_createseq || ' START WITH ' || varcount;
        sql_createseq   := sql_createseq || ' INCREMENT BY 1';

        -- DBMS_OUTPUT.PUT_LINE (sql_createseq);

        BEGIN
        EXECUTE IMMEDIATE sql_createseq;
        EXCEPTION
            WHEN OTHERS THEN null;
        END;
        DBMS_OUTPUT.PUT_LINE('Sequence ' || sequencename || ' updated to ' || varcount);

    END SP_ReInitializeSequence;

END;
/
