alter FUNCTION [dbo].[FN_CDMS_AuditTrailXML_Settlement](@ID INT,@ReasonForChange VARCHAR(1024) = ''

)RETURNS VARCHAR(MAX)/*

154366-SS-29032012-CDMS-Audit-Trail-Enhancement156249 DKG 20120418 Added the new Columns.157244-UG-20120426-updated the query to display SttlmtCompanyID193836-VKG-08232013-R2 P2.2-Document-Repository-Region-Enforcement-Objec

t-ID-naming199497-PRK-20132511-Modify to use new fields of table CDMS_Settlement198989-AK-20131220 CDMS - R2 P3 S2 - Remove Program Year/Update Requirem

ent reference from Violation*/

BEGIN

DECLARE @AuditTrailXML VARCHAR(MAX)

DECLARE @SttlmtCompanyID INT,@RegionID INT

SELECT @SttlmtCompanyID = ST.CompanyID,@RegionID = C.RegionID

FROM CDMS_Settlement STINNER JOIN APPL_Company C WITH (NOLOCK) ON C.CompanyID = ST.CompanyIDWHERE SettlementID = @ID

DECLARE @EntitycomList VARCHAR(MAX)SET @EntitycomList = ''SELECT @EntitycomList = @EntitycomList + CASE WHEN @EntitycomList != '' THEN '' ELSE '' END + '- CommentID: ' + CONVERT(VARCHAR(100),c.CommentID) + ' Updated On: ' + CONVERT(VARCHAR(24),c.UpdateDT) + ' GMT By User: ' + u.UserLastName + ', ' + u.UserFirstName + ' (' + u.UserName + ')'FROM dbo.CDMS_Comment cINNER JOIN dbo.APPL_Users u WITH (NOLOCK) ON u.UserID = c.UpdateUserIDWHERE c.ParentInstanceID = @IDAND c.ParentObjectID = (SELECT ParentObjectID FROM dbo.CDMS_ParentObject WHERE ParentObjectCode = 'Settlement')AND c.CommentActiveFlag = 1AND c.IsRegionalCommentFlag = 0ORDER BY c.CommentID

DECLARE @RegionalcomList VARCHAR(MAX)SET @RegionalcomList = ''SELECT @RegionalcomList = @RegionalcomList + CASE WHEN @RegionalcomList != '' THEN '' ELSE '' END + '- CommentID: ' + CONVERT(VARCHAR(100),c.CommentID) + ' Updated On: ' + CONVERT(VARCHAR(24),c.UpdateDT) + ' GMT By User: ' + u.UserLastName + ', ' + u.UserFirstName + ' (' + u.UserName + ')'FROM dbo.CDMS_Comment cINNER JOIN dbo.APPL_Users u WITH (NOLOCK) ON u.UserID = c.UpdateUserIDWHERE c.ParentInstanceID = @IDAND c.ParentObjectID = (SELECT ParentObjectID FROM dbo.CDMS_ParentObject WHERE ParentObjectCode = 'Settlement')AND c.CommentActiveFlag = 1AND c.IsRegionalCommentFlag = 1ORDER BY c.CommentID

DECLARE @EntitydocList VARCHAR(MAX)

SET @EntitydocList = ''SELECT @EntitydocList = @EntitydocList + CASE WHEN @EntitydocList != '' THEN '' ELSE '' END + '- DocumentID: ' + CONVERT(VARCHAR(100),d.DocID) + ' Updated On: ' + CONVERT(VARCHAR(24),d.UpdateDT) + ' GMT By User: ' + u.UserLastName + ', ' + u.UserFirstName + ' (' + u.UserName + ')'FROM dbo.CDMS_Document dINNER JOIN dbo.APPL_Users u WITH (NOLOCK) ON u.UserID = d.UpdateUserIDWHERE d.ParentInstanceID = @IDAND d.ParentObjectID = (SELECT ParentObjectID FROM dbo.CDMS_ParentObject WHERE ParentObjectCode = 'Settlement')AND d.DocActiveFlag = 1AND d.IsRegionalDocumentFlag = 0ORDER BY d.DocID

DECLARE @RegionaldocList VARCHAR(MAX)SET @RegionaldocList = ''SELECT @RegionaldocList = @RegionaldocList + CASE WHEN @RegionaldocList != '' THEN '' ELSE '' END + '- DocumentID: ' + CONVERT(VARCHAR(100),d.DocID) + ' Updated On: ' + CONVERT(VARCHAR(24),d.UpdateDT) + ' GMT By User: ' + u.UserLastName + ', ' + u.UserFirstName + ' (' + u.UserName + ')'FROM dbo.CDMS_Document dINNER JOIN dbo.APPL_Users u WITH (NOLOCK) ON u.UserID = d.UpdateUserIDWHERE d.ParentInstanceID = @IDAND d.ParentObjectID = (SELECT ParentObjectID FROM dbo.CDMS_ParentObject WHERE ParentObjectCode = 'Settlement')AND d.DocActiveFlag = 1AND d.IsRegionalDocumentFlag = 1ORDER BY d.DocID

DECLARE @vltnList VARCHAR(MAX)SET @vltnList = ''SELECT @vltnList = @vltnList + CASE WHEN @vltnList != '' THEN '' ELSE '' END + '- ViolationCode: ' + CONVERT(VARCHAR(100),V.NERCViolationID) + ' Updated On: ' + CONVERT(VARCHAR(24),V.UpdateDT) + ' GMT By User: ' + u.UserLastName + ', ' + u.UserFirstName + ' (' + u.UserName + ')'FROM dbo.CDMS_Violation VINNER JOIN dbo.APPL_Users u WITH (NOLOCK) ON u.UserID = V.UpdateUserIDWHERE V.SettlementID = @ID--AND V.ActiveFlag = 1ORDER BY V.NERCViolationIDSELECT @AuditTrailXML =

