Skip to Content

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

17 Comments
You must be Logged on to comment or reply to a post.
  • 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

    • 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.

      • 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

        • 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.

          • 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.

      • 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

  • 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ç,

  • 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.

    • 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

  • 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.

  • 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