
/****ENPRISE_TSM_USERS*******************************************************************************************************************************************************/
IF (SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'ENPRISE_TSM_USERS') = 1
BEGIN
       IF NOT EXISTS(
                SELECT *
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE COLUMN_NAME = 'CREATEDATE' 
                AND TABLE_NAME = 'ENPRISE_TSM_USERS')
       BEGIN
              ALTER TABLE ENPRISE_TSM_USERS
              ADD CREATEDATE DATETIME DEFAULT GETDATE(),
              CREATEDBY INT,
              LASTLOGINDATE DATETIME;
           
              ALTER TABLE ENPRISE_TSM_USERS 
                ADD CONSTRAINT FK_ENPRISE_TSM_USERS_CREATEDBY FOREIGN KEY (CREATEDBY) 
                REFERENCES ENPRISE_TSM_USERS(USERID);
       END 
END


GO

/****ENPRISE_JOBCOST_DOCUMENT*******************************************************************************************************************************************************/
IF (SELECT  COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME ='ENPRISE_JOBCOST_DOCUMENT' AND COLUMN_NAME ='JOBID' AND IS_NULLABLE = 'YES') = 0
BEGIN
      ALTER TABLE ENPRISE_JOBCOST_DOCUMENT
      ALTER COLUMN JOBID INT NULL;
      
      ALTER TABLE ENPRISE_JOBCOST_DOCUMENT
      ALTER COLUMN DOCGROUP INT NULL;
      
END

GO


IF (SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'OBJTYPE' AND TABLE_NAME = 'ENPRISE_JOBCOST_DOCUMENT') = 0
BEGIN
      ALTER TABLE ENPRISE_JOBCOST_DOCUMENT
      ADD OBJTYPE INT,
      OBJSEQNO INT;
      
        
        CREATE INDEX IX_ENPRISE_JOBCOST_DOCUMENT_OBJTYPE ON ENPRISE_JOBCOST_DOCUMENT(OBJTYPE);        
        CREATE INDEX IX_ENPRISE_JOBCOST_DOCUMENT_OBJSEQNO ON ENPRISE_JOBCOST_DOCUMENT(OBJSEQNO);       
END

GO


/*****ENPRISE_JOBCOST_SIGNOFF******************************************************************************************************************************************************/

IF (SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'ENPRISE_JOBCOST_SIGNOFF') = 0
BEGIN

      CREATE TABLE ENPRISE_JOBCOST_SIGNOFF(
            SEQNO INT IDENTITY (1, 1) PRIMARY KEY NOT NULL,
            OBJECTTYPE INT NOT NULL,
            OBJECTSEQNO INT NOT NULL,
            SIGNEDBY NVARCHAR(100),
            SIGNATURE IMAGE,  
            SIGNATUREDATE DATETIME,
            RESOLUTION NTEXT
      );

      CREATE INDEX IX_ENPRISE_JOBCOST_SIGNOFF_OBJECTTYPE ON ENPRISE_JOBCOST_SIGNOFF(OBJECTTYPE);
      CREATE INDEX IX_ENPRISE_JOBCOST_SIGNOFF_OBJECTSEQNO ON ENPRISE_JOBCOST_SIGNOFF(OBJECTSEQNO);

END

GO



/*********ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS**************************************************************************************************************************************************/


