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ı
Editör ekibi
7 dk okuma
İçindekiler
"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; bakiyeCUSTTRANSya daCUSTTRANSOPENtoplamıdır. - Şirket süzgecini unutmak. Aynı müşteri numarası başka şirkette de bulunabilir. Sorgu hata vermez, tutar yanlış çıkar.
DIRPARTYTABLE'aDATAAREAIDyazmak. 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-01boş 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.
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.







