ALTER PROCEDURE [dbo].[ASC_SCS_InsertHDAIssueAndActivities]
@IssueID int,
@DateCreated datetime,
@ReceivedDate datetime,
@DueDate datetime = NULL,
@ResolvedDate datetime = NULL,
@ResolvedByUserID int = NULL,
@AssignedGroupID int = NULL,
@UpdatedByUserID int = NULL,
@CreatedByUserID int,
@AssignedUserID int,
@IssueStatusID int,
@IssueSubStatusID int,
@PriorityID int,
@IssueCategoryID int,
@ConfigurationID int,
@LocationID int,
@RoomID int,
@POCID int,
@IssueTypeID int,
@VersionID int,
@ProductID int,
@SupportVisitTypeID int = NULL,
@CallbackNumber varchar(50) = NULL,
@AlternateEmail varchar(200) = NULL,
@IssueSummary varchar(255),
@IssueDescription text,
@LastUpdatedDate datetime = NULL,
@DRNumber varchar(50) = NULL,
@DRLink varchar(500) = NULL,
@ECRNumber varchar(50) = NULL,
@ECRLink varchar(500) = NULL,
@IssueResolution text = NULL,
@EquipmentReplacedID int = NULL,
@EquipmentReplacedQuantity int = NULL,
@EquipmentDescription varchar(500) = NULL,
@ProjectedSoftwareVersionID int = NULL,
@ReleasedSoftwareVersionNumber varchar(25) = NULL,
@OriginID int,
@xmlActivities ntext
AS
DECLARE @FileHandle int
BEGIN TRANSACTION
--Attempt to insert Issue.
INSERT INTO I(IssueID, DateCreated, ReceivedDate, DueDate, ResolvedDate, ResolvedByUserID,
AssignedGroupID, UpdatedByUserID, CreatedByUserID, AssignedUserID, IssueStatusID, IssueSubStatusID, PriorityID,
IssueCategoryID, ConfigurationID, LocationID, RoomID, POCID, IssueTypeID, VersionID, ProductID, SupportVisitTypeID,
CallbackNumber, AlternateEmail, IssueSummary, IssueDescription, LastUpdatedDate, DRNumber, DRLink, ECRNumber,
ECRLink, IssueResolution, EquipmentReplacedID, EquipmentReplacedQuantity, EquipmentDescription, ProjectedSoftwareVersionID,
ReleasedSoftwareVersionNumber, OriginID)
VALUES (@IssueID, @DateCreated, @ReceivedDate, @DueDate, @ResolvedDate, @ResolvedByUserID,
@AssignedGroupID, @UpdatedByUserID, @CreatedByUserID, @AssignedUserID, @IssueStatusID, @IssueSubStatusID, @PriorityID,
@IssueCategoryID, @ConfigurationID, @LocationID, @RoomID, @POCID, @IssueTypeID, @VersionID, @ProductID, @SupportVisitTypeID,
@CallbackNumber, @AlternateEmail, @IssueSummary, @IssueDescription, @LastUpdatedDate, @DRNumber, @DRLink, @ECRNumber,
@ECRLink, @IssueResolution, @EquipmentReplacedID, @EquipmentReplacedQuantity, @EquipmentDescription, @ProjectedSoftwareVersionID,
@ReleasedSoftwareVersionNumber, @OriginID)
--Rollback on errors.
IF @@ERROR != 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
--Attempt to insert Issue Activity.
--Convert the xml data into a recordset.
EXEC dbo.sp_xml_preparedocument @FileHandle output, @xmlActivities
INSERT INTO IA (IssueActivityTypeID, IssueID, CreatedByUserID, ActivityDate, TimeSpent, Notes, Notify)
SELECT IssueActivityTypeID, IssueID, CreatedByUserID, ActivityDate, TimeSpent, Notes, Notify
FROM OPENXML(@FileHandle, 'Activities/Activity')
WITH (IssueActivityTypeID int 'IssueActivityTypeID', IssueID int 'IssueID', CreatedByUserID int 'CreatedByUserID',
ActivityDate datetime 'ActivityDate', TimeSpent int 'TimeSpent', Notes text 'Notes', Notify bit 'Notify')
--Rollback on errors.
IF @@ERROR != 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
ELSE
COMMIT TRANSACTION