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

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

	*****************************************************************************
	This package contains stored procedures and functions that provide data
    required for the Reports.
	*****************************************************************************
	[USED BY]	JReports
	*****************************************************************************
	[By Laura Francheri	- 10/16/2006]
    [Major rewrite for new structures by Chris Scherlacher 4/25/2007]
	*****************************************************************************
	Change Log:
            1)  Added the Procedure spRPTWorkOrders on 10/16/2006
                by Suresh Neravati.
            2)  Added the Procedure spStr_To_Strs on 10/16/2006
                by Suresh Neravati.
            3)  Added the Procedure spRPTWOsForAllProv on 11/09/2006
                by Imran Naqvi.
            4)  Added the Procedure spRPTAvgQTimeByRG on 11/10/2006
                by Imran Naqvi.
            5)  Added the Procedure spRPTAvgTaskDurByRG on 11/10/2006
                by Imran Naqvi.
            6)  Added the Procedure spRPTAvgTaskDurByRG on 11/10/2006
                by Kinjal Meheta.
            7)  Added the Procedure spContntListByProvdrAssetTyp on 11/10/2006
                by Sahil Verma.
            8)  Added the Procedure spWOsLicenseWindowMiss on 11/10/2006
                by Sahil Verma.
            9)  Added the Procedure spFailureTrendByResGroup on 11/14/2006
                by Suresh Neravati.
            10) Added the Procedure spFailureTrendByResource on 11/15/2006
                by Suresh Neravati.
            11) Added the Procedure spRPTDriveUsage on 11/15/2006
                by Silva Vijay.
            12) Added the Procedure spWOsPerPackageAssetID on 11/15/2006
                by Jakobac, Vladimir.
            13) Added the Procedure spRPTWOsPurge on 11/15/2006
                by Imran Naqvi.
            14) Rewrite of spRPTWorkOrders on 4/25/2007
	*****************************************************************************
*/
  TYPE WosCurType IS REF CURSOR ;
  TYPE wosRec is RECORD(ProviderName varchar2(50),
                        TemplateName varchar2(50),
                        monthName    varchar2(50),
                        month_cnt    number(10));
  PROCEDURE spRPTWorkOrders( from_       IN  varchar2,
                             to_         IN  varchar2,
                             template    IN  varchar2,
                             Provider_WK IN  integer,
                             wosData     OUT WosCurType);

  TYPE PLSQLStrTabType IS TABLE OF Varchar2(100) INDEX BY BINARY_INTEGER;

  PROCEDURE spStr_To_Strs(pVar IN Varchar2, OutPLSQLTable OUT PLSQLStrTabType);

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

  TYPE WosForAllProvCurType IS REF CURSOR ;
  TYPE WOsForAllProvRec is RECORD(TemplateName varchar2(50),
                        monthName    varchar2(50),
                        month_cnt    number(10));
  PROCEDURE spRPTWOsForAllProv( from_    IN  varchar2,
                             to_         IN  varchar2,
                             template    IN  varchar2,
                             wosData     OUT WosForAllProvCurType);

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

  TYPE CURSORTYPE IS REF CURSOR;
	/* Returns a list of assets by provider and asset types */
  PROCEDURE spContntListByProvdrAssetTyp(   FromDate    IN  varchar2,
                                                ToDate      IN  varchar2,
                                                Provider    IN  varchar2,
                                                AssetTypes  IN  varchar2,
                                                ContentList OUT CURSORTYPE);

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

  /** Returns a list of work orders for a given package asset ID **/
  PROCEDURE spWOsPerPackageAssetID(fromDate         IN varchar2,
                                   toDate           IN varchar2,
                                   packageAssetID   IN varchar2,
                                   WorkOrders       OUT CURSORTYPE);

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

	/*
	 * Returns a list of work orders that did not finish processing an asset by the
	 * time it was ready to be broadcasted i.e. it's license window had opened
	 */
  PROCEDURE spWOsLicenseWindowMiss(fromDate IN varchar2,
                                   toDate IN varchar2,
                                   WorkOrders OUT CURSORTYPE);--6

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

  PROCEDURE spRPTWOsPurge(  from_ IN varchar2,
                            to_ IN varchar2,
                            provider IN  varchar2,
                            wosData OUT CURSORTYPE);

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

  TYPE WosAvgQTimeByRGCurType IS REF CURSOR ;
  TYPE WosAvgQTimeByRGRec is RECORD(  ResourceGroupName   varchar2(50),
                                            monthName           varchar2(50),
                                            monthTime_avg       number(10));
  PROCEDURE spRPTAvgQTimeByRG(  from_         IN  varchar2,
                                to_           IN  varchar2,
                                resourceGroup IN  varchar2,
                                wosData       OUT WosAvgQTimeByRGCurType);--2

------
  TYPE WosCurType_spTaskDetails IS REF CURSOR ;
  TYPE wosRec_spTaskDetailsByResource is RECORD(
                        ResourceName    varchar2(4000),
                        workorder       number,
                        templateName    varchar2(50),
                        taskName        varchar2(50),
                        title           varchar2(50),
                        starttime       varchar2(50),
                        endtime         varchar2(50),
                        duration        varchar2(50),
                        taskStatus      varchar2(30));

 PROCEDURE spTaskDetailsByResource(
                        startdate IN varchar2,
                        enddate IN varchar2,
                        resources IN varchar2,
                        wosData OUT WosCurType_spTaskDetails);--4

-----
  TYPE WosAvgTaskDurByRGRec is RECORD(  ResourceGroupName   varchar2(50),
                                        monthName           varchar2(50),
                                        monthTime_avg       number(10));

  TYPE WosAvgTaskDurByRGCurType IS REF CURSOR;

  PROCEDURE spRPTAvgTaskDurByRG(   from_         IN  varchar2,
                                   to_           IN  varchar2,
                                   resourceGroup IN  varchar2,
                                   wosData       OUT WosAvgTaskDurByRGCurType); --3
------

  PROCEDURE spFailureTrendByResGroup(  FromDate        IN  varchar2,
                                       ToDate          IN  varchar2,
                                       ResourceGroup   IN  varchar2,
                                       RGData         OUT WosCurType); --rep3

  PROCEDURE spFailureTrendByResource(  FromDate     IN  varchar2,
                                       ToDate       IN  varchar2,
                                       Resource_    IN  varchar2,
                                       ResourceData OUT WosCurType); --rep4

------
  TYPE WosDriveUsageCurType IS REF CURSOR;
  PROCEDURE spRPTDriveUsage ( from_            IN  varchar2,
                              to_              IN  varchar2,
                              driveNames       IN  varchar2,
                              wosData          OUT WosDriveUsageCurType);

----

END;
/

