[ОТВЕТИТЬ]
Опции темы
16.10.2007 16:32  
Pyatak
Авось кому еще пригодится или поможет.

Итак, стояла задача получить данные для заполнения декларации о розничном обороте алкогольной продукции по форме, принятой на территории Санкт-Петербурга.

Декларация имеет следующие поля:
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 - возврат от начеления

Надеюсь, не зря я всё это расписывал и кому-то пригодится, если что пишите.
 
"Спасибо" Pyatak от:
 
Опции темы



Часовой пояс GMT +3, время: 13:32.

Все в прочитанное - Календарь - RSS - - Карта - Вверх 👫 Яндекс.Метрика
Форум сделан на основе vBulletin®
Copyright ©2000 - 2016, Jelsoft Enterprises Ltd. Перевод: zCarot и OlegON
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.