Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
fbio_bilicki
Active Contributor


Bom dia a todos,

Abaixo está as consultas desenvolvidas até o momento para gerar Contas à Pagar, Contas Pagas, Contas à receber e Contas recebidas.

Sempre tive essa visão de divisão dos relatórios financeiros, esse método ajuda atualmente bastante o pessoal do meu financeiro.

Se encontrarem algum problema na consulta, esteja a vontade para sugerir correções e melhorias, já que são baseadas no no modo que uso o SAP.

Contas à receber

-- Notas fiscais de saída
SELECT
'NS' as 'Tipo'
, MAX(T0.[DocNum]) as 'ID'
,( ISNULL((SELECT TOP 1 T4.NfmName from ONFM T4 WHERE T4.AbsEntry = T0.Model),'') + ' / ' + t0.SeriesStr + ' / ' + CAST(T0.SERIAL AS VARCHAR) ) as 'Documento'

, (CAST( MAX(T1.[InstlmntID]) as varchar) + ' de ' +
(SELECT CAST(MAX(T3.[InstlmntID]) AS VARCHAR) FROM [dbo].[INV6] T3 WHERE T3.[DocEntry] = T0.[DocEntry]) ) as Parcela
, MAX(T0.[TaxDate]) as 'Dt.Emissão'
, MAX(T0.[DocDate]) as 'Dt.Entrada'
, MAX(T1.[DueDate]) as 'Dt.Vencimento'
, MAX(T0.[CardCode]) as 'Codigo PN'
, MAX(T0.[CardName]) as 'Nome PN'
, MAX(T0.[TransId]) as TransId
, MAX(T1.[InsTotal]) as 'TotalTitulo'
, ( MAX(T1.[InsTotal]) - MAX(T1.[PaidToDate]) ) as 'TotalSaldo'
, MAX(T0.[Comments]) as 'Observação'
, T0.[ObjType]
, (SELECT MAX(T3.BaseRef) FROM INV1 T3 WHERE T3.DocEntry = T0.DocEntry) as BaseRef
FROM [dbo].[OINV] T0
INNER JOIN [dbo].[INV6] T1 ON T1.[DocEntry] = T0.[DocEntry]
LEFT OUTER JOIN [dbo].[INV5] T2 ON T0.[DocEntry] = T2.[AbsEntry]
WHERE (T1.[TotalBlck] <> T1.[InsTotal] AND
( T1.[InsTotal] - T1.[PaidToDate] ) <> 0 )
GROUP BY T0.[DocEntry], T1.[InstlmntID], T0.[ObjType], T0.Model, T0.SeriesStr, T0.Serial

UNION ALL
-- Lançamentos manuais
SELECT 'LC30' as Tipo
, T0.[TransId] as 'ID'
, CAST( T0.[TransId] as varchar) as 'Documento'
, '1 de 1' as 'Parcela'
, T0.[TaxDate] as 'Dt.Emissão'
, T0.[RefDate] as 'Dt.Entrada'
, T0.[DueDate] as 'Dt.Vencimento'
, T2.[CardCode] as 'Codigo PN'
, T2.[CardName] as 'Nome PN'
, T0.[TransId]
, T0.[Debit] as 'TotalTitulo'
, T0.[BalDueDeb] as 'TotalSaldo'
, T0.[LineMemo] as 'Observação'
, T0.[ObjType]
, 0 as BaseRef
FROM [dbo].[JDT1] T0
INNER JOIN [dbo].[OJDT] T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN [dbo].[OCRD] T2 ON T0.[ShortName] = T2.[CardCode]
where T0.[TransType] = 30 and T0.[BalDueDeb] > 0

UNION ALL

-- Baixa por conta.
SELECT 'LC30' as Tipo
, T0.[TransId] as 'ID'
, CAST( T0.[TransId] as varchar) as 'Documento'
, '1 de 1' as 'Parcela'
, T0.[TaxDate] as 'Dt.Emissão'
, T0.[RefDate] as 'Dt.Entrada'
, T0.[DueDate] as 'Dt.Vencimento'
, T2.[CardCode] as 'Codigo PN'
, T2.[CardName] as 'Nome PN'
, T0.[TransId]
, T0.[Credit] as 'TotalTitulo'
, T0.[BalDueCred] as 'TotalSaldo'
, T0.[LineMemo] as 'Observação'
, T0.[ObjType]
, 0 as BaseRef
FROM [dbo].[JDT1] T0
INNER JOIN [dbo].[OJDT] T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN [dbo].[OCRD] T2 ON T0.[ShortName] = T2.[CardCode]
where T0.[TransType] = 30 and T0.[BalDueCred] <> 0

