-- =============================================-- Description: <>-- Author: <>-- Create date: <>-- =============================================
CREATE PROCEDURE [dbo].[pro_get_parts_list] @IN_MODEL_CD1 NVARCHAR(5), @IN_MODEL_CD2 NVARCHAR(10), @IN_LOT INTEGER AS
BEGIN
SET NOCOUNT ON;
DECLARE @CTMP_BLOCK_NO INTEGER DECLARE @CTMP_LINE_NO INTEGER DECLARE @CTMP_LINE_BRANCH_NO INTEGER DECLARE @CTMP_DRAW_CD1 NVARCHAR(5) DECLARE @CTMP_DRAW_CD2 NVARCHAR(10) DECLARE @CTMP_ORDER_FLG NVARCHAR(1)
DECLARE @iCount INT=0 DECLARE @iLoop INT=1 IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '#TMP_M_PARTSLIST') DROP TABLE #TMP_M_PARTSLIST CREATE TABLE #TMP_M_PARTSLIST ( ID INT IDENTITY(1,1) ,BLOCK_NO INT NOT NULL ,LINE_NO INT NOT NULL ,LINE_BRANCH_NO INT NOT NULL ,DRAW_CD1 NVARCHAR(5) ,DRAW_CD2 NVARCHAR(10) ,USE_QUANTITY NUMERIC(15,4) ,LEVEL INT ,ORDER_FLG TINYINT ,ASSY_PLACE TINYINT ,ASSYNO NVARCHAR(8) ,FACTORY_KBN TINYINT ,DEL_PLACE NVARCHAR(20) ,SHOW_FLG TINYINT ,CONSTRAINT pk_tmp_m_partslist PRIMARY KEY ( BLOCK_NO ,LINE_NO ,LINE_BRANCH_NO ) )
INSERT INTO #TMP_M_PARTSLIST WITH(TABLOCK) SELECT BLOCK_NO ,LINE_NO ,LINE_BRANCH_NO ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL FROM( SELECT T1.BLOCK_NO AS BLOCK_NO, T1.LINE_NO AS LINE_NO, T1.LINE_BRANCH_NO AS LINE_BRANCH_NO FROM T_PART_CONS_HISTORY AS T1 WHERE 1=1 AND T1.MODEL_CD1 = @IN_MODEL_CD1 AND T1.MODEL_CD2 = @IN_MODEL_CD2 AND T1.PRC_KBN <> '3' UNION SELECT T2.BLOCK_NO AS BLOCK_NO, T2.LINE_NO AS LINE_NO, T2.LINE_BRANCH_NO AS LINE_BRANCH_NO FROM M_ORDERTRANS AS T2 WHERE 1=1 AND T2.MODEL_CD1 = @IN_MODEL_CD1 AND T2.MODEL_CD2 = @IN_MODEL_CD2 ) AS T SELECT @iCount=MAX(ID) FROM #TMP_M_PARTSLIST
WHILE @iLoop <= @iCount BEGIN print '@iLoop:'+convert(nvarchar,@iLoop) SELECT @CTMP_BLOCK_NO= BLOCK_NO, @CTMP_LINE_NO=LINE_NO, @CTMP_LINE_BRANCH_NO=LINE_BRANCH_NO FROM #TMP_M_PARTSLIST WHERE ID=@iLoop
SET @iLOOP = @iLOOP + 1
UPDATE #TMP_M_PARTSLIST SET DRAW_CD1=@TMP_DRAW_CD1 ,DRAW_CD2=@TMP_DRAW_CD2 ,USE_QUANTITY=@TMP_USE_QTY ,LEVEL=@TMP_LEVEL ,ORDER_FLG=@TMP_ORDER_FLG ,ASSY_PLACE=@TMP_ASSY_PLACE ,ASSYNO=@TMP_ASSY_NO ,FACTORY_KBN=@TMP_FACTORY_KBN ,DEL_PLACE=@TMP_DEL_PLACE ,SHOW_FLG=1 WHERE 1=1 AND BLOCK_NO = @CTMP_BLOCK_NO AND LINE_NO = @CTMP_LINE_NO AND LINE_BRANCH_NO = @CTMP_LINE_BRANCH_NO
END
TRUNCATE TABLE #TMP_M_PARTSLIST IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '#TMP_M_PARTSLIST') DROP TABLE #TMP_M_PARTSLISTEND
