· 4 years ago · Jun 13, 2021, 07:46 PM
1USE [C52012MTI]
2GO
3/****** Object: StoredProcedure [dbo].[sp_MD_MT_PDA_Get_ItemTrack] Script Date: 13-06-2021 21:05:37 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9
10
11
12-- =============================================
13-- Author: Bjarne Morsing Larsen
14-- Create date: 06-08-2020
15-- Description: Get Item
16-- =============================================
17ALTER PROCEDURE [dbo].[sp_MD_MT_PDA_Get_ItemTrack]
18
19 @Company Varchar(3) = 'DAT',
20 @UseInvenLocation Int,
21 @Empty Int = 0,
22 @Item VarChar(20)
23
24
25AS
26BEGIN
27 Declare @PDAQTY Decimal(18,2)
28 -- SET NOCOUNT ON added to prevent extra result sets from
29 -- interfering with SELECT statements.
30 SET NOCOUNT ON;
31
32 -- Insert statements for procedure here
33
34 --DROP TABLE IF EXISTS #TMPPDA
35 IF OBJECT_ID('tempdb.dbo.#TMPPDA', 'U') IS NOT NULL DROP TABLE #TMPPDA
36 CREATE TABLE #TMPPDA
37 (
38 [DATASET] [varchar](3) COLLATE Danish_Norwegian_CI_AS NOT NULL,
39 [ITEMNUMBER] [varchar](20) COLLATE Danish_Norwegian_CI_AS NOT NULL,
40 [INVENLOCATION] [varchar](10) COLLATE Danish_Norwegian_CI_AS NOT NULL,
41 [PLACE] [varchar](20) COLLATE Danish_Norwegian_CI_AS NOT NULL,
42 [SUMQTY] [numeric](28, 2) NOT NULL,
43 );
44
45 --DROP TABLE IF EXISTS #TMPPDASUM
46 IF OBJECT_ID('tempdb.dbo.#TMPPDASUM', 'U') IS NOT NULL DROP TABLE #TMPPDASUM
47 CREATE TABLE #TMPPDASUM
48 (
49 [DATASET] [varchar](3) COLLATE Danish_Norwegian_CI_AS NOT NULL,
50 [ITEMNUMBER] [varchar](20) COLLATE Danish_Norwegian_CI_AS NOT NULL,
51 [INVENLOCATION] [varchar](10) COLLATE Danish_Norwegian_CI_AS NOT NULL,
52 [PLACE] [varchar](20) COLLATE Danish_Norwegian_CI_AS NOT NULL,
53 [SUMQTY] [numeric](28, 2) NOT NULL,
54 );
55
56
57 INSERT INTO #TMPPDA
58 Select DATASET,ITEMNUMBER,FROM_INVENLOCATION AS [INVENLOCATION],FROM_PLACEMENT AS PLACE,Sum(Cast(Qty *-1 AS Decimal(18,2))) AS Qty from MD_PDAINVMOVE
59 WHERE (DATASET = @Company) AND POSTED = 0
60 AND (LTRIM(ITEMNUMBER) = @Item)
61 Group by DATASET,ITEMNUMBER,FROM_INVENLOCATION,FROM_PLACEMENT
62 UNION
63 Select DATASET,ITEMNUMBER,TO_INVENLOCATION AS [INVENLOCATION],TO_PLACEMENT,Sum(Cast(Qty AS Decimal(18,2))) AS Qty from MD_PDAINVMOVE
64 WHERE (DATASET = @Company) AND POSTED = 0
65 AND (LTRIM(ITEMNUMBER) = @Item)
66 Group by DATASET,ITEMNUMBER,TO_INVENLOCATION,TO_PLACEMENT
67
68 INSERT INTO #TMPPDASUM
69 Select DATASET,ITEMNUMBER, [INVENLOCATION],PLACE ,Sum(Cast(SUMQTY AS Decimal(18,2))) AS Qty from #TMPPDA
70 WHERE (DATASET = @Company)
71 AND (LTRIM(ITEMNUMBER) = @Item)
72 Group by DATASET,ITEMNUMBER,INVENLOCATION,PLACE
73
74
75
76
77
78 IF @Empty = 0
79 BEGIN
80 SELECT ROWNUMBER, ITEMNUMBER,
81 REPLACE(INVENLOCATION, CHAR(2), '') AS INVENLOCATION,
82 SERIALNUMBER AS PLACEMENT,
83 CAST(INVENTORY AS decimal(18, 2)) AS INVENTORY,
84
85 Isnull((Select Top(1) SUMQTY From #TMPPDASUM Where #TMPPDASUM.DATASET = INVENITEMTRACK.DATASET
86 AND #TMPPDASUM.ITEMNUMBER = INVENITEMTRACK.ITEMNUMBER
87 AND #TMPPDASUM.INVENLOCATION = INVENITEMTRACK.INVENLOCATION
88 AND #TMPPDASUM.PLACE = INVENITEMTRACK.SERIALNUMBER),0)
89 AS PDAQTY
90 FROM INVENITEMTRACK
91 where Dataset = @Company AND Ltrim(Itemnumber) = @Item AND (INVENTORY ) > 0
92
93
94 AND (
95 (@UseInvenLocation = 0 AND Ltrim(INVENLOCATION) = Char(2))
96 OR
97 (@UseInvenLocation = 1 AND 1=1))
98
99
100 --WHERE (DATASET = 'DAT') AND (LTRIM(ITEMNUMBER) = 'PW3B00K002')
101 UNION
102 Select 0,ITEMNUMBER,REPLACE(INVENLOCATION, CHAR(2), '') AS INVENLOCATION,PLACE,0,SUMQTY
103 from #TMPPDASUM WHERE #TMPPDASUM.PLACE NOT IN(
104 Select SERIALNUMBER FROM INVENITEMTRACK
105 where Dataset = @Company AND Ltrim(Itemnumber) = @Item AND (INVENTORY ) > 0
106
107 )
108
109 END
110 ELSE
111 BEGIN
112
113 SELECT ROWNUMBER,
114 ITEMNUMBER,
115 REPLACE(INVENLOCATION, CHAR(2), '') AS LOCATION,
116 SERIALNUMBER AS PLACE,
117 CAST(INVENTORY AS decimal(18, 2)) AS Inventory,
118
119 Isnull((Select Top(1) SUMQTY From #TMPPDASUM Where #TMPPDASUM.DATASET = INVENITEMTRACK.DATASET
120 AND #TMPPDASUM.ITEMNUMBER = INVENITEMTRACK.ITEMNUMBER
121 AND #TMPPDASUM.INVENLOCATION = INVENITEMTRACK.INVENLOCATION
122 AND #TMPPDASUM.PLACE = INVENITEMTRACK.SERIALNUMBER),0)
123 AS PDAQTY
124 FROM INVENITEMTRACK
125 where Dataset = @Company AND Ltrim(Itemnumber) = @Item
126
127 END
128END