ZMDaJGtx

1
2-- =======================================================================================================================
3-- Create Table tbl_LOG_GestaoReclamacoes ================================================================================
4-- Autor: Pedro Neves ====================================================================================================
5-- Data: 2019.12.01 ======================================================================================================
6-- =======================================================================================================================
7IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name ='tbl_LOG_GestaoReclamacoes' AND Type ='U')
8BEGIN
9CREATE TABLE tbl_LOG_GestaoReclamacoes
10(
11
12Id INT IDENTITY(1,1) NOT NULL
13, TipoDocRCL NVARCHAR(5) NOT NULL
14, SerieRCL NVARCHAR(5) NOT NULL
15, NumDocRCL INT NOT NULL
16, FilialRCL NVARCHAR(5) NOT NULL
17, Utilizador NVARCHAR(20) NOT NULL
18, Data DATETIME NOT NULL
19, TipoDoc NVARCHAR(5) NOT NULL
20, Serie NVARCHAR(5) NOT NULL
21, NumDoc INT NOT NULL
22, Filial NVARCHAR(5) NOT NULL
23, Observacoes NVARCHAR(500) NULL
24, CONSTRAINT tbl_LOG_GestaoReclamacoes_Id PRIMARY KEY (Id)
25
26)
27END
28ELSE
29BEGIN
30PRINT 'A Tabela tbl_LOG_GestaoReclamacoes já existe na base de dados'
31
32END
33GO
34
35-- =======================================================================================================================
36-- Create Procedure sp_Insert_tbl_LOG_GestaoReclamacoes ==================================================================
37-- Autor: Pedro Neves ====================================================================================================
38-- Data: 2019.12.01 ======================================================================================================
39-- =======================================================================================================================
40IF EXISTS (SELECT 1 FROM sys.objects WHERE Name ='sp_Insert_tbl_LOG_GestaoReclamacoes' AND Type='P')
41BEGIN
42DROP PROCEDURE sp_Insert_tbl_LOG_GestaoReclamacoes
43END
44GO
45CREATE PROCEDURE sp_Insert_tbl_LOG_GestaoReclamacoes
46(
47@TipoDocRCL NVARCHAR(5)
48, @SerieRCL NVARCHAR(5)
49, @NumDocRCL INT
50, @FilialRCL NVARCHAR(5)
51, @Utilizador NVARCHAR(20)
52, @TipoDoc NVARCHAR(5)
53, @Serie NVARCHAR(5)
54, @NumDoc INT
55, @Filial NVARCHAR(5)
56, @Observacoes NVARCHAR(500)
57)
58AS
59BEGIN
60BEGIN TRY
61
62--Insere o Registo
63INSERT INTO tbl_LOG_GestaoReclamacoes
64(
65TipoDocRCL
66, SerieRCL
67, NumDocRCL
68, FilialRCL
69, Utilizador
70, Data
71, TipoDoc
72, Serie
73, NumDoc
74, Filial
75, Observacoes
76)
77VALUES
78(
79@TipoDocRCL
80, @SerieRCL
81, @NumDocRCL
82, @FilialRCL
83, @Utilizador
84, GETDATE()
85, @TipoDoc
86, @Serie
87, @NumDoc
88, @Filial
89, @Observacoes
90)
91END TRY
92BEGIN CATCH
93DECLARE @Num_Erro INT, @Msg_Erro VARCHAR(5000)
94
95SELECT @Num_Erro = ERROR_NUMBER(), @Msg_Erro = ERROR_MESSAGE()
96
97RAISERROR ('sp_Insert_tbl_LOG_GestaoReclamacoes: %d: %s', 16, 1, @Num_Erro, @Msg_Erro);
98
99END CATCH;
100END
101GO
102
103
104-- =======================================================================================================================
105-- Create Table tbl_EstadosReclamacoes ===================================================================================
106-- Autor: Pedro Neves ====================================================================================================
107-- Data: 2019.12.01 ======================================================================================================
108-- =======================================================================================================================
109IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name ='tbl_EstadosReclamacoes' AND Type ='U')
110BEGIN
111CREATE TABLE tbl_EstadosReclamacoes
112(
113IdLinhaStk UniqueIdentifier NOT NULL
114, TipoReclamacao NVARCHAR(2) NOT NULL
115, Estado NVARCHAR(2) NOT NULL
116, Responsabilidade NVARCHAR(2) NULL
117, Restituicao Bit NULL
118, Aceite Bit NULL
119, CustosDevolucao Money Null
120, CONSTRAINT tbl_EstadosReclamacoes_IdLinhaStk PRIMARY KEY (IdLinhaStk)
121, CONSTRAINT [tbl_EstadosReclamacoes_IdLinhasStk_FK] FOREIGN KEY(IdLinhaStk) REFERENCES [dbo].[LinhasStk] ([ID])
122
123)
124END
125ELSE
126BEGIN
127PRINT 'A Tabela tbl_EstadosReclamacoes já existe na base de dados'
128END
129GO
130
131
132
133-- =======================================================================================================================
134-- Create Procedure sp_Insert_tbl_EstadosReclamacoes =====================================================================
135-- Autor: Pedro Neves ====================================================================================================
136-- Data: 2019.12.01 ======================================================================================================
137-- =======================================================================================================================
138IF EXISTS (SELECT 1 FROM sys.objects WHERE Name ='sp_Insert_tbl_EstadosReclamacoes' AND Type='P')
139BEGIN
140DROP PROCEDURE sp_Insert_tbl_EstadosReclamacoes
141END
142GO
143
144CREATE PROCEDURE sp_Insert_tbl_EstadosReclamacoes
145(
146@IdLinhaStk UniqueIdentifier
147, @TipoReclamacao NVARCHAR(2)
148, @Estado NVARCHAR(2)
149, @Responsabilidade NVARCHAR(2)
150, @Restituicao Bit
151, @Aceite Bit
152, @CustosDevolucao Money
153)
154AS
155BEGIN
156BEGIN TRY
157
158--Insere o Registo
159INSERT INTO tbl_EstadosReclamacoes
160(
161IdLinhaStk
162, TipoReclamacao
163, Estado
164, Responsabilidade
165, Restituicao
166, Aceite
167, CustosDevolucao
168)
169VALUES
170(
171@IdLinhaStk
172, @TipoReclamacao
173, @Estado
174, @Responsabilidade
175, @Restituicao
176, @Aceite
177, @CustosDevolucao
178)
179END TRY
180BEGIN CATCH
181DECLARE @Num_Erro INT, @Msg_Erro VARCHAR(5000)
182
183SELECT @Num_Erro = ERROR_NUMBER(), @Msg_Erro = ERROR_MESSAGE()
184
185RAISERROR ('sp_Insert_tbl_EstadosReclamacoes: %d: %s', 16, 1, @Num_Erro, @Msg_Erro);
186
187END CATCH;
188END
189GO
190
191-- =======================================================================================================================
192-- Create Procedure sp_Update_tbl_EstadosReclamacoes =====================================================================
193-- Autor: Pedro Neves ====================================================================================================
194-- Data: 2019.12.01 ======================================================================================================
195-- =======================================================================================================================
196IF EXISTS (SELECT 1 FROM sys.objects WHERE Name ='sp_Update_tbl_EstadosReclamacoes' AND Type='P')
197BEGIN
198DROP PROCEDURE sp_Update_tbl_EstadosReclamacoes
199END
200GO
201
202CREATE PROCEDURE sp_Update_tbl_EstadosReclamacoes
203(
204@IdLinhaStk UniqueIdentifier
205, @Estado NVARCHAR(2)
206, @Responsabilidade NVARCHAR(2)
207, @Restituicao Bit
208, @Aceite Bit
209, @CustosDevolucao Money
210)
211AS
212BEGIN
213BEGIN TRY
214
215-- Verifica se o Registo existe:
216IF NOT EXISTS(SELECT 1 FROM tbl_EstadosReclamacoes WHERE IdLinhaStk = @IdLinhaStk )
217BEGIN
218RAISERROR ('Erro: %d: %s', 16, 1, 0, 'O Registo não existe na tabela tbl_EstadosReclamacoes.');
219END
220ELSE
221BEGIN
222-- Actualiza a tabela
223UPDATE T SET Estado=@Estado
224, Responsabilidade = @Responsabilidade
225, Restituicao = @Restituicao
226, Aceite = @Aceite
227, CustosDevolucao = @CustosDevolucao
228FROM tbl_EstadosReclamacoes T
229WHERE IdLinhaStk = @IdLinhaStk
230END
231
232
233END TRY
234BEGIN CATCH
235DECLARE @Num_Erro INT, @Msg_Erro VARCHAR(5000)
236
237SELECT @Num_Erro = ERROR_NUMBER(), @Msg_Erro = ERROR_MESSAGE()
238
239RAISERROR ('sp_Update_tbl_EstadosReclamacoes: %d: %s', 16, 1, @Num_Erro, @Msg_Erro);
240
241END CATCH;
242
243END
244GO
245
246-- =======================================================================================================================
247-- Alter Table dbo.CabecCompras ==========================================================================================
248-- Campo: CDU_IDPE =======================================================================================================
249-- Autor: Pedro Neves ====================================================================================================
250-- Data: 2019.12.01 ======================================================================================================
251-- =======================================================================================================================
252IF NOT EXISTS(SELECT 1 FROM sys.objects O INNER JOIN sys.all_columns C ON O.object_id = C.object_id
253WHERE C.Name = 'CDU_IDPE' AND O.name = 'CabecCompras')
254BEGIN
255
256ALTER TABLE dbo.CabecCompras ADD
257CDU_IDPE uniqueidentifier NULL
258
259ALTER TABLE dbo.CabecCompras ADD CONSTRAINT
260FK_CabecCompras_CabecCompras FOREIGN KEY
261(
262CDU_IDPE
263) REFERENCES dbo.CabecCompras
264(
265Id
266) ON UPDATE NO ACTION
267ON DELETE NO ACTION
268
269INSERT INTO StdCamposVar (tabela,Campo,Descricao,Texto, Visivel, Ordem,ExportarTTE, DadosSensiveis)
270VALUES ('CabecCompras','CDU_IDPE','ID PE','ID PE',0,1,0,0)
271
272END
273ELSE
274PRINT 'CDU_IDPE - Já existe na Tabela: CabecCompras'
275GO
276
277-- =======================================================================================================================
278-- Create Procedure sp_DevolveArtigosPendentesEmRCL ======================================================================
279-- Autor: Pedro Neves ====================================================================================================
280-- Data: 2019.12.01 ======================================================================================================
281-- =======================================================================================================================
282IF EXISTS (SELECT 1 FROM sys.objects WHERE Name ='sp_DevolveArtigosPendentesEmRCL' AND Type='P')
283BEGIN
284DROP PROCEDURE sp_DevolveArtigosPendentesEmRCL
285END
286GO
287
288CREATE PROCEDURE sp_DevolveArtigosPendentesEmRCL
289(
290@TipoDoc as nvarchar(5)
291,@Serie as nvarchar(5)
292,@NumDoc as integer
293,@Filial as nvarchar(3)
294)
295AS
296BEGIN
297
298SELECT er.TipoReclamacao,
299ls.Artigo, ls.Descricao, ls.Quantidade as QuantidadeReclamada, ls.id as IdLinhaStk,
300-isnull((
301select sum(lc2.Quantidade) as QuantidadeEntregue from CabecCompras cc2 inner join
302LinhasCompras lc2 on cc2.Id = lc2.IdCabecCompras
303where cc2.id = cs.CDU_IDPE
304and lc2.Artigo = ls.Artigo
305),0) as QuantidadeRecebida,
306
307-isnull((
308select sum(LCECF.Quantidade) as QuantidadeEncomendada from CabecCompras cc2 inner join
309LinhasCompras lc2 on cc2.Id = lc2.IdCabecCompras left join
310LinhasComprasTrans lctvfa on lctvfa.IdLinhasCompras = lc2.Id left join
311LinhasComprasTrans lctecf on lctecf.IdLinhasCompras = lctvfa.IdLinhasComprasOrigem left join
312LinhasCompras LCECF on LCECF.Id = lctecf.IdLinhasCompras
313where cc2.id = cs.CDU_IDPE
314and lc2.Artigo = ls.Artigo
315),0) as QuantidadeEncomendada
316
317FROM dbo.LinhasSTK LS INNER JOIN
318dbo.CabecSTK cs INNER JOIN
319dbo.CabecCompras cc ON cs.CDU_IDPE = cc.Id ON ls.IdCabecOrig = cs.Id LEFT OUTER JOIN
320dbo.tbl_EstadosReclamacoes er ON ls.Id = er.IdLinhaStk
321WHERE
322(cs.TipoDoc = @TipoDoc)
323AND (cs.Serie = @Serie)
324AND (cs.NumDoc = @NumDoc)
325AND (cs.Filial = @Filial)
326AND (ls.Armazem = N'RCL')
327AND (ls.EntradaSaida = N'E')
328AND er.Estado ='P'
329
330END
331GO
332
333-- =======================================================================================================================
334-- Create Procedure sp_DevolvePrecoArtigoParaNotaCredito =================================================================
335-- Autor: Pedro Neves ====================================================================================================
336-- Data: 2019.12.01 ======================================================================================================
337-- =======================================================================================================================
338IF EXISTS (SELECT 1 FROM sys.objects WHERE Name ='sp_DevolvePrecoArtigoParaNotaCredito' AND Type='P')
339BEGIN
340DROP PROCEDURE sp_DevolvePrecoArtigoParaNotaCredito
341END
342GO
343
344CREATE PROCEDURE sp_DevolvePrecoArtigoParaNotaCredito
345(
346@TipoDoc as nvarchar(5)
347,@Serie as nvarchar(5)
348,@NumDoc as integer
349,@Filial as nvarchar(3)
350,@CodArtigo as NVarChar(50)
351)
352AS
353BEGIN
354
355SELECT
356isnull((
357select (lc2.PrecoLiquido / lc2.Quantidade ) as PrecoLiquido from CabecCompras cc2 with (nolock) inner join
358LinhasCompras lc2 with (nolock) on cc2.Id = lc2.IdCabecCompras
359where cc2.id = cs.CDU_IDPE
360and lc2.Artigo = ls.Artigo
361and lc2.Artigo = @CodArtigo
362),0) as Preco
363
364FROM dbo.LinhasSTK LS with (nolock) INNER JOIN
365dbo.CabecSTK cs with (nolock) INNER JOIN
366dbo.CabecCompras cc with (nolock) ON cs.CDU_IDPE = cc.Id ON ls.IdCabecOrig = cs.Id LEFT OUTER JOIN
367dbo.tbl_EstadosReclamacoes er with (nolock) ON ls.Id = er.IdLinhaStk
368WHERE
369(cs.TipoDoc = @TipoDoc)
370AND (cs.Serie = @Serie)
371AND (cs.NumDoc = @NumDoc)
372AND (cs.Filial = @Filial)
373AND (ls.Armazem = N'RCL')
374AND (ls.EntradaSaida = N'E')
375AND ls.Artigo = @CodArtigo
376AND er.Estado ='P'
377
378END
379GO
380
381-- =======================================================================================================================
382-- Create Procedure SP_DevolveRCLs =======================================================================================
383-- Autor: Pedro Neves ====================================================================================================
384-- Data: 2019.12.01 ======================================================================================================
385-- =======================================================================================================================
386IF EXISTS (SELECT 1 FROM sys.objects WHERE Name ='SP_DevolveRCLs' AND Type='P')
387BEGIN
388DROP PROCEDURE SP_DevolveRCLs
389END
390GO
391
392CREATE PROCEDURE SP_DevolveRCLs
393AS
394BEGIN
395
396SELECT DISTINCT dbo.CabecStk.TipoDoc, dbo.CabecStk.Serie, dbo.CabecStk.NumDoc, dbo.CabecStk.Filial, e.Nome, dbo.CabecSTK.CDU_IDPE
397FROM dbo.CabecStk INNER JOIN
398dbo.LinhasStk ON dbo.CabecStk.Id = dbo.LinhasStk.IdCabecOrig LEFT JOIN
399dbo.tbl_EstadosReclamacoes ON dbo.LinhasStk.id = dbo.tbl_EstadosReclamacoes.idLinhaStk left join
400dbo.v_entidades e on e.TipoEntidade = dbo.CabecStk.TipoEntidade and e.Entidade = dbo.CabecStk.Entidade
401WHERE (dbo.CabecStk.TipoDoc = 'RCL') AND (dbo.tbl_Estadosreclamacoes.Estado = N'P')
402END
403GO