|
-- =============================================
-- 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
?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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
