Авось кому еще пригодится или поможет.
Итак, стояла задача получить данные для заполнения декларации о розничном обороте алкогольной продукции по форме, принятой на территории Санкт-Петербурга.
Декларация имеет следующие поля:
1) Вид алкогольной продукции
будем использовать наименование ветки классификатора, к которой принадлежит продукция
2) Код вида алкогольной продукции
хранится в дополнительных характеристиках товара (ид. характеристики TypeCode (Тип: Целое))
3) Наименование алкогольной продукции
используем короткое название из СМ, приведенное к виду соответствующему требованиям декларации
4) Субъект РФ, в котором произведена алкогольная продукция (или страна происхождения, для импортной алкогольной продукции)
Всю информацию о производителе храним в таблице контрагентов (собранных в отдельную ветку классификатора контрагентов). При этом привязку производителя к карточке осуществляем указанием идентификатора контрагента-производителя в дополнительной характеристике товара с ид. ProducerCode (Тип: Целое).
Субъект РФ храним в дополнительной характеристике контрагентов с ид. Region (Тип: Строчка)
5) Емкость потребительской тары, л
берем из дополнительных характеристик товара с ид. Volume (Тип: Число)
6) Содержание безводного спирта, %
берем из дополнительных характеристик товара с ид. Spirt (Тип: Число)
7) Наименование производителя
см. пункт 4
8) ИНН производителя
см. пункт 4
9) Остаток алкогольной продукции на начало периода, дкл
10) Наименование поставщика
поставщиков вычисляем путем исследования приходных/расходных накладных за нужный период
11) ИНН поставщика
12) Серия и номер лицензии поставщика, срок действия
берем из дополнительной характеристики контрагентов с ид. License (Тип: Строчка)
13) Адрес отгрузки
берем из поля "физический адрес контрагента"
14) Код вида деятельности поставщика
берем из дополнительной характеристики контрагентов с ид. ProdCode (Тип: Число)
15) Поступило алкогольной продукции за отчетный период, дкл
16) Возвращено алкогольной продукции за отчетный период, дкл
17) Списано алкогольной продукции за отчетный период, дкл
18) Реализовано населению алкогольной продукции за отчетный период, дкл
19) возврат от населения алкогольной продукции за отчетный период, дкл
20) Остаток алкогольной продукции на конец отчетного периода, дкл
Выборку товаров в отчет осуществляем по признаку "Акцизный товар". При этом из отчета необходимо исключить товар, по которому небыло движения за указанный период
и нулевой остаток на конец периода.
Потерянный товар (инвентаризация недостачи) приплюсовываем к графе "Списано..."
Найденный товар (инвентаризация излишков) отнимаем от графы "Списано...", т.е. как будь-то бы мы ничего не находили, а просто меньше списали. Если при этом в графе "Списано..." получится отрицательный результат, то графу "Списано..." обнуляем, а весь отрицательный результат прибавляем к графе "Продано...", тем самым уменьшая продажу. Может это не совсем корректно, но как иначе объяснить налоговым органам, что мы нашли алкогольную продукцию, которую вероятно потеряли в прошлом отчетном периоде и провели ее по графе "Списано..."? Проще объяснить тем, что мы на это кол-во меньше продали. Если же и графа "Продано..." получится отрицательной, то с таким товаром надо уже разбираться индивидуально.
В запрос так же добавляем две дополнительные колонки, которые нужны при первоначальной обработке данных и удаляемые в окончательном варианте декларации. Это "Артикул", вставляем как самую первую колонку. И "Регион производителя", вставляем как самую последнюю колонку; так как данная колонка заполняется только для отечественных производителей, то сделав по ней сортировку легко отделить иностранных.
И так, вот сам запрос:
Код:
1 SELECT c.article as "Артикул",
2 c.treename as "Вид",
3 tcode.propval as "Код вида",
4 c.name as "Наименование",
5 DECODE(UPPER(c.country),'РОССИЯ',reg.propval,c.country) as "Страна происхождения",
6 vol.propval as "Емкость",
7 spirt.propval as "Содержание спирта",
8 ci.name as "Производитель",
9 ci.inn as "ИНН производителя",
10 ((ost.quantity-mov1.quantity)*TO_NUMBER(NVL(vol.propval,'0'),'999.999')/10) as "Остаток на начало, дкл",
11 ci2.name as "Наименование поставщика",
12 ci2.inn as "ИНН поставщика",
13 lic.propval as "Серия и номер лицензии",
14 ci2.faddress as "Адрес отгрузки",
15 pcd.propval as "Код вида деятельности",
16 prih.quantity*TO_NUMBER(NVL(vol.propval,'0'),'999.999')/10 as "Поступило, дкл",
17 vozv.quantity*TO_NUMBER(NVL(vol.propval,'0'),'999.999')/10 as "Возвращено, дкл",
18 DECODE(SIGN(NVL(spis.quantity,0)-NVL(inv.quantity,0)),-1,0,(NVL(spis.quantity,0)-NVL(inv.quantity,0)))*TO_NUMBER(NVL(vol.propval,'0'),'999.999')/10 as "Списано, дкл",
19 DECODE(SIGN(NVL(spis.quantity,0)-NVL(inv.quantity,0)),-1,prod.quantity+(NVL(spis.quantity,0)-NVL(inv.quantity,0)),prod.quantity)*TO_NUMBER(NVL(vol.propval,'0'),'999.999')/10 as "Реализовано, дкл",
20 vprod.quantity*TO_NUMBER(NVL(vol.propval,'0'),'999.999')/10 as "Возврат от населения, дкл",
21 (ost.quantity-mov2.quantity)*TO_NUMBER(NVL(vol.propval,'0'),'999.999')/10 as "Остаток на конец, дкл",
22 reg.propval as "Регион производителя"
23
24 FROM supermag.smcardproperties vol,
25 supermag.smcardproperties spirt,
26 supermag.smcardproperties tcode,
27 supermag.smcardproperties prcode,
28 supermag.smclientinfo ci,
29 supermag.smclientinfo ci2,
30 supermag.smclientproperties lic,
31 supermag.smclientproperties pcd,
32 supermag.smclientproperties reg,
33 supermag.smgoods ost,
34 (
35 SELECT c.article,
36 c.shortname as name,
37 cc.name as treename,
38 cc.tree as tree,
39 c.country,
40 NVL(cli.clientindex,MAX(d.clientindex)) as clientindex
41 FROM supermag.smcard c,
42 supermag.sacardclass cc,
43 supermag.smspec dc,
44 supermag.smdocuments d,
45 (
46 SELECT dc.article, d.clientindex
47 FROM supermag.smspec dc,
48 supermag.smdocuments d
49 WHERE d.id=dc.docid
50 AND d.doctype=dc.doctype
51 AND d.docstate=3
52 AND d.opcode IN (0,2)
53 AND (d.locationto=(%storeloc%) OR d.locationfrom=(%storeloc%))
54 AND d.doctype IN ('WI','WO')
55 AND TO_DATE(d.createdat) BETWEEN ('%startdate%') AND ('%enddate%')
56 ) cli
57 WHERE c.idclass=cc.id
58 AND c.article=dc.article
59 AND d.doctype=dc.doctype
60 AND d.id=dc.docid
61 AND d.docstate=3
62 AND (d.locationto=(%storeloc%) OR d.locationfrom=(%storeloc%))
63 AND TO_DATE(d.createdat) <= ('%enddate%')
64 AND c.accepted = 1
65 AND BITAND(c.flags,128)=128
66 AND c.article=cli.article(+)
67 GROUP BY c.article,c.shortname,cc.name,cc.tree,c.country,cli.clientindex
68 ) c,
69 (
70 SELECT c.article,
71 (NVL(pr.quantity,0)-NVL(rs.quantity,0)) as quantity
72 FROM supermag.smcard c,
73 (
74 SELECT dc.article,
75 SUM(dc.quantity) as quantity
76 FROM supermag.smdocuments d,
77 supermag.smspec dc
78 WHERE d.doctype=dc.doctype
79 AND d.id=dc.docid
80 AND d.docstate=3
81 AND TO_DATE(d.createdat) >= ('%startdate%')
82 AND d.locationto = (%storeloc%)
83 GROUP BY dc.article
84 ) pr,
85 (
86 SELECT dc.article,
87 SUM(dc.quantity) as quantity
88 FROM supermag.smdocuments d,
89 supermag.smspec dc
90 WHERE d.doctype=dc.doctype
91 AND d.id=dc.docid
92 AND d.docstate=3
93 AND TO_DATE(d.createdat) >= ('%startdate%')
94 AND d.locationfrom = (%storeloc%)
95 GROUP BY dc.article
96 ) rs
97 WHERE c.article=pr.article(+)
98 AND c.article=rs.article(+)
99 ) mov1,
100 (
101 SELECT c.article,
102 (NVL(pr.quantity,0)-NVL(rs.quantity,0)) as quantity
103 FROM supermag.smcard c,
104 (
105 SELECT dc.article,
106 SUM(dc.quantity) as quantity
107 FROM supermag.smdocuments d,
108 supermag.smspec dc
109 WHERE d.doctype=dc.doctype
110 AND d.id=dc.docid
111 AND d.docstate=3
112 AND TO_DATE(d.createdat) > ('%enddate%')
113 AND d.locationto = (%storeloc%)
114 GROUP BY dc.article
115 ) pr,
116 (
117 SELECT dc.article,
118 SUM(dc.quantity) as quantity
119 FROM supermag.smdocuments d,
120 supermag.smspec dc
121 WHERE d.doctype=dc.doctype
122 AND d.id=dc.docid
123 AND d.docstate=3
124 AND TO_DATE(d.createdat) > ('%enddate%')
125 AND d.locationfrom = (%storeloc%)
126 GROUP BY dc.article
127 ) rs
128 WHERE c.article=pr.article(+)
129 AND c.article=rs.article(+)
130 ) mov2,
131 (
132 SELECT dc.article,
133 d.clientindex,
134 SUM(dc.quantity) as quantity
135 FROM supermag.smdocuments d,
136 supermag.smspec dc
137 WHERE d.doctype=dc.doctype
138 AND d.id=dc.docid
139 AND d.doctype='WI'
140 AND d.docstate=3
141 AND d.opcode<>9
142 AND TO_DATE(d.createdat) BETWEEN ('%startdate%') AND ('%enddate%')
143 AND d.locationto = (%storeloc%)
144 GROUP BY dc.article, d.clientindex
145 ) prih,
146 (
147 SELECT dc.article,
148 d.clientindex,
149 SUM(dc.quantity) as quantity
150 FROM supermag.smdocuments d,
151 supermag.smspec dc
152 WHERE d.doctype=dc.doctype
153 AND d.id=dc.docid
154 AND d.doctype='WO'
155 AND d.opcode=2
156 AND d.docstate=3
157 AND TO_DATE(d.createdat) BETWEEN ('%startdate%') AND ('%enddate%')
158 AND d.locationfrom = (%storeloc%)
159 GROUP BY dc.article, d.clientindex
160 ) vozv,
161 (
162 SELECT dc.article,
163 SUM(dc.quantity) as quantity
164 FROM supermag.smdocuments d,
165 supermag.smspec dc
166 WHERE d.doctype=dc.doctype
167 AND d.id=dc.docid
168 AND d.doctype='WI'
169 AND d.docstate=3
170 AND d.opcode=9
171 AND TO_DATE(d.createdat) BETWEEN ('%startdate%') AND ('%enddate%')
172 AND d.locationto = (%storeloc%)
173 GROUP BY dc.article, d.clientindex
174 ) inv,
175 (
176 SELECT dc.article,
177 SUM(dc.quantity) as quantity
178 FROM supermag.smdocuments d,
179 supermag.smspec dc
180 WHERE d.doctype=dc.doctype
181 AND d.id=dc.docid
182 AND d.doctype='WO'
183 AND d.opcode IN (7,8)
184 AND d.docstate=3
185 AND TO_DATE(d.createdat) BETWEEN ('%startdate%') AND ('%enddate%')
186 AND d.locationfrom = (%storeloc%)
187 GROUP BY dc.article
188 ) spis,
189 (
190 SELECT dc.article,
191 SUM(dc.quantity) as quantity
192 FROM supermag.smdocuments d,
193 supermag.smspec dc
194 WHERE d.doctype=dc.doctype
195 AND d.id=dc.docid
196 AND d.doctype='CS'
197 AND d.docstate=3
198 AND TO_DATE(d.createdat) BETWEEN ('%startdate%') AND ('%enddate%')
199 AND d.locationfrom = (%storeloc%)
200 GROUP BY dc.article
201 ) prod,
202 (
203 SELECT dc.article,
204 SUM(dc.quantity) as quantity
205 FROM supermag.smdocuments d,
206 supermag.smspec dc
207 WHERE d.doctype=dc.doctype
208 AND d.id=dc.docid
209 AND d.doctype='CR'
210 AND d.docstate=3
211 AND TO_DATE(d.createdat) BETWEEN ('%startdate%') AND ('%enddate%')
212 AND d.locationto = (%storeloc%)
213 GROUP BY dc.article
214 ) vprod
215
216 WHERE c.article=vol.article(+)
217 AND 'Volume'=vol.propid(+)
218 AND c.article=spirt.article(+)
219 AND 'Spirt'=spirt.propid(+)
220 AND c.article=tcode.article(+)
221 AND 'TypeCode'=tcode.propid(+)
222 AND c.article=prcode.article(+)
223 AND 'ProducerCode'=prcode.propid(+)
224 AND prcode.propval=TO_CHAR(ci.id(+))
225 AND c.clientindex=ci2.id(+)
226 AND c.clientindex=lic.idclient(+)
227 AND 'License'=lic.propid(+)
228 AND c.clientindex=pcd.idclient(+)
229 AND 'ProdCode'=pcd.propid(+)
230 AND ci.id=reg.idclient(+)
231 AND 'Region'=reg.propid(+)
232 AND c.article=ost.article
233 AND ost.storeloc=(%storeloc%)
234 AND c.article=mov1.article
235 AND c.article=mov2.article
236 AND c.article=prih.article(+)
237 AND c.clientindex=prih.clientindex(+)
238 AND c.article=vozv.article(+)
239 AND c.clientindex=vozv.clientindex(+)
240 AND c.article=inv.article(+)
241 AND c.article=spis.article(+)
242 AND c.article=prod.article(+)
243 AND c.article=vprod.article(+)
244 AND ( (ost.quantity-mov1.quantity)<>0
245 OR (ost.quantity-mov2.quantity)<>0
246 OR prih.quantity<>0
247 OR vozv.quantity<>0
248 OR vprod.quantity<>0
249 OR spis.quantity<>0
250 )
251
252 order by c.tree, c.name Пояснения к запросу:
%startdate% - начало отчетного периода;
%enddate% - конец отчетного периода;
%storeloc% - код места хранения;
В строках 34-68 определяется базовый набор артикулов, которые необходимо включить в декларацию, причем подзапрос 45-56 служит для создания связи артикул-поставщик, отбирая тех поставщиков, движения по которым с данными артикулами имели место быть в указанном периоде. Надо так же не забыть, что в указанном периоде движения по артикулу небыло, но есть остаток, который тоже необходимо продекларировать, тогда в качестве поставщика принимаем такого, кто когда либо делал поставку и имеет максимальный код (строчка 40, 43-44, 58-63)
Подзапрос 69-99 вычисляет движение по артикулам от начала периода до текущего момента, с цель дальнейшего вычисления остатков на начало периода путем вычитания из текущих остатков.
Подзапрос 100-130 аналогичным образом вычисляет движение от конца периода до текущего момента, для дальнейшего определения остатка на конец периода аналогичным способом.
131-145 - определение поставок по поставщикам в периоде
146-160 - определение возвратов поставщикам в периоде
161-174 - "находки" товара (инвентаризация излишков)
175-188 - списания и потери (инвентаризация недостачи)
189-201 - продажи населению
202-214 - возврат от начеления
Надеюсь, не зря я всё это расписывал и кому-то пригодится, если что пишите.