Sorunun Tarihçesi
İkili muhasebe, 15. yüzyılda Luca Pacioli tarafından formelleştirilen, Aktiflerin = Yükümlülükler + Öz Sermaye olması gerektiğini gerektirir. Modern ERP sistemleri, ebeveyn hesapların çocukları topladığı hiyerarşik Hesap Planı yapıları aracılığıyla bunu uygulamaktadır. Karşı hesaplar (örn. Birikmiş Amortisman veya Hazine Hissesi) ebeveynlerinin bakiyesini artırmak yerine azaltır. Bu denklemin her konsolidasyon düzeyinde—sadece kök düzeyinde değil—doğrulanması, yan hesapların şirket mali tablolarına aktarılmadan önce iç tutarlılığa sahip olmasını sağlar.
Sorun
Standart SQL toplaması (SUM) ekleme ilişkileri varsayar. Ancak karşı hesaplar çıkarma gerektirir ve iç içe yerleştirildiğinde (bir karşı hesap başka bir karşı hesabın altında) işaretler çarpılmalıdır (negatif × negatif = pozitif). Dahası, yalnızca üst düzey kök düğümünün doğrulanması, aradaki iş birimlerinde hata saklar. Zorluk, bu işaret çarpanlarını keyfi hiyerarşi derinliklerinde yaymak ve her düğümde cebirsel doğrulamayı gerçekleştirmektir.
Çözüm
Hiyerarşiyi kökten yaprağa kadar izleyen ve birikimli işaret çarpanını taşıyan bir geri çağırmalı CTE kullanın. Her düğüm, ebeveyninin işaret bağlamını miras alır ve kendi karşı hesap mantığını çarpan olarak uygular. Sorgu daha sonra düğüme göre sonuçları gruplamak ve muhasebe denklemini yerel olarak doğrulamak için kullanılır.
WITH RECURSIVE AccountHierarchy AS ( -- Ana: Başlangıç işaret mantığına sahip kök hesaplar SELECT a.account_id, a.parent_id, a.account_type, a.amount, CASE WHEN a.is_contra = 1 THEN -1 ELSE 1 END AS sign_multiplier, CAST(a.account_id AS VARCHAR(1000)) AS path, 1 AS depth FROM accounts a WHERE a.parent_id IS NULL UNION ALL -- Geri çağırmalı: Çocuklar ebeveynlerinin birikimli işaretini miras alır SELECT c.account_id, c.parent_id, c.account_type, c.amount, p.sign_multiplier * CASE WHEN c.is_contra = 1 THEN -1 ELSE 1 END, p.path || ',' || CAST(c.account_id AS VARCHAR(1000)), p.depth + 1 FROM accounts c INNER JOIN AccountHierarchy p ON c.parent_id = p.account_id -- Dolaşım tespiti: Kötü veriden sonsuz döngüleri önleyin WHERE p.path NOT LIKE '%,' || CAST(c.account_id AS VARCHAR(1000)) || ',%' AND p.path != CAST(c.account_id AS VARCHAR(1000)) ), NodeBalances AS ( SELECT account_id, depth, SUM(CASE WHEN account_type = 'Asset' THEN amount * sign_multiplier ELSE 0 END) AS assets, SUM(CASE WHEN account_type IN ('Liability', 'Equity') THEN amount * sign_multiplier ELSE 0 END) AS liab_equity FROM AccountHierarchy GROUP BY account_id, depth ) SELECT account_id, CASE WHEN ABS(assets - liab_equity) < 0.01 THEN 'Dengeli' ELSE 'DENGESİZLİK TESPİT EDİLDİ' END AS validation_status, assets, liab_equity FROM NodeBalances ORDER BY depth, account_id;
Bir Fortune 500 üretim şirketi, üç aylık SEC dosyaları için hazırlık yapıyordu. Genel Defterlerinde, 15 düzey derinliğinde 50,000+ hesap içeren 12 yan kuruluş vardı. Konsolidasyon sırasında, muhasebe ekibi, kurumsal bilanço dengelense de, bireysel iş birimlerinin yanlış sınıflandırılmış karşı hesaplar nedeniyle imkansız negatif varlık toplamları gösterdiğini keşfetti (örn. Hazine Hissesi, Öz Sermaye'den ekleme olarak değil, çıkarma olarak muamele edilmesi).
Sorun Tanımı
CFO, kurumsal ana şirkete geçmeden önce, Hesap Planı'nın her düğümünde muhasebe denkliğinin doğrulanmasını istedi. Basit alt-yukarı toplama işlemi başarısız oldu çünkü Hazine Hissesi (karşı öz sermaye) Toplam Öz Sermayeyi azaltmalıdır, ancak onun çocuk hesapları (belirli hisse geri alım grupları) pozitif değerlerini korumalı ve negatif toplama mantığını miras almalıdır. Excel aracılığıyla manuel doğrulama, veri hacmi ve 48 saatlik dosyalama süresi nedeniyle mümkün olmadı.
Düşünülen Farklı Çözümler
Prosedürel ETL yaklaşımı: Tüm hiyerarşiyi Python'a çıkarın, networkx kullanarak bir ağaç yapısı oluşturun, dengeleri geri çağırmalı hesaplayın ve ihlalleri veritabanına yazın. Artılar: İhtiyaca göre karmaşık iş mantığını uygulamak kolay. Eksiler: 2GB'lık finansal veriyi ağ üzerinden iletmek gerektiriyordu, şirketin "veri ikamet" güvenlik politikasını ihlal etti ve yürütülmesi 6 saat sürdü.
Seviye materyalizasyonu ile kendiliğinden birleştirme: Her bir hesabın seviyesini non-recursive yöntem kullanarak önceden hesaplayın, ardından her seviye için işaret mantığını uygulayarak 15 kendiliğinden birleştirici işlemi (her seviye için bir tane) gerçekleştirin. Artılar: Geri çağırma olmadan saf SQL. Eksiler: Sorgu, eksponensiyel karmaşık predikatlarla bir 15'li birleştirme kabusuna dönüştü, performans 45 dakikaya kötüleşti ve 16. bir düzey eklemek, tam bir sorgu yeniden yazılması gerektiriyordu. Ayrıca, "karşı-karşı" işaret çarpanlarını iç içe CASE ifadeleriyle beceriksiz bir şekilde ele aldı.
İşaret yayılımı ile geri çağırmalı CTE: Yukarıda açıklanan çözümü ANSI SQL geri çağırmalı CTE'ler kullanarak uygulayın. Artılar: Keyfi derinlikleri dinamik olarak yönetir (20 seviyeye kadar test edilmiştir), tam veri kümesinde 8 saniyede çalışır, veri yerelliğini korur ve aritmetik (-1 × -1 = 1) aracılığıyla işaret çarpanlarını doğru bir şekilde uygular. Eksiler: Geri çağırmalı CTE yürütme planlarını ve kötü hiyerarşi verilerinden kaçınmak için döngü tespiti gerektirir.
Hangi çözüm seçildi ve neden
Geri çağırmalı CTE yaklaşımı, sıkı güvenlik gereksinimini (veri ikamet) karşıladığı, 15 dakikalık SLA içinde performans gösterdiği ve şirket yeni bir yan kuruluş edindiğinde kod değişikliği gerektirmediği için seçildi. Her düğümde doğrulama yapılması, birinci çalışmada, 10-K dosyasında maddi hatalara neden olabilecek 23 yanlış sınıflandırılmış hesap tespit etti.
Sonuç
Doğrulama sorgusu, SOX uyum çerçevelerinde kritik bir otomatik kontrol haline geldi. Şu anda her mali kapatma öncesinde otomatik olarak çalışıyor, konsolidasyon hatalarını önlüyor ve uzlaştırma süresini 6 saatten 10 dakikanın altına düşürüyor. İkinci çeyrekte, önceki Excel tabanlı süreçte gözden kaçan "Tahsil Edilemeyen Hesaplar İçin Karşılık" ile ilgili 2.3 milyon dolarlık bir sınıflandırma hatasını tespit etti, bu da şirketin bir yeniden beyan yapmasını engelledi.
Bir karşı hesabın başka bir karşı hesaba ebeveynlik yapabileceği durumda, işaret çarpanlarını birden fazla düzeyde doğru bir şekilde nasıl yayarsınız?
Birçok aday, son SELECT'te yalnızca hesabın kendi is_contra bayrağına ve account_type'a dayanarak bir CASE ifadesi kullanarak işareti belirlemeye çalışır. Bu, hiyerarşik bağlamı göz ardı ettiği için başarısız olur. Doğru yaklaşım, işaretin birikimli bir özellik olarak ele alınmasıdır: geri çağırma sırasında ebeveynin işaret çarpanını çocuğun doğuştan işareti (1 veya -1) ile çarpmaktır. Bu, bir karşı hesabın (işaret -1) başka bir karşı hesabın (ebeveyn işareti -1) altında pozitif bir katkı sağladığını (-1 × -1 = 1) doğru bir şekilde temsil eder. Bu çarpan yayılımı olmadan, ara dengeler yanlış olacaktır, hatta kök dengesi tesadüfen eşleşse bile.
Bütün varlıkların = Yükümlülükler + Öz Sermaye denkleminin, tamamlanmamış bilanço sayfalarını temsil eden bir ağaç içinde doğrulanması için ANSI SQL yöntemi nedir?
Adaylar sıklıkla denklemin her keyfi düğümde geçerli olması gerektiğini varsayar, ancak "Cari Varlıklar" gibi alt ağaçların karşılık gelen Yükümlülük bölümleri yoktur. Çözüm, doğrulama mantığının, konteyner düğümleri (sadece toplama ebeveynleri) ve tam muhasebe denklemleri arasında ayırt edici olmasını tanımayı içerir. Herhangi bir düğüm için, işaretli miktarların cebirsel toplamını hesaplayın ve Varlık tarafını Yükümlülük + Öz Sermaye tarafıyla karşılaştırın. false pozitifleri önlemek için sıfır bakiyeye sahip düğümleri filtrelemek için bir HAVING ifadesi kullanın (saf başlıklar) ve yalnızca içerilen kategoriler için matematiksel ilişki sağlandığında doğrulama yapın.
Neden ANSI SQL geri çağırmalı CTE, hesap hiyerarşilerinde döngü tespitini gerektirir ve bunu özel veritabanı uzantıları olmadan nasıl gerçekleştirirsiniz?
Adaylar genellikle üretim finansal verilerinin sıklıkla döngüsel referanslar (örn. Hesap A, Hesap B'yi ebeveyn yapar, Hesap B, Hesap C'yi ebeveyn yapar, Hesap C, yanlışlıkla Hesap A'ya geri döner) gibi veri kalitesi sorunları içerdiğini göz ardı eder. Koruma olmadan, geri çağırmalı CTE, veritabanı geri çağırma sınırlarına ulaşana veya tüm geçici depolamayı tüketene kadar çalışır, kritik mali kapanmalar sırasında doğrulama işini çökertir. ANSI SQL:1999, CYCLE ifadesini tanıtırken, taşınabilir bir uygulama, geri çağırmalı CTE'de bir yol dizesi veya ziyaret edilen kimliklerin dizisini taşımayı gerektirir. Bir çocuğu birleştirmeden önce, kimliğinin ebeveynin yol dizesinde zaten mevcut olmadığını LIKE desen eşleştirme veya dize fonksiyonları kullanarak kontrol edin. Eğer tespit edilirse, o satırı hariç tutarak terimi sağlamış olursunuz. Bu savunmacı programlama, sorgunun bozulmuş hiyerarşi verileriyle bile tamamlanmasını sağlar.