Wiki source code of Пример исправления битых пар в СМ без перегенерации проводок
Last modified by Nefedenkova Viktoria on 2021/11/30 16:58
Show last authors
| author | version | line-number | content |
|---|---|---|---|
| 1 | При наличии явных проблем с сальдо нужно проверить наличие пар "сломанных" проводок, а именно: нужно открыть форму Тесты в меню Сервис или нажать Alt+F11 на клавиатуре. | ||
| 2 | |||
| 3 | [[image:01.png||height="20%" width="20%"]] | ||
| 4 | |||
| 5 | Затем выбрать Битые пары СМ и нажать на кнопку Выбранный тест. | ||
| 6 | |||
| 7 | [[image:1.png||height="70%" width="70%"]] | ||
| 8 | |||
| 9 | Результат выполнения выглядит так: | ||
| 10 | |||
| 11 | [[image:2.png||height="70%" width="70%"]] | ||
| 12 | |||
| 13 | В UNA-desktop проводки записываются так: sptype=1 - это развёрнутая с минусом КT часть из sptype=0, записанная в ДT, чтобы при необходимости, можно было считать остатки только по ДT. | ||
| 14 | |||
| 15 | Ниже приведён пример исправления битых пар в СМ без перегенерации проводок, то есть проверочный запрос по TMDB_CM, выявляющий нестыковки между оригинальной и дублирующей записью с TMDB_CM. ,,[[Файл в формате SQL>>attach:haruzupdee.sql||rel="__blank" title="Файл в формате SQL"]].,, | ||
| 16 | |||
| 17 | (% style="font-size: 14px; font-family: Consolas; background-color:#fbfdff" %) | ||
| 18 | select dt,ct, | ||
| 19 | sptype , sum (suma) | ||
| 20 | -- ct1,ctsc,ctdep,ctsc1,ctnrdoc,ctstrsc,ctnrcm,sumavalct,valutact,ctdata,ctcant1 | ||
| 21 | -- ,cant,suma,sumagaap,f,m,dtdiv,ctdiv | ||
| 22 | from tmdb_cm where | ||
| 23 | -- sptype=0 | ||
| 24 | -- and dt<9000 | ||
| 25 | -- and | ||
| 26 | nrdoc=404218 | ||
| 27 | group by dt,ct,sptype | ||
| 28 | |||
| 29 | |||
| 30 | select * --count(*) | ||
| 31 | from ( | ||
| 32 | (select nrdoc,cod,--sptype, | ||
| 33 | data,funct,isvalid,codfcdebaza,nrdocfcdebaza | ||
| 34 | ,ct,ct1,ctsc,ctdep,ctsc1,ctnrdoc,ctstrsc | ||
| 35 | ,ctnrcm,sumavalct,valutact,ctdata,ctcant1 | ||
| 36 | ,cant,suma,sumagaap | ||
| 37 | --,f,m,ctdiv | ||
| 38 | from tmdb_cm where nrdoc=333567 --404218 | ||
| 39 | -- and cod=4509799 | ||
| 40 | and sptype=0) | ||
| 41 | --union all --- | ||
| 42 | minus | ||
| 43 | ( select nrdoc,cod,--sptype, | ||
| 44 | data,funct,isvalid,codfcdebaza,nrdocfcdebaza | ||
| 45 | ,dt,dt1,dtsc,dtdep,dtsc1,dtnrdoc,dtstrsc | ||
| 46 | ,dtnrcm,-sumavaldt,valutadt,dtdata,-dtcant1 dtcant1 | ||
| 47 | ,-cant cant,-suma suma, -sumagaap sumagaap | ||
| 48 | --,f,m,dtdiv | ||
| 49 | from tmdb_cm where nrdoc=333567 --404218 | ||
| 50 | -- and cod=4509799 | ||
| 51 | and sptype=1) | ||
| 52 | ) | ||
| 53 | order by cod | ||
| 54 | |||
| 55 | |||
| 56 | select * from tmdb_cm where nrdoc=--404218 - | ||
| 57 | 333567 --404218 | ||
| 58 | and cod=4509799 | ||
| 59 | |||
| 60 | |||
| 61 | |||
| 62 | select * from tmdb_cm where sptype=1 and (nrdoc,cod) in ( | ||
| 63 | select nrdoc,cod --count(*) | ||
| 64 | from ( | ||
| 65 | (select nrdoc,cod,--sptype, | ||
| 66 | data,funct,isvalid,codfcdebaza,nrdocfcdebaza | ||
| 67 | ,ct,ct1,ctsc,ctdep,ctsc1,ctnrdoc,ctstrsc,ctnrcm,sumavalct,valutact,ctdata,ctcant1 | ||
| 68 | ,cant,suma,sumagaap | ||
| 69 | --,f,m,ctdiv | ||
| 70 | from tmdb_cm where data>='01.01.2008' --nrdoc=404218 | ||
| 71 | and dt<9000 | ||
| 72 | and sptype=0) | ||
| 73 | -- union all --- | ||
| 74 | minus | ||
| 75 | ( select nrdoc,cod,--sptype, | ||
| 76 | data,funct,isvalid,codfcdebaza,nrdocfcdebaza | ||
| 77 | ,dt,dt1,dtsc,dtdep,dtsc1,dtnrdoc,dtstrsc,dtnrcm,-sumavaldt,valutadt,dtdata,-dtcant1 dtcant1 | ||
| 78 | ,-cant cant,-suma suma, -sumagaap sumagaap | ||
| 79 | --,f,m,dtdiv | ||
| 80 | from tmdb_cm where data>='01.01.2008' -- nrdoc=404218 | ||
| 81 | and sptype=1) | ||
| 82 | ) | ||
| 83 | -- order by cod | ||
| 84 | ) | ||
| 85 | |||
| 86 | |||
| 87 | update tmdb_cm a set suma=(select -suma from tmdb_cm b where b.sptype=0 and a.nrdoc=b.nrdoc and a.cod=b.cod) | ||
| 88 | where sptype=1 and data>='01.01.2008' and (nrdoc,cod) in ( | ||
| 89 | select nrdoc,cod --count(*) | ||
| 90 | from ( | ||
| 91 | (select nrdoc,cod,--sptype, | ||
| 92 | data,funct,isvalid,codfcdebaza,nrdocfcdebaza | ||
| 93 | ,ct,ct1,ctsc,ctdep,ctsc1,ctnrdoc,ctstrsc,ctnrcm,sumavalct,valutact,ctdata,ctcant1 | ||
| 94 | ,cant,suma,sumagaap | ||
| 95 | --,f,m,ctdiv | ||
| 96 | from tmdb_cm where data>='01.01.2008' --nrdoc=404218 | ||
| 97 | and dt<9000 | ||
| 98 | and sptype=0) | ||
| 99 | -- union all --- | ||
| 100 | minus | ||
| 101 | ( select nrdoc,cod,--sptype, | ||
| 102 | data,funct,isvalid,codfcdebaza,nrdocfcdebaza | ||
| 103 | ,dt,dt1,dtsc,dtdep,dtsc1,dtnrdoc,dtstrsc,dtnrcm,-sumavaldt,valutadt,dtdata,-dtcant1 dtcant1 | ||
| 104 | ,-cant cant,-suma suma, -sumagaap sumagaap | ||
| 105 | --,f,m,dtdiv | ||
| 106 | from tmdb_cm where data>='01.01.2008' -- nrdoc=404218 | ||
| 107 | and sptype=1) | ||
| 108 | ) | ||
| 109 | -- order by cod | ||
| 110 | ) | ||
| 111 | |||
| 112 | update tmdb_cm a set dtnrdoc=(select ctnrdoc from tmdb_cm b where b.sptype=0 and a.nrdoc=b.nrdoc and a.cod=b.cod) | ||
| 113 | where sptype=1 and data>='01.01.2008' and (nrdoc,cod) in ( | ||
| 114 | select nrdoc,cod --count(*) | ||
| 115 | from ( | ||
| 116 | (select nrdoc,cod,--sptype, | ||
| 117 | data,funct,isvalid,codfcdebaza,nrdocfcdebaza | ||
| 118 | ,ct,ct1,ctsc,ctdep,ctsc1,ctnrdoc,ctstrsc,ctnrcm,sumavalct,valutact,ctdata,ctcant1 | ||
| 119 | ,cant,suma,sumagaap | ||
| 120 | --,f,m,ctdiv | ||
| 121 | from tmdb_cm where data>='01.01.2008' --nrdoc=404218 | ||
| 122 | and dt<9000 | ||
| 123 | and sptype=0) | ||
| 124 | -- union all --- | ||
| 125 | minus | ||
| 126 | ( select nrdoc,cod,--sptype, | ||
| 127 | data,funct,isvalid,codfcdebaza,nrdocfcdebaza | ||
| 128 | ,dt,dt1,dtsc,dtdep,dtsc1,dtnrdoc,dtstrsc,dtnrcm,-sumavaldt,valutadt,dtdata,-dtcant1 dtcant1 | ||
| 129 | ,-cant cant,-suma suma, -sumagaap sumagaap | ||
| 130 | --,f,m,dtdiv | ||
| 131 | from tmdb_cm where data>='01.01.2008' -- nrdoc=404218 | ||
| 132 | and sptype=1) | ||
| 133 | ) | ||
| 134 | -- order by cod | ||
| 135 | ) |