ERP AsistanıERP Asistanı Dal Net
AX SQL

Axapta Cari Bakiye ve Yaşlandırma SQL: Dynamics AX CUSTTRANS Rehberi

Axapta (Dynamics AX 2012) cari bakiye, açık kalem, 30/60/90 gün yaşlandırma ve ekstre hangi SQL ile alınır? CUSTTRANS, CUSTTRANSOPEN ve şirket süzgeciyle adım adım rehber.

Erp Asistanı ERP Asistanı Editör ekibi 7 dk okuma
Axapta Cari Bakiye ve Yaşlandırma SQL: Dynamics AX CUSTTRANS Rehberi

"Hangi müşterinin ne kadar borcu var, bu borcun ne kadarı vadesini geçti?" Finans ekiplerinin her hafta sorduğu bu soru, Axapta adıyla da bilinen Microsoft Dynamics AX 2012'de CUSTTABLE tablosundan cevaplanmaz: müşteri kartında bakiye kolonu yoktur. Bakiye, cari hareketlerden hesaplanır. Bu rehberde cari bakiye, açık kalem, yaşlandırma ve ekstre sorgularını adım adım kuruyoruz.

Bütün sorgular salt okunurdur ve şirket süzgeci taşır. N'trd' yerine kendi şirket kodunuzu, 5637144576 yerine kendi bölüm (partition) değerinizi yazın; şirket mantığının ayrıntısını DATAAREAID ve şirket bazlı raporlama yazısında anlatıyoruz.

Sorgular nerede denendi? Microsoft'un yayımladığı AX 2012 tablo referansından kurduğumuz test veritabanında çalıştırıldı. Veritabanındaki trd şirketi bizim ürettiğimiz kurgu veri taşır, gerçek bir firmaya ait değildir. Gerçek bir AX kurulumunda firmanıza özel alanlar ve parametreler sonucu değiştirebilir; ilk çalıştırmada rakamları AX ekranlarıyla karşılaştırın.

Cari veriler hangi tabloda?

Tablo Ne tutar Dikkat edilecek nokta
CUSTTABLE Müşteri kartı (ACCOUNTNUM) Şirket bazlıdır; ad ve bakiye taşımaz
DIRPARTYTABLE Taraf kaydı, müşteri adı (NAME) Şirketsizdir; CUSTTABLE.PARTY = DIRPARTYTABLE.RECID ile bağlanır
CUSTTRANS Bütün cari hareketler (AMOUNTMST, SETTLEAMOUNTMST) Fatura pozitif, tahsilat negatif işaretlidir
CUSTTRANSOPEN Kapanmamış kalemler (AMOUNTMST, DUEDATE) Tamamen kapanan kalemin satırı silinir
CUSTSETTLEMENT Hangi hareketin hangisiyle kapandığı TRANSRECID kapanan, OFFSETRECID karşı hareket

AMOUNTMST şirketin muhasebe para birimindeki tutardır. Şirketlerin para birimi farklı olabileceği için bu kolon şirketler arasında toplanmaz; işlem para birimindeki tutar AMOUNTCUR kolonundadır.

1. Müşteri bakiye listesi

-- Pozitif bakiye: müşteri borçlu
SELECT t.ACCOUNTNUM      AS CariKod,
       p.NAME            AS CariAd,
       SUM(t.AMOUNTMST)  AS Bakiye
FROM CUSTTRANS t
JOIN CUSTTABLE c ON c.ACCOUNTNUM = t.ACCOUNTNUM AND c.DATAAREAID = t.DATAAREAID AND c.PARTITION = t.PARTITION
JOIN DIRPARTYTABLE p ON p.RECID = c.PARTY AND p.PARTITION = c.PARTITION
WHERE t.DATAAREAID = N'trd' AND t.PARTITION = 5637144576
GROUP BY t.ACCOUNTNUM, p.NAME
HAVING SUM(t.AMOUNTMST) <> 0
ORDER BY Bakiye DESC;

Bu raporu SQL yazmadan, Türkçe sorarak alın: Dynamics AX raporlama

DIRPARTYTABLE birleştirmesinde DATAAREAID yazılmaz, çünkü bu tabloda o kolon yoktur; yazarsanız sorgu Invalid column name hatasıyla durur.

2. Bakiyeyi açık kalemlerle karşılaştırın

Aynı bakiyeye iki yoldan varılır: bütün hareketlerin toplamı ve açık kalemlerin toplamı. İkisinin tutması, kapama kayıtlarının sağlıklı olduğunu gösterir. Bir raporu devreye almadan önce bu karşılaştırmayı bir kez çalıştırmanızı öneririz.

