1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
CREATE PROCEDURE MigrateDataFromTORG
@ID char( 9 ),
@VID int
AS
SET NOCOUNT ON
IF @VID = 33
BEGIN
IF not exists(select ID from SC156 (NOLOCK) where ID = @ID)
BEGIN
insert into SC156 (ID, PARENTID, CODE, DESCR, ISFOLDER, ISMARK, VERSTAMP, SP137, SP10965, SP140, SP148, SP138, SP139, SP141, SP15933, SP142, SP143, SP40398)
select
_SC33.ID, -- ID
_SC33.PARENTID, -- ГРУППА
_SC33.CODE, -- КОД
_SC33.DESCR, -- НАИМЕНОВАНИЕ
_SC33.ISFOLDER, -- фл ПАПКИ
_SC33.ISMARK, -- фл ПОМЕТКИ НА УДАЛЕНИЕ
_SC33.VERSTAMP, -- ???
' CU ', -- ТИП ТОВАРА (ПО УМОЛЧАНИЮ - СОБСТВЕННЫЙ)
CASE WHEN _SC33.SP689 = ' J2 ' THEN ' CG ' -- ТОВАР
WHEN _SC33.SP689 = ' J4 ' THEN ' CI ' -- УСЛУГА
WHEN _SC33.ISFOLDER = 1 THEN ' 0 ' END, -- ЕСЛИ ГРУППА - ТОГДА НЕ ВЫСТАВЛЯЕМ
CASE WHEN _SC131.SP3748 = ' UH ' THEN ' 1 ' -- МЕТР
WHEN _SC131.SP3748 = ' 3 ' THEN ' 7 ' -- КИЛОГРАММ
WHEN _SC131.SP3748 = ' 1 ' THEN ' G ' -- ШТУКА
WHEN _SC131.SP3748 = ' V110 ' THEN ' I ' -- КОМПЛЕКТ
WHEN _SC131.SP3748 = ' UK ' THEN ' E ' -- РУЛОН
WHEN _SC131.SP3748 = ' V410 ' THEN ' 8 ' -- ТОННА
WHEN _SC131.SP3748 = ' UI ' THEN ' F ' -- УПАКОВКА
WHEN _SC33.ISFOLDER = 1 THEN ' 0 ' END, -- ЕСЛИ ГРУППА - ТОГДА НЕ ВЫСТАВЛЯЕМ
ISNULL(_SC5241.DESCR,''), -- СТРАНА ПРОИСХОЖДЕНИЯ
SP3024, -- ПОЛНОЕ НАИМЕНОВАНИЕ
' 0 ', 0 , 0 , 0 ,' 1 ', 0
from
DB_TEST..SC33 _SC33 (NOLOCK) LEFT OUTER JOIN DB_TEST..SC5241 _SC5241 (NOLOCK) ON _SC33.SP4091 = _SC5241.ID
LEFT OUTER JOIN DB_TEST..SC131 _SC131 (NOLOCK) ON _SC33.SP3753 = _SC131.ID
where
_SC33.ID = @ID
insert into _1SCONST(OBJID, ID, DATE, VALUE, ACTNO, LINENO_, TVALUE, TIME, DOCID) -- УСТАНАВЛИВАЕМ СТАВКУ НДС
select
__1SCONST.OBJID, 144 , DATE,
CASE WHEN VALUE = ' L9 ' THEN ' 3 ' -- без НДС
WHEN VALUE = ' LB ' THEN ' 2 ' -- НДС 10%
WHEN VALUE = ' 4WB ' THEN ' 1 ' -- НДС 18%
WHEN VALUE = ' LA ' THEN ' 0 ' -- НДС 20% (а такое есть?)
WHEN ISNULL(VALUE, 0 ) = 0 THEN ' 3 ' END, -- Если не установлено, тогда без НДС
ACTNO, LINENO_, TVALUE, 0 , ' 0 '
from
DB_TEST.._1SCONST __1SCONST (NOLOCK)
where
OBJID = @ID and ID = 599
END
ELSE
BEGIN
update SC156 SET
SC156.PARENTID = _SC33.PARENTID, -- ГРУППА
SC156.CODE = _SC33.CODE, -- КОД
SC156.DESCR = _SC33.DESCR, -- НАИМЕНОВАНИЕ
SC156.ISMARK = _SC33.ISMARK, -- ПОМЕТКА УДАЛЕНИЯ
SP10965 = CASE WHEN _SC33.SP689 = ' J2 ' THEN ' CG ' -- ТОВАР
WHEN _SC33.SP689 = ' J4 ' THEN ' CI ' -- УСЛУГА
WHEN _SC33.ISFOLDER = 1 THEN ' 0 ' END, -- ЕСЛИ ГРУППА - ТОГДА НЕ ВЫСТАВЛЯЕМ
SC156.SP140 = CASE WHEN _SC131.SP3748 = ' UH ' THEN ' 1 ' -- МЕТР
WHEN _SC131.SP3748 = ' 3 ' THEN ' 7 ' -- КИЛОГРАММ
WHEN _SC131.SP3748 = ' 1 ' THEN ' G ' -- ШТУКА
WHEN _SC131.SP3748 = ' V110 ' THEN ' I ' -- КОМПЛЕКТ
WHEN _SC131.SP3748 = ' UK ' THEN ' E ' -- РУЛОН
WHEN _SC131.SP3748 = ' V410 ' THEN ' 8 ' -- ТОННА
WHEN _SC131.SP3748 = ' UI ' THEN ' F ' -- УПАКОВКА
WHEN _SC33.ISFOLDER = 1 THEN ' 0 ' END, -- ЕСЛИ ГРУППА - ТОГДА НЕ ВЫСТАВЛЯЕМ
SC156.SP148 = ISNULL(_SC5241.DESCR,''), -- СТРАНА ПРОИСХОЖДЕНИЯ
SC156.SP138 = _SC33.SP3024 -- ПОЛНОЕ НАИМЕНОВАНИЕ
from
DB_TEST..SC33 _SC33 (NOLOCK) LEFT OUTER JOIN DB_TEST..SC5241 _SC5241 (NOLOCK) ON _SC33.SP4091 = _SC5241.ID
LEFT OUTER JOIN DB_TEST..SC131 _SC131 (NOLOCK) ON _SC33.SP3753 = _SC131.ID
where
SC156.ID = _SC33.ID AND _SC33.ID=@ID
/*delete from _1SCONST where OBJID = @ID and ID = 144 -- ОЧИЩАЕМ СТАВКУ НДС
insert into _1SCONST(OBJID, ID, DATE, VALUE, ACTNO, LINENO_, TVALUE, TIME, DOCID) -- УСТАНАВЛИВАЕМ СТАВКУ НДС
select
__1SCONST.OBJID, 144, DATE,
CASE WHEN VALUE = ' L9 ' THEN ' 3 ' -- без НДС
WHEN VALUE = ' LB ' THEN ' 2 ' -- НДС 10%
WHEN VALUE = ' 4WB ' THEN ' 1 ' -- НДС 18%
WHEN VALUE = ' LA ' THEN ' 0 ' -- НДС 20% (а такое есть?)
WHEN ISNULL(VALUE,0) = 0 THEN ' 3 ' END, -- Если не установлено, тогда без НДС
ACTNO, LINENO_, TVALUE, 0, ' 0 '
from
TEST.._1SCONST __1SCONST (NOLOCK)
where
OBJID = @ID and ID = 599*/
END
END
ELSE IF @VID = 46
BEGIN
IF not exists(select ID from SC133 (NOLOCK) where ID = @ID)
BEGIN
insert into SC133 (ID, PARENTID, CODE, DESCR, ISFOLDER, ISMARK, VERSTAMP, SP121, SP123, SP124, SP125, SP126, SP127, SP128, SP129, SP131, SP37027, SP122, SP130)
select
_SC46.ID, -- ID
_SC46.PARENTID, -- ГРУППА
_SC46.CODE, -- КОД
_SC46.DESCR, -- НАИМЕНОВАНИЕ
_SC46.ISFOLDER, -- фл ПАПКИ
_SC46.ISMARK, -- ПОМЕТКА УДАЛЕНИЯ
_SC46.VERSTAMP,
CASE WHEN SP2884 = ' 14D ' THEN ' C4 ' -- ОРГАНИЗАЦИЯ
WHEN SP2884 = ' 14E ' THEN ' C5 ' -- ЧАСТНОЕ ЛИЦО
WHEN SP2884 = ' 5MY ' THEN ' BNZ ' -- ФИЛИАЛ
ELSE ' C4 ' END,
UrAdress, --SP3145, -- ЮРИДИЧЕСКИЙ АДРЕС
PochAdress, --SP50, -- ПОЧТОВЫЙ АДРЕС
'', -- ТЕЛЕФОНЫ
ltrim(rtrim(SP56)) + '\' + KPP, --SP6195), -- ИНН\КПП
'', '', '', ' 0 ', ' 0 ',
FullName,--SP48, -- ПОЛНОЕ НАИМЕНОВАНИЕ
SP3148
from
DB_TEST..SC46 _SC46 (NOLOCK),
(select
ltrim(rtrim(VALUE)) KPP
from
DB_TEST.._1SCONST (NOLOCK)
where
DATE = (select max(DATE) from DB_TEST.._1SCONST (NOLOCK) where ID = 6195 and OBJID = @ID)
and
ROW_ID = (select max(ROW_ID) from DB_TEST.._1SCONST (NOLOCK) where ID = 6195 and OBJID = @ID)
and
ID = 6195
and
OBJID = @ID
) s1,
(select
ltrim(rtrim(VALUE)) UrAdress
from
DB_TEST.._1SCONST (NOLOCK)
where
DATE = (select max(DATE) from DB_TEST.._1SCONST (NOLOCK) where ID = 3145 and OBJID = @ID)
and
ROW_ID = (select max(ROW_ID) from DB_TEST.._1SCONST (NOLOCK) where ID = 3145 and OBJID = @ID)
and
ID = 3145
and
OBJID = @ID
) s2,
(select
ltrim(rtrim(VALUE)) PochAdress
from
DB_TEST.._1SCONST (NOLOCK)
where
DATE = (select max(DATE) from DB_TEST.._1SCONST (NOLOCK) where ID = 50 and OBJID = @ID)
and
ROW_ID = (select max(ROW_ID) from DB_TEST.._1SCONST (NOLOCK) where ID = 50 and OBJID = @ID)
and
ID = 50
and
OBJID = @ID
) s3,
(select
ltrim(rtrim(VALUE)) FullName
from
DB_TEST.._1SCONST (NOLOCK)
where
DATE = (select max(DATE) from DB_TEST.._1SCONST (NOLOCK) where ID = 48 and OBJID = @ID)
and
ROW_ID = (select max(ROW_ID) from DB_TEST.._1SCONST (NOLOCK) where ID = 48 and OBJID = @ID)
and
ID = 48
and
OBJID = @ID
) s4
where
ID = @ID
END
ELSE
BEGIN
update SC133 SET
SC133.PARENTID = _SC46.PARENTID, -- ГРУППА
SC133.CODE = _SC46.CODE, -- КОД
SC133.DESCR = _SC46.DESCR, -- НАИМЕНОВАНИЕ
SC133.ISMARK = _SC46.ISMARK, -- ПОМЕТКА УДАЛЕНИЯ
SC133.VERSTAMP = _SC46.VERSTAMP,
SC133.SP121 = CASE WHEN SP2884 = ' 14D ' THEN ' C4 ' -- ОРГАНИЗАЦИЯ
WHEN SP2884 = ' 14E ' THEN ' C5 ' -- ЧАСТНОЕ ЛИЦО
WHEN SP2884 = ' 5MY ' THEN ' BNZ '
ELSE ' C4 ' END, -- ФИЛИАЛ
SC133.SP123 = UrAdress,--_SC46.SP3145, -- ЮРИДИЧЕСКИЙ АДРЕС
SC133.SP124 = PochAdress,--_SC46.SP50, -- ПОЧТОВЫЙ АДРЕС
SC133.SP126 = ltrim(rtrim(_SC46.SP56)) + '\' + KPP,--_SC46.SP6195), -- ИНН\КПП
SC133.SP122 = FullName--_SC46.SP48 -- ПОЛНОЕ НАИМЕНОВАНИЕ
from
DB_TEST..SC46 _SC46 (NOLOCK),
(select
ltrim(rtrim(VALUE)) KPP
from
DB_TEST.._1SCONST (NOLOCK)
where
DATE = (select max(DATE) from DB_TEST.._1SCONST (NOLOCK) where ID = 6195 and OBJID = @ID)
and
ROW_ID = (select max(ROW_ID) from DB_TEST.._1SCONST (NOLOCK) where ID = 6195 and OBJID = @ID)
and
ID = 6195
and
OBJID = @ID
) s1,
(select
ltrim(rtrim(VALUE)) UrAdress
from
DB_TEST.._1SCONST (NOLOCK)
where
DATE = (select max(DATE) from DB_TEST.._1SCONST (NOLOCK) where ID = 3145 and OBJID = @ID)
and
ROW_ID = (select max(ROW_ID) from DB_TEST.._1SCONST (NOLOCK) where ID = 3145 and OBJID = @ID)
and
ID = 3145
and
OBJID = @ID
) s2,
(select
ltrim(rtrim(VALUE)) PochAdress
from
DB_TEST.._1SCONST (NOLOCK)
where
DATE = (select max(DATE) from DB_TEST.._1SCONST (NOLOCK) where ID = 50 and OBJID = @ID)
and
ROW_ID = (select max(ROW_ID) from DB_TEST.._1SCONST (NOLOCK) where ID = 50 and OBJID = @ID)
and
ID = 50
and
OBJID = @ID
) s3,
(select
ltrim(rtrim(VALUE)) FullName
from
DB_TEST.._1SCONST (NOLOCK)
where
DATE = (select max(DATE) from DB_TEST.._1SCONST (NOLOCK) where ID = 48 and OBJID = @ID)
and
ROW_ID = (select max(ROW_ID) from DB_TEST.._1SCONST (NOLOCK) where ID = 48 and OBJID = @ID)
and
ID = 48
and
OBJID = @ID
) s4
where
SC133.ID = _SC46.ID AND _SC46.ID = @ID
END
END
ELSE IF @VID = 3760
BEGIN
IF not exists(select ID from SC10951 (NOLOCK) where ID = @ID)
BEGIN
insert into SC10951 (ID, CODE, DESCR, ISFOLDER, ISMARK, VERSTAMP, PARENTID, SP10954, SP27275)
select
ID, -- ID
SP6212, -- КОД
DESCR, -- НАИМЕНОВАНИЕ
ISFOLDER, -- фл ПАПКИ
ISMARK, -- ПОМЕТКА УДАЛЕНИЯ
VERSTAMP, -- ???
' 0 ', ' 0 ', ' 0 '
from
DB_TEST..SC3760
where
ID = @ID
END
ELSE
BEGIN
update SC10951 SET
SC10951.CODE = _SC3760.SP6212, -- КОД
SC10951.DESCR = _SC3760.DESCR, -- НАИМЕНОВАНИЕ
SC10951.ISMARK = _SC3760.ISMARK -- ПОМЕТКА УДАЛЕНИЯ
from
DB_TEST..SC3760 _SC3760
where
SC10951.ID = _SC3760.ID and _SC3760.ID = @ID
END
END
