SAP Business One: query de contas a receber vencidas por faixa de atraso (aging) em SQL Server

Quem dá suporte a SAP Business One cedo ou tarde ouve do financeiro: "me passa quanto está vencido por cliente, separado por faixa de atraso". Dá para resolver com uma query só, direto nas tabelas de documentos de nota de saída.

As tabelas que entram

• OINV: cabeçalho das notas fiscais de saída (A/R Invoice).
• Campos usados: CardCode, CardName, DocNum, DocDate, DocDueDate, DocTotal, PaidToDate, DocStatus e CANCELED.

O saldo em aberto de cada nota é DocTotal - PaidToDate. Nota com DocStatus = 'O' está aberta, e CANCELED = 'N' tira as canceladas.

A query

SELECT
    T0.CardCode,
    T0.CardName,
    SUM(CASE WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 1 AND 30
             THEN T0.DocTotal - T0.PaidToDate ELSE 0 END) AS [1-30 dias],
    SUM(CASE WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 31 AND 60
             THEN T0.DocTotal - T0.PaidToDate ELSE 0 END) AS [31-60 dias],
    SUM(CASE WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 61 AND 90
             THEN T0.DocTotal - T0.PaidToDate ELSE 0 END) AS [61-90 dias],
    SUM(CASE WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) > 90
             THEN T0.DocTotal - T0.PaidToDate ELSE 0 END) AS [90+ dias],
    SUM(T0.DocTotal - T0.PaidToDate) AS [Total vencido]
FROM OINV T0
WHERE T0.DocStatus = 'O'
  AND T0.CANCELED = 'N'
  AND T0.DocDueDate < CAST(GETDATE() AS DATE)
GROUP BY T0.CardCode, T0.CardName
ORDER BY [Total vencido] DESC;

Pontos de atenção

1. Moeda: DocTotal e PaidToDate estão na moeda local. Se a empresa fatura em moeda estrangeira e quer o valor na moeda do documento, use DocTotalFC e PaidFC.
2. Devoluções: notas de crédito ficam na ORIN e não entram aqui. Se o financeiro abate devolução do aging, some a ORIN com sinal negativo num UNION ALL.
3. Parcelas: com condição de pagamento parcelada, o vencimento real de cada parcela está na INV6 (DueDate, InsTotal, PaidToDate). Para aging por parcela, troque a OINV pela INV6 com join pelo DocEntry.
4. HANA: no SAP HANA, troque DATEDIFF(DAY, a, b) por DAYS_BETWEEN(a, b) e GETDATE() por CURRENT_DATE.

Para salvar no B1

Salve no Gerenciador de Consultas (Query Manager) e, se quiser, use a consulta como base de um alerta semanal para o financeiro.

---

Produto meu: esta query faz parte de um mini pack de R$9 com 5 consultas de financeiro para o Query Manager (faturamento do período, faturamento por cliente/mês, faturamento líquido, contas vencidas e este aging), em SQL Server e HANA: https://pay.kiwify.com.br/KPkxSbq