(SELECT ID

= SettlementID,Region_Settlement_Code = ST.RegionSettlementCod

e,Regional_Entity = (SELECT Compan

yName FROM APPL_Company WHERE CompanyID= @SttlmtCompanyID),Region_Contact = (SELECT Contac

tLastName+', '+ContactFirstName FROM CDMS_Contact C WHERE C.ContactID=ST.ContactID_Region)

,Requested_On = isnull(ST.SettlementRequestedOnDate,'')

,Recieved_By = (SELECT ContactLastName+', '+ContactFirstName FROM CDMS_Contact C WHERE C.ContactID=ST.ContactID_ReceivedBy)

,AuthToNegotiateName = ST.AuthToNegotiateName,AuthToNegotiateAddressLine1 = ST.AuthToNegotiateAddressLine1,AuthToNegotiateAddressLine2 = ST.AuthToNegotiateAddressLine2,AuthToNegotiateCity = ST.AuthToNegotiateCity

,StateName = S.StateName

,AuthToNegotiatePhone = ST.AuthToNegotiatePhone

,AuthToNegotiateFax = ST.AuthToNegotiateFax

,AuthToNegotiateEmail = ST.AuthToNegotiateEmail

,EntityComment = ST.EntityComment

,RegionReceiptContactName = RRCT.ContactFirstName + ' ' + RRCT.ContactLastName

,RegionReceiptComment = ST.RegionReceiptComment

,SendToNERCFlag = ST.SendToNERCFlag

,NERCSttlmtCode = ST.NERCSettlementCode

,ParentObjectStatusCode = PO.ParentObjectStatusCode

,Proposed_Amount = ISNULL(CAST(ST.ProposedAmount AS VARCHAR),'')

,Final_Amount = ISNULL(CAST(ST.FinalAmount AS VARCHAR),'')

,NERC_State = ISNULL(ST.NERCSettlementState,'')

,RegionName =(SELECT RegionName FROM CDMS_Region WHERE RegionID= @RegionID)

,Region_Note = ST.RegionNotesToNERC

,Entity_Comments = @EntitycomList,Entity_Documents = @EntitydocList,Regional_Comments = @RegionalcomLi

st,Regional_Documents = @RegionaldocLi

st,Violation_list = @vltnList,Acknowledge_On = ISNULL(ST.Ackn

owledgedOnDate,''),Discussion_End_Date = ISNULL(ST.DiscussionsS

cheduledEndDate,''),Discussion_Actual_Date = ISNULL(ST.DiscussionsA

ctualEndDate,''),Execute_Date = ISNULL(ST.Exec

utionDate,''),Created_By = CASE W

HEN ST.CreationUserID IS NOT NULL THEN (SELECT UserLastName + ', ' + UserFirstName + ' (' + UserName + ')' FROM dbo.APPL_Users WHERE UserID = ST.CreationUserID) ELSE '' END

,Created_On = ST.[CreationDT]

,Updated_By = CASE WHEN ST.CreationUserID IS NOT NULL THEN (SELECT UserLastName + ', ' + UserFirstName + ' (' + UserName + ')' FROM dbo.APPL_Users WHERE UserID = ST.UpdateUserID) ELSE '' END

,Updated_On = ISNULL(ST.[UpdateDT],'')

--Original Column names, _SettlementID = ST.SettlementI

D,_RegionSttlmtCode = ST.RegionSettl

ementCode,_SendToNERCFlag = ST.SendToNERCF

lag,_LastSentToNERCDT = ST.LastSentToN

ERCDT,_SttlmtCompanyID = ST.CompanyID,_CompanyName = (SELECT Compan

yName FROM APPL_Company WHERE CompanyID= @SttlmtCompanyID),_SttlmtRegionContactID = ST.ContactID_Region,_SttlmtRequestedOnDT = ST.SettlementRequested

OnDate,_SttlmtRcvdByContactID = ST.ContactID_ReceivedB

y,_SttlmtAckedOnDT = ST.Acknowledge

dOnDate,_SttlmtDiscSchedEndDT = ST.DiscussionsSchedule

dEndDate,_SttlmtDiscActEndDT = ST.DiscussionsActualEn

dDate,_SttlmtExecuteDT = ST.ExecutionDa

te,_SttlmtProposedAmt = ST.ProposedAmo

unt,_SttlmtFinalAmt = ST.FinalAmount,_SttlmtNERCState = ST.NERCSettlem

entState,_RegionID = C.Regi

onID,_RegionName = (SELECT Region

Name FROM CDMS_Region WHERE RegionID= @RegionID),_SttlmtRegionNoteToNERC = ST.RegionNotesToNERC,_CreationUserID = ST.[CreationUs

erID],_CreationDT = ST.[CreationDT

],_UpdateUserID = ST.[UpdateUser

ID],_UpdateDT = ST.[Up

dateDT],CDMSSettlementCode = ST.CDMSSettlem

entCodeFROM CDMS_Settlement STINNER JOIN APPL_Company C WITH (NOLOCK) ON C.CompanyID = ST.CompanyIDINNER JOIN CDMS_Region R WITH (NOLOCK) ON R.RegionID = C.RegionIDLEFT JOIN CDMS_Contact RRCT WITH (NOLOCK) ON RRCT.ContactID = ST.Contact

ID_RegionReceiptLEFT JOIN States S WITH (NOLOCK) ON S.StateID = ST.AuthToNegotiateStateI

DLEFT JOIN [CDMS_ParentObjectStatus] PO WITH (NOLOCK) ON PO.[ParentObject

StatusID] = ST.[ParentObjectStatusID]WHERE SettlementID = @IDFOR XML AUTO)

RETURN @AuditTrailXML

END