91 lines
3.8 KiB
Transact-SQL
91 lines
3.8 KiB
Transact-SQL
ALTER VIEW [dbo].[View_Batch_BatchTrust]
|
|
AS
|
|
/********轟愆官辜********/
|
|
SELECT BatchTrust.TrustBatchId,
|
|
BatchTrust.TrustBatchCode,
|
|
(CASE WHEN BatchTrust.TrustType='1' THEN BatchTrust.TrustBatchCode
|
|
WHEN BatchTrust.TrustType='2' THEN BatchTrust.TrustBatchCode+'K1'
|
|
WHEN BatchTrust.TrustType='3' THEN BatchTrust.TrustBatchCode+'R1' END) AS NewTrustBatchCode,
|
|
BatchTrust.TrustDate,
|
|
BatchTrust.NDEUnit,
|
|
BatchTrust.ProjectId,
|
|
project.ProjectCode,
|
|
project.ProjectName,
|
|
BatchTrust.UnitId,
|
|
BatchTrust.UnitWorkId,
|
|
BatchTrust.TrustType,
|
|
BatchTrust.DetectionTypeId,
|
|
BatchTrust.DetectionRateId,
|
|
BatchTrust.IsCheck,
|
|
BatchTrust.IsAudit,
|
|
BatchTrust.TopointBatch,
|
|
BatchTrust.PointBatchId,
|
|
Unit.UnitCode,
|
|
Unit.UnitName,
|
|
UnitWork.UnitWorkCode,
|
|
UnitWork.UnitWorkName,
|
|
DetectionType.DetectionTypeCode,
|
|
detectionRate.DetectionRateValue,
|
|
ndtCheck.TrustBatchId AS CheckTrustBatchId
|
|
FROM dbo.HJGL_Batch_BatchTrust AS BatchTrust
|
|
LEFT JOIN Base_Unit AS Unit ON Unit.UnitId=BatchTrust.UnitId
|
|
LEFT JOIN WBS_UnitWork AS UnitWork ON UnitWork.UnitWorkId=BatchTrust.UnitWorkId
|
|
LEFT JOIN Base_DetectionType AS DetectionType ON DetectionType.DetectionTypeId=BatchTrust.DetectionTypeId
|
|
LEFT JOIN dbo.Base_Project project ON project.ProjectId = BatchTrust.ProjectId
|
|
LEFT JOIN dbo.HJGL_Batch_NDE ndtCheck ON ndtCheck.TrustBatchId = BatchTrust.TrustBatchId
|
|
left join Base_DetectionRate detectionRate on detectionRate.DetectionRateId = BatchTrust.DetectionRateId
|
|
go
|
|
|
|
|
|
ALTER VIEW [dbo].[View_Batch_BatchTrustItem]
|
|
AS
|
|
/********轟愆官辜********/
|
|
SELECT
|
|
ROW_NUMBER() OVER(ORDER BY WeldJointCode) AS Number,
|
|
BatchTrustItem.TrustBatchItemId,
|
|
BatchTrustItem.TrustBatchId,
|
|
BatchTrustItem.PointBatchItemId,
|
|
BatchTrustItem.WeldJointId,
|
|
BatchTrustItem.CreateDate,
|
|
BatchTrustItem.TrustNum,
|
|
BatchTrust.TrustType,
|
|
BatchTrustItem.RepairNum,
|
|
BatchTrust.TrustBatchCode, --官辜데뵀
|
|
BatchTrust.ProjectId,
|
|
BatchTrust.UnitWorkId,
|
|
UnitWork.UnitWorkCode, --데貫묏넋
|
|
pipe.PipelineCode, --밗窟뵀
|
|
pipingClass.PipingClassCode, --밗窟된섬
|
|
jot.WeldJointCode+isnull((select top 1 RepairMark from [dbo].[HJGL_RepairRecord] where [WeldJointId]=BatchTrustItem.WeldJointId and RepairRecordId=BatchTrustItem.RepairRecordId),'')+(case when PointBatchItem.PointState='2' then 'K' else '' end) as WeldJointCode, --보왯뵀
|
|
mat.MaterialCode, --꼼醴
|
|
jot.JointArea, --보쌈혐堵
|
|
welder.WelderCode AS WelderCode, --보묏뵀
|
|
weldType.WeldTypeCode, --보룹잚謹
|
|
jot.Dia, --棍쓺
|
|
jot.Size, --당쓺
|
|
jot.Thickness, --귿비
|
|
method.WeldingMethodCode, --보쌈렘랬
|
|
rate.DetectionRateCode,
|
|
null as CheckDefects,
|
|
PointBatchItem.PointDate, --듐왯휑퍅
|
|
grooveType.GrooveTypeCode,--팃왯近駕
|
|
BatchTrust.DetectionTypeId
|
|
FROM dbo.HJGL_Batch_BatchTrustItem AS BatchTrustItem
|
|
LEFT JOIN dbo.HJGL_Batch_BatchTrust AS BatchTrust ON BatchTrust.TrustBatchId=BatchTrustItem.TrustBatchId
|
|
LEFT JOIN dbo.HJGL_Batch_PointBatchItem AS PointBatchItem ON PointBatchItem.PointBatchItemId=BatchTrustItem.PointBatchItemId
|
|
LEFT JOIN dbo.HJGL_Batch_PointBatch point ON point.PointBatchId = PointBatchItem.PointBatchId
|
|
LEFT JOIN dbo.Base_DetectionRate rate ON rate.DetectionRateId = point.DetectionRateId
|
|
LEFT JOIN dbo.HJGL_WeldJoint jot ON jot.WeldJointId = BatchTrustItem.WeldJointId
|
|
LEFT JOIN dbo.HJGL_Pipeline pipe ON pipe.PipelineId = jot.PipelineId
|
|
LEFT JOIN Base_PipingClass AS pipingClass ON PipingClass.PipingClassId=pipe.PipingClassId
|
|
LEFT JOIN dbo.WBS_UnitWork UnitWork ON UnitWork.UnitWorkId = BatchTrust.UnitWorkId
|
|
LEFT JOIN dbo.Base_WeldType weldType ON weldType.WeldTypeId = jot.WeldTypeId
|
|
LEFT JOIN dbo.SitePerson_Person welder ON welder.PersonId = jot.BackingWelderId and welder.ProjectId=BatchTrust.ProjectId
|
|
LEFT JOIN dbo.Base_Material mat ON mat.MaterialId = jot.Material1Id
|
|
LEFT JOIN dbo.Base_WeldingMethod method ON method.WeldingMethodId = jot.WeldingMethodId
|
|
left join Base_GrooveType grooveType on grooveType.GrooveTypeId = jot.GrooveTypeId
|
|
|
|
GO
|
|
|
|
|