phares3757/mesa-fab-approval
3
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
mesa-fab-approval/SQL/New Parts Request.txt
Jonathan Ouellette 580e90f6a2 initial add
2022-09-27 14:10:30 -07:00

1259 lines
41 KiB
Plaintext
Raw Permalink Blame History

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
SET XACT_ABORT ON
GO
BEGIN TRANSACTION
GO
CREATE TABLE dbo.PartsRequest
(
PRNumber int NOT NULL IDENTITY (1, 1),
Title varchar(200) NOT NULL,
CreateDate datetime NOT NULL,
SubmitDate datetime NULL,
CloseDate datetime NULL,
OriginatorID int NOT NULL,
RequestorID int NOT NULL,
TechLeadID int NOT NULL,
Description varchar(5000) NOT NULL,
LastUpdateDate datetime NULL,
CurrentStep int NOT NULL,
[Status] AS CASE WHEN CloseDate IS NOT NULL THEN 'Closed' WHEN SubmitDate IS NOT NULL THEN 'Submitted' ELSE 'New' END
) ON [PRIMARY]
GO
ALTER TABLE dbo.PartsRequest ADD CONSTRAINT
PK_PartsRequest PRIMARY KEY CLUSTERED
(
PRNumber
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PartsRequest] ADD CONSTRAINT [DF_PartsRequest_CurrentStep] DEFAULT ((0)) FOR [CurrentStep]
GO
ALTER TABLE dbo.PartsRequest SET (LOCK_ESCALATION = TABLE)
GO
CREATE TABLE dbo.PartsRequestAttachment
(
AttachmentID int NOT NULL IDENTITY (1, 1),
PRNumber int NOT NULL,
FileName varchar(255) NOT NULL,
UserID int NOT NULL,
UploadDate datetime NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.PartsRequestAttachment ADD CONSTRAINT
PK_PartsRequestAttachment PRIMARY KEY CLUSTERED
(
AttachmentID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.PartsRequestAttachment SET (LOCK_ESCALATION = TABLE)
GO
CREATE PROCEDURE [dbo].[PartsRequestGetList]
AS
BEGIN
SELECT
[PRNumber],
[Title],
[CreateDate],
[SubmitDate],
[CloseDate],
[OriginatorID],
[RequestorID],
[TechLeadID],
[Description],
[LastUpdateDate],
[CurrentStep],
CASE
WHEN EXISTS (SELECT 1 FROM Approval a WHERE a.IssueID = PRNumber AND a.DocumentTypeID = 10 AND ItemStatus = 2) THEN 'Denied'
ELSE [Status]
END AS [Status],
Originator.FirstName + ' ' + Originator.LastName AS [Originator],
Requestor.FirstName + ' ' + Requestor.LastName AS [Requestor],
TechLead.FirstName + ' ' + TechLead.LastName AS [TechLead]
FROM PartsRequest
LEFT OUTER JOIN Users AS Originator ON Originator.UserID = PartsRequest.OriginatorID
LEFT OUTER JOIN Users AS Requestor ON Requestor.UserID = PartsRequest.RequestorID
LEFT OUTER JOIN Users AS TechLead ON TechLead.UserID = PartsRequest.TechLeadID
END
GO
CREATE PROCEDURE [dbo].[PartsRequestInsert]
@OriginatorID INT,
@PRNumber INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO PartsRequest(
[Title], [CreateDate], [OriginatorID], [RequestorID], [TechLeadID], [Description])
SELECT
'', GETDATE(), @OriginatorID, -1, -1, '';
SET @PRNumber = CAST(SCOPE_IDENTITY() AS INT)
END
GO
CREATE PROCEDURE [dbo].[PartsRequestGet]
@PRNumber int
AS
BEGIN
SELECT * FROM PartsRequest WHERE PRNumber = @PRNumber
END
GO
CREATE PROCEDURE [dbo].[PartsRequestInsertAttachment]
@PRNumber INT,
@FileName VARCHAR(255),
@UserID INT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO PartsRequestAttachment(PRNumber, FileName, UserID, UploadDate)
SELECT @PRNumber, @FileName, @UserID, GETDATE()
UPDATE PartsRequest
SET LastUpdateDate = GETDATE()
WHERE PRNumber = @PRNumber
END
GO
CREATE PROCEDURE [dbo].[PartsRequestGetAttachmentFileName]
@AttachmentID int
AS
BEGIN
SET NOCOUNT ON;
SELECT [FileName] FROM PartsRequestAttachment WHERE AttachmentID = @AttachmentID
END
GO
CREATE PROCEDURE [dbo].[PartsRequestDeleteAttachment]
@AttachmentID int
AS
BEGIN
SET NOCOUNT ON;
UPDATE PartsRequest
SET LastUpdateDate = GETDATE()
WHERE PRNumber IN (SELECT PRNumber FROM PartsRequestAttachment WHERE AttachmentID = @AttachmentID)
DELETE FROM PartsRequestAttachment WHERE AttachmentID = @AttachmentID
END
GO
CREATE PROCEDURE [dbo].[PartsRequestUpdate]
@PRNumber int,
@Title varchar(200),
@RequestorID int,
@TechLeadID int,
@Description varchar(5000)
AS
BEGIN
UPDATE PartsRequest
SET
LastUpdateDate = GETDATE(),
Title = ISNULL(@Title, ''),
RequestorID = @RequestorID,
TechLeadID = @TechLeadID,
Description = ISNULL(@Description, '')
WHERE PRNumber = @PRNumber
END
GO
CREATE FUNCTION [dbo].[fnPartsRequestGetSubRolesForNextStep]
(
@PRNumber int
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @NextStep int = 0
DECLARE @r varchar(100)
SELECT @NextStep = CurrentStep + 1 FROM PartsRequest WHERE PRNumber = @PRNumber
SET @r =
CASE
WHEN @NextStep = 1 THEN 'Part Assignment'
WHEN @NextStep = 2 THEN 'Pre-Approver'
WHEN @NextStep = 3 THEN 'Tech Lead'
WHEN @NextStep = 4 THEN 'Finance'
WHEN @NextStep = 5 THEN 'SAP'
WHEN @NextStep = 6 THEN 'Source List & Info Record'
WHEN @NextStep = 7 THEN 'Buyer/Planner'
WHEN @NextStep = 8 THEN 'Close to Folder'
ELSE 'Part Assignment'
END
RETURN @r
END
GO
CREATE PROCEDURE [dbo].[PartsRequestSubmitForApproval]
@PRNumber int,
@UserID int
AS
BEGIN
DECLARE @DocumentTypeID int = 10
CREATE TABLE #TempApprovals (
[IssueID] [int] NULL,
[RoleName] [nvarchar](50) NULL,
[SubRole] [nvarchar](50) NOT NULL,
[UserID] [int] NOT NULL,
[SubRoleID] [int] NOT NULL,
[ItemStatus] [int] NULL,
[Step] [int] NULL,
[NotifyDate] [datetime] NULL,
[AssignedDate] [datetime] NULL,
[RoleAssignedDate] [datetime] NULL,
[ApprovalType] [tinyint] NULL
)
DECLARE @initialtrancount int = @@TRANCOUNT
BEGIN TRANSACTION
BEGIN TRY
UPDATE PartsRequest
SET SubmitDate = GETDATE(), CurrentStep = 0, CloseDate = NULL
WHERE PRNumber = @PRNumber
-- make sure to delete any existing approval items before sumbitting/re-submitting an issue
DELETE FROM Approval WHERE IssueID = @PRNumber AND DocumentTypeID = @DocumentTypeID
IF @@ROWCOUNT > 0
BEGIN
-- UPDATE THE APPROVAL LOG for a system initiated transaction
INSERT INTO ApprovalLog
(IssueID, UserID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@PRNumber, @UserID, 'Delete', 'Delete Existing Approval', @DocumentTypeID)
END
-- UPDATE THE APPROVAL LOG
INSERT INTO ApprovalLog
(IssueID, UserID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@PRNumber, @UserID, 'Submit', 'Submitted the document', @DocumentTypeID)
DECLARE @SubRoleCategoriesClause varchar(100)
SET @SubRoleCategoriesClause = '(''' + dbo.fnPartsRequestGetSubRolesForNextStep(@PRNumber) + ''')'
PRINT @SubRoleCategoriesClause
DECLARE @WorkFlowNumber int
SET @WorkFlowNumber = (SELECT MIN(WorkflowNumber) FROM Workflows WHERE WorkflowName = 'PRWkFlow')
INSERT INTO #TempApprovals
(IssueID, RoleName, SubRole, UserID, SubRoleID, ItemStatus, Step, AssignedDate, NotifyDate,RoleAssignedDate, ApprovalType )
EXEC GetRoles
@WorkflowStepNumber = 1,
@WhereClause = @SubRoleCategoriesClause,
@CheckForITARCompliant = 0,
@DocumentTypeID = @DocumentTypeID,
@IssueID = @PRNumber,
@WorkFlowNumber = @WorkFlowNumber
IF (SELECT COUNT(*) FROM #TempApprovals) = 0
BEGIN
RAISERROR('Parts Request submit error - no approvers found', 16, 1)
END
-- INSERT INTO THE ACTUAL TABLE
INSERT INTO Approval
(IssueID, RoleName, SubRole, UserID, SubRoleID, ItemStatus, Step, AssignedDate, NotifyDate,RoleAssignedDate, ApprovalType, DocumentTypeID )
(SELECT DISTINCT *, @DocumentTypeID AS DocumentTypeID FROM #TempApprovals)
DROP TABLE #TempApprovals
UPDATE PartsRequest SET CurrentStep = 1 WHERE PRNumber = @PRNumber
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
DECLARE @ErrorState INT = ERROR_STATE()
INSERT INTO EventLog
(UserID, DocumentType, IssueID, OperationType, InsertTimeStamp, Comments)
VALUES
(@UserID, 'PR', @PRNumber, 'Error', GETDATE(), LEFT('Error while submitting PR: ' + @ErrorMessage, 500) )
IF @initialtrancount > 0 BEGIN TRANSACTION
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END
GO
CREATE FUNCTION [dbo].[fnGetApprovalFullNames]
(
@IssueID int,
@DocumentTypeID int,
@Separator varchar(10),
@IncludeRole bit,
@IncludeSubRole bit,
@IncludePending bit,
@IncludeApproved bit,
@IncludeOthers bit
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @r varchar(max)
DECLARE @t TABLE(u varchar(200))
INSERT INTO @t
SELECT
@Separator + ISNULL(U.FirstName, '') + ' ' + ISNULL(U.LastName, '') +
CASE WHEN @IncludeRole <> 0 THEN ' (' + A.RoleName + ')' ELSE '' END +
CASE WHEN @IncludeSubRole <> 0 THEN ' (' + A.SubRole + ')' ELSE '' END
FROM Approval A
LEFT OUTER JOIN Users U ON A.UserID = U.UserID
WHERE A.IssueID = @IssueID AND A.DocumentTypeID = @DocumentTypeID
AND (
(@IncludeOthers <> 0 AND A.ItemStatus NOT IN (0, 1))
OR (@IncludePending <> 0 AND A.ItemStatus = 0)
OR (@IncludeApproved <> 0 AND A.ItemStatus = 1)
)
ORDER BY A.Step, U.FirstName, U.LastName
SELECT @r = (SELECT u + '' FROM @t FOR XML PATH(''))
--SELECT @r = (
-- SELECT
-- @Separator + ISNULL(U.FirstName, '') + ' ' + ISNULL(U.LastName, '') +
-- CASE WHEN @IncludeRole <> 0 THEN ' (' + A.RoleName + ')' ELSE '' END +
-- CASE WHEN @IncludeSubRole <> 0 THEN ' (' + A.SubRole + ')' ELSE '' END
-- FROM Approval A
-- LEFT OUTER JOIN Users U ON A.UserID = U.UserID
-- WHERE A.IssueID = @IssueID AND A.DocumentTypeID = @DocumentTypeID
-- AND (
-- (@IncludeOthers <> 0 AND A.ItemStatus NOT IN (0, 1))
-- OR (@IncludePending <> 0 AND A.ItemStatus = 0)
-- OR (@IncludeApproved <> 0 AND A.ItemStatus = 1)
-- )
-- ORDER BY A.Step
-- FOR XML PATH('')
--)
IF @r <> '' SET @r = STUFF(@r, 1, LEN(@Separator), '')
RETURN @r
END
GO
CREATE FUNCTION [dbo].[fn_GetPRsByUser]
(
@UserID INT
)
RETURNS TABLE
AS
RETURN
(
SELECT DISTINCT 'PartsRequest' AS DocumentType, *
FROM
(
SELECT
'Parts Request' AS SubDoc,
PR.PRNumber AS IssueID,
PR.PRNumber AS DocID,
CreateDate AS IssueDate,
'' AS LotNos,
dbo.fnGetApprovalFullNames(PR.PRNumber, 10, ' / ', 0, 1, 1, 0, 0) AS 'PendingApprovers',
Title,
Description AS IssueDescription,
U1.FirstName + ' ' + U1.LastName AS Originator,
SubmitDate AS SubmitedDate,
CloseDate,
A.ItemStatus
FROM PartsRequest PR
INNER JOIN Approval A ON A.IssueID = PR.PRNumber AND A.DocumentTypeID = 10
--INNER JOIN Users U2 ON PR.OriginatorID = U2.UserID
INNER JOIN Users U1 ON PR.RequestorID = U1.UserID
WHERE (A.UserID = @UserID)
AND (ItemStatus = 0 OR (ItemStatus = 2 AND DisplayDeniedDocument = 1))
) AS A
)
GO
ALTER PROCEDURE [dbo].[GetTaskListByUser]
@UserID INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--List of documents waiting on the user for approval/rejection
SELECT * FROM dbo.fn_GetLotDispositionsByUser(@UserID)
UNION
SELECT * FROM dbo.fn_GetMRBByUser(@UserID)
UNION
SELECT * FROM fn_GetECNsByUser(@UserID)
UNION
SELECT * FROM fn_GetLTWorkRequestsByUser(@UserID)
UNION
SELECT * FROM fn_Get8DCAByUser(@UserID)
UNION
SELECT * FROM fn_Get8DAuditByUser(@UserID)
UNION
SELECT * from fn_GetPRsByUser(@UserID)
ORDER BY DocumentType, IssueID DESC
END
GO
ALTER PROCEDURE [dbo].[UpdateApproval]
@IssueID INT,
@CurrentStep INT,
@ItemStatus INT,
@UserID INT,
@Comments VARCHAR(1000),
@SubRoleCategoriesClause VARCHAR(500),
@DocumentTypeID INT,
@LastStep BIT OUTPUT,
@LastApproverInCurrentStep BIT OUTPUT,
@WorkFlowNumber INT = 1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @ApprovalType INT
DECLARE @MaxStep INT
DECLARE @RemainingApprovers INT
DECLARE @NewStep INT
DECLARE @ApproverCountForThisStep INT
DECLARE @SubRoleID INT
DECLARE @ConvertedFromNumber INT
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @RHCount INT
SET @RHCount = 0
IF @DocumentTypeID = 1
BEGIN
SET @RHCount = (SELECT COUNT(*) FROM dbo.fnGetLot_RH(@IssueID))
END
ELSE IF @DocumentTypeID = 2
BEGIN
SET @RHCount = (
SELECT COUNT(*) FROM MRB M WITH(NOLOCK)
INNER JOIN MRBLot L WITH(NOLOCK) ON M.MRBNumber = L.MRBNumber
INNER JOIN [TEMIRWAP019].[FAB2SPN].[dbo].[MP_RECORD] P WITH(NOLOCK)
ON (L.WipPartNo = P.MP_PART_NUMBER OR L.DiePartNo = MP_PART_NUMBER)
WHERE MP_ITAR_CONTROLLED_SW = 'Y' AND M.MRBNumber = @IssueID
)
END
SET @ApprovalType = (SELECT DISTINCT TOP 1 ApprovalType FROM Approval
WHERE IssueID = @IssueID AND UserID = @UserID AND Step = @CurrentStep AND DocumentTypeID = @DocumentTypeID AND ItemStatus=0)
SET @LastStep = 0
SET @NewStep = @CurrentStep + 1
-- GET THE LAST STEP IN THE WORKFLOW
SELECT @MaxStep = (SELECT MAX(WS.WorkflowStepNumber)
FROM DocumentType D
INNER JOIN Workflows W ON D.DocumentTypeID = W.DocumentTypeID AND WorkFlowNumber = @WorkFlowNumber
INNER JOIN WorkflowSteps WS ON WS.WorkflowID = W.WorkflowID
AND D.DocumentTypeID = @DocumentTypeID)
-- Get the SubRole ID of the Person Approving/Denying it
SET @SubRoleID = (SELECT TOP 1 SubRoleID FROM Approval
WHERE IssueID = @IssueID AND UserID = @UserID AND Step = @CurrentStep AND DocumentTypeID = @DocumentTypeID)
DECLARE @OperationTypeString VARCHAR(50)
IF EXISTS (SELECT * FROM ECN WHERE CancellationInProgress = 1 AND ECNNumber = @IssueID)
SET @OperationTypeString = 'Cancellation Approved'
ELSE IF EXISTS (SELECT * FROM ECN WHERE ExpirationInProgress = 1 AND ECNNumber = @IssueID)
SET @OperationTypeString = 'Expiration Approved'
ELSE
SET @OperationTypeString = 'Approved'
BEGIN TRY
BEGIN TRAN
IF @ItemStatus = 1 -- Approve
BEGIN
UPDATE Approval
SET ItemStatus = 1,
CompletedDate = GETDATE(),
Comments = @Comments
WHERE IssueID = @IssueID AND UserID = @UserID AND Step = @CurrentStep AND DocumentTypeID = @DocumentTypeID AND CompletedDate IS NULL
IF @@ROWCOUNT > 0
BEGIN
-- UPDATE THE APPROVAL LOG
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID,@SubRoleID, @OperationTypeString, 'Approved at step ' + CONVERT(NCHAR(10), @CurrentStep),@DocumentTypeID)
END
IF @ApprovalType = 2 -- ONLY ONE APPROVER PER SUBROLE IS REQUIRED FOR THIS STEP
BEGIN
---- Get the SubRole ID of the Person Approving it
--SET @SubRoleID = (SELECT TOP 1 SubRoleID FROM Approval
-- WHERE IssueID = @IssueID AND UserID = @UserID AND Step = @CurrentStep AND DocumentTypeID = @DocumentTypeID)
-- Because Only approver is required to approve per SubRole
-- Selete rest the approvers for the SubRoles bellonging to the this approver
DELETE FROM Approval
WHERE IssueID = @IssueID AND UserID <> @UserID AND Step = @CurrentStep AND DocumentTypeID = @DocumentTypeID AND SubRoleID = @SubRoleID
-- UPDATE THE APPROVAL LOG
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Delete', 'Delete approvers for step ' + CONVERT(NCHAR(10), @CurrentStep) + ' for SubRoleID ' + CONVERT(NCHAR(10), @SubRoleID) ,@DocumentTypeID )
-- Check if there are any pending approvals
SET @RemainingApprovers = (
SELECT COUNT(*)
FROM Approval
WHERE IssueID = @IssueID AND Step = @CurrentStep AND ItemStatus = 0 AND DocumentTypeID = @DocumentTypeID
)
-- if there are no more approvers in this step then proceed to next step
IF @RemainingApprovers = 0
BEGIN
IF @MaxStep > @CurrentStep -- there are some steps remaining
BEGIN
SET @LastStep = 0
IF @DocumentTypeID = 1 -- Lot Disposition
BEGIN
UPDATE LotDisposition
SET CurrentStep = @NewStep
WHERE IssueID = @IssueID
END
ELSE IF @DocumentTypeID = 2 -- MRB
BEGIN
UPDATE MRB
SET CurrentStep = @NewStep
WHERE MRBNumber = @IssueID
END
ELSE IF @DocumentTypeID = 3 OR @DocumentTypeID = 4 OR @DocumentTypeID = 5-- ECN
BEGIN
UPDATE ECN
SET CurrentStep = @NewStep
WHERE ECNNumber = @IssueID
END
ELSE IF @DocumentTypeID = 6 -- LotTraveler
BEGIN
UPDATE LTWorkRequest
SET CurrentStep = @NewStep
WHERE ID = @IssueID
END
ELSE IF @DocumentTypeID = 9 -- Corrective Action
BEGIN
UPDATE _8DCorrectiveAction
SET CurrentStep = @NewStep
WHERE CANo = @IssueID
END
ELSE IF @DocumentTypeID = 10
BEGIN
UPDATE PartsRequest
SET CurrentStep = @NewStep
WHERE PRNumber = @IssueID
END
-- proceed to the next
EXEC InsertApprovers @IssueID, @NewStep, @DocumentTypeID, @SubRoleCategoriesClause, @RHCount, @ApproverCountForThisStep
-- UPDATE THE APPROVAL LOG
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Insert', 'Insert approvers for step ' + CONVERT(NCHAR(10), @NewStep), @DocumentTypeID)
IF @ApproverCountForThisStep = 0
BEGIN
SET @LastApproverInCurrentStep = 1
RETURN;
END
END
ELSE
BEGIN -- It was the last step, close the document
SET @LastStep = 1
IF @DocumentTypeID = 1
BEGIN
UPDATE LotDisposition
SET CloseDate = GETDATE()
WHERE IssueID = @IssueID
END
ELSE IF @DocumentTypeID = 2
BEGIN
UPDATE MRB
SET ApprovalDate = GETDATE(), ApprovalStatus = 1
WHERE MRBNumber = @IssueID
END
ELSE IF @DocumentTypeID = 3 OR @DocumentTypeID = 4 OR @DocumentTypeID = 5
BEGIN
IF EXISTS (SELECT * FROM ECN WHERE CancellationInProgress = 1 AND ECNNumber = @IssueID)
BEGIN
UPDATE ECN
SET CancellationInProgress = 0, CancellationApproved = 1, CancellationApprovalDate = GETDATE()
WHERE ECNNumber = @IssueID
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Cancelled', 'Document closed', @DocumentTypeID)
END
ELSE IF EXISTS (SELECT * FROM ECN WHERE ExpirationInProgress = 1 AND ECNNumber = @IssueID)
BEGIN
UPDATE ECN
SET ExpirationInProgress = 0, ExpirationProcessed = 1, ExpirationProcessedlDate = GETDATE()
WHERE ECNNumber = @IssueID
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Expired', 'Document closed', @DocumentTypeID)
END
ELSE
BEGIN
UPDATE ECN
SET CloseDate = GETDATE(), TECNExtensionState = 0, ConversionApprovalInProgress = 0
WHERE ECNNumber = @IssueID
SET @ConvertedFromNumber = (SELECT ConvertedFromNumber FROM ECN WHERE ECNNumber = @IssueID)
-- Set the flag for the TECN, which this ECN was derived from
IF (@ConvertedFromNumber IS NOT NULL)
BEGIN
UPDATE ECN
SET ConversionApprovalInProgress = 0
WHERE ECNNumber = @ConvertedFromNumber
END
END
END
ELSE IF @DocumentTypeID = 6
BEGIN
UPDATE LTWorkRequest
SET CloseDate = GETDATE(), Status = 1
WHERE ID = @IssueID
END
ELSE IF @DocumentTypeID = 9
BEGIN
UPDATE _8DCorrectiveAction
SET ClosedDate = GETDATE(), Status = 1, ApprovalStatus = 2, ApprovedDate = GETDATE()
WHERE CANo = @IssueID
END
ELSE IF @DocumentTypeID = 10
BEGIN
UPDATE PartsRequest
SET CloseDate = GETDATE()
WHERE PRNumber = @IssueID
END
-- UPDATE THE APPROVAL LOG
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Last Step', 'Document closed', @DocumentTypeID)
END
SET @LastApproverInCurrentStep = 1
END
ELSE
BEGIN
SET @LastApproverInCurrentStep = 0
END
END
ELSE -- ALL THE APPROVERS ARE REQUIRED TO APPROVE FOR THIS STEP
BEGIN
-- Check if there are any pending approvals
SET @RemainingApprovers = (
SELECT COUNT(*)
FROM Approval
WHERE IssueID = @IssueID AND Step = @CurrentStep AND ItemStatus = 0 AND DocumentTypeID = @DocumentTypeID
)
-- if there are no more approvers in this step then proceed to next step
IF @RemainingApprovers = 0
BEGIN
IF @MaxStep > @CurrentStep -- there are some steps remaining
BEGIN
SET @LastStep = 0
IF @DocumentTypeID = 1
BEGIN
UPDATE LotDisposition
SET CurrentStep = @NewStep
WHERE IssueID = @IssueID
END
ELSE IF @DocumentTypeID = 2
BEGIN
UPDATE MRB
SET CurrentStep = @NewStep
WHERE MRBNumber = @IssueID
END
ELSE IF @DocumentTypeID = 3 OR @DocumentTypeID = 4 OR @DocumentTypeID = 5
BEGIN
UPDATE ECN
SET CurrentStep = @NewStep
WHERE ECNNumber = @IssueID
END
ELSE IF @DocumentTypeID = 6
BEGIN
UPDATE LTWorkRequest
SET CurrentStep = @NewStep
WHERE ID = @IssueID
END
ELSE IF @DocumentTypeID = 9
BEGIN
UPDATE _8DCorrectiveAction
SET CurrentStep = @NewStep
WHERE CANo = @IssueID
END
ELSE IF @DocumentTypeID = 10
BEGIN
UPDATE PartsRequest
SET CurrentStep = @NewStep
WHERE PRNumber = @IssueID
END
-- UPDATE THE APPROVAL LOG
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Update', 'Update Document with the new step ' + CONVERT(NCHAR(10), @NewStep), @DocumentTypeID)
-- proceed to the next step
SET @LastApproverInCurrentStep = 1
EXEC InsertApprovers @IssueID, @NewStep, @DocumentTypeID, @SubRoleCategoriesClause, @RHCount, @ApproverCountForThisStep
-- UPDATE THE APPROVAL LOG
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Insert', 'Insert approvers for step ' + CONVERT(NCHAR(10), @NewStep), @DocumentTypeID)
IF @ApproverCountForThisStep = 0
BEGIN
SET @LastApproverInCurrentStep = 1
RETURN;
END
END
ELSE
BEGIN -- It was the last step, close the document
SET @LastStep = 1
SET @LastApproverInCurrentStep = 1
IF @DocumentTypeID = 1
BEGIN
UPDATE LotDisposition
SET CloseDate = GETDATE()
WHERE IssueID = @IssueID
END
ELSE IF @DocumentTypeID = 2
BEGIN
UPDATE MRB
SET ApprovalDate = GETDATE(), ApprovalStatus = 1
WHERE MRBNumber = @IssueID
END
ELSE IF @DocumentTypeID = 3 OR @DocumentTypeID = 4 OR @DocumentTypeID = 5
BEGIN
IF EXISTS (SELECT * FROM ECN WHERE CancellationInProgress = 1 AND ECNNumber = @IssueID)
BEGIN
UPDATE ECN
SET CancellationInProgress = 0, CancellationApproved = 1, CancellationApprovalDate = GETDATE()
WHERE ECNNumber = @IssueID
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Cancelled', 'Document closed', @DocumentTypeID)
END
ELSE IF EXISTS (SELECT * FROM ECN WHERE ExpirationInProgress = 1 AND ECNNumber = @IssueID)
BEGIN
UPDATE ECN
SET ExpirationInProgress = 0, ExpirationProcessed = 1, ExpirationProcessedlDate = GETDATE()
WHERE ECNNumber = @IssueID
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Expired', 'Document closed', @DocumentTypeID)
END
ELSE
BEGIN
UPDATE ECN
SET CloseDate = GETDATE(), TECNExtensionState = 0, ConversionApprovalInProgress = 0
WHERE ECNNumber = @IssueID
-- Set the flag for the TECN, which this ECN was derived from
SET @ConvertedFromNumber = (SELECT ConvertedFromNumber FROM ECN WHERE ECNNumber = @IssueID)
-- Set the flag for the TECN, which this ECN was derived from
IF (@ConvertedFromNumber IS NOT NULL)
BEGIN
UPDATE ECN
SET ConversionApprovalInProgress = 0
WHERE ECNNumber = @ConvertedFromNumber
END
END
END
ELSE IF @DocumentTypeID = 6
BEGIN
UPDATE LTWorkRequest
SET CloseDate = GETDATE(), Status = 1
WHERE ID = @IssueID
END
ELSE IF @DocumentTypeID = 9
BEGIN
UPDATE _8DCorrectiveAction
SET ClosedDate = GETDATE(), Status = 1, ApprovalStatus = 2, ApprovedDate = GETDATE()
WHERE CANo = @IssueID
END
ELSE IF @DocumentTypeID = 10
BEGIN
UPDATE PartsRequest
SET CloseDate = GETDATE()
WHERE PRNumber = @IssueID
END
-- UPDATE THE APPROVAL LOG
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Last Step', 'Document closed', @DocumentTypeID)
END
END
ELSE
BEGIN
SET @LastApproverInCurrentStep = 0
END
END
END
ELSE
IF @ItemStatus = 2 -- denied
BEGIN
SET @LastStep = 0
SET @LastApproverInCurrentStep = 0
-- begin from step 1
UPDATE Approval
SET ItemStatus = 2,
CompletedDate = NULL,
Comments = @Comments
WHERE IssueID = @IssueID AND UserID = @UserID AND Step = @CurrentStep AND DocumentTypeID = @DocumentTypeID
-- UPDATE THE APPROVAL LOG
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Denied', 'Denied at step ' + CONVERT(NCHAR(10), @CurrentStep), @DocumentTypeID)
-- get the approvalid of the rejected record,
-- as this is the only record which needs to be retained for this issue
-- this retained record will be deleted when the issue is re-submitted
DECLARE @ApprovalID INT
SELECT @ApprovalID = (SELECT MAX(ApprovalID) FROM Approval
WHERE IssueID = @IssueID AND UserID = @UserID AND Step = @CurrentStep AND DocumentTypeID = @DocumentTypeID)
IF @ApprovalID IS NOT NULL -- do not process, if an invalid approver has processed it
BEGIN
-- delete all the other approvers record for the issue for the current step
DELETE FROM Approval
WHERE (IssueID = @IssueID AND ApprovalID <> @ApprovalID AND DocumentTypeID = @DocumentTypeID)
-- If the document is denied and the originator of the document belongs to "Probe" Subrole at the "Execution" step
-- make sure the that the document appears in the task list of the other users under the "Probe" Subrole
-- so that the other users under the "Probe" subrole know that the document needs to be re-submitted
-- this feature is needed becaue the users under the "Probe" subrole work on shifts.
DECLARE @OriginatorUserID INT
-- get the originator ID
IF @DocumentTypeID = 1
BEGIN
SELECT @OriginatorUserID = USR.UserID
FROM LotDisposition LD
INNER JOIN UserSubRole USR ON LD.OriginatorID = USR.UserID
INNER JOIN SubRole SR ON USR.SubRoleID = SR.SubRoleID
INNER JOIN SubRoleCategory SRC ON SR.SubRoleCategoryID = SRC.SubRoleCategoryID
WHERE (SRC.SubRoleCategoryItem = 'Probe' OR SRC.SubRoleCategoryItem = 'Fab') AND LD.IssueID = @IssueID
END
IF NOT @OriginatorUserID IS NULL
BEGIN
-- Add the remaiming users from the "Probe" into the "Approval",
-- so that it appears in the Task List of the Users in the "Probe" Subrole
INSERT INTO Approval
(A.IssueID, A.RoleName, A.SubRole, B.UserID, A.SubRoleID, A.ItemStatus, A.Step, A.NotifyDate, A.AssignedDate, A.RoleAssignedDate, A.CompletedDate, Comments, ApprovalType, A.DocumentTypeID, DisplayDeniedDocument )
SELECT A.IssueID, B.RoleName, B.SubRoleCategoryItem, B.UserID, A.SubRoleID, A.ItemStatus, A.Step, A.NotifyDate, A.AssignedDate, A.RoleAssignedDate, A.CompletedDate, 'Denied' AS 'Comments', ApprovalType, A.DocumentTypeID, 1
FROM
(
SELECT *
FROM Approval
WHERE ApprovalID = @ApprovalID
) AS A
INNER JOIN
(
SELECT @ApprovalID AS ApprovalID, USR.UserID , SRC.SubRoleCategoryItem, R.RoleName
FROM UserSubRole USR
INNER JOIN SubRole SR ON USR.SubRoleID = SR.SubRoleID
INNER JOIN SubRoleCategory SRC ON SR.SubRoleCategoryID = SRC.SubRoleCategoryID
INNER JOIN Role R ON SR.RoleID = R.RoleID
WHERE SRC.SubRoleCategoryItem = 'Probe' AND USR.UserID <> @OriginatorUserID
) AS B ON A.ApprovalID = B.ApprovalID
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Delete', 'Delete all the other approvals add other users from the Probe "SubRole" the denied one', @DocumentTypeID)
END
ELSE
BEGIN
-- UPDATE THE APPROVAL LOG
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Delete', 'Delete all the other approvals except the denied one', @DocumentTypeID)
END
-- Update LotDisposition Item to begining of the workflow loop
IF @DocumentTypeID = 1
UPDATE LotDisposition
SET CurrentStep = 0, SubmitedDate = NULL
WHERE IssueID = @IssueID
ELSE IF @DocumentTypeID = 2
UPDATE MRB
SET CurrentStep = 0, SubmitedDate = NULL
WHERE MRBNumber = @IssueID
ELSE IF @DocumentTypeID = 3 OR @DocumentTypeID = 4 OR @DocumentTypeID = 5
BEGIN
UPDATE ECN
SET CurrentStep = 0, SubmitedDate = NULL, ConversionApprovalInProgress = 0
WHERE ECNNumber = @IssueID
-- Set the flag for the TECN, which this ECN was derived from
SET @ConvertedFromNumber = (SELECT ConvertedFromNumber FROM ECN WHERE ECNNumber = @IssueID)
-- Set the flag for the TECN, which this ECN was derived from
IF NOT @ConvertedFromNumber IS NULL
BEGIN
UPDATE ECN
SET ConversionApprovalInProgress = 0
WHERE ECNNumber = @ConvertedFromNumber
END
END
ELSE IF @DocumentTypeID = 6
UPDATE LTWorkRequest
SET CurrentStep = 0, SubmitedDate = NULL
WHERE ID = @IssueID
ELSE IF @DocumentTypeID = 9
BEGIN
DECLARE @CAType VARCHAR(10)
SET @CAType = (SELECT CAType FROM _8DCorrectiveAction WHERE CANo = @IssueID)
IF @CAType = 'D8'
BEGIN
UPDATE _8DCorrectiveAction
SET CurrentStep = 0, D8CompleteDate = NULL, ApprovalStatus = 0
WHERE CANo = @IssueID
END
IF @CAType = 'D6'
BEGIN
UPDATE _8DCorrectiveAction
SET CurrentStep = 0, D6ValidatedDate = NULL, ApprovalStatus = 0
WHERE CANo = @IssueID
END
IF @CAType = 'D3'
BEGIN
UPDATE _8DCorrectiveAction
SET CurrentStep = 0, D3CompleteDate = NULL, ApprovalStatus = 0
WHERE CANo = @IssueID
END
IF @CAType = 'D0'
BEGIN
UPDATE _8DCorrectiveAction
SET CurrentStep = 0, D0CompleteDate = NULL, ApprovalStatus = 0
WHERE CANo = @IssueID
END
END
ELSE IF @DocumentTypeID = 10
BEGIN
UPDATE PartsRequest
SET CurrentStep = 0, SubmitDate = NULL
WHERE PRNumber = @IssueID
END
-- UPDATE THE APPROVAL LOG
INSERT INTO ApprovalLog
(IssueID, UserID, SubRoleID, OperationType, OperationLog, DocumentTypeID)
VALUES
(@IssueID, @UserID, @SubRoleID, 'Update', 'Update the document to step 0', @DocumentTypeID)
END
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
INSERT INTO EventLog
(UserID, DocumentType, IssueID, OperationType, InsertTimeStamp, Comments, SysDocumentID)
VALUES
(@UserID, 'Doc', @IssueID, 'Error', GETDATE(), 'Error while Approving the Doc ' + CONVERT(VARCHAR(10), @IssueID) + ' Doc# ' + CONVERT(VARCHAR(10), @IssueID), @IssueID)
SELECT
@ErrorMessage = ERROR_MESSAGE() + CONVERT(VARCHAR(10), @IssueID) + ' Doc# ' + CONVERT(VARCHAR(10), @IssueID) ,
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END
GO
CREATE FUNCTION [dbo].[fn_GetPRs]()
RETURNS TABLE
AS
RETURN
(
SELECT
'PartsRequest' AS DocumentType,
'PR' AS SubDoc,
PR.PRNumber AS IssueID,
PR.PRNumber AS DocID,
CreateDate AS IssueDate,
'' AS LotNos,
dbo.fnGetApprovalFullNames(PR.PRNumber, 10, ' / ', 0, 1, 1, 0, 0) AS 'PendingApprovers',
Title,
Description AS IssueDescription,
U1.FirstName + ' ' + U1.LastName AS Originator,
SubmitDate AS SubmitedDate,
CloseDate
FROM PartsRequest PR
INNER JOIN Users U1 ON PR.RequestorID = U1.UserID
)
GO
ALTER PROCEDURE [dbo].[GetDocuments]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT * FROM dbo.fn_GetLotDispositions()
UNION
SELECT * FROM dbo.fn_GetMRBs()
UNION
SELECT * FROM dbo.fn_GetECNs()
UNION
SELECT * FROM dbo.fn_GetLTWorkRequests()
UNION
SELECT * FROM dbo.fn_GetChangeControl()
UNION
SELECT * FROM dbo.fn_GetPRs()
ORDER BY DocumentType, SubDoc, IssueID DESC
END
GO
ALTER PROCEDURE [dbo].[GetRoles]
@WorkflowStepNumber INT,
@WhereClause NVARCHAR(1000),
@CheckForITARCompliant BIT,
@DocumentTypeID INT,
@IssueID INT,
@WorkFlowNumber INT = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Sql NVARCHAR(3000)
-- 2018/12/21 - Special Business Logic For Parts Requests
IF @DocumentTypeID = 10 AND @WhereClause LIKE '%Tech Lead%'
BEGIN
-- We need to assign a Tech Lead based on who is selected on the document itself
SELECT DISTINCT CONVERT(NVARCHAR(20),@IssueID) AS IssueID, R.RoleName, SRC.SubRoleCategoryItem AS [SubRole], U.UserID,
SR.SubRoleID, 0 AS [ItemStatus], WS.WorkflowStepNumber AS [Step], GETDATE() AS [AssignedDate], GETDATE() AS [NotifyDate],
GETDATE() AS [RoleAssignedDate], ApprovalType
FROM DocumentType D
INNER JOIN PartsRequest PR ON PR.PRNumber = @IssueID
INNER JOIN Workflows W ON D.DocumentTypeID = W.DocumentTypeID AND WorkFlowNumber = @WorkFlowNumber
INNER JOIN WorkFlowSteps WS ON W.WorkflowID = WS.WorkflowID
INNER JOIN Role R ON WS.RoleID = R.RoleID
INNER JOIN SubRole SR ON R.RoleID = SR.RoleID
INNER JOIN SubRoleCategory SRC ON SR.SubRoleCategoryID = SRC.SubRoleCategoryID
INNER JOIN Users U ON U.UserID = PR.TechLeadID
WHERE D.DocumentTypeID = @DocumentTypeID AND WorkflowStepNumber = @WorkflowStepNumber AND SRC.SubRoleCategoryItem = 'Tech Lead'
END
IF @CheckForITARCompliant = 0 -- do not check for ITAR compliant users
SET @Sql = N'
SELECT DISTINCT ' + CONVERT(NVARCHAR(20),@IssueID) + ' AS IssueID, R.RoleName, SRC.SubRoleCategoryItem AS ''SubRole'', U.UserID,
SR.SubRoleID, 0 AS ''ItemStatus'', WS.WorkflowStepNumber AS ''Step'', GETDATE() AS ''AssignedDate'', GETDATE() AS ''NotifyDate'',
GETDATE() AS ''RoleAssignedDate'', ApprovalType
FROM DocumentType D
INNER JOIN Workflows W ON D.DocumentTypeID = W.DocumentTypeID AND WorkFlowNumber = ' + CONVERT(VARCHAR(10),@WorkFlowNumber) + '
INNER JOIN WorkFlowSteps WS ON W.WorkflowID = WS.WorkflowID
INNER JOIN Role R ON WS.RoleID = R.RoleID
INNER JOIN SubRole SR ON R.RoleID = SR.RoleID
INNER JOIN SubRoleCategory SRC ON SR.SubRoleCategoryID = SRC.SubRoleCategoryID
INNER JOIN UserSubRole USR ON SR.SubRoleID = USR.SubRoleID
INNER JOIN Users U ON U.UserID = USR.UserID
WHERE D.DocumentTypeID = ' + CONVERT(VARCHAR(10),@DocumentTypeID) + ' AND WorkflowStepNumber = ' + CONVERT(VARCHAR(10),@WorkflowStepNumber) + ' AND SRC.SubRoleCategoryItem IN ' + @WhereClause
ELSE
SET @Sql = N'
SELECT DISTINCT ' + CONVERT(NVARCHAR(20),@IssueID) + ' AS IssueID, R.RoleName, SRC.SubRoleCategoryItem AS ''SubRole'', U.UserID,
SR.SubRoleID, 0 AS ''ItemStatus'', WS.WorkflowStepNumber AS ''Step'', GETDATE() AS ''AssignedDate'', GETDATE() AS ''NotifyDate'',
GETDATE() AS ''RoleAssignedDate'', ApprovalType
FROM DocumentType D
INNER JOIN Workflows W ON D.DocumentTypeID = W.DocumentTypeID AND WorkFlowNumber = ' + CONVERT(VARCHAR(10),@WorkFlowNumber) + '
INNER JOIN WorkFlowSteps WS ON W.WorkflowID = WS.WorkflowID
INNER JOIN Role R ON WS.RoleID = R.RoleID
INNER JOIN SubRole SR ON R.RoleID = SR.RoleID
INNER JOIN SubRoleCategory SRC ON SR.SubRoleCategoryID = SRC.SubRoleCategoryID
INNER JOIN UserSubRole USR ON SR.SubRoleID = USR.SubRoleID
INNER JOIN Users U ON U.UserID = USR.UserID
INNER JOIN dbo.fnGetITARUsers() IT ON U.LoginID = IT.UserID
WHERE D.DocumentTypeID = ' + CONVERT(VARCHAR(10),@DocumentTypeID) + ' AND WorkflowStepNumber = ' + CONVERT(VARCHAR(10),@WorkflowStepNumber) + ' AND SRC.SubRoleCategoryItem IN ' + @WhereClause
EXEC (@Sql)
END
GO
CREATE PROCEDURE [dbo].[PartsRequestGetListByUser]
@UserID int
AS
BEGIN
SELECT
PR.PRNumber,
Title,
[Description],
CreateDate,
SubmitDate,
CloseDate,
U2.FirstName + ' ' + U2.LastName AS Originator,
U1.FirstName + ' ' + U1.LastName AS Requestor,
U3.FirstName + ' ' + U3.LastName AS TechLead,
CASE
WHEN EXISTS (SELECT 1 FROM Approval a WHERE a.IssueID = PR.PRNumber AND a.DocumentTypeID = 10 AND ItemStatus = 2) THEN 'Denied'
ELSE [Status]
END AS [Status],
dbo.fnGetApprovalFullNames(PR.PRNumber, 10, ' / ', 0, 1, 1, 0, 0) AS 'PendingApprovers',
ws.WorkFlowStepName
FROM PartsRequest PR
INNER JOIN Workflows w ON w.DocumentTypeID = 10 AND w.WorkFlowNumber = 1
LEFT OUTER JOIN WorkflowSteps ws ON ws.WorkflowID = w.WorkflowID AND ws.WorkflowStepNumber = pr.CurrentStep
LEFT OUTER JOIN Users U3 ON PR.TechLeadID = U3.UserID
INNER JOIN Users U2 ON PR.OriginatorID = U2.UserID
LEFT OUTER JOIN Users U1 ON PR.RequestorID = U1.UserID
WHERE CloseDate IS NULL
END
GO
ALTER TABLE dbo.WorkflowSteps ADD
AllowReject bit NULL
GO
COMMIT
/*
set identity_insert DocumentType on
insert into DocumentType([DocumentTypeID], [DocumentType]) values (10, 'PartsRequest')
set identity_insert DocumentType off
set identity_insert dbo.[Role] on
INSERT INTO Role(RoleID, RoleName) VALUES (21, 'Parts Request')
set identity_insert dbo.[Role] off
set identity_insert dbo.Workflows on
INSERT INTO Workflows([WorkflowID], [WorkflowName], [DocumentTypeID], [WorkFlowNumber])
VALUES (9, 'PRWkFlow', 10, 1)
set identity_insert dbo.Workflows off
insert into SubRoleCategory(SubRoleCategory, SubRoleCategoryItem) VALUES ('Parts Request', 'Originator')
insert into SubRoleCategory(SubRoleCategory, SubRoleCategoryItem) VALUES ('Parts Request', 'Part Assignment')
insert into SubRoleCategory(SubRoleCategory, SubRoleCategoryItem) VALUES ('Parts Request', 'Pre-Approver')
insert into SubRoleCategory(SubRoleCategory, SubRoleCategoryItem) VALUES ('Parts Request', 'Tech Lead')
insert into SubRoleCategory(SubRoleCategory, SubRoleCategoryItem) VALUES ('Parts Request', 'Finance')
insert into SubRoleCategory(SubRoleCategory, SubRoleCategoryItem) VALUES ('Parts Request', 'SAP')
insert into SubRoleCategory(SubRoleCategory, SubRoleCategoryItem) VALUES ('Parts Request', 'Source List & Info Record')
insert into SubRoleCategory(SubRoleCategory, SubRoleCategoryItem) VALUES ('Parts Request', 'Buyer/Planner')
insert into SubRoleCategory(SubRoleCategory, SubRoleCategoryItem) VALUES ('Parts Request', 'Close to Folder')
INSERT INTO SubRole(SubRole, SubRoleCategoryID, RoleID)
SELECT 'PRSubRole', SubRoleCategoryID, 21
FROM SubRoleCategory
WHERE SubRoleCategory = 'Parts Request'
AND SubRoleCategoryID NOT IN (SELECT SubRoleCategoryID FROM SubRole)
ORDER BY SubRoleCategoryID
insert into WorkflowSteps([WorkflowID], [WorkflowStepNumber], [WorkFlowStepName], [RoleID], [RulesApply], [ApprovalType])
select 9, ROW_NUMBER() OVER (ORDER BY SubRoleCategoryID), SubRoleCategoryItem, 21, 0, 2
from SubRoleCategory
where SubRoleCategory.SubRoleCategory = 'Parts Request'
and SubRoleCategoryItem <> 'Originator'
order by SubRoleCategory.SubRoleCategoryID
update workflowsteps set allowreject = 1 where workflowid = 9 and workflowstepnumber <= 5
update workflowsteps set allowreject = 0 where workflowid = 9 and workflowstepnumber > 5
*/
Reference in New Issue View Git Blame Copy Permalink