WITH Hareket AS (
    SELECT t.ACCOUNTNUM, SUM(t.AMOUNTMST) AS Bakiye
    FROM CUSTTRANS t
    WHERE t.DATAAREAID = N'trd' AND t.PARTITION = 5637144576
    GROUP BY t.ACCOUNTNUM
), Acik AS (
    SELECT o.ACCOUNTNUM, SUM(o.AMOUNTMST) AS AcikTutar
    FROM CUSTTRANSOPEN o
    WHERE o.DATAAREAID = N'trd' AND o.PARTITION = 5637144576
    GROUP BY o.ACCOUNTNUM
)
SELECT h.ACCOUNTNUM                                AS CariKod,
       h.Bakiye,
       ISNULL(a.AcikTutar, 0)                      AS AcikKalemToplami,
       h.Bakiye - ISNULL(a.AcikTutar, 0)           AS Fark
FROM Hareket h
LEFT JOIN Acik a ON a.ACCOUNTNUM = h.ACCOUNTNUM
WHERE h.Bakiye <> ISNULL(a.AcikTutar, 0)
ORDER BY ABS(h.Bakiye - ISNULL(a.AcikTutar, 0)) DESC;

Sorgu fark olan carileri listeler. Liste boşsa iki kaynak birbirini tutuyor demektir.

3. Yaşlandırma raporu: 30, 60, 90 gün

Yaşlandırma açık kalemlerden, vade tarihine göre kurulur. AX'te boş tarih 1900-01-01 değeridir; vadesi boş kalemde hareket tarihini kullanmak gerekir.

SELECT o.ACCOUNTNUM AS CariKod,
       p.NAME       AS CariAd,
       SUM(CASE WHEN g.Gun <= 0 THEN o.AMOUNTMST ELSE 0 END)              AS VadesiGelmemis,
       SUM(CASE WHEN g.Gun BETWEEN 1 AND 30 THEN o.AMOUNTMST ELSE 0 END)  AS Gun1_30,
       SUM(CASE WHEN g.Gun BETWEEN 31 AND 60 THEN o.AMOUNTMST ELSE 0 END) AS Gun31_60,
       SUM(CASE WHEN g.Gun BETWEEN 61 AND 90 THEN o.AMOUNTMST ELSE 0 END) AS Gun61_90,
       SUM(CASE WHEN g.Gun > 90 THEN o.AMOUNTMST ELSE 0 END)              AS Gun90Ustu,
       SUM(o.AMOUNTMST)                                                   AS Toplam
FROM CUSTTRANSOPEN o
JOIN CUSTTABLE c ON c.ACCOUNTNUM = o.ACCOUNTNUM AND c.DATAAREAID = o.DATAAREAID AND c.PARTITION = o.PARTITION
JOIN DIRPARTYTABLE p ON p.RECID = c.PARTY AND p.PARTITION = c.PARTITION
CROSS APPLY (
    SELECT DATEDIFF(DAY, CASE WHEN o.DUEDATE = '1900-01-01' THEN o.TRANSDATE ELSE o.DUEDATE END, GETDATE()) AS Gun
) g
WHERE o.DATAAREAID = N'trd' AND o.PARTITION = 5637144576
GROUP BY o.ACCOUNTNUM, p.NAME
ORDER BY Toplam DESC;

Negatif tutar, henüz bir faturayla kapatılmamış tahsilat ya da avanstır. Raporda ayrı görmek isterseniz o.AMOUNTMST < 0 koşuluyla ayrı bir sütun ekleyin.

4. Vadesi geçmiş kalemler, belge belge

Tahsilat ekibinin arayacağı liste, toplam değil belge düzeyindedir. Açık kalem satırı belge numarasını taşımaz; CUSTTRANSOPEN.REFRECID ile CUSTTRANS satırına gidilir.

SELECT t.ACCOUNTNUM  AS CariKod,
       t.VOUCHER     AS FisNo,
       t.INVOICE     AS FaturaNo,
       t.TRANSDATE   AS BelgeTarihi,
       o.DUEDATE     AS VadeTarihi,
       DATEDIFF(DAY, o.DUEDATE, GETDATE()) AS GecikenGun,
       o.AMOUNTMST   AS AcikTutar
FROM CUSTTRANSOPEN o
JOIN CUSTTRANS t ON t.RECID = o.REFRECID AND t.PARTITION = o.PARTITION
WHERE o.DATAAREAID = N'trd' AND o.PARTITION = 5637144576
  AND o.AMOUNTMST > 0
  AND o.DUEDATE <> '1900-01-01'
  AND o.DUEDATE < CAST(GETDATE() AS date)
ORDER BY GecikenGun DESC, o.AMOUNTMST DESC;

5. Yürüyen bakiyeli cari ekstre

Müşteriyle mutabakat ya da itiraz görüşmesinde her hareketin ardından oluşan bakiye gerekir.

