Правильная ссылка на эту страницу
http://az-design.ru/Support/DataBase/002b3300.shtml

Таблица "План счетов" в бухгалтерском учете

Архангельский Андрей

English

Испытательная лаборатория

       "План счетов бухгалтерского учета" — это типично древовидная структура, в которой можно и нужно использовать все свойства древовидный структур. Т.е. не только поиск по дереву, но и суммирование с использованием дерева.
       Для проведения экспериментов создадим маленькую бухгалтерскую систему, состоящую из трех таблиц:
       — ChartAcc - "План счетов бухгалтерского учета",
       — Journal - "Журнал хозяйственных операций" (ЖХО),
       — TurnSheet - "Оборотная ведомость.
       В качестве "Плана Счетов" возьмем "План счетов бухгалтерского учета финансово-хозяйственной деятельности организаций", утвежденный МинФином РФ в 2003г. Скрипт для его заполнения находится в файле dat\ChartAcc2003.txt.
       Для того чтобы как-то продемонстрировать работу Плана Счетов и Оборотной ведомости, создадим организацию (фирму) "АзБука" с учредителями Папой Карло и Буратино, которые начинают свою хозяйственную деятельность со знаменитой покупки Буратино трех корочек хлеба в харчевне "Три пескаря". Скрипт для заполнения ЖХО находится в файле dat\Journal.txt.
       Совокупный результат деятельности фирмы, основанный на журнале хозяйственных операций, можно посмотреть в отчете, который получил название "Оборотная ведомость". Что это такое, показано ниже:

ACCNT       SALDOBEG     TURNCRD     TURNDBT    SALDOEND 
======== =========== =========== =========== =========== 
01-Киоск      0.0000      0.0000    508.4700    508.4700 
04-00         5.0000      0.0000      0.0000      5.0000 
08-Киоск      0.0000   -600.0000    600.0000      0.0000 
10-МтСтр      0.0000   -600.0000    600.0000      0.0000 
19-НДС09      2.3100    -30.9600    117.6900     89.0400 
19-НДС18      0.0000    -91.5300    183.0600     91.5300 
26-Банк       0.0000      0.0000     10.0000     10.0000 
41-Питан      0.0000  -1424.9900   1425.0000      0.0100 
42-Питан    -49.5500   -653.6900      0.0000   -703.2400 
50-Текущ     59.0100  -2190.0000   2137.5000      6.5100 
51-Б001       0.0000  -1660.0000   1815.0000    155.0000 
60-00         0.0000  -2035.0000   2035.0000      0.0000 
62-00         0.0000  -2137.5000   2137.5000      0.0000 
68-НДС09     -6.7700   -176.5100     30.9600   -152.3200 
75-ВБртн      0.0000      0.0000      0.0000      0.0000 
75-ВКрло      0.0000      0.0000      0.0000      0.0000 
80-00       -10.0000      0.0000      0.0000    -10.0000 
90-00         0.0000  -2137.5000   2137.5000      0.0000

       Первый столбец [Accnt], содержит номера счетов (из "Плана счетов") по которым проводились хозяйственные операции.
       Второй столбец [SaldoBeg], показывает "Сальдо" или остаток денежных средств на начало отчетного периода.
       Третий столбец [TurnCrd], показывает обороты по Кредиту или сколько денежных средств было снято (убыло) с соответствующего счета за отчетный период.
       Четвертый столбец [TurnDbt], показывает обороты по Дебету или сколько денежных средств было добавлено (прибыло) на соответствующий счет за отчетный период.
       Пятый столбец [SaldoEnd], показывает "Сальдо" или остаток денежных средств на конец отчетного периода.
       Таким образом для грамотного человека эта табличка рассказывает почти все о финансовом состоянии фирмы за отчетный период.
       Однако, в налоговые органы сдается не оборотная ведомость, а "Бухгалтерский Баланс", который очень похож на "оборотную ведомость", но, во-первых по другому группирует счета, а во-вторых, в нем отсутствуют средние колонки с оборотами. Основная часть "Бухгалтерского баланса" приведена ниже:

А К Т И В Код На начало отчетного периода На конец отчетного периода
1 2 3 4
I. ВНЕОБОРОТНЫЕ АКТИВЫ      
Нематериальные активы [04,05] 110    
Основные средства [01,02] 120    
Незавершенное строительство [07,08,16,60] 130    
Доходные вложения в материальные ценности [03,02] 135    
Долгосрочные финансовые вложения [58,59] 140    
Отложенные налоговые активы [09] 145    
Прочие внеоборотные активы 150    
Итого по разделу: 190    
II. ОБОРОТНЫЕ АКТИВЫ      
Запасы 210    
В том числе:      
- сырье, материалы и другие ценности [10,14,15,16] 211    
- животные на выращивании и откорме [11] 212    
- затраты в незавершенном производстве [14,20, 21, 23, 29, 44, 46] 213    
- готовая продукция для перепродажи [14,16,41,43] 214    
- товары отгруженные [45] 215    
- расходы будущих периодов [97] 216    
- прочие запасы и затраты 217    
Налог на добавленную стоимость по приобретенным ценностям [19] 220    
Дебиторская задолженность (платежи по которой ожидаются более чем через 12 месяцев после отчетной даты) 230    
В том числе покупатели и заказчики [62,63,76] 231    
Дебиторская задолженность (платежи по которой ожидаются в течение 12 месяцев после отчетной даты) 240    
В том числе покупатели и заказчики [62,63,76] 241    
Краткосрочные финансовые вложения [58,59,81] 250    
Денежные средства [50,51,52,55,57] 260    
Прочие оборотные активы 270    
Итого по разделу: 290    
Баланс 300    
       