IF (SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS') = 0
BEGIN
      CREATE TABLE ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS(
            SEQNO INT IDENTITY(1,1),
            SCHEDULEDETAILSID INT NOT NULL,
            SUBJOBID INT NOT NULL,
            CONSTRAINT FK_ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS_SCHEDULEDETAILSID FOREIGN KEY ( SCHEDULEDETAILSID ) REFERENCES ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS ( SEQNO ), 
                  CONSTRAINT FK_ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS_SUBJOBID FOREIGN KEY ( SUBJOBID ) REFERENCES ENPRISE_JOBCOST_SUBJOB ( SUBJOBID ), 
                  CONSTRAINT UK_ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS_SCHEDULEDETAILID_SUBJOBID UNIQUE NONCLUSTERED (SCHEDULEDETAILSID, SUBJOBID)
      );
      
     CREATE INDEX IX_ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS_SCHEDULEDETAILSID ON ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS(SCHEDULEDETAILSID);
     CREATE INDEX IX_ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS_SUBJOBID ON ENPRISE_JOBCOST_JOBSCHEDULING_SUBJOBS(SUBJOBID);
END

GO

/*********ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS**************************************************************************************************************************************************/

IF (SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'DESCRIPTIONSEQNO' AND TABLE_NAME = 'ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS') = 0
BEGIN
      ALTER TABLE ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS
              ADD DESCRIPTIONSEQNO INT,
              INTERNALNOTESEQNO INT,
              ADDRESS1 NVARCHAR(254),
              ADDRESS2 NVARCHAR(254),   
              ADDRESS3 NVARCHAR(254),   
              ADDRESS4 NVARCHAR(254),   
              ADDRESS5 NVARCHAR(254),   
              ADDRESS6 NVARCHAR(254),
              CONSTRAINT FK_ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS_DESCRIPTIONSEQNO FOREIGN KEY ( DESCRIPTIONSEQNO ) REFERENCES ENPRISE_JOBCOST_NOTES ( SEQNO ), 
              CONSTRAINT FK_ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS_INTERNALNOTESEQNO FOREIGN KEY ( INTERNALNOTESEQNO ) REFERENCES ENPRISE_JOBCOST_NOTES ( SEQNO );
     
END

GO

IF (SELECT COUNT(COL_NAME(B.[ID], B.COLID)) 
      FROM SYSINDEXES AS A  
      JOIN SYSINDEXKEYS AS B ON A.[ID] = B.[ID] AND A.INDID = B.INDID 
    WHERE A.[ID] = OBJECT_ID('ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS') 
    AND COL_NAME(B.[ID], B.COLID) = 'SCHEDULINGID') = 0
BEGIN
      CREATE INDEX IX_ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS_SCHEDULINGID ON ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS(SCHEDULINGID);
END

GO


DECLARE @SQL NVARCHAR(2000);

IF (SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CREATEDATE' AND TABLE_NAME = 'ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS') = 0
BEGIN
    ALTER TABLE ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS
    ADD CREATEDATE DATETIME DEFAULT(GETDATE());
    
    SET @SQL = 'UPDATE ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS SET CREATEDATE = GETDATE() WHERE CREATEDATE IS NULL';  
    EXEC SP_EXECUTESQL @SQL;
END

GO

/*********dbo.SUBJOBLIST **************************************************************************************************************************************************/
IF TYPE_ID('SUBJOBLIST') IS NULL
BEGIN
    CREATE TYPE dbo.SUBJOBLIST AS TABLE
       (
       SUBJOBID INT
       );
END


GO

/*********ENPRISE_JOBCOST_TEMPBATCHJOBLINES**************************************************************************************************************************************************/
IF (SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'SCHEDULEDETAIL' AND TABLE_NAME = 'ENPRISE_JOBCOST_TEMPBATCHJOBLINES') = 0
BEGIN
       ALTER TABLE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
       ADD SCHEDULEDETAIL INT,            
       CONSTRAINT FK_ENPRISE_JOBCOST_TEMPBATCHJOBLINES_SCHEDULEDETAIL FOREIGN KEY (SCHEDULEDETAIL) 
              REFERENCES ENPRISE_JOBCOST_JOBSCHEDULINGDETAILS (SEQNO);
        
       CREATE INDEX IX_ENPRISE_JOBCOST_TEMPBATCHJOBLINES_SCHEDULEDETAIL ON ENPRISE_JOBCOST_TEMPBATCHJOBLINES(SCHEDULEDETAIL);                
END

GO

/*********ENPRISE_EA_TEMPBATCHJOBLINESVIEW**************************************************************************************************************************************************/
IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ENPRISE_EA_TEMPBATCHJOBLINESVIEW') > 0
BEGIN
       DROP VIEW ENPRISE_EA_TEMPBATCHJOBLINESVIEW;
END

GO

DECLARE @SQL NVARCHAR(4000);

SET @SQL = 
'CREATE VIEW ENPRISE_EA_TEMPBATCHJOBLINESVIEW ' +
'AS ' +
'SELECT L.SEQNO, L.SUBJOBID, S.JOBTITLE, L.STOCKCODE, L.DESCRIPTION, S.JOBID, L.QTY, L.AVECOST, ISNULL(N.NOTE,'''') NOTES,  ' +
'ISNULL(L.ANALYSIS1,0) ANALYSIS1ID, ISNULL(A1.DESCRIPTION,'''') ANALYSIS1NAME, ' +
'ISNULL(L.ANALYSIS2,0) ANALYSIS2ID, ISNULL(A2.DESCRIPTION,'''') ANALYSIS2NAME, ' +
'L.NONBILLABLE, L.FROMWHS, W.WHSNAME, L.ENTERBY USERID, U.NAME USERFULLNAME, L.CREATEDATE,  ' +
'L.TRANSDATE, L.STARTDATE, L.POSTED, L.BATCHNO, L.LINETYPE, I.MANSERNUM, O.CARDCODE, O.CARDNAME,  ' +
'ISNULL(L.SUBMITTED, ''N'') AS SUBMITTED, L.APPROVED, L.ACTIVITYID, ' +
'ISNULL(U.SUBMITREQ, 0) AS ENTEREDBYSUBMITREQ, ISNULL(U.TIMESHEETAPPR, 0) AS ENTEREDBYTIMESHEETAPPR, ' +
'ISNULL(U.EXPENSEAPPR, 0) AS ENTERBYEXPENSEAPPR, ISNULL(U.EXPENSEMIN, 0) AS ENTERBYEXPENSEMIN,                    ' +
'ISNULL(L.EXPREIMBURSE, ''N'') AS EXPREIMBURSE, ISNULL(L.EXPREIMBURSECARDCODE, '''') AS EXPREIMBURSECARDCODE,  ' +
'ISNULL(U.EXPREIMBURSECARDCODE, '''') AS ENTEREDBYEXPREIMBURSECARDCODE,  ' +
'ISNULL(L.EXPFXCOST, L.AVECOST) AS EXPFXCOST, ISNULL(L.EXPFXRATE, 1) AS EXPFXRATE, ISNULL(L.EXPCURRCODE, '''') AS EXPCURRCODE,  ' +
'ISNULL(L.EXPREIMBURSEOBJTYPE, 0) AS EXPREIMBURSEOBJTYPE, ISNULL(U.EXPREIMBURSEOBJTYPE, 0) AS ENTEREDBYEXPREIMBURSEOBJTYPE,  ' +                                                                                
'L.TAXCODE AS TAXCODE, L.COSTINGCODE AS COSTINGCODE, L.SCHEDULEDETAIL ' +
DBO.ENPRISE_GETTABLEUDFCOLUMNS('ENPRISE_JOBCOST_TEMPBATCHJOBLINES', 'L') +
' FROM ENPRISE_JOBCOST_TEMPBATCHJOBLINES L  ' +
' JOIN ENPRISE_JOBCOST_SUBJOB S ON S.SUBJOBID = L.SUBJOBID ' +
' JOIN ENPRISE_TSM_USERS U ON U.USERID = L.ENTERBY ' +
' LEFT JOIN ENPRISE_JOBCOST_TEMPBATCHNOTES N ON N.SEQNO = L.NOTENO ' +
' LEFT JOIN ENPRISE_JOBCOST_ANALYSIS A1 ON A1.SEQNO = L.ANALYSIS1 ' +
' LEFT JOIN ENPRISE_JOBCOST_ANALYSIS A2 ON A2.SEQNO = L.ANALYSIS2 ' +
' LEFT JOIN OCRD O ON O.CARDCODE = S.CARDCODE ' +
' LEFT JOIN OWHS W ON W.WHSCODE = L.FROMWHS ' +
'LEFT JOIN OITM I ON I.ITEMCODE = L.STOCKCODE';

EXEC SP_EXECUTESQL @SQL;

GO

/*********ENPRISE_EA_ACTIVITIESVIEW**************************************************************************************************************************************************/
IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ENPRISE_EA_ACTIVITIESVIEW') > 0
BEGIN
       DROP VIEW ENPRISE_EA_ACTIVITIESVIEW;
END

GO

CREATE VIEW ENPRISE_EA_ACTIVITIESVIEW
AS
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

GO

/*********ENPRISE_EA_SUBJOBVIEW**************************************************************************************************************************************************/
IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ENPRISE_EA_SUBJOBVIEW') > 0
BEGIN
       DROP VIEW ENPRISE_EA_SUBJOBVIEW;
END

GO

CREATE VIEW ENPRISE_EA_SUBJOBVIEW AS 
SELECT 
C.*,
D.DESCRIPTION AS STATUSDESC, D.EDITABLE AS STATUSEDITABLE,  
E.TYPE AS TYPEDESC, F.FIRSTNAME + ' ' + F.LASTNAME FULLNAME,  
G.CATEGORY AS CATEGORYDESC, 
CASE 
   WHEN OCPR.TEL1 IS NOT NULL THEN OCPR.TEL1 
   WHEN OCPR.TEL2 IS NOT NULL THEN OCPR.TEL2 
   WHEN OCPR.CELLOLAR IS NOT NULL THEN OCPR.CELLOLAR 
   WHEN OCRD.PHONE1 IS NOT NULL THEN OCRD.PHONE1 
   WHEN OCRD.PHONE2 IS NOT NULL THEN OCRD.PHONE2 
   WHEN OCRD.CELLULAR IS NOT NULL THEN OCRD.CELLULAR 
   ELSE NULL 
END AS CONTACTPHONE, 
J.ACTIVE AS JOBACTIVE,
JS.EDITABLE AS JOBSTATUSEDITABLE
FROM ENPRISE_JOBCOST_SUBJOB C  
LEFT JOIN ENPRISE_JOBCOST_STATUS D ON D.SEQNO = C.STATUS  
LEFT JOIN ENPRISE_JOBCOST_JOBTYPE E ON E.SEQNO = C.JOBTYPE 
LEFT JOIN OHEM F ON F.EMPID = C.ENTERBY  
LEFT JOIN ENPRISE_JOBCOST_CATEGORY G ON G.SEQNO = C.CATEGORY 
LEFT JOIN OCPR ON OCPR.CARDCODE = C.CARDCODE AND OCPR.CNTCTCODE = C.CONTACTCODE 
LEFT JOIN OCRD ON OCRD.CARDCODE = C.CARDCODE
LEFT JOIN ENPRISE_JOBCOST_JOB J ON J.JOBID = C.JOBID
LEFT JOIN ENPRISE_JOBCOST_STATUS JS ON JS.SEQNO = J.STATUS; 

GO 

/*********ENPRISE_JOBCOST_EA_CAPTIONS**************************************************************************************************************************************************/

IF (SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'ENPRISE_JOBCOST_EA_CAPTIONS') = 0
BEGIN
      CREATE TABLE ENPRISE_JOBCOST_EA_CAPTIONS(
            SEQNO INT IDENTITY(1,1),
            FIELDID VARCHAR(50) NOT NULL,
            CAPTION NVARCHAR(300) NOT NULL                             
      );
      
     CREATE INDEX IX_ENPRISE_JOBCOST_EA_CAPTIONS_FIELDID ON ENPRISE_JOBCOST_EA_CAPTIONS(FIELDID);     
END

GO

IF NOT EXISTS(SELECT 1 FROM SYS.COLUMNS WHERE NAME = 'VIEWNAME' AND OBJECT_ID = OBJECT_ID('ENPRISE_JOBCOST_EA_CAPTIONS'))
BEGIN
   ALTER TABLE ENPRISE_JOBCOST_EA_CAPTIONS
   ADD VIEWNAME NVARCHAR(100);
END

GO

IF NOT EXISTS(SELECT 1 FROM SYS.INDEXES WHERE NAME='IX_ENPRISE_JOBCOST_EA_CAPTIONS_VIEWNAME' AND OBJECT_ID = OBJECT_ID('ENPRISE_JOBCOST_EA_CAPTIONS'))
BEGIN
    CREATE INDEX IX_ENPRISE_JOBCOST_EA_CAPTIONS_VIEWNAME ON ENPRISE_JOBCOST_EA_CAPTIONS(VIEWNAME)
END

GO

IF NOT EXISTS(SELECT 1 FROM ENPRISE_JOBCOST_EA_CAPTIONS WHERE FIELDID = 'SEQNO' AND CAPTION = 'EXPENSE ID' AND VIEWNAME = 'expense-dataentry-en' )
BEGIN
    INSERT INTO ENPRISE_JOBCOST_EA_CAPTIONS
    (FIELDID, CAPTION, VIEWNAME)
    VALUES
    ('SEQNO', 'EXPENSE ID', 'expense-dataentry-en'),
    ('SEQNO', 'EXPENSE ID', 'expense-desktop-list'),
    ('SEQNO', 'EXPENSE ID', 'expense-mobile-list'),
    ('SEQNO', 'MATERIAL ID', 'material-dataentry-e'),
    ('SEQNO', 'MATERIAL ID', 'material-desktop-lis'),
    ('SEQNO', 'MATERIAL ID', 'material-mobile-list'),
    ('STOCKCODE', 'ITEM CODE', NULL),
    ('QTY', 'QUANTITY', NULL),
    ('JOBID', 'JOB ID', NULL),
    ('FROMWHS', 'WHS', NULL),
    ('JOBTITLE', 'JOB TITLE', NULL),
    ('SUBJOBID', 'SUBJOB ID', NULL),
    ('DESCRIPTION', 'DESCRIPTION', NULL),
    ('CREATEDATE', 'DATE CREATED', NULL),
    ('TRANSDATE', 'TRANS DATE', NULL),
    ('NONBILLABLE', 'NON BILLABLE', NULL),
    ('TAXCODE', 'TAX CODE', NULL),
    ('ACTIVITYID', 'ACTIVITY ID', NULL),
    ('STARTDATE', 'START DATE', NULL),
    ('ENDDATE', 'END DATE', NULL),
    ('FXRATE', 'EXCHANGE RATE', NULL),
    ('CURRCODE', 'CURRENCY', NULL),
    ('AVECOST', 'COST PRICE', NULL),
    ('TOTALCOST', 'TOTAL COST', NULL),
    ('STATUSDESC', 'STATUS', NULL),
    ('TYPEDESC', 'TYPE', NULL),
    ('CATEGORYDESC', 'CATEGORY', NULL)
END 

GO

/*********ENPRISE_ADDON_USERS**************************************************************************************************************************************************/

IF (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME = 'ENPRISE_ADDON_USERS'  AND COLUMN_NAME = 'SAPUSER') = 30
BEGIN
       ALTER TABLE ENPRISE_ADDON_USERS 
       ALTER COLUMN SAPUSER NVARCHAR(50) 
END;

GO

/*********ENPRISE_JOBCOST_TEMPBATCHJOBLINES**************************************************************************************************************************************************/

IF (SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MATERIALRECEIPT' AND TABLE_NAME = 'ENPRISE_JOBCOST_TEMPBATCHJOBLINES') = 0
BEGIN
    ALTER TABLE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
    ADD MATERIALRECEIPT INT;        
END

GO

IF (SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'SHOWTAXINCLAVECOST' AND TABLE_NAME = 'ENPRISE_JOBCOST_TEMPBATCHJOBLINES') = 0
BEGIN
      ALTER TABLE ENPRISE_JOBCOST_TEMPBATCHJOBLINES
              ADD SHOWTAXINCLAVECOST NVARCHAR(1) DEFAULT 'N';                   
END

GO

/*********ENPRISE_JOBCOST_STATUS**************************************************************************************************************************************************/
IF NOT EXISTS(
      SELECT *
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE COLUMN_NAME = 'EA_STATUS' 
      AND TABLE_NAME = 'ENPRISE_JOBCOST_STATUS')
BEGIN
    ALTER TABLE ENPRISE_JOBCOST_STATUS
    ADD EA_STATUS INT;        
END 

GO

IF NOT EXISTS(
      SELECT * 
      FROM SYS.INDEXES 
      WHERE NAME='IX_ENPRISE_JOBCOST_STATUS_EA_STATUS' AND OBJECT_ID = OBJECT_ID('ENPRISE_JOBCOST_STATUS')
)
BEGIN
      CREATE UNIQUE NONCLUSTERED INDEX IX_ENPRISE_JOBCOST_STATUS_EA_STATUS
      ON ENPRISE_JOBCOST_STATUS(EA_STATUS)
      WHERE ENPRISE_JOBCOST_STATUS.EA_STATUS IS NOT NULL;
END

GO




