Skip to Content
Author's profile photo Fabio Bilicki dos Santos

Relatórios financeiros (A receber, recebidos, a pagar e pagos).

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

Assigned Tags

      19 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Oi, Fábio, tudo bem?

      Que bom que achei esse post seu, nossa me ajudou muito!

      Mas eu preciso de consulta de contas à receber Vencidas, quais seriam os ajustes nas consultas de Contas a Receber?

      Abç,

      Roni

      Author's profile photo Fabio Bilicki
      Fabio Bilicki
      Blog Post Author

      Bom dia Roni,

      você pode usar o primeiro SQL da lista, Contas à Receber, e colocar um filtro no crystal reports como fiz.

      "

      (

        not HasValue( {?DataEmissao} ) or

         {Comando.Dt.Entrada} in {?DataEmissao}

      )

      and

      (

        not HasValue( {?DataVence} ) or

        {Comando.Dt.Vencimento} in {?DataVence}

      )

      "


      Depois, é só filtrar a data de vencimento passada, por exemplo: 01/01/2012 até ontem 11/12/2013 , assim você terá as contas vencidas.

      É assim que faço pra filtrar o que está vencido.

      Eu poderia melhorar o SQL e filtrar nele (usando where e between), seria bem melhor e mais rapido, mas isso será a nova versão do meu relatório.

      []'s

      Fabio.

      Author's profile photo Former Member
      Former Member

      Fábio,

      Muito obrigado por ter respondido, mas então nessa consulta não traz o que já foi pago?

      Eu preciso somente do que está vencido e não foi pago ainda, se for ta resolvido.

      Outra coisa, na minha base não tem a tabela ONFM, tem esta informação em outra lugar?

      Abç,

      Roni

      Author's profile photo Fabio Bilicki
      Fabio Bilicki
      Blog Post Author

      Boa noite Roni, cada SQL trás uma informação, aqui na empresa onde trabalho são duas pessoas, uma toma conta de contas a pagar e pagas, e outra de receber e recebidas, por isso que fizemos 4 consultas separadas, o filtro serve para todas as consultas.

      A tabela ONFM só traz o modelo do documento (NFe, Modelo1, NFS-e, Outras, etc), se você tiver essa informação em outra tabela, é só usar, isso é enfeite, se você não precisa, é só remover.

      Qual é a versão do seu SAP?

      Abraço

      Fabio.

      Author's profile photo Former Member
      Former Member

      Fábio,

      A versão é o SAP Business One 2007 B 8.6

      Abç,

      Roni

      Author's profile photo Fabio Bilicki
      Fabio Bilicki
      Blog Post Author

      Roni, bom dia,

      troca a tabela ONFM por algo fixo, por exemplo.

      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  )

      por

      WHEN T0.InvType = 18  THEN ('NF') + ' / ' + ISNULL(T3.SeriesStr,'') + ' / ' + CAST(T3.Serial AS VARCHAR) )  

      FROM OPCH T3 WHERE T3.DocNum = T0.DocEntry  )

      ou, ainda melhor

      WHEN T0.InvType = 18  THEN 'NF/ ' + ISNULL(T3.SeriesStr,'') + ' / ' + CAST(T3.Serial AS VARCHAR) )  

      FROM OPCH T3 WHERE T3.DocNum = T0.DocEntry  )

      Abraço

      Fabio.

      Author's profile photo Luis Gustavo Farah
      Luis Gustavo Farah

      Prezado Flavio,

      Ótimo trabalho, obrigado por compratilhar

      Sou iniciante no SAP e testei suas consultas e tenho duas questoes:

      1 - Como faço para incluir um periodo para a consulta

      2 - Na consulta de contas recebidas o SAP está dando um erro, parece que minha tabela OCDR não tem o Campo: U_UpCodAnt, 

      Estou usando o SAP 9.0 PL 9.0

      O erro completo na hora da execuçao da consulta é:

      1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'U_UpCodAnt'.

      2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement '' (WLS1) (s) could not be prepared.

      Abraço

      Author's profile photo Former Member
      Former Member

      Blz, valeu demais!.. Fábio, trabalho em uma empresa de TI, temos um cliente com SAP B1, se pudesse me ajudar mantendo contato, se não te atrapalhar.. meu skype é roni.amaro

      Abç,

      Author's profile photo Former Member
      Former Member

      Boa tarde Fábio,

      Estou trabalhando dando uma olhada no seu primeiro comando Contas à receber.
      Mas me surgiu uma dúvida. Nos títulos que já foram gerados boleto, eu precisava que o campo 'Documento' ficasse no mesmo formato que no primeiro select, onde é informado o número da NFe e a série. Consegue me ajudar?

      Abraço.

      Author's profile photo Fabio Bilicki
      Fabio Bilicki
      Blog Post Author

      Bom dia Péricles,

      O problema de boleto é que podemos ter várias notas em um único boleto, por esse motivo, podemos fazer usando dois métodos (consegui fazer rapidamente hoje usando somente documentos tipo 13 - Nota de saída, o certo é fazer para todos os documentos contemplados pelo boleto),

      Para ler a nota, devemos fazer a leitura nas seguintes tabelas.

      Boleto (OBOE) --> Cabeçalho da Baixa (ORCT) --> Detalhes da Baixa (RCT2) --> Nota Fiscal (OINV).

      1o Criando um subrelatório dentro do Crystal.

      Esse serial interessante, já que podemos colocar mais informações referente a nota fiscal no relatório, para isso é só ler a partir do boleto, as notas que o compõe.

      select
          ('BOL/'+ CAST( T0.[BoeNum] as varchar)) as 'Documento',
          ( ISNULL((SELECT TOP 1 T4.NfmName from ONFM T4 WHERE T4.AbsEntry = T2.Model),'') + ' / ' + T2.SeriesStr + ' / ' + CAST(T2.SERIAL AS VARCHAR) ) as 'NotaFiscal'
          from ORCT T0
          INNER JOIN RCT2 T1 ON T1.DocNum = T0.DocEntry
          INNER JOIN OINV T2 ON T2.DocEntry = T1.DocEntry and t2.ObjType = T1.InvType
          WHERE T0.BoeAbs is not null

      aqui é só melhorar, colocar outros campos e passar o número do boleto como filtro para o subrelatório.

      2o Somente número da nota.

      Trocar  o 'Boleto' as 'Observação' para

      , '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('')
          )

      Abraço,

      Fabio

      Author's profile photo Former Member
      Former Member

      Legal Fabio!

      Author's profile photo Augustus Pinto
      Augustus Pinto

      Ótimo.

      Author's profile photo Maicon Mendes Macedo
      Maicon Mendes Macedo

      Fantástico.

      Muito bom.

      A melhor forma de gerar relatórios no SAP B1 é conhecer as telas, saber as informações que você precisa e debruçar-se no Crystal Reports, não tem melhor forma.

      Author's profile photo Former Member
      Former Member

      Bom dia..

      Muito bom essas consultas..

      Só fiquei com uma dúvida. como faria para filtrar no where pela data que foi pago ?

      Author's profile photo Former Member
      Former Member

      Bom dia..

      A consulta me ajudou muito..

      Author's profile photo Former Member
      Former Member

      Bom dia,

       

      Top a consulta, me ajudou  muito !

       

      Abraços

      Author's profile photo Former Member
      Former Member

      Obrigado pela ajuda Fabio,

       

      Minha dúvida é se vai rodar dentro do SAP 9.2 PL 10.

      Ainda nao consegui rodar essa consulta, mas gostaria de saber se ja está trazendo o valor dos juros quando houver.

       

      Grato

      Author's profile photo Renatta Oliveira
      Renatta Oliveira

      Bom dia,

       

      Já possui uma versão para HANA?

       

      Att.

      Author's profile photo Fábio Bilicki
      Fábio Bilicki
      Blog Post Author

      Boa noite, ainda não, só vou chegar no HANA em 2022.