· 4 years ago · May 04, 2021, 11:22 AM
1-- 2021.05.03.AM
2-- 2021.05.03.AM - TDU_ContasRH
3
4--######################################### TDU ############################# TDU ############################################
5-- ===========================================================================================================================
6-- Create Table TDU_ContasRH =================================================================================================
7-- Autor: António Muteca ====================================================================================================
8-- Data: 2021.05.AM ==========================================================================================================
9-- ===========================================================================================================================
10 IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE name='TDU_ContasRH' AND Type ='U')
11 BEGIN
12 CREATE TABLE TDU_ContasRH
13 (
14 CDU_Conta NVARCHAR(8) NOT NULL
15 ,CDU_AMPRimavera NVARCHAR(5) NOT NULL
16 ,CDU_DescricaoPrimavera NVARCHAR(100)
17 ,CDU_DescricaoSAGE NVARCHAR(150)
18 ,CDU_DebitoCredito NVARCHAR(1) NOT NULL CHECK(CDU_DebitoCredito='C' OR CDU_DebitoCredito='D')
19 PRIMARY KEY (CDU_Conta, CDU_AMPrimavera)
20 --TODO.AM - Add Constraints
21 )
22
23 IF NOT EXISTS (SELECT 1 FROM StdTabelasVar WHERE Tabela='TDU_ContasRH') AND EXISTS(SELECT 1 FROM sys.objects WHERE name ='TDU_ContasRH' AND Type ='U')
24 BEGIN
25 INSERT INTO StdTabelasVar VALUES('TDU_ContasRH','ERP')
26 END
27
28 DELETE FROM StdCamposVar WHERE Tabela='TDU_ContasRH'
29
30 INSERT INTO StdCamposVar(Tabela, Campo,Descricao,Texto,Visivel,Query,Ordem)
31 VALUES ('TDU_ContasRH','CDU_Conta','Conta','Conta',1,'',1)
32 ,('TDU_ContasRH','CDU_AMPRimavera','AM. Primavera','AM. Primavera',1,'',2)
33 ,('TDU_ContasRH','CDU_DescricaoPrimavera','Descrição Primavera','Descrição Primavera',1,'',3)
34 ,('TDU_ContasRH','CDU_DescricaoSAGE','Descrição SAGE','Descrição SAGE',1,'',4)
35 ,('TDU_ContasRH','CDU_DebitoCredito','Debito/Crédito','Debito/Crédito',1,'',5)
36 END
37 GO
38 -- DROP TABLE TDU_ContasRH
39 -- DELETE FROM TDU_ContasRH
40 -- SELECT * FROM TDU_ContasRH
41
42
43 --SELECT ABS(SUM(a.CURRENCY_DEBIT_AMOUNT) - SUM(a.CURRENCY_CREDIT_AMOUNT)) from
44 (SELECT F.Nome
45 ,M.Ano AS 'ACCOUNTING_YEAR'
46 ,T.CDU_Conta AS 'ACCOUNT'
47 ,M.Funcionario AS 'CODE_C'
48 ,C.CCusto AS 'CODE_E'
49 ,'EXT' AS 'CODE_H'
50 ,C.CCusto AS 'CODE_I'
51 ,M.Moeda AS 'CURRENCY_CODE'
52 ,IIF(T.CDU_DebitoCredito='D',M.Valor,0) AS 'CURRENCY_DEBIT_AMOUNT'
53 ,IIF(T.CDU_DebitoCredito='C',M.Valor,0) AS 'CURRENCY_CREDIT_AMOUNT'
54 ,M.DataMov AS 'TRANSACTION_DATE'
55 ,M.
56
57 --,(SELECT SUM(Valor) FROM MovimentosFuncionarios WHERE MesFiscal=2 GROUP BY Funcionario )
58
59
60FROM MovimentosFuncionarios M
61INNER JOIN TDU_ContasRH T ON T.CDU_AMPrimavera = M.CodMov
62INNER JOIN Funcionarios F ON F.Codigo =M.Funcionario
63INNER JOIN FuncCCusto C ON M.Funcionario = C.Funcionario
64WHERE M.MesFiscal=2)
65--a
66
67--SELECT * FROM TDU_ContasRH
68--SELECT * FROM MovimentosFuncionarios
69--SELECT * FROM FuncCCusto
70
71SELECT Remuneracao AS CodMov, 1 AS TipoTabela, 'C' AS CD, Descricao AS Descricao_ERP FROM Remuneracoes