411 lines
26 KiB
Transact-SQL
411 lines
26 KiB
Transact-SQL
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
ALTER TABLE dbo.ECN ADD
|
||
FIChangeRequired bit NOT NULL CONSTRAINT DF_ECN_FIChangeRequired DEFAULT 0
|
||
GO
|
||
|
||
-- =============================================
|
||
-- Author: <Author,,Name>
|
||
-- Create date: <Create Date,,>
|
||
-- Description: <Description,,>
|
||
-- =============================================
|
||
ALTER PROCEDURE [dbo].[ECNUpdate]
|
||
-- Add the parameters for the stored procedure here
|
||
@ECNNumber INT,
|
||
@Title VARCHAR(200),
|
||
@IsECN BIT,
|
||
@IsTECN BIT,
|
||
@IsEmergencyTECN BIT,
|
||
@ExpirationDate DATETIME,
|
||
@ExtensionDate DATETIME,
|
||
@CancellationDate DATETIME,
|
||
@AcknowledgementRequired BIT,
|
||
@TrainingRequired BIT,
|
||
@AreaID INT,
|
||
@TechnologyID INT,
|
||
@PCRBRequired BIT,
|
||
@PCRBNumber VARCHAR(50),
|
||
@TestProgramChangeRequired BIT,
|
||
@SPCChangeRequired BIT,
|
||
@NewPartFlowRequired BIT,
|
||
@SPNChangeRequired BIT,
|
||
@ImplementationDetails VARCHAR(5000),
|
||
@ImpactOnEnvironment TINYINT,
|
||
@ImpactOnEnvironmentDescription VARCHAR(1000),
|
||
@ImpactOnCapacity TINYINT,
|
||
@ImpactOnCapacityDescription VARCHAR(1000),
|
||
@IsMA BIT,
|
||
@IsRH BIT,
|
||
@IsAU BIT,
|
||
@IsIndustrial BIT,
|
||
@MaterialConsumptionChangeRequired TINYINT,
|
||
@MaterialConsumptionChangeDescription VARCHAR(1000),
|
||
@ReasonForChange VARCHAR(5000),
|
||
@DescriptionOfChange VARCHAR(5000),
|
||
@NotAFlowChange BIT,
|
||
@AttachECN_TECNToLots BIT,
|
||
@SPNFlowChangeAtSingleStep BIT,
|
||
@SPNFlowChangeAtMultipleSteps BIT,
|
||
@CategoryID int = NULL,
|
||
@FIChangeRequired BIT = 0
|
||
AS
|
||
BEGIN
|
||
/*
|
||
2019-01-14 add FIChangeRequired
|
||
*/
|
||
-- SET NOCOUNT ON added to prevent extra result sets from
|
||
-- interfering with SELECT statements.
|
||
SET NOCOUNT ON;
|
||
|
||
-- Insert statements for procedure here
|
||
UPDATE ECN
|
||
SET
|
||
Title = @Title,
|
||
IsECN = @IsECN,
|
||
IsTECN = @IsTECN,
|
||
IsEmergencyTECN = @IsEmergencyTECN,
|
||
ExpirationDate = @ExpirationDate,
|
||
ExtensionDate = @ExtensionDate,
|
||
CancellationDate = @CancellationDate,
|
||
AcknowledgementRequired = @AcknowledgementRequired,
|
||
TrainingRequired = @TrainingRequired,
|
||
AreaID = @AreaID,
|
||
TechnologyID = @TechnologyID,
|
||
PCRBRequired = @PCRBRequired,
|
||
PCRBNumber = @PCRBNumber,
|
||
TestProgramChangeRequired = @TestProgramChangeRequired,
|
||
SPCChangeRequired = @SPCChangeRequired,
|
||
NewPartFlowRequired = @NewPartFlowRequired,
|
||
SPNChangeRequired = @SPNChangeRequired,
|
||
ImplementationDetails = @ImplementationDetails,
|
||
ImpactOnEnvironment = @ImpactOnEnvironment,
|
||
ImpactOnEnvironmentDescription = @ImpactOnEnvironmentDescription,
|
||
ImpactOnCapacity = @ImpactOnCapacity,
|
||
ImpactOnCapacityDescription = @ImpactOnCapacityDescription,
|
||
IsMA = @IsMA,
|
||
IsRH = @IsRH,
|
||
IsAU = @IsAU,
|
||
IsIndustrial = @IsIndustrial,
|
||
MaterialConsumptionChangeRequired = @MaterialConsumptionChangeRequired,
|
||
MaterialConsumptionChangeDescription = @MaterialConsumptionChangeDescription,
|
||
ReasonForChange = @ReasonForChange,
|
||
DescriptionOfChange = @DescriptionOfChange,
|
||
NotAFlowChange = @NotAFlowChange,
|
||
AttachECN_TECNToLots = @AttachECN_TECNToLots,
|
||
SPNFlowChangeAtSingleStep = @SPNFlowChangeAtSingleStep,
|
||
SPNFlowChangeAtMultipleSteps = @SPNFlowChangeAtMultipleSteps ,
|
||
LastUpdateTimeStamp = GETDATE(),
|
||
LastUpdateDate = GETDATE(),
|
||
CategoryID = @CategoryID,
|
||
FIChangeRequired = @FIChangeRequired
|
||
WHERE ECNNumber = @ECNNumber
|
||
END
|
||
GO
|
||
|
||
ALTER TABLE PartsRequest
|
||
DROP COLUMN [Status]
|
||
GO
|
||
|
||
ALTER TABLE PartsRequest
|
||
ADD [Status] AS (case when CurrentStep < 0 then 'Deleted' when [CloseDate] IS NOT NULL then 'Closed' when [SubmitDate] IS NOT NULL then 'Submitted' else 'New' end)
|
||
GO
|
||
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
ALTER 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],
|
||
dbo.fnGetApprovalFullNames(PRNumber, 10, ' / ', 0, 1, 1, 0, 0) AS 'PendingApprovers'
|
||
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
|
||
WHERE CurrentStep >= 0
|
||
END
|
||
GO
|
||
|
||
ALTER 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
|
||
AND CurrentStep >= 0
|
||
|
||
END
|
||
GO
|
||
|
||
ALTER 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
|
||
WHERE CurrentStep >= 0
|
||
)
|
||
GO
|
||
|
||
ALTER 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))
|
||
AND CurrentStep >= 0
|
||
) AS A
|
||
)
|
||
GO
|
||
|
||
CREATE PROCEDURE [dbo].[PartsRequestDelete]
|
||
@UserID INT,
|
||
@PRNumber INT
|
||
AS
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
|
||
IF EXISTS (SELECT 1 FROM PartsRequest WHERE PRNumber = @PRNumber AND CloseDate IS NOT NULL)
|
||
BEGIN
|
||
RAISERROR('Cannot delete closed parts request', 16, 1)
|
||
RETURN
|
||
END
|
||
|
||
UPDATE PartsRequest SET CurrentStep = -1 WHERE PRNumber = @PRNumber AND CloseDate IS NULL
|
||
|
||
IF @@ROWCOUNT = 0
|
||
BEGIN
|
||
RAISERROR('Error deleting parts request', 16, 1)
|
||
RETURN
|
||
END
|
||
|
||
INSERT INTO EventLog
|
||
(UserID, IssueID, DocumentType, OperationType, Comments)
|
||
VALUES
|
||
(@UserID, @PRNumber, 'PR', 'Delete', 'Deleted the document')
|
||
|
||
END
|
||
GO
|
||
|
||
ALTER PROCEDURE [dbo].[ECNGetPdfItem]
|
||
-- Add the parameters for the stored procedure here
|
||
@ECNNumber INT
|
||
AS
|
||
BEGIN
|
||
|
||
DECLARE @ParentECNNumber INT;
|
||
DECLARE @SuperParentECNNumber INT;
|
||
|
||
-- SET NOCOUNT ON added to prevent extra result sets from
|
||
-- interfering with SELECT statements.
|
||
|
||
--SELECT COUNT(*) FROM dbo.fnIsUserITARCompliant(114)
|
||
|
||
-- Insert statements for procedure here
|
||
SELECT E.ECNNumber
|
||
,E.Title
|
||
,E.IssueDate
|
||
,E.SubmitedDate
|
||
,E.CloseDate
|
||
,E.OriginatorID
|
||
,E.IsECN
|
||
,E.IsTECN
|
||
,E.IsEmergencyTECN
|
||
,E.ExpirationDate
|
||
,E.ExtensionDate
|
||
,E.CancellationDate
|
||
,E.AcknowledgementRequired
|
||
,E.TrainingRequired
|
||
,E.AreaID
|
||
,E.TechnologyID
|
||
,E.PCRBRequired
|
||
,E.PCRBNumber
|
||
,E.TestProgramChangeRequired
|
||
,E.SPCChangeRequired
|
||
,E.NewPartFlowRequired
|
||
,E.SPNChangeRequired
|
||
,REPLACE(E.ImplementationDetails, char(10), '<BR/>') AS ImplementationDetails
|
||
,E.ImpactOnEnvironment
|
||
,REPLACE(E.ImpactOnEnvironmentDescription, char(10), '<BR/>') AS ImpactOnEnvironmentDescription
|
||
,E.ImpactOnCapacity
|
||
,REPLACE(E.ImpactOnCapacityDescription, char(10), '<BR/>') AS ImpactOnCapacityDescription
|
||
,E.IsMA
|
||
,E.IsRH
|
||
,E.IsAU
|
||
,E.IsIndustrial
|
||
,E.MaterialConsumptionChangeRequired
|
||
,REPLACE(E.MaterialConsumptionChangeDescription, char(10), '<BR/>') AS MaterialConsumptionChangeDescription
|
||
,REPLACE(E.ReasonForChange, char(10), '<BR/>') AS ReasonForChange
|
||
,REPLACE(E.DescriptionOfChange, char(10), '<BR/>') AS DescriptionOfChange
|
||
,E.NotAFlowChange
|
||
,E.AttachECN_TECNToLots
|
||
,E.SPNFlowChangeAtSingleStep
|
||
,E.SPNFlowChangeAtMultipleSteps
|
||
,E.CurrentStep
|
||
,E.CancellationApprovalDate
|
||
,E.TECNExtensionState,
|
||
U.FirstName + ' ' + LastName AS 'OriginatorName', A.Area , T.Technology
|
||
,E.ConvertedFromNumber
|
||
,E.ConvertedToNumber
|
||
,E.FIChangeRequired
|
||
FROM ECN E
|
||
INNER JOIN Users U ON E.OriginatorID = U.UserID
|
||
LEFT JOIN ECNArea A ON E.AreaID = A.AreaID
|
||
LEFT JOIN ECNTechnology T ON E.TechnologyID = T.TechnologyID
|
||
WHERE ECNNumber = @ECNNumber
|
||
|
||
SELECT SRC.SubRoleCategoryItem AS AffectedDepartments
|
||
FROM ECNAffectedDepartment ED INNER JOIN SubRoleCategory SRC ON ED.DepartmentID = SRC.SubRoleCategoryID
|
||
WHERE ECNNumber = @ECNNumber
|
||
|
||
SELECT M.ModuleName AS AffectedModules
|
||
FROM ECNAffectedModule EM INNER JOIN ECNModule M ON EM.ModuleID = M.ModuleID
|
||
WHERE ECNNumber = @ECNNumber
|
||
|
||
SELECT [FileName] AS Attachments FROM
|
||
ECNAttachment
|
||
WHERE ECNNumber = @ECNNumber
|
||
|
||
|
||
|
||
|
||
SELECT @ParentECNNumber = ISNULL(ConvertedFromNumber, -1) FROM ECN
|
||
WHERE ECNNumber = @ECNNumber;
|
||
|
||
IF @ParentECNNumber <> -1 -- If the current document is an ECN and was converted from E-TECN -> TECN -> ECN
|
||
BEGIN
|
||
SELECT @SuperParentECNNumber = ISNULL(ConvertedFromNumber, -1) FROM ECN
|
||
WHERE ECNNumber = @ParentECNNumber;
|
||
|
||
END
|
||
|
||
|
||
|
||
SELECT U.FirstName + ' ' + U.LastName AS FullName, A.OperationType Operation, ISNULL(SRC.SubRoleCategoryItem, 'N/A') AS SubRole, LogDateTime AS OperationTime
|
||
FROM ApprovalLog A
|
||
INNER JOIN Users U ON A.UserID = U.UserID
|
||
LEFT JOIN SubRole SR ON A.SubRoleID = SR.SubRoleID
|
||
LEFT JOIN SubRoleCategory SRC ON SR.SubRoleCategoryID = SRC.SubRoleCategoryID
|
||
|
||
WHERE (OperationType = 'Approved' OR OperationType = 'Denied'
|
||
OR OperationType LIKE '%CHANGED%' OR OperationType LIKE '%Cancel%' OR OperationType LIKE '%Expir%'
|
||
OR OperationType LIKE '%initiated%'
|
||
)
|
||
AND (IssueID = @ECNNumber OR IssueID = @ParentECNNumber OR IssueID = @SuperParentECNNumber) AND (DocumentTypeID = 3 OR DocumentTypeID = 4 OR DocumentTypeID = 5)
|
||
ORDER By LogDateTime DESC
|
||
|
||
|
||
SELECT A.Area AS AffectedAreas
|
||
FROM ECNAffectedArea E INNER JOIN ECNArea A
|
||
ON E.AreaID = A.AreaID
|
||
WHERE ECNNumber = @ECNNumber
|
||
|
||
SELECT T.Technology AS Afefctedtechologies
|
||
FROM ECNAffectedTechnology E INNER JOIN ECNTechnology T
|
||
ON E.TechnologyID = T.TechnologyID
|
||
WHERE ECNNumber = @ECNNumber
|
||
|
||
SELECT A.AcknowledgementTrainingBy AS AcknowledgementBy
|
||
FROM ECNAcknowledgementBy E INNER JOIN ECNAcknowledgementTrainingBy A
|
||
ON E.AcknowledgementTrainingByID = A.AcknowledgementTrainingByID
|
||
WHERE ECNNumber = @ECNNumber
|
||
|
||
SELECT A.AcknowledgementTrainingBy AS TrainingBy
|
||
FROM ECNTrainingBy T INNER JOIN ECNAcknowledgementTrainingBy A
|
||
ON T.AcknowledgementTrainingByID = A.AcknowledgementTrainingByID
|
||
WHERE ECNNumber = @ECNNumber
|
||
|
||
END
|
||
GO
|
||
|
||
insert into SubRoleCategory([SubRoleCategory], [SubRoleCategoryItem]) values ('Execution', 'FI-Execution')
|
||
|
||
insert into SubRole(SubRole, SubRoleCategoryID, RoleID)
|
||
select 'ExecutionRole',
|
||
(select SubRoleCategoryID from SubRoleCategory where SubRoleCategoryItem = 'FI-Execution'),
|
||
(select RoleID FROM [Role] where RoleName = 'Execution')
|
||
|
||
GO
|