58 lines
2.9 KiB
MySQL
58 lines
2.9 KiB
MySQL
|
|
|||
|
|
ALTER TABLE dbo.FC_SESRelatedData ADD OtherDef NVARCHAR(300) NULL
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
|
|||
|
|
ALTER VIEW [dbo].[View_Contractor_Evaluation]
|
|||
|
|
AS
|
|||
|
|
/*********框架承包商评估表**********/
|
|||
|
|
SELECT t.*,(t.Spending_commitment+t.Forecasted) AS Total,
|
|||
|
|
(CASE WHEN t.YearDiff<>0 THEN CONVERT(DECIMAL(18,2),(t.Spending_commitment+t.Forecasted)/t.YearDiff) ELSE NULL END) AS YearAvg
|
|||
|
|
FROM
|
|||
|
|
(SELECT datas.ID, datas.FO_NO, datas.TechnicalBonus,datas.SafetyBonus,
|
|||
|
|
(CASE WHEN con.Contractor IS NULL THEN con.ContractorCN
|
|||
|
|
WHEN con.ContractorCN IS NULL THEN con.Contractor ELSE con.Contractor+con.ContractorCN END)AS Contractor,
|
|||
|
|
datas.DisciplineId,
|
|||
|
|
(CASE WHEN dis.Discipline IS NULL THEN dis.DisciplineCN
|
|||
|
|
WHEN dis.DisciplineCN IS NULL THEN dis.Discipline
|
|||
|
|
ELSE dis.Discipline+dis.DisciplineCN END) AS Discipline,
|
|||
|
|
datas.Buyer,
|
|||
|
|
datas.BuyerId,
|
|||
|
|
datas.Main_Coordinator AS Main_CoordinatorId,
|
|||
|
|
u2.UserName AS Main_Coordinator,
|
|||
|
|
datas.Contract_Admin,
|
|||
|
|
datas.Validate_Date,
|
|||
|
|
datas.Expire_Date,
|
|||
|
|
(CASE WHEN datas.Validate_Date IS NOT NULL AND datas.Expire_Date IS NOT NULL THEN CONVERT(DECIMAL(9,2),DATEDIFF(DAY,datas.Validate_Date,datas.Expire_Date)*1.00/365) ELSE 0 END ) AS YearDiff,
|
|||
|
|
v.Spending_commitment AS Spending_commitment,
|
|||
|
|
(CASE WHEN datas.Proportion_of_FC_Definition IS NOT NULL THEN CONVERT(VARCHAR(10),CONVERT(DECIMAL(9,2),datas.Proportion_of_FC_Definition*100.0))+'%' END) AS VolumeAllocation,
|
|||
|
|
CONVERT(DECIMAL(18,2), ISNULL(datas.Forecasted,0)) AS Forecasted,
|
|||
|
|
datas.ReviewOfFC,datas.PriceEvaluation1,datas.PriceEvaluation2,datas.PriceLevel,datas.enumeration,datas.NCRIsReview,datas.NCRReviewNum,
|
|||
|
|
datas.IsInquiry,datas.InquiryNum,datas.AuditResult,datas.BoQIsAudit,datas.BoQAuditComments,datas.IsOthers,datas.OthersNum,datas.OtherDef,datas.Proposed
|
|||
|
|
|
|||
|
|
FROM FC_SESRelatedData AS datas
|
|||
|
|
LEFT JOIN dbo.Base_Contractor AS con ON con.ContractorId = datas.Contractor
|
|||
|
|
LEFT JOIN dbo.Sys_User AS u2 ON u2.UserId = datas.Main_Coordinator
|
|||
|
|
LEFT JOIN dbo.Base_Discipline AS dis ON dis.DisciplineId = datas.DisciplineId
|
|||
|
|
LEFT JOIN
|
|||
|
|
(SELECT datas.FO_NO,
|
|||
|
|
(CONVERT(DECIMAL(18,2),ISNULL(c.CheckedValue,0))+CONVERT(DECIMAL(18,2),ISNULL(a.Commitment,0))) AS Spending_commitment
|
|||
|
|
FROM FC_SESRelatedData AS datas
|
|||
|
|
LEFT JOIN dbo.Base_Discipline AS dis ON dis.DisciplineId = datas.DisciplineId
|
|||
|
|
LEFT JOIN dbo.Base_FOType AS ft ON ft.FOTypeId = datas.FOTypeId
|
|||
|
|
LEFT JOIN(SELECT SUM(ISNULL(SSR_Actual_cost,0)) AS CheckedValue,FO FROM dbo.FC_SESReport
|
|||
|
|
WHERE Accepted='X' AND Deleted <> 'X' AND Blocked <> 'X' GROUP BY FO) AS c ON c.FO=datas.FO_NO
|
|||
|
|
LEFT JOIN(SELECT SUM(ISNULL(SSR_budget,0)) AS Commitment,FO FROM dbo.FC_SESReport
|
|||
|
|
WHERE Accepted <> 'X' AND Deleted <> 'X' AND Blocked <> 'X' GROUP BY FO) AS a ON a.FO=datas.FO_NO
|
|||
|
|
--WHERE ft.FOType!='OEM' AND dis.Discipline NOT LIKE '%Basf Expert Service%'
|
|||
|
|
-- AND datas.FC_Status!='Closed' AND (datas.Expire_Date IS NOT NULL AND DATEDIFF(MONTH,Expire_Date,GETDATE())<=1)
|
|||
|
|
|
|||
|
|
)v ON v.FO_NO = datas.FO_NO
|
|||
|
|
)t
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
|