П А С С И В Код На начало отчетного периода На конец отчетного периода
1 2 3 4
III. КАПИТАЛ И РЕЗЕРВЫ      
Уставный капитал [80] 410    
Собственные акции, выкупленные у акционеров 411    
Добавочный капитал [83] 420    
Резервный капитал [82] 430    
- резервы, образованные в соответствием с законодательством 431    
- резервы, образованные в соответствии с учредительными документами 432    
Нераспределенная прибыль (непокрытый убыток) [84,99] 470    
ИТОГО по разделу: 490    
IV. ДОЛГОСРОЧНЫЕ ОБЯЗАТЕЛЬСТВА      
Займы и кредиты [67] 510    
Отложенные налоговые обязательства [77] 515    
Прочие долгосрочные обязательства 520    
ИТОГО по разделу:      
V. КРАТКОСРОЧНЫЕ ОБЯЗАТЕЛЬСТВА      
Займы и кредиты [66] 610    
Кредиторская задолженность 620    
В том числе      
- поставщики и подрядчики [60,76] 621    
- задолженность перед персоналом организации [70] 622    
- задолженность перед государственными внебюджетными фондами [69] 623    
- задолженность по налогам и сборам [68] 624    
- прочие кредиторы 625    
Задолженность перед участниками (учредителями) по выплате доходов [75] 630    
Доходы будущих периодов [98] 640    
Резервы предстоящих расходов [96] 650    
Прочие краткосрочные обязательства 660    
ИТОГО по разделу: 690    
БАЛАНС 700    

       Как видно, каждая строка "Баланса" имеет номер, а в наименовании строки указано, какие счета входят в сумму по этой строке. Сам "Баланс" имеет разделы и легко преобразуется в древовидную форму.
       Кроме этого, в налоговые органы сдается другой отчет, который называется "Отчет о прибылях и убытках". Этот отчет также может быть построен в древовидном виде и содержит данные из средних колонок "Оборотной ведомости". Основная часть "Очета о прибылях и убытках" приведена ниже:

Наименование Код показателя За отчетный период
1 2 3
ДОХОДЫ И РАСХОДЫ ПО ОБЫЧНЫМ ВИДАМ ДЕЯТЕЛЬНОСТИ    
Выручка (нетто) от продажи товаров, продукции, работ, услуг (за минусом налога на добавленную стоимость, акцизов и аналогичных обязательных платежей) 010  
Себестоимость проданных товаров, продукции, работ, услуг 020  
Валовая прибыль 029  
Коммерческие расходы 030  
Управленческие расходы 040  
Прибыль (убыток) от продаж 050  
ПРОЧИЕ ДОХОДЫ И РАСХОДЫ    
Проценты к получению 060  
Проценты к уплате 070  
Доходы от участия в других организациях 080  
Прочие доходы 090  
Прочие расходы 100  
ПРИБЫЛЬ (УБЫТОК) ДО НАЛОГООБЛОЖЕНИЯ 140  
Отложенные налоговые акивы 141  
Отложенные налоговые обязательства 142  
Текущий налог на прибыль 150  
     
ЧИСТАЯ ПРИБЫЛЬ (УБЫТОК) ОТЧЕТНОГО ПЕРИОДА 190  
СПРАВОЧНО    
Постоянные налоговые обязательства (активы) 200  
Базовая прибыль (убыток) на акцию 210  
Разводненная прибыль (убыток) на акцию 220  

       В этом отчете также каждая строка имеет свой номер, а сам отчет имеет явную древовидную структуру.
       Таким образом, если в таблице ChartAcc ("План счетов") использовать три дерева — "План счетов", "Баланс" и "Отчет о прибылях и убытках", то появляется возможность легко автоматизировать некоторые функции. После получения оборотной ведомости ее можно просуммировать по соответствующему дереву:
       — суммирование по первому дереву дает структуру доходов, расходов, остатков для принятия управленческих решений. При любой глубине детализации счетов отчет для директора не будет превышать одной страницы.
       — суммирование по второму дереву дает готовый отчет "Баланс предприятия"
       — суммирование по третьему дереву дает готовый "Отчет о прибылях и убытках"

 

Реализация таблиц и процедур

       Итак, сначала построим таблицу ChartAcc ("План счетов"):

Create table CHARTACC (
   AccntNum     CRDB NOT NULL PRIMARY KEY,
   AccPARNT     CRDB,   AccCount   AZInt32D0,  AccOrder   AZInt32D0,
   AccBalns     CRDB,   AccBlCnt   AZInt32D0,  AccBlOrd   AZInt32D0,
   AccProft     CRDB,   AccPfCnt   AZInt32D0,  AccPfOrd   AZInt32D0,
   AccTITLE     AZTITLE,
   ACDESCRP     AZNOTES);