CREATE OR REPLACE PACKAGE BODY WFSDW.PKG_REPORTS
AS
/*
	*****************************************************************************
	PROCEDURE: spRPTWorkOrders
	[REPORTS]
	*****************************************************************************
	This provides the Data required for the Workorders by Template and by Time 
    for the Provider
	[RETURNS] 	WorkOrders RefCursor which contain the work orders generated 
                with in that period and for the templates list and for that given    
                Provider.
	[NOTE]		As per the Development team request at this point I'm not raising 
                application errors . Just uncommenting the raise_application_error 
                calls.
	*****************************************************************************
	[USED BY]	Should be used to generate the WFS Report count of Work Orders 
                by Template, Time and Provider.
	*****************************************************************************
	[SQL Server version by Suresh Neravati 10/16/2006]
	[Oracle version by Suresh - revised by Suresh on 10/16/2006]
    	[New and Improved Oracle version by Chris Scherlacher]
	*****************************************************************************
	Change Log:
	*****************************************************************************
*/
    PROCEDURE spRPTWorkOrders ( from_       IN  varchar2, 
                                to_         IN  varchar2, 
                                template    IN  varchar2, 
                                Provider_WK IN  integer,
                                wosData     OUT WosCurType) IS
        vSQLCode      int;
        vErrMessage   varchar2(500);
        procName      varchar2(50);    
        diff_days     int;
        paramsValid   boolean:=true;
        vtemplate     varchar2(4000);
        vProvider     varchar2(100);
        stmtStr       varchar2(4000);
        firstTemplate boolean:=TRUE;
        qryAddedToStr boolean:=FALSE;
        stmtStr11     varchar2(32767):='';
        stmtStr12     varchar2(32767):='';
        stmtStr13     varchar2(32767):='';
        stmtStr14     varchar2(32767):='';
        qryString     number(3):=1;
        strsUsed      number(3);
        vfromDate     varchar2(20);
        vtoDate       varchar2(20);
        noOfTemplates number(3);
        vPLSQLTabStr  wfsdw.PKG_REPORTS.PLSQLStrTabType;

      BEGIN

        procName := 'spRPTWorkOrders';

               /******** Validation of Parameters ********/

        IF from_ IS NULL OR to_ IS NULL THEN

            --raise_application_error(-20101,'Dates should not be null');
            paramsValid := false;

        ELSE

            SELECT (to_date(to_,'rrrr/mm/dd') - to_date(from_,'rrrr/mm/dd')) INTO diff_days from dual;

            IF diff_days < 0 THEN

                --raise_application_error(-20102,'To Date is less than the From date');
                paramsValid := false;

            END IF;  

        END IF;        

        IF Provider_WK IS NULL THEN

            paramsValid := false;
            --raise_application_error(-20001,'Provider not selected, please select the Provider');

        ELSE

            BEGIN

                SELECT providerName 
                  INTO vProvider 
                  FROM WFSDW.DIM_PROVIDER 
                 WHERE VariableInstanceWK = Provider_WK;

            EXCEPTION

                WHEN NO_DATA_FOUND THEN

                    paramsValid := false;

                WHEN OTHERS THEN

                    paramsValid := false;

            END;

        END IF;

        IF template IS NULL THEN

            paramsValid := false;
            --raise_application_error(-20001,'WorkOrders not selected, please select at least one Work Order from the list');

        ELSE

            wfsdw.pkg_reports.spStr_To_Strs(template, vPLSQLTabStr);

            noOfTemplates:=vPLSQLTabStr.count;

            FOR i IN 1..noOfTemplates
            LOOP
                vtemplate:= ''''||vPLSQLTabStr(i)||'''';

                IF i = 1 THEN

                    stmtStr:= 'SELECT p.ProviderName,t.Name, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0''), wom.WorkOrderCount FROM FACT_WORKORDERMONTH wom JOIN DIM_PROVIDER p ON wom.ProviderWK = p.VariableInstanceWK JOIN DIM_TEMPLATE t ON wom.TemplateWK = t.TemplateWK JOIN DIM_MONTH m ON wom.MonthWK = m.MonthWK WHERE m.MonthWK BETWEEN to_char(to_date('''||from_||''',''RRRR-MM-DD''),''YYYYMM'') AND to_char(to_date('''||to_||''',''RRRR-MM-DD''),''YYYYMM'') AND p.VariableInstanceWK = '||provider_WK||' AND t.Name in ('||vtemplate;

                ELSE 

                  stmtStr:= stmtStr||','||vtemplate;

                END IF;

            END LOOP;

            stmtStr:= stmtStr||')';
           
        END IF;

        /******* Selecting the data into RefCursor *******/
        IF paramsValid THEN            

            OPEN wosData FOR stmtStr;   

        ELSE

            /************ Creating an empty cursor to return ************/
            OPEN wosData FOR 'SELECT p.ProviderName,
                                      t.Name,
                                      m.MonthName,
                                      wom.WorkOrderCount
                               FROM FACT_WORKORDERMONTH wom
                                JOIN DIM_PROVIDER p
                                  ON wom.ProviderWK = p.VariableInstanceWK
                                JOIN DIM_TEMPLATE t
                                  ON wom.TemplateWK = t.TemplateWK
                                JOIN DIM_MONTH m
                                  ON wom.MonthWK = m.MonthWK
                               WHERE 1=2';

        END IF; 

       EXCEPTION
        WHEN INVALID_CURSOR THEN

            raise_application_error(-20890,'No WorkOrders Data created');

        WHEN NO_DATA_FOUND THEN

            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spRPTWosByTemplateTimeProvider', 
						   vSQLCode, 
						   section => 'Query - No data found for the period from '||from_||' to '||to_||' for the templateWK '||template||' and for the prividerId '||provider_WK);

        WHEN OTHERS THEN

            vSQLCode := SQLCODE;
            dbms_output.put_line('Oracle Error '||SQLCODE|| '  DB error.  Can''t provide data from the spRPTWorkOrders..!');
            vErrMessage := procName || ': DB error.  Can''t provide data from the spRPTWosByTemplateTimeProvider..!';
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spRPTWosByTemplateTimeProvider', vSQLCode, section => vErrMessage);   
 
      END spRPTWorkOrders;

   /********************************************************************************                         
   PROCEDURE: spString_Ints
   *****************************************************************************
	This Procedure converts String to Strings values
    
   ********************************************************************************/
 
      PROCEDURE spStr_To_Strs(pVar IN Varchar2, OutPLSQLTable OUT PLSQLStrTabType) IS
        vIntPrevVal int:=1;
        vInt int:=1;
        continueloop boolean:=true;
        vals varchar2(100);
        tabIndex int:=1;
        myStr varchar2(100);

      BEGIN       
 
         WHILE continueloop
         LOOP

            select instr( pVar,',',vInt+1) into vInt from dual;

            IF vInt=0 THEN 

                continueloop:=FALSE;  
                select substr(pVar,vIntPrevVal) into myStr from dual;
                OutPLSQLTable(tabIndex):=trim(myStr);

            ELSE

                select substr(pVar,vIntPrevVal,vInt-vIntPrevVal) into myStr from dual;
                OutPLSQLTable(tabIndex):=trim(myStr);
                vIntPrevVal:=vInt+1;

            END IF; 

            tabIndex:=tabIndex+1;

         END LOOP;    
   
      END spStr_To_Strs;

/*
	*****************************************************************************
	PROCEDURE: spRPTWOsForAllProv
	[REPORTS]
	*****************************************************************************
	This provides the Data required for the Workorders by Template and by Time 
    for all Providers
	[RETURNS] 	WorkOrders RefCursor which contain the work orders generated 
                with in that period and for the templates list.
	*****************************************************************************
	[USED BY]	Should be used to generate the WFS Report count of Work Orders 
                by Template and Time.
	*****************************************************************************
    [Developed by Imran Naqvi 11/09/2006]
    [ReWritten by Chris Scherlacher 4/26/2007]
    *****************************************************************************
	Change Log: 
	*****************************************************************************
*/

    PROCEDURE spRPTWOsForAllProv (  from_       IN  varchar2, 
                                    to_         IN  varchar2, 
                                    template    IN  varchar2, 
                                    wosData     OUT WosForAllProvCurType) IS
        vSQLCode      int;
        vErrMessage   varchar2(500);
        procName      varchar2(50);    
        diff_days     int;
        paramsValid   boolean:=true;
        vtemplate     varchar2(4000);        
        stmtStr       varchar2(4000);
        vfromDate     varchar2(20);
        vtoDate       varchar2(20);
        noOfTemplates number(3);
        vPLSQLTabStr  wfsdw.PKG_REPORTS.PLSQLStrTabType;

      BEGIN

        procName := 'spRPTWOsForAllProv';

               /******** Validation of Parameters ********/
        IF from_ IS NULL OR to_ IS NULL THEN

            --raise_application_error(-20101,'Dates should not be null');

            paramsValid := false;

        ELSE

            SELECT (to_date(to_,'rrrr/mm/dd') - to_date(from_,'rrrr/mm/dd')) INTO diff_days from dual;

            IF diff_days < 0 THEN

                --raise_application_error(-20102,'To Date is less than the From date');

                paramsValid := false;

            END IF;  

        END IF;        

        IF template IS NULL THEN

            paramsValid := false;

            --raise_application_error(-20001,'WorkOrders not selected, please select at least one Work Order from the list');

        ELSE

            wfsdw.pkg_reports.spStr_To_Strs(template, vPLSQLTabStr);

            noOfTemplates:=vPLSQLTabStr.count;

            FOR i IN 1..noOfTemplates
            LOOP

                vtemplate:= ''''||vPLSQLTabStr(i)||'''';

                IF i = 1 THEN

                    stmtStr:= 'SELECT t.Name, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0''), SUM(wom.WorkOrderCount) FROM FACT_WORKORDERMONTH wom JOIN DIM_PROVIDER p ON wom.ProviderWK = p.VariableInstanceWK JOIN DIM_TEMPLATE t ON wom.TemplateWK = t.TemplateWK JOIN DIM_MONTH m ON wom.MonthWK = m.MonthWK WHERE m.MonthWK BETWEEN to_char(to_date('''||from_||''',''RRRR-MM-DD''),''YYYYMM'') AND to_char(to_date('''||to_||''',''RRRR-MM-DD''),''YYYYMM'') AND t.Name in ('||vtemplate;


                ELSE      

                    stmtStr:= stmtStr||','||vtemplate;

                END IF;             
                
            END LOOP;

            stmtStr:= stmtStr||') GROUP BY t.Name, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0'') ORDER BY 1,2';
            
        END IF;

        /******* Selecting the data into RefCursor *******/
        IF paramsValid THEN            

            OPEN wosData FOR stmtStr; 
  
        ELSE
            /************ Creating an empty cursor to return ************/

            OPEN wosData FOR 'SELECT t.Name, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0''), wom.WorkOrderCount FROM FACT_WORKORDERMONTH wom JOIN DIM_PROVIDER p ON wom.ProviderWK = p.VariableInstanceWK JOIN DIM_TEMPLATE t ON wom.TemplateWK = t.TemplateWK JOIN DIM_MONTH m ON wom.MonthWK = m.MonthWK WHERE m.MonthWK BETWEEN to_char(to_date('''||from_||''',''RRRR-MM-DD''),''YYYYMM'') AND to_char(to_date('''||to_||''',''RRRR-MM-DD''),''YYYYMM'') AND 1 = 2';

        END IF; 

       EXCEPTION
        WHEN INVALID_CURSOR THEN
            raise_application_error(-20890,'No WorkOrders Data created');

        WHEN NO_DATA_FOUND THEN
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spRPTWOsForAllProv', vSQLCode, section => 'Query - No data found for the period from '||from_||' to '||to_||' for the templateWK '||template);

        WHEN OTHERS THEN
            vSQLCode := SQLCODE;
            dbms_output.put_line('Oracle Error '||SQLCODE|| '  DB error.  Can''t provide data from the spRPTWorkOrders..!');
            vErrMessage := procName || ': DB error.  Can''t provide data from the spRPTWosByTemplateTimeProvider..!';
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spRPTWosByTemplateTimeProvider', vSQLCode, section => vErrMessage);     

      END spRPTWOsForAllProv;

/*
	*****************************************************************************
	PROCEDURE: spContntListByProvdrAssetTyp
	[REPORTS]
	*****************************************************************************
	Retrieves a list of assets of a specified type that are created by the 
	given list of providers. The only tricky implementation note is that
	we have to convert the comma-delimited provider names into an IN
	expression. This procedure returns the list of assets grouped by month.
    *****************************************************************************
    	[SQL Server version by Sahil Verma 11/09/2006]
	[ReWritten by Chris Scherlacher 4/26/2007]
	*****************************************************************************
	Change Log: 
	*****************************************************************************
*/
	PROCEDURE spContntListByProvdrAssetTyp(   FromDate    IN  varchar2, 
                                                ToDate      IN  varchar2, 
                                                Provider    IN  varchar2, 
                                                AssetTypes  IN  varchar2, 
                                                ContentList OUT CURSORTYPE) IS
        startdate date;
        enddate date;
        errorcode int;
        errormessage varchar2(500);
		tmp wfsdw.PKG_REPORTS.PLSQLStrTabType;
		assetcount number(4);
		assetTypeList varchar2(4000) := '';
		stmt varchar2(4000);
        paramsValid   boolean:=true;
        diff_days     int;

      BEGIN
            --DBMS_OUTPUT.PUT_LINE('Parsing asset types...');

            IF FromDate IS NULL OR ToDate IS NULL THEN
                --raise_application_error(-20101,'Dates should not be null');

                paramsValid := false;

            ELSE

                SELECT (to_date(ToDate,'rrrr/mm/dd') - to_date(FromDate,'rrrr/mm/dd')) INTO diff_days from dual;
                --DBMS_OUTPUT.PUT_LINE(diff_days);

                IF diff_days < 0 THEN
                    --raise_application_error(-20102,'To Date is less than the From date');

                    paramsValid := false;

                ELSE    
                
                    SELECT (to_date(FromDate, 'RRRR-MM-DD HH24:MI:SS')) INTO startdate FROM dual;
                    SELECT (to_date(ToDate, 'RRRR-MM-DD HH24:MI:SS')+1) INTO enddate FROM dual;
                    --SELECT (to_date(round(ToDate,'day'), 'RRRR-MM-DD HH24:MI:SS')) INTO enddate FROM dual;

                    IF Provider IS NULL OR AssetTypes IS NULL THEN

                        paramsValid := FALSE;

                    END IF;

                END IF;  

            END IF;     

            /*
             *	Remember that the input string is 'Movie, Package' and we have to
             * 	convert it to 'Movie', 'Package' to be able to create an IN predicate
             */

            IF  paramsValid THEN 

                wfsdw.pkg_reports.spStr_To_Strs(AssetTypes, tmp);
                assetcount := tmp.count;

                FOR i IN 1..assetcount
                LOOP

                    assetTypeList := assetTypeList || '''' || tmp(i) || '''';

                    if i != assetcount then

                        assetTypeList := assetTypeList || ', ';

                    End IF;

                END LOOP;

                /* Just a simple query, that's all */
                OPEN ContentList FOR
                    'SELECT dwo.Title,
                            dwo.AssetType,
                            dwo.AssetID,
                            dwo.PackageAssetID,
                            to_char(d.CalenderDate,''YYYY-MM'') AS MonthName,
                            dwo.ProcessInstanceID,
                            dwos.Name as OperStatusName
                     FROM DIM_WORKORDER dwo
                       JOIN FACT_WORKORDERDETAIL fwo
                         ON dwo.WorkOrderWK = fwo.WorkOrderWK
                       JOIN DIM_DATE d
                         ON fwo.LocalStartDateWK = d.DateWK
                       JOIN DIM_PROVIDER p
                         ON fwo.ProviderWK = p.VariableInstanceWK
		       JOIN DIM_WORKORDERSTATUS dwos
                         ON fwo.OPERSTATUSWK = dwos.WORKORDERSTATUSWK
                     WHERE d.CalenderDate BETWEEN to_date('''||to_char(startdate,'YYYY-MM-DD')||''',''YYYY-MM-DD'') AND 
                                                  to_date('''||to_char(enddate,'YYYY-MM-DD')||''',''YYYY-MM-DD'') AND
                           p.ProviderName = ''' || Provider || ''' AND
                           dwo.ASSETTYPE IN (' || assetTypeList || ')
                     ORDER BY  MonthName DESC, dwo.ProcessInstanceID';   

            ELSE

                OPEN ContentList FOR
                      'SELECT dwo.Title,
                            dwo.AssetType,
                            dwo.AssetID,
                            dwo.PackageAssetID,
                            to_char(d.CalenderDate,''YYYY-MM'') AS MonthName,
                            dwo.ProcessInstanceID,
                            dwos.Name as OperStatusName
                     FROM DIM_WORKORDER dwo
                       JOIN FACT_WORKORDERDETAIL fwo
                         ON dwo.WorkOrderWK = fwo.WorkOrderWK
                       JOIN DIM_DATE d
                         ON fwo.LocalStartDateWK = d.DateWK
                       JOIN DIM_PROVIDER p
                         ON fwo.ProviderWK = p.VariableInstanceWK
		       JOIN DIM_WORKORDERSTATUS dwos
                         ON fwo.OPERSTATUSWK = dwos.WORKORDERSTATUSWK
                     WHERE 1 = 2
                     ORDER BY  MonthName DESC, dwo.ProcessInstanceID';

            END IF;

      EXCEPTION
            WHEN INVALID_CURSOR THEN
				DBMS_OUTPUT.PUT_LINE('Error');
                raise_application_error(-10080, 'spContentListByProvdrAssetType: Invalid cursor');

            WHEN NO_DATA_FOUND THEN
				DBMS_OUTPUT.PUT_LINE('No data found');
                errorcode := SQLCODE;
                errormessage := 'No data found for the period from '||FromDate||' to '||ToDate||' for asset types '||AssetTypes||' and provider '||Provider;
                wfsdw.PKG_METADATA.spMD_WriteEventLog('spContentListByProvdrAssetType', errorcode, section => errormessage);

            WHEN OTHERS THEN
				DBMS_OUTPUT.PUT_LINE('No data found');
                errorcode := SQLCODE;
                errormessage := 'spContentListByProvdrAssetType: No data found';
                wfsdw.PKG_METADATA.spMD_WriteEventLog('spContentListByProvdrAssetType', errorcode, section => errormessage);

      END spContntListByProvdrAssetTyp;

/*
	******************************************************************************
	PROCEDURE: spWorkOrdersPerPackageAssetID
	[REPORTS]
	******************************************************************************
	This StoredProcedure retrieves a list of workOrders for a given packageAssetID
	[NOTE]		As per the Development team request at this point I'm not raising 
                application errors . Just uncommenting the raise_application_error 
                calls.
	******************************************************************************
	[USED BY]	Should be used to generate the WFS Report.
	******************************************************************************
	Developed by Jakobac, Vladimir 10/16/2006
	[ReWritten by Chris Scherlacher 4/26/2007]
	******************************************************************************
	Change Log: 
	******************************************************************************
*/
    PROCEDURE spWOsPerPackageAssetID(   fromDate        IN varchar2, 
                                        toDate          IN varchar2, 
                                        packageAssetID  IN varchar2, 
                                        WorkOrders      OUT CURSORTYPE)
	IS

        paramsValid   boolean:=true;
        diff_days     int;
        vSQLCode      int;
        vErrMessage   varchar2(500);
        procName      varchar2(50);

	BEGIN

       procName := 'spWOsPerPackageAssetID';

        -- check for input validation
       IF fromDate IS NULL OR toDate IS NULL THEN

            --raise_application_error(-20101,'Dates should not be null');
            paramsValid := false;

        ELSE

            SELECT (to_date(toDate,'rrrr/mm/dd') - to_date(fromDate,'rrrr/mm/dd')) INTO diff_days from dual;
            
            IF diff_days<0 THEN

                --raise_application_error(-20102,'To Date is less than the From date');
                paramsValid := false;

            END IF;  

        END IF;    

        IF packageAssetID is NULL THEN

            paramsValid := false;

        END IF;

        IF paramsValid THEN

            OPEN WorkOrders FOR
                'SELECT dwo.ProcessInstanceID,
                        t.Name AS TemplateName,
                        p.ProviderName as ProviderName,
                        dwo.AssetID as AssetID,
                        dwo.Title as Title,
                        dwo.HouseMaterialID as HouseMaterialID,
                        dwo.LicenseEndDate as LicenseEnd,
                        dwos.Name as OperStatus,
                        dwo.AssetType as AssetType
                 FROM DIM_WORKORDER dwo
                   JOIN FACT_WORKORDERDETAIL fwo
                     ON dwo.WorkOrderWK = fwo.WorkOrderWK
                   JOIN DIM_TEMPLATE t
                     ON fwo.TemplateWK = t.TemplateWK
                   JOIN DIM_PROVIDER p
                     ON fwo.ProviderWK = p.VariableInstanceWK
                   JOIN DIM_DATE d
                     ON fwo.LocalStartDateWK = d.DateWK
                   JOIN DIM_WORKORDERSTATUS dwos
                     ON dwos.WORKORDERSTATUSWK = fwo.OPERSTATUSWK
                 WHERE d.CalenderDate BETWEEN to_date('''||FromDate||''', ''RRRR-MM-DD HH24:MI:SS'') AND
                                              to_date('''||ToDate||''', ''RRRR-MM-DD HH24:MI:SS'') AND
                       dwo.PackageAssetID = '''||packageAssetID||'''';

        ELSE

            dbms_output.put_line('no cursor');

            OPEN WorkOrders FOR
                'SELECT dwo.ProcessInstanceID,
                        t.Name AS TemplateName,
                        p.ProviderName,
                        dwo.AssetID,
                        dwo.Title,
                        dwo.HouseMaterialID,
                        dwo.LicenseEndDate,
                        dwos.Name,
                        dwo.AssetType
                 FROM DIM_WORKORDER dwo
                   JOIN FACT_WORKORDERDETAIL fwo
                     ON dwo.WorkOrderWK = fwo.WorkOrderWK
                   JOIN DIM_TEMPLATE t
                     ON fwo.TemplateWK = t.TemplateWK
                   JOIN DIM_PROVIDER p
                     ON fwo.ProviderWK = p.VariableInstanceWK
                   JOIN DIM_DATE d
                     ON fwo.LocalStartDateWK = d.DateWK
		   JOIN DIM_WORKORDERSTATUS dwos
		     ON fwo.OPERSTATUSWK = dwos.WorkOrderStatusWK
                 WHERE 1 = 2';

         END IF;
    
    EXCEPTION
        WHEN INVALID_CURSOR THEN
            raise_application_error(-20890,'No WorkOrders Data created');
        WHEN NO_DATA_FOUND THEN
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spWOsPerPackageAssetID', vSQLCode, section => 'Query - No data found for the period from '||fromdate||' to '||todate||' for the package asset ID '||packageAssetID);
        WHEN OTHERS THEN
            vSQLCode := SQLCODE;
            dbms_output.put_line('Oracle Error '||SQLCODE|| '  DB error.  Can''t provide data from the spWOsPerPackageAssetID..!');
            vErrMessage := procName || ': DB error.  Can''t provide data from the spRPTWosByTemplateTimeProvider..!';
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spWOsPerPackageAssetID', vSQLCode, section => vErrMessage);     

	END spWOsPerPackageAssetID;

/*
	*****************************************************************************
	PROCEDURE: spWOsLicenseWindowMiss
	[REPORTS]
	*****************************************************************************
	Retrieves a list of work orders that missed the license window. Basically
	the asset must get processed before the license window starts. Only
	movie, trailer and poster assets are returned - remember that home pages
	and row ads do not have license restrictions.
    *****************************************************************************
    	[SQL Server version by Sahil Verma 11/10/2006]
	[ReWritten by Chris Scherlacher 4/26/2007]
	*****************************************************************************
	Change Log: 
	*****************************************************************************
*/
	PROCEDURE spWOsLicenseWindowMiss(fromDate IN varchar2, 
                                     toDate IN varchar2, 
                                     WorkOrders OUT CURSORTYPE) IS
		startDate date;
		endDate date;
        errorcode int;
        errormessage varchar2(500);
        paramsValid   boolean:=true;
        diff_days     int;

	BEGIN
		
        IF fromDate IS NULL OR toDate IS NULL THEN

            --raise_application_error(-20101,'Dates should not be null');
            paramsValid := false;

        ELSE

            SELECT (to_date(toDate,'rrrr/mm/dd') - to_date(fromDate,'rrrr/mm/dd')) INTO diff_days from dual;

            IF diff_days<0 THEN

                --raise_application_error(-20102,'To Date is less than the From date');
                paramsValid := false;

            ELSE

                SELECT (to_date(fromDate,'RRRR-MM-DD HH24:MI:SS')) INTO startDate FROM dual;
                SELECT (to_date(toDate,  'RRRR-MM-DD HH24:MI:SS')) INTO endDate   FROM dual;

            END IF;  

        END IF;  
 
        IF paramsValid THEN

            OPEN WorkOrders FOR
               SELECT dwo.ProcessInstanceID,
                        t.Name AS TemplateName,
                        p.ProviderName,
                        dwo.Title,
                        dwo.HouseMaterialID,
                        dwo.LocalStartDate,
                        dwo.LocalEndDate,
                        dwo.LicenseStartDate,
                        dwo.LicenseEndDate,
                        dwos.Name as OperStatusName
                 FROM DIM_WORKORDER dwo
                   JOIN FACT_WORKORDERDETAIL fwo
                     ON dwo.WorkOrderWK = fwo.WorkOrderWK
                   JOIN DIM_TEMPLATE t
                     ON fwo.TemplateWK = t.TemplateWK
                   JOIN DIM_PROVIDER p
                     ON fwo.ProviderWK = p.VariableInstanceWK
		   JOIN DIM_WORKORDERSTATUS dwos
		     ON fwo.OPERSTATUSWK = dwos.WorkOrderStatusWK
                 WHERE dwo.LicenseStartDate IS NOT NULL AND
                       dwo.LicenseStartDate BETWEEN startDate AND
                                                    endDate AND
                       (dwo.LocalEndDate > dwo.LicenseStartDate OR LocalEndDate IS NULL)
                 ORDER BY LicenseStartDate, ProcessInstanceID;

        ELSE

            DBMS_OUTPUT.PUT_LINE('NO CURSOR DATA');

            OPEN WorkOrders FOR
                 SELECT dwo.ProcessInstanceID,
                        t.Name AS TemplateName,
                        p.ProviderName,
                        dwo.Title,
                        dwo.HouseMaterialID,
                        LocalStartDate,
                        LocalEndDate,
                        dwo.LicenseStartDate,
                        dwo.LicenseEndDate,
                        dwos.Name as OperStatusName
                 FROM DIM_WORKORDER dwo
                   JOIN FACT_WORKORDERDETAIL fwo
                     ON dwo.WorkOrderWK = fwo.WorkOrderWK
                   JOIN DIM_TEMPLATE t
                     ON fwo.TemplateWK = t.TemplateWK
                   JOIN DIM_PROVIDER p
                     ON fwo.ProviderWK = p.VariableInstanceWK
		   JOIN DIM_WORKORDERSTATUS dwos
		     ON fwo.OPERSTATUSWK = dwos.WORKORDERSTATUSWK
                WHERE 1 = 2;

        END IF;

    EXCEPTION
            WHEN INVALID_CURSOR THEN
                raise_application_error(-10080, 'spWorkOrdersLicenseWindowMiss: Invalid cursor');
            WHEN NO_DATA_FOUND THEN
                errorcode := SQLCODE;
                errormessage := 'No data found for the period from '||FromDate||' to '||ToDate||'';
                wfsdw.PKG_METADATA.spMD_WriteEventLog('spContentListByProvdrAssetType', errorcode, section => errormessage);
            WHEN OTHERS THEN
				errorcode := SQLCODE;
                errormessage := 'spWorkOrdersLicenseWindowMiss: No data found';
                wfsdw.PKG_METADATA.spMD_WriteEventLog('spWorkOrdersLicenseWindowMiss', errorcode, section => errormessage);

    END spWOsLicenseWindowMiss;

/*
	*****************************************************************************
	PROCEDURE: spRPTWOsPurge
	[REPORTS]
	*****************************************************************************
	This procedure returns the list of purge work orders.
	[RETURNS] 	RefCursor which contains the purge work orders.
	*****************************************************************************
	[USED BY]	Should be used to generate the List of Purge Work ORders Report.
    *****************************************************************************
    	[SQL Server version by Imran Naqvi 11/10/2006]
	[ReWritten by Chris Scherlacher 4/26/2007]
	*****************************************************************************
	Change Log: 
	*****************************************************************************
*/

    PROCEDURE spRPTWOsPurge( from_      IN  varchar2, 
                             to_        IN  varchar2,
                             provider   IN  varchar2,
                             wosData    OUT CURSORTYPE) IS
        vSQLCode      int;
        vErrMessage   varchar2(500);
        procName      varchar2(50);    
        diff_days     int;
        paramsValid   boolean:=true;
        vprovider     varchar2(4000);
        stmtStr       varchar2(32767); 
        qryString     varchar2(32767):='';
        
        noOfProviders number(3);
        vPLSQLTabStr  wfsdw.PKG_REPORTS.PLSQLStrTabType;

      BEGIN

        procName := 'spRPTWOSPURGE';

             /******** Validation of Parameters ********/
        IF from_ IS NULL OR to_ IS NULL THEN

            --raise_application_error(-20101,'Dates should not be null');
            paramsValid := false;

        ELSE

            SELECT (to_date(to_,'rrrr-mm-dd') - to_date(from_,'rrrr-mm-dd')) INTO diff_days from dual;

            IF diff_days<0 THEN

                --raise_application_error(-20102,'To Date is less than the From date');
                paramsValid := false;

            END IF;  

        END IF;    
   
      IF provider IS NULL THEN

            paramsValid := false;
            --raise_application_error(-20001,'WorkOrders not selected, please select at least one Work Order from the list');

        ELSE

            wfsdw.pkg_reports.spStr_To_Strs(provider, vPLSQLTabStr);
            noOfProviders:=vPLSQLTabStr.count;

            FOR i IN 1..noOfProviders
            LOOP

                vprovider:= ''''||vPLSQLTabStr(i)||'''';

    
                IF stmtStr IS NULL THEN

                    stmtStr := vprovider;

                ELSE

                    stmtStr := stmtStr||', '||vprovider;

                END IF;

            END LOOP;
            
        END IF;

        /******* Selecting the data into RefCursor *******/
        IF paramsValid THEN            

            OPEN wosData FOR 
                'SELECT p.ProviderName,
                       dwo.ProcessInstanceID,
                       t.Name AS TemplateName,
                       dwo.PackageAssetID,
                       dwo.AssetID,
                       dwo.Title,
                       to_char(dwo.LicenseEndDate,''RRRR-MM-DD HH:MI:SS AM'') AS LicenseEndDate,
                       dwo.HouseMaterialID,
                       dwo.BroadcastMaterialID,
                       dwos.Name
                 FROM DIM_WORKORDER dwo
                   JOIN FACT_WORKORDERDETAIL fwo
                     ON dwo.WorkOrderWK = fwo.WorkOrderWK
                   JOIN DIM_PROVIDER p
                     ON fwo.ProviderWK = p.VariableInstanceWK
                   JOIN DIM_TEMPLATE t
                     ON fwo.TemplateWK = t.TemplateWK
		   JOIN DIM_WORKORDERSTATUS dwos
		     ON fwo.OperStatusWK = dwos.WorkOrderStatusWK
                 WHERE lower(t.Name) LIKE ''%purge%'' AND
                       p.ProviderName in ('||stmtStr||') AND
                       dwo.LocalStartDate BETWEEN to_date('''||from_||''',''RRRR-MM-DD'') AND 
                                                  to_date('''||to_||''',''RRRR-MM-DD'')+1
                ORDER BY 1,4';  

		--dbms_output.put_line('t.Name LIKE ''%purge%'' AND');
		--dbms_output.put_line('p.ProviderName in ('||stmtStr||') AND');
		--dbms_output.put_line('BETWEEN to_date('''||from_||''',''RRRR-MM-DD'')');

        ELSE

            /************ Creating an empty cursor to return ************/
            OPEN wosData FOR 
                SELECT p.ProviderName,
                       dwo.ProcessInstanceID,
                       t.Name AS TemplateName,
                       dwo.PackageAssetID,
                       dwo.AssetID,
                       dwo.Title,
                       to_char(dwo.LicenseEndDate,'RRRR-MM-DD HH:MI:SS AM') AS LicenseEndDate,
                       dwo.HouseMaterialID,
                       dwo.BroadcastMaterialID,
                       dwos.Name as OperStatusName
                 FROM DIM_WORKORDER dwo
                   JOIN FACT_WORKORDERDETAIL fwo
                     ON dwo.WorkOrderWK = fwo.WorkOrderWK
                   JOIN DIM_PROVIDER p
                     ON fwo.ProviderWK = p.VariableInstanceWK
                   JOIN DIM_TEMPLATE t
                     ON fwo.TemplateWK = t.TemplateWK
		   JOIN DIM_WORKORDERSTATUS dwos
	   	     ON fwo.OPERSTATUSWK = dwos.WorkOrderStatusWK
                 WHERE 1 = 2
                 ORDER BY 1,4;

        END IF; 

      EXCEPTION
        WHEN INVALID_CURSOR THEN
            raise_application_error(-20890,'No Resource Groups Data created');
        WHEN NO_DATA_FOUND THEN
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spRPTWOsPurge', vSQLCode, section => 'Query - No data found for the period from '||from_||' to '||to_||' for the provider '||provider);
        WHEN OTHERS THEN
            vSQLCode := SQLCODE;
            dbms_output.put_line('Oracle Error '||SQLCODE|| '  DB error.  Can''t provide data from the spRPTWOsPurge..!');
            vErrMessage := procName || ': DB error.  Can''t provide data from the spRPTWOsPurge..!';
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spRPTWOsPurge', vSQLCode, section => vErrMessage);   

        END spRPTWOsPurge;


/*
	*****************************************************************************
	PROCEDURE: spRPTAvgQTimeByRG
	[REPORTS]
	*****************************************************************************
	This provides the Data required for the average time a resource was used by
    Resource Groups.
	[RETURNS] 	RefCursor which contain the resource groups, their 
    average time used for a particular month.
	*****************************************************************************
	[USED BY]	Should be used to generate the Average Queue Time by Resource
    Group.
    *****************************************************************************
    	[SQL Server version by Imran Naqvi 11/10/2006]
	[ReWritten by Chris Scherlacher 4/26/2007]
	*****************************************************************************
	Change Log: 
	*****************************************************************************
*/

    PROCEDURE spRPTAvgQTimeByRG( from_          IN  varchar2, 
                                 to_            IN  varchar2, 
                                 resourceGroup  IN  varchar2, 
                                 wosData        OUT WosAvgQTimeByRGCurType) IS

        vSQLCode      		int;
        vErrMessage   		varchar2(500);
        procName      		varchar2(50);    
        diff_days     		int;
        paramsValid   		boolean:=true;
        vresourceGroup  	varchar2(4000);
        stmtStr       		varchar2(4000);
        firstResourceGroup 	boolean:=TRUE;
        qryAddedToStr 		boolean:=FALSE;
        qryString     		number(3):=1;
        vfromDate     		varchar2(20);
        vtoDate       		varchar2(20);
        noOfResourceGroups 	number(3);
        vPLSQLTabStr  		wfsdw.PKG_REPORTS.PLSQLStrTabType;

      BEGIN

        procName := 'spRPTAvgQTimeByRG';

        /******** Validation of Parameters ********/
        IF from_ IS NULL OR to_ IS NULL THEN

            --raise_application_error(-20101,'Dates should not be null');
            paramsValid := false;

        ELSE

            SELECT (to_date(to_,'rrrr/mm/dd') - to_date(from_,'rrrr/mm/dd')) INTO diff_days from dual;
            
            IF diff_days < 0 THEN

                --raise_application_error(-20102,'To Date is less than the From date');
                paramsValid := false;

            END IF;

        END IF;    
   
      IF resourceGroup IS NULL THEN

            paramsValid := false;
            --raise_application_error(-20001,'WorkOrders not selected, please select at least one Work Order from the list');

      ELSE

          wfsdw.pkg_reports.spStr_To_Strs(resourceGroup, vPLSQLTabStr);
          noOfResourceGroups:=vPLSQLTabStr.count;

          FOR i IN 1..noOfResourceGroups
          LOOP

              vresourceGroup:= ''''||vPLSQLTabStr(i)||'''';

	      IF i = 1 THEN

		-- Report displayed in average minutes
               	stmtStr:= 'SELECT rg.RESOURCEGROUPNAME, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0''), NVL((SUM(totalDuration)/60)/REPLACE(SUM(totalQueued),''0'',null),0) FROM FACT_RESOURCEQUEUEMONTH f JOIN DIM_RESOURCEGROUP rg ON f.RESOURCEGROUPWK = rg.RESOURCEGROUPWK JOIN DIM_MONTH m ON f.MONTHWK = m.MONTHWK WHERE m.MonthWK BETWEEN to_char(to_date('''||from_||''',''RRRR-MM-DD''),''YYYYMM'') AND to_char(to_date('''||to_||''',''RRRR-MM-DD''),''YYYYMM'') AND rg.RESOURCEGROUPNAME IN ('||vresourceGroup;

	      ELSE

		stmtStr:= stmtStr||','||vresourceGroup;

	      END IF;

	    END LOOP;

	    stmtStr:= stmtStr||') GROUP BY rg.RESOURCEGROUPNAME, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0'') ORDER BY 1,2';

            -- dbms_output.put_line(stmtStr);
	
	END IF;
	
        /******* Selecting the data into RefCursor *******/
        IF paramsValid THEN

            OPEN wosData FOR stmtStr;

        ELSE

            /************ Creating an empty cursor to return ************/
            OPEN wosData FOR 'SELECT rg.RESOURCEGROUPNAME, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0''), f.TotalDURATION 
			      FROM FACT_RESOURCEQUEUEMONTH f 
  				JOIN DIM_RESOURCEGROUP rg 
    				  ON f.RESOURCEGROUPWK = rg.RESOURCEGROUPWK 
  				JOIN DIM_MONTH m 
    				  ON f.MONTHWK = m.MONTHWK 
			      WHERE 1 = 2';
 
        END IF; 

       EXCEPTION
        WHEN INVALID_CURSOR THEN
            raise_application_error(-20890,'No Resource Groups Data created');
        WHEN NO_DATA_FOUND THEN
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spRPTAvgQTimeByRG', vSQLCode, section => 'Query - No data found for the period from '||from_||' to '||to_||' for the resourceGroup '||resourceGroup);
        WHEN OTHERS THEN
            vSQLCode := SQLCODE;
            dbms_output.put_line('Oracle Error '||SQLCODE|| '  DB error.  Can''t provide data from the spRPTAvgQTimeByRG..!');
            vErrMessage := procName || ': DB error.  Can''t provide data from the spRPTAvgQTimeByRG..!';
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spRPTAvgQTimeByRG', vSQLCode, section => vErrMessage);   

     END spRPTAvgQTimeByRG;

/*
	*****************************************************************************
	PROCEDURE: spRPTAvgTaskDurByRG
	[REPORTS]
	*****************************************************************************
	This provides the Data required for the average task duration for a resource 
    by Resource Groups.
	[RETURNS] 	RefCursor which contain the resource groups, their 
    average time used for a particular month.
	*****************************************************************************
	[USED BY]	Should be used to generate the Average Queue Time by Resource
    Group.
    *****************************************************************************
    [SQL Server version by Imran Naqvi 11/10/2006]
    [ReWritten by Chris Scherlacher 6/4/2007]
	*****************************************************************************
	Change Log: 
	*****************************************************************************
*/

    PROCEDURE spRPTAvgTaskDurByRG( from_            IN  varchar2, 
                                   to_              IN  varchar2, 
                                   resourceGroup    IN  varchar2, 
                                   wosData          OUT WosAvgTaskDurByRGCurType) IS

        vSQLCode            int;
        vErrMessage         varchar2(500);
        procName            varchar2(50);    
        diff_days           int;
        paramsValid         boolean:=true;
        vresourceGroup      varchar2(4000);
        stmtStr             varchar2(4000);
        firstResourceGroup  boolean:=TRUE;
        qryAddedToStr       boolean:=FALSE;
        qryString           number(3):=1;
        vfromDate           varchar2(20);
        vtoDate             varchar2(20);
        noOfResourceGroups  number(3);
        vPLSQLTabStr        wfsdw.PKG_REPORTS.PLSQLStrTabType;

      BEGIN

        procName := 'spRPTAvgTaskDurByRG';

             /******** Validation of Parameters ********/
        IF from_ IS NULL OR to_ IS NULL THEN

            --raise_application_error(-20101,'Dates should not be null');
            paramsValid := false;

        ELSE

            SELECT (to_date(to_,'rrrr/mm/dd') - to_date(from_,'rrrr/mm/dd')) INTO diff_days from dual;

            IF diff_days<0 THEN

                --raise_application_error(-20102,'To Date is less than the From date');
                paramsValid := false;

            END IF;

        END IF;    
   
      IF resourceGroup IS NULL THEN

            paramsValid := false;
            --raise_application_error(-20001,'WorkOrders not selected, please select at least one Work Order from the list');

        ELSE

            wfsdw.pkg_reports.spStr_To_Strs(resourceGroup, vPLSQLTabStr);
            noOfResourceGroups:=vPLSQLTabStr.count;

            FOR i IN 1..noOfResourceGroups
            LOOP

                vresourceGroup:= ''''||trim(vPLSQLTabStr(i))||'''';

	      IF i = 1 THEN

		-- Report is desplaying data in average minutes

                stmtStr:= 'SELECT rg.ResourceGroupName, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0''), ROUND(NVL((SUM(f.totalDuration)/60)/REPLACE(SUM(f.totalTasks),''0'',null),0),2) FROM FACT_RESOURCETASKMONTH f JOIN DIM_RESOURCEGROUP rg ON f.RESOURCEGROUPWK = rg.RESOURCEGROUPWK JOIN DIM_MONTH m ON f.MONTHWK = m.MONTHWK WHERE m.MonthWK BETWEEN to_char(to_date('''||from_||''',''RRRR-MM-DD''),''YYYYMM'') AND to_char(to_date('''||to_||''',''RRRR-MM-DD''),''YYYYMM'') AND rg.RESOURCEGROUPNAME IN ('||vresourceGroup;

	      ELSE

	   	stmtStr:= stmtStr||','||vresourceGroup;

	      END IF;

	    END LOOP;

	    stmtStr:= stmtStr||') GROUP BY rg.RESOURCEGROUPNAME, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0'') ORDER BY 1,2';
              
        END IF;

        /******* Selecting the data into RefCursor *******/
        IF paramsValid THEN

            OPEN wosData FOR stmtStr;

        ELSE

            /************ Creating an empty cursor to return ************/
            OPEN wosData FOR 'SELECT rg.RESOURCEGROUPNAME, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0''), ROUND(f.TotalDURATION,2)
			      FROM FACT_RESOURCETASKMONTH f
  				JOIN DIM_RESOURCEGROUP rg
    				  ON f.RESOURCEGROUPWK = rg.RESOURCEGROUPWK
  				JOIN DIM_MONTH m
    				  ON f.MONTHWK = m.MONTHWK
			      WHERE 1 = 2'; 

        END IF; 

       EXCEPTION
        WHEN INVALID_CURSOR THEN
            raise_application_error(-20890,'No Resource Groups Data created');

        WHEN NO_DATA_FOUND THEN
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spRPTAvgTaskDurByRG', vSQLCode, section => 'Query - No data found for the period from '||from_||' to '||to_||' for the resourceGroup '||resourceGroup);

        WHEN OTHERS THEN
            vSQLCode := SQLCODE;
            dbms_output.put_line('Oracle Error '||SQLCODE|| '  DB error.  Can''t provide data from the spRPTAvgTaskDurByRG..!');
            vErrMessage := procName || ': DB error.  Can''t provide data from the spRPTAvgTaskDurByRG..!';
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spRPTAvgQTimeByRG', vSQLCode, section => vErrMessage);   

     END spRPTAvgTaskDurByRG;


/*
	*****************************************************************************
	PROCEDURE: spTaskDetailsByResource
	[REPORTS]
	*****************************************************************************
	Retrieves task details for selected resources
        *****************************************************************************
        [SQL Server version by Kinjal Meheta 11/10/2006]
	[Procedure Rewritten by Chris Scherlacher 6/8/2007]
	*****************************************************************************
	Change Log: 
	*****************************************************************************
*/

PROCEDURE spTaskDetailsByResource(
                        startdate IN varchar2,
                        enddate IN varchar2,
                        resources IN varchar2,
                        wosData OUT WosCurType_spTaskDetails)
IS

    noOfResources number(3);
    vPLSQLTabStr  wfsdw.PKG_REPORTS.PLSQLStrTabType;
    resourceList varchar2(4000):='';
    stmt         varchar2(4000);
    paramsValid   boolean:=true;
    diff_days     int;
    vSQLCode      int;
    vErrMessage   varchar2(500);
    procName      varchar2(50);

    BEGIN

        procName := 'spTaskDetailsByResource';

        -- check for input validation
       IF startdate IS NULL OR enddate IS NULL THEN

            --raise_application_error(-20101,'Dates should not be null');
            paramsValid := false;

       ELSE

            SELECT (to_date(enddate,'rrrr/mm/dd') - to_date(startdate,'rrrr/mm/dd')) INTO diff_days from dual;
            
            IF diff_days<0 THEN

                --raise_application_error(-20102,'To Date is less than the From date');
                paramsValid := false;

            END IF;

        END IF;        
        
        -- check for resources validation
        IF resources is NULL THEN

            paramsValid := false;

        END IF;

        IF paramsValid THEN

            wfsdw.pkg_reports.spStr_To_Strs(resources, vPLSQLTabStr);
            noOfResources:=vPLSQLTabStr.count;

            FOR i IN 1..noOfResources
            LOOP

                resourceList := '''' || trim(replace(vPLSQLTabStr(i),'''','''''')) || '''';

		IF i = 1 THEN

			stmt := 'SELECT dr.ResourceName, dwo.ProcessInstanceID, dt.Name as TemplateName ,dn.Name as taskName, dwo.TITLE, to_char(dsd.CALENDERDATE,''RRRR-MM-DD'')||'' ''||to_char(dst.TIMEOFDAY,''HH:MI:SS AM'') as starttime, to_char(dsd.CALENDERDATE,''RRRR-MM-DD'')||'' ''||to_char(dst.TIMEOFDAY,''HH:MI:SS AM'') as endtime, Duration/60 as Duration, dwos.Name as TaskStatus FROM FACT_RESOURCETASKDETAIL f JOIN DIM_RESOURCE dr ON f.RESOURCEWK = dr.RESOURCEWK JOIN DIM_WORKORDER dwo ON f.WORKORDERWK = dwo.WORKORDERWK JOIN DIM_TEMPLATE dt ON f.TEMPLATEWK = dt.TEMPLATEWK JOIN DIM_NODE dn ON f.NODEWK = dn.NODEWK JOIN DIM_DATE dsd ON f.LOCALENTERDATEWK = dsd.DATEWK JOIN DIM_TIME dst ON f.LOCALENTERTIMEWK = dst.TIMEWK JOIN DIM_DATE dld ON f.LOCALLEAVEDATEWK = dld.DATEWK JOIN DIM_TIME dlt ON f.LOCALLEAVETIMEWK = dlt.TIMEWK JOIN DIM_WORKORDERSTATUS dwos ON f.OperStatusWK = dwos.WORKORDERSTATUSWK WHERE dsd.CALENDERDATE >= to_date('''||startdate||''',''RRRR-MM-DD'') AND dld.CALENDERDATE <= to_date('''||enddate||''',''RRRR-MM-DD'')+1 AND dr.ResourceName IN ('||resourceList;

		ELSE

			stmt:= stmt||','||resourceList;

		END IF;

            END LOOP;

	    stmt := stmt||') ORDER BY dr.ResourceName, to_char(dsd.CALENDERDATE,''RRRR-MM-DD'')||'' ''||to_char(dst.TIMEOFDAY,''HH:MI:SS AM'')';

            OPEN wosData FOR stmt;

        ELSE

            OPEN wosData FOR SELECT dr.ResourceName,
			            dwo.ProcessInstanceID,
				    dt.Name as TemplateName,
				    dn.Name as TaskName,
				    dwo.Title,
				    to_char(dsd.CALENDERDATE,'RRRR-MM-DD')||' '||to_char(dst.TIMEOFDAY,'HH:MI:SS AM') as starttime,
				    to_char(dsd.CALENDERDATE,'RRRR-MM-DD')||' '||to_char(dst.TIMEOFDAY,'HH:MI:SS AM') as endtime,
				    Duration/60 as Duration,
				    dwos.Name as TaskStatus
			     FROM FACT_RESOURCETASKDETAIL f 
				JOIN DIM_RESOURCE dr ON f.RESOURCEWK = dr.RESOURCEWK 
				JOIN DIM_WORKORDER dwo ON f.WORKORDERWK = dwo.WORKORDERWK 
				JOIN DIM_TEMPLATE dt ON f.TEMPLATEWK = dt.TEMPLATEWK 
				JOIN DIM_NODE dn ON f.NODEWK = dn.NODEWK 
				JOIN DIM_DATE dsd ON f.LOCALENTERDATEWK = dsd.DATEWK 
				JOIN DIM_TIME dst ON f.LOCALENTERTIMEWK = dst.TIMEWK 
				JOIN DIM_DATE dld ON f.LOCALLEAVEDATEWK = dld.DATEWK 
				JOIN DIM_TIME dlt ON f.LOCALLEAVETIMEWK = dlt.TIMEWK 
				JOIN DIM_WORKORDERSTATUS dwos ON f.OperStatusWK = dwos.WORKORDERSTATUSWK 
			     WHERE 1 = 2;

         END IF;

    EXCEPTION
        WHEN INVALID_CURSOR THEN
            raise_application_error(-20890,'No WorkOrders Data created');

        WHEN NO_DATA_FOUND THEN
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spTaskDetailsByResource', vSQLCode, section => 'Query - No data found for the period from '||startdate||' to '||enddate||' for the resources '||resources);

        WHEN OTHERS THEN
            vSQLCode := SQLCODE;
            dbms_output.put_line('Oracle Error '||SQLCODE|| '  DB error.  Can''t provide data from the spTaskDetailByResource..!');
            vErrMessage := procName || ': DB error.  Can''t provide data from the spTaskDetailByResource..!';
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spTaskDetailsByResource', vSQLCode, section => vErrMessage);

    END spTaskDetailsByResource;

/*
	*****************************************************************************
	PROCEDURE: spFailureTrendByResGroup
	[REPORTS]
	*****************************************************************************
	This provides the Failure percentage Data required for the Resource Groups.
	[RETURNS] 	RGData RefCursor contains the Month wise Resource Group Failure 
                percentage.
                
	[NOTE]		As per the Development team request at this point I'm not raising 
                application errors . Just uncommenting the raise_application_error 
                calls.
	*****************************************************************************
	[USED BY]	Should be used to generate the WFS Report Month wise Resource 
                Group Failure percentage
	*****************************************************************************
	[SQL Server version by Suresh Neravati 11/14/2006]
	[Oracle version by Suresh - revised by Suresh on 11/14/2006]
	[Re-Written by Chris Scherlacher 6/14/07]
	*****************************************************************************
	Change Log: 
	*****************************************************************************
*/
   PROCEDURE spFailureTrendByResGroup (   FromDate        IN  varchar2, 
                                           ToDate          IN  varchar2, 
                                           ResourceGroup   IN  varchar2,
                                           RGData         OUT WosCurType) IS

        vSQLCode      		int;
        vErrMessage   		varchar2(500);
        procName      		varchar2(50);    
        diff_days     		int;
        paramsValid   		boolean:=true;
	vresourceGroup		varchar2(1000);
        vProvider     		varchar2(100);

        stmtStr       		varchar2(4000);
        firstRG       		boolean:=TRUE;
        qryAddedToStr 		boolean:=FALSE;
        stmtStr11     		varchar2(32767):='';
        stmtStr12     		varchar2(32767):='';
        stmtStr13     		varchar2(32767):='';
        qryString     		number(3):=1;

        strsUsed      		number(3);
        vfromDate     		varchar2(20);
        vtoDate       		varchar2(20);
        noOfResGroups 		number(3);
        vPLSQLTabStr  		wfsdw.PKG_REPORTS.PLSQLStrTabType;

      BEGIN

        procName := 'spFailureTrendByResGroup';

               /******** Validation of Parameters ********/
        IF FromDate IS NULL OR ToDate IS NULL THEN

            --raise_application_error(-20101,'Dates should not be null');
            paramsValid := false;

        ELSE

            SELECT (to_date(ToDate,'rrrr/mm/dd') - to_date(FromDate,'rrrr/mm/dd')) INTO diff_days from dual;

            --DBMS_OUTPUT.PUT_LINE(diff_days);

            IF diff_days<0 THEN

                --raise_application_error(-20102,'To Date is less than the From date');
                paramsValid := false;

            END IF;

	    IF ResourceGroup IS NULL THEN

		--raise_application_error(-20105,'The ResourceGroup is null');
                paramsValid := false;

	    END IF;

        END IF; 
        
        IF paramsValid THEN

            WFSDW.PKG_REPORTS.spStr_To_Strs(ResourceGroup,vPLSQLTabStr);
            noOfResGroups:= vPLSQLTabStr.count;
            
            FOR i IN 1..noOfResGroups
            LOOP

               	  vresourceGroup := '''' || trim(vPLSQLTabStr(i)) || '''';

                  IF i = 1 THEN

		      stmtStr:= 'SELECT rg.ResourceGroupName, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0''), ROUND(NVL(SUM(f.totalErrors)/REPLACE(SUM(f.totalTasks),''0'',null),0)*100,2) FROM FACT_RESOURCETASKMONTH f JOIN DIM_RESOURCEGROUP rg ON f.RESOURCEGROUPWK = rg.RESOURCEGROUPWK JOIN DIM_MONTH m ON f.MONTHWK = m.MONTHWK WHERE m.MonthWK BETWEEN to_char(to_date('''||fromDate||''',''RRRR-MM-DD''),''YYYYMM'') AND to_char(to_date('''||toDate||''',''RRRR-MM-DD''),''YYYYMM'') AND rg.RESOURCEGROUPNAME IN ('||vresourceGroup;

                  ELSE

                    stmtStr:= stmtStr||','||vresourceGroup;

                  END IF;

            END LOOP;

            stmtStr:= stmtStr||') GROUP BY rg.RESOURCEGROUPNAME, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0'') ORDER BY 1,2';

        END IF;

        /******* Selecting the data into RefCursor *******/
        IF paramsValid THEN

            OPEN RGData FOR stmtStr;

        ELSE

            /************ Creating an empty cursor to return ************/
            OPEN RGData FOR 'SELECT rg.RESOURCEGROUPNAME, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0''), f.TotalDURATION
                              FROM FACT_RESOURCETASKMONTH f
                                JOIN DIM_RESOURCEGROUP rg
                                  ON f.RESOURCEGROUPWK = rg.RESOURCEGROUPWK
                                JOIN DIM_MONTH m
                                  ON f.MONTHWK = m.MONTHWK
                              WHERE 1 = 2';

        END IF;
 
      EXCEPTION
        WHEN INVALID_CURSOR THEN
            raise_application_error(-20890,'No WorkOrders Data created');
        WHEN NO_DATA_FOUND THEN
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spFailureTrendByResGroup', vSQLCode, section => 'Query - No data found for the period from '||FromDate||' to '||ToDate||' for the Resource Group '||ResourceGroup);
        WHEN OTHERS THEN
            vSQLCode := SQLCODE;
            dbms_output.put_line('Oracle Error '||SQLCODE|| '  DB error.  Can''t provide data from the spFailureTrendByResourceGroup..!');
            vErrMessage := procName || ': DB error.  Can''t provide data from the spFailureTrendByResGroup..!';
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spFailureTrendByResGroup', vSQLCode, section => vErrMessage);     

      END spFailureTrendByResGroup;

/*
	*****************************************************************************
	PROCEDURE: spFailureTrendByResource
	[REPORTS]
	*****************************************************************************
	This provides the Failure percentage Data required for the Resources.
	[RETURNS] 	RGData RefCursor contains the Month wise Resources Failure 
                percentage.
                
	[NOTE]		As per the Development team request at this point I'm not raising 
                application errors . Just uncommenting the raise_application_error 
                calls.
	*****************************************************************************
	[USED BY]	Should be used to generate the WFS Report Month wise Resources 
                Failure percentage.
	*****************************************************************************
	[SQL Server version by Suresh Neravati 11/15/2006]
	[Oracle version by Suresh - revised by Suresh on 11/15/2006]
	[ReWritten by Chris Scherlacher 6/18/2007]
	*****************************************************************************
	Change Log: 
	*****************************************************************************
*/
    PROCEDURE spFailureTrendByResource (   FromDate     IN  varchar2, 
                                           ToDate       IN  varchar2, 
                                           Resource_    IN  varchar2,
                                           ResourceData OUT WosCurType) IS

        vSQLCode      int;
        vErrMessage   varchar2(500);
        procName      varchar2(50);    
        diff_days     int;
        paramsValid   boolean:=true;
        vResource     varchar2(1000);
        vProvider     varchar2(100);

        stmtStr       varchar2(4000);

        firstRG       boolean:=TRUE;
        qryAddedToStr boolean:=FALSE;
        stmtStr11     varchar2(32767):='';
        stmtStr12     varchar2(32767):='';
        stmtStr13     varchar2(32767):='';        
        qryString     number(3):=1;

        strsUsed      number(3);
        vfromDate     varchar2(20);
        vtoDate       varchar2(20);
        noOfResources number(3);
        vPLSQLTabStr  wfsdw.PKG_REPORTS.PLSQLStrTabType;

      BEGIN

        procName := 'spFailureTrendByResource';

               /******** Validation of Parameters ********/
        IF FromDate IS NULL OR ToDate IS NULL THEN

            --raise_application_error(-20101,'Dates should not be null');
            paramsValid := false;

        ELSE

            SELECT (to_date(ToDate,'rrrr/mm/dd') - to_date(FromDate,'rrrr/mm/dd')) INTO diff_days from dual;

            --DBMS_OUTPUT.PUT_LINE(diff_days);

            IF diff_days<0 THEN

                --raise_application_error(-20102,'To Date is less than the From date');
                paramsValid := false;

            END IF;

	    IF Resource_ IS NULL THEN

		--raise_application_error(20105,'The Resource value is Null');
		paramsValid := false;

	    END IF;

        END IF; 


	IF paramsValid THEN

            WFSDW.PKG_REPORTS.spStr_To_Strs(Resource_,vPLSQLTabStr);

            noOfResources:= vPLSQLTabStr.count;

            FOR i IN 1..noOfResources
            LOOP

                  vResource := '''' || trim(replace(vPLSQLTabStr(i),'''','''''')) || '''';

                  IF i = 1 THEN

                      stmtStr:= 'SELECT dr.ResourceName, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0''), ROUND(NVL(SUM(f.totalErrors)/REPLACE(SUM(f.totalTasks),''0'',null),0)*100,2) FROM FACT_RESOURCETASKMONTH f JOIN DIM_RESOURCE dr ON f.RESOURCEWK = dr.RESOURCEWK JOIN DIM_MONTH m ON f.MONTHWK = m.MONTHWK WHERE m.MonthWK BETWEEN to_char(to_date('''||fromDate||''',''RRRR-MM-DD''),''YYYYMM'') AND to_char(to_date('''||toDate||''',''RRRR-MM-DD''),''YYYYMM'') AND dr.RESOURCENAME IN ('||vResource;

                  ELSE

                    stmtStr:= stmtStr||','||vResource;

                  END IF;

            END LOOP;

            stmtStr:= stmtStr||') GROUP BY dr.RESOURCENAME, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0'') ORDER BY 1,2';

        END IF;

        /******* Selecting the data into RefCursor *******/
        IF paramsValid THEN

            OPEN ResourceData FOR stmtStr;

        ELSE

            /************ Creating an empty cursor to return ************/
            OPEN ResourceData FOR 'SELECT dr.RESOURCENAME, m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0''), f.TotalDURATION
                              FROM FACT_RESOURCETASKMONTH f
                                JOIN DIM_RESOURCE dr
                                  ON f.RESOURCEWK = dr.RESOURCEWK
                                JOIN DIM_MONTH m
                                  ON f.MONTHWK = m.MONTHWK
                              WHERE 1 = 2';

        END IF;
        
      EXCEPTION
        WHEN INVALID_CURSOR THEN
            raise_application_error(-20890,'No WorkOrders Data created');
        WHEN NO_DATA_FOUND THEN
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spFailureTrendByResource', vSQLCode, section => 'Query - No data found for the period from '||fromDate||' to '||toDate||' for the Resources '||Resource_);
        WHEN OTHERS THEN
            vSQLCode := SQLCODE;
            dbms_output.put_line('Oracle Error '||SQLCODE|| '  DB error.  Can''t provide data from the spFailureTrendByResource..!');
            vErrMessage := procName || ': DB error.  Can''t provide data from the spFailureTrendByResource..!';
            wfsdw.PKG_METADATA.spMD_WriteEventLog ('spFailureTrendByResource', vSQLCode, section => vErrMessage);

      END spFailureTrendByResource;

-----
/*
	*****************************************************************************
	PROCEDURE: spRPTDriveUsage
	[REPORTS]
	*****************************************************************************
	This Stored Procedure provides the Data that shows the change of the SAN 
    Drive usage over time.
	[NOTE]		As per the Development team request at this point I'm not raising 
                application errors . Just uncommenting the raise_application_error 
                calls.
	*****************************************************************************
	[USED BY]	Should be used to generate the WFS Report.
	*****************************************************************************
	Developed by Silva Vijay 10/16/2006
	[ReWritten by Chris Scherlacher 7/11/2007]
	*****************************************************************************
	Change Log: 
	*****************************************************************************
*/
    PROCEDURE spRPTDriveUsage ( from_            IN  varchar2, 
                                to_              IN  varchar2, 
                                driveNames       IN  varchar2, 
                                wosData          OUT WosDriveUsageCurType) IS
        vSQLCode      int;
        vErrMessage   varchar2(500);
        procName      varchar2(50);
        diff_days     int;
        paramsValid   boolean:=true;

        queryString   varchar2(32767) := '';
        subQueryString varchar2(4000) := '';

        vdriveName    varchar2(4000);
        noOfDrives    number(3);
        vPLSQLTabStr  wfsdw.PKG_REPORTS.PLSQLStrTabType;

      BEGIN

        procName := 'spRPTDriveUsage';

        /******** Validation of Parameters ********/
        IF from_ IS NULL OR to_ IS NULL THEN

            -- raise_application_error(-20101, 'Dates should not be null');
            paramsValid := false;

        ELSE

            SELECT (to_date(to_, 'rrrr/mm/dd') - to_date(from_,'rrrr/mm/dd')) INTO diff_days from dual;

            IF diff_days < 0 THEN

                -- raise_application_error(-20102, 'To Date is less than the From date');
                paramsValid := false;

            END IF;

        END IF;

        IF driveNames IS NULL THEN

            paramsValid := false;
            -- raise_application_error(-20001, 'WorkOrders not selected, please select at least one Work Order from the list');

        ELSE

            /* Read the Names of the Drives on table: vPLSQLTabStr */
            wfsdw.pkg_reports.spStr_To_Strs(driveNames, vPLSQLTabStr);

            noOfDrives := vPLSQLTabStr.count;

            FOR i IN 1 .. noOfDrives
            LOOP

                vDriveName:= '''' || trim(vPLSQLTabStr(i)) || '''';

                IF i=1 THEN

		  queryString :='SELECT md.NAME_ as DriveName, 
                                        m.YEARNUM || ''-'' || LPAD(m.MONTHNUM,2,''0'') as MonthName, 
                                        f.MAXPERCENTUSED as MaxUsedPercentage
				 FROM FACT_MDHISTORYMONTH f
  				   JOIN DIM_MONITOREDDRIVE md
    				     ON f.MONITOREDDRIVEWK = md.MONITOREDDRIVEWK
  			 	   JOIN DIM_MONTH m
    				     ON f.MONTHWK = m.MONTHWK
				 WHERE m.MonthWK BETWEEN to_char(to_date('''||from_||''',''RRRR-MM-DD''),''YYYYMM'') AND to_char(to_date('''||to_||''',''RRRR-MM-DD''),''YYYYMM'') AND md.NAME_ IN ('||vDriveName;

		ELSE

		  queryString := queryString||','||vDriveName;

		END IF;

            END LOOP;

        END IF;

        /******* Selecting the data into RefCursor *******/
        IF paramsValid THEN

            queryString := queryString ||') ORDER BY 1, 2';

            OPEN wosData FOR queryString;

        ELSE

            /************ Creating an empty cursor to return ************/
            OPEN wosData FOR 'SELECT ''Drive Name'' AS "DRIVE_NAME", ''Month'' AS "MONTH", 0 AS "USED_PERCENTAGE"
                              FROM DUAL
                              WHERE 1 = 2';

        END IF;

      EXCEPTION
        WHEN INVALID_CURSOR THEN
            raise_application_error(-20890,'No SAN Drive Data created.');

        WHEN NO_DATA_FOUND THEN
            wfsdw.PKG_METADATA.spMD_WriteEventLog (procName, vSQLCode, section => 'Query - No data found for the period from '||from_||' to '||to_||' for the SAN Drive: '|| driveNames);

        WHEN OTHERS THEN
            vSQLCode := SQLCODE;
            dbms_output.put_line('Oracle Error: ' || vSQLCode || ' DB error.  Can''t provide data from procedure: ' || procName);
            vErrMessage := procName || ': DB error.  Can''t provide data.';
            wfsdw.PKG_METADATA.spMD_WriteEventLog (procName, vSQLCode, section => vErrMessage);

    END spRPTDriveUsage;

END;
/

GRANT EXECUTE ON WFSDW.PKG_REPORTS TO REPORTREADER;

CREATE OR REPLACE PROCEDURE "REPORTREADER"."SPCONTNTLISTBYPROVDRASSETTYPRR" (   FromDate    IN  varchar2,
                                                                            ToDate      IN  varchar2,
                                                                            Provider    IN  varchar2,
                                                                            AssetTypes  IN  varchar2,
                                                                            ContentList OUT WFSDW.PKG_REPORTS.CURSORTYPE) AS
BEGIN
  WFSDW.PKG_REPORTS.spContntListByProvdrAssetTyp( FromDate, ToDate, Provider, AssetTypes, ContentList);
END;
/


CREATE OR REPLACE PROCEDURE "REPORTREADER"."SPFAILURETRENDBYRESGROUPRR" (  FromDate        IN  varchar2,
                                            ToDate          IN  varchar2,
                                            ResourceGroup   IN  varchar2,
                                            wosData         OUT WFSDW.PKG_REPORTS.WosCurType) AS
BEGIN
  WFSDW.PKG_REPORTS.spFailureTrendByResGroup(  FromDate, ToDate, ResourceGroup, wosData);
END;
/
 

CREATE OR REPLACE PROCEDURE "REPORTREADER"."SPFAILURETRENDBYRESOURCERR" (  FromDate     IN  varchar2,
                                       ToDate       IN  varchar2,
                                       Resource_    IN  varchar2,
                                       ResourceData OUT WFSDW.PKG_REPORTS.WosCurType) AS
BEGIN
    WFSDW.PKG_REPORTS.SPFAILURETRENDBYRESOURCE(  FromDate, ToDate, Resource_ ,ResourceData);
END;
/


CREATE OR REPLACE PROCEDURE "REPORTREADER"."SPRPTAVGQTIMEBYRGRR" (  from_         IN  varchar2,
                                                               to_           IN  varchar2,
                                                               resourceGroup IN  varchar2,
                                                               wosData       OUT WFSDW.PKG_REPORTS.WosAvgQTimeByRGCurType) AS
BEGIN
        WFSDW.PKG_REPORTS.spRPTAvgQTimeByRG( from_ , to_ , resourceGroup , wosData);
END;
/


CREATE OR REPLACE PROCEDURE "REPORTREADER"."SPRPTAVGTASKDURBYRGRR" ( from_         IN  varchar2,
                                                                to_           IN  varchar2,
                                                                resourceGroup IN  varchar2,
                                                                wosData       OUT WFSDW.PKG_REPORTS.WosAvgTaskDurByRGCurType) AS
BEGIN
  WFSDW.PKG_REPORTS.spRPTAvgTaskDurByRG(   from_, to_ , resourceGroup, wosData);
END;
/


CREATE OR REPLACE PROCEDURE "REPORTREADER"."SPRPTDRIVEUSAGERR" ( from_            IN  varchar2,
                                                             to_              IN  varchar2,
                                                             driveNames       IN  varchar2,
                                                             wosData          OUT WFSDW.PKG_REPORTS.WosDriveUsageCurType) AS
BEGIN
  WFSDW.PKG_REPORTS.spRPTDriveUsage ( from_, to_, driveNames, wosData);
END;
/


CREATE OR REPLACE PROCEDURE "REPORTREADER"."SPRPTWORKORDERSRR" ( from_       IN  varchar2,
                                               to_         IN  varchar2,
                                               template    IN  varchar2,
                                               Provider_WK IN  integer,
                                               wosData     OUT WFSDW.PKG_REPORTS.WosCurType) IS
BEGIN
	wfsdw.PKG_REPORTS.spRPTWorkOrders(from_,to_,template,Provider_WK,wosData);
END;
/


CREATE OR REPLACE PROCEDURE "REPORTREADER"."SPRPTWOSFORALLPROVRR" (  from_    IN  varchar2,
                                                                to_      IN  varchar2,
                                                                template IN  varchar2,
                                                                wosData  OUT WFSDW.PKG_REPORTS.WosForAllProvCurType) AS
BEGIN
   WFSDW.PKG_REPORTS.spRPTWOsForAllProv( from_, to_, template, wosData);
END;
/


CREATE OR REPLACE PROCEDURE "REPORTREADER"."SPRPTWOSPURGERR" (  from_      IN  varchar2,
                                                            to_        IN  varchar2,
                                                            provider   IN  varchar2,
                                                            wosData    OUT WFSDW.PKG_REPORTS.CURSORTYPE) IS
BEGIN
  WFSDW.PKG_REPORTS.spRPTWOsPurge(from_, to_, provider, wosData);
END;
/


CREATE OR REPLACE PROCEDURE "REPORTREADER"."SPTASKDETAILSBYRESOURCERR" ( startdate IN varchar2,
                        enddate IN varchar2,
                        resources IN varchar2,
                        wosData OUT wfsdw.PKG_REPORTS.WosCurType )  IS
BEGIN
  WFSDW.PKG_REPORTS.spTaskDetailsByResource( startdate, enddate , resources, wosData );
END;
/


CREATE OR REPLACE PROCEDURE "REPORTREADER"."SPWOSLICENSEWINDOWMISSRR" (fromDate IN varchar2,
                                            toDate IN varchar2,
                                            WorkOrders OUT WFSDW.PKG_REPORTS.CURSORTYPE) AS
BEGIN
    WFSDW.PKG_REPORTS.spWOsLicenseWindowMiss(fromDate, toDate, WorkOrders);
END;
/


CREATE OR REPLACE PROCEDURE "REPORTREADER"."SPWOSPERPACKAGEASSETIDRR" (  fromDate        IN varchar2,
                                                                    toDate          IN varchar2,
                                                                    packageAssetID  IN varchar2,
                                                                    WorkOrders      OUT WFSDW.PKG_REPORTS.CURSORTYPE)
  IS
BEGIN
  WFSDW.PKG_REPORTS.spWOsPerPackageAssetID(fromDate, toDate, packageAssetID, WorkOrders);
END;
/
           
