SAP B1 Alış Raporu SQL: OPCH ile Tedarikçi Borçları
SAP Business One'da OPCH ve PCH1 ile tedarikçi bazında net alım, ay bazında alış ve açık borç raporu SQL sorguları. İade ve tediye eşleşmesi dahil.
ERP Asistanı
Editör ekibi
10 dk okuma
İçindekiler
Satış tarafında OINV ne kadar standartsa, alış tarafında da OPCH o kadar standarttır; ama alış raporlarında hata payı daha yüksektir. Sebebi basit: alış iade faturası (ORPC) çoğu raporda unutulur, mal girişi (OPDN) alış faturasıyla birlikte toplanır ve ödenmemiş borç DocTotal üzerinden okunur. Bu yazıda tedarikçi bazında net alımı, ay bazında alış eğrisini, vadesi gelmiş açık borcu ve tediye eşleşmesini tek tek SQL ile kuruyoruz. Sorguların tümü SQL Server (T-SQL) sözdizimindedir ve yalnız okuma yapar; kopyalayıp kendi şirket veritabanınızda çalıştırabilirsiniz.
Alış tarafının tablo haritası
Alışta üç ayrı belge ailesi var ve her biri farklı bir soruya cevap verir. Karıştırıldığında rakam şişer:
| Soru | Tablo | Kolonlar | Not |
|---|---|---|---|
| Tedarikçiden ne aldım? | OPCH (PCH1) |
DocTotal, VatSum, DocStatus |
KDV dahil toplam; net için ORPC düşülür |
| Ne kadarını ödedim? | OVPM (VPM2) |
DocTotal, Canceled, SumApplied |
İptal kolonu Canceled |
| Ne kadar borcum var? | OPCH |
DocTotal - PaidToDate, DocStatus |
Kalan borç tek tek faturada |
| Mal ne zaman girdi? | OPDN (PDN1) |
- | Alışta stok hareketi burada; rapora EKLENMEZ |
| Tedarikçi kim? | OCRD |
CardType = 'S' |
C müşteri, S tedarikçi, L aday |
Tablo adlarının genel mantığını bilmiyorsanız SAP Business One tablo yapısı rehberi iyi bir başlangıçtır.
Net alış = OPCH eksi ORPC
Alış iade faturası (ORPC, ObjType 19) tedarikçiye yapılan iadeyi temsil eder ve alıştan düşülmelidir. UNION ALL kalıbında iadeyi negatif tutarla ekliyoruz; böylece GROUP BY altında iadenin hiç olmadığı tedarikçi de listede kalıyor:
SELECT x.CardCode AS TedarikciKod,
MAX(x.CardName) AS Tedarikci,
SUM(x.Malzeme) AS AlimKdvHaric,
SUM(x.Kdv) AS Kdv,
SUM(x.Toplam) AS AlimKdvDahil
FROM (
SELECT CardCode, CardName,
DocTotal - VatSum AS Malzeme, VatSum AS Kdv, DocTotal AS Toplam
FROM OPCH
WHERE CANCELED = 'N'
UNION ALL
SELECT CardCode, CardName,
-(DocTotal - VatSum), -VatSum, -DocTotal
FROM ORPC
WHERE CANCELED = 'N'
) x
GROUP BY x.CardCode
ORDER BY AlimKdvDahil DESC;
Bu raporu SQL yazmadan, Türkçe sorarak alın: SAP Business One raporlama
DocTotal KDV dahildir, KDV hariç tutar DocTotal - VatSum ile çıkar. CardName belgeye kopyalanmış addır; kartta unvan sonradan değiştiyse eski ad görünür.
Ay bazında alım
Alış eğrisini görmek için aynı UNION ALL kalıbını tarih kırılımıyla kullanın. DocDate deftere işlenme tarihidir; tedarikçinin fatura tarihi NumAtCard ile kağıt üzerinde, TaxDate ile belge tarihi olarak ayrıca durur:
SELECT YEAR(s.DocDate) AS Yil,
MONTH(s.DocDate) AS Ay,
SUM(s.Tutar) AS AlimKdvHaric
FROM (
SELECT DocDate, DocTotal - VatSum AS Tutar FROM OPCH WHERE CANCELED = 'N'
UNION ALL
SELECT DocDate, -(DocTotal - VatSum) FROM ORPC WHERE CANCELED = 'N'
) s
WHERE s.DocDate >= DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1)
GROUP BY YEAR(s.DocDate), MONTH(s.DocDate)
ORDER BY Yil, Ay;
Tedarikçi bazında ay kırılımı isteniyorsa GROUP BY x.CardCode, YEAR(s.DocDate), MONTH(s.DocDate) yeterlidir; ayrı bir tabloya ihtiyaç yoktur.
Açık borç: DocTotal eksi PaidToDate
Ödenmemiş borç, fatura toplamından o belgeye uygulanmış tediye tutarının çıkarılmasıyla bulunur. PaidToDate kısmi ödemelerde kalan borcu doğru verir. JPX benzeri ara tablolara gitmeye gerek yok:
SELECT c.CardCode AS TedarikciKod,
c.CardName AS Tedarikci,
c.LicTradNum AS VergiNo,
COUNT(*) AS AcikFaturaSayisi,
SUM(p.DocTotal) AS FaturaTutari,
SUM(p.PaidToDate) AS Odenen,
SUM(p.DocTotal - p.PaidToDate) AS KalanBorc
FROM OPCH p
JOIN OCRD c ON c.CardCode = p.CardCode AND c.CardType = 'S'
WHERE p.CANCELED = 'N'
AND p.DocStatus = 'O'
AND p.DocTotal - p.PaidToDate > 0
GROUP BY c.CardCode, c.CardName, c.LicTradNum
ORDER BY KalanBorc DESC;
DocStatus = 'O' açık, 'C' tamamen kapalı fatura demektir. Kısmi ödenmiş fatura hâlâ O durumundadır, bu yüzden ek olarak DocTotal - PaidToDate > 0 koşulunu koyuyoruz; aksi halde kuruş farkı yüzünden kapanmış görünen satırlar listeye düşer.
Vade kovaları: 0-30 / 31-60 / 61-90 / 90+
Borcu tedarikçiye göre değil vadesine göre görmek isteyen finans tarafı için DocDueDate üzerinden tek sorguda kova üretebilirsiniz:
SELECT c.CardCode AS TedarikciKod,
c.CardName AS Tedarikci,
SUM(CASE WHEN p.DocDueDate >= CAST(GETDATE() AS date)
THEN p.DocTotal - p.PaidToDate ELSE 0 END) AS VadesiGelmemis,
SUM(CASE WHEN DATEDIFF(DAY, p.DocDueDate, CAST(GETDATE() AS date)) BETWEEN 1 AND 30
THEN p.DocTotal - p.PaidToDate ELSE 0 END) AS Gun1_30,
SUM(CASE WHEN DATEDIFF(DAY, p.DocDueDate, CAST(GETDATE() AS date)) BETWEEN 31 AND 60
THEN p.DocTotal - p.PaidToDate ELSE 0 END) AS Gun31_60,
SUM(CASE WHEN DATEDIFF(DAY, p.DocDueDate, CAST(GETDATE() AS date)) BETWEEN 61 AND 90
THEN p.DocTotal - p.PaidToDate ELSE 0 END) AS Gun61_90,
SUM(CASE WHEN DATEDIFF(DAY, p.DocDueDate, CAST(GETDATE() AS date)) > 90
THEN p.DocTotal - p.PaidToDate ELSE 0 END) AS Gun90Ustu
FROM OPCH p
JOIN OCRD c ON c.CardCode = p.CardCode
WHERE p.CANCELED = 'N'
AND p.DocStatus = 'O'
AND p.DocTotal - p.PaidToDate > 0
GROUP BY c.CardCode, c.CardName
ORDER BY Gun90Ustu DESC, Gun61_90 DESC;
DocDueDate datetime kolondur, DATEDIFF ile güvenle kullanılır. Aynı yaşlandırmayı yevmiye (JDT1) üzerinden kurmak isterseniz yöntem SAP Business One cari bakiye ve yaşlandırma SQL yazısında anlatılıyor; alış tarafında belge bazlı kalan borç için OPCH.PaidToDate daha hızlıdır.
Tediye eşleşmesi: OVPM ve VPM2
Hangi faturanın hangi tediye ile kapandığını görmek için OVPM tablosunu VPM2 ile bağlarız. Dikkat: VPM2.DocNum tediye belgesinin DocEntry değerini taşır, VPM2.DocEntry ise kapatılan belgenin DocEntry değeridir. InvType = 18 alış faturasını işaret eder:
SELECT v.DocNum AS TediyeNo,
v.DocDate AS Tarih,
v.CardCode AS TedarikciKod,
v.CardName AS Tedarikci,
v.DocTotal AS TediyeTutari,
f.DocNum AS AlisFaturaNo,
f.DocDate AS FaturaTarihi,
p.SumApplied AS KapatilanTutar
FROM OVPM v
LEFT JOIN VPM2 p ON p.DocNum = v.DocEntry AND p.InvType = 18
LEFT JOIN OPCH f ON f.DocEntry = p.DocEntry
WHERE v.Canceled = 'N'
AND v.DocDate >= DATEADD(MONTH, -3, CAST(GETDATE() AS date))
ORDER BY v.DocDate DESC, v.DocNum;
LEFT JOIN bilinçli: peşin ödeme (avans) olarak girilen tediye hiçbir faturaya bağlanmaz ve AlisFaturaNo boş döner. InvType = 204 peşinat belgesini işaret eder; o satırları da görmek istiyorsanız InvType IN (18, 19, 204) yazın.
Tedarikçi bazında ödenen toplam
Tediye toplamını VPM2 ile birleştirip SUM almak satır çoğalması yüzünden tutarı şişirir. Toplamı doğrudan başlık tablosundan okuyun:
SELECT v.CardCode AS TedarikciKod,
MAX(v.CardName) AS Tedarikci,
COUNT(*) AS TediyeSayisi,
SUM(v.DocTotal) AS Odenen
FROM OVPM v
WHERE v.Canceled = 'N'
AND v.DocDate >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
GROUP BY v.CardCode
ORDER BY Odenen DESC;
Son alış tarihi: uyuyan tedarikçiyi yakala
Pasifleşen tedarikçiyi görmek için OCRD ile OPCH tekrar birleşir. LEFT JOIN sayesinde hiç alışı olmayan tedarikçi de listelenir ve SonAlisTarihi boş döner:
SELECT c.CardCode AS TedarikciKod,
c.CardName AS Tedarikci,
c.City AS Sehir,
MAX(p.DocDate) AS SonAlisTarihi,
DATEDIFF(DAY, MAX(p.DocDate), CAST(GETDATE() AS date)) AS GunOnce
FROM OCRD c
LEFT JOIN OPCH p ON p.CardCode = c.CardCode AND p.CANCELED = 'N'
WHERE c.CardType = 'S'
AND c.frozenFor = 'N'
GROUP BY c.CardCode, c.CardName, c.City
ORDER BY SonAlisTarihi DESC;
SonAlisTarihi boş olanlar hiç alış yapılmamış tedarikçilerdir; sıralamada en üste düşmemeleri için DESC yazın ya da WHERE MAX(...) IS NULL ile ayrı liste alın.
Kalem bazında alım: PCH1
Tedarikçi ya da ürün bazında alım kırılımı gerektiğinde PCH1 satırlarına inin. Satır toplamı LineTotal KDV hariçtir ve satır iskontosu düşülmüştür:
SELECT TOP 20
l.ItemCode AS StokKod,
MAX(l.Dscription) AS KalemAdi,
SUM(l.Quantity) AS ToplamMiktar,
SUM(l.LineTotal) AS AlimKdvHaric
FROM PCH1 l
JOIN OPCH f ON f.DocEntry = l.DocEntry
WHERE f.CANCELED = 'N'
AND f.DocDate >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
GROUP BY l.ItemCode
ORDER BY AlimKdvHaric DESC;
Birim alış fiyatını izlemek istiyorsanız AVG(l.Price) yerine belge bazında Price kolonuna bakın; ortalama, farklı birim ve iskonto oranlarını gizler. Sapma kontrolü için alış fiyat sapması yazısındaki kalıp daha işe yarar.
Sık yapılan hatalar
- Mal girişini (
OPDN) alışa eklemek: fatura mal girişinden kopyalandıysa tutar iki kez sayılır. Alış =OPCHeksiORPC. - İade faturasını (
ORPC) atlamak: net alım şişer, tedarikçi borcu olduğundan yüksek görünür. - Borcu
DocTotalile okumak: borçDocTotal - PaidToDateile hesaplanır;DocStatustek başına kısmi ödemeyi göstermez. - Tediye toplamını
VPM2ile birleştiripSUMalmak: tek tediye birden çok faturayı kapatır, satır çoğalır. ToplamıOVPM.DocTotalüzerinden alın. OJDT/JDT1yerineOCRD.Balancekullanmak:Balancealanının işareti kuruluma göre ters olabilir, bakiye her zaman yevmiyeden doğrulanır.- Tedarikçi filtresi koymamak:
OCRDüzerinden gelen listedeCardTypesüzülmezse aday ve müşteri kartları da alış listesine düşer. DocNumile JOIN kurmak: belge numarası seriye göre tekrar edebilir; eşleşme daimaDocEntryile yapılır.
Sık sorulanlar
Alış raporunda KDV hariç tutarı nasıl bulurum? Belge düzeyinde DocTotal - VatSum, satır düzeyinde PCH1.LineTotal KDV hariç tutarı verir. DocTotal her zaman KDV dahildir; rapor başlığında hangisini kullandığınızı yazın, aksi halde iki rapor tutmaz.
Ödenmemiş borç ile cari bakiye neden farklı çıkıyor? Açık fatura listesi yalnız faturaları görür; tediye avansı, kur farkı ve mahsup kayıtları yevmiyede kalır. Resmi mutabakat için bakiyeyi JDT1 üzerinden ShortName = CardCode ile hesaplayın, OPCH listesini kontrol amaçlı kullanın.
PaidToDate neden faturayı tamamen kapattığım halde sıfırlanmıyor? Kuruş yuvarlaması ya da kısmi mahsup kalmış olabilir. DocStatus = 'C' ise fatura SAP tarafından kapalı sayılır; kalan tutarı DocTotal - PaidToDate > 0 koşuluyla süzün ve SAP ekranındaki kapatma kaydıyla karşılaştırın.
Bu sorgular hangi sürümde çalışır? Sorgular SAP Business One 10.0 SQL Server sürümünde test edildi. OPCH, PCH1, OVPM ve VPM2 tabloları 9.x sürümlerinde de aynı adla durur. HANA sürümünde tarih fonksiyonları ve tırnak kuralları farklıdır, sorgular uyarlanmadan çalışmaz.
Alış raporunu her ay elle yeniden kurmak yerine sorunuzu Türkçe yazın: ERP Asistanı SAP Business One şemanıza uygun, doğrulanmış SQL'i üretir. Nasıl çalıştığını SQL üretme rehberinde görebilirsiniz.
Ücretsiz üye olun, alış ve borç sorgunuzu doğal dille üretin →
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.