Commit;

       Поле AccntNum содержит номер счета и имеет формат Char(8), что позволяет использовать любой вид нумерации счетов, сохраняя при этом читаемость всех документов. Использование во многих программах, в том числе 1С, Фрегат и др., числовой нумерации приводит к многочисленным проблемам:
       — при многих уровнях детализации невозможно запомнить что означает номер 37 на пятом уровне.
       — Обещание разработчиков этих программ — "Вы можете использовать счета с любым уровнем вложенности" приводит к тому, что создаются абсолютно не читаемые номера счетов типа — 10.03.23.01 и т.д. Так в программе Фрегат в течение 2 минут удалось создать номер счета 5 уровня длинной 136 символов. Никаких ошибок программа не обнаружила. Но этого мало. Если попробовать напечатать Журнал Хозяйственных Операций с такими счетами или оборотную ведомость, то на стандартный лист бумаги такой отчет не помещается. Генеральный директор фирмы "Фрегат" — Абасов Рауф Гулиевич — в частном разговоре на это ответил, что "этот бардак сделан пользователем и он должен за ним следить". Что не совсем так. Для того и существует программист, чтобы пользователь не мог делать глупости. Правда в программе "1С-Бухгалтерия" есть стандартный способ решения таких проблем — уменьшение размера шрифта. Если для разработчика такой способ является наиболее удобным, то для бухгалтера он неприемлемым. Вероятность ошибки при чтении документов с микроскопом увеличивается в десятки раз. Первое требование к бухгалтерским документам — удобочитаемость.
       Деревья строятся стандартным способом — Родительское поле, Счетчик потомков, Порядок сортировки для каждого дерева.
       Поле AccTitle содержит наименование счета, а поле AcDescrp, при необходимости, описание или инструкцию по применению счета.

Insert into ChartAcc(AccntNum,AccParnt,AccBalns,AccProft,
                              AccOrder,AccBlOrd,AccPfOrd,AccTitle)
Values('.','.','.','.',-10,-10,-10,'Корень дерева');
Commit;

       До установки связей в таблице, заполняется первая строка для того чтобы исключить использования пустых (Null) полей. Параметр сортировки -10 заставляет эту строку быть всегда наверху.

ALTER TABLE CHARTACC add foreign key (AccPARNT) references CHARTACC
                         on UPDATE cascade;
ALTER TABLE CHARTACC add foreign key (AccBalns) references CHARTACC
                         on UPDATE cascade;
ALTER TABLE CHARTACC add foreign key (AccProft) references CHARTACC
                         on UPDATE cascade;
Commit;

       После чего можно установить связи, которые определяют древовидную структуру.
       Триггеры Insert, Update и Delete для изменения счетчиков потомков, также стандартные.

SET TERM !! ;
CREATE TRIGGER CHARTACC_INSERT FOR CHARTACC
ACTIVE BEFORE INSERT POSITION 0 AS 
BEGIN
   Update ChartAcc a set a.AccCount = a.AccCount+1
          where a.AccntNum=new.AccParnt;
   Update ChartAcc a set a.AccBlCnt = a.AccBlCnt+1
          where a.AccntNum=new.AccBalns;
   Update ChartAcc a set a.AccPfCnt = a.AccPfCnt+1
          where a.AccntNum=new.AccProft;
END !!
SET TERM ; !!
Commit;
SET TERM !! ;
Create trigger CHARTACC_UPDATE for CHARTACC
ACTIVE BEFORE UPDATE POSITION 0 AS
BEGIN
If (old.AccParnt<>new.AccParnt) then
    begin
      Update ChartAcc a set a.AccCount = a.AccCount+1
             where a.AccntNum=new.AccParnt;
      Update ChartAcc a set a.AccCount = a.AccCount-1
             where a.AccntNum=old.AccParnt;
    End
If (old.AccBalns<>new.AccBalns) then
    begin
      Update ChartAcc a set a.AccBlCnt = a.AccBlCnt+1
             where a.AccntNum=new.AccBalns;
      Update ChartAcc a set a.AccBlCnt = a.AccBlCnt-1
             where a.AccntNum=old.AccBalns;
    End
If (old.AccProft<>new.AccProft) then
    begin
      Update ChartAcc a set a.AccPfCnt = a.AccPfCnt+1
             where a.AccntNum=new.AccProft;
      Update ChartAcc a set a.AccPfCnt = a.AccPfCnt-1
             where a.AccntNum=old.AccProft;
    End
END !!
SET TERM ; !!
Commit;
SET TERM !! ;
CREATE TRIGGER CHARTACC_DELETE FOR CHARTACC
ACTIVE AFTER DELETE POSITION 0 AS 
BEGIN
   Update ChartAcc a set a.AccCount = a.AccCount-1
          where a.AccntNum=old.AccParnt;
   Update ChartAcc a set a.AccBlCnt = a.AccBlCnt-1
          where a.AccntNum=old.AccBalns;
   Update ChartAcc a set a.AccPfCnt = a.AccPfCnt-1
          where a.AccntNum=old.AccProft;
END !!
SET TERM ; !!
Commit;
Insert into ChartAcc(AccntNum,AccParnt,AccBalns,AccProft,
                              AccOrder,AccBlOrd,AccPfOrd,AccTitle,AcDescrp)
Values('AccBase','.','.','.',-3,-3,-3,'Основной план счетов','');
Insert into ChartAcc(AccntNum,AccParnt,AccBalns,AccProft,
                              AccOrder,AccBlOrd,AccPfOrd,AccTitle,AcDescrp)
Values('Balans','.','.','.',-2,-2,-2,'Баланс предприятия','');
Insert into ChartAcc(AccntNum,AccParnt,AccBalns,AccProft,
                              AccOrder,AccBlOrd,AccPfOrd,AccTitle,AcDescrp)
Values('Profit','.','.','.',-1,-1,-1,'Отчет о прибылях и убытках','');
Commit;

       Первоначальное заполнение таблицы происходит также как в таблице Goods (Товары), описанной выше. Дальнейшее заполнение происходит из скрипта находящиегося в файле dat\ChartAcc2003.txt. Например:

Insert into ChartAcc(AccntNum,AccParnt,AccBalns,AccProft,
                     AccOrder,AccBlOrd,AccPfOrd,AccTitle)
