-- =============================================
-- Author:		<赵文兵>
-- Create date: <2021年4月2日10:34:40>
-- Description:	<创建视图V_LOT_POS_TST>
-- =============================================

IF EXISTS(SELECT 1 FROM sys.views WHERE name = 'V_LOT_POS_TST')

DROP VIEW MES.[V_LOT_POS_TST]
GO

CREATE VIEW [MES].[V_LOT_POS_TST]
AS
SELECT   MES.MM_LOT.LOT_NO, MES.MM_LOT.LOT_ID, MES.MM_LOT.LOT_NAME, MES.MM_LOT.LOT_TYPE, 
                MES.MM_LOT.PRD_TYPE, MES.MM_LOT.STATUS, MES.MM_LOT.DEF_NO, MES.MM_LOT.PCK_NO, 
                MES.MM_LOT.MST_LOT_NO, MES.MM_LOT.INIT_QTY, MES.MM_LOT.QUANTITY, MES.MM_LOT.UOM_ID, 
                MES.MM_LOT.MAT_TIME, MES.MM_LOT.EXPIRED_TIME, MES.MM_LOT.PHY_STATE, MES.MM_LOT.LOC_STATUS, 
                MES.MM_LOT.LOC_ID, MES.MM_LOT.LOT_CTRL_CODE, MES.MM_LOT.LOT_SUB_ID, MES.MM_LOT.PLAN_NO, 
                MES.MM_LOT.PO_NO, MES.MM_LOT.POS_MAT_NO, MES.MM_LOT.TGT_POS_NO, MES.MM_LOT.QC_STATUS, 
                MES.MM_LOT.QC_GRADE, MES.MM_LOT.CHK_STATUS, MES.MM_LOT.CHK_USER, MES.MM_LOT.SUPPLIER, 
                MES.MM_LOT.SRC_LOT_NO, MES.MM_LOT.TGT_LOT_NO, MES.MM_LOT.EQP_NO, MES.MM_LOT.CREATE_TIME, 
                MES.MM_LOT.CREATE_USER, MES.MM_LOT.UPDATE_TIME, MES.MM_LOT.UPDATE_USER, 
                MES.MM_LOT.REWORK_QTY, MES.MM_LOT.SCRAP_QTY, MES.MM_LOT.DGRADE_QTY, MES.MM_LOT.REMARK, 
                MES.MM_LOT.EXT_TYPE01, MES.MM_LOT.EXT_TYPE02, MES.MM_LOT.EXT_TYPE03, MES.MM_LOT.EXT_VALUE01, 
                MES.MM_LOT.EXT_VALUE02, MES.MM_LOT.EXT_VALUE03, MES.MM_LOT.EXT_VALUE04, 
                MES.MM_LOT.EXT_VALUE05, MES.MM_LOT.EXT_VALUE06, MES.MM_LOT.EXT_VALUE07, 
                MES.MM_LOT.EXT_VALUE08, MES.MM_LOT.EXT_VALUE09, MES.MM_LOT.EXT_VALUE10, MES.MM_LOT.EXT_QTY01, 
                MES.MM_LOT.EXT_QTY02, MES.MM_LOT.EXT_QTY03, MES.MM_LOT.EXT_QTY04, MES.MM_LOT.EXT_TIME01, 
                MES.MM_LOT.EXT_TIME02, MES.MM_DEFINITION.DEF_ID, MES.MM_DEFINITION.DEF_NAME, 
                MES.MM_DEFINITION.VERSION AS DEF_VERSION, MES.PO_PRODUCTION_ORDER.PO_ID, 
                MES.PO_PRODUCTION_ORDER.EXT_VALUE01 AS PROJECT_ID, 
                MES.PO_PRODUCTION_ORDER.EXT_VALUE02 AS PO_NUMBER, MES.PO_SEGMENT.POS_NAME, 
                MES.PO_SEGMENT.PS_POS, MES.PO_SEGMENT.PS_CODE, MES.PO_POS_TST.TEST_ID, 
                MES.PO_POS_TST.TEST_NAME, MES.PO_POS_TST.TEST_POS, MES.PDM_PRODUCTION_ROUTE.PPR_NO, 
                MES.PDM_PRODUCTION_ROUTE.PPR_ID, MES.PDM_PRODUCTION_ROUTE.PPR_NAME, 
                MES.PDM_PRODUCTION_ROUTE.VERSION AS PPR_VERSION, 
                MES.PDM_PRODUCTION_ROUTE.EXT_VALUE01 AS PDW_ID, MES.PO_SEGMENT.POS_NO, 
                MES.PO_PRODUCTION_ORDER.STATUS AS PO_STATUS, MES.MM_DEFINITION.STATUS AS DEF_STATUS, 
                MES.PO_SEGMENT.STATUS AS POS_STATUS, MES.PO_POS_TST.POS_TST_NO
FROM      MES.MM_LOT INNER JOIN
                MES.MM_DEFINITION ON MES.MM_LOT.DEF_NO = MES.MM_DEFINITION.DEF_NO INNER JOIN
                MES.PO_PRODUCTION_ORDER ON MES.MM_LOT.PO_NO = MES.PO_PRODUCTION_ORDER.PO_NO INNER JOIN
                MES.PO_SEGMENT ON MES.PO_PRODUCTION_ORDER.PO_NO = MES.PO_SEGMENT.PO_NO INNER JOIN
                MES.PO_POS_TST ON MES.PO_SEGMENT.POS_NO = MES.PO_POS_TST.POS_NO INNER JOIN
                MES.PDM_PRODUCTION_ROUTE ON 
                MES.PO_PRODUCTION_ORDER.PPR_NO = MES.PDM_PRODUCTION_ROUTE.PPR_NO