UNION ALL
-- Boletos
SELECT 'BOL' as Tipo
, T0.[BoeNum] as 'ID'
, ('BOL/'+ CAST( T0.[BoeNum] as varchar)) as 'Documento'
, '1 de 1' as 'Parcela'
, T0.[Pmntdate] as 'Dt.Emissão'
, T0.[Pmntdate] as 'Dt.Entrada'
, T0.[DueDate] as 'Dt.Vencimento'
, T0.[CardCode] as 'Codigo PN'
, T0.[CardName] as 'Nome PN'
, T0.[BoeNum]
, T0.[BoeSum] as 'TotalTitulo'
, T0.[BoeSum] as 'TotalSaldo'
-- , 'Boleto' as 'Observação' -- agora mostra o número das notas fiscais de saída (somente objtype=13 por enquanto)
, 'Observação' = ( select
( ISNULL((SELECT TOP 1 T14.NfmName from ONFM T14 WHERE T14.AbsEntry = T12.Model),'') + ' / ' + T12.SeriesStr + ' / ' + CAST(T12.SERIAL AS VARCHAR) ) + ', '
from ORCT T10
INNER JOIN RCT2 T11 ON T11.DocNum = T10.DocEntry
INNER JOIN OINV T12 ON T12.DocEntry = T11.DocEntry and T12.ObjType = T11.InvType
WHERE T10.BoeAbs = T0.BoeKey
FOR XML PATH('')
)
, 0 as 'ObjType'
, T0.RefNum as BaseRef
FROM [dbo].[OBOE] T0
where T0.BoeType = 'I' and (T0.BoeStatus = 'G' or T0.BoeStatus = 'S')

Contas Recebidas