Values('41-00','Товары','Бл.214','Пр.020',41,0,0,'Счет 41. Товары');

       В данном случае заполнение скрипта очень простое. Так как идентификатор и ключевое поле это одно и тоже, то не требуется искать идентификатор с помощью вложенного запроса. Достаточно напрямую вписать родительский код соответствующего дерева если он уже существует в таблице.
       В приведенной строке:
       — '41-00' - номер счета,
       — 'Товары' - группа, в которую он входит в "Плане счетов",
       — 'Бл.214' - номер строки в "Балансе", на которой он учитывается,
       — 'Пр.020' - номер строки в "Отчеты о Прибылиях", на которой он учитывается.
       Процедура поиска потомков текущего узла построена таким же образом, как и в таблице Goods (Товары):

set term !! ;
create procedure AccntChild (AccPrnt char(8), Tree SmallInt)
       returns (AccChld char(8)) as
begin
IF (Tree=0) then begin
   for select AccntNum from ChartAcc
       where  AccPARNT=:AccPrnt  into :AccChld
       do begin 
          suspend;
          for select AccChld from  AccntChild(:AccChld,:Tree) into :AccChld
          do begin
             suspend;
             end
          end
   end
IF (Tree=1) then begin
   for select AccntNum from ChartAcc
       where  AccBalns=:AccPrnt  into :AccChld
       do begin 
          suspend;
          for select AccChld from  AccntChild(:AccChld,:Tree) into :AccChld
          do begin 
             suspend;
             end
          end
   end
IF (Tree=2) then begin
   for select AccntNum from ChartAcc
       where  AccProft=:AccPrnt  into :AccChld
       do begin 
          suspend;
          for select AccChld from  AccntChild(:AccChld,:Tree) into :AccChld
          do begin 
             suspend;
             end
          end
   end
end !!
set term ; !!
Commit;

       Для суммирования по дереву необходимо знать уровень узла относительно вершины и узел, находящийся на определенном уровне. Для этого используется хранимая процедура, которая ранее не описывалась.
       Процедура AccLevel выполняет следующие функции:
       — Если параметр ILev>=0 то возвращается уровень счета от вершины дерева, с учетом начального значения параметра.
       — Если параметр ILev<0 то возвращается название счета на ILev уровня выше текущего.
       Параметр Tree определяет номер дерева, по которому выполняется поиск.

set term !! ;
create procedure AccntLevel (Accnt char(8), ILev Integer, Tree Integer)
                  returns (AccPrnt Char(8), AccLev Integer) as
begin

AccLev = ILev + 1; If (Tree=0) then select AccParnt from ChartAcc where AccntNum=:Accnt into :AccPrnt; If (Tree=1) then select AccBalns from ChartAcc where AccntNum=:Accnt into :AccPrnt; If (Tree=2) then select AccProft from ChartAcc where AccntNum=:Accnt into :AccPrnt; If (AccLev=0) then suspend; else begin If (:AccPrnt <> '.') then begin select AccPrnt, AccLev from AccntLevel(:AccPrnt,:AccLev,:Tree) into :AccPrnt,:AccLev; suspend; end else suspend; end end !! set term ; !! Commit;

       Работа процедуры очень проста.
       Первая строка увеличивает счетчик уровней на единицу.
       Далее в переменную AccPrnt записывается значение родительского поля по дереву, которое определяется переменной Tree.
       Если значение AccLev=0, то выполнение процедуры завершается и возвращается результат в виде переменной AccPrnt.
       В противном случае продолжается поиск — если AccPrnt<>'.', т.е. если это не вершина дерева, то обращаемся снова к процедуре AccLevel. Если достигли вершины дерева, то выполнение процедуры завершается.
       Для тестирования создается таблица простейшего журнала хозяйственных операций:

Create table Journal
    (JrnDate    Date not null,
     JrCredit   CRDB references ChartAcc on update cascade,
     JrDebit    CRDB references ChartAcc on update cascade,
     JrAmount   AZMoney not null,
     JrLegend   AZLegend);
Commit;

       Журнал состоит из нескольких полей — Дата операции, Счет по Кредиту, Счет по Дебиту, Сумма операции и описание операции. В принципе можно добавить какие-либо индексы, но так как объем таблицы небольшой, использоваться она будет для тестирования, то различные улучшения и аналитические признаки не использовались.
       И, наконец, для хранения оборотной ведомости используется таблица TurnSheet:

Create table TURNSHEET (
       TRNAccnt    CRDB not null, -- номер счета
       TRAParnt    CRDB,          -- номер счета для группировки
       TCls        AZInt32 not null,
       TRALev      AZInt32,
       MSldBeg     AZMoney default 0.0, -- сальдо на начало периода
       MTrnCrd     AZMoney default 0.0, -- оборот по кредиту
       MTrnDbt     AZMoney default 0.0, -- оборот по дебиту
       MSldEnd     AZMoney default 0.0, -- Сальдо на конец периода
       TRNDTBeg    Date,    -- дата начала периода
       TRNDTEnd    Date,    -- дата конца периода
Primary key (TRNAccnt,TCls));
Commit;

       Кроме вполне очевидных полей — TrnAccnt, MSldBeg, MTrnCrd, MTrnDbt, MSldEnd — используются несколько служебных полей, назначение который будет понятно в дальнейшем.

Create view TurnSheetView
    (Accnt,SaldoBeg,TurnCrd,TurnDbt,SaldoEnd,DateBeg,DateEnd)
as
Select TrAParnt,SUM(MSldBeg),SUM(MTrnCrd),SUM(MTrnDbt),SUM(MSldEnd),
       Min(TRNDTBeg),Min(TRNDTEnd)
  from TurnSheet
 Group by TrAParnt;