SELECT t.TRANSDATE  AS Tarih,
       t.VOUCHER    AS FisNo,
       t.INVOICE    AS FaturaNo,
       t.TXT        AS Aciklama,
       CASE WHEN t.AMOUNTMST > 0 THEN t.AMOUNTMST ELSE 0 END  AS Borc,
       CASE WHEN t.AMOUNTMST < 0 THEN -t.AMOUNTMST ELSE 0 END AS Alacak,
       SUM(t.AMOUNTMST) OVER (ORDER BY t.TRANSDATE, t.RECID ROWS UNBOUNDED PRECEDING) AS YuruyenBakiye
FROM CUSTTRANS t
WHERE t.DATAAREAID = N'trd' AND t.PARTITION = 5637144576
  AND t.ACCOUNTNUM = N'M0001'
ORDER BY t.TRANSDATE, t.RECID;

6. Tahsilat gecikmesi: faturalar vadesinden kaç gün sonra kapanıyor?

Kapanan faturanın kapanış tarihi CUSTSETTLEMENT tablosundadır. Bu tablodan müşterinin ödeme alışkanlığı ölçülür. Bir fatura birden fazla tahsilatla kapanabileceği için tutar ağırlıklı ortalama, düz ortalamadan daha doğru sonuç verir.

SELECT t.ACCOUNTNUM AS CariKod,
       COUNT(DISTINCT t.RECID) AS KapananFatura,
       SUM(s.SETTLEAMOUNTMST)  AS KapananTutar,
       SUM(DATEDIFF(DAY, t.DUEDATE, s.TRANSDATE) * s.SETTLEAMOUNTMST) / NULLIF(SUM(s.SETTLEAMOUNTMST), 0) AS OrtalamaGecikmeGun
FROM CUSTSETTLEMENT s
JOIN CUSTTRANS t ON t.RECID = s.TRANSRECID AND t.PARTITION = s.PARTITION
WHERE s.DATAAREAID = N'trd' AND s.PARTITION = 5637144576
  AND t.AMOUNTMST > 0
  AND t.DUEDATE <> '1900-01-01'
GROUP BY t.ACCOUNTNUM
ORDER BY OrtalamaGecikmeGun DESC;

Sık yapılan hatalar

  • Müşteri kartında bakiye aramak. CUSTTABLE'da bakiye kolonu yoktur; bakiye CUSTTRANS ya da CUSTTRANSOPEN toplamıdır.
  • Şirket süzgecini unutmak. Aynı müşteri numarası başka şirkette de bulunabilir. Sorgu hata vermez, tutar yanlış çıkar.
  • DIRPARTYTABLE'a DATAAREAID yazmak. Bu tablo şirketsizdir, sorgu derlenmez.
  • AMOUNTMST'yi şirketler arasında toplamak. Her şirketin muhasebe para birimi farklı olabilir.
  • Boş vadeyi gerçek tarih saymak. 1900-01-01 boş tarihtir; işlenmezse kalem çok gecikmiş görünür.

Sık sorulanlar

Axapta'da cari bakiye hangi tabloda? Hazır bir bakiye kolonu yoktur. Bakiye CUSTTRANS.AMOUNTMST toplamıyla ya da açık kalemler için CUSTTRANSOPEN.AMOUNTMST toplamıyla hesaplanır.

CUSTTRANS ile CUSTTRANSOPEN farkı nedir? CUSTTRANS bütün hareketleri kalıcı olarak tutar, kapanan kısmı SETTLEAMOUNTMST kolonunda gösterir. CUSTTRANSOPEN yalnız kapanmamış kısmı tutar; kalem tamamen kapanınca satırı silinir. Yaşlandırma için ikincisi, ekstre için birincisi kullanılır.

Müşteri adı neden CUSTTABLE'da yok? AX 2012'de ad, küresel adres defterindeki taraf kaydında durur. CUSTTABLE.PARTY alanı DIRPARTYTABLE.RECID kolonuna bağlanır, ad DIRPARTYTABLE.NAME kolonundan okunur.

Sonuçlarım AX'in kendi yaşlandırma raporuyla neden tutmuyor? AX'teki rapor kendi parametreleriyle çalışır: yaşlandırma dönemi tanımı, baz alınan tarih ve seçili müşteri grupları farklı olabilir. Karşılaştırmayı aynı tarih ve aynı dönem aralığıyla yapın.


Her ay aynı yaşlandırma raporunu elle uyarlamak yerine ERP Asistanı'na "vadesi 60 günü geçen müşteri borçlarını getir" yazın; sistem seçtiğiniz şirkete ve Dynamics AX 2012 tablolarına uygun SQL'i üretir, sorgu çalışmadan şirket süzgecini denetler.

Ücretsiz üye olun, Dynamics AX sorgunuzu Türkçe yazın →

Bu yazıyı paylaş
Dynamics AX raporlama

Bu raporu SQL yazmadan, Türkçe sorarak alın

Türkçe sorunuzu yazın, şemanıza uygun çalışan SQL'i ya da hazır uygulamanızı saniyeler içinde alın.