select T0.DocNum as 'NumBaixa', T0.DocEntry as 'NumNR', T0.SumApplied as 'ValorBaixa', T1.DocDate as 'DataBaixa',
CASE
WHEN T0.InvType = 13 THEN (SELECT MAX(T3.BaseRef) FROM INV1 T3 WHERE T3.DocEntry = T0.DocEntry)
END as 'BaseRef',
CASE
WHEN T0.InvType = 13 THEN (SELECT TOP 1 ( ISNULL((SELECT TOP 1 T4.NfmName from ONFM T4 WHERE T4.AbsEntry = T3.Model),'') + ' / ' + ISNULL(T3.SeriesStr,'') + ' / ' + CAST(T3.Serial AS VARCHAR) )
FROM OINV T3 WHERE T3.DocNum = T0.DocEntry )
WHEN T0.InvType = 30 THEN 'LC /' + CAST(T0.DocNum AS VARCHAR)
END as 'Documento',
CASE
WHEN T0.InvType = 13 THEN
(CAST( T0.[DocLine] + 1 as varchar) + ' de ' +
(SELECT CAST(MAX(T3.[InstlmntID]) AS VARCHAR) FROM [dbo].[INV6] T3 WHERE T3.[DocEntry] = T0.[DocEntry]) )
WHEN T0.InvType = 30 THEN '1 de 1'
END as 'Parcela',
CASE
WHEN T0.InvType = 13 THEN (SELECT TOP 1 T3.[TaxDate] FROM OINV T3 WHERE T3.DocNum = T0.DocEntry)
WHEN T0.InvType = 30 THEN (SELECT TOP 1 T3.[TaxDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
END as 'DataEmissao',

CASE
WHEN T0.InvType = 13 THEN (SELECT TOP 1 T3.[DocDate] FROM OINV T3 WHERE T3.DocNum = T0.DocEntry)
WHEN T0.InvType = 30 THEN (SELECT TOP 1 T3.[RefDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
END as 'DataEntrada',
CASE
WHEN T0.InvType = 13 THEN (SELECT TOP 1 T3.[DueDate] FROM INV6 T3 WHERE T3.DocEntry = T0.DocEntry and T3.InstlmntID = T0.DocLine + 1)
WHEN T0.InvType = 30 THEN (SELECT TOP 1 T3.[DueDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
END as 'DataVencimento',

CASE
WHEN T0.InvType = 13 THEN (SELECT TOP 1 T3.[InsTotal] FROM INV6 T3 WHERE T3.DocEntry = T0.DocEntry and T3.InstlmntID = T0.DocLine + 1)
WHEN T0.InvType = 30 THEN (SELECT TOP 1 T3.[LocTotal] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
END as 'ValorDocumento',

(ISNULL( (SELECT TOP 1 T3.AcctName from OACT T3 WHERE T3.AcctCode = T1.TrsfrAcct and T1.TrsfrSum <> 0 ) + ', ', '') +
ISNULL( (SELECT TOP 1 T3.AcctName from OACT T3 WHERE T3.AcctCode = T1.CashAcct and T1.CashSum <> 0) + ', ', '') +
ISNULL( (SELECT TOP 1 T4.AcctName from VPM1 T3 INNER JOIN OACT T4 ON (T4.AcctCode = T3.CheckAct) WHERE T3.DocNum = T1.DocNum and T1.CheckSum > 0 )+ ', ', '' ))
as 'TipoBaixa',
isnull((select max(T4.ExtrMatch) from JDT1 T4 where (T4.Transid = T1.Transid and T4.ExtrMatch <> 0) ),0) as 'Reconciliacao',
(ISNULL( (SELECT TOP 1 T3.Finanse from OACT T3 WHERE T3.AcctCode = T1.TrsfrAcct and T1.TrsfrSum <> 0 ) + ', ', '') +
ISNULL( (SELECT TOP 1 T3.Finanse from OACT T3 WHERE T3.AcctCode = T1.CashAcct and T1.CashSum <> 0) + ', ', '') +
ISNULL( (SELECT TOP 1 T4.Finanse from VPM1 T3 INNER JOIN OACT T4 ON (T4.AcctCode = T3.CheckAct) WHERE T3.DocNum = T1.DocNum and T1.CheckSum > 0 )+ ', ', '' ))
as 'ContaFinanceira',

T1.TrsfrAcct,
T1.CashAcct,
T1.CheckAcct,
T1.CardCode,
T5.U_UpCodAnt,
T1.CardName,
T1.BoeNum

FROM RCT2 T0
INNER JOIN ORCT T1 ON T1.DocEntry = T0.DocNum
LEFT JOIN OCRD T5 ON T5.CardCode = T1.CardCode
WHERE T1.Canceled = 'N' AND T1.BoeStatus is null
order by T0.DocNum desc

Contas à Pagar

SELECT
'NE' as 'Tipo'
, MAX(T0.[DocNum]) as 'ID'
,( (SELECT TOP 1 T4.NfmName from ONFM T4 WHERE T4.AbsEntry = T0.Model) + ' / ' + t0.SeriesStr + ' / ' + CAST(T0.SERIAL AS VARCHAR) ) as 'Documento'

, (CAST( MAX(T1.[InstlmntID]) as varchar) + ' de ' +
(SELECT CAST(MAX(T3.[InstlmntID]) AS VARCHAR) FROM [dbo].[PCH6] T3 WHERE T3.[DocEntry] = T0.[DocEntry]) ) as Parcela
, MAX(T0.[TaxDate]) as 'Dt.Emissão'
, MAX(T0.[DocDate]) as 'Dt.Entrada'
, MAX(T1.[DueDate]) as 'Dt.Vencimento'
, MAX(T0.[CardCode]) as 'Codigo PN'
, MAX(T0.[CardName]) as 'Nome PN'
, MAX(T0.[TransId]) as TransId
, MAX(T1.[InsTotal]) as 'TotalTitulo'
, ( MAX(T1.[InsTotal]) - MAX(T1.[PaidToDate]) ) as 'TotalSaldo'
, ISNULL(MAX(T0.[Comments]),'') as 'Observação'
, T0.[ObjType]
, (SELECT MAX(T3.BaseRef) FROM PCH1 T3 WHERE T3.DocEntry = T0.DocEntry) as BaseRef
FROM [dbo].[OPCH] T0
INNER JOIN [dbo].[PCH6] T1 ON T1.[DocEntry] = T0.[DocEntry]
LEFT OUTER JOIN [dbo].[PCH5] T2 ON T0.[DocEntry] = T2.[AbsEntry]
WHERE T1.[TotalBlck] <> T1.[InsTotal] AND
(T1.[InsTotal] - T1.[PaidToDate]) <> 0
GROUP BY T0.[DocEntry], T1.[InstlmntID], T0.[ObjType], T0.Model, T0.SeriesStr, T0.Serial
UNION ALL

SELECT 'LC46' as Tipo
, T0.[TransId] as 'ID'
, CAST( T0.[TransId] as varchar) as 'Documento'
, '1 de 1' as 'Parcela'
, T0.[TaxDate] as 'Dt.Emissão'
, T0.[RefDate] as 'Dt.Entrada'
, T0.[DueDate] as 'Dt.Vencimento'
, T2.[CardCode] as 'Codigo PN'
, T2.[CardName] as 'Nome PN'
, T0.[TransId]
, (T0.[Debit] * -1) as 'TotalTitulo'
, (T0.[BalDueDeb] * -1) as 'TotalSaldo'
, T0.[LineMemo] as 'Observação'
, T0.[ObjType]
, 0 as BaseRef
FROM [dbo].[JDT1] T0
INNER JOIN [dbo].[OJDT] T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN [dbo].[OCRD] T2 ON T0.[ShortName] = T2.[CardCode]
where T0.[TransType] = 46 and T0.[BalDueDeb] < 0
UNION ALL
SELECT 'LC30' as Tipo
, T0.[TransId] as 'ID'
, CAST( T0.[TransId] as varchar) as 'Documento'
, '1 de 1' as 'Parcela'
, T0.[TaxDate] as 'Dt.Emissão'
, T0.[RefDate] as 'Dt.Entrada'
, T0.[DueDate] as 'Dt.Vencimento'
, T2.[CardCode] as 'Codigo PN'
, T2.[CardName] as 'Nome PN'
, T0.[TransId]
, T0.[Credit] as 'TotalTitulo'
, T0.[BalDueCred] as 'TotalSaldo'
, T0.[LineMemo] as 'Observação'
, T0.[ObjType]
, 0 as BaseRef
FROM [dbo].[JDT1] T0
INNER JOIN [dbo].[OJDT] T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN [dbo].[OCRD] T2 ON T0.[ShortName] = T2.[CardCode]
where T0.[TransType] = 30 and T0.[BalDueCred] <> 0

UNION ALL
SELECT 'LC30' as Tipo
, T0.[TransId] as 'ID'
, CAST( T0.[TransId] as varchar) as 'Documento'
, '1 de 1' as 'Parcela'
, T0.[TaxDate] as 'Dt.Emissão'
, T0.[RefDate] as 'Dt.Entrada'
, T0.[DueDate] as 'Dt.Vencimento'
, T2.[CardCode] as 'Codigo PN'
, T2.[CardName] as 'Nome PN'
, T0.[TransId]
, T0.[Credit] as 'TotalTitulo'
, T0.[BalDueCred] as 'TotalSaldo'
, T0.[LineMemo] as 'Observação'
, T0.[ObjType]
, 0 as BaseRef
FROM [dbo].[JDT1] T0
INNER JOIN [dbo].[OJDT] T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN [dbo].[OCRD] T2 ON T0.[ShortName] = T2.[CardCode]
where T0.[TransType] = 30 and T0.[BalDueCred] <> 0
UNION ALL
SELECT
'DE' as 'Tipo'
, MAX(T0.[DocNum]) as 'ID'
,( ISNULL((SELECT TOP 1 T4.NfmName from ONFM T4 WHERE T4.AbsEntry = T0.Model),'') + ' / ' + t0.SeriesStr + ' / ' + CAST(T0.SERIAL AS VARCHAR) ) as 'Documento'
, (CAST( MAX(T1.[InstlmntID]) as varchar) + ' de ' +
(SELECT CAST(MAX(T3.[InstlmntID]) AS VARCHAR) FROM [dbo].[PCH6] T3 WHERE T3.[DocEntry] = T0.[DocEntry]) ) as Parcela
, MAX(T0.[TaxDate]) as 'Dt.Emissão'
, MAX(T0.[DocDate]) as 'Dt.Entrada'
, MAX(T1.[DueDate]) as 'Dt.Vencimento'
, MAX(T0.[CardCode]) as 'Codigo PN'
, MAX(T0.[CardName]) as 'Nome PN'
, MAX(T0.[TransId]) as TransId
, (MAX(T1.[InsTotal]) * -1) as 'TotalTitulo'
, ( ( MAX(T1.[InsTotal]) - MAX(T1.[PaidToDate]) ) * -1) as 'TotalSaldo'
, MAX(T0.[Comments]) as 'Observação'
, T0.[ObjType]
, (SELECT MAX(T3.BaseRef) FROM RPC1 T3 WHERE T3.DocEntry = T0.DocEntry) as BaseRef
FROM [dbo].[ORPC] T0
INNER JOIN [dbo].[RPC6] T1 ON T1.[DocEntry] = T0.[DocEntry]
LEFT OUTER JOIN [dbo].[RPC5] T2 ON T0.[DocEntry] = T2.[AbsEntry]
WHERE T1.[TotalBlck] <> T1.[InsTotal] AND
(T1.[InsTotal] - T1.[PaidToDate]) <> 0
GROUP BY T0.[DocEntry], T1.[InstlmntID], T0.[ObjType], T0.Model, T0.SeriesStr, T0.Serial

Contas pagas (este é legal, sai os números dos cheques recebidos separados por virgula, comando XML PATH)


select T0.DocNum as 'NumBaixa', T0.DocEntry as 'NumNR', T0.SumApplied as 'ValorBaixa', T1.DocDate as 'DataBaixa',
CASE
WHEN T0.InvType = 18 THEN (SELECT MAX(T3.BaseRef) FROM PCH1 T3 WHERE T3.DocEntry = T0.DocEntry)
WHEN T0.InvType = 204 THEN (SELECT MAX(T3.BaseRef) FROM DPO1 T3 WHERE T3.DocEntry = T0.DocEntry)
END as 'BaseRef',
CASE
WHEN T0.InvType = 18 THEN (SELECT TOP 1 ( ISNULL((SELECT TOP 1 T4.NfmName from ONFM T4 WHERE T4.AbsEntry = T3.Model),'') + ' / ' + ISNULL(T3.SeriesStr,'') + ' / ' + CAST(T3.Serial AS VARCHAR) )
FROM OPCH T3 WHERE T3.DocNum = T0.DocEntry )
WHEN T0.InvType = 204 THEN 'ADT/' + CAST(T0.DocNum AS VARCHAR)
WHEN T0.InvType = 46 THEN 'LC/' + CAST(T0.DocNum AS VARCHAR)
WHEN T0.InvType = 30 THEN 'LC/' + CAST(T0.DocNum AS VARCHAR) + ' / ' + (SELECT TOP 1 T3.[Memo] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
END as 'Documento',
CASE
WHEN T0.InvType = 18 THEN
(CAST( T0.[DocLine] + 1 as varchar) + ' de ' +
(SELECT CAST(MAX(T3.[InstlmntID]) AS VARCHAR) FROM [dbo].[PCH6] T3 WHERE T3.[DocEntry] = T0.[DocEntry]) )
WHEN T0.InvType = 204 THEN
(CAST( T0.[DocLine] + 1 as varchar) + ' de ' +
(SELECT CAST(MAX(T3.[InstlmntID]) AS VARCHAR) FROM [dbo].[DPO6] T3 WHERE T3.[DocEntry] = T0.[DocEntry]) )
WHEN T0.InvType = 46 THEN '1 de 1'
WHEN T0.InvType = 30 THEN '1 de 1'
END as 'Parcela',
CASE
WHEN T0.InvType = 18 THEN (SELECT TOP 1 T3.[TaxDate] FROM OPCH T3 WHERE T3.DocNum = T0.DocEntry)
WHEN T0.InvType = 204 THEN (SELECT TOP 1 T3.[TaxDate] FROM ODPO T3 WHERE T3.DocNum = T0.DocEntry)
WHEN T0.InvType = 46 THEN (SELECT TOP 1 T3.[TaxDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
WHEN T0.InvType = 30 THEN (SELECT TOP 1 T3.[TaxDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
END as 'DataEmissao',

CASE
WHEN T0.InvType = 18 THEN (SELECT TOP 1 T3.[DocDate] FROM OPCH T3 WHERE T3.DocNum = T0.DocEntry)
WHEN T0.InvType = 204 THEN (SELECT TOP 1 T3.[DocDate] FROM ODPO T3 WHERE T3.DocNum = T0.DocEntry)
WHEN T0.InvType = 46 THEN (SELECT TOP 1 T3.[RefDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
WHEN T0.InvType = 30 THEN (SELECT TOP 1 T3.[RefDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
END as 'DataEntrada',
CASE
WHEN T0.InvType = 18 THEN (SELECT TOP 1 T3.[DueDate] FROM PCH6 T3 WHERE T3.DocEntry = T0.DocEntry and T3.InstlmntID = T0.DocLine + 1)
WHEN T0.InvType = 204 THEN (SELECT TOP 1 T3.[DueDate] FROM DPO6 T3 WHERE T3.DocEntry = T0.DocEntry and T3.InstlmntID = T0.DocLine + 1)
WHEN T0.InvType = 46 THEN (SELECT TOP 1 T3.[DueDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
WHEN T0.InvType = 30 THEN (SELECT TOP 1 T3.[DueDate] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
END as 'DataVencimento',

CASE
WHEN T0.InvType = 18 THEN (SELECT TOP 1 T3.[InsTotal] FROM PCH6 T3 WHERE T3.DocEntry = T0.DocEntry and T3.InstlmntID = T0.DocLine + 1)
WHEN T0.InvType = 204 THEN (SELECT TOP 1 T3.[InsTotal] FROM DPO6 T3 WHERE T3.DocEntry = T0.DocEntry and T3.InstlmntID = T0.DocLine + 1)
WHEN T0.InvType = 46 THEN (SELECT TOP 1 T3.[LocTotal] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
WHEN T0.InvType = 30 THEN (SELECT TOP 1 T3.[LocTotal] FROM OJDT T3 WHERE T3.Number = T0.DocEntry)
END as 'ValorDocumento',

(ISNULL( (SELECT TOP 1 T3.AcctName from OACT T3 WHERE T3.AcctCode = T1.TrsfrAcct and T1.TrsfrSum <> 0 ) + ', ', '') +
ISNULL( (SELECT TOP 1 T3.AcctName from OACT T3 WHERE T3.AcctCode = T1.CashAcct and T1.CashSum <> 0) + ', ', '') +
ISNULL( (SELECT TOP 1 T4.AcctName from VPM1 T3 INNER JOIN OACT T4 ON (T4.AcctCode = T3.CheckAct) WHERE T3.DocNum = T1.DocNum and T1.CheckSum > 0 )+ ', ', '' ))
as 'TipoBaixa',
isnull((select max(T4.ExtrMatch) from JDT1 T4 where (T4.Transid = T1.Transid and T4.ExtrMatch <> 0) ),0) as 'Reconciliacao',
(ISNULL( (SELECT TOP 1 T3.Finanse from OACT T3 WHERE T3.AcctCode = T1.TrsfrAcct and T1.TrsfrSum <> 0 ) + ', ', '') +
ISNULL( (SELECT TOP 1 T3.Finanse from OACT T3 WHERE T3.AcctCode = T1.CashAcct and T1.CashSum <> 0) + ', ', '') +
ISNULL( (SELECT TOP 1 T4.Finanse from VPM1 T3 INNER JOIN OACT T4 ON (T4.AcctCode = T3.CheckAct) WHERE T3.DocNum = T1.DocNum and T1.CheckSum > 0 )+ ', ', '' ))
as 'ContaFinanceira',
T1.TrsfrAcct,
T1.CashAcct,
T1.CheckAcct,
T1.CardCode,
T1.CardName,
T0.InvType,
Cheques = (Select CAST(T11.CheckNum as varchar(10)) + ', ' FROM VPM1 T11 WHERE T11.DocNum = T0.DocNum for XML PATH(''))

FROM VPM2 T0
INNER JOIN OVPM T1 ON T1.DocEntry = T0.DocNum
WHERE T1.Canceled = 'N'

Se for usar as consultas, por favor identifique a origem.

Abraço a todos

Fabio.

SAP 8.82 PL11

24 Comments
Labels in this area