clear_sp.sql

USE [OMNIA_ESUM_P_SUM_CM]
GO

/****** Object:  StoredProcedure [dbo].[SUM_Harmonize_Clear_SP]    Script Date: 8/30/2017 10:28:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[SUM_Harmonize_Clear_SP] as 

truncate table [dbo].[SUM_Harmonize_Clear] 


insert  into 
 [dbo].[SUM_Harmonize_Clear] 

SELECT
ab.TroubleTicketNumber,
cd.AccountCode, 
cd.AccountID,
cd.CustomerName,
cd.CustomerType,
cd.WireCenter,
cd.Address, 
cd.City, 
cd.TroubleTicketCreateDate,
cd.TroubleTicketCreateBy,
cd.ReservationNumber, 
cd.ReservationTime, 
cd.ReservationCreateTime,
cd.JobCode,
cd.FoundCode, 
cd.CauseCode,
cd.TroubleType,
cd.ReportedTrouble,
cd.TroubleComments,
trr.Resolution as ClearedComments,
ab.ResolutionComment as ReservationResolutionComments,
ab.ClosedDate,
ab.ClosedTime

  FROM [dbo].[SUM_HeatIntegrationClosedReservations] ab
  left JOIN  [dbo].[SUM_Harmonize_Create] cd on ab.TroubleTicketNumber=cd.TroubleTicketNumber
  left join [OMNIA_ESUM_P_SUM_CM].[dbo].TROUBLE trr on cd.TroubleTicketNumber=trr.ReportNum
  left join 
  
  (SELECT TROUBLE_ReportNum as TroubleTicketNumber, 
     STUFF(
         (SELECT DISTINCT  
          text+'//    ',Id
          FROM [OMNIA_ESUM_P_SUM_CM].[dbo].[TROUBLEDELTA]
          WHERE TROUBLE_ReportNum = a.TROUBLE_ReportNum 
          order by [Id]
FOR XML PATH (''))
          , 1, 0, '')  AS TroubleComments
FROM [OMNIA_ESUM_P_SUM_CM].[dbo].[TROUBLEDELTA] AS a
GROUP BY TROUBLE_ReportNum)tr
on cd.TroubleTicketNumber=tr.TroubleTicketNumber




GO

create_sp.sql

USE [OMNIA_ESUM_P_SUM_CM]
GO

/****** Object:  StoredProcedure [dbo].[SUM_Harmonize_Create_SP]    Script Date: 8/30/2017 10:28:15 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE Procedure [dbo].[SUM_Harmonize_Create_SP] as
truncate table SUM_Harmonize_Create

insert into SUM_Harmonize_Create 
select distinct
tt.ReportNum as TroubleTicketNumber, 
tt.AccountCode,
tt.AccountID,
nc.Name as CustomerName,
cwr.[Customer Type] AS CustomerType,
wr.WireCenter,
tt.Address,
tt.City,
tt.CreateDate as TroubleTicketCreateDate,
tt.CreateBy as TroubleTicketCreateBy,
dd.[RSVR #] as ReservationNumber,
dd.ReservationTime,
dd.ReservationCreateTime,
dd.[Job Code] AS JobCode,
tt.FoundCode,
tt.CauseCode,
tt.Type as TroubleType,
tt.ReportedTrouble,
tr.TroubleComments

from 
OasisFMWeb_TT_TroubleTicketList tt
left join ADDRESSAddress a
on tt.Address=a.Address
inner join 
SrvLocation sl on sl.LocationID=a.LocationID
inner join
PrdWireCenter wr on sl.WireCenterID=wr.WireCenterID
left join SUM_CustomerTypesByWirecenter cwr on wr.WireCenterID=cwr.WireCenterID

left join 

(SELECT     dbo.CusAccountContact.AccountID, ISNULL(dbo.NamCorporation.Corporation, ISNULL(dbo.NamContact.[First] + ' ', '') + 
         ISNULL(dbo.NamContact.Middle + ' ', '') + ISNULL(dbo.NamContact.[Last], '')) AS Name
FROM         dbo.NamContact WITH (NOLOCK) INNER JOIN
                      dbo.CusAccountContact WITH (NOLOCK) ON dbo.NamContact.ContactID = dbo.CusAccountContact.ContactID LEFT OUTER JOIN
                      dbo.NamCorporation WITH (NOLOCK) ON dbo.NamContact.CorporationID = dbo.NamCorporation.CorporationID                 
                      where ContactClass='B')nc
on tt.AccountID=nc.AccountID

left join 


(SELECT [RSVR #],fm.TroubleLink as TroubleTicketNumber,fm.[Customer Name],fm.Address
,fm.City,fm.State,fm.Time as ReservationTime,fm.[Job Code],fm.TechID,fm.Technician,fm.[Created By],
CAST(fm.[Create Date] as date) as ReservationCreateTime,fm.wfWORKGROUP_Id,fm.WorkGroup,fm.Account_Id,
fm.ReportedTrouble,FoundCode,CauseCode
  FROM [OMNIA_ESUM_P_SUM_CM].[dbo].[SUM_FM_Reservations]fm)dd
  on tt.ReportNum=dd.TroubleTicketNumber


  left join

  (SELECT TROUBLE_ReportNum as TroubleTicketNumber, 
     STUFF(
         (SELECT DISTINCT  
          text+'//    ',Id
          FROM [OMNIA_ESUM_P_SUM_CM].[dbo].[TROUBLEDELTA]
          WHERE TROUBLE_ReportNum = a.TROUBLE_ReportNum 
          order by [Id]
FOR XML PATH (''))
          , 1, 0, '')  AS TroubleComments
FROM [OMNIA_ESUM_P_SUM_CM].[dbo].[TROUBLEDELTA] AS a
GROUP BY TROUBLE_ReportNum)tr
on tt.ReportNum=tr.TroubleTicketNumber











GO