/****** Object:  UserDefinedFunction [dbo].[ENPRISE_GET_APOSTROPHE_STR]    Script Date: 06/02/2015 11:57:32 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ENPRISE_GET_APOSTROPHE_STR]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION ENPRISE_GET_APOSTROPHE_STR

GO

CREATE FUNCTION [dbo].[ENPRISE_GET_APOSTROPHE_STR](@THESTR NVARCHAR(MAX)) 
RETURNS NVARCHAR(MAX) AS 
BEGIN 
    DECLARE @RESULT NVARCHAR(MAX) 
    SET @RESULT = REPLACE(RTRIM(LTRIM(@THESTR)), ',', ''',''');


    IF (SUBSTRING(@RESULT, 1 , 1)) <> ''''
        SET @RESULT = '''' + @RESULT;
	
    IF (SUBSTRING(@RESULT, LEN(@RESULT), 1)) <> ''''
        SET @RESULT = @RESULT + '''';	
		
    RETURN @RESULT 
END

GO
/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHNONBILLABLEENTRIES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHNONBILLABLEENTRIES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHNONBILLABLEENTRIES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHNONBILLABLEENTRIES]
    @SEARCH	NVARCHAR(100),
    @MAXRECORDS	INT
AS
BEGIN	
    DECLARE @NONBILLABLELIST TABLE(  ID INT,  TITLE NVARCHAR(50) );
	
    INSERT INTO @NONBILLABLELIST
    (ID, TITLE)
    SELECT 0, 'By Item';	
	
    INSERT INTO @NONBILLABLELIST
    (ID, TITLE)
    SELECT 1, 'Default NO';	
	
    INSERT INTO @NONBILLABLELIST
    (ID, TITLE)
    SELECT 2, 'Default YES';
		
    INSERT INTO @NONBILLABLELIST
    (ID, TITLE)
    SELECT 3, 'Force NO';	
	
    INSERT INTO @NONBILLABLELIST
    (ID, TITLE)
    SELECT 4 ID, 'Force YES';
	
    SELECT TOP(@MAXRECORDS) ID, TITLE
    FROM @NONBILLABLELIST
    WHERE (@SEARCH IS NULL OR TITLE LIKE '%' + @SEARCH + '%');
END

GO
/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETTIMESHEETENTRIES]    Script Date: 06/02/2015 11:57:31 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETTIMESHEETENTRIES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETTIMESHEETENTRIES]
GO

CREATE PROCEDURE SP_ENPRISE_EA_GETTIMESHEETENTRIES
    @START_DATE	DATETIME,
    @END_DATE DATETIME,
    @USER_IDS VARCHAR(100),
    @SUBJOB_IDS	VARCHAR(100),
    @APPOINTMENT_IDS VARCHAR(100)
AS
BEGIN
    DECLARE @WHERESTR NVARCHAR(4000);
    DECLARE @SQL NVARCHAR(4000);
    DECLARE @PARMDEFINITION NVARCHAR(4000);
    DECLARE @EMP_IDS VARCHAR(500);
    DECLARE @TBL_USERIDS TABLE(ID INT);
    DECLARE @XML_USERIDS XML;
			
    SET @WHERESTR = '(A.LINETYPE = ''S'')'; 
	
    IF (@START_DATE IS NOT NULL)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND ';
			
	    SET @WHERESTR = @WHERESTR + ' A.TRANSDATE >= @STARTTRANSDATE';		
    END;
	
    IF (@END_DATE IS NOT NULL)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND ';
			
	    SET @WHERESTR = @WHERESTR + ' A.TRANSDATE <= @ENDTRANSDATE';			
    END;
	
    IF (@USER_IDS IS NOT NULL AND LEN(@USER_IDS) > 0)
    BEGIN				
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND ';
			
	    SET @WHERESTR = @WHERESTR + ' U.USERID IN (' + @USER_IDS + ')';					
    END
		
    IF (@SUBJOB_IDS IS NOT NULL AND LEN(@SUBJOB_IDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND ';
			
	    SET @WHERESTR = @WHERESTR + ' A.SUBJOBID IN (' + @SUBJOB_IDS + ')';
    END;
	
    IF (@APPOINTMENT_IDS IS NOT NULL AND LEN(@APPOINTMENT_IDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND ';

	    SET @WHERESTR = @WHERESTR + 'A.SCHEDULEDETAIL IN (' + @APPOINTMENT_IDS + ')';
    END;
	
    IF LEN(@WHERESTR) > 0  
	    SET @WHERESTR = ' WHERE ' + @WHERESTR;

    SET @SQL =
	    'SELECT A.SEQNO AS ID, ' +
	    'U.USERID AS USER_ID, ' +
	    'ISNULL(U.NAME, '''') AS USER_DISPLAY_NAME, ' +
	    'A.STARTDATE AS START_DATE, ' +
	    'A.ENDDATE AS END_DATE,	' +
	    'A.QTY AS DURATION, ' +
	    'A.SUBJOBID AS SUB_JOB_ID, ' +
	    'S.JOBTITLE AS SUB_JOB_TITLE, ' +
	    'S.CATEGORY AS SUB_JOB_CATEGORY_ID, ' +
	    'C.CATEGORY AS SUB_JOB_CATEGORY_TITLE, ' +
	    'J.JOBID AS JOB_ID, ' +
	    'J.JOBTITLE AS JOB_TITLE, ' +
	    'O.CARDCODE AS CUSTOMER_ID, ' +
	    'O.CARDNAME AS CUSTOMER_NAME, ' +
	    'A.STOCKCODE AS ITEM_CODE, ' +
	    'A.DESCRIPTION AS ITEM_DESCRIPTION, ' +
	    'CASE A.NONBILLABLE ' +
	    '	WHEN ''Y'' THEN 1 ' +
	    '	ELSE 0 ' +
	    'END AS NON_BILLABLE, ' +
	    'A.COSTINGCODE AS COST_CENTER_ID, ' +
	    'A.ANALYSIS1 AS  ANALYSIS_CODE_1, ' +
	    'A.ANALYSIS2 AS ANALYSIS_CODE_2, ' +
	    'A.ACTIVITYID AS ACTIVITY_ID, ' +
	    'A.CREATEDATE AS DATE_CREATED, ' +
	    'A.TRANSDATE AS TRANS_DATE, ' +
	    'N.NOTE AS ADDITIONAL_NOTES, ' +
	    'A.TIMETYPE AS TIME_TYPE_ID, ' +
	    'CASE ' +
	    '	WHEN A.APPROVED = ''Y'' THEN 3 ' +
	    '	WHEN A.SUBMITTED = ''Y'' THEN 2 ' +
	    '	ELSE 1 ' +
	    'END AS STATUS ' +
	    'FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES A ' +		
	    'LEFT JOIN ENPRISE_TSM_USERS U ON U.REFERENCEID = A.EMPID ' +
	    'LEFT JOIN ENPRISE_JOBCOST_SUBJOB S ON S.SUBJOBID = A.SUBJOBID ' +		
	    'LEFT JOIN ENPRISE_JOBCOST_CATEGORY C ON C.SEQNO = S.CATEGORY ' +
	    'LEFT JOIN ENPRISE_JOBCOST_JOB J ON J.JOBID = S.JOBID ' +
	    'LEFT JOIN OCRD O ON O.CARDCODE = S.CARDCODE ' +
	    'LEFT JOIN ENPRISE_JOBCOST_TEMPBATCHNOTES N ON N.SEQNO = A.NOTENO ' +		
	    @WHERESTR;
		
	    SET @PARMDEFINITION = '@STARTTRANSDATE DATETIME, @ENDTRANSDATE DATETIME';
		
	    PRINT @SQL
		
	    EXEC SP_EXECUTESQL @SQL
	    , @PARMDEFINITION 
	    , @STARTTRANSDATE = @START_DATE
	    , @ENDTRANSDATE = @END_DATE;
				
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETAPPOINTMENTS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETAPPOINTMENTS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETAPPOINTMENTS]
GO

CREATE PROCEDURE SP_ENPRISE_EA_GETAPPOINTMENTS
       @START_DATE  DATETIME,
       @END_DATE DATETIME,
       @USER_IDS VARCHAR(100)
AS
BEGIN
    DECLARE @WHERESTR NVARCHAR(4000);
    DECLARE @PARMDEFINITION NVARCHAR(500);
    DECLARE @SQL NVARCHAR(4000);
    DECLARE @REFERENCE_USERS VARCHAR(500);
    DECLARE @TBL_USERIDS TABLE(ID INT);
    DECLARE @XML_USERIDS XML;

    SET @WHERESTR = 'B.RESOURCETYPE = ''S'' ';

    IF (@START_DATE IS NOT NULL)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		     SET @WHERESTR = @WHERESTR + ' AND ';
	    SET @WHERESTR = @WHERESTR + ' A.STARTDATE >= @STARTDATE'
    END;

    IF (@END_DATE IS NOT NULL)
    BEGIN
	      IF LEN(@WHERESTR) > 0
			     SET @WHERESTR = @WHERESTR + ' AND ';
	      SET @WHERESTR = @WHERESTR + ' A.ENDDATE <= @ENDDATE'
    END;

    IF (@USER_IDS IS NOT NULL AND LEN(@USER_IDS) > 0)
    BEGIN
	    --SET @XML_USERIDS = '<xml><e>' + REPLACE(@USER_IDS,',','</e><e>') + '</e></xml>';
	       
	    ---- Get ID's from xml
	    --INSERT INTO @TBL_USERIDS(ID)
	    --SELECT N.value('.', 'INT')
	    --FROM @XML_USERIDS.nodes('xml/e') AS T(N);	
		 
	    --SET @REFERENCE_USERS =
	    --STUFF(( SELECT ',' + CONVERT(VARCHAR(20), A.REFERENCEID)
	    --		FROM ENPRISE_TSM_USERS A
	    --		JOIN @TBL_USERIDS B ON B.ID = A.USERID		
	    --		FOR XML PATH ('')), 1, 1, '');
				
	    --IF (@REFERENCE_USERS IS NOT NULL AND LEN(@REFERENCE_USERS) > 0)
	    --BEGIN		 				             			                   
	    --	IF LEN(@WHERESTR) > 0
	    --		SET @WHERESTR = @WHERESTR + ' AND ';
	    --	SET @WHERESTR = @WHERESTR + ' C.USERID IN (' + @REFERENCE_USERS + ')';
	    --END;
		
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND ';
	    SET @WHERESTR = @WHERESTR + ' C.USERID IN (' + @USER_IDS + ')';
    END;

    IF  LEN(@WHERESTR) > 0
	      SET @WHERESTR = ' WHERE ' + @WHERESTR;
	      
    SET @SQL = 
	    ' SELECT A.SEQNO AS ID,' +
	    ' CONVERT(VARCHAR(20), B.SUBJOBID) + ' +
	    ' ISNULL( ' +
	    '   STUFF(( ' +                           
	    '                 SELECT '','' + CONVERT(VARCHAR(20), T.SUBJOBID)' +
	    '                 FROM ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS T ' +
	    '                 WHERE T.SCHEDULEDETAILSID = A.SEQNO ' +
	    '                 FOR XML PATH ('''')), 1, 0, ''''), '''') AS SUB_JOB_IDS, ' + 
	    ' CASE ' +
	    '    WHEN A.STARTTIME IS NULL THEN A.STARTDATE ' +
	    '    ELSE DATEADD(MINUTE, ((A.STARTTIME/100) * 60) + (A.STARTTIME % 100), CAST(CAST(A.STARTDATE AS DATE) AS DATETIME)) ' +		
	    ' END AS START_DATE, ' +
	    ' CASE ' +
	    '    WHEN A.ENDTIME IS NULL THEN A.ENDDATE ' +
	    '    ELSE DATEADD(MINUTE, ((A.ENDTIME/100) * 60) + (A.ENDTIME % 100), CAST(CAST(A.ENDDATE AS DATE) AS DATETIME)) ' +		
	    ' END AS END_DATE, ' +               
	    ' CASE ' +
	    '    WHEN A.DESCRIPTION  IS NULL THEN O.FIRSTNAME + '' '' + O.LASTNAME ' +
	    '    ELSE A.DESCRIPTION ' +
	    ' END AS SUBJECT, ' +
	    ' B.RESOURCEID AS ASSIGNED_USER_ID, ' +
	    ' C.USERNAME AS ASSIGNED_USER_DISPLAY_NAME, ' + 
	    ' A.ADDRESS1 + '','' + A.ADDRESS2 + '','' + A.ADDRESS3 + '','' + A.ADDRESS4 + '','' + A.ADDRESS5 + '','' + A.ADDRESS6 AS SITE_LOCATION_FULL_ADDRESS ' +
	    ' FROM ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS A ' +            
	    ' LEFT JOIN ENPRISE_JOBCOST_JOBSCHEDULING B ON B.SEQNO = A.SCHEDULINGID ' +
	    ' LEFT JOIN OHEM O ON O.EMPID = B.RESOURCEID ' + 
	    ' LEFT JOIN ENPRISE_TSM_USERS C ON C.REFERENCEID = O.USERID ' +  		                 
	    @WHERESTR;           
	         
    SET @PARMDEFINITION = '@STARTDATE DATETIME, @ENDDATE DATETIME';

    EXEC SP_EXECUTESQL @SQL
    , @PARMDEFINITION 
    , @STARTDATE = @START_DATE
    , @ENDDATE = @END_DATE;       
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETCONTEXTSENSITIVEHELPURL]    Script Date: 06/02/2015 11:57:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETCONTEXTSENSITIVEHELPURL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETCONTEXTSENSITIVEHELPURL]

GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETCONTEXTSENSITIVEHELPURL]
AS
BEGIN
    SELECT 'www.enprise.com/ProductInfo/EnpriseAnywhere/manual/index.html';
END;

GO
/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETLICENSEPERMISSIONS]    Script Date: 06/02/2015 11:57:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETLICENSEPERMISSIONS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETLICENSEPERMISSIONS]

GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETLICENSEPERMISSIONS]
    @LICENSETYPE  INT
AS
BEGIN	
    IF (@LICENSETYPE IS NULL) OR (@LICENSETYPE NOT IN (640, 650))
	    RETURN;
	
    SELECT * FROM
    (
	    SELECT 1 AS PERMISSION_ID, 		
	    'Time Sheet Entry' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 2 AS PERMISSION_ID, 		
	    'Time Sheet Entry Other' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 3 AS PERMISSION_ID, 		
	    'Expense Entry' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 4 AS PERMISSION_ID, 		
	    'Expense Entry Other' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 5 AS PERMISSION_ID, 		
	    'Attachments Entry' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 6 AS PERMISSION_ID, 		
	    'Activity Entry' AS TITLE,
	    1 AS READ_AVAILABLE,
	    0 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 7 AS PERMISSION_ID, 		
	    'Activity Entry Other' AS TITLE,
	    1 AS READ_AVAILABLE,
	    0 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 8 AS PERMISSION_ID, 		
	    'Material Entry' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 9 AS PERMISSION_ID, 		
	    'Material Entry Other' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 10 AS PERMISSION_ID, 		
	    'Material Receipts' AS TITLE,
	    0 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 11 AS PERMISSION_ID, 		
	    'Subjob Maintenance' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 12 AS PERMISSION_ID, 		
	    'Appointment Entry Other' AS TITLE,
	    0 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 13 AS PERMISSION_ID, 		
	    'Appointment View Other' AS TITLE,
	    0 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
						
    ) AS A
    WHERE @LICENSETYPE <> 640 OR PERMISSION_ID NOT IN (6,7,8,9,10,11,12,13)
    -- if application is Anywhere Lite then no Activity, Material or Subjob Permissions
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_DISPLAY_CLEARPREFERENCES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_DISPLAY_CLEARPREFERENCES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_DISPLAY_CLEARPREFERENCES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_DISPLAY_CLEARPREFERENCES]
    @P_VIEWNAME VARCHAR(20),
    @P_USERID INT
AS
BEGIN	
    DELETE FROM ENPRISE_SETUP_FORM
    WHERE USERSIGN = @P_USERID AND FORMID = @P_VIEWNAME AND ITEMID = @P_VIEWNAME
END

GO
/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_DISPLAY_ADDPREFERENCES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_DISPLAY_ADDPREFERENCES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_DISPLAY_ADDPREFERENCES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_DISPLAY_ADDPREFERENCES]
        @P_VIEWNAME VARCHAR(20),
        @P_USERID INT,
        @P_ORDER INT,
        @P_FIELDID VARCHAR(200)
AS
BEGIN
    DECLARE @CAPTION NVARCHAR(100);
    
    SET @CAPTION = 
    (
        SELECT TOP 1 A.CAPTION
        FROM ENPRISE_JOBCOST_EA_CAPTIONS A
        WHERE A.FIELDID = @P_FIELDID AND (A.VIEWNAME = @P_VIEWNAME OR A.VIEWNAME IS NULL)
        ORDER BY A.VIEWNAME DESC
    )
    --ensure the caption is set to something
    SET @CAPTION = ISNULL(@CAPTION, @P_FIELDID);
        
    IF EXISTS(SELECT 1 FROM ENPRISE_SETUP_FORM WHERE USERSIGN = @P_USERID AND FORMID = @P_VIEWNAME AND ITEMID = @P_VIEWNAME AND COLID = @P_FIELDID)
    BEGIN                
        UPDATE ENPRISE_SETUP_FORM
        SET VISUALINDEX = @P_ORDER, CAPTION = @CAPTION
        WHERE USERSIGN = @P_USERID AND FORMID = @P_VIEWNAME AND ITEMID = @P_VIEWNAME AND COLID = @P_FIELDID 
    END
    ELSE
    BEGIN
        INSERT INTO ENPRISE_SETUP_FORM
        (USERSIGN, FORMID, ITEMID, COLID, CAPTION, VISUALINDEX, VISINFORM, EDITINFORM, ENABLED, CHANGEDBYUSER)
        VALUES(@P_USERID, @P_VIEWNAME, @P_VIEWNAME, @P_FIELDID, @CAPTION, @P_ORDER, 'Y', 'Y', 'Y', 'Y');
    END;
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_ACTIVITIES_GETTABLE]    Script Date: 06/02/2015 11:57:31 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_ACTIVITIES_GETTABLE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_ACTIVITIES_GETTABLE]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_ACTIVITIES_GETTABLE]
    @CUSTOMERIDS VARCHAR(500),
    @JOBIDS VARCHAR(500),
    @SUBJOBIDS VARCHAR(500),
    @ACTIVITIESIDS VARCHAR(500),
    @P_VIEWNAME VARCHAR(20),
    @VIEWING_PLATFORM INT,
    @FORMSTYLE INT,    
    @P_USERID INT,
    @I_PAGEINDEX INT,
    @I_PAGESIZE INT,
    @I_SORTORDER VARCHAR(200),
    @I_STARTDATE DATETIME,
    @I_ENDDATE DATETIME,   
    @O_TOTALRECORDS INT OUTPUT
AS
BEGIN
    DECLARE @DEFAULTCOLUMNS NVARCHAR(MAX);
    DECLARE @USERCOLUMNS NVARCHAR(MAX);
    DECLARE @COLUMNS NVARCHAR(MAX);
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @WHERESTR NVARCHAR(MAX)
    DECLARE @PARMDEFINITION NVARCHAR(500);	
	
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
	
    SET @O_TOTALRECORDS = 0;
			
    --SET @DEFAULTCOLUMNS = 
    --	'L.ID, L.SUB_JOB_ID, L.SUB_JOB_TITLE, L.JOB_ID, L.CUSTOMER_ID, L.CUSTOMER_NAME, L.SUBJECT, ' +
    --	'L.ASSIGNED_USER_ID, L.ASSIGNED_USER_DISPLAY_NAME, L.STATUS_TEXT, L.SITE_LOCATION_FULL_ADDRESS, ' +
    --	'L.DESCRIPTION, L.INTERNAL_NOTES, L.START_DATE, L.END_DATE, L.DATE_CREATED';	
	
    SET @DEFAULTCOLUMNS = 
	    'L.ID, L.SUB_JOB_ID, L.SUB_JOB_TITLE, L.JOB_ID, L.CUSTOMER_ID, L.CUSTOMER_NAME';
		
   	
    -- get the user column preferences
    SET @USERCOLUMNS = ''; 

    SELECT @USERCOLUMNS = 
    CASE LEN(@USERCOLUMNS)
	    WHEN 0 THEN @USERCOLUMNS + COLID 
	    ELSE @USERCOLUMNS + ',' + COLID 
    END    
    FROM ENPRISE_SETUP_FORM
    WHERE USERSIGN = @P_USERID AND FORMID = @P_VIEWNAME AND ITEMID = @P_VIEWNAME
    AND ENABLED = 'Y' AND VISINFORM = 'Y'
    ORDER BY VISUALINDEX;
	
    IF LEN(@USERCOLUMNS) > 0
	    SET @COLUMNS = @USERCOLUMNS
    ELSE
	    SET @COLUMNS = @DEFAULTCOLUMNS;
		
    SET @WHERESTR = '';
	
    IF (@CUSTOMERIDS IS NOT NULL AND LEN(@CUSTOMERIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND ';		
			
	    SET @WHERESTR = @WHERESTR + ' CUSTOMER_ID IN (' + DBO.ENPRISE_GET_APOSTROPHE_STR(@CUSTOMERIDS) + ')'					
    END;
	
    IF (@JOBIDS IS NOT NULL AND LEN(@JOBIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND ';
		
	    SET @WHERESTR = @WHERESTR + ' JOB_ID IN (' + @JOBIDS + ')';
    END;
	
    IF (@SUBJOBIDS IS NOT NULL AND LEN(@SUBJOBIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND ';		
		
	    SET @WHERESTR = @WHERESTR + ' SUB_JOB_ID IN (' + @SUBJOBIDS + ')'
    END;
	
    IF (@ACTIVITIESIDS IS NOT NULL AND LEN(@ACTIVITIESIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND ';		
		
	    SET @WHERESTR = @WHERESTR + ' ID IN (' + @ACTIVITIESIDS + ')';
    END;
	
    IF (@I_STARTDATE IS NOT NULL)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND ';
		
	    SET @WHERESTR = @WHERESTR + ' START_DATE >= @STARTDATE '
    END;
	
    IF (@I_ENDDATE IS NOT NULL)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND ';
		
	    SET @WHERESTR = @WHERESTR + ' END_DATE <= @ENDDATE '
    END;
	
    IF LEN(@WHERESTR) > 0	
	    SET @WHERESTR = ' WHERE ' + @WHERESTR;
	
    -- there MUST be a sort order!!
    IF (@I_SORTORDER IS NULL OR LEN(@I_SORTORDER) = 0)
	    SET @I_SORTORDER = ' ORDER BY ID ';
    ELSE IF LEN(@I_SORTORDER) > 0
	    SET @I_SORTORDER = ' ORDER BY ' + @I_SORTORDER;

    --- determine the total number of rows which meet the criteria		
    SET @SQL = 
	    ' SELECT @RESULT = COUNT(*) '+
	    ' FROM ENPRISE_EA_ACTIVITIESVIEW L ' +
	    @WHERESTR;			
			
    SET @PARMDEFINITION = '@RESULT INT OUTPUT, @STARTDATE DATETIME, @ENDDATE DATETIME';	
	
    EXEC SP_EXECUTESQL @SQL  
      , @PARMDEFINITION
      , @RESULT = @O_TOTALRECORDS OUTPUT
      , @STARTDATE = @I_STARTDATE
      , @ENDDATE = @I_ENDDATE;

    ---- return the desired result set
    SET @SQL = 
	    ' SELECT TOP (' + CAST(@I_PAGESIZE AS NVARCHAR) + ') A.* FROM ' +
	    ' ( ' +
	    '	SELECT ' + @COLUMNS + ', ROW_NUMBER() OVER ( ' + @I_SORTORDER + ' )  AS ROWNUM ' +
	    '	FROM ENPRISE_EA_ACTIVITIESVIEW L ' +
		    @WHERESTR +
	    ' ) AS A ' +
	    ' WHERE ROWNUM > ' + CAST(@I_PAGEINDEX * @I_PAGESIZE AS NVARCHAR) + ';';
	
    --SELECT @WHERESTR + @I_SORTORDER;
	
    --SELECT @SQL;		
	
    SET @PARMDEFINITION = '@STARTDATE DATETIME, @ENDDATE DATETIME';	
	
    EXEC SP_EXECUTESQL @SQL
      , @PARMDEFINITION
      , @STARTDATE = @I_STARTDATE
      , @ENDDATE = @I_ENDDATE;
		

END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_EXPENSES_GETTABLE]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_EXPENSES_GETTABLE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_EXPENSES_GETTABLE]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_EXPENSES_GETTABLE]
        @CUSTOMERIDS VARCHAR(500),
        @JOBIDS VARCHAR(500),
        @SUBJOBIDS VARCHAR(500),
        @APPOINTMENTIDS	VARCHAR(500),
        @EXPENSESIDS VARCHAR(500),
        @P_VIEWNAME VARCHAR(20),
        @VIEWING_PLATFORM INT,
        @FORMSTYLE INT,           
        @P_USERID INT,
        @I_PAGEINDEX INT,
        @I_PAGESIZE INT,
        @I_SORTORDER VARCHAR(200),
        @I_CREATESTARTDATE DATETIME,
        @I_CREATEENDDATE DATETIME,                 
        @O_TOTALRECORDS INT OUTPUT
AS
BEGIN
    DECLARE @DEFAULTCOLUMNS NVARCHAR(MAX);
    DECLARE @USERCOLUMNS NVARCHAR(MAX);
    DECLARE @COLUMNS NVARCHAR(MAX);
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @WHERESTR NVARCHAR(MAX)
    DECLARE @PARMDEFINITION NVARCHAR(500);
    
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
    SET @O_TOTALRECORDS = 0;
        
  --  SET @DEFAULTCOLUMNS = 
	    --' L.SEQNO, L.SUBJOBID, L.JOBTITLE, L.STOCKCODE, L.DESCRIPTION, L.JOBID, L.QTY, L.AVECOST, L.NOTES, ' +
	    --' L.ANALYSIS1ID, L.ANALYSIS1NAME,' +
	    --' L.ANALYSIS2ID, L.ANALYSIS2NAME, ' +
	    --' L.NONBILLABLE, L.USERID, L.USERFULLNAME, L.CREATEDATE, ' +
	    --' L.TRANSDATE, L.STARTDATE, L.POSTED, L.CARDCODE, L.CARDNAME, ' +
	    --' L.SUBMITTED, L.APPROVED, L.ACTIVITYID,' +
	    --' L.ENTEREDBYSUBMITREQ, L.ENTEREDBYTIMESHEETAPPR,' +
	    --' L.ENTERBYEXPENSEAPPR, L.ENTERBYEXPENSEMIN, ' +           
	    --' L.EXPREIMBURSE, L.EXPREIMBURSECARDCODE, ' +
	    --' L.ENTEREDBYEXPREIMBURSECARDCODE, ' +
	    --' L.EXPFXCOST, L.EXPFXRATE, L.EXPCURRCODE, ' +
	    --' L.EXPREIMBURSEOBJTYPE, L.ENTEREDBYEXPREIMBURSEOBJTYPE, ' +		
	    --' L.TAXCODE, L.COSTINGCODE ' +           
	    --DBO.ENPRISE_GETTABLEUDFCOLUMNS('ENPRISE_JOBCOST_TEMPBATCHJOBLINES', 'L');
		
    SET @DEFAULTCOLUMNS = 
	    ' L.SEQNO, L.SUBJOBID, L.JOBTITLE, L.JOBID, L.CARDCODE, L.CARDNAME, L.QTY, L.STOCKCODE, L.DESCRIPTION, L.CREATEDATE, L.TOTALCOST ';
		
		        
    -- get the user column preferences
    SET @USERCOLUMNS = ''; 

    SELECT @USERCOLUMNS = 
    CASE LEN(@USERCOLUMNS)
	    WHEN 0 THEN @USERCOLUMNS + COLID 
	    ELSE @USERCOLUMNS + ',' + COLID 
    END    
    FROM ENPRISE_SETUP_FORM
    WHERE USERSIGN = @P_USERID AND FORMID = @P_VIEWNAME AND ITEMID = @P_VIEWNAME
    AND ENABLED = 'Y' AND VISINFORM = 'Y'
    ORDER BY VISUALINDEX;
    
    IF LEN(@USERCOLUMNS) > 0
	    SET @COLUMNS = @USERCOLUMNS
    ELSE
	    SET @COLUMNS = @DEFAULTCOLUMNS;

    SET @WHERESTR = ' L.BATCHNO = 2 AND L.LINETYPE = ''M'' AND L.POSTED = ''N'' ';
    
    IF (@CUSTOMERIDS IS NOT NULL AND LEN(@CUSTOMERIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND L.CARDCODE IN (' + DBO.ENPRISE_GET_APOSTROPHE_STR(@CUSTOMERIDS) + ')'
	    ELSE
		    SET @WHERESTR = ' L.CARDCODE IN (' + DBO.ENPRISE_GET_APOSTROPHE_STR(@CUSTOMERIDS) + ')'                                 
    END;
    
    IF (@JOBIDS IS NOT NULL AND LEN(@JOBIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND L.JOBID IN (' + @JOBIDS + ')'
	    ELSE
		    SET @WHERESTR = ' L.JOBID IN (' + @JOBIDS + ')';
    END;
        
    IF (@SUBJOBIDS IS NOT NULL AND LEN(@SUBJOBIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND L.SUBJOBID IN (' + @SUBJOBIDS + ')'
	    ELSE
		    SET @WHERESTR = ' L.SUBJOBID IN (' + @SUBJOBIDS + ')'
    END;

    IF (@APPOINTMENTIDS IS NOT NULL AND LEN(@APPOINTMENTIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND L.SCHEDULEDETAIL IN (' + @APPOINTMENTIDS + ')'
	    ELSE
		    SET @WHERESTR = ' L.SCHEDULEDETAIL IN (' + @APPOINTMENTIDS + ')'
    END;
        
    IF (@EXPENSESIDS IS NOT NULL AND LEN(@EXPENSESIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
	        SET @WHERESTR = @WHERESTR + ' AND L.SEQNO IN (' + @EXPENSESIDS + ')'
	    ELSE
		    SET @WHERESTR = ' L.SEQNO IN (' + @EXPENSESIDS + ')';
    END;
	
    IF (@I_CREATESTARTDATE IS NOT NULL)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND L.CREATEDATE >= @STARTCREATEDATE '
	    ELSE
		    SET @WHERESTR = ' L.CREATEDATE >= @STARTCREATEDATE '
    END;
	
    IF (@I_CREATEENDDATE IS NOT NULL)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND L.CREATEDATE <= @ENDCREATEDATE '
	    ELSE
		    SET @WHERESTR = ' L.CREATEDATE <= @ENDCREATEDATE '
    END;

    IF LEN(@WHERESTR) > 0  
	    SET @WHERESTR = ' WHERE ' + @WHERESTR;

    -- there MUST be a sort order!!
    IF (@I_SORTORDER IS NULL OR LEN(@I_SORTORDER) = 0)
	       SET @I_SORTORDER = ' ORDER BY L.SEQNO ';
    ELSE IF LEN(@I_SORTORDER) > 0
	       SET @I_SORTORDER = ' ORDER BY ' + @I_SORTORDER;
              
    --- determine the total number of rows which meet the criteria             
    SET @SQL = 
	       ' SELECT @RESULT = COUNT(*) '+
	       ' FROM ENPRISE_EA_TEMPBATCHJOBLINESVIEW L ' + 
       	    @WHERESTR;
	       
    SET @PARMDEFINITION = '@RESULT INT OUTPUT, @STARTCREATEDATE DATETIME, @ENDCREATEDATE DATETIME';
	
    EXEC SP_EXECUTESQL 
    @SQL  
    , @PARMDEFINITION
    , @RESULT = @O_TOTALRECORDS OUTPUT
    , @STARTCREATEDATE = @I_CREATESTARTDATE
    , @ENDCREATEDATE = @I_CREATEENDDATE;
	
    
    ---- return the desired result set
    SET @SQL = 
           ' SELECT TOP (' + CAST(@I_PAGESIZE AS NVARCHAR) + ') A.* FROM ' +
           ' ( ' +
           '	SELECT ' + @COLUMNS + ', ROW_NUMBER() OVER ( ' + @I_SORTORDER + ' )  AS ROWNUM ' +
           '	FROM ENPRISE_EA_TEMPBATCHJOBLINESVIEW L ' +            
                @WHERESTR +
           ' ) AS A ' +
           ' WHERE ROWNUM > ' + CAST(@I_PAGEINDEX * @I_PAGESIZE AS NVARCHAR) + ';';
    
    --SELECT @WHERESTR + @I_SORTORDER;
    
    --SELECT @SQL;
    
    SET @PARMDEFINITION = '@STARTCREATEDATE DATETIME, @ENDCREATEDATE DATETIME';
    
    EXEC SP_EXECUTESQL @SQL
    , @PARMDEFINITION 
    , @STARTCREATEDATE = @I_CREATESTARTDATE
    , @ENDCREATEDATE = @I_CREATEENDDATE;
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_DISPLAY_PREFERENCES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_DISPLAY_PREFERENCES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_DISPLAY_PREFERENCES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_DISPLAY_PREFERENCES]
    @P_VIEWNAME VARCHAR(20),
    @P_USERID INT,
    @P_OBJTYPE INT,
    @VIEWING_PLATFORM INT,
    @FORMSTYLE INT
AS
BEGIN
    DECLARE @TOTALCOUNT INT;
        
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT @TOTALCOUNT = COUNT(*)
    FROM ENPRISE_SETUP_FORM
    WHERE USERSIGN = @P_USERID AND FORMID = @P_VIEWNAME AND ITEMID = @P_VIEWNAME;
    
    IF (@TOTALCOUNT > 0)
    BEGIN
	    SELECT COLID AS FIELDID, CAPTION AS TITLE 
	    FROM ENPRISE_SETUP_FORM
	    WHERE USERSIGN = @P_USERID AND FORMID = @P_VIEWNAME AND ITEMID = @P_VIEWNAME
	    ORDER BY VISUALINDEX;
    END
    ELSE
    BEGIN
	    SELECT COLID AS FIELDID, CAPTION AS TITLE 
	    FROM ENPRISE_SETUP_FORM
	    WHERE USERSIGN = -1 AND FORMID = CAST(@P_OBJTYPE AS NVARCHAR) AND ITEMID = CAST(@P_OBJTYPE AS NVARCHAR)
	    ORDER BY VISUALINDEX;
    END;		
    
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_DISPLAY_GETPREFERENCE_OPTIONS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_DISPLAY_GETPREFERENCE_OPTIONS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_DISPLAY_GETPREFERENCE_OPTIONS]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_DISPLAY_GETPREFERENCE_OPTIONS]
    @P_VIEWNAME VARCHAR(20),
    @P_OBJTYPE INT,
    @VIEWING_PLATFORM INT,
    @FORMSTYLE INT,
    @USERID	INT
AS
BEGIN
    SELECT A.COLID AS FIELDID,	
    ISNULL(A.CAPTION, X.CAPTION) AS TITLE,
    CASE A.USERSIGN
      WHEN -1 THEN 1	  
      ELSE 0
    END AS ISDEFAULT
    FROM ENPRISE_SETUP_FORM A	
    LEFT JOIN
    (
        SELECT TOP 1 C.FIELDID, C.CAPTION
        FROM ENPRISE_JOBCOST_EA_CAPTIONS C
        WHERE (C.VIEWNAME = @P_VIEWNAME OR C.VIEWNAME IS NULL)
        ORDER BY C.VIEWNAME DESC
    ) X ON X.FIELDID = A.COLID
    WHERE (A.USERSIGN = @USERID AND A.FORMID = @P_VIEWNAME AND A.ITEMID = @P_VIEWNAME)
    OR (A.USERSIGN = -1 AND A.FORMID = CAST(@P_OBJTYPE AS NVARCHAR) AND A.ITEMID = CAST(@P_OBJTYPE AS NVARCHAR))
    ORDER BY A.USERSIGN DESC, A.VISUALINDEX;
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETLICENSEINFO]    Script Date: 07/23/2015 11:11:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETLICENSEINFO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETLICENSEINFO]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETLICENSEINFO]
    @INSTALLATION_ID NVARCHAR(100) OUTPUT,
    @COMPANY_NAME NVARCHAR(100) OUTPUT, 
    @APPID INT OUTPUT,
    @REG_CODE INT OUTPUT,
    @VERSION_ID INT OUTPUT,
    @USERS INT OUTPUT,
    @EXPIRY_DATE NVARCHAR(80) OUTPUT,
    @REGISTERED_NAME NVARCHAR(200) OUTPUT
AS
BEGIN
    SELECT @COMPANY_NAME = COMPNYNAME
    FROM OADM;
    
    SELECT @INSTALLATION_ID = INSTALLATIONID
    FROM ENPRISE_SECURITY
    WHERE APPLICATION = 501;
    
    SELECT @APPID = APPLICATION,
    @REG_CODE = REGISTRATION_CODE,
    @VERSION_ID = VERSION,
    @USERS = USERS,
    @EXPIRY_DATE = VALIDATION_DATE,
    @REGISTERED_NAME = REGISTERED_NAME
    FROM ENPRISE_SECURITY
    WHERE APPLICATION IN (640, 650);
    
    SELECT 
    A.APPLICATION AS LICENSE_TYPE_ID,
    CASE A.APPLICATION
	    WHEN 640 THEN 'Enprise Anywhere Lite'
	    WHEN 650 THEN 'Enprise Anywhere Professional'		
    END AS LICENSE_TYPE_TITLE,
    A.USERS AS TOTAL,
    B.ALLOCATEDUSERS AS ALLOCATED,
    A.USERS - B.ALLOCATEDUSERS AS AVAILABLE
    FROM ENPRISE_SECURITY A
    LEFT JOIN
    (
	    SELECT APPID, COUNT(*) AS ALLOCATEDUSERS
	    FROM ENPRISE_ADDON_USERS
	    GROUP BY APPID
    )B ON B.APPID = A.APPLICATION
    WHERE APPLICATION IN (640, 650)
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETTEXTTEMPLATES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETTEXTTEMPLATES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETTEXTTEMPLATES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETTEXTTEMPLATES]
AS
BEGIN
    SELECT TEXTCODE AS TITLE,
    CAST(TEXT AS NVARCHAR(1000)) AS CONTENT
    FROM OPDT;
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETSYSTEMINFO]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETSYSTEMINFO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETSYSTEMINFO]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETSYSTEMINFO]
AS
BEGIN
    SELECT A.VERSION AS DB_VERSION, B.VERSION AS SAP_INFO, C.VERSION JOB_COSTING_VERSION
    FROM ENPRISE_SECURITY A, CINF B, ENPRISE_SECURITY C
    WHERE A.APPLICATION = 650 AND C.APPLICATION = 501;
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHDOCUMENTGROUPS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHDOCUMENTGROUPS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHDOCUMENTGROUPS]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHDOCUMENTGROUPS]
    @SEARCH	NVARCHAR(100),
    @MAXRECORDS	INT	
AS
BEGIN
    SELECT TOP(@MAXRECORDS) SEQNO AS ID, DESCRIPTION AS TITLE
    FROM ENPRISE_JOBCOST_DOCUMENT_GROUPS
    WHERE @SEARCH IS NULL OR DESCRIPTION LIKE '%' + @SEARCH + '%';
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHCURRENCIES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHCURRENCIES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHCURRENCIES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHCURRENCIES]
    @SEARCH	NVARCHAR(100),
    @MAXRECORDS	INT
AS
BEGIN
    SELECT TOP(@MAXRECORDS) CURRCODE AS ID, CURRNAME AS TITLE
    FROM OCRN
    WHERE @SEARCH IS NULL OR CURRNAME LIKE '%' + @SEARCH + '%' 
    OR CURRCODE LIKE '%' + @SEARCH + '%';
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHCOSTCENTERS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHCOSTCENTERS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHCOSTCENTERS]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHCOSTCENTERS]
    @SEARCH	NVARCHAR(100),
    @MAXRECORDS	INT
AS
BEGIN
    SELECT TOP(@MAXRECORDS) OCRCODE AS ID, OCRNAME AS TITLE
    FROM OOCR
    WHERE @SEARCH IS NULL OR OCRNAME LIKE '%' + @SEARCH + '%' 
    OR OCRCODE LIKE '%' + @SEARCH + '%';
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_MATERIALS_GETTABLE]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_MATERIALS_GETTABLE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_MATERIALS_GETTABLE]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_MATERIALS_GETTABLE]
        @CUSTOMERIDS VARCHAR(500),
        @JOBIDS VARCHAR(500),
        @SUBJOBIDS VARCHAR(500),
        @APPOINTMENTIDS	VARCHAR(500),
        @MATERIALSIDS VARCHAR(500),
        @P_VIEWNAME VARCHAR(20),
        @VIEWING_PLATFORM INT,
        @FORMSTYLE INT,    
        @P_USERID INT,
        @I_PAGEINDEX INT,
        @I_PAGESIZE INT,
        @I_SORTORDER VARCHAR(200),
        @I_CREATESTARTDATE DATETIME,
        @I_CREATEENDDATE DATETIME,   
        @O_TOTALRECORDS INT OUTPUT
AS
BEGIN
    DECLARE @DEFAULTCOLUMNS NVARCHAR(MAX);
    DECLARE @USERCOLUMNS NVARCHAR(MAX);
    DECLARE @COLUMNS NVARCHAR(MAX);
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @WHERESTR NVARCHAR(MAX)
    DECLARE @PARMDEFINITION NVARCHAR(500);
    
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
    SET @O_TOTALRECORDS = 0;
    
    --SET @DEFAULTCOLUMNS = 
    --       ' L.SEQNO, L.SUBJOBID, L.JOBTITLE, L.STOCKCODE, L.DESCRIPTION, L.JOBID, L.QTY, L.AVECOST, L.NOTES,' +
    --       ' L.ANALYSIS1ID, L.ANALYSIS1NAME,' +
    --       ' L.ANALYSIS2ID, L.ANALYSIS2NAME, ' +
    --       ' L.NONBILLABLE, L.FROMWHS, L.WHSNAME, L.USERID, L.USERFULLNAME, L.CREATEDATE, ' +
    --       ' L.TRANSDATE, L.STARTDATE, L.POSTED, L.MANSERNUM, L.CARDCODE, L.CARDNAME, ' +
    --       ' L.SUBMITTED, L.APPROVED, L.ACTIVITYID,' +
    --       ' L.ENTEREDBYSUBMITREQ, L.ENTEREDBYTIMESHEETAPPR,' +
    --       ' L.ENTERBYEXPENSEAPPR, L.ENTERBYEXPENSEMIN, ' +
    --       ' L.TAXCODE, L.COSTINGCODE ' +           
    --       DBO.ENPRISE_GETTABLEUDFCOLUMNS('ENPRISE_JOBCOST_TEMPBATCHJOBLINES', 'L');
                           
    SET @DEFAULTCOLUMNS = 
           ' L.SEQNO, L.SUBJOBID, L.JOBTITLE, L.JOBID, L.CARDCODE, L.CARDNAME, L.STOCKCODE, L.QTY, L.TRANSDATE, L.FROMWHS ';
           
                   
    -- get the user column preferences
    SET @USERCOLUMNS = ''; 

    SELECT @USERCOLUMNS = 
    CASE LEN(@USERCOLUMNS)
	    WHEN 0 THEN @USERCOLUMNS + COLID 
	    ELSE @USERCOLUMNS + ',' + COLID 
    END    
    FROM ENPRISE_SETUP_FORM
    WHERE USERSIGN = @P_USERID AND FORMID = @P_VIEWNAME AND ITEMID = @P_VIEWNAME
    AND ENABLED = 'Y' AND VISINFORM = 'Y'
    ORDER BY VISUALINDEX;
    
    IF LEN(@USERCOLUMNS) > 0
	    SET @COLUMNS = @USERCOLUMNS
    ELSE
	    SET @COLUMNS = @DEFAULTCOLUMNS;

    SET @WHERESTR = ' L.BATCHNO = 3 AND L.LINETYPE = ''M'' AND L.POSTED = ''N'' ';
    
    IF (@CUSTOMERIDS IS NOT NULL AND LEN(@CUSTOMERIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND L.CARDCODE IN (' + DBO.ENPRISE_GET_APOSTROPHE_STR(@CUSTOMERIDS) + ')'
	    ELSE
		    SET @WHERESTR = ' L.CARDCODE IN (' + DBO.ENPRISE_GET_APOSTROPHE_STR(@CUSTOMERIDS) + ')'                                 
    END;
    
    IF (@JOBIDS IS NOT NULL AND LEN(@JOBIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND L.JOBID IN (' + @JOBIDS + ')'
	    ELSE
		    SET @WHERESTR = ' L.JOBID IN (' + @JOBIDS + ')';
    END;
        
    IF (@SUBJOBIDS IS NOT NULL AND LEN(@SUBJOBIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND L.SUBJOBID IN (' + @SUBJOBIDS + ')'
	    ELSE
		    SET @WHERESTR = ' L.SUBJOBID IN (' + @SUBJOBIDS + ')'
    END;
    
    IF (@APPOINTMENTIDS IS NOT NULL AND LEN(@APPOINTMENTIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND L.SCHEDULEDETAIL IN (' + @APPOINTMENTIDS + ')'
	    ELSE
		    SET @WHERESTR = ' L.SCHEDULEDETAIL IN (' + @APPOINTMENTIDS + ')'
    END;
        
    IF (@MATERIALSIDS IS NOT NULL AND LEN(@MATERIALSIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
	        SET @WHERESTR = @WHERESTR + ' AND L.SEQNO IN (' + @MATERIALSIDS + ')'
	    ELSE
		    SET @WHERESTR = ' L.SEQNO IN (' + @MATERIALSIDS + ')';
    END;

    IF (@I_CREATESTARTDATE IS NOT NULL)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND L.TRANSDATE >= @STARTCREATEDATE '
	    ELSE
		    SET @WHERESTR = ' L.TRANSDATE >= @STARTCREATEDATE '
    END;
	
    IF (@I_CREATEENDDATE IS NOT NULL)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND L.TRANSDATE <= @ENDCREATEDATE '
	    ELSE
		    SET @WHERESTR = ' L.TRANSDATE <= @ENDCREATEDATE '
    END;
	
    IF LEN(@WHERESTR) > 0  
	    SET @WHERESTR = ' WHERE ' + @WHERESTR;

    -- there MUST be a sort order!!
    IF (@I_SORTORDER IS NULL OR LEN(@I_SORTORDER) = 0)
	       SET @I_SORTORDER = ' ORDER BY L.SEQNO ';
    ELSE IF LEN(@I_SORTORDER) > 0
	       SET @I_SORTORDER = ' ORDER BY ' + @I_SORTORDER;

    --- determine the total number of rows which meet the criteria             
    SET @SQL = 
	       ' SELECT @RESULT = COUNT(*) '+
	       ' FROM ENPRISE_EA_TEMPBATCHJOBLINESVIEW  L ' +            
	       @WHERESTR;
	       	
    SET @PARMDEFINITION = '@RESULT INT OUTPUT, @STARTCREATEDATE DATETIME, @ENDCREATEDATE DATETIME';

    EXEC SP_EXECUTESQL 
    @SQL  
    , @PARMDEFINITION
    , @RESULT = @O_TOTALRECORDS OUTPUT
    , @STARTCREATEDATE = @I_CREATESTARTDATE
    , @ENDCREATEDATE = @I_CREATEENDDATE;

    ---- return the desired result set
    SET @SQL = 
           ' SELECT TOP (' + CAST(@I_PAGESIZE AS NVARCHAR) + ') A.* FROM ' +
           ' ( ' +
           '	SELECT ' + @COLUMNS + ', ROW_NUMBER() OVER ( ' + @I_SORTORDER + ' )  AS ROWNUM ' +
           '	FROM ENPRISE_EA_TEMPBATCHJOBLINESVIEW L ' + 
                @WHERESTR +
           ' ) AS A ' +
           ' WHERE ROWNUM > ' + CAST(@I_PAGEINDEX * @I_PAGESIZE AS NVARCHAR) + ';';
    
    --SELECT @WHERESTR + @I_SORTORDER;
    
    --SELECT @SQL;
    
    SET @PARMDEFINITION = '@STARTCREATEDATE DATETIME, @ENDCREATEDATE DATETIME';
    
    EXEC SP_EXECUTESQL @SQL
    , @PARMDEFINITION 
    , @STARTCREATEDATE = @I_CREATESTARTDATE
    , @ENDCREATEDATE = @I_CREATEENDDATE;
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHTIMETYPECODES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHTIMETYPECODES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHTIMETYPECODES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHTIMETYPECODES]
    @SEARCH	NVARCHAR(100),
    @MAXRECORDS	INT
AS
BEGIN
    SELECT TOP(@MAXRECORDS) A.SEQNO AS ID, 
    A.DESCRIPTION AS TITLE
    FROM ENPRISE_JOBCOST_TIMETYPE A
    WHERE (@SEARCH IS NULL OR A.DESCRIPTION LIKE '%' + @SEARCH + '%');
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SUBJOBS_GETTABLE]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SUBJOBS_GETTABLE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SUBJOBS_GETTABLE]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SUBJOBS_GETTABLE]
    @CUSTOMERIDS VARCHAR(500),
    @JOBIDS VARCHAR(500),
    @SUBJOBIDS VARCHAR(500),
    @SUBJOBSTATUSES VARCHAR(500),
    @P_VIEWNAME VARCHAR(20),
    @VIEWING_PLATFORM INT,
    @FORMSTYLE INT,    
    @P_USERID INT,
    @I_PAGEINDEX INT,
    @I_PAGESIZE INT,
    @I_SORTORDER VARCHAR(200),
    @I_STARTDATE DATETIME,
    @I_ENDDATE DATETIME,   
    @O_TOTALRECORDS INT OUTPUT
AS
BEGIN
    DECLARE @DEFAULTCOLUMNS NVARCHAR(MAX);
    DECLARE @USERCOLUMNS NVARCHAR(MAX);
    DECLARE @COLUMNS NVARCHAR(MAX);
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @WHERESTR NVARCHAR(MAX)
    DECLARE @PARMDEFINITION NVARCHAR(500);
    
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
	
    SET @O_TOTALRECORDS = 0;
	
    --SET @DEFAULTCOLUMNS = 
    --	'SUBJOBID,JOBID,CARDCODE,CARDNAME,ACTIVE,BRANCH,COMPLETEDDATE,CONTACTCODE,DELIVERYADDR1,DELIVERYADDR2,DELIVERYADDR3,DELIVERYADDR4,DELIVERYADDR5,DELIVERYADDR6,' +
    --	'DESCRIPTION,ENTERBY,ENTEREDDATE,INTERNALINSTRUCTIONS,JOBTITLE,ALTCODE,JOBTYPE,ORDERNO,QUOTEDATE,STARTDATE,STATUS,CATEGORY,DUEDATE,DELIVERYADDRNAME,' +
    --	'SERVICECALLID,TAXCODE,MSPTASKID,QUOTEFXRATE,QUOTECURRCODE,QUOTECURRTYPE ' +
    --	 DBO.ENPRISE_GETTABLEUDFCOLUMNS('ENPRISE_JOBCOST_SUBJOB', '');
	
    SET @DEFAULTCOLUMNS = 
	    'SUBJOBID,JOBID,CARDCODE,CARDNAME,JOBTITLE,STATUSDESC,TYPEDESC,CATEGORYDESC';		
   	
    -- get the user column preferences
    SET @USERCOLUMNS = ''; 

    SELECT @USERCOLUMNS = 
    CASE LEN(@USERCOLUMNS)
	    WHEN 0 THEN @USERCOLUMNS + COLID 
	    ELSE @USERCOLUMNS + ',' + COLID 
    END    
    FROM ENPRISE_SETUP_FORM
    WHERE USERSIGN = @P_USERID AND FORMID = @P_VIEWNAME AND ITEMID = @P_VIEWNAME
    AND ENABLED = 'Y' AND VISINFORM = 'Y'
    ORDER BY VISUALINDEX;
    
    IF LEN(@USERCOLUMNS) > 0
	    SET @COLUMNS = @USERCOLUMNS
    ELSE
	    SET @COLUMNS = @DEFAULTCOLUMNS;
    
    -- both the subjob and job must be active and have editable statuses
    SET @WHERESTR = ' (ACTIVE = ''Y'') AND (STATUSEDITABLE = ''Y'') AND (JOBACTIVE = ''Y'') AND (JOBSTATUSEDITABLE = ''Y'') ';
	
    IF (@CUSTOMERIDS IS NOT NULL AND LEN(@CUSTOMERIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND CARDCODE IN (' + DBO.ENPRISE_GET_APOSTROPHE_STR(@CUSTOMERIDS) + ')'
	    ELSE
		    SET @WHERESTR = ' CARDCODE IN (' + DBO.ENPRISE_GET_APOSTROPHE_STR(@CUSTOMERIDS) + ')'					
    END;
	
    IF (@JOBIDS IS NOT NULL AND LEN(@JOBIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND JOBID IN (' + @JOBIDS + ')'
	    ELSE
		    SET @WHERESTR = ' JOBID IN (' + @JOBIDS + ')';
    END;
	
    IF (@SUBJOBIDS IS NOT NULL AND LEN(@SUBJOBIDS) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND SUBJOBID IN (' + @SUBJOBIDS + ')'
	    ELSE
		    SET @WHERESTR = ' SUBJOBID IN (' + @SUBJOBIDS + ')'
    END;
	
    IF (@SUBJOBSTATUSES IS NOT NULL AND LEN(@SUBJOBSTATUSES) > 0)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND STATUS IN (' + @SUBJOBSTATUSES + ')'
	    ELSE
		    SET @WHERESTR = ' STATUS IN (' + @SUBJOBSTATUSES + ')';
    END;
	
    IF (@I_STARTDATE IS NOT NULL)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND STARTDATE >= @STARTDATE '
	    ELSE
		    SET @WHERESTR = ' STARTDATE >= @STARTDATE '
    END;
	
    IF (@I_ENDDATE IS NOT NULL)
    BEGIN
	    IF LEN(@WHERESTR) > 0
		    SET @WHERESTR = @WHERESTR + ' AND COMPLETEDDATE <= @ENDDATE '
	    ELSE
		    SET @WHERESTR = ' COMPLETEDDATE <= @ENDDATE '
    END;
	
    IF LEN(@WHERESTR) > 0	
	    SET @WHERESTR = ' WHERE ' + @WHERESTR;
	
    -- there MUST be a sort order!!
    IF (@I_SORTORDER IS NULL OR LEN(@I_SORTORDER) = 0)
	    SET @I_SORTORDER = ' ORDER BY SUBJOBID ';
    ELSE IF LEN(@I_SORTORDER) > 0
	    SET @I_SORTORDER = ' ORDER BY ' + @I_SORTORDER;

    --- determine the total number of rows which meet the criteria		
    SET @SQL = 
	    ' SELECT @RESULT = COUNT(*) '+
	    ' FROM ENPRISE_EA_SUBJOBVIEW ' +
	    @WHERESTR;
		
    SET @PARMDEFINITION = '@RESULT INT OUTPUT, @STARTDATE DATETIME, @ENDDATE DATETIME';	
	
    EXEC SP_EXECUTESQL @SQL  
      , @PARMDEFINITION
      , @RESULT = @O_TOTALRECORDS OUTPUT
      , @STARTDATE = @I_STARTDATE
      , @ENDDATE = @I_ENDDATE;

    ---- return the desired result set
    SET @SQL = 
	    ' SELECT TOP (' + CAST(@I_PAGESIZE AS NVARCHAR) + ') A.* FROM ' +
	    ' ( ' +
	    '	SELECT ' + @COLUMNS + ', ROW_NUMBER() OVER ( ' + @I_SORTORDER + ' )  AS ROWNUM ' +
	    '	FROM ENPRISE_EA_SUBJOBVIEW ' +
		    @WHERESTR +
	    ' ) AS A ' +
	    ' WHERE ROWNUM > ' + CAST(@I_PAGEINDEX * @I_PAGESIZE AS NVARCHAR) + ';';
	
    --SELECT @WHERESTR + @I_SORTORDER;
	
    --SELECT @SQL;		
	
    SET @PARMDEFINITION = '@STARTDATE DATETIME, @ENDDATE DATETIME';	
	
    EXEC SP_EXECUTESQL @SQL
      , @PARMDEFINITION
      , @STARTDATE = @I_STARTDATE
      , @ENDDATE = @I_ENDDATE;
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHWAREHOUSES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHWAREHOUSES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHWAREHOUSES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHWAREHOUSES]
    @SEARCH	NVARCHAR(100),
    @MAXRECORDS	INT
AS
BEGIN
    SELECT TOP(@MAXRECORDS) WHSCODE AS ID, WHSNAME AS TITLE
    FROM OWHS
    WHERE @SEARCH IS NULL OR WHSCODE LIKE '%' + @SEARCH + '%' 
    OR WHSNAME LIKE '%' + @SEARCH + '%';
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHTAXCODES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHTIMESHEETITEMCODES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHTIMESHEETITEMCODES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHTIMESHEETITEMCODES]
      @SEARCH     NVARCHAR(100),
      @MAXRECORDS INT,
      @SUBJOB_ID INT = NULL
AS
BEGIN
	SELECT TOP(@MAXRECORDS) A.ITEMCODE AS ID, 
	ITEMNAME AS TITLE
	FROM OITM A
	LEFT JOIN ENPRISE_JOBCOST_SUBJOB S ON S.SUBJOBID = @SUBJOB_ID
	LEFT JOIN ENPRISE_JOBCOST_JOBTYPE T ON T.SEQNO = S.JOBTYPE
	LEFT JOIN ENPRISE_JOBCOST_JOBLINES L ON L.SUBJOBID = S.SUBJOBID AND L.LINETYPE = 'Q' AND L.STOCKCODE = A.ITEMCODE
    WHERE (@SEARCH IS NULL OR @SEARCH = '*' OR A.ITEMCODE LIKE '%' + @SEARCH + '%' 
    OR A.ITEMNAME LIKE '%' + @SEARCH + '%')
	AND (A.INVNTITEM = 'N') AND (A.SELLITEM = 'Y' OR A.PRCHSEITEM = 'Y')
	AND (A.FROZENFOR = 'N') 
	AND (@SUBJOB_ID IS NULL OR T.RESTRICTTIMETOQUOTE = 'N' OR L.STOCKCODE IS NOT NULL)
	ORDER BY A.ITEMCODE
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHSUPPLIERCARDS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHSUPPLIERCARDS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHSUPPLIERCARDS]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHSUPPLIERCARDS]
    @SEARCH NVARCHAR(100),
    @MAXRECORDS INT
AS
BEGIN
    SELECT TOP(@MAXRECORDS) A.CARDCODE AS ID, A.CARDNAME AS TITLE
    FROM OCRD A
    WHERE A.CARDTYPE = 'S' AND
    (@SEARCH IS NULL) OR 
    (A.CARDCODE LIKE '%' + @SEARCH + '%') OR
    (A.CARDNAME LIKE '%' + @SEARCH + '%')
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHSUBJOBCATEGORIES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHSUBJOBCATEGORIES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHSUBJOBCATEGORIES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHSUBJOBCATEGORIES]
      @SEARCH     NVARCHAR(100),
      @MAXRECORDS INT
AS
BEGIN
      SELECT TOP(@MAXRECORDS) A.SEQNO AS ID, A.CATEGORY AS TITLE
      FROM ENPRISE_JOBCOST_CATEGORY A
      WHERE A.ACTIVE = 'Y' AND (@SEARCH IS NULL OR A.CATEGORY LIKE '%' + @SEARCH + '%')
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHREFERENCEUSERS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHREFERENCEUSERS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHREFERENCEUSERS]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHREFERENCEUSERS]
    @SEARCH NVARCHAR(100),
    @MAXRECORDS INT
AS
BEGIN
    SELECT TOP(@MAXRECORDS) A.EMPID AS ID, 
    B.USER_CODE AS USERNAME, 
    A.EMAIL,
    B.U_NAME AS DISPLAY_NAME
    FROM OHEM A
    LEFT JOIN OUSR B ON B.USERID = A.USERID
    WHERE @SEARCH IS NULL OR
    A.FIRSTNAME LIKE '%' + @SEARCH + '%' OR 
    A.LASTNAME LIKE '%' + @SEARCH + '%' OR
    B.U_NAME LIKE '%' + @SEARCH + '%'
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHPROJECTMANAGERS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHPROJECTMANAGERS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHPROJECTMANAGERS]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHPROJECTMANAGERS]
    @SEARCH	NVARCHAR(100),
    @MAXRECORDS	INT
AS
BEGIN
    SELECT TOP(@MAXRECORDS) A.EMPID AS ID, A.FIRSTNAME + ' ' + A.LASTNAME AS TITLE 
    FROM OHEM A
    WHERE @SEARCH IS NULL OR A.FIRSTNAME LIKE '%' + @SEARCH + '%' OR A.LASTNAME LIKE '%' + @SEARCH + '%'
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SAVEUSER]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SAVEUSER]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SAVEUSER]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SAVEUSER]
    @USERID	INT,
    @USERNAME NVARCHAR(50),
    @PASSWORD NVARCHAR(50),
    @STATUS BIT,	
    @DISPLAY_NAME NVARCHAR(50),
    @DATE_CREATED DATETIME,
    @DATE_LAST_LOGIN DATETIME,
    @ADMINISTRATOR BIT,
    @SYSADMIN BIT,	
    @USER_GROUP_ID INT,
    @REFERENCE_USER	INT,
    @CREATED_BY_USER_ID	INT,
    @DEFAULT_VIEW VARCHAR(20),
    @NON_BILLABLE_ENTRY	INT,
    @SIGNED_BY_REQUIRED	BIT,
    @SIGNATURE_ON_CLOSE_REQUIRED BIT,
    @AUTO_POPULATE_ITEM_DESC_ENTRY INT,	
    @LINKED_SUPPLIER_CARD_CODE	NVARCHAR(15),
    @REIMBURSABLE_EXPENSE_PROCESS_TYPE INT,
    @CAN_APPROVE_TIMESHEET_ENTRY INT,
    @ACTIVITIES_REQUIRED_ON_TIMESHEET BIT,
    @LICENSE_TYPE_ID INT,
    @SUCCESS BIT OUTPUT,
    @ID	INT OUTPUT,
    @ERROR_NUMBER INT OUTPUT,
    @ERROR_MESSAGE	NVARCHAR(100) OUTPUT
AS
BEGIN
    DECLARE @AVAIL_LIC INT = 0;
    DECLARE @IS_CURRENT_LIC_USER VARCHAR(1) = 'N';
    DECLARE @SAPUSER NVARCHAR(100);
    DECLARE @NEWSAPUSER VARCHAR(100);
	
    -- set default values for output parameters
    SET @ERROR_NUMBER = 0;
    SET @ERROR_MESSAGE = NULL;
	
	
    IF (@LICENSE_TYPE_ID IS NOT NULL) AND NOT EXISTS(SELECT * FROM ENPRISE_SECURITY WHERE APPLICATION = @LICENSE_TYPE_ID)
    BEGIN
	    SET @SUCCESS = 0;
	    SET @ERROR_NUMBER = 5;
	    SET @ERROR_MESSAGE = 'LICENSE DOES NOT EXIST FOR LICENSE TYPE ID';
	    RETURN;
    END;
	
    SELECT @AVAIL_LIC = (A.USERS - B.ALLOCATEDUSERS)
    FROM ENPRISE_SECURITY A
    LEFT JOIN
    (
	    SELECT APPID, COUNT(*) AS ALLOCATEDUSERS
	    FROM ENPRISE_ADDON_USERS
	    GROUP BY APPID
    )B ON B.APPID = A.APPLICATION
    WHERE APPLICATION = @LICENSE_TYPE_ID;

	IF (@REFERENCE_USER IS NULL) AND (@USERNAME != 'ADMIN')
	BEGIN
		-- the SAP Employee ID must be set for everyone except the Admin user
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 9;
		SET @ERROR_MESSAGE = 'REFERENCE USER IS NOT SET';
		RETURN;			
	END;
	
    IF (@REFERENCE_USER IS NOT NULL) AND NOT EXISTS(SELECT * FROM OHEM WHERE EMPID = @REFERENCE_USER)
    BEGIN
	    -- the SAP Employee does not exist
	    SET @SUCCESS = 0;
	    SET @ERROR_NUMBER = 8;
	    SET @ERROR_MESSAGE = 'REFERENCE USER IS NOT LINKED TO A VALID SAP EMPLOYEE';
	    RETURN;			
    END;
	
    IF (@USERID IS NULL)
    BEGIN
	    IF (@LICENSE_TYPE_ID NOT IN (640, 650))
	    BEGIN			
		    SET @SUCCESS = 0;
		    SET @ERROR_NUMBER = 4;
		    SET @ERROR_MESSAGE = 'INVALID LICENSE TYPE ID - MUST BE EITHER 640 OR 650';
		    RETURN;
	    END;
		
	    IF EXISTS (SELECT * FROM ENPRISE_TSM_USERS WHERE REFERENCEID = @REFERENCE_USER)
	    BEGIN					
		    -- another user is using the reference id
		    SET @SUCCESS = 0;
		    SET @ERROR_NUMBER = 2;
		    SET @ERROR_MESSAGE = 'REFERENCE USER IS ALREADY USED BY ANOTHER USER';
		    RETURN;
	    END;				
		
	    IF (@PASSWORD IS NULL OR LEN(@PASSWORD) = 0)
	    BEGIN
		    -- a password must be entered for a new user
		    SET @SUCCESS = 0;
		    SET @ERROR_NUMBER = 3;
		    SET @ERROR_MESSAGE = 'INVALID PASSWORD - A PASSWORD MUST BE ENTERED';
		    RETURN;
	    END;
		
	    IF (@AVAIL_LIC <= 0)
	    BEGIN
		    -- all available licenses have been used
		    SET @SUCCESS = 0;
		    SET @ERROR_NUMBER = 6;
		    SET @ERROR_MESSAGE = 'LICENSE HAS BEEN FULLY ALLOCATED - NO MORE USERS CAN BE ADDED';
		    RETURN;
	    END;
		
	    INSERT INTO ENPRISE_TSM_USERS
	    (CUSTOMERID, USERNAME, NAME, PASSWORD, ENABLED, CREATEDATE, CREATEDBY, LASTLOGINDATE, ADMINISTRATOR, SYSADMIN,
	    USERGROUPID, REFERENCEID, DEFAULTVIEW , NONBILLABLEENTRY, SIGNEDBYMAND, SIGNATUREBYMAND,		
	    AUTOPOPULATEENTRY, EXPREIMBURSECARDCODE, EXPREIMBURSEOBJTYPE, APPROVETIMESHEETEXPENSE,
	    FORCEACTIVITIESTSENTRY)
	    VALUES(1, @USERNAME, @DISPLAY_NAME, @PASSWORD, @STATUS, @DATE_CREATED, @CREATED_BY_USER_ID, @DATE_LAST_LOGIN, @ADMINISTRATOR, @SYSADMIN, 
	    @USER_GROUP_ID, @REFERENCE_USER, @DEFAULT_VIEW,	@NON_BILLABLE_ENTRY,				
	    CASE @SIGNED_BY_REQUIRED	
		    WHEN 1 THEN 'Y'
		    ELSE 'N'
	    END,		
	    CASE @SIGNATURE_ON_CLOSE_REQUIRED 
		    WHEN 1 THEN 'Y'
		    ELSE 'N'
	    END,
	    @AUTO_POPULATE_ITEM_DESC_ENTRY,	
	    @LINKED_SUPPLIER_CARD_CODE,
	    @REIMBURSABLE_EXPENSE_PROCESS_TYPE,
	    @CAN_APPROVE_TIMESHEET_ENTRY, 		
	    CASE @ACTIVITIES_REQUIRED_ON_TIMESHEET 
		    WHEN 1 THEN 'Y'
		    ELSE 'N'
	    END);
		
	    SELECT @ID = IDENT_CURRENT('ENPRISE_TSM_USERS');
		
	    --determine sap user name. Need the call to cast otherwise collation problems occur
	    SELECT @SAPUSER = ISNULL(CAST(B.USER_CODE AS NVARCHAR(100)), CAST(A.USERNAME AS NVARCHAR(100))) 
	    FROM ENPRISE_TSM_USERS A	
	    LEFT JOIN OHEM O ON O.EMPID = A.REFERENCEID	
	    LEFT JOIN OUSR B ON B.USERID = O.USERID
	    WHERE A.USERID = @ID;
		
	    -- just in case, delete any existing licenses for the user
	    DELETE FROM ENPRISE_ADDON_USERS
	    WHERE APPID IN (640, 650) AND SAPUSER = @SAPUSER;	
		
	    INSERT INTO ENPRISE_ADDON_USERS
	    (APPID, SAPUSER, SYSTEMID, INSTALLATIONID, LOG_DATE)
	    SELECT TOP (1) @LICENSE_TYPE_ID, @SAPUSER, SYSTEMID, INSTALLATIONID, @DATE_CREATED
	    FROM ENPRISE_ADDON_USERS
	    WHERE APPID = 501 AND INSTALLATIONID IS NOT NULL;

    END	
    ELSE
    BEGIN	
	    IF EXISTS (SELECT * FROM ENPRISE_TSM_USERS WHERE REFERENCEID = @REFERENCE_USER AND USERID <> @USERID)
	    BEGIN					
		    -- another user is using the reference id
		    SET @SUCCESS = 0;
		    SET @ERROR_NUMBER = 2;
		    SET @ERROR_MESSAGE = 'REFERENCE USER IS ALREADY USED BY ANOTHER USER';			
		    RETURN;
	    END;		
		
	    --determine sap user name
	    SELECT @SAPUSER = ISNULL(CAST(B.USER_CODE AS NVARCHAR(100)), CAST(A.USERNAME AS NVARCHAR(100)))
	    FROM ENPRISE_TSM_USERS A	
	    LEFT JOIN OHEM O ON O.EMPID = A.REFERENCEID	
	    LEFT JOIN OUSR B ON B.USERID = O.USERID			
	    WHERE A.USERID = @USERID;			
		
	    IF (@LICENSE_TYPE_ID IS NOT NULL)
	    BEGIN
		    IF (@LICENSE_TYPE_ID NOT IN (640, 650))
		    BEGIN			
			    SET @SUCCESS = 0;
			    SET @ERROR_NUMBER = 4;
			    SET @ERROR_MESSAGE = 'INVALID LICENSE TYPE ID - MUST BE EITHER 640 OR 650';
			    RETURN;
		    END;	
				
		    -- determine if existing user	
		    IF EXISTS (
			    SELECT *			
			    FROM ENPRISE_ADDON_USERS A 
			    WHERE A.SAPUSER = @SAPUSER AND A.APPID = @LICENSE_TYPE_ID 
			    )
		    BEGIN
			    SET @IS_CURRENT_LIC_USER = 'Y';			
		    END;
			
		    IF (@IS_CURRENT_LIC_USER = 'N' AND @AVAIL_LIC <= 0)
		    BEGIN
			    -- all available licenses have been used
			    SET @SUCCESS = 0;
			    SET @ERROR_NUMBER = 7;
			    SET @ERROR_MESSAGE = 'LICENSE HAS BEEN FULLY ALLOCATED - NO MORE USERS CAN BE ADDED';
			    RETURN;
		    END;
	    END;
	
	    IF (@PASSWORD IS NULL)
	    BEGIN
		    -- update the user without updating the password
		    UPDATE ENPRISE_TSM_USERS
		    SET USERNAME = @USERNAME, NAME = @DISPLAY_NAME, LASTLOGINDATE = @DATE_LAST_LOGIN, ENABLED = @STATUS,
		    ADMINISTRATOR = @ADMINISTRATOR, SYSADMIN = @SYSADMIN,	 
		    USERGROUPID = @USER_GROUP_ID, REFERENCEID = @REFERENCE_USER, DEFAULTVIEW = @DEFAULT_VIEW,
		    NONBILLABLEENTRY = @NON_BILLABLE_ENTRY,		
		    SIGNEDBYMAND = 
		    CASE @SIGNED_BY_REQUIRED	
			    WHEN 1 THEN 'Y'
			    ELSE 'N'
		    END,
		    SIGNATUREBYMAND = 		
		    CASE @SIGNATURE_ON_CLOSE_REQUIRED 
			    WHEN 1 THEN 'Y'
			    ELSE 'N'
		    END,
		    AUTOPOPULATEENTRY = @AUTO_POPULATE_ITEM_DESC_ENTRY,	
		    EXPREIMBURSECARDCODE = @LINKED_SUPPLIER_CARD_CODE,
		    EXPREIMBURSEOBJTYPE = @REIMBURSABLE_EXPENSE_PROCESS_TYPE,
		    APPROVETIMESHEETEXPENSE = @CAN_APPROVE_TIMESHEET_ENTRY,
		    FORCEACTIVITIESTSENTRY = 
		    CASE @ACTIVITIES_REQUIRED_ON_TIMESHEET 
			    WHEN 1 THEN 'Y'
			    ELSE 'N'
		    END		
		    WHERE USERID = @USERID;
	    END
	    ELSE
	    BEGIN
		    IF (LEN(@PASSWORD) = 0)
		    BEGIN
			    -- a password cannot be an empty string
			    SET @SUCCESS = 0;
			    SET @ERROR_NUMBER = 3;
			    SET @ERROR_MESSAGE = 'INVALID PASSWORD - A PASSWORD MUST BE ENTERED';
			    RETURN;
		    END;

		    UPDATE ENPRISE_TSM_USERS
		    SET USERNAME = @USERNAME, NAME = @DISPLAY_NAME, LASTLOGINDATE = @DATE_LAST_LOGIN, ENABLED = @STATUS,
		    ADMINISTRATOR = @ADMINISTRATOR, SYSADMIN = @SYSADMIN,	 
		    USERGROUPID = @USER_GROUP_ID, REFERENCEID = @REFERENCE_USER, DEFAULTVIEW = @DEFAULT_VIEW,
		    NONBILLABLEENTRY = @NON_BILLABLE_ENTRY,		
		    SIGNEDBYMAND = 
		    CASE @SIGNED_BY_REQUIRED	
			    WHEN 1 THEN 'Y'
			    ELSE 'N'
		    END,
		    SIGNATUREBYMAND = 		
		    CASE @SIGNATURE_ON_CLOSE_REQUIRED 
			    WHEN 1 THEN 'Y'
			    ELSE 'N'
		    END,
		    AUTOPOPULATEENTRY = @AUTO_POPULATE_ITEM_DESC_ENTRY,	
		    EXPREIMBURSECARDCODE = @LINKED_SUPPLIER_CARD_CODE,
		    EXPREIMBURSEOBJTYPE = @REIMBURSABLE_EXPENSE_PROCESS_TYPE,
		    APPROVETIMESHEETEXPENSE = @CAN_APPROVE_TIMESHEET_ENTRY,
		    FORCEACTIVITIESTSENTRY = 
		    CASE @ACTIVITIES_REQUIRED_ON_TIMESHEET 
			    WHEN 1 THEN 'Y'
			    ELSE 'N'
		    END,
		    PASSWORD = @PASSWORD		
		    WHERE USERID = @USERID;		
	    END;
		
	    IF (@@ROWCOUNT = 0)
	    BEGIN
		    -- if no update occurred
		    SET @SUCCESS = 0;
		    SET @ERROR_NUMBER = 1;
		    SET @ERROR_MESSAGE = 'INVALID USER ID';
		    RETURN;
	    END;
		
	    SET @ID = @USERID;
				
	    --determine sap user name, it may have changed from the new data saved/updated
	    SELECT @NEWSAPUSER = ISNULL(CAST(B.USER_CODE AS NVARCHAR(100)), CAST(A.USERNAME AS NVARCHAR(100)))   
	    FROM ENPRISE_TSM_USERS A		
	    LEFT JOIN OHEM O ON O.EMPID = A.REFERENCEID	
	    LEFT JOIN OUSR B ON B.USERID = O.USERID		
	    WHERE A.USERID = @USERID;
		
	    IF (@LICENSE_TYPE_ID IS NULL)
	    BEGIN
		    -- delete all existing licenses for the user
		    DELETE FROM ENPRISE_ADDON_USERS
		    WHERE APPID IN (640, 650) AND SAPUSER = @SAPUSER; 
	    END			
	    ELSE IF (@IS_CURRENT_LIC_USER = 'N')
	    BEGIN
		    -- delete existing "other" license for the user. User can have 640 or 650 but not both!!
		    DELETE FROM ENPRISE_ADDON_USERS
		    WHERE APPID IN (640, 650) AND (APPID <> @LICENSE_TYPE_ID) AND SAPUSER = @SAPUSER; 
							
		    INSERT INTO ENPRISE_ADDON_USERS
		    (APPID, SAPUSER, SYSTEMID, INSTALLATIONID, LOG_DATE)
		    SELECT TOP (1) @LICENSE_TYPE_ID, @NEWSAPUSER, SYSTEMID, INSTALLATIONID, GETDATE()
		    FROM ENPRISE_ADDON_USERS
		    WHERE APPID = 501 AND INSTALLATIONID IS NOT NULL;
	    END
	    ELSE		
	    BEGIN
		    -- update the existing user only if the SAP user name has changed
		    UPDATE ENPRISE_ADDON_USERS
		    SET SAPUSER = @NEWSAPUSER
		    WHERE APPID = @LICENSE_TYPE_ID AND SAPUSER = @SAPUSER AND (@SAPUSER <> @NEWSAPUSER);
	    END;
    END;

    SET @SUCCESS = 1;
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHUSERGROUPS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHUSERGROUPS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHUSERGROUPS]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHUSERGROUPS]
    @SEARCH NVARCHAR(100),
    @MAXRECORDS INT
AS
BEGIN
    SELECT TOP (@MAXRECORDS) A.CUSTOMERGROUPID, A.NAME
    FROM ENPRISE_TSM_CUSTOMERGROUP A	
    WHERE @SEARCH IS NULL OR A.NAME LIKE '%' + @SEARCH + '%'			
END

GO


/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_VALIDATEUSER]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_VALIDATEUSER]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_VALIDATEUSER]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_VALIDATEUSER]
    @USERNAME NVARCHAR(100),
    @PASSWORD NVARCHAR(100)	
AS
BEGIN
    IF EXISTS(SELECT * FROM ENPRISE_TSM_USERS WHERE USERNAME = @USERNAME AND PASSWORD = @PASSWORD)
    BEGIN	    
	    SELECT 1 AS STATUS, '' AS MESSAGE, USERID		
	    FROM ENPRISE_TSM_USERS
	    WHERE USERNAME = @USERNAME AND PASSWORD = @PASSWORD
    END
    ELSE
    BEGIN
	    SELECT 0 AS STATUS, 'Sorry the username/password you entered are invalid' AS MESSAGE, NULL AS USERID
    END
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_USER_GETTEAMMEMBERS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_USER_GETTEAMMEMBERS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_USER_GETTEAMMEMBERS]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_USER_GETTEAMMEMBERS]
    @USERID	INT
AS
BEGIN
    SELECT A.USERID, 
    RTRIM(LTRIM(A.NAME)) AS DISPLAY_NAME
    FROM ENPRISE_TSM_USERS A
    JOIN ENPRISE_TSM_APPROVALGROUP B ON B.APPROVALGROUPID  = A.APPROVALGROUP AND B.ACTIVE = 'Y'
    JOIN ENPRISE_TSM_APPROVALGROUPITEM C ON C.GROUPID = B.APPROVALGROUPID
    JOIN ENPRISE_TSM_USERS D ON D.APPROVALGROUP = B.APPROVALGROUPID
    WHERE D.USERID = @USERID AND A.USERID != D.USERID
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETUSERS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETUSERS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETUSERS]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETUSERS]
       @USERID       INT,
       @USERNAME NVARCHAR(100) = NULL,
       @EMAIL NVARCHAR(100) = NULL
AS
BEGIN
       SELECT DISTINCT
       A.USERID, 
       RTRIM(LTRIM(A.USERNAME)) AS USERNAME,
       C.USERID AS SAPUSERID,
       B.EMPID AS REFERENCEID, 
       A.ENABLED AS STATUS,
       B.EMAIL,
       RTRIM(LTRIM(A.NAME)) AS DISPLAY_NAME,
       A.CREATEDATE AS DATE_CREATED,
       A.LASTLOGINDATE AS DATE_LAST_LOGIN,
       CASE          
                WHEN A.ADMINISTRATOR = 1 THEN 'ADMIN'
                WHEN A.SYSADMIN = 1 THEN 'SYSADMIN'
                ELSE 'USER'
       END AS ROLE,
       A.USERGROUPID,
       A.CREATEDBY AS CREATED_BY_USER_ID,
       CASE
              WHEN A.DEFAULTVIEW IS NULL OR LEN(A.DEFAULTVIEW) = 0 THEN '1020' -- CALENDAR
              ELSE A.DEFAULTVIEW
       END AS DEFAULT_VIEW,
       A.NONBILLABLEENTRY,
       A.SIGNEDBYMAND AS SIGNED_BY_REQUIRED,
       A.SIGNATUREBYMAND AS SIGNATURE_ON_CLOSE_REQUIRED,
       A.AUTOPOPULATEENTRY AS AUTO_POPULATE_ITEM_DESC_ENTRY,
       A.EXPREIMBURSECARDCODE  AS LINKED_SUPPLIER_CARD_CODE,
       A.EXPREIMBURSEOBJTYPE AS REIMBURSABLE_EXPENSE_PROCESS_TYPE,
       A.APPROVETIMESHEETEXPENSE AS CAN_APPROVE_TIMESHEET_ENTRY,
       A.FORCEACTIVITIESTSENTRY AS ACTIVITIES_REQUIRED_ON_TIMESHEET,
       D.DATEFORMAT AS DATE_FORMAT, 
       D.DATESEP AS DATE_SEPARATOR,
       D.CHARMONTH AS DATE_MONTH_LENGTH,
       D.TIMEFORMAT AS TIME_FORMAT,
       C.USER_CODE AS SAP_USERNAME, --ADDED 
       E.APPID AS LICENSE_TYPE_ID,
       D.MAINCURNCY AS DEFAULT_CURRENCY
       FROM ENPRISE_TSM_USERS A
       LEFT JOIN OHEM B ON B.EMPID = A.REFERENCEID     
       LEFT JOIN OUSR C ON C.USERID = B.USERID
       LEFT JOIN OADM D ON D.PRICEDEC ! = -999
       LEFT JOIN ENPRISE_ADDON_USERS E ON E.SAPUSER = ISNULL(C.USER_CODE, A.USERNAME) AND E.APPID IN (640, 650) 
       --LEFT JOIN ENPRISE_ADDON_USERS F ON F.SAPUSER = A.USERNAME collate SQL_Latin1_General_CP1_CI_AS AND F.APPID IN (640, 650)     
       WHERE (A.USERID = @USERID)
       OR (A.USERNAME = @USERNAME)
       OR (B.EMAIL = @EMAIL)
       OR (@USERID IS NULL AND @USERNAME IS NULL AND @EMAIL IS NULL)
       
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETUSERPERMISSIONS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETUSERPERMISSIONS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETUSERPERMISSIONS]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETUSERPERMISSIONS]
    @USERID INT
AS
BEGIN
    DECLARE @PERMISSION BIGINT;
    DECLARE @NOACCESS INT = 0
    DECLARE @READACCESS INT = 1;
    DECLARE @FULLACCESS INT = 2;
    DECLARE @APPID INT;
     
    SELECT @PERMISSION = ENTRYPERMISSION1 
    FROM ENPRISE_TSM_USERS 
    WHERE USERID = @USERID;
		
    SELECT TOP(1) @APPID = D.APPID
    FROM ENPRISE_TSM_USERS A
    LEFT JOIN OHEM B ON B.EMPID = A.REFERENCEID
    LEFT JOIN OUSR C ON C.USERID = B.USERID
    LEFT JOIN ENPRISE_ADDON_USERS D ON D.SAPUSER = C.USER_CODE	
    WHERE A.USERID = @USERID AND D.APPID IN (640, 650) 
    ORDER BY D.APPID DESC
	
    IF @APPID IS NULL
    BEGIN
	    SELECT TOP(1) @APPID = B.APPID
	    FROM ENPRISE_TSM_USERS A		
	    LEFT JOIN ENPRISE_ADDON_USERS B ON B.SAPUSER = A.USERNAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
	    WHERE A.USERID = @USERID AND B.APPID IN (640, 650) 
	    ORDER BY B.APPID DESC
    END;
	
    IF @APPID IS NULL
	    RETURN;
	
    SELECT * FROM
    (
	    SELECT 1 AS PERMISSION_ID, 
	    CASE (@PERMISSION & 1)
		    WHEN 1 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & 2)
				    WHEN 2 THEN @READACCESS
				    ELSE 
					    CASE (@PERMISSION & 4)
						    WHEN 4 THEN @FULLACCESS
						    ELSE @NOACCESS
					    END
			    END
	    END AS LEVEL,
	    'Time Sheet Entry' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 2 AS PERMISSION_ID, 
	    CASE (@PERMISSION & 8)
		    WHEN 8 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & 16)
				    WHEN 16 THEN @READACCESS
				    ELSE 
					    CASE (@PERMISSION & 32)
						    WHEN 32 THEN @FULLACCESS
						    ELSE @NOACCESS
				    END
			    END
	    END AS LEVEL,
	    'Time Sheet Entry Other' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 3 AS PERMISSION_ID, 
	    CASE (@PERMISSION & 64)
		    WHEN 64 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & 128)
				    WHEN 128 THEN @READACCESS
				    ELSE 
					    CASE (@PERMISSION & 256)
						    WHEN 256 THEN @FULLACCESS
						    ELSE @NOACCESS
				    END
			    END
	    END AS LEVEL,
	    'Expense Entry' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 4 AS PERMISSION_ID, 
	    CASE (@PERMISSION & 512)
		    WHEN 512 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & 1024)
				    WHEN 1024 THEN @READACCESS
				    ELSE 
					    CASE (@PERMISSION & 2048)
						    WHEN 2048 THEN @FULLACCESS
						    ELSE @NOACCESS
				    END
			    END
	    END AS LEVEL,
	    'Expense Entry Other' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 5 AS PERMISSION_ID, 
	    CASE (@PERMISSION & 4096)
		    WHEN 4096 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & 8192)
				    WHEN 8192 THEN @READACCESS
				    ELSE 
					    CASE (@PERMISSION & 16384)
						    WHEN 16384 THEN @FULLACCESS
						    ELSE @NOACCESS
				    END
			    END
	    END AS LEVEL,
	    'Attachments Entry' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 6 AS PERMISSION_ID, 
	    CASE (@PERMISSION & 32768)
		    WHEN 32768 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & 65536)
				    WHEN 65536 THEN @READACCESS
				    ELSE @NOACCESS									
			    END
	    END AS LEVEL,
	    'Activity Entry' AS TITLE,
	    1 AS READ_AVAILABLE,
	    0 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 7 AS PERMISSION_ID, 
	    CASE (@PERMISSION & 131072)
		    WHEN 131072 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & 262144)
				    WHEN 262144 THEN @READACCESS
				    ELSE @NOACCESS									
			    END
	    END AS LEVEL,
	    'Activity Entry Other' AS TITLE,
	    1 AS READ_AVAILABLE,
	    0 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 8 AS PERMISSION_ID, 
	    CASE (@PERMISSION & 524288)
		    WHEN 524288 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & 1048576)
				    WHEN 1048576 THEN @READACCESS
				    ELSE 
					    CASE (@PERMISSION & 2097152)
						    WHEN 2097152 THEN @FULLACCESS
						    ELSE @NOACCESS
				    END
			    END
	    END AS LEVEL,
	    'Material Entry' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 9 AS PERMISSION_ID, 
	    CASE (@PERMISSION & 4194304)
		    WHEN 4194304 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & 8388608)
				    WHEN 8388608 THEN @READACCESS
				    ELSE 
					    CASE (@PERMISSION & 16777216)
						    WHEN 16777216 THEN @FULLACCESS
						    ELSE @NOACCESS
				    END
			    END
	    END AS LEVEL,
	    'Material Entry Other' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 10 AS PERMISSION_ID, 
	    CASE (@PERMISSION & 33554432)
		    WHEN 33554432 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & 67108864)
				    WHEN 67108864 THEN @FULLACCESS
				    ELSE @NOACCESS									
			    END
	    END AS LEVEL,
	    'Material Receipts' AS TITLE,
	    0 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 11 AS PERMISSION_ID, 
	    CASE (@PERMISSION & 134217728)
		    WHEN 134217728 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & 268435456)
				    WHEN 268435456 THEN @READACCESS
				    ELSE 
					    CASE (@PERMISSION & 536870912)
						    WHEN 536870912 THEN @FULLACCESS
						    ELSE @NOACCESS
				    END
			    END
	    END AS LEVEL,
	    'Subjob Maintenance' AS TITLE,
	    1 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 12 AS PERMISSION_ID, 
	    CASE (@PERMISSION & 1073741824)
		    WHEN 1073741824 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & CAST(2147483648 AS BIGINT))
				    WHEN 2147483648 THEN @FULLACCESS
				    ELSE @NOACCESS												
			    END
	    END AS LEVEL,
	    'Appointment Entry Other' AS TITLE,
	    0 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE
		
	    UNION
		
	    SELECT 13 AS PERMISSION_ID, 
	    CASE (@PERMISSION & CAST(4294967296 AS BIGINT))
		    WHEN 4294967296 THEN @NOACCESS
		    ELSE
			    CASE (@PERMISSION & CAST(8589934592 AS BIGINT))
				    WHEN 8589934592 THEN @FULLACCESS
				    ELSE @NOACCESS											
			    END
	    END AS LEVEL,
	    'Appointment View Other' AS TITLE,
	    0 AS READ_AVAILABLE,
	    1 AS FULL_ACCESS_AVAILABLE	
		
	    --UNION
		
	    --SELECT 12 AS PERMISSION_ID, 
	    --CASE (@PERMISSION & 1073741824)
	    --	WHEN 1073741824 THEN @NOACCESS
	    --	ELSE
	    --		CASE (@PERMISSION & CAST(2147483648 AS BIGINT))
	    --			WHEN 2147483648 THEN @READACCESS
	    --			ELSE 
	    --				CASE (@PERMISSION & CAST(4294967296 AS BIGINT))
	    --					WHEN 4294967296 THEN @FULLACCESS
	    --					ELSE @NOACCESS
	    --			END
	    --		END
	    --END AS LEVEL,
	    --'SubJobScheduling' AS TITLE
		
	    --UNION
		
	    --SELECT 13 AS PERMISSION_ID, 
	    --CASE (@PERMISSION & CAST(8589934592 AS BIGINT))
	    --	WHEN 8589934592 THEN @NOACCESS
	    --	ELSE
	    --		CASE (@PERMISSION & CAST(17179869184 AS BIGINT))
	    --			WHEN 17179869184 THEN @READACCESS
	    --			ELSE 
	    --				CASE (@PERMISSION & CAST(34359738368 AS BIGINT))
	    --					WHEN 34359738368 THEN @FULLACCESS
	    --					ELSE @NOACCESS
	    --			END
	    --		END
	    --END AS LEVEL,
	    --'WorksheetEntry' AS TITLE	
    ) AS A
    WHERE @APPID <> 640 OR PERMISSION_ID NOT IN (6,7,8,9,10,11,12,13)
    -- if application is Anywhere Lite then no Activity, Material or Subjob Permissions
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_ADDUSERPERMISSION]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_ADDUSERPERMISSION]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_ADDUSERPERMISSION]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_ADDUSERPERMISSION]
    @USERID	INT,
    @PERMISSIONID INT,
    @LEVEL INT,
    @SUCCESS BIT OUTPUT,	
    @ERROR_NUMBER INT OUTPUT,
    @ERROR_MESSAGE	NVARCHAR(100) OUTPUT 
AS
BEGIN
    DECLARE @USERPERMISSION BIGINT;
    DECLARE @NOACCESS INT = 0
    DECLARE @READACCESS INT = 1;
    DECLARE @FULLACCESS INT = 2;
    DECLARE @PREV_LEVEL_PERMISSION BIGINT = 0;
    DECLARE @NEW_LEVEL_PERMISSION BIGINT = 0;
	
    -- set default values for output parameters
    SET @ERROR_NUMBER = 0;
    SET @ERROR_MESSAGE = NULL;	
	
    SELECT @USERPERMISSION = ENTRYPERMISSION1 
    FROM ENPRISE_TSM_USERS 
    WHERE USERID = @USERID;
	
    IF (@@ROWCOUNT = 0)
    BEGIN
	    SET @SUCCESS = 0;
	    SET @ERROR_NUMBER = 1;
	    SET @ERROR_MESSAGE = 'INVALID USER ID';
	    RETURN;
    END;
	
    SET @PREV_LEVEL_PERMISSION = 
    CASE @PERMISSIONID		
	    WHEN 1 THEN
				    CASE (@USERPERMISSION & 1)
					    WHEN 1 THEN 1
					    ELSE
						    CASE (@USERPERMISSION & 2)
							    WHEN 2 THEN 2
							    ELSE 
								    CASE (@USERPERMISSION & 4)
									    WHEN 4 THEN 4
									    ELSE 0
								    END
						    END
				    END
	    WHEN 2 THEN						
				    CASE (@USERPERMISSION & 8)
					    WHEN 8 THEN 8
					    ELSE
						    CASE (@USERPERMISSION & 16)
							    WHEN 16 THEN 16
							    ELSE 
								    CASE (@USERPERMISSION & 32)
									    WHEN 32 THEN 32
									    ELSE 0
								    END
						    END
				    END 
	    WHEN 3 THEN
				    CASE (@USERPERMISSION & 64)
					    WHEN 64 THEN 64
					    ELSE
						    CASE (@USERPERMISSION & 128)
							    WHEN 128 THEN 128
							    ELSE 
								    CASE (@USERPERMISSION & 256)
									    WHEN 256 THEN 256
									    ELSE 0
								    END
						    END
				    END
	    WHEN 4 THEN
				    CASE (@USERPERMISSION & 512)
					    WHEN 512 THEN 512
					    ELSE
						    CASE (@USERPERMISSION & 1024)
							    WHEN 1024 THEN 1024
							    ELSE 
								    CASE (@USERPERMISSION & 2048)
									    WHEN 2048 THEN 2048
									    ELSE 0
								    END
						    END
				    END 
	    WHEN 5 THEN
				    CASE (@USERPERMISSION & 4096)
					    WHEN 4096 THEN 4096
					    ELSE
						    CASE (@USERPERMISSION & 8192)
							    WHEN 8192 THEN 8192
							    ELSE 
								    CASE (@USERPERMISSION & 16384)
									    WHEN 16384 THEN 16384
									    ELSE 0
								    END
						    END
				    END 		
	    WHEN 6 THEN
				    CASE (@USERPERMISSION & 32768)
					    WHEN 32768 THEN 32768
					    ELSE
						    CASE (@USERPERMISSION & 65536)
							    WHEN 65536 THEN 65536
							    ELSE 0									
						    END
				    END		
	    WHEN 7 THEN
				    CASE (@USERPERMISSION & 131072)
					    WHEN 131072 THEN 131072
					    ELSE
						    CASE (@USERPERMISSION & 262144)
							    WHEN 262144 THEN 262144
							    ELSE 0									
						    END
				    END		
	    WHEN 8 THEN
				    CASE (@USERPERMISSION & 524288)
					    WHEN 524288 THEN 524288
					    ELSE
						    CASE (@USERPERMISSION & 1048576)
							    WHEN 1048576 THEN 1048576
							    ELSE 
								    CASE (@USERPERMISSION & 2097152)
									    WHEN 2097152 THEN 2097152
									    ELSE 0
								    END
						    END
				    END		
	    WHEN 9 THEN
				    CASE (@USERPERMISSION & 4194304)
					    WHEN 4194304 THEN 4194304
					    ELSE
						    CASE (@USERPERMISSION & 8388608)
							    WHEN 8388608 THEN 8388608
							    ELSE 
								    CASE (@USERPERMISSION & 16777216)
									    WHEN 16777216 THEN 16777216
									    ELSE 0
								    END
						    END
				    END		
	    WHEN 10 THEN
				    CASE (@USERPERMISSION & 33554432)
					    WHEN 33554432 THEN 33554432
					    ELSE
						    CASE (@USERPERMISSION & 67108864)
							    WHEN 67108864 THEN 67108864
							    ELSE 0									
						    END
				    END		
	    WHEN 11 THEN
				    CASE (@USERPERMISSION & 134217728)
					    WHEN 134217728 THEN 134217728
					    ELSE
						    CASE (@USERPERMISSION & 268435456)
							    WHEN 268435456 THEN 268435456
							    ELSE 
								    CASE (@USERPERMISSION & 536870912)
									    WHEN 536870912 THEN 536870912
									    ELSE 0
								    END
						    END
				    END
	    WHEN 12 THEN
				    CASE (@USERPERMISSION & 1073741824)
					    WHEN 1073741824 THEN 1073741824
					    ELSE 
						    CASE (@USERPERMISSION & CAST(2147483648 AS BIGINT))
							    WHEN 2147483648 THEN 2147483648
							    ELSE 0
						    END
				    END							
	    WHEN 13 THEN
				    CASE (@USERPERMISSION & CAST(4294967296 AS BIGINT))
					    WHEN 4294967296 THEN 4294967296
					    ELSE 
						    CASE (@USERPERMISSION & CAST(8589934592 AS BIGINT))
							    WHEN 8589934592 THEN 8589934592
							    ELSE 0
						    END
				    END					
			
    END;
	
    --remove then existing permission for the level;
    SET @USERPERMISSION = @USERPERMISSION - @PREV_LEVEL_PERMISSION;
	
    SET @NEW_LEVEL_PERMISSION = 
    CASE @PERMISSIONID		
	    WHEN 1 THEN
				    CASE @LEVEL
					    WHEN @NOACCESS THEN 1 
					    WHEN @READACCESS THEN 2
					    WHEN @FULLACCESS THEN 4
					    ELSE 1 -- default to no access						
				    END		
	    WHEN 2 THEN
				    CASE @LEVEL 
					    WHEN @NOACCESS THEN 8
					    WHEN @READACCESS THEN 16
					    WHEN @FULLACCESS THEN 32 
					    ELSE 8 -- default to no access						
				    END		
	    WHEN 3 THEN
				    CASE @LEVEL 
					    WHEN @NOACCESS THEN 64
					    WHEN @READACCESS THEN 128
					    WHEN @FULLACCESS THEN 256 
					    ELSE 64 -- default to no access						
				    END				
	    WHEN 4 THEN
				    CASE @LEVEL 
					    WHEN @NOACCESS THEN 512
					    WHEN @READACCESS THEN 1024
					    WHEN @FULLACCESS THEN 2048 
					    ELSE 512 -- default to no access						
				    END							
	    WHEN 5 THEN
				    CASE @LEVEL 
					    WHEN @NOACCESS THEN 4096
					    WHEN @READACCESS THEN 8192
					    WHEN @FULLACCESS THEN 16384 
					    ELSE 4096 -- default to no access						
				    END									
	    WHEN 6 THEN
				    CASE @LEVEL 
					    WHEN @NOACCESS THEN 32768
					    WHEN @READACCESS THEN 65536						
					    ELSE 32768 -- default to no access						
				    END									
	    WHEN 7 THEN
				    CASE @LEVEL 
					    WHEN @NOACCESS THEN 131072
					    WHEN @READACCESS THEN 262144						
					    ELSE 131072 -- default to no access						
				    END											
	    WHEN 8 THEN
				    CASE @LEVEL 
					    WHEN @NOACCESS THEN 524288
					    WHEN @READACCESS THEN 1048576
					    WHEN @FULLACCESS THEN 2097152 
					    ELSE 524288 -- default to no access						
				    END											
	    WHEN 9 THEN
				    CASE @LEVEL 
					    WHEN @NOACCESS THEN 4194304
					    WHEN @READACCESS THEN 8388608
					    WHEN @FULLACCESS THEN 16777216 
					    ELSE 4194304 -- default to no access						
				    END											
	    WHEN 10 THEN
				    CASE @LEVEL 
					    WHEN @NOACCESS THEN 33554432						
					    WHEN @FULLACCESS THEN 67108864 
					    ELSE 33554432 -- default to no access						
				    END													
	    WHEN 11 THEN
				    CASE @LEVEL 
					    WHEN @NOACCESS THEN 134217728
					    WHEN @READACCESS THEN 268435456						
					    WHEN @FULLACCESS THEN 536870912 
					    ELSE 134217728 -- default to no access						
				    END	
	    WHEN 12 THEN
				    CASE @LEVEL 
					    WHEN @NOACCESS THEN 1073741824						
					    WHEN @FULLACCESS THEN 2147483648 
					    ELSE 1073741824 -- default to no access						
				    END	
	    WHEN 13 THEN
				    CASE @LEVEL 
					    WHEN @NOACCESS THEN 4294967296						
					    WHEN @FULLACCESS THEN 8589934592 
					    ELSE 4294967296 -- default to no access						
				    END																																						

    END;

    -- add the new permission
    SET @USERPERMISSION = @USERPERMISSION | @NEW_LEVEL_PERMISSION;
			
    UPDATE ENPRISE_TSM_USERS 
    SET ENTRYPERMISSION1 = @USERPERMISSION
    WHERE USERID = @USERID;
	
    IF (@@ROWCOUNT = 0)
    BEGIN
	    -- if no update occurred
	    SET @SUCCESS = 0;
	    SET @ERROR_NUMBER = 1;
	    SET @ERROR_MESSAGE = 'INVALID USER ID';
	    RETURN;
    END;
		
    SET @SUCCESS = 1;
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_DELETEALLUSERPERMISSIONS]    Script Date: 06/02/2015 11:57:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_DELETEALLUSERPERMISSIONS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].SP_ENPRISE_EA_DELETEALLUSERPERMISSIONS
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_DELETEALLUSERPERMISSIONS]
	@USERID	INT,
	@SUCCESS BIT OUTPUT,	
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE	NVARCHAR(100) OUTPUT 
AS
BEGIN
	DECLARE @NOACCESS_TO_ANYTHING INT = 1 + 8 + 64 + 512 + 4096 + 32768 + 131072 + 524288 + 4194304 + 33554432 + 134217728;
	
	-- set default values for output parameters
	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;

	UPDATE ENPRISE_TSM_USERS
	SET ENTRYPERMISSION1 = @NOACCESS_TO_ANYTHING
	WHERE USERID = @USERID;
	
	IF (@@ROWCOUNT = 0)
	BEGIN
		-- if no update occurred
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 1;
		SET @ERROR_MESSAGE = 'INVALID USER ID';
		RETURN;
	END;
	
	SET @SUCCESS = 1;
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_DISPLAY_GETCUSTOM_FIELDS]    Script Date: 06/02/2015 11:57:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_DISPLAY_GETCUSTOM_FIELDS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].SP_ENPRISE_EA_DISPLAY_GETCUSTOM_FIELDS
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_DISPLAY_GETCUSTOM_FIELDS]
	@P_OBJTYPE INT,
	@VIEWING_PLATFORM INT,
	@FORMSTYLE INT, 
	@USERID INT
AS
BEGIN		
	SELECT A.FIELDNAME AS FIELD_ID, 
	A.DESCRIPTION AS FIELD_TITLE, 
	CASE B.DATA_TYPE
		WHEN 'NVARCHAR' THEN 1
		WHEN 'INT' THEN 2
		WHEN 'NUMERIC' THEN 4 --DOUBLE/FLOAT
		WHEN 'DATETIME' THEN 3
		WHEN 'NTEXT' THEN 5 --LINK
	END AS DATA_TYPE,
	A.FLOAT_TYPE AS DATA_SUBTYPE, 
	CASE A.FLOAT_TYPE
		WHEN 1 THEN C.RATEDEC
		WHEN 2 THEN C.SUMDEC
		WHEN 3 THEN C.PRICEDEC
		WHEN 4 THEN C.QTYDEC
		WHEN 5 THEN C.PERCENTDEC
		WHEN 6 THEN C.MEASUREDEC
		ELSE 0
	END AS DECIMALS_PLACES,
	A.DISPLAYTYPE AS UI_COMPONENT,  --EDITTEXT = 1, COMBOBOX = 2, BUTTON = 3
	A.ACTIVE,
	A.VISIBLE,	
	CASE B.IS_NULLABLE
		WHEN 'YES' THEN 'N'
		ELSE 'Y'
	END AS MANDATORY,
	A.ORDERNO,	
	B.COLUMN_DEFAULT,
	A.BUTTONTASK AS BUTTON_ACTION,
	CASE
		WHEN A.DISPLAYTYPE = 3 AND B.DATA_TYPE = 'NTEXT' THEN 4 -- Link
		ELSE A.BUTTONTYPE --Report = 1, Execution = 2, Website = 3
	END AS ACTION_TYPE,	
	DBO.ENPRISE_GETCUSTOM_FIELD_VALIDVALUES(A.TABLENAME, A.FIELDNAME) AS VALIDVALUES,
	D.SEQNO AS GROUP_ID,
	D.DESCRIPTION AS GROUP_TITLE	
	FROM ENPRISE_JOBCOST_USERFIELDSPOSITION A
	JOIN INFORMATION_SCHEMA.COLUMNS B ON B.TABLE_NAME = A.TABLENAME AND B.COLUMN_NAME = A.FIELDNAME
	JOIN OADM C ON C.PRICEDEC != -999
	LEFT JOIN ENPRISE_JOBCOST_USERFIELD_CATEGORY D ON D.SEQNO = A.CATEGORY
	WHERE A.TABLENAME = 
		CASE @P_OBJTYPE
		   WHEN 1001 THEN 'ENPRISE_JOBCOST_TEMPBATCHJOBLINES'
		   WHEN 1002 THEN 'ENPRISE_JOBCOST_TEMPBATCHJOBLINES'
		   WHEN 1004 THEN 'ENPRISE_JOBCOST_JOB'
		   WHEN 1005 THEN 'ENPRISE_JOBCOST_SUBJOB'
		   WHEN 1014 THEN 'ENPRISE_JOBCOST_TEMPBATCHJOBLINES'
		END
	ORDER BY A.SCREEN_POSITION, A.ORDERNO
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_DELETEUSER]    Script Date: 06/02/2015 11:57:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_DELETEUSER]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_DELETEUSER]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_DELETEUSER]
	@ID	INT,
	@SUCCESS BIT OUTPUT,
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE NVARCHAR(100) OUTPUT
AS
BEGIN
	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;

	DELETE FROM ENPRISE_TSM_USERS
	WHERE USERID = @ID;
	
	IF @@ROWCOUNT = 0
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 1;
		SET @ERROR_MESSAGE = 'USER ID NOT FOUND';
		RETURN;
	END
	
	SET @SUCCESS = 1;
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETAPPOINTMENT]    Script Date: 06/02/2015 11:57:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETAPPOINTMENT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETAPPOINTMENT]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETAPPOINTMENT]
       @ID    INT
AS
BEGIN
	DECLARE @APPOINTMENTS_ENTRY INT = 3400;

	SELECT A.SEQNO AS ID,                
	CASE
	   WHEN A.STARTTIME IS NULL THEN A.STARTDATE
	   ELSE DATEADD(MINUTE, ((A.STARTTIME/100) * 60) + (A.STARTTIME % 100), CAST(CAST(A.STARTDATE AS DATE) AS DATETIME))	   
	END AS START_DATE,
	CASE
	   WHEN A.ENDTIME IS NULL THEN A.ENDDATE
	   ELSE DATEADD(MINUTE, ((A.ENDTIME/100) * 60) + (A.ENDTIME % 100), CAST(CAST(A.ENDDATE AS DATE) AS DATETIME))	   
	END AS END_DATE,
	A.CREATEDATE AS DATE_CREATED, 
	A.DESCRIPTION AS SUBJECT,  
	C.USERID AS ASSIGNED_USER_ID,  
	C.USERNAME AS ASSIGNED_USER_DISPLAY_NAME,   
	A.ADDRESS1,
	A.ADDRESS2,  
	A.ADDRESS3,
	A.ADDRESS4,
	A.ADDRESS5,
	A.ADDRESS6,
	CAST(D.NOTE AS NVARCHAR(500)) AS DESCRIPTION,
	CAST(E.NOTE AS NVARCHAR(500)) AS INTERNAL_NOTES,
	F.SIGNATUREDATE AS SIGNATURE_DATE,
	F.SIGNEDBY AS SIGNATURE_SIGNED_BY_NAME,
	F.RESOLUTION AS JOB_RESOLUTION_NOTES
	FROM ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS A  
	LEFT JOIN ENPRISE_JOBCOST_JOBSCHEDULING B ON B.SEQNO = A.SCHEDULINGID 	
	LEFT JOIN ENPRISE_TSM_USERS C ON C.REFERENCEID = B.RESOURCEID 
	LEFT JOIN ENPRISE_JOBCOST_NOTES D ON D.SEQNO = A.DESCRIPTIONSEQNO
	LEFT JOIN ENPRISE_JOBCOST_NOTES E ON E.SEQNO = A.INTERNALNOTESEQNO
	LEFT JOIN ENPRISE_JOBCOST_SIGNOFF F ON F.OBJECTSEQNO = A.SEQNO AND OBJECTTYPE = @APPOINTMENTS_ENTRY
	WHERE A.SEQNO = @ID;       

	SELECT C.SUBJOBID AS SUBJOB_ID, 
	C.JOBTITLE AS TITLE
	FROM ENPRISE_JOBCOST_JOBSCHEDULING A 
	JOIN ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS B ON B.SCHEDULINGID = A.SEQNO
	JOIN ENPRISE_JOBCOST_SUBJOB C ON C.SUBJOBID = A.SUBJOBID
	WHERE B.SEQNO = @ID
	UNION  
	SELECT T.SUBJOBID AS SUBJOB_ID,
	S.JOBTITLE AS TITLE
	FROM ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS T  
	JOIN ENPRISE_JOBCOST_SUBJOB S ON S.SUBJOBID = T.SUBJOBID
	WHERE T.SCHEDULEDETAILSID = @ID;
                           
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETACTIVITY]    Script Date: 06/02/2015 11:57:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETACTIVITY]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETACTIVITY]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETACTIVITY]
	@ID	INT
AS
BEGIN
	SELECT A.CLGCODE AS ID,
	B.SUBJOBID AS SUB_JOB_ID,
	B.JOBTITLE AS SUB_JOB_TITLE,
	B.JOBID AS JOB_ID,
	A.CARDCODE AS CUSTOMER_ID,
	D.CARDNAME AS CUSTOMER_NAME,
	C.NAME AS SUBJECT,
	CASE
		WHEN A.ATTENDUSER IS NOT NULL THEN A.ATTENDUSER
		WHEN A.ATTENDEMPL IS NOT NULL THEN A.ATTENDEMPL
	END AS ASSIGNED_USER_ID,
	CASE
		WHEN A.ATTENDUSER IS NOT NULL THEN F.U_NAME
		WHEN A.ATTENDEMPL IS NOT NULL THEN E.FIRSTNAME + ' ' + E.LASTNAME
	END AS ASSIGNED_USER_DISPLAY_NAME,
	CASE 
		WHEN A.CLOSED = 'Y' THEN 'Closed'
		WHEN A.INACTIVE = 'Y' THEN 'Inactive'
		WHEN A.TENTATIVE = 'Y' THEN 'Tentative'
		ELSE 'Open'
	END AS STATUS_TEXT,	
	ISNULL(A.ROOM + ',', '') + ISNULL(A.STREET + ',', '') +  ISNULL(A.CITY + ',', '') + ISNULL(A.STATE + ',', '') + ISNULL(A.COUNTRY + ',', '') AS SITE_LOCATION_FULL_ADDRESS,
	A.DETAILS AS DESCRIPTION,
	A.NOTES AS INTERNAL_NOTES,
	A.RECONTACT AS START_DATE,
	A.ENDDATE AS END_DATE,
	A.CNTCTDATE AS DATE_CREATED
	FROM OCLG A
	LEFT JOIN ENPRISE_JOBCOST_SUBJOB B ON B.SUBJOBID = A.U_ENP_SUBJOBID 
	LEFT JOIN OCLS C ON C.CODE = A.CNTCTSBJCT
	LEFT JOIN OCRD D ON D.CARDCODE = A.CARDCODE
	LEFT JOIN OHEM E ON E.EMPID = A.ATTENDEMPL
	LEFT JOIN OUSR F ON F.USERID = A.ATTENDUSER
	WHERE A.CLGCODE = @ID;
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GENERALDROPDOWNSEARCH]    Script Date: 06/02/2015 11:57:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GENERALDROPDOWNSEARCH]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GENERALDROPDOWNSEARCH]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GENERALDROPDOWNSEARCH]
	@SEARCH	NVARCHAR(100),
	@MAXRECORDS	INT
AS
BEGIN
	DECLARE @ANYWHERE_OFFSET INT = 3000;
	
	DECLARE @JOB_ENTRY INT = 1004;
	DECLARE @SUBJOB_ENTRY INT = 1005;
	DECLARE @CUSTOMERS_ENTRY INT = 1010 + @ANYWHERE_OFFSET;
	DECLARE @TITLE_LENGTH INT = 100;
	
	SET @SEARCH = '%' + @SEARCH + '%';
	
	SELECT TOP(@MAXRECORDS) DATA.* FROM 
	(
		SELECT CARDCODE AS ID,
		@CUSTOMERS_ENTRY AS OBJTYPE,
		LEFT(CARDNAME, @TITLE_LENGTH) AS TITLE 
		FROM OCRD
		WHERE CARDCODE LIKE @SEARCH OR CARDNAME LIKE @SEARCH
		
		UNION ALL
		
		SELECT CAST(JOBID AS VARCHAR(20)) AS ID,
		@JOB_ENTRY AS OBJTYPE,
		LEFT(JOBTITLE, @TITLE_LENGTH) AS TITLE
		FROM ENPRISE_JOBCOST_JOB
		WHERE  CAST(JOBID AS VARCHAR(20)) LIKE @SEARCH OR JOBTITLE LIKE @SEARCH OR 
		DESCRIPTION LIKE @SEARCH OR INTERNALINSTRUCTIONS LIKE @SEARCH OR PROJECT LIKE @SEARCH OR
		DELIVERYADDRNAME LIKE @SEARCH OR
		DELIVERYADDR1 LIKE @SEARCH OR DELIVERYADDR2 LIKE @SEARCH OR DELIVERYADDR3 LIKE @SEARCH OR
		DELIVERYADDR4 LIKE @SEARCH OR DELIVERYADDR5 LIKE @SEARCH  OR DELIVERYADDR6 LIKE @SEARCH OR
		BILLADDRNAME LIKE @SEARCH OR 
		BILLADDR1 LIKE @SEARCH OR BILLADDR2 LIKE @SEARCH OR BILLADDR3 LIKE @SEARCH OR 
		BILLADDR4 LIKE @SEARCH OR BILLADDR5 LIKE @SEARCH OR BILLADDR6 LIKE @SEARCH 
		
		UNION ALL
		
		SELECT CAST(SUBJOBID AS VARCHAR(20)) AS ID,
		@SUBJOB_ENTRY AS OBJTYPE,
		LEFT(JOBTITLE, @TITLE_LENGTH) AS TITLE
		FROM ENPRISE_JOBCOST_SUBJOB
		WHERE CAST(SUBJOBID AS VARCHAR(20)) LIKE @SEARCH OR JOBTITLE LIKE @SEARCH OR 
		DESCRIPTION LIKE @SEARCH OR INTERNALINSTRUCTIONS LIKE @SEARCH OR
		DELIVERYADDRNAME LIKE @SEARCH OR
		DELIVERYADDR1 LIKE @SEARCH OR DELIVERYADDR2 LIKE @SEARCH OR DELIVERYADDR3 LIKE @SEARCH OR
		DELIVERYADDR4 LIKE @SEARCH OR DELIVERYADDR5 LIKE @SEARCH  OR DELIVERYADDR6 LIKE @SEARCH		
	) AS DATA
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETSUBJOB]    Script Date: 06/02/2015 11:57:31 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETSUBJOB]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETSUBJOB]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETSUBJOB]
	@ID	INT
AS
BEGIN
	SELECT A.SUBJOBID AS ID, 
	A.JOBID AS JOB_ID, 
	B.TYPE AS JOB_TYPE_TITLE,
	E.EA_STATUS AS JOB_STATUS,
	C.SUBJECT AS SERVICE_CALL_TITLE,
	A.JOBTITLE AS TITLE,
	A.ENTEREDDATE AS DATE_CREATED,
	A.CARDCODE AS CUSTOMER_ID,
	A.CARDNAME AS CUSTOMER_NAME,
	A.DELIVERYADDR1 AS SITE_LOCATION_ADDRESS1,
	A.DELIVERYADDR2 AS SITE_LOCATION_ADDRESS2,
	A.DELIVERYADDR3 AS SITE_LOCATION_ADDRESS3,
	A.DELIVERYADDR4 AS SITE_LOCATION_ADDRESS4,
	A.DELIVERYADDR5 AS SITE_LOCATION_ADDRESS5,
	A.DELIVERYADDR6 AS SITE_LOCATION_ADDRESS6, 
	A.STARTDATE AS START_DATE,
	A.DUEDATE  AS END_DATE,
	A.ORDERNO AS ORDER_NUMBER,
	A.ENTERBY AS PROJECT_MANAGER_USER_ID,
	A.CATEGORY AS CATEGORY_ID,
	A.DESCRIPTION AS DESCRIPTION,
	A.INTERNALINSTRUCTIONS AS INTERNAL_NOTES,
	D.SIGNATUREDATE AS SIGNATURE_DATE,
	D.SIGNEDBY AS SIGNATURE_SIGNED_BY_NAME,
	D.RESOLUTION AS JOB_RESOLUTION_NOTES
	FROM ENPRISE_JOBCOST_SUBJOB A
	LEFT JOIN ENPRISE_JOBCOST_JOBTYPE B ON B.SEQNO = A.JOBTYPE
	LEFT JOIN OSCL C ON C.CALLID = A.SERVICECALLID
	LEFT JOIN ENPRISE_JOBCOST_SIGNOFF D ON D.OBJECTSEQNO = A.SUBJOBID AND D.OBJECTTYPE = 1005
	LEFT JOIN ENPRISE_JOBCOST_STATUS E ON E.SEQNO = A.STATUS
	WHERE A.SUBJOBID = @ID
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SAVEAPPOINTMENT]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SAVEAPPOINTMENT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SAVEAPPOINTMENT]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SAVEAPPOINTMENT]
	@ID INT,
	@START_DATE  DATETIME,
	@END_DATE DATETIME,
	@SUBJECT NVARCHAR(200),
	@ASSIGNED_USER_ID INT,
	@ASSIGNED_USER_DISPLAY_NAME NVARCHAR(100),
	@SITE_LOCATION_STREET_ADDRESS NVARCHAR(200),
	@SITE_LOCATION_SUBURB NVARCHAR(200),
	@SITE_LOCATION_CITY  NVARCHAR(200),
	@SITE_LOCATION_STATE NVARCHAR(200),
	@SITE_LOCATION_POSTCODE NVARCHAR(200),
	@SITE_LOCATION_COUNTRY NVARCHAR(200),
	@DESCRIPTION NVARCHAR(500),
	@INTERNAL_NOTES NVARCHAR(500),
	@SIGNATURE_DATE DATETIME,
	@SIGNATURE_SIGNED_BY_NAME NVARCHAR(100),
	@JOB_RESOLUTION_NOTES NVARCHAR(500),
	@SUB_JOB_LIST DBO.SUBJOBLIST READONLY,
	@CHANGED_BY NVARCHAR(50),
	@SUCCESS BIT OUTPUT,
	@NEWID INT OUTPUT,
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE NVARCHAR(100) OUTPUT
AS
BEGIN
	DECLARE @APPOINTMENTS_ENTRY INT = 3400;
	DECLARE @DESCRIPTION_NOTE_SEQNO INT;
	DECLARE @INTERNAL_NOTE_SEQNO INT;
	DECLARE @SUBJOBID INT;
	DECLARE @JOBID INT;
	DECLARE @SCHEDULINGID INT;
	DECLARE @CHANGEDATE DATETIME = GETDATE();
	DECLARE	@STARTDATE DATE;
	DECLARE	@ENDDATE DATE;
	DECLARE	@STARTTIME INT;
	DECLARE	@ENDTIME INT;	
	--DECLARE	@REFERENCE_USER	INT;
	DECLARE @EMPID INT;	

	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;

	IF (@START_DATE > @END_DATE)
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 2;
		SET @ERROR_MESSAGE = 'START DATE MUST BE AFTER END DATE';
		RETURN;       
	END;

	IF (SELECT COUNT(*) FROM @SUB_JOB_LIST) = 0
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 3;
		SET @ERROR_MESSAGE = 'NO SUBJOBS SPECIFIED - THERE MUST BE AT LEAST ONE SUBJOB';
		RETURN;       
	END;

	IF (@CHANGED_BY IS NULL OR LEN(@CHANGED_BY) = 0)
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 4;
		SET @ERROR_MESSAGE = '@CHANGED_BY NOT SPECIFIED';
		RETURN;       
	END;

	--SEPARATE THE START TIME AND DATE TO MAINTAIN CONSISTENCY WITH INTERNAL SCHEDULING
	SET @STARTDATE = CAST(@START_DATE AS DATE);
	SET @ENDDATE = CAST(@END_DATE AS DATE);	 
            
	SET @STARTTIME = (DATEPART(HOUR, @START_DATE) * 100) + DATEPART(MINUTE, @START_DATE);
	SET @ENDTIME = (DATEPART(HOUR, @END_DATE) * 100) + DATEPART(MINUTE, @END_DATE);

	SELECT @EMPID = B.EMPID
	FROM ENPRISE_TSM_USERS A
	JOIN OHEM B ON B.EMPID = A.REFERENCEID
	WHERE A.USERID = @ASSIGNED_USER_ID        

	IF (@ID IS NULL)
	BEGIN         
		IF (@DESCRIPTION IS NOT NULL AND LEN(@DESCRIPTION) > 0)
		BEGIN
			 INSERT INTO ENPRISE_JOBCOST_NOTES
			 (NOTE)
			 VALUES(@DESCRIPTION);
		     
			 SET @DESCRIPTION_NOTE_SEQNO = IDENT_CURRENT('ENPRISE_JOBCOST_NOTES');                                                      
		END;          

		IF (@INTERNAL_NOTES IS NOT NULL AND LEN(@INTERNAL_NOTES) > 0)
		BEGIN
			 INSERT INTO ENPRISE_JOBCOST_NOTES
			 (NOTE)
			 VALUES(@INTERNAL_NOTES);
		     
			 SET @INTERNAL_NOTE_SEQNO = IDENT_CURRENT('ENPRISE_JOBCOST_NOTES');                                                  
		END;  
	      	      	     	      
		IF EXISTS (	SELECT *
					FROM ENPRISE_JOBCOST_JOBSCHEDULING A
					JOIN @SUB_JOB_LIST B ON B.SUBJOBID = A.SUBJOBID
					WHERE A.RESOURCETYPE = 'S' AND A.RESOURCEID = @EMPID 
					)
		BEGIN
			SELECT @SUBJOBID = MIN(SUBJOBID)
			FROM @SUB_JOB_LIST
			WHERE SUBJOBID IN 
			(	
				SELECT SUBJOBID 
				FROM ENPRISE_JOBCOST_JOBSCHEDULING 
				WHERE RESOURCETYPE = 'S' AND RESOURCEID = @EMPID
			)
								
			SELECT @SCHEDULINGID = SEQNO
			FROM ENPRISE_JOBCOST_JOBSCHEDULING
			WHERE RESOURCEID = @EMPID AND RESOURCETYPE = 'S' AND SUBJOBID = @SUBJOBID;				

		END
		ELSE
		BEGIN
			--get the lowest subjobid in the table
			SELECT @SUBJOBID = MIN(SUBJOBID)
			FROM @SUB_JOB_LIST;

			INSERT INTO ENPRISE_JOBCOST_JOBSCHEDULING
			(JOBID, SUBJOBID, RESOURCETYPE, RESOURCEID, CHANGEDBY, CHANGEDON)
			SELECT A.JOBID, A.SUBJOBID, 'S', @EMPID, @CHANGED_BY, @CHANGEDATE 
			FROM ENPRISE_JOBCOST_SUBJOB A
			WHERE A.SUBJOBID = @SUBJOBID;

			SET @SCHEDULINGID = IDENT_CURRENT('ENPRISE_JOBCOST_JOBSCHEDULING');			  
		END
	              
		INSERT INTO ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS
		(SCHEDULINGID, STARTDATE, ENDDATE, STARTTIME,ENDTIME,DESCRIPTION, DESCRIPTIONSEQNO, INTERNALNOTESEQNO,
		ADDRESS1, ADDRESS2, ADDRESS3,
		ADDRESS4, ADDRESS5, ADDRESS6,
		CHANGEDBY, CHANGEDON,CREATEDATE)
		VALUES(@SCHEDULINGID, @STARTDATE, @ENDDATE, @STARTTIME,@ENDTIME,@SUBJECT,  @DESCRIPTION_NOTE_SEQNO, @INTERNAL_NOTE_SEQNO,
		@SITE_LOCATION_STREET_ADDRESS, @SITE_LOCATION_SUBURB, @SITE_LOCATION_CITY,
		@SITE_LOCATION_STATE, @SITE_LOCATION_POSTCODE, @SITE_LOCATION_COUNTRY,
		@CHANGED_BY, @CHANGEDATE,GETDATE())

		SELECT @NEWID = IDENT_CURRENT('ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS');  		  
			        
		-- if multiple subjobs            
		IF (SELECT COUNT(*) FROM @SUB_JOB_LIST) > 1
		BEGIN
			INSERT INTO ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS
			(SCHEDULEDETAILSID, SUBJOBID)
			SELECT @NEWID, SUBJOBID
			FROM @SUB_JOB_LIST
			WHERE SUBJOBID <> @SUBJOBID;
		END;

		IF (@SIGNATURE_DATE  IS NOT NULL) OR(@SIGNATURE_SIGNED_BY_NAME IS NOT NULL AND LEN(@SIGNATURE_SIGNED_BY_NAME) > 0) OR
		(@JOB_RESOLUTION_NOTES IS NOT NULL AND LEN(@JOB_RESOLUTION_NOTES) > 0)
		BEGIN
			INSERT INTO ENPRISE_JOBCOST_SIGNOFF
			(OBJECTTYPE, OBJECTSEQNO, SIGNEDBY, SIGNATUREDATE, RESOLUTION)
			VALUES(@APPOINTMENTS_ENTRY, @NEWID, @SIGNATURE_SIGNED_BY_NAME, @SIGNATURE_DATE, @JOB_RESOLUTION_NOTES);
		END;   
	END
	ELSE
	BEGIN
		SELECT @DESCRIPTION_NOTE_SEQNO = DESCRIPTIONSEQNO, @INTERNAL_NOTE_SEQNO = INTERNALNOTESEQNO, @SCHEDULINGID = SCHEDULINGID      
		FROM ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS
		WHERE SEQNO = @ID;

		UPDATE ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS
		SET STARTDATE = @STARTDATE, ENDDATE = @ENDDATE, STARTTIME = @STARTTIME, ENDTIME = @ENDTIME, DESCRIPTION = @SUBJECT,
		ADDRESS1 = @SITE_LOCATION_STREET_ADDRESS, ADDRESS2 = @SITE_LOCATION_SUBURB, ADDRESS3 = @SITE_LOCATION_CITY,
		ADDRESS4 = @SITE_LOCATION_STATE, ADDRESS5 = @SITE_LOCATION_POSTCODE, ADDRESS6 = @SITE_LOCATION_COUNTRY,
		CHANGEDBY = @CHANGED_BY, CHANGEDON = @CHANGEDATE
		WHERE SEQNO = @ID;

		IF (@@ROWCOUNT = 0)
		BEGIN
			-- if no update occurred
			SET @SUCCESS = 0;
			SET @ERROR_NUMBER = 1;
			SET @ERROR_MESSAGE = 'INVALID APPOINTMENT ID ';
			RETURN;
		END;
	      
		--get the lowest subjobid in the table
		SELECT @SUBJOBID = MIN(SUBJOBID)
		FROM @SUB_JOB_LIST;

		IF (SELECT COUNT(*) FROM ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS WHERE SCHEDULINGID = @SCHEDULINGID) = 1
		BEGIN
			SELECT @JOBID = JOBID
			FROM ENPRISE_JOBCOST_SUBJOB
			WHERE SUBJOBID = @SUBJOBID;

			-- update the header if there is one appointment linked to the scheduling header
			UPDATE ENPRISE_JOBCOST_JOBSCHEDULING
			SET JOBID = @JOBID, SUBJOBID = @SUBJOBID, RESOURCETYPE = 'S', RESOURCEID = @EMPID,
			CHANGEDBY = @CHANGED_BY, CHANGEDON = @CHANGEDATE                     
			WHERE SEQNO = @SCHEDULINGID;
		                         
		END
		ELSE
		BEGIN
			--there are multiple appointments linked to the scheduling header
			IF (SELECT COUNT(*) FROM ENPRISE_JOBCOST_JOBSCHEDULING A JOIN @SUB_JOB_LIST B ON B.SUBJOBID = A.SUBJOBID WHERE A.SEQNO = @SCHEDULINGID AND RESOURCETYPE = 'S' AND RESOURCEID = @EMPID) = 0
			BEGIN
				-- create a new header when multiple appointments linked to the scheduling header
				-- and if the subjob is not in the subjoblist or the resource has changed
				INSERT INTO ENPRISE_JOBCOST_JOBSCHEDULING
				(JOBID, SUBJOBID, RESOURCETYPE, RESOURCEID, CHANGEDBY, CHANGEDON)
				SELECT A.JOBID, A.SUBJOBID, 'S', @EMPID, @CHANGED_BY, @CHANGEDATE  
				FROM ENPRISE_JOBCOST_SUBJOB A
				WHERE A.SUBJOBID = @SUBJOBID;

				SET @SCHEDULINGID = IDENT_CURRENT('ENPRISE_JOBCOST_JOBSCHEDULING');

				--update the appointment to link it to the new scheduling header
				UPDATE ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS
				SET SCHEDULINGID =   @SCHEDULINGID, CHANGEDBY = @CHANGED_BY, CHANGEDON = @CHANGEDATE
				WHERE SEQNO = @ID;   
			END
			ELSE
			BEGIN
				-- the scheduling header has the required subjobid, resource type and resource id
				-- so we don't need to update it.

				-- reset the subjobid variable to the one in the scheduling header
				SELECT @SUBJOBID = SUBJOBID
				FROM ENPRISE_JOBCOST_JOBSCHEDULING
				WHERE SEQNO = @SCHEDULINGID                     
			END;
		END;
	      
		DELETE FROM ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS
		WHERE SCHEDULEDETAILSID = @ID;

		-- if multiple subjobs            
		IF (SELECT COUNT(*) FROM @SUB_JOB_LIST) > 1
		BEGIN
			INSERT INTO ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS
			(SCHEDULEDETAILSID, SUBJOBID)
			SELECT @ID, SUBJOBID
			FROM @SUB_JOB_LIST
			WHERE SUBJOBID <> @SUBJOBID;
		END;
	      
		IF (@DESCRIPTION IS NOT NULL AND LEN(@DESCRIPTION) > 0)
		BEGIN
			IF @DESCRIPTION_NOTE_SEQNO IS NULL
			BEGIN
				INSERT INTO ENPRISE_JOBCOST_NOTES
				(NOTE)
				VALUES(@DESCRIPTION);

				SET @DESCRIPTION_NOTE_SEQNO = IDENT_CURRENT('ENPRISE_JOBCOST_NOTES');

				UPDATE ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS
				SET DESCRIPTIONSEQNO =  @DESCRIPTION_NOTE_SEQNO
				WHERE SEQNO = @ID;
			END
			ELSE
			BEGIN
				UPDATE ENPRISE_JOBCOST_NOTES
				SET NOTE = @DESCRIPTION
				WHERE SEQNO = @DESCRIPTION_NOTE_SEQNO
			END;
		END
		ELSE IF @DESCRIPTION_NOTE_SEQNO IS NOT NULL
		BEGIN
			-- there is no description, so delete the existing one if available
			UPDATE ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS
			SET DESCRIPTIONSEQNO =  NULL
			WHERE SEQNO = @ID;

			DELETE FROM ENPRISE_JOBCOST_NOTES
			WHERE SEQNO = @DESCRIPTION_NOTE_SEQNO;                                     
		END;
	      
		IF (@INTERNAL_NOTES IS NOT NULL AND LEN(@INTERNAL_NOTES) > 0)
		BEGIN
			IF @INTERNAL_NOTE_SEQNO IS NULL
			BEGIN
				INSERT INTO ENPRISE_JOBCOST_NOTES
				(NOTE)
				VALUES(@INTERNAL_NOTES);

				SET @INTERNAL_NOTE_SEQNO = IDENT_CURRENT('ENPRISE_JOBCOST_NOTES');

				UPDATE ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS
				SET INTERNALNOTESEQNO =  @INTERNAL_NOTE_SEQNO
				WHERE SEQNO = @ID;
			END
			ELSE
			BEGIN
				UPDATE ENPRISE_JOBCOST_NOTES
				SET NOTE = @INTERNAL_NOTES
				WHERE SEQNO = @INTERNAL_NOTE_SEQNO;
			END;
		END
		ELSE IF @INTERNAL_NOTE_SEQNO IS NOT NULL
		BEGIN
			-- there is no internal note, so delete the existing one if available
			UPDATE ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS
			SET INTERNALNOTESEQNO =  NULL
			WHERE SEQNO = @ID;

			DELETE FROM ENPRISE_JOBCOST_NOTES
			WHERE SEQNO = @INTERNAL_NOTE_SEQNO;                                        
		END;                       

		IF EXISTS(SELECT * FROM ENPRISE_JOBCOST_SIGNOFF WHERE OBJECTTYPE = @APPOINTMENTS_ENTRY AND OBJECTSEQNO = @ID)
		BEGIN
			UPDATE ENPRISE_JOBCOST_SIGNOFF
			SET SIGNEDBY = SIGNEDBY, SIGNATUREDATE = @SIGNATURE_DATE, RESOLUTION = @JOB_RESOLUTION_NOTES                    
			WHERE OBJECTTYPE = @APPOINTMENTS_ENTRY AND OBJECTSEQNO = @ID;
		END
		ELSE IF (@SIGNATURE_DATE IS NOT NULL) OR(@SIGNATURE_SIGNED_BY_NAME IS NOT NULL AND LEN(@SIGNATURE_SIGNED_BY_NAME) > 0) OR
		(@JOB_RESOLUTION_NOTES IS NOT NULL AND LEN(@JOB_RESOLUTION_NOTES) > 0)            
		BEGIN
			INSERT INTO ENPRISE_JOBCOST_SIGNOFF
			(OBJECTTYPE, OBJECTSEQNO, SIGNEDBY, SIGNATUREDATE, RESOLUTION)
			VALUES(@APPOINTMENTS_ENTRY, @ID, @SIGNATURE_SIGNED_BY_NAME, @SIGNATURE_DATE, @JOB_RESOLUTION_NOTES);
		END;                 
	END;
              
	SET @SUCCESS = 1;    
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHANALYSISCODES2]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHANALYSISCODES2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHANALYSISCODES2]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHANALYSISCODES2]
	@SEARCH	NVARCHAR(100),
	@MAXRECORDS	INT,
	@SUBJOBID INT, 
	@ANALYSIS1 INT
AS
BEGIN
    DECLARE @JOBID INT;
    
    SELECT @JOBID = JOBID
    FROM ENPRISE_JOBCOST_SUBJOB
    WHERE SUBJOBID = @SUBJOBID;
    
	SELECT TOP(@MAXRECORDS) A.SEQNO AS ID,
	A.DESCRIPTION AS TITLE
	FROM ENPRISE_JOBCOST_ANALYSIS2 A	
	WHERE (@SEARCH IS NULL OR A.DESCRIPTION LIKE '%' + @SEARCH + '%') AND
	(@JOBID IS NULL OR A.JOBID = @JOBID OR A.JOBID IS NULL) AND 
	(@ANALYSIS1 IS NULL OR A.ANALYSIS1 = @ANALYSIS1 OR A.ANALYSIS1 IS NULL)
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHANALYSISCODES1]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHANALYSISCODES1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHANALYSISCODES1]
GO


CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHANALYSISCODES1]
	@SEARCH	NVARCHAR(100),
	@MAXRECORDS	INT,
	@SUBJOBID INT
AS
BEGIN
    DECLARE @JOBID INT;
    
    SELECT @JOBID = JOBID
    FROM ENPRISE_JOBCOST_SUBJOB
    WHERE SUBJOBID = @SUBJOBID;
    
	SELECT TOP(@MAXRECORDS) A.SEQNO AS ID,
	A.DESCRIPTION AS TITLE
	FROM ENPRISE_JOBCOST_ANALYSIS A	
	WHERE (@SEARCH IS NULL OR A.DESCRIPTION LIKE '%' + @SEARCH + '%') AND
	(A.JOBID IS NULL OR A.JOBID = @JOBID)
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHACTIVITIES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHACTIVITIES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHACTIVITIES]
GO


CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHACTIVITIES]
	@SEARCH	NVARCHAR(100),
	@MAXRECORDS	INT
AS
BEGIN
	SELECT TOP(@MAXRECORDS) A.CLGCODE AS ID,
	A.DETAILS AS TITLE
	FROM OCLG A
	LEFT JOIN ENPRISE_JOBCOST_SUBJOB B ON B.SUBJOBID = A.U_ENP_SUBJOBID 
	LEFT JOIN OCLS C ON C.CODE = A.CNTCTSBJCT
	LEFT JOIN OCRD D ON D.CARDCODE = A.CARDCODE
	LEFT JOIN OHEM E ON E.EMPID = A.ATTENDEMPL
	LEFT JOIN OUSR F ON F.USERID = A.ATTENDUSER	
	WHERE @SEARCH IS NULL OR 
	A.DETAILS LIKE '%' + @SEARCH + '%' OR
	A.NOTES LIKE '%' + @SEARCH + '%' OR 
	A.CARDCODE LIKE '%' + @SEARCH + '%' OR 
	D.CARDNAME LIKE '%' + @SEARCH + '%' OR 
	F.U_NAME LIKE '%' + @SEARCH + '%' OR 
	A.ROOM LIKE '%' + @SEARCH + '%' OR 
	A.STREET LIKE '%' + @SEARCH + '%' OR 
	A.CITY LIKE '%' + @SEARCH + '%' OR 
	A.STATE LIKE '%' + @SEARCH + '%' OR 
	A.COUNTRY LIKE '%' + @SEARCH + '%' OR
	B.JOBTITLE LIKE '%' + @SEARCH + '%'
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SAVESUBJOB]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SAVESUBJOB]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SAVESUBJOB]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SAVESUBJOB]
	@ID INT,
	@JOB_STATUS INT,
	@TITLE NVARCHAR(100),
	@START_DATE DATETIME,
	@END_DATE DATETIME,
	@ORDER_NUMBER NVARCHAR(100),
	@PROJECT_MANAGER_USER_ID INT,
	@CATEGORY_ID INT,
	@DESCRIPTION NVARCHAR(500),
	@INTERNAL_NOTES NVARCHAR(500),
	@SIGNATURE_DATE DATETIME,
	@SIGNATURE_SIGNED_BY_NAME NVARCHAR(100),
	@JOB_RESOLUTION_NOTES NVARCHAR(500),
	@SUCCESS BIT OUTPUT,
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE NVARCHAR(100) OUTPUT
AS
BEGIN
	DECLARE @STATUSSEQNO INT;
	DECLARE @CURRSTATUS INT;
	DECLARE @PENDING INT = 1;
	DECLARE @ACCEPTED INT = 2;
	DECLARE @INPROGRESS INT = 4;
	DECLARE @COMPLETE INT = 8;
	DECLARE @CANCELLED INT = 16;
	
	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;	
	
	IF (@JOB_STATUS NOT IN (@PENDING, @ACCEPTED, @INPROGRESS, @COMPLETE, @CANCELLED))
	BEGIN
		SET @SUCCESS = 0; 
	    SET @ERROR_NUMBER = 3;
	    SET @ERROR_MESSAGE = 'INVALID NEW JOB STATUS - MUST BE PENDING,ACCEPTED,INPROGRESS,COMPLETE OR CANCELLED';
	    RETURN;	
	END;
	
	SELECT @STATUSSEQNO = SEQNO
	FROM ENPRISE_JOBCOST_STATUS
	WHERE EA_STATUS = @JOB_STATUS;	
	
	IF (@@ROWCOUNT = 0)
	BEGIN		
		-- Anywhere job status does not exist
	    SET @SUCCESS = 0; 
	    SET @ERROR_NUMBER = 2;
	    SET @ERROR_MESSAGE = 'JOB STATUS - DOES NOT EXIST';
	    RETURN;
	END;	
	
	SELECT @CURRSTATUS = EA_STATUS
	FROM ENPRISE_JOBCOST_SUBJOB A
	LEFT JOIN ENPRISE_JOBCOST_STATUS B ON B.SEQNO = A.STATUS
	WHERE A.SUBJOBID = @ID;		
	
	-- perform checks if the job status has changed
	IF (@CURRSTATUS IS NOT NULL AND @CURRSTATUS <> @JOB_STATUS)
	BEGIN
		--check the new job status is a valid follow on state from the current job status
		IF (@CURRSTATUS = @PENDING AND @JOB_STATUS NOT IN (@ACCEPTED, @CANCELLED))
		BEGIN
			-- the new job status does not follow on from the current job status
			SET @SUCCESS = 0; 
			SET @ERROR_NUMBER = 3;
			SET @ERROR_MESSAGE = 'INVALID NEW JOB STATUS - MUST BE EITHER ACCEPTED OR CANCELLED';
			RETURN;
		END
		ELSE IF (@CURRSTATUS = @ACCEPTED AND @JOB_STATUS NOT IN (@INPROGRESS, @CANCELLED))
		BEGIN
			-- the new job status does not follow on from the current job status
			SET @SUCCESS = 0; 
			SET @ERROR_NUMBER = 3;
			SET @ERROR_MESSAGE = 'INVALID NEW JOB STATUS - MUST BE EITHER IN PROGRESS OR CANCELLED';
			RETURN;
		END
		ELSE IF (@CURRSTATUS = @INPROGRESS  AND @JOB_STATUS NOT IN (@COMPLETE, @CANCELLED))
		BEGIN
			-- the new job status does not follow on from the current job status
			SET @SUCCESS = 0; 
			SET @ERROR_NUMBER = 3;
			SET @ERROR_MESSAGE = 'INVALID NEW JOB STATUS - MUST BE EITHER COMPLETE OR CANCELLED';
			RETURN;
		END
		ELSE IF (@CURRSTATUS = @COMPLETE  AND @JOB_STATUS NOT IN (@ACCEPTED))
		BEGIN
			-- the new job status does not follow on from the current job status
			SET @SUCCESS = 0; 
			SET @ERROR_NUMBER = 3;
			SET @ERROR_MESSAGE = 'INVALID NEW JOB STATUS - MUST BE ACCEPTED';
			RETURN;
		END
		ELSE IF (@CURRSTATUS = @CANCELLED  AND @JOB_STATUS NOT IN (@ACCEPTED))
		BEGIN
			-- the new job status does not follow on from the current job status
			SET @SUCCESS = 0; 
			SET @ERROR_NUMBER = 3;
			SET @ERROR_MESSAGE = 'INVALID NEW JOB STATUS - MUST BE ACCEPTED';
			RETURN;
		END;
	END;	
	
	UPDATE ENPRISE_JOBCOST_SUBJOB
	SET STATUS = @STATUSSEQNO, JOBTITLE = @TITLE, STARTDATE = @START_DATE, DUEDATE = @END_DATE, ORDERNO = @ORDER_NUMBER,
	ENTERBY = @PROJECT_MANAGER_USER_ID, CATEGORY = @CATEGORY_ID, DESCRIPTION = @DESCRIPTION,
	INTERNALINSTRUCTIONS = @INTERNAL_NOTES
	WHERE SUBJOBID = @ID;	
	
	IF (@@ROWCOUNT = 0)
	BEGIN
		-- no subjob updated
	    SET @SUCCESS = 0; 
	    SET @ERROR_NUMBER = 1;
	    SET @ERROR_MESSAGE = 'INVALID SUBJOB';
	    RETURN;
	END;
		
	IF (@SIGNATURE_DATE IS NOT NULL OR @SIGNATURE_SIGNED_BY_NAME IS NOT NULL OR @JOB_RESOLUTION_NOTES IS NOT NULL)
	BEGIN
		IF EXISTS(SELECT * FROM ENPRISE_JOBCOST_SIGNOFF WHERE OBJECTSEQNO = @ID AND OBJECTTYPE = 1005)
		BEGIN
			UPDATE ENPRISE_JOBCOST_SIGNOFF
			SET SIGNATUREDATE = @SIGNATURE_DATE, SIGNEDBY = @SIGNATURE_SIGNED_BY_NAME, RESOLUTION = @JOB_RESOLUTION_NOTES
			WHERE OBJECTSEQNO = @ID AND OBJECTTYPE = 1005			
		END
		ELSE
		BEGIN
			INSERT INTO ENPRISE_JOBCOST_SIGNOFF
			(OBJECTTYPE, OBJECTSEQNO, SIGNEDBY, SIGNATUREDATE, RESOLUTION)
			VALUES(1005, @ID, @SIGNATURE_SIGNED_BY_NAME, ISNULL(@SIGNATURE_DATE, GETDATE()), @JOB_RESOLUTION_NOTES)
		END
	END	
	
	SET @SUCCESS = 1;	
END

GO
/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SUMTIMESHEETHOUROUTSIDEOFRANGE]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SUMTIMESHEETHOUROUTSIDEOFRANGE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SUMTIMESHEETHOUROUTSIDEOFRANGE]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SUMTIMESHEETHOUROUTSIDEOFRANGE]
	@START_DATE DATETIME,
	@END_DATE DATETIME,
	@USER_IDS VARCHAR(100),
	@STATUS INT
AS
BEGIN
	DECLARE @TBL_USERIDS TABLE(ID INT);
	DECLARE @XML_USERIDS XML;

	SET @XML_USERIDS = '<xml><e>' + REPLACE(@USER_IDS,', ','</e><e>') + '</e></xml>';

	-- Get ID's from xml
	IF (@USER_IDS IS NOT NULL AND LEN(@USER_IDS) > 0)
	BEGIN
		  SET @XML_USERIDS = '<xml><e>' + REPLACE(@USER_IDS,',','</e><e>') + '</e></xml>';

		  -- Get ID's from xml
		  INSERT INTO @TBL_USERIDS(ID)
		  SELECT N.value('.', 'INT')
		  FROM @XML_USERIDS.nodes('xml/e') AS T(N);
	END
	ELSE
	BEGIN
		  INSERT INTO @TBL_USERIDS(ID)
		  SELECT REFERENCEID
		  FROM ENPRISE_TSM_USERS;
	END;


	SELECT SUM(A.QTY) AS HOURS
	FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES A
	LEFT JOIN @TBL_USERIDS B ON B.ID = A.EMPID
	WHERE (A.LINETYPE = 'S') AND (A.TRANSDATE < @START_DATE OR A.TRANSDATE > @END_DATE)
	AND ((@STATUS = 1) OR (@STATUS = 2 AND A.SUBMITTED = 'Y') OR (@STATUS = 3 AND A.APPROVED = 'Y'))
	AND (@USER_IDS IS NULL OR B.ID IS NOT NULL);		
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SUBMITTIMESHEET]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SUBMITTIMESHEET]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SUBMITTIMESHEET]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SUBMITTIMESHEET]
	@START_DATE	DATETIME,
	@END_DATE DATETIME,
	@USER_ID INT,
	@SUCCESS BIT OUTPUT,
	@ERROR_NUMBER INT OUTPUT, 
	@ERROR_MESSAGE NVARCHAR(100) OUTPUT
AS
BEGIN
	DECLARE @EMPID INT;
	
	-- set default values for output parameters
	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;
	
	SELECT @EMPID = A.EMPID
	FROM OHEM A
	JOIN ENPRISE_TSM_USERS B ON B.REFERENCEID = A.EMPID
	WHERE B.USERID = @USER_ID;
	
	IF (@EMPID IS NULL)
	BEGIN
		-- ENPRISE_TSM_USER must be linked to an SAP employee
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 2;
		SET @ERROR_MESSAGE = 'USER_ID IS NOT LINKED TO AN SBO EMPLOYEE';
		RETURN;
	END;

	UPDATE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
	SET SUBMITTED = 'Y'
	WHERE EMPID = @EMPID AND TRANSDATE >= @START_DATE AND TRANSDATE <= @END_DATE 
	AND POSTED = 'N' AND SUBMITTED = 'N';
	
	IF (@@ROWCOUNT = 0)
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 1;
		SET @ERROR_MESSAGE = 'NO TIMESHEETS UPDATED';
		RETURN;
	END;
	
	SET @SUCCESS = 1;
END;


GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHTIMESHEETITEMCODES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHTIMESHEETITEMCODES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHTIMESHEETITEMCODES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHTIMESHEETITEMCODES]
      @SEARCH     NVARCHAR(100),
      @MAXRECORDS INT,
      @SUBJOB_ID INT = NULL
AS
BEGIN
	SELECT TOP(@MAXRECORDS) A.ITEMCODE AS ID, 
	ITEMNAME AS TITLE
	FROM OITM A
	LEFT JOIN ENPRISE_JOBCOST_SUBJOB S ON S.SUBJOBID = @SUBJOB_ID
	LEFT JOIN ENPRISE_JOBCOST_JOBTYPE T ON T.SEQNO = S.JOBTYPE
	LEFT JOIN ENPRISE_JOBCOST_JOBLINES L ON L.SUBJOBID = S.SUBJOBID AND L.LINETYPE = 'Q' AND L.STOCKCODE = A.ITEMCODE
    WHERE (@SEARCH IS NULL OR @SEARCH = '*' OR A.ITEMCODE LIKE '%' + @SEARCH + '%' 
    OR A.ITEMNAME LIKE '%' + @SEARCH + '%')
	AND (A.INVNTITEM = 'N') AND (A.SELLITEM = 'Y' OR A.PRCHSEITEM = 'Y')
	AND (A.FROZENFOR = 'N') 
	AND (@SUBJOB_ID IS NULL OR T.RESTRICTTIMETOQUOTE = 'N' OR L.STOCKCODE IS NOT NULL)
	ORDER BY A.ITEMCODE
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHMATERIALITEMCODES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHMATERIALITEMCODES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHMATERIALITEMCODES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHMATERIALITEMCODES]
	@SEARCH	NVARCHAR(100),
	@MAXRECORDS	INT,
	@SUBJOB_ID INT = NULL
AS
BEGIN
	-- if @SUBJOB_ID is passed in, and RESTRICTTIMETOQUOTE is 'Y' then only show stock items which have been quoted
	SELECT TOP(@MAXRECORDS) A.ITEMCODE AS ID, 
	ITEMNAME AS TITLE
	FROM OITM A
	LEFT JOIN ENPRISE_JOBCOST_SUBJOB S ON S.SUBJOBID = @SUBJOB_ID
	LEFT JOIN ENPRISE_JOBCOST_JOBTYPE T ON T.SEQNO = S.JOBTYPE
	LEFT JOIN ENPRISE_JOBCOST_JOBLINES L ON L.SUBJOBID = S.SUBJOBID AND L.LINETYPE = 'Q' AND L.STOCKCODE = A.ITEMCODE
	WHERE (@SEARCH IS NULL OR @SEARCH = '*' OR A.ITEMCODE LIKE '%' + @SEARCH + '%' 
	OR A.ITEMNAME LIKE '%' + @SEARCH + '%')
	AND (A.INVNTITEM = 'Y' OR A.SELLITEM = 'Y' OR A.PRCHSEITEM = 'Y')
	AND (A.FROZENFOR = 'N') 
	AND (@SUBJOB_ID IS NULL OR T.RESTRICTTIMETOQUOTE = 'N' OR L.STOCKCODE IS NOT NULL)
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SEARCHEXPENSEITEMCODES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SEARCHEXPENSEITEMCODES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHEXPENSEITEMCODES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SEARCHEXPENSEITEMCODES]
	@SEARCH	NVARCHAR(100),
	@MAXRECORDS	INT,
	@SUBJOB_ID INT = NULL
AS
BEGIN
	-- if @SUBJOB_ID is passed in, and RESTRICTTIMETOQUOTE is 'Y' then only show stock items which have been quoted
	SELECT TOP(@MAXRECORDS) A.ITEMCODE AS ID, 
	ITEMNAME AS TITLE
	FROM OITM A
	LEFT JOIN ENPRISE_JOBCOST_SUBJOB S ON S.SUBJOBID = @SUBJOB_ID
	LEFT JOIN ENPRISE_JOBCOST_JOBTYPE T ON T.SEQNO = S.JOBTYPE
	LEFT JOIN ENPRISE_JOBCOST_JOBLINES L ON L.SUBJOBID = S.SUBJOBID AND L.LINETYPE = 'Q' AND L.STOCKCODE = A.ITEMCODE
	WHERE (@SEARCH IS NULL OR @SEARCH = '*' OR A.ITEMCODE LIKE '%' + @SEARCH + '%' 
	OR A.ITEMNAME LIKE '%' + @SEARCH + '%')
	AND (A.INVNTITEM = 'N')
	AND (A.FROZENFOR = 'N') 
	AND (@SUBJOB_ID IS NULL OR T.RESTRICTTIMETOQUOTE = 'N' OR L.STOCKCODE IS NOT NULL)
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SAVETIMESHEETENTRY]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SAVETIMESHEETENTRY]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SAVETIMESHEETENTRY]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SAVETIMESHEETENTRY]
	@ID    INT,
	@USER_ID INT,
	@CREATED_BY_USER_ID  INT,
	@START_DATE   DATETIME,
	@END_DATE DATETIME,
	@DURATION SMALLMONEY, 
	@SUB_JOB_ID   INT,
	@ITEM_CODE NVARCHAR(20),
	@ITEM_DESCRIPTION NVARCHAR(100),
	@NON_BILLABLE BIT,
	@COST_CENTER_ID      NVARCHAR(8),
	@ANALYSIS_CODE_1 INT,
	@ANALYSIS_CODE_2 INT,
	@ACTIVITY_ID INT,    
	@TRANS_DATE DATETIME,
	@ADDITIONAL_NOTES NVARCHAR(500),
	@TIME_TYPE_ID INT,
	@APPOINTMENT_ID INT, 
	@SUCCESS BIT OUTPUT,
	@NEWID INT OUTPUT,
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE  NVARCHAR(100) OUTPUT
AS
BEGIN
	DECLARE	@NOTESEQNO INT;
	DECLARE @TRANSDATE DATE;	
	DECLARE @ENTERBY INT;
	DECLARE @EMPID INT;

	-- set default values for output parameters
	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;

	IF @TRANS_DATE IS NULL
		SET @TRANSDATE = CAST(@START_DATE AS DATE)
	ELSE 
		SET @TRANSDATE = CAST(@TRANS_DATE AS DATE)	
				
	SELECT @EMPID = A.EMPID
	FROM OHEM A
	JOIN ENPRISE_TSM_USERS B ON B.REFERENCEID = A.EMPID
	WHERE B.USERID = @USER_ID
	
	IF (@EMPID IS NULL)
	BEGIN
		-- ENPRISE_TSM_USER must be linked to an SAP employee
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 2;
		SET @ERROR_MESSAGE = 'USER_ID IS NOT LINKED TO AN SBO EMPLOYEE';
		RETURN;
	END;
				
	SELECT @ENTERBY = A.EMPID
	FROM OHEM A
	JOIN ENPRISE_TSM_USERS B ON B.REFERENCEID = A.EMPID
	WHERE B.USERID = @CREATED_BY_USER_ID;
	
	IF (@ENTERBY IS NULL)
	BEGIN
		-- ENPRISE_TSM_USER must be linked to an SAP employee
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 3;
		SET @ERROR_MESSAGE = 'CREATED_BY_USER_ID IS NOT LINKED TO AN SBO EMPLOYEE';
		RETURN;
	END;
			
		
	IF (@ID IS NULL)
	BEGIN
		IF (@ADDITIONAL_NOTES IS NOT NULL AND LEN(@ADDITIONAL_NOTES) > 0)
		BEGIN
			INSERT INTO ENPRISE_JOBCOST_TEMPBATCHNOTES
			(NOTE)
			VALUES(@ADDITIONAL_NOTES);

			SET @NOTESEQNO = IDENT_CURRENT('ENPRISE_JOBCOST_TEMPBATCHNOTES');                                                   
		END;          
				
		INSERT INTO ENPRISE_JOBCOST_TEMPBATCHJOBLINES
		(LINETYPE, POSTED, SUBMITTED, APPROVED, BATCHNO, SOURCE, SUBJOBID, ENTERBY, EMPID, CREATEDATE, NOTENO, NONBILLABLE,
		TIMETYPE, STOCKCODE, DESCRIPTION, QTY, TRANSDATE, AVECOST, SELLPR, ORIGSELLPR,
		STARTDATE, ENDDATE, COSTINGCODE, ANALYSIS1, ANALYSIS2, ACTIVITYID, SCHEDULEDETAIL )
		VALUES('S', 'N', 'N', 'N', 1, 'EAW', @SUB_JOB_ID, @ENTERBY, @EMPID, GETDATE(), @NOTESEQNO,
		CASE @NON_BILLABLE
			 WHEN 1 THEN 'Y'
			 ELSE 'N'
		END,
		-- set AVECOST, SELLPR and ORIGSELLPR to null to allow them to be set during web import			
		@TIME_TYPE_ID, @ITEM_CODE, @ITEM_DESCRIPTION, @DURATION, @TRANSDATE, NULL, NULL, NULL,
		@START_DATE, @END_DATE, @COST_CENTER_ID, @ANALYSIS_CODE_1, @ANALYSIS_CODE_2, @ACTIVITY_ID, @APPOINTMENT_ID);

		SELECT @NEWID = IDENT_CURRENT('ENPRISE_JOBCOST_TEMPBATCHJOBLINES');               
	END
	ELSE
	BEGIN
		SELECT @NOTESEQNO = NOTENO 
		FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES
		WHERE SEQNO = @ID;

		UPDATE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
		SET SUBJOBID = @SUB_JOB_ID, EMPID = @EMPID,
		NONBILLABLE =
		CASE @NON_BILLABLE
			 WHEN 1 THEN 'Y'
			 ELSE 'N'
		END,          
		TIMETYPE = @TIME_TYPE_ID,
		STOCKCODE = @ITEM_CODE, DESCRIPTION = @ITEM_DESCRIPTION, QTY = @DURATION, TRANSDATE = @TRANS_DATE,                             
		STARTDATE = @START_DATE, ENDDATE = @END_DATE,
		COSTINGCODE = @COST_CENTER_ID, ANALYSIS1 = @ANALYSIS_CODE_1, ANALYSIS2 = @ANALYSIS_CODE_2,
		ACTIVITYID = @ACTIVITY_ID, SCHEDULEDETAIL = @APPOINTMENT_ID
		WHERE SEQNO = @ID AND LINETYPE = 'S' AND POSTED = 'N';

		IF (@@ROWCOUNT = 0)
		BEGIN
			-- if no update occurred
			SET @SUCCESS = 0;
			SET @ERROR_NUMBER = 1;
			SET @ERROR_MESSAGE = 'INVALID TIMESHEET ID OR TIMESHEET HAS BEEN POSTED';
			RETURN;
		END;

		IF (@ADDITIONAL_NOTES IS NOT NULL AND LEN(@ADDITIONAL_NOTES) > 0)
		BEGIN
			IF @NOTESEQNO IS NULL
			BEGIN
				INSERT INTO ENPRISE_JOBCOST_TEMPBATCHNOTES
				(NOTE)
				VALUES(@ADDITIONAL_NOTES);

				SET @NOTESEQNO = IDENT_CURRENT('ENPRISE_JOBCOST_TEMPBATCHNOTES');

				UPDATE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
				SET NOTENO =  @NOTESEQNO
				WHERE SEQNO = @ID;
			END
			ELSE
			BEGIN
				UPDATE ENPRISE_JOBCOST_TEMPBATCHNOTES
				SET NOTE = @ADDITIONAL_NOTES
				WHERE SEQNO = @NOTESEQNO
			END;
		END
		ELSE IF @NOTESEQNO IS NOT NULL
		BEGIN
			-- there is no note, so delete the existing one if available
			UPDATE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
			SET NOTENO =  NULL
			WHERE SEQNO = @ID;

			DELETE FROM ENPRISE_JOBCOST_TEMPBATCHNOTES
			WHERE SEQNO = @NOTESEQNO;                                     
		END;

		SET @NEWID = @ID;          
	END;
	      
	SET @SUCCESS = 1;
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SAVEMATERIAL]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SAVEMATERIAL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SAVEMATERIAL]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SAVEMATERIAL]
       @ID    INT,
       @SUB_JOB_ID   INT,
       @ASSIGNED_USER_ID INT,
       @CREATED_BY_USER_ID  INT,   
       @NON_BILLABLE BIT,
       @WHS NVARCHAR(8),
       @ITEM_CODE    NVARCHAR(20),
       @ITEM_QUANTITY       SMALLMONEY,
       @ITEM_DESCRIPTION NVARCHAR(100),
       @COST_CENTER_ID      NVARCHAR(8),
       @ANALYSIS_CODE_1 INT,
       @ANALYSIS_CODE_2 INT,
       @ACTIVITY_ID INT,
       @DATE_CREATED DATETIME,
       @TRANS_DATE DATETIME,       
       @APPOINTMENT_ID INT,
       @MATERIALRECEIPT INT,
       @SUCCESS BIT OUTPUT,
       @NEWID INT OUTPUT,
       @ERROR_NUMBER INT OUTPUT,
       @ERROR_MESSAGE  NVARCHAR(100) OUTPUT
AS
BEGIN
	DECLARE @EMPID INT;
	DECLARE @ENTERBY INT;
	DECLARE @INSTOCK NUMERIC(19, 6);	
	-- set default values for output parameters
	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;

	SELECT @EMPID = A.EMPID
	FROM OHEM A
	JOIN ENPRISE_TSM_USERS B ON B.REFERENCEID = A.EMPID
	WHERE B.USERID = @ASSIGNED_USER_ID

	IF (@EMPID IS NULL)
	BEGIN
		-- ENPRISE_TSM_USER must be linked to an SAP employee
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 2;
		SET @ERROR_MESSAGE = 'ASSIGNED_USER_ID IS NOT LINKED TO AN SBO EMPLOYEE';
		RETURN;
	END;

	SELECT @ENTERBY = A.EMPID
	FROM OHEM A
	JOIN ENPRISE_TSM_USERS B ON B.REFERENCEID = A.EMPID
	WHERE B.USERID = @CREATED_BY_USER_ID;		

	IF (@ENTERBY IS NULL)
	BEGIN
		-- ENPRISE_TSM_USER must be linked to an SAP employee
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 3;
		SET @ERROR_MESSAGE = 'CREATED_BY_USER_ID IS NOT LINKED TO AN SBO EMPLOYEE';
		RETURN;
	END;
	
	IF (@WHS IS NULL OR LEN(@WHS) = 0)
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 5;
		SET @ERROR_MESSAGE = 'A WAREHOUSE MUST BE SPECIFIED';
		RETURN;	
	END;
	
	IF EXISTS(SELECT 1 FROM OITM WHERE ITEMCODE = @ITEM_CODE AND INVNTITEM = 'Y')
	BEGIN
        SELECT @INSTOCK = (ISNULL(ONHAND, 0) - ISNULL(ISCOMMITED, 0) )
	    FROM OITW
	    WHERE ITEMCODE = @ITEM_CODE AND WHSCODE = @WHS;	
	    
        IF (@INSTOCK < @ITEM_QUANTITY) 
	    BEGIN
	        SET @SUCCESS = 0;
		    SET @ERROR_NUMBER = 4;
		    SET @ERROR_MESSAGE = 'INSUFFICIENT STOCK IN WAREHOUSE';
		    RETURN;
	    END;	    
	END;
	
	IF (@ID IS NULL)
	BEGIN
		INSERT INTO ENPRISE_JOBCOST_TEMPBATCHJOBLINES
		(SUBJOBID, CREATEDATE, TRANSDATE, EMPID, ENTERBY, NONBILLABLE, 
		FROMWHS, MATERIALRECEIPT, STOCKCODE, DESCRIPTION, QTY, COSTINGCODE,
		AVECOST, SELLPR, ORIGSELLPR, LINEDISCOUNT,
		ANALYSIS1, ANALYSIS2, ACTIVITYID, SOURCE,
		BATCHNO, LINETYPE, POSTED, SUBMITTED, APPROVED, SCHEDULEDETAIL)
		VALUES(@SUB_JOB_ID, @DATE_CREATED, @TRANS_DATE, @EMPID, @ENTERBY, 
		CASE @NON_BILLABLE 
			 WHEN 1 THEN 'Y'
			 ELSE 'N'
		END,
		@WHS, @MATERIALRECEIPT, @ITEM_CODE, @ITEM_DESCRIPTION, @ITEM_QUANTITY, @COST_CENTER_ID,
		-- set AVECOST, SELLPR and ORIGSELLPR to null to allow them to be set during web import		
		-- set LINEDISCOUNT to null due to nitpicking
		NULL, NULL, NULL, NULL, 
		@ANALYSIS_CODE_1, @ANALYSIS_CODE_2, @ACTIVITY_ID, 'EAW',
		3, 'M', 'N', 'Y', 'Y', @APPOINTMENT_ID);

		SELECT @NEWID = IDENT_CURRENT('ENPRISE_JOBCOST_TEMPBATCHJOBLINES');  
	END
	ELSE
	BEGIN
		UPDATE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
		SET SUBJOBID = @SUB_JOB_ID, EMPID = @EMPID, 
		NONBILLABLE = 
		CASE @NON_BILLABLE
			WHEN 1 THEN 'Y'
			ELSE 'N'				
		END, 
		TRANSDATE = @TRANS_DATE, FROMWHS = @WHS, MATERIALRECEIPT = @MATERIALRECEIPT, STOCKCODE = @ITEM_CODE, 
		DESCRIPTION = @ITEM_DESCRIPTION, QTY = @ITEM_QUANTITY, COSTINGCODE = @COST_CENTER_ID, 
		ANALYSIS1 = @ANALYSIS_CODE_1, ANALYSIS2 = @ANALYSIS_CODE_2, ACTIVITYID = @ACTIVITY_ID, SCHEDULEDETAIL = @APPOINTMENT_ID
		WHERE SEQNO = @ID AND BATCHNO = 3 AND LINETYPE = 'M' AND POSTED = 'N';

		IF (@@ROWCOUNT = 0)
		BEGIN
			-- if no update occurred
			SET @SUCCESS = 0;
			SET @ERROR_NUMBER = 1;
			SET @ERROR_MESSAGE = 'INVALID MATERIAL LINE ID OR MATERIAL LINE HAS BEEN POSTED';
			RETURN;
		END;

		SET @NEWID = @ID;    
	END;
	SET @SUCCESS = 1;
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SAVEEXPENSE]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SAVEEXPENSE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SAVEEXPENSE]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SAVEEXPENSE]
	@ID	INT,
	@SUB_JOB_ID	INT,
	@ASSIGNED_USER_ID INT,
	@CREATED_BY_USER_ID	INT,
	@NON_BILLABLE BIT,
	@REIMBURSEABLE	BIT,
	@ITEM_CODE NVARCHAR(20),
	@ITEM_DESCRIPTION NVARCHAR(100),
	@ITEM_QUANTITY SMALLMONEY,
	@ITEM_PRICE	SMALLMONEY,
	@ITEM_CURRENCY	NVARCHAR(3),
	@ITEM_FX_RATE SMALLMONEY,
	@ITEM_TAX_PERCENT SMALLMONEY,
	@ITEM_TAX_INCLUSIVE	BIT,
	@ITEM_TOTAL	SMALLMONEY,
	@ITEM_TAX_AMOUNT SMALLMONEY,
	@COST_CENTER_ID	NVARCHAR(8),
	@ANALYSIS_CODE_1 INT,
	@ANALYSIS_CODE_2 INT,
	@ACTIVITY_ID INT,
	@DATE_CREATED DATETIME,
	@DATE_PURCHASED	DATETIME,
	@TAXCODE NVARCHAR(8),
	@APPOINTMENT_ID INT,  
	@SUCCESS BIT OUTPUT,
	@NEWID	INT OUTPUT,
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE NVARCHAR(100) OUTPUT
AS
BEGIN	
	DECLARE @PRICEDEC INT;
	DECLARE @TAXCOUNT INT;
	DECLARE @EMPID INT;
	DECLARE @ENTERBY INT;
	
	-- set default values for output parameters
	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;
	
	--check the tax code
	IF EXISTS(SELECT * FROM ENPRISE_JOBCOST_SETUP WHERE USATAX = 'Y')
	BEGIN			
		SELECT @TAXCOUNT = COUNT(*)
		FROM OSTC 
		WHERE VALIDFORAR = 'Y' AND CODE = @TAXCODE AND RATE = @ITEM_TAX_PERCENT;
	END
	ELSE
	BEGIN			
		SELECT @TAXCOUNT = COUNT(*)
		FROM OVTG 
		WHERE CATEGORY = 'O' AND CODE = @TAXCODE AND RATE = @ITEM_TAX_PERCENT;
	END;
	
	IF (@TAXCOUNT = 0)
	BEGIN		
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 2;
		SET @ERROR_MESSAGE = 'CANNOT FIND THE TAX CODE OR TAX RATE';
		RETURN;
	END;
	
	SELECT @EMPID = A.EMPID
	FROM OHEM A
	JOIN ENPRISE_TSM_USERS B ON B.REFERENCEID = A.EMPID
	WHERE B.USERID = @ASSIGNED_USER_ID
	
	IF (@EMPID IS NULL)
	BEGIN
		-- ENPRISE_TSM_USER must be linked to an SAP employee
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 2;
		SET @ERROR_MESSAGE = 'ASSIGNED_USER_ID IS NOT LINKED TO AN SBO EMPLOYEE';
		RETURN;
	END;
	
	SELECT @ENTERBY = A.EMPID
	FROM OHEM A
	JOIN ENPRISE_TSM_USERS B ON B.REFERENCEID = A.EMPID
	WHERE B.USERID = @CREATED_BY_USER_ID;
	
	IF (@ENTERBY IS NULL)
	BEGIN
		-- ENPRISE_TSM_USER must be linked to an SAP employee
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 3;
		SET @ERROR_MESSAGE = 'CREATED_BY_USER_ID IS NOT LINKED TO AN SBO EMPLOYEE';
		RETURN;
	END;
	
	SELECT @PRICEDEC = PRICEDEC
	FROM OADM;
		
	IF (@ID IS NULL)
	BEGIN		    	    			 							
		INSERT INTO ENPRISE_JOBCOST_TEMPBATCHJOBLINES
		(SUBJOBID, ENTERBY, EMPID, NONBILLABLE, EXPREIMBURSE, STOCKCODE, DESCRIPTION, QTY, AVECOST, SELLPR, ORIGSELLPR,
		SHOWTAXINCLAVECOST, CURRCODE, FXRATE, TAXCODE, COSTTAXINC, COSTTAXSUM, COSTINGCODE, 
		ANALYSIS1, ANALYSIS2, SOURCE, ACTIVITYID, CREATEDATE, TRANSDATE, BATCHNO, LINETYPE, POSTED, SUBMITTED, APPROVED, SCHEDULEDETAIL)
		VALUES(@SUB_JOB_ID, @ENTERBY, @EMPID,
		CASE @NON_BILLABLE 
			WHEN 1 THEN 'Y' 
			ELSE 'N' 
		END,
		CASE @REIMBURSEABLE
			WHEN 1 THEN 'Y'
			ELSE 'N'
		END,
		@ITEM_CODE, @ITEM_DESCRIPTION, @ITEM_QUANTITY,
		-- always store the price as exclusive of tax
		CASE @ITEM_TAX_INCLUSIVE 
			WHEN 0 THEN @ITEM_PRICE
			ELSE ROUND(@ITEM_PRICE - @ITEM_TAX_AMOUNT, @PRICEDEC)
		END,
		-- set SELLPR and ORIGSELLPR to null to allow them to be set during web import		
		NULL, NULL,
		CASE @ITEM_TAX_INCLUSIVE
			WHEN 1 THEN 'Y'
			ELSE 'N'
		END,
		@ITEM_CURRENCY, @ITEM_FX_RATE, @TAXCODE, 
		-- always store the price exclusive of tax
		'N',
		@ITEM_TAX_AMOUNT, @COST_CENTER_ID, @ANALYSIS_CODE_1, @ANALYSIS_CODE_2, 'EAW',
		-- default submitted and approved to Y for phase 1
		@ACTIVITY_ID, @DATE_CREATED, @DATE_PURCHASED, 2, 'M', 'N', 'Y', 'Y', @APPOINTMENT_ID);	
		
		SELECT @NEWID = IDENT_CURRENT('ENPRISE_JOBCOST_TEMPBATCHJOBLINES');
	END
	ELSE
	BEGIN
		UPDATE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
		SET SUBJOBID = @SUB_JOB_ID, EMPID = @EMPID,
		NONBILLABLE =
		CASE @NON_BILLABLE
			WHEN 1 THEN 'Y'
			ELSE 'N'
		END,
		EXPREIMBURSE = 
		CASE @REIMBURSEABLE
			WHEN 1 THEN 'Y'
			ELSE 'N'
		END,
		STOCKCODE = @ITEM_CODE, DESCRIPTION = @ITEM_DESCRIPTION, QTY = @ITEM_QUANTITY,
		-- always store the price exclusive of tax
		AVECOST =
		CASE @ITEM_TAX_INCLUSIVE
			WHEN 0 THEN @ITEM_PRICE
			ELSE ROUND(@ITEM_PRICE - @ITEM_TAX_AMOUNT, @PRICEDEC)
		END,
		SHOWTAXINCLAVECOST = 
		CASE @ITEM_TAX_INCLUSIVE
			WHEN 1 THEN 'Y'
			ELSE 'N'
		END,
		CURRCODE = @ITEM_CURRENCY, FXRATE = @ITEM_FX_RATE, TAXCODE = @TAXCODE,
		-- always store the price exclusive of tax
		COSTTAXINC = 'N',		
		COSTTAXSUM = @ITEM_TAX_AMOUNT,
		COSTINGCODE = @COST_CENTER_ID, ANALYSIS1 = @ANALYSIS_CODE_1, ANALYSIS2 = @ANALYSIS_CODE_2,
		ACTIVITYID = @ACTIVITY_ID, CREATEDATE = @DATE_CREATED, TRANSDATE = @DATE_PURCHASED,
		SCHEDULEDETAIL = @APPOINTMENT_ID		
		WHERE SEQNO = @ID AND BATCHNO = 2 AND LINETYPE = 'M' AND POSTED = 'N';
				
		IF (@@ROWCOUNT = 0)
		BEGIN
			-- if no update occurred
			SET @SUCCESS = 0;
			SET @ERROR_NUMBER = 1;
			SET @ERROR_MESSAGE = 'INVALID EXPENSE ID OR EXPENSE HAS BEEN POSTED';
			RETURN;
		END;
		
		SET @NEWID = @ID;
	END;
	SET @SUCCESS = 1;
END;

GO


/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_SAVEATTACHMENT]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_SAVETIMESHEETENTRY]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_SAVETIMESHEETENTRY]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_SAVETIMESHEETENTRY]
	@ID    INT,
	@USER_ID INT,
	@CREATED_BY_USER_ID  INT,
	@START_DATE   DATETIME,
	@END_DATE DATETIME,
	@DURATION SMALLMONEY, 
	@SUB_JOB_ID   INT,
	@ITEM_CODE NVARCHAR(20),
	@ITEM_DESCRIPTION NVARCHAR(100),
	@NON_BILLABLE BIT,
	@COST_CENTER_ID      NVARCHAR(8),
	@ANALYSIS_CODE_1 INT,
	@ANALYSIS_CODE_2 INT,
	@ACTIVITY_ID INT,    
	@TRANS_DATE DATETIME,
	@ADDITIONAL_NOTES NVARCHAR(500),
	@TIME_TYPE_ID INT,
	@APPOINTMENT_ID INT, 
	@SUCCESS BIT OUTPUT,
	@NEWID INT OUTPUT,
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE  NVARCHAR(100) OUTPUT
AS
BEGIN
	DECLARE	@NOTESEQNO INT;
	DECLARE @TRANSDATE DATE;	
	DECLARE @ENTERBY INT;
	DECLARE @EMPID INT;

	-- set default values for output parameters
	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;

	IF @TRANS_DATE IS NULL
		SET @TRANSDATE = CAST(@START_DATE AS DATE)
	ELSE 
		SET @TRANSDATE = CAST(@TRANS_DATE AS DATE)	
				
	SELECT @EMPID = A.EMPID
	FROM OHEM A
	JOIN ENPRISE_TSM_USERS B ON B.REFERENCEID = A.EMPID
	WHERE B.USERID = @USER_ID
	
	IF (@EMPID IS NULL)
	BEGIN
		-- ENPRISE_TSM_USER must be linked to an SAP employee
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 2;
		SET @ERROR_MESSAGE = 'USER_ID IS NOT LINKED TO AN SBO EMPLOYEE';
		RETURN;
	END;
				
	SELECT @ENTERBY = A.EMPID
	FROM OHEM A
	JOIN ENPRISE_TSM_USERS B ON B.REFERENCEID = A.EMPID
	WHERE B.USERID = @CREATED_BY_USER_ID;
	
	IF (@ENTERBY IS NULL)
	BEGIN
		-- ENPRISE_TSM_USER must be linked to an SAP employee
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 3;
		SET @ERROR_MESSAGE = 'CREATED_BY_USER_ID IS NOT LINKED TO AN SBO EMPLOYEE';
		RETURN;
	END;
			
		
	IF (@ID IS NULL)
	BEGIN
		IF (@ADDITIONAL_NOTES IS NOT NULL AND LEN(@ADDITIONAL_NOTES) > 0)
		BEGIN
			INSERT INTO ENPRISE_JOBCOST_TEMPBATCHNOTES
			(NOTE)
			VALUES(@ADDITIONAL_NOTES);

			SET @NOTESEQNO = IDENT_CURRENT('ENPRISE_JOBCOST_TEMPBATCHNOTES');                                                   
		END;          
				
		INSERT INTO ENPRISE_JOBCOST_TEMPBATCHJOBLINES
		(LINETYPE, POSTED, SUBMITTED, APPROVED, BATCHNO, SOURCE, SUBJOBID, ENTERBY, EMPID, CREATEDATE, NOTENO, NONBILLABLE,
		TIMETYPE, STOCKCODE, DESCRIPTION, QTY, TRANSDATE, AVECOST, SELLPR, ORIGSELLPR,
		STARTDATE, ENDDATE, COSTINGCODE, ANALYSIS1, ANALYSIS2, ACTIVITYID, SCHEDULEDETAIL )
		VALUES('S', 'N', 'N', 'N', 1, 'EAW', @SUB_JOB_ID, @ENTERBY, @EMPID, GETDATE(), @NOTESEQNO,
		CASE @NON_BILLABLE
			 WHEN 1 THEN 'Y'
			 ELSE 'N'
		END,
		-- set AVECOST, SELLPR and ORIGSELLPR to null to allow them to be set during web import			
		@TIME_TYPE_ID, @ITEM_CODE, @ITEM_DESCRIPTION, @DURATION, @TRANSDATE, NULL, NULL, NULL,
		@START_DATE, @END_DATE, @COST_CENTER_ID, @ANALYSIS_CODE_1, @ANALYSIS_CODE_2, @ACTIVITY_ID, @APPOINTMENT_ID);

		SELECT @NEWID = IDENT_CURRENT('ENPRISE_JOBCOST_TEMPBATCHJOBLINES');               
	END
	ELSE
	BEGIN
		SELECT @NOTESEQNO = NOTENO 
		FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES
		WHERE SEQNO = @ID;

		UPDATE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
		SET SUBJOBID = @SUB_JOB_ID, EMPID = @EMPID,
		NONBILLABLE =
		CASE @NON_BILLABLE
			 WHEN 1 THEN 'Y'
			 ELSE 'N'
		END,          
		TIMETYPE = @TIME_TYPE_ID,
		STOCKCODE = @ITEM_CODE, DESCRIPTION = @ITEM_DESCRIPTION, QTY = @DURATION, TRANSDATE = @TRANS_DATE,                             
		STARTDATE = @START_DATE, ENDDATE = @END_DATE,
		COSTINGCODE = @COST_CENTER_ID, ANALYSIS1 = @ANALYSIS_CODE_1, ANALYSIS2 = @ANALYSIS_CODE_2,
		ACTIVITYID = @ACTIVITY_ID, SCHEDULEDETAIL = @APPOINTMENT_ID
		WHERE SEQNO = @ID AND LINETYPE = 'S' AND POSTED = 'N';

		IF (@@ROWCOUNT = 0)
		BEGIN
			-- if no update occurred
			SET @SUCCESS = 0;
			SET @ERROR_NUMBER = 1;
			SET @ERROR_MESSAGE = 'INVALID TIMESHEET ID OR TIMESHEET HAS BEEN POSTED';
			RETURN;
		END;

		IF (@ADDITIONAL_NOTES IS NOT NULL AND LEN(@ADDITIONAL_NOTES) > 0)
		BEGIN
			IF @NOTESEQNO IS NULL
			BEGIN
				INSERT INTO ENPRISE_JOBCOST_TEMPBATCHNOTES
				(NOTE)
				VALUES(@ADDITIONAL_NOTES);

				SET @NOTESEQNO = IDENT_CURRENT('ENPRISE_JOBCOST_TEMPBATCHNOTES');

				UPDATE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
				SET NOTENO =  @NOTESEQNO
				WHERE SEQNO = @ID;
			END
			ELSE
			BEGIN
				UPDATE ENPRISE_JOBCOST_TEMPBATCHNOTES
				SET NOTE = @ADDITIONAL_NOTES
				WHERE SEQNO = @NOTESEQNO
			END;
		END
		ELSE IF @NOTESEQNO IS NOT NULL
		BEGIN
			-- there is no note, so delete the existing one if available
			UPDATE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
			SET NOTENO =  NULL
			WHERE SEQNO = @ID;

			DELETE FROM ENPRISE_JOBCOST_TEMPBATCHNOTES
			WHERE SEQNO = @NOTESEQNO;                                     
		END;

		SET @NEWID = @ID;          
	END;
	      
	SET @SUCCESS = 1;
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETTIMESHEETDAYSUMMARIES]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETTIMESHEETDAYSUMMARIES]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETTIMESHEETDAYSUMMARIES]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETTIMESHEETDAYSUMMARIES]
	@START_DATE	DATETIME,
	@END_DATE DATE,
	@USER_ID INT
AS
BEGIN
	SELECT A.TRANSDATE AS DATE, SUM(A.QTY) AS TOTAL_TIME
	FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES A	
	LEFT JOIN ENPRISE_TSM_USERS U ON U.REFERENCEID = A.EMPID
	WHERE A.LINETYPE = 'S' AND U.USERID = @USER_ID AND A.TRANSDATE >= @START_DATE AND A.TRANSDATE <= @END_DATE 
	GROUP BY A.TRANSDATE
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETTIMESHEETENTRY]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETTIMESHEETENTRY]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETTIMESHEETENTRY]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETTIMESHEETENTRY]
	@ID	INT
AS
BEGIN		

	SELECT A.SEQNO AS ID,
	U.USERID AS USER_ID,
	ISNULL(U.NAME, '') AS USER_DISPLAY_NAME,
	A.STARTDATE AS START_DATE,
	A.ENDDATE AS END_DATE,	
	--DATEDIFF(HH, A.STARTDATE, A.ENDDATE) AS DURATION,
	A.QTY AS DURATION,
	A.SUBJOBID AS SUB_JOB_ID,
	S.JOBTITLE AS SUB_JOB_TITLE,
	S.CATEGORY AS SUB_JOB_CATEGORY_ID,
	C.CATEGORY AS SUB_JOB_CATEGORY_TITLE,
	J.JOBID AS JOB_ID,
	J.JOBTITLE AS JOB_TITLE,
	O.CARDCODE AS CUSTOMER_ID,
	O.CARDNAME AS CUSTOMER_NAME,
	A.STOCKCODE AS ITEM_CODE,
	A.DESCRIPTION AS ITEM_DESCRIPTION,
	CASE A.NONBILLABLE 
		WHEN 'Y' THEN 1
		ELSE 0
	END AS NON_BILLABLE,
	A.COSTINGCODE AS COST_CENTER_ID,
	A.ANALYSIS1 AS  ANALYSIS_CODE_1,
	A.ANALYSIS2 AS ANALYSIS_CODE_2,
	A.ACTIVITYID AS ACTIVITY_ID,
	A.CREATEDATE AS DATE_CREATED,
	A.TRANSDATE AS TRANS_DATE,
	N.NOTE AS ADDITIONAL_NOTES,
	A.TIMETYPE AS TIME_TYPE_ID,
	CASE 
		WHEN A.APPROVED = 'Y' THEN 3
		WHEN A.SUBMITTED = 'Y' THEN 2
		ELSE 1
	END AS STATUS,
	A.SCHEDULEDETAIL AS APPOINTMENT_ID			
	FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES A	
	LEFT JOIN ENPRISE_TSM_USERS U ON U.REFERENCEID = A.EMPID
	LEFT JOIN ENPRISE_JOBCOST_SUBJOB S ON S.SUBJOBID = A.SUBJOBID
	LEFT JOIN ENPRISE_JOBCOST_CATEGORY C ON C.SEQNO = S.CATEGORY
	LEFT JOIN ENPRISE_JOBCOST_JOB J ON J.JOBID = S.JOBID
	LEFT JOIN OCRD O ON O.CARDCODE = S.CARDCODE
	LEFT JOIN ENPRISE_JOBCOST_TEMPBATCHNOTES N ON N.SEQNO = A.NOTENO	
	WHERE (A.LINETYPE = 'S') AND (A.SEQNO = @ID);
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETMATERIAL]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETMATERIAL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETMATERIAL]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETMATERIAL]
	@ID	INT
AS
BEGIN	
	SELECT A.SEQNO AS ID,
	A.SUBJOBID AS SUB_JOB_ID,
	B.JOBID AS JOB_ID,
	B.CARDCODE AS CUSTOMER_ID,
	B.CARDNAME AS CUSTOMER_NAME,	
	U1.USERID AS ASSIGNED_USER_ID,	
	U2.USERID AS CREATED_BY_USER_ID,
	CASE A.NONBILLABLE
		WHEN 'Y' THEN 1
		ELSE 0
	END AS NON_BILLABLE,
	A.FROMWHS AS WHS,
	ISNULL(A.MATERIALRECEIPT, 0) AS MATERIALRECEIPT,
	A.STOCKCODE AS ITEM_CODE,
	A.DESCRIPTION AS ITEM_DESCRIPTION,	
	A.QTY AS ITEM_QUANTITY,
	A.COSTINGCODE AS COST_CENTER_ID,
	A.ANALYSIS1 AS ANALYSIS_CODE_1,
	A.ANALYSIS2 AS ANALYSIS_CODE_2,
	A.ACTIVITYID AS ACTIVITY_ID,	
	A.CREATEDATE AS DATE_CREATED,
	A.TRANSDATE AS TRANS_DATE,
	A.SCHEDULEDETAIL AS APPOINTMENT_ID	
	FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES A
	LEFT JOIN ENPRISE_JOBCOST_SUBJOB B ON B.SUBJOBID = A.SUBJOBID
	LEFT JOIN ENPRISE_TSM_USERS U1 ON U1.REFERENCEID = A.EMPID
	LEFT JOIN ENPRISE_TSM_USERS U2 ON U2.REFERENCEID = A.ENTERBY		
	WHERE A.SEQNO = @ID AND A.BATCHNO = 3 AND A.LINETYPE = 'M' AND A.POSTED = 'N';
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETEXPENSE]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETEXPENSE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETEXPENSE]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETEXPENSE]
	@ID	INT
AS
BEGIN
    DECLARE @EMPID INT;
	DECLARE @ENTERBY INT;
	
	DECLARE @TAXTABLE TABLE
	(
		CODE NVARCHAR(20),
		NAME NVARCHAR(100),
		RATE NUMERIC(19,6)
	);
	
	IF EXISTS(SELECT * FROM ENPRISE_JOBCOST_SETUP WHERE USATAX = 'Y')
	BEGIN
		INSERT INTO @TAXTABLE
		(CODE, NAME, RATE)
		SELECT CODE, NAME, RATE 
		FROM OSTC 
		WHERE VALIDFORAR = 'Y'
	END
	ELSE
	BEGIN
		INSERT INTO @TAXTABLE
		(CODE, NAME, RATE)
		SELECT CODE, NAME, RATE 
		FROM OVTG 
		WHERE CATEGORY = 'O'
	END;
	
	SELECT A.SEQNO AS ID,
	A.SUBJOBID AS SUB_JOB_ID,
	B.JOBID AS JOB_ID,
	B.CARDCODE AS CUSTOMER_ID,
	B.CARDNAME AS CUSTOMER_NAME,	
	U1.USERID AS ASSIGNED_USER_ID,	
	U2.USERID AS CREATED_BY_USER_ID,
	CASE A.NONBILLABLE
		WHEN 'Y' THEN 1
		ELSE 0
	END AS NON_BILLABLE,
	CASE A.EXPREIMBURSE
		WHEN 'Y' THEN 1
		ELSE 0
	END AS REIMBURSEABLE,
	A.STOCKCODE AS ITEM_CODE,
	A.DESCRIPTION AS ITEM_DESCRIPTION,
	A.QTY AS ITEM_QUANTITY,
	A.AVECOST AS ITEM_PRICE,
	A.CURRCODE AS ITEM_CURRENCY,
	ISNULL(A.FXRATE, 1) AS ITEM_FX_RATE,
	TX.CODE AS ITEM_TAX_CODE,
	ISNULL(TX.RATE, 0) AS ITEM_TAX_PERCENT,
	CASE A.COSTTAXINC  
		WHEN 'Y' THEN 1
		ELSE 0
	END AS ITEM_TAX_INCLUSIVE,
	ISNULL(A.AVECOST, 0) + ISNULL(A.COSTTAXSUM, 0) AS ITEM_TOTAL,
	A.COSTTAXSUM AS ITEM_TAX_AMOUNT,
	A.COSTINGCODE AS COST_CENTER_ID,
	A.ANALYSIS1 AS ANALYSIS_CODE_1,
	A.ANALYSIS2 AS ANALYSIS_CODE_2,
	A.ACTIVITYID AS ACTIVITY_ID,
	A.CREATEDATE AS DATE_CREATED,
	A.TRANSDATE AS DATE_PURCHASED,
	A.SCHEDULEDETAIL AS APPOINTMENT_ID
	FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES A
	LEFT JOIN ENPRISE_JOBCOST_SUBJOB B ON B.SUBJOBID = A.SUBJOBID	
	LEFT JOIN ENPRISE_JOBCOST_SETUP E ON E.WIPWHS != '-9999'
	LEFT JOIN ENPRISE_TSM_USERS U1 ON U1.REFERENCEID = A.EMPID
	LEFT JOIN ENPRISE_TSM_USERS U2 ON U2.REFERENCEID = A.ENTERBY
	LEFT JOIN @TAXTABLE AS TX ON TX.CODE = 
		CASE 
			WHEN A.TAXCODE IS NULL OR LEN(A.TAXCODE) = 0 THEN B.TAXCODE 
			ELSE A.TAXCODE 
		END	
	WHERE A.SEQNO = @ID AND A.BATCHNO = 2 AND A.LINETYPE = 'M' AND A.POSTED = 'N';
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETATTACHMENTS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETATTACHMENTS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETATTACHMENTS]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETATTACHMENTS]
	@APPOINTMENTID INT, 
	@SUBJOBID INT, 
	@EXPENSEID INT, 
	@MATERIALID INT, 
	@TIMESHEETENTRYID INT
AS
BEGIN
	DECLARE @ANYWHERE_OFFSET INT = 3000;

	DECLARE @MATERIAL_ENTRY INT = 1001 + @ANYWHERE_OFFSET;
	DECLARE @TIMESHEET_ENTRY INT = 1002 + @ANYWHERE_OFFSET;
	DECLARE @EXPENSES_ENTRY INT = 1014 + @ANYWHERE_OFFSET;	
	DECLARE @APPOINTMENTS_ENTRY INT = 3400;
		
	-- return any attached documents for the subjob or scheduled work, or any documents related to the scheduled work
	SELECT A.SEQNO AS ID, 
	A.LINK AS FILENAME,
	A.DESCRIPTION,   
	A.DATEATTACHED AS DATE_ATTACHED,
	A.DOCGROUP AS DOC_GROUP_ID, 
	B.DESCRIPTION AS DOC_GROUP_TITLE     
	FROM ENPRISE_JOBCOST_DOCUMENT A
	LEFT JOIN ENPRISE_JOBCOST_DOCUMENT_GROUPS B ON B.SEQNO = A.DOCGROUP     
	LEFT JOIN ENPRISE_JOBCOST_TEMPBATCHJOBLINES C ON C.SEQNO = A.OBJSEQNO AND A.OBJTYPE IN (@MATERIAL_ENTRY, @TIMESHEET_ENTRY, @EXPENSES_ENTRY)  	  	
	WHERE (A.ISLINK = 'N') AND
	(
	 (A.SUBJOBID = @SUBJOBID AND @APPOINTMENTID IS NULL) OR      
	 (A.OBJSEQNO = @APPOINTMENTID AND A.OBJTYPE = @APPOINTMENTS_ENTRY) OR        
	 (A.OBJSEQNO = @EXPENSEID AND A.OBJTYPE = @EXPENSES_ENTRY) OR 
	 (A.OBJSEQNO = @MATERIALID AND A.OBJTYPE = @MATERIAL_ENTRY) OR
	 (A.OBJSEQNO = @TIMESHEETENTRYID AND A.OBJTYPE = @TIMESHEET_ENTRY) OR
	 (C.BATCHNO = @TIMESHEETENTRYID) OR    
	 (C.SCHEDULEDETAIL = @APPOINTMENTID AND (@SUBJOBID IS NULL OR A.SUBJOBID = @SUBJOBID))
	);
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_GETATTACHMENT]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_GETATTACHMENT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_GETATTACHMENT]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_GETATTACHMENT]
	@ID	INT 
AS
BEGIN
	SELECT A.SEQNO AS ID,
	A.LINK AS FILENAME,
	A.DESCRIPTION,   
    A.DATEATTACHED AS DATE_ATTACHED,
    A.DOCGROUP AS DOC_GROUP_ID,
    A.ATTACHMENT AS FILE_CONTENT   
	FROM ENPRISE_JOBCOST_DOCUMENT A
	WHERE A.SEQNO = @ID;
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_CHANGETIMESHEETENTRYSTATUS]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_CHANGETIMESHEETENTRYSTATUS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_CHANGETIMESHEETENTRYSTATUS]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_CHANGETIMESHEETENTRYSTATUS]
	@TIMESHEET_ENTRY_IDS VARCHAR(500), 
	@PROJECT_MANAGER_ID	INT, 
	@STATUS	INT,
	@SUCCESS BIT OUTPUT,
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE NVARCHAR(100) OUTPUT
AS
BEGIN
	DECLARE @TBL_TIMESHEETIDS TABLE(ID INT);
	DECLARE @XML_TIMESHEETIDS XML;
	DECLARE @UPDATECOUNT INT;
	DECLARE @IDCOUNT INT;
	
	-- set default values for output parameters
	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;				

	-- Get ID's from xml
	IF (@TIMESHEET_ENTRY_IDS IS NOT NULL AND LEN(@TIMESHEET_ENTRY_IDS) > 0)
	BEGIN
		SET @XML_TIMESHEETIDS = '<xml><e>' + REPLACE(@TIMESHEET_ENTRY_IDS,',','</e><e>') + '</e></xml>';

		-- Get ID's from xml
		INSERT INTO @TBL_TIMESHEETIDS(ID)
		SELECT N.value('.', 'INT')
		FROM @XML_TIMESHEETIDS.nodes('xml/e') AS T(N);
	END
	ELSE
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 2;
		SET @ERROR_MESSAGE = 'NO TIMESHEET IDS SPECIFIED';
		RETURN;
	END;
	
	SELECT @IDCOUNT = COUNT(*) FROM @TBL_TIMESHEETIDS;
	
	IF (@STATUS = 1)
	BEGIN
		-- change status to unsubmitted
		UPDATE A
		SET SUBMITTED = 'N'
		FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES A
		JOIN @TBL_TIMESHEETIDS T ON T.ID = A.SEQNO
		WHERE A.POSTED = 'N';
		
		SET @UPDATECOUNT = @@ROWCOUNT;		
	END
	ELSE IF (@STATUS = 2)
	BEGIN
		-- change status to submitted
		UPDATE A
		SET SUBMITTED = 'Y'
		FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES A
		JOIN @TBL_TIMESHEETIDS T ON T.ID = A.SEQNO
		WHERE A.POSTED = 'N';	
		
		SET @UPDATECOUNT = @@ROWCOUNT;											
	END
	ELSE IF (@STATUS = 3)
	BEGIN
		IF @PROJECT_MANAGER_ID IS NULL
		BEGIN
			SET @SUCCESS = 0;
			SET @ERROR_NUMBER = 4;
			SET @ERROR_MESSAGE = 'NO PROJECT MANAGER SPECIFIED';		
			RETURN;
		END;
		
		-- change status to approved
		UPDATE A
		SET APPROVED = 'Y', APPROVEDBY = @PROJECT_MANAGER_ID, APPROVEDDATE = GETDATE()
		FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES A
		JOIN @TBL_TIMESHEETIDS T ON T.ID = A.SEQNO
		WHERE A.POSTED = 'N';
		
		SET @UPDATECOUNT = @@ROWCOUNT;						
	END
	ELSE
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 5;
		SET @ERROR_MESSAGE = 'UNKNOWN STATUS ' + CAST(@STATUS AS VARCHAR(15));
		RETURN;
	END;
	
	IF (@UPDATECOUNT = 0)
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 1;
		SET @ERROR_MESSAGE = 'NO TIMESHEETS UPDATED';
		RETURN;
	END
	ELSE IF (@UPDATECOUNT < @IDCOUNT)
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 3;
		SET @ERROR_MESSAGE = 'PARTIAL UPDATE HAS OCCURRED - ' + CAST(@UPDATECOUNT AS VARCHAR(10)) + ' / ' + CAST(@IDCOUNT AS VARCHAR(10)) + ' TIMESHEETS UPDATED ';
		RETURN;
	END;
		
	SET @SUCCESS = 1;
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_DELETETIMESHEETENTRY]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_DELETETIMESHEETENTRY]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_DELETETIMESHEETENTRY]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_DELETETIMESHEETENTRY]
	@ID INT,
	@SUCCESS BIT OUTPUT,
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE	NVARCHAR(100) OUTPUT
AS
BEGIN
	DECLARE @NOTENO INT;
	DECLARE @ANYWHERE_OFFSET INT = 3000;		
	DECLARE @TIMESHEET_ENTRY INT = 1002 + @ANYWHERE_OFFSET;	
	
	-- set default values for output parameters
	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;
	
	SELECT @NOTENO = NOTENO
	FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES
	WHERE SEQNO = @ID;
	
	DELETE FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES
	WHERE SEQNO = @ID AND APPROVED <> 'Y' AND POSTED <> 'Y';
	
	IF (@@ROWCOUNT = 0)
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 1;
		SET @ERROR_MESSAGE = 'INVALID TIMESHEET ID OR TIMESHEET APPROVED OR POSTED';
		RETURN;		
	END;
	
	IF ISNULL(@NOTENO,0) > 0 
	BEGIN
		DELETE FROM ENPRISE_JOBCOST_TEMPBATCHNOTES
		WHERE SEQNO = @NOTENO;
	END;
	
	-- delete any attachments
	DELETE FROM ENPRISE_JOBCOST_DOCUMENT
	WHERE OBJTYPE = @TIMESHEET_ENTRY AND OBJSEQNO = @ID;	
	
	SET @SUCCESS = 1;
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_DELETEMATERIAL]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_DELETEMATERIAL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_DELETEMATERIAL]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_DELETEMATERIAL]
	@ID	INT,
	@SUCCESS	BIT OUTPUT,
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE NVARCHAR(100) OUTPUT
AS
BEGIN
	DECLARE @NOTENO INT
	DECLARE @ANYWHERE_OFFSET INT = 3000;	
	DECLARE @MATERIAL_ENTRY INT = 1001 + @ANYWHERE_OFFSET;
	

	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;
	
	SELECT @NOTENO = NOTENO
	FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES
	WHERE SEQNO = @ID;

	DELETE
	FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES
	WHERE SEQNO = @ID AND APPROVED <> 'Y' AND POSTED <> 'Y';
	
	IF @@ROWCOUNT = 0
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 1;
		SET @ERROR_MESSAGE = 'MATERIAL LINE NOT FOUND OR MATERIAL LINE APPROVED OR POSTED';
		RETURN;
	END

	IF ISNULL(@NOTENO,0) > 0 
	BEGIN
		DELETE FROM ENPRISE_JOBCOST_TEMPBATCHNOTES
		WHERE SEQNO = @NOTENO;
	END;
	
	-- delete any attachments
	DELETE FROM ENPRISE_JOBCOST_DOCUMENT
	WHERE OBJTYPE = @MATERIAL_ENTRY AND OBJSEQNO = @ID;	
	
	SET @SUCCESS = 1;
END

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_DELETEEXPENSE]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_DELETEMATERIAL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_DELETEMATERIAL]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_DELETEMATERIAL]
	@ID	INT,
	@SUCCESS	BIT OUTPUT,
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE NVARCHAR(100) OUTPUT
AS
BEGIN
	DECLARE @NOTENO INT
	DECLARE @ANYWHERE_OFFSET INT = 3000;	
	DECLARE @MATERIAL_ENTRY INT = 1001 + @ANYWHERE_OFFSET;
	

	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;
	
	SELECT @NOTENO = NOTENO
	FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES
	WHERE SEQNO = @ID;

	DELETE
	FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES
	WHERE SEQNO = @ID AND APPROVED <> 'Y' AND POSTED <> 'Y';
	
	IF @@ROWCOUNT = 0
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 1;
		SET @ERROR_MESSAGE = 'MATERIAL LINE NOT FOUND OR MATERIAL LINE APPROVED OR POSTED';
		RETURN;
	END

	IF ISNULL(@NOTENO,0) > 0 
	BEGIN
		DELETE FROM ENPRISE_JOBCOST_TEMPBATCHNOTES
		WHERE SEQNO = @NOTENO;
	END;
	
	-- delete any attachments
	DELETE FROM ENPRISE_JOBCOST_DOCUMENT
	WHERE OBJTYPE = @MATERIAL_ENTRY AND OBJSEQNO = @ID;	
	
	SET @SUCCESS = 1;
END

GO


/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_DELETEATTACHMENT]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_DELETEATTACHMENT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_DELETEATTACHMENT]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_DELETEATTACHMENT]
	@ID	INT,
	@SUCCESS	BIT OUTPUT,
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE NVARCHAR(100) OUTPUT	
AS
BEGIN
	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;

	DELETE FROM ENPRISE_JOBCOST_DOCUMENT
	WHERE SEQNO = @ID;
	
	IF @@ROWCOUNT = 0
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 1;
		SET @ERROR_MESSAGE = 'ATTACHMENT NOT FOUND';
		RETURN;
	END;

	SET @SUCCESS = 1;
END;

GO

/****** Object:  StoredProcedure [dbo].[SP_ENPRISE_EA_DELETEAPPOINTMENT]    Script Date: 06/02/2015 11:57:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_ENPRISE_EA_DELETEAPPOINTMENT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_ENPRISE_EA_DELETEAPPOINTMENT]
GO

CREATE PROCEDURE [dbo].[SP_ENPRISE_EA_DELETEAPPOINTMENT]
	@ID	INT,
	@SUCCESS BIT OUTPUT,
	@ERROR_NUMBER INT OUTPUT,
	@ERROR_MESSAGE NVARCHAR(100) OUTPUT
AS
BEGIN
	DECLARE @APPOINTMENTS_LINKED_TO_HEADER INT;
	DECLARE @DESCRIPTIONSEQNO INT;
	DECLARE @INTNOTESEQNO INT;
	DECLARE @NOTESEQNO INT;
	DECLARE @SCHEDULINGID INT;
	DECLARE @APPOINTMENTS_ENTRY INT = 3400;
	
	SET @ERROR_NUMBER = 0;
	SET @ERROR_MESSAGE = NULL;
	
	SELECT @SCHEDULINGID = SCHEDULINGID, @DESCRIPTIONSEQNO = DESCRIPTIONSEQNO, 
	@INTNOTESEQNO = INTERNALNOTESEQNO, @NOTESEQNO = NOTESEQNO
	FROM ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS
	WHERE SEQNO = @ID;
		
	SELECT @APPOINTMENTS_LINKED_TO_HEADER = COUNT(*)
	FROM ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS A	
	WHERE A.SCHEDULINGID = @SCHEDULINGID;
	
	-- null the link from TempBatchJobLines to JobSchedulingDetails
	UPDATE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
	SET SCHEDULEDETAIL = NULL
	WHERE SCHEDULEDETAIL = @ID;
	
	DELETE FROM ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS
	WHERE SCHEDULEDETAILSID = @ID;
	
	DELETE FROM ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS
	WHERE SEQNO = @ID;
	
	IF (@@ROWCOUNT = 0)
	BEGIN
		SET @SUCCESS = 0;
		SET @ERROR_NUMBER = 1;
		SET @ERROR_MESSAGE = 'INVALID ID - APPOINTMENT DOES NOT EXIST';
		RETURN;
	END;
	
	-- delete all related notes
	IF (@DESCRIPTIONSEQNO IS NOT NULL)
	BEGIN
		DELETE FROM ENPRISE_JOBCOST_NOTES
		WHERE SEQNO = @DESCRIPTIONSEQNO;
	END;
	
	IF (@INTNOTESEQNO IS NOT NULL)
	BEGIN
		DELETE FROM ENPRISE_JOBCOST_NOTES
		WHERE SEQNO = @INTNOTESEQNO;
	END;
	
	IF (@NOTESEQNO IS NOT NULL)
	BEGIN
		DELETE FROM ENPRISE_JOBCOST_NOTES
		WHERE SEQNO = @NOTESEQNO;
	END;
	
	-- delete any attachments
	DELETE FROM ENPRISE_JOBCOST_DOCUMENT
	WHERE OBJTYPE = @APPOINTMENTS_ENTRY AND OBJSEQNO = @ID;
	
	-- delete the header record if there is only one appointment linked to it
	IF (@APPOINTMENTS_LINKED_TO_HEADER = 1)
	BEGIN
		DELETE FROM ENPRISE_JOBCOST_JOBSCHEDULING
		WHERE SEQNO = @SCHEDULINGID;
		
		IF (@@ROWCOUNT = 0)
		BEGIN
			SET @SUCCESS = 0;
			SET @ERROR_NUMBER = 2;
			SET @ERROR_MESSAGE = 'UNABLE TO DELETE LINKED HEADER';
			RETURN;
		END;
	END;
	
	SET @SUCCESS = 1;
END;

GO

