Skip to Content

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

To report this post you need to login first.

16 Comments

You must be Logged on to comment or reply to a post.

  1. Ronivaldo Amaro

    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

    (0) 
    1. Fabio Bilicki 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.

      (0) 
      1. Ronivaldo Amaro

        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

        (0) 
        1. Fabio Bilicki 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.

          (0) 
            1. Fabio Bilicki 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.

              (0) 
      2. 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

        (0) 
  2. Ronivaldo Amaro

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

    (0) 
  3. Péricles Andrade

    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.

    (0) 
    1. Fabio Bilicki 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

      (0) 
  4. 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.

    (0) 

Leave a Reply