|
-- =============================================
-- 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
?
?DECLARE @TMP_TBL TABLE(
??????? 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
?????????????????????????? )
?INSERT INTO @TMP_TBL
?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_TBL
?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_TBL WHERE ID=@iLoop
???SET @iLOOP = @iLOOP + 1
??? UPDATE @TMP_TBL
??? 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???? DELETE FROM @TMP_TBL??? END
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
