forked from MRGhidini/SAP_Queries
-
Notifications
You must be signed in to change notification settings - Fork 0
/
FUNC - Contas pagas.sql
224 lines (203 loc) · 9.67 KB
/
FUNC - Contas pagas.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
USE [SBOKromi_Brazil]
GO
/****** Object: UserDefinedFunction [dbo].[GA_ContasPagarPagas] Script Date: 08/19/2013 14:56:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[GA_ContasPagarPagas] ( @P1 nvarchar(16),@P2 datetime2,@P3 datetime2 )
RETURNS TABLE AS RETURN
select k0.DocDate 'Data da Baixa',
k0.DueDate 'Data do Vencimento',
k0.TaxDate 'Data do Documento',
k0.[Formulário],
k0.DocEntry 'Chave do Pagamento',
k0.TransId 'Chave do Lançamento',
k0.LineId 'Linha do Lançamento',
k0.JrnlMemo 'Observações Diário',
k0.CardCode 'Cód.: PN / Conta',
k0.CardName 'Fornecedor / Conta',
k0.CNPJ,
--k0.debit 'Débito',
--k0.SumApplied 'Crédito',
round(isnull(k0.debit,0),2) - round(isnull(k0.SumApplied,0),2) 'Saldo',
'Kromi Joinville' 'Empresa',
k0.TrsfrAcct 'Conta do Banco'
from
(
--declare @P1 varchar(20) = '1.1.1.02.01.001'
select T1.RefDate DocDate,
T1.DueDate DueDate,
T1.TaxDate TaxDate,
'Lançamento Contábil' 'Formulário',
T0.TransId DocEntry,
T0.TransId TransId,
T1.Line_ID LineId,
T1.LineMemo JrnlMemo,
T1.ContraAct CardCode,
CASE when ISNULL(T2.AcctName,'') = '' then T3.CardName else T2.AcctName end CardName,
(select case when isnull(max(T10.TaxId0),'') = '' then isnull(max(T10.TaxId4),'') else isnull(max(T10.TaxId0),'') end from CRD7 T10 where T10.CardCode = T1.ContraAct and (isnull(T10.TaxId0,'') <> '' or isnull(T10.TaxId4,'') <> '') GROUP BY T10.CardCode) 'CNPJ',
T1.Debit 'Debit',
T1.Credit 'SumApplied',
(select top 1 T10.CompnyName From OADM T10) 'Empresa',
T1.Account 'TrsfrAcct'
from OJDT T0, JDT1 T1 LEFT OUTER JOIN OACT T2 ON T1.ContraAct = T2.AcctCode LEFT OUTER JOIN OCRD T3 on T1.ContraAct = T3.CardCode
WHERE T0.TransId = T1.TransId and
T1.Account in (SELECT isnull(T10.[TrsfrAcct],'') FROM OVPM T10 where isnull(T10.[TrsfrAcct],'') <> '' GROUP BY isnull(T10.[TrsfrAcct],'')) and
T0.TransType = 30 and
isnull(T0.Stornototr,'') = '' and
T0.TransId <> isnull((select max(T10.Stornototr) from OJDT T10 where T10.TransType = 30 and isnull(T10.Stornototr,0) <> 0 and isnull(T10.Stornototr,0) = T0.TransId),0)
union all
select T3.PostDate DocDate,
T7.DueDate,
T7.TaxDate TaxDate,
'Contas a pagar' 'Formulário',
T5.DocEntry DocEntry,
T0.TransId TransId,
T7.Line_ID LineId,
T7.LineMemo JornlMemo,
T5.CardCode CardCode,
T5.CardName CardName,
(select case when isnull(max(T10.TaxId0),'') = '' then isnull(max(T10.TaxId4),'') else isnull(max(T10.TaxId0),'') end from CRD7 T10 where T10.CardCode = T5.CardCode and (isnull(T10.TaxId0,'') <> '' or isnull(T10.TaxId4,'') <> '') GROUP BY T10.CardCode) 'CNPJ',
0 'Debit',
case when isnull(T6.SumApplied,0)-isnull(T6.WTOnhldPst,0) = T2.BoeSum then T2.BoeSum else case when T5.Doctotal <> 0 then (isnull(T6.SumApplied,0)-isnull(T6.WTOnhldPst,0))*(T2.BoeSum/T5.DocTotal) else 0 end end 'SumApplied',
(select top 1 T10.CompnyName From OADM T10) 'Empresa',
T1.ContraAct 'TrsfrAcct'
from OJDT T0, JDT1 T1,
OBOE T2,
OBOT T3 INNER JOIN BOT1 T4 ON T3.AbsEntry = T4.AbsEntry,
OVPM T5 INNER JOIN VPM2 T6 ON T5.DocEntry = T6.DocNum
INNER JOIN JDT1 T7 ON T7.TransId = T6.DocTransId and T7.Line_ID = T6.DocLine
WHERE T2.BoeStatus = 'P' and T3.AbsEntry = T1.SourceID and T2.BoeKey = T1.Ref1 and
T4.[BOEAbs] = T2.[BoeKey] and
T5.DocEntry = T2.[PmntNum] and
T5.DocType = 'S' and
T0.TransId = T1.TransId and
T1.ContraAct in (SELECT isnull(T10.[TrsfrAcct],'') FROM OVPM T10 where isnull(T10.[TrsfrAcct],'') <> '' GROUP BY isnull(T10.[TrsfrAcct],'')) and
T0.TransType = 182
union all
select T3.PostDate DocDate,
T3.PostDate,
T3.TaxDate TaxDate,
'Contas a pagar' 'Formulário',
T5.DocEntry DocEntry,
T0.TransId TransId,
T6.LineId LineId,
T5.JrnlMemo JornlMemo,
T6.AcctCode CardCode,
T6.AcctName CardName,
'' 'CNPJ',
0 'Debit',
case when isnull(T6.SumApplied,0) = T2.BoeSum then T2.BoeSum else case when T5.Doctotal <> 0 then isnull(T6.SumApplied,0)*(T2.BoeSum/T5.DocTotal) else 0 end end 'SumApplied',
(select top 1 T10.CompnyName From OADM T10) 'Empresa',
T1.ContraAct 'TrsfrAcct'
from OJDT T0, JDT1 T1,
OBOE T2,
OBOT T3 INNER JOIN BOT1 T4 ON T3.AbsEntry = T4.AbsEntry,
OVPM T5 INNER JOIN VPM4 T6 ON T5.DocEntry = T6.DocNum
WHERE T2.BoeStatus = 'P' and T3.AbsEntry = T1.SourceID and T2.BoeKey = T1.Ref1 and
T4.[BOEAbs] = T2.[BoeKey] and
T5.DocEntry = T2.[PmntNum] and
T5.DocType = 'A' and
T0.TransId = T1.TransId and
T1.ContraAct in (SELECT isnull(T10.[TrsfrAcct],'') FROM OVPM T10 where isnull(T10.[TrsfrAcct],'') <> '' GROUP BY isnull(T10.[TrsfrAcct],'')) and
T0.TransType = 182
union all
select T5.DocDate DocDate,
T7.DueDate,
T7.TaxDate TaxDate,
'Contas a pagar' 'Formulário',
T5.DocEntry DocEntry,
T0.TransId TransId,
T7.Line_ID LineId,
T7.LineMemo JornlMemo,
T5.CardCode CardCode,
T5.CardName CardName,
(select case when isnull(max(T10.TaxId0),'') = '' then isnull(max(T10.TaxId4),'') else isnull(max(T10.TaxId0),'') end from CRD7 T10 where T10.CardCode = T5.CardCode and (isnull(T10.TaxId0,'') <> '' or isnull(T10.TaxId4,'') <> '') GROUP BY T10.CardCode) 'CNPJ',
0 'Debit',
case when T5.Doctotal <> 0 then ((isnull(T6.SumApplied,0)-isnull(T6.WTOnhldPst,0))+(((isnull(T6.SumApplied,0)-isnull(T6.WTOnhldPst,0))*T5.UndOvDiff)/(select SUM(isnull(T10.SumApplied,0)-isnull(T10.WTOnhldPst,0)) from VPM2 T10 where T10.DocNum = T5.DocEntry) ) )*((case when isnull(T5.CashAcct,'') = @P1 then ISNULL(T5.Cashsum,0) else 0 end + case when isnull(T5.TrsfrAcct ,'') = @P1 then ISNULL(T5.TrsfrSum,0) else 0 end + case when isnull(T5.CheckAcct,'') = @P1 then ISNULL(T5.CheckSum,0) else 0 end)/T5.DocTotal) else 0 end 'SumApplied',
(select top 1 T10.CompnyName From OADM T10) 'Empresa',
T1.Account 'TrsfrAcct'
from OJDT T0, (select T10.Account, T10.TransId from JDT1 T10 where T10.TransType = 46 and T10.Account = @P1 group by T10.Account, T10.TransId) T1,
OVPM T5 INNER JOIN VPM2 T6 ON T5.DocEntry = T6.DocNum
INNER JOIN JDT1 T7 ON T7.TransId = T6.DocTransId and T7.Line_ID = T6.DocLine
WHERE T0.TransId = T1.TransId and
T0.TransType = 46 and
T5.TransId = T0.TransId and
T5.DocType = 'S' and
T5.Canceled <> 'Y'
union all
select T5.DocDate DocDate,
T5.DocDueDate ,
T5.TaxDate TaxDate,
'Contas a pagar' 'Formulário',
T5.DocEntry DocEntry,
T0.TransId TransId,
T6.LineId LineId,
T5.JrnlMemo JornlMemo,
T6.AcctCode CardCode,
T6.AcctName CardName,
'' 'CNPJ',
0 'Debit',
case when T5.Doctotal <> 0 then isnull(T6.SumApplied,0)*((case when isnull(T5.CashAcct,'') = @P1 then ISNULL(T5.Cashsum,0) else 0 end + case when isnull(T5.TrsfrAcct ,'') = @P1 then ISNULL(T5.TrsfrSum,0) else 0 end + case when isnull(T5.CheckAcct,'') = @P1 then ISNULL(T5.CheckSum,0) else 0 end)/T5.DocTotal) else 0 end 'SumApplied',
(select top 1 T10.CompnyName From OADM T10) 'Empresa',
T1.Account 'TrsfrAcct'
from OJDT T0, (select T10.Account, T10.TransId from JDT1 T10 where T10.TransType = 46 and T10.Account = @P1 group by T10.Account, T10.TransId) T1,
OVPM T5 INNER JOIN VPM4 T6 ON T5.DocEntry = T6.DocNum
WHERE T0.TransId = T1.TransId and
T0.TransType = 46 and
T5.TransId = T0.TransId and
T5.DocType = 'A' and
T5.Canceled <> 'Y'
union all
select T5.DocDate DocDate,
T7.DueDate,
T7.TaxDate TaxDate,
'Contas a receber' 'Formulário',
T5.DocEntry DocEntry,
T0.TransId TransId,
T7.Line_ID LineId,
T7.LineMemo JornlMemo,
T5.CardCode CardCode,
T5.CardName CardName,
(select case when isnull(max(T10.TaxId0),'') = '' then isnull(max(T10.TaxId4),'') else isnull(max(T10.TaxId0),'') end from CRD7 T10 where T10.CardCode = T5.CardCode and (isnull(T10.TaxId0,'') <> '' or isnull(T10.TaxId4,'') <> '') GROUP BY T10.CardCode) 'CNPJ',
case when T5.Doctotal <> 0 then ((isnull(T6.SumApplied,0)-isnull(T6.WTOnhldPst,0))+(((isnull(T6.SumApplied,0)-isnull(T6.WTOnhldPst,0))*T5.UndOvDiff)/(select SUM(isnull(T10.SumApplied,0)-isnull(T10.WTOnhldPst,0)) from RCT2 T10 where T10.DocNum = T5.DocEntry) ) )*((case when isnull(T5.CashAcct,'') = @P1 then ISNULL(T5.Cashsum,0) else 0 end + case when isnull(T5.TrsfrAcct ,'') = @P1 then ISNULL(T5.TrsfrSum,0) else 0 end + case when isnull(T5.CheckAcct,'') = @P1 then ISNULL(T5.CheckSum,0) else 0 end)/T5.DocTotal) else 0 end,
0,
(select top 1 T10.CompnyName From OADM T10) 'Empresa',
T1.Account 'TrsfrAcct'
from OJDT T0, (select T10.Account, T10.TransId from JDT1 T10 where T10.TransType = 24 and T10.Account = @P1 group by T10.Account, T10.TransId) T1,
ORCT T5 INNER JOIN RCT2 T6 ON T5.DocEntry = T6.DocNum
INNER JOIN JDT1 T7 ON T7.TransId = T6.DocTransId and T7.Line_ID = T6.DocLine
WHERE T0.TransId = T1.TransId and
T0.TransType = 24 and
T5.TransId = T0.TransId and
T5.DocType = 'C' and
T5.Canceled <> 'Y'
union all
select T5.DocDate DocDate,
T5.DocDueDate ,
T5.TaxDate TaxDate,
'Contas a receber' 'Formulário',
T5.DocEntry DocEntry,
T0.TransId TransId,
T6.LineId LineId,
T5.JrnlMemo JornlMemo,
T6.AcctCode CardCode,
T6.AcctName CardName,
'' 'CNPJ',
case when T5.Doctotal <> 0 then isnull(T6.SumApplied,0)*((case when isnull(T5.CashAcct,'') = @P1 then ISNULL(T5.Cashsum,0) else 0 end + case when isnull(T5.TrsfrAcct ,'') = @P1 then ISNULL(T5.TrsfrSum,0) else 0 end + case when isnull(T5.CheckAcct,'') = @P1 then ISNULL(T5.CheckSum,0) else 0 end)/T5.DocTotal) else 0 end,
0 ,
(select top 1 T10.CompnyName From OADM T10) 'Empresa',
T1.Account 'TrsfrAcct'
from OJDT T0, (select T10.Account, T10.TransId from JDT1 T10 where T10.TransType = 24 and T10.Account = @P1 group by T10.Account, T10.TransId) T1,
ORCT T5 INNER JOIN RCT4 T6 ON T5.DocEntry = T6.DocNum
WHERE T0.TransId = T1.TransId and
T0.TransType = 24 and
T5.TransId = T0.TransId and
T5.DocType = 'A' and
T5.Canceled <> 'Y'
) k0
where k0.DocDate >= @P2
and k0.DocDate <= @p3
and k0.TrsfrAcct = @P1
--order by k0.DocDate, k0.DocEntry