Commit;

       Так как оборотная ведомость будет рассматриваться не только в прямом, но и преобразованном (суммированом) виде, то результат лучше смотреть через представление, которое группирует строки по полю TrAParnt и суммирует их. Поле TrAParnt будет изменяться процедурой TurnSum в зависимости от вида и уровня дерева, по которому происходит суммирование.
       И, наконец, нужно получить саму оборотную ведомость. Для этого используется простейшая процедура TuenSheetCore, которая в качестве параметров принимает даты начала и конца отчетного периода. Процедура состоит из 4.5 запросов:

SET TERM !! ;
CREATE PROCEDURE TurnSheetCore (
       DtBeg    DATE,     -- Дата начала периода
       DtEnd    DATE)     -- Дата конца периода
as
Begin
  Delete from TurnSheet; -- удаление предыдущего результата
  Insert into TurnSheet(TRNAccnt,TCLs,MSldBeg,MSldEnd)
       Select JrCREDIT,1,-SUM(JrAmount),-SUM(JrAmount)
         from Journal  where JrnDATE<:DTBEG 
        group by JrCREDIT;

       — вычисляется сумма всех операций по Кредиту счетов в журнале операций, которые произошли до даты начала отчетного периода, и результат со знаком минус записывается в Сальдо начала и конца периода. Для того тобы отличать каким запросом получена та или иная строка используется признак в поле TCls, который устанавливается явным образом.

  Insert into TurnSheet(TRNAccnt,TCLs,MSldBeg, MSldEnd)
       Select JrDEBIT,2,SUM(JrAmount),SUM(JrAmount)
        from Journal  where JrnDATE<:DTBEG 
       group by JrDEBIT;

       — вычисляется сумма всех операций по Дебету счетов в журнале операций, которые произошли до даты начала отчетного периода, и результат со знаком плюс записывается в Сальдо начало и конца периода.

  Insert into TurnSheet(TRNAccnt,TCLs,MTrnCrd, MSldEnd)
       Select JrCREDIT,3,-SUM(JrAmount),-SUM(JrAmount)
         from Journal
        where (JrnDATE>=:DTBEG AND JrnDATE<:DTEND)
        group by JrCREDIT;

       — вычисляется сумма всех операций по Кредиту счетов в журнале операций, которые произошли в течении отчетного периода, и результат со знаком минус записывается в столбец MTrnCrd как оборот по Кредиту, а также в столбец MSldEnd как Сальдо конца периода.

  Insert into TurnSheet(TRNAccnt,TCLs,MTrnDbt, MSldEnd)
       Select JrDEBIT,4,SUM(JrAmount), SUM(JrAmount)
         from Journal
        where (JrnDATE>=:DTBEG AND JrnDATE<:DTEND)
        group by JrDEBIT;

       — вычисляется сумма всех операций по Дебету счетов в журнале операций, которые произошли в течении отчетного периода, и результат со знаком плюс записывается в столбец MTrnDbt как оборот по Дебиту, а также в столбец MSldEnd как Сальдо конца периода.

Insert into TurnSheet(TrnAccnt,TRAParnt,TCls,
             MSldBeg,MTrnCrd,MTrnDbt,MSldEnd,TRNDtBeg,TRNDtEnd)
Select TRNAccnt,TRNAccnt as TRAParnt,0,
       Sum(MSldBeg),Sum(MTrnCrd),Sum(MTrnDbt),Sum(MSldEnd),:DtBeg,:DtEnd
  from TurnSheet
  where TCls>0
  group by TRNAccnt;

       Окончательный запрос просто суммирует результаты предыдущих запросов, сохраненных в таблице TurnSheet и записывает их в нее же с признаком TCls=0. Для справки добавляются даты начала и конца периода из входных переменных.

  Delete from TurnSheet where TCls>0;

       Последняя строка просто удаляет промежуточные результаты.

END !!
SET TERM ; !!
Commit;

       Таким образом, после выполнения процедуры TurnSheetCore в таблице TurnSheet образовалась оборотная ведомость за указанный период. Теперь можно с ней эксперименттировать.
       Хранимая процедура TurnSum предназначена для суммирования оборотной ведомости используя различные деревья и глубину. Задача процедуры простая — записать в поле TrAParnt номер счета, который имеет уровень, заданный в переменной Lev, в дереве, заданный в переменной Tr.

SET TERM !! ;

CREATE PROCEDURE TurnSum (Tr SmallInt, Lev SmallInt) as declare variable TAcc Char(8); -- Текущий Счет DECLARE VARIABLE TLev SmallInt; -- Текущий уровень DECLARE VARIABLE TDLv SmallInt; -- Разница уровней BEGIN 01 Update TurnSheet set TRAParnt=TRNAccnt; 02 for select all TrnAccnt from TurnSheet order by TrnAccnt into :TAcc 03 do 04 begin 05 Select AccLev from AccLevel(:TAcc,0,:Tr) into :TLev; 06 TDLv = Lev - TLev; 07 IF (TDLv<0) THEN 08 Update TurnSheet set 09 TrAParnt=(select AccPrnt from AccLevel(:TAcc,:TDLv,:Tr)) 10 where TrnAccnt=:TAcc; 11 ELSE 12 Update TurnSheet set TrAParnt=:TAcc where TrnAccnt=:TAcc; 13 end END !! SET TERM ; !! Commit;

       Строка 01 приводит таблицу TurnSheet в первоначальное состояние.
       Со строки 02 начинается просмотр всей таблицы TurnSheet и выполнения для каждой строки необходимых действий.
       Строка 05 с помощью процедуры AccLevel находит уровень текущего счета в заданном дереве.
       Строка 06 получает разницу уровней между текущим и заданным.
       Строка 07 — если разница уровней отрицательна, то с помощью процедуры AccLevel находим номер родительского счета в заданном дереве на заданном уровне (строка 09) и записываем его в поле TrAParnt (строка 08) для текущей строки.
       В противном случае записываем в поле TrAParnt текущее значение поля TrnAccnt.
       Теперь можно посмотреть на результат.
       Для получения оборотной ведомости выполним команду:

Execute procedure TurnSheetCore('11/1/2006','12/1/2006');
Commit;

       И посмотрим результат:

Select Accnt,SaldoBeg,TurnCrd,TurnDbt,SaldoEnd from TurnSheetView;
ACCNT            SALDOBEG          TURNCRD          TURNDBT         SALDOEND 
======== ================ ================ ================ ================ 
01-Киоск           0.0000           0.0000         508.4700         508.4700 
04-00              5.0000           0.0000           0.0000           5.0000 
08-Киоск           0.0000        -600.0000         600.0000           0.0000 
10-МтСтр           0.0000        -600.0000         600.0000           0.0000 
19-НДС09           2.3100         -30.9600         117.6900          89.0400 
19-НДС18           0.0000         -91.5300         183.0600          91.5300 
26-Банк            0.0000           0.0000          10.0000          10.0000 
41-Питан           0.0000       -1424.9900        1425.0000           0.0100 
42-Питан         -49.5500        -653.6900           0.0000        -703.2400 
50-Текущ          59.0100       -2190.0000        2137.5000           6.5100 
51-Б001            0.0000       -1660.0000        1815.0000         155.0000 
60-00              0.0000       -2035.0000        2035.0000           0.0000 
62-00              0.0000       -2137.5000        2137.5000           0.0000 
68-НДС09          -6.7700        -176.5100          30.9600        -152.3200 
75-ВБртн           0.0000           0.0000           0.0000           0.0000 
75-ВКрло           0.0000           0.0000           0.0000           0.0000 
80-00            -10.0000           0.0000           0.0000         -10.0000 
90-00              0.0000       -2137.5000        2137.5000           0.0000

       Теперь выполним суммирование по первому дереву (План счетов) по уровеню 3 и посмотрим что получилось:

Execute procedure TurnSum(0,3);
Commit;
ACCNT            SALDOBEG          TURNCRD          TURNDBT         SALDOEND 
======== ================ ================ ================ ================ 
01-00              0.0000           0.0000         508.4700         508.4700 
04-00              5.0000           0.0000           0.0000           5.0000 
08-00              0.0000        -600.0000         600.0000           0.0000 
10-00              0.0000        -600.0000         600.0000           0.0000 
19-00              2.3100        -122.4900         300.7500         180.5700 
26-00              0.0000           0.0000          10.0000          10.0000 
41-00              0.0000       -1424.9900        1425.0000           0.0100 
42-00            -49.5500        -653.6900           0.0000        -703.2400 
50-00             59.0100       -2190.0000        2137.5000           6.5100 
51-00              0.0000       -1660.0000        1815.0000         155.0000 
60-00              0.0000       -2035.0000        2035.0000           0.0000 
62-00              0.0000       -2137.5000        2137.5000           0.0000 
68-00             -6.7700        -176.5100          30.9600        -152.3200 
75-00              0.0000           0.0000           0.0000           0.0000 
80-00            -10.0000           0.0000           0.0000         -10.0000 
90-00              0.0000       -2137.5000        2137.5000           0.0000

       Получилась таблица, соответствующая плану счетов без субсчетов. Однако директору можно подавать более краткую информацию — суммированную по уровню 2.

Execute procedure TurnSum(0,2);
Commit;
ACCNT            SALDOBEG          TURNCRD          TURNDBT         SALDOEND
======== ================ ================ ================ ================
Активы             5.0000        -600.0000        1108.4700         513.4700
Деньги            59.0100       -3850.0000        3952.5000         161.5100
Запасы             2.3100        -722.4900         900.7500         180.5700
Затраты            0.0000           0.0000          10.0000          10.0000
Капитал          -10.0000           0.0000           0.0000         -10.0000
Расчеты           -6.7700       -4349.0100        4203.4600        -152.3200
Товары           -49.5500       -2078.6800        1425.0000        -703.2300
ФинРез.            0.0000       -2137.5000        2137.5000           0.0000

       Такой объем информации можно даже передавать по SMS.

       Теперь просуммируем оборотную ведомость по второму дереву ("Баланс") по уровню 4:

Execute procedure TurnSum(1,4);
Commit;
Select * from TurnSheetView
where SaldoBeg<>0.0 and SaldoEnd<>0.0;
ACCNT            SALDOBEG          TURNCRD          TURNDBT         SALDOEND
======== ================ ================ ================ ================
Бл.110             5.0000           0.0000           0.0000           5.0000
Бл.220             2.3100        -122.4900         300.7500         180.5700
Бл.260            59.0100       -3850.0000        3952.5000         161.5100
Бл.410           -10.0000           0.0000           0.0000         -10.0000
Бл.620            -6.7700       -2211.5100        2065.9600        -152.3200

       В результате, если взять только колонки SaldoBeg и SaldoEnd, то заполнение баланса сводится к переписыванию значений в соответствующие столбики "Бухгалтерского баланса" в строках, которые указаны в колонке Accnt.

       Если просуммировать оборотную ведомость по третьему дереву ("Отчет о прибылях")

Execute procedure TurnSum(2,3);
Commit;
Select * from TurnSheetView
where TurnCrd<>0.0 and TurnDbt<>0.0;
ACCNT            SALDOBEG          TURNCRD          TURNDBT         SALDOEND
======== ================ ================ ================ ================
Пр.020             0.0000       -1424.9900        1425.0000           0.0100

Как это отобразить?

       Осталось отобразить дерево "План счетов". Способ отображения несколько отличается от предыдущих, потому что вместо ID используется строковое поле с номером счета. В качестве примера используется маленькая бухгалтерская программа, которая расположена в каталоге Example09.
       Итак, для того чтобы получить имена деревьев и сформировать из них ComboBox воспользуемся таким же приемом, как с таблицей Goods:

procedure TForm1.FormCreate(Sender: TObject); {Example09}
Var
   db : String;
   ps : Integer;
begin
. . . . . .
   cbxTreeSel.Items.Clear;
   qrExeProc.Close;   qrExeProc.SQL.Clear;
   qrExeProc.SQL.Add('Select first 4 AccTitle from ChartAcc');
   qrExeProc.SQL.Add(' where AccParnt=''.'' and AccOrder<0');
   qrExeProc.SQL.Add(' order by AccOrder');
   qrExeProc.Open;  qrExeProc.First;
   While not qrExeProc.Eof do begin
      cbxTreeSel.Items.Add(qrExeProc.FieldValues['AccTitle']);
      qrExeProc.Next;
   end;
   cbxTreeSel.ItemIndex:=0;
. . . . .
end;

       Из таблицы ChartAcc выбираются первые 4 записи, которые в качестве родителя имеют корневую запись — '.'. Так как в таблице могут быть временно неопределенные записи, то необходимо каким то образом определить какие записи действительно являются корневыми. Для этого используется дополнительный признак — поле сортировки должно иметь отрицательное значение. Полученный набор последовательно записывается в cbxTreeSel, после чего его индес устанавливается в 0. Что и показано на рисунке:


Рис.3-11 План счетов — начальное состояние

       Выбор из списка инициирует первоначальное построение дерева процедурой TreeDataSetOpen:

procedure TForm1.TreeDatasetOpen();
Var
   RowChild   : Integer;
   sFld  : String;
   ChildNode,NewNode : TTreeNode;
begin
  inherited;
  If qrTVChartAcc.Database.Connected then
     begin
        tvChartAcc.Items.BeginUpdate;
        tvChartAcc.Visible := False;
        tvChartAcc.Items.Clear;
        tvChartAcc.Visible := True;
        If cbxTreeSel.ItemIndex>0 then begin
        trTVChartAcc.Active := True;
        qrTVChartAcc.Close;   qrTVChartAcc.SQL.Clear;
        qrTVChartAcc.SQL.Add('Select * from ChartAcc');
        If cbxTreeSel.ItemIndex=1 then 
          qrTVChartAcc.SQL.Add('where AccParnt=''AccBase'' order by AccOrder');
        If cbxTreeSel.ItemIndex=2 then 
           qrTVChartAcc.SQL.Add('where AccBalns=''Balans'' order by AccBlOrd');
        If cbxTreeSel.ItemIndex=3 then
           qrTVChartAcc.SQL.Add('where AccProft=''Profit'' order by AccPfOrd');

       В зависимости от состояния cbxTreeSel запрос выбирает записи первого уровня из того или иного дерева.

        qrTVChartAcc.Open;     qrTVChartAcc.First;
        While not qrTVChartAcc.EOF do
          begin
             RowChild := 0;
             sFld := qrTVChartAcc.FieldValues['AccntNum'];
             sFld := acText8(sFld);
             sFld := sFld+' - '+qrTVChartAcc.FieldValues['AccTitle'];

       В отличии от предыдущих случаев ключевое поле является строкой. Разные компоненты по разному обрабатывают текстовые поля. Например, IBO рассматривает ВСЕ строковые поля как VarChar, в то же время IBX рассматривает ВСЕ строковые поля как Char. Для того чтобы исправить эту ситуацию используется функция acText8.

             NewNode := tvChartAcc.Items.Add(tvChartAcc.TopItem,sFld);
             If cbxTreeSel.ItemIndex=1 then 
                RowChild := qrTVChartAcc.FieldValues['AccCount'];
             If cbxTreeSel.ItemIndex=2 then 
                RowChild := qrTVChartAcc.FieldValues['AccBlCnt'];
             If cbxTreeSel.ItemIndex=3 then 

RowChild := qrTVChartAcc.FieldValues['AccPfCnt']; If RowChild>0 then ChildNode := tvChartAcc.Items.AddChild(NewNode,IntToStr(RowChild));

       Если счетчик потомков больше 0, то создается потомок в узле.

             qrTVChartAcc.Next;
          end; // While not qrTVChartAcc.EOF do
          end;
          tvChartAcc.Items.EndUpdate;
          tvChartAcc.Update;
          qrTVChartAcc.Close;      trTVChartAcc.Active := False;
    end; // qrTVGoods.Database.Connected
end;

       Для раскрытия узлов используется процедура tvChartAccExpanding

procedure TForm1.tvChartAccExpanding(Sender: TObject; Node: TTreeNode;
                                     var AllowExpansion: Boolean);
Var
   RowChild : Integer;
   sFld : String;
   ChildNode,NewNode : TTreeNode;
begin
  inherited;
     If Node.HasChildren then begin
        Node.DeleteChildren;
        trTVChartAcc.Active := True;
        qrTVChartAcc.Close;   qrTVChartAcc.SQL.Clear;
        sFld := Copy(Node.Text,0,8);

       В данном случае нет необходимости хранить ID узла в каком то дополнительном признаке. Достаточно из текста узла взять первые 8 символов, что и выполняется оператором Copy.

        qrTVChartAcc.SQL.Add('Select * from ChartAcc');
        If cbxTreeSel.ItemIndex=1 then 
           qrTVChartAcc.SQL.Add('where AccParnt='''+sFld
                               +''' order by AccOrder');
        If cbxTreeSel.ItemIndex=2 then
           qrTVChartAcc.SQL.Add('where AccBalns='''+sFld
                               +''' order by AccBlOrd');
        If cbxTreeSel.ItemIndex=3 then 
           qrTVChartAcc.SQL.Add('where AccProft='''+sFld
                               +''' order by AccPfOrd');
        qrTVChartAcc.Open;     qrTVChartAcc.First;
        While not qrTVChartAcc.EOF do
          begin
             RowChild := 0;
             sFld := qrTVChartAcc.FieldValues['AccntNum'];
             sFld := acText8(sFld);
             sFld := sFld+' - '+qrTVChartAcc.FieldValues['AccTitle'];
             NewNode := tvChartAcc.Items.AddChild(Node,sFld);
             If cbxTreeSel.ItemIndex=1 then 
                RowChild := qrTVChartAcc.FieldValues['AccCount'];
             If cbxTreeSel.ItemIndex=2 then 
                RowChild := qrTVChartAcc.FieldValues['AccBlCnt'];
             If cbxTreeSel.ItemIndex=3 then 
                RowChild := qrTVChartAcc.FieldValues['AccPfCnt'];
             If RowChild>0 then 
             ChildNode:=tvChartAcc.Items.AddChild(NewNode,IntToStr(RowChild));
             qrTVChartAcc.Next;
          end; // While not qrTVChartAcc.EOF do
           tvChartAcc.Items.EndUpdate;
           trTVChartAcc.Active := False;
     end;// Node.HasChildren
     tvChartAcc.Update;
end;

       Остальная часть процедуры не отличается от процедуры TreeDatasetOpen.
       Результат можно посмотреть на рисунке:


Рис.3-12 План счетов - раскрытое дерево

       Для получения описания счета используется событие onChange, по которому изменяется запрос.......

procedure TForm1.tvChartAccChange(Sender: TObject; Node: TTreeNode);
begin
  qrNodeAccnt.Close;
  qrNodeAccnt.Params.ParamValues['SelAccnt'] := Copy(Node.Text,0,8);
  qrNodeAccnt.Open;
end;

Резюме

       Описанный случай интересен как идея. На самом деле здесь имеются проблемы, выходящие за рамки программирования.
       1) По мнению МинФина дебиторская задолженность может быть только у покупателей и заказчиков, а кредиторская задолженность только у поставщиков и подрядчиков. На самом деле это не так. Как у покупателей, так и у поставщиков может быть и кредиторская, и дебиторская задолженность. А это значит, что счет будет входит в две ветки в зависимости от знака суммы на нем, что сделать довольно проблематично.
       2) Любое Советско/Российское законодательство включает обходную лазейку в виде слов "Прочие...". Бухгалтерская отчетность не исключение, и имеет ряд строк, которые ни с каким счетом не связаны, а заполняются на усмотрение конкретного бухгалтера или налогового инспектора.
       3) И, наконец, главная проблема состоит в том, что сам "План счетов" меняется в среднем раз в 3 года, а формы отчетности могут меняться раз в квартал — ну нужно нашему МинФину показать, что и он что-то делает. Но бухгалтерский учет вешь непрерывная. Изменив связи по отчетности в соответствии с последними инструкциями МинФина, одновременно уничтожаются предыдущие формы отчетности. Следовательно бухгалтер лишается возможности восстановить утерянную отчетность по старым формам.
       Даже простые изменения по плану счетов создают громадные проблемы. Достаточно вспомнить выкрутасы со счетом 77 — старый вариант получал другой номер, а новый с тем же номером получал новое значение. Отличить как учитывать запись в журнале операций до изменений и после изменений невозможно.
       Однако, сам принцип суммирования полезен и может применятся не только в плане счетов, но и в других деревьях. Например, если получить сводную ведомость по товару, то можно получить обороты по группам товаров, в зависимости от выбранного дерева — внутренний код, "ТН ВЭД", "Каталог фирмы-производителя". Регулируя глубину дерева для суммирования, получаем готовый инструмент для анализа производственной деятельности.
       Проблема только в том, что при разработке классификации (структуры дерева) необходимо учитывать не только подчиненность узлов, но и уровень классификации. Например, каталог деталей автомобилей "ВАЗ" имеет глубину 3 уровня, а каталог деталей автомобилей "ГАЗ" имеет глубину 27 уровней. Соответственно, если попытаться сделать анализ какие узлы продаются больше, а какие меньше для всех автомобилей, то результат будет неверный. Следовательно необходимо переработать структуру каталогов "ГАЗ", уменьшив ее до 3 уровней, либо увеличить глубину каталогов "ВАЗ". В любом случае необходимо приводить структуры однотипных товаров выпускаемых разными производствами к некоторому единому виду. Руководство отдела цен НАМИ этого никак понять не могло.

© 01.07.2007, Архангельский А.Г.

<<Пред. Оглавление
Начало раздела
Главная страница
След.>>




Дата последнего изменения:
Thursday, 21-Aug-2014 09:10:44 MSK


Постоянный адрес статьи:
http://az-design.ru/Support/DataBase/002b3300.shtml