GO
ALTER TABLE [HM].[DW] ADD krajPochodzenia INT NULL;
ALTER TABLE [HM].[IT] ADD iddw INT NULL;
GO
IF OBJECT_ID('[HM].[hm_DW_WstawDostawe]', 'P') IS NOT NULL DROP PROCEDURE [HM].[hm_DW_WstawDostawe]
GO
CREATE PROCEDURE HM.hm_DW_WstawDostawe
@idpoz int,
@iddwo int,
@iddw int output
as
declare @return_status int
if @iddwo <> 0
begin
set @iddw = 0
select @iddw = DW.id
from DW join MZ on DW.idtw = MZ.idtw and DW.magazyn = MZ.magazyn
where MZ.id = @idpoz and DW.iddw = @iddwo
if @iddw <> 0
return 0
insert into DW select DWO.flag, 0, 1, DWO.kod, DWO.id, MG.id, MG.khid, NULL,
ISNULL((select MAX(numer) from DW where DW.idtw = MZ.idtw and DW.numer > 0), 16777215) + 1,
MZ.idtw, MZ.data, 0.0, 0.0, MZ.cena, 0.0, MZ.magazyn, 0.0, 0.0, 0.0, MG.bufor,NEWID(),NULL
from DW as DWO, MZ join MG on MZ.super = MG.id where DWO.id = @iddwo and MZ.id = @idpoz
end
else
begin
insert into DW select 0, 0, 1, case when MG.bufor = 0 then MG.kod else '' end, NULL, MG.id, MG.khid, NULL,
ISNULL((select MAX(numer) from DW where DW.idtw = MZ.idtw and DW.numer > 0), 16777215) + 1,
MZ.idtw, MZ.data, 0.0, 0.0, MZ.cena, 0.0, MZ.magazyn, 0.0, 0.0, 0.0, MG.bufor,NEWID(),NULL
from MZ join MG on MZ.super = MG.id where MZ.id = @idpoz
end
select @return_status = @@error, @iddw = SCOPE_IDENTITY()
if @return_status <> 0 return @return_status
GO
IF OBJECT_ID('[HM].[hm_PW_WstawPW]', 'P') IS NOT NULL DROP PROCEDURE [HM].[hm_PW_WstawPW]
GO
CREATE PROCEDURE HM.hm_PW_WstawPW
@idmz int,
@iddw int,
@iddwm int,
@ilosc float,
@wartosc float,
@kod varchar(100),
@numer int,
@zatwwart smallint,
@krajPochodzenia int
as
declare @iddwo int, @idpwo int, @idkoryg int, @idpw int
declare @return_status int
declare @iloscpw decimal(24,6), @wartoscpw decimal(24,2)
declare @iloscdosp decimal(24,6), @wartoscdosp decimal(24,2), @iloscdw decimal(24,6), @wartoscdw decimal(24,2), @iloscPz decimal(24,6)
declare @new smallint, @bufor smallint, @subtyp smallint, @typ smallint
declare @datao datetime
declare @cena decimal(24,6)
declare @przychod smallint
set @iloscpw = @ilosc
set @wartoscpw = @wartosc
set @cena = @wartosc / @ilosc
set @new = 0
select @bufor = MZ.bufor, @subtyp = MG.subtyp from MZ join MG on MZ.super = MG.id where MZ.id = @idmz
select @przychod = case when @subtyp in (74,131,75,132,81,82,83,89) then 1 else 0 end
if @przychod = 1 and @iddw = 0
begin
if @iddwm = 0
begin
select @kod = case when @kod <> '' then @kod else
case when TW.szablon <> '' then '' else
case when MG.bufor = 0 then MG.kod else '' end end end
from MZ join MG on MG.id = MZ.super join TW on TW.id = MZ.idtw where MZ.id = @idmz
insert into DW select @zatwwart, 0, 0, ISNULL(@kod, ''), NULL, MZ.super, MG.khid, NULL,
case when @numer <> 0 then @numer else ISNULL((select MAX(numer) from DW where DW.idtw = MZ.idtw and DW.numer > 0), 16777215) + 1 end,
MZ.idtw, MZ.data, @iloscpw, 0.0, @cena, 0.0, MZ.magazyn, 0.0, 0.0, 0.0, MZ.bufor,NEWID(),@krajPochodzenia
from MZ join MG on MG.id = MZ.super where MZ.id = @idmz
select @return_status = @@error, @iddw = SCOPE_IDENTITY()
if @return_status <> 0 return @return_status
set @new = 1
end
else
begin
select @iddwo = ISNULL(DW.iddw, DW.id) from DW where DW.id = @iddwm
select @iddw = DW.id
from DW join MZ on DW.idtw = MZ.idtw and DW.magazyn = MZ.magazyn
where MZ.id = @idmz and ISNULL(DW.iddw, DW.id) = @iddwo
if @iddw = 0
begin
insert into DW select DWO.flag, 0, 0, DWO.kod, DWO.id, MZ.super, NULL, NULL,
ISNULL((select MAX(numer) from DW where DW.idtw = MZ.idtw and DW.numer > 0), 16777215) + 1,
MZ.idtw, MZ.data, @iloscpw, 0.0, DWO.cena, 0.0, MZ.magazyn, 0.0, 0.0, 0.0, MZ.bufor,NEWID(),@krajPochodzenia
from MZ, DW as DWO where MZ.id = @idmz and DWO.id = @iddwo
select @return_status = @@error, @iddw = SCOPE_IDENTITY()
if @return_status <> 0 return @return_status
set @new = 1
end
end
if @iddw is NULL
return -1
end
if @iddw = 0
return -1
set @idkoryg = NULL
if @new = 0
begin
select @idkoryg = MIN(PW.id)
from MZ join MZ as MZK on MZ.superkoryg = MZK.superkoryg and MZ.lp = MZK.lp join PW on PW.typ = 37 and PW.idmg = MZK.id
where MZ.id = @idmz and PW.iddw = @iddw and MZ.idpozkoryg is not NULL
end
if @przychod = 1
begin
set @iloscpw = -@iloscpw
set @wartoscpw = -@wartoscpw
end
else
begin
if @bufor = 0 or @iloscpw > 0
begin
select @iloscdosp = iloscdosp, @wartoscdosp = wartoscdosp, @cena = cena
from DW where id = @iddw
if @iloscdosp = 0.0
set @wartoscpw = @iloscpw * @cena
else
set @wartoscpw = @iloscpw * @wartoscdosp / @iloscdosp
end
end
insert into PW select (case when @przychod = 1 then 0x10 else 0 end |
case when @new = 1 then 0x08 else 0 end |
case when (DW.flag & 1) <> 0 then 0x04 else 0 end),
MG.subtyp, case when MZ.bufor <> 0 then 39 else 37 end, MZ.super, MZ.id, DW.id, MZ.idtw, @iloscpw, @wartoscpw, 0.0, @idkoryg, MZ.data
from MZ join MG on MG.id = MZ.super, DW where MZ.id = @idmz and DW.id = @iddw
select @return_status = @@error, @idpw = SCOPE_IDENTITY()
if @return_status <> 0 return @return_status
if @bufor = 0
begin
select @iloscdosp = iloscdosp - @iloscpw, @wartoscdosp = wartoscdosp - @wartoscpw,
@iloscdw = stan - @iloscpw, @wartoscdw = wartoscst - @wartoscpw,
@iloscPz = iloscPz - case when @przychod = 1 then @iloscpw else 0.0 end
from DW where id = @iddw
if @iloscdosp < 0.0
return -4900
if @wartoscdosp < 0.0
return -4901
update DW set iloscdosp = @iloscdosp, wartoscdosp = @wartoscdosp,
stan = @iloscdw, wartoscst = @wartoscdw, iloscPz = @iloscPz,
typ = case when @iloscdw = 0.0 then 0 else 1 end
from DW where DW.id = @iddw
set @return_status = @@error
if @return_status <> 0 return @return_status
end
if @new = 0 and @subtyp in (82,83)
begin
set @datao = NULL
set @typ = NULL
select @datao = MIN(data), @typ = MIN(typ) from PW where typ = 37 and (flag & 0x10) <> 0 and iddw = @iddw
if @typ is NULL
select @datao = min(data), @typ = MIN(typ) from PW where typ = 39 and (flag & 0x10) <> 0 and iddw = @iddw
select @idpwo = MIN(id) from PW where typ = @typ and (flag & 0x10) <> 0 and data = @datao and iddw = @iddw
if @idpwo = @idpw
begin
update PW set flag = (flag | 8) where id = @idpw
set @return_status = @@error
if @return_status <> 0 return @return_status
update PW set flag = (flag & ~8) where id <> @idpw and iddw = @iddw and (flag & 0x10) <> 0
set @return_status = @@error
if @return_status <> 0 return @return_status
update DW set iddkpz = PW.iddkmg, data = PW.data, ilosc = -PW.ilosc from DW join PW on DW.id = PW.iddw where PW.id = @idpw
set @return_status = @@error
if @return_status <> 0 return @return_status
end
end
GO
IF OBJECT_ID('[HM].[hm_PW_ZatwierdzPowiazaniaPZ]', 'P') IS NOT NULL DROP PROCEDURE [HM].[hm_PW_ZatwierdzPowiazaniaPZ]
GO
CREATE PROCEDURE HM.hm_PW_ZatwierdzPowiazaniaPZ
@iddok int
as
set NOCOUNT ON
declare @return_status int
declare @tblDWB TABLE
(id int, idtw int)
declare @tblDWS TABLE
(id int primary key not NULL, idtw int, magazyn int, iloscdosp decimal(24,6), wartoscdosp decimal(24,2), ilosc decimal(24,6), wartosc decimal(24,2),
iloscPz decimal(24,6), data datetime)
declare @tblPWO TABLE
(iddw int primary key not NULL, idpwo int, data datetime)
declare @tblSMS TABLE
(id int primary key not NULL, idtw int, magazyn int, iloscdosp decimal(24,6), ilosc decimal(24,6), wartosc decimal(24,2))
update PW set flag = (PW.flag | 0x10), idmg = MZ.id, iddkmg = MG.id, subtyp = MG.subtyp, typ = 37, data = MZ.data
from PW join MZ on PW.typ = 39 and PW.idmg = MZ.id
join MG on MZ.super = MG.id
where MG.id = @iddok
set @return_status = @@error
if @return_status <> 0 return @return_status
insert @tblDWB
select DW.id, PWS.idtw
from DW join
(select distinct PW.iddw, MZ.idtw
from PW join MZ on PW.typ = 37 and PW.idmg = MZ.id where MZ.super = @iddok) as PWS on PWS.iddw = DW.id
where DW.bufor = 1
update PW set flag = (flag & ~8)
from PW join @tblDWB as DWB on PW.iddw = DWB.id where (PW.flag & 0x08) <> 0
set @return_status = @@error
if @return_status <> 0 return @return_status
if exists (select * from @tblDWB)
begin
update DW set bufor = 0, iddkpz = MG.id, data = MG.data, idtw = DWB.idtw, magazyn = MG.magazyn, idkh = MG.khid, kod = CASE WHEN '' = DW.kod THEN MG.kod ELSE DW.kod END
from DW join @tblDWB as DWB on DW.id = DWB.id, MG where MG.id = @iddok
set @return_status = @@error
if @return_status <> 0 return @return_status
end
insert @tblDWS
select distinct PW.iddw, NULL, NULL, 0.0, 0.0, 0.0, 0.0, 0.0, NULL
from PW join MZ on PW.typ = 37 and PW.idmg = MZ.id where MZ.super = @iddok
update @tblDWS
set iloscdosp = PWR.ilosc, wartoscdosp = PWR.wartosc, ilosc = PWS.ilosc, wartosc = PWS.wartosc, iloscPz = PWP.ilosc, data = PWP.data,
idtw = DW.idtw, magazyn = DW.magazyn
from @tblDWS as DWS join DW on DW.id = DWS.id
join
(select PW.iddw, -SUM(PW.ilosc) as ilosc, -SUM(PW.wartosc) as wartosc
from PW where PW.typ = 37 or PW.typ = 26 group by PW.iddw) as PWR on PWR.iddw = DWS.id
join
(select PW.iddw, -SUM(PW.ilosc) as ilosc, -SUM(PW.wartosc) as wartosc
from PW where PW.typ = 37 group by PW.iddw) as PWS on PWS.iddw = DWS.id
join
(select PW.iddw, -SUM(PW.ilosc) as ilosc, min(data) as data
from PW where PW.typ = 37 and (PW.flag & 0x10) <> 0 group by PW.iddw) as PWP on PWP.iddw = DWS.id
if exists (select * from @tblDWS where ilosc < 0.0)
return -4900
if exists (select * from @tblDWS where wartosc < 0.0)
return -4901
update DW set iloscdosp = DWS.iloscdosp, wartoscdosp = DWS.wartoscdosp,
stan = DWS.ilosc, wartoscst = DWS.wartosc, iloscPz = DWS.iloscPz,
typ = case when DWS.ilosc = 0.0 then 0 else 1 end
from DW join @tblDWS as DWS on DW.id = DWS.id
set @return_status = @@error
if @return_status <> 0 return @return_status
insert @tblPWO
select distinct DWS.id, 0, DWS.data
from @tblDWS as DWS
left join (select min(data) as data, iddw from PW where typ = 37 and (flag & 0x08) <> 0 group by iddw) as PWO on DWS.id = PWO.iddw
where PWO.data is NULL or DWS.data <> PWO.data
if exists (select * from @tblPWO)
begin
update @tblPWO set idpwo = PWDO.idpwo
from @tblPWO as PWO join
(select MIN(PW.id) as idpwo, PWO.iddw
from @tblPWO as PWO join PW on PWO.iddw = PW.iddw and PWO.data = PW.data
where (PW.flag & 0x10) <> 0 and PW.typ = 37 group by PWO.iddw) as PWDO on PWO.iddw = PWDO.iddw
update PW set flag = (flag | 8) where PW.id in (select idpwo from @tblPWO)
set @return_status = @@error
if @return_status <> 0 return @return_status
update PW set flag = (flag & ~8) from PW join @tblPWO as PWO on PW.iddw = PWO.iddw
where PW.id not in (select idpwo from @tblPWO) and (PW.flag & 0x10) <> 0 and (PW.flag & 0x08) <> 0
set @return_status = @@error
if @return_status <> 0 return @return_status
update DW set iddkpz = PW.iddkmg, data = PW.data, ilosc = -PW.ilosc, cena = case when cast(PW.ilosc as decimal(24,6)) = 0.0 then 0.0 else PW.wartosc/PW.ilosc end
from DW join PW on DW.id = PW.iddw where PW.id in (select idpwo from @tblPWO)
set @return_status = @@error
if @return_status <> 0 return @return_status
end
insert @tblSMS
select SM.id, SM.idtw, SM.magazyn, SUM(DW.iloscdosp), SUM(DW.stan), SUM(DW.wartoscst)
from SM WITH (NOLOCK, INDEX(UC_SM_IDTW_MAGAZYN)) join DW on SM.idtw = DW.idtw and SM.magazyn = DW.magazyn
join (select distinct DWS.idtw, DWS.magazyn from @tblDWS as DWS) as DWS on SM.idtw = DWS.idtw and SM.magazyn = DWS.magazyn
where DW.bufor = 0
group by SM.id, SM.idtw, SM.magazyn
if exists (select * from @tblSMS where ilosc < 0.0)
return -4700
if exists (select * from @tblSMS where wartosc < 0.0)
return -4702
update SM set stanHandl = SMS.iloscdosp, stan = SMS.ilosc, wartosc = SMS.wartosc
from SM join @tblSMS as SMS on SM.id = SMS.id
set @return_status = @@error
if @return_status <> 0 return @return_status
insert LOCAL_LOG (baseName, rec_id,operation,term) select distinct 'SM', SMS.id, 3, @@SPID
from @tblSMS as SMS where SMS.id IS NOT NULL
insert DW
select 0, 0, 0, 'Rezerwacja ilościowa', NULL, NULL, NULL, NULL, -SMS.magazyn, SMS.idtw, convert(datetime, '9999-01-01', 20),
0.0, 0.0, 0.0, 0.0, SMS.magazyn, 0.0, 0.0, 0.0, 0, NEWID(),NULL
from @tblSMS as SMS
left join DW on DW.numer < 0 and SMS.idtw = DW.idtw and SMS.magazyn = DW.magazyn
where SMS.id IS NULL and DW.id IS NULL
set @return_status = @@error
if @return_status <> 0 return @return_status
GO
IF OBJECT_ID('[HM].[stdhm_getrec_dw]', 'P') IS NOT NULL DROP PROCEDURE [HM].[stdhm_getrec_dw]
GO
CREATE PROCEDURE HM.stdhm_getrec_dw
@id int
AS
SELECT id,flag,subtyp,typ,kod,iddw,iddkpz,idkh,iddkzk,numer,idtw,data,ilosc,iloscdosp,cena,wartoscdosp,magazyn,iloscPz,stan,wartoscst,bufor,krajPochodzenia
FROM DW
WHERE id = @id
ORDER BY id ASC
GO
IF OBJECT_ID('[HM].[stdhm_update_dw]', 'P') IS NOT NULL DROP PROCEDURE [HM].[stdhm_update_dw]
GO
CREATE PROCEDURE HM.stdhm_update_dw
@id int, @flag smallint, @subtyp smallint, @typ smallint, @kod varchar(101),
@iddw int, @iddkpz int, @idkh int, @iddkzk int, @numer int,
@idtw int, @data datetime, @ilosc float, @iloscdosp float, @cena float,
@wartoscdosp float, @magazyn int, @iloscPz float, @stan float, @wartoscst float, @bufor smallint, @krajPochodzenia int
AS
UPDATE DW
SET flag = @flag, subtyp = @subtyp, typ = @typ, kod = @kod, iddw = @iddw,
iddkpz = @iddkpz, idkh = @idkh, iddkzk = @iddkzk, numer = @numer, idtw = @idtw,
data = @data, ilosc = @ilosc, iloscdosp = @iloscdosp, cena = @cena, wartoscdosp = @wartoscdosp,
magazyn = @magazyn, iloscPz = @iloscPz, stan = @stan, wartoscst = @wartoscst, bufor = @bufor, krajPochodzenia = @krajPochodzenia
WHERE @id = id
UPDATE IT SET idKrajuPo = @krajPochodzenia where iddw = @id
GO
IF OBJECT_ID('[HM].[hm_DW_PowiazaniaDostawPozycji]', 'P') IS NOT NULL DROP PROCEDURE [HM].[hm_DW_PowiazaniaDostawPozycji]
GO
CREATE PROCEDURE HM.hm_DW_PowiazaniaDostawPozycji
@poz int
AS
declare @superkoryg int
declare @super int
declare @lp smallint
declare @bufor smallint
declare @przychod smallint
select @superkoryg = ISNULL(MG.iddokkoryg, MG.id), @super = MZ.super, @lp = MZ.lp, @bufor = MG.bufor,
@przychod = case when (MG.flag & 0x0100) <> 0 then 1 else 0 end
from MG join MZ on MG.id = MZ.super where MZ.id = @poz
declare @tblPWM TABLE
(iddw int primary key, ilosc decimal(24,6), idpw int, flagpw smallint, datapw datetime, idkoryg int,
ilmag decimal(24,6), wartmag decimal(24,2), ilbuf decimal(24,6), wartbuf decimal(24,2), ilrez decimal(24,6), wartrez decimal(24,2))
declare @tblPWA TABLE
(id int, iddw int, ilosc decimal(24,6), wartosc decimal(24,2), idmz int, super int, primary key (iddw, super))
if @bufor = 1
if @superkoryg = @super
begin
if @przychod = 1
begin
insert @tblPWM (iddw, ilosc, idpw, flagpw, datapw, idkoryg, ilmag, wartmag, ilbuf, wartbuf, ilrez, wartrez)
select PW.iddw, PW.ilosc, PW.id, PW.flag, PW.data, 0, 0.0, 0.0, PW.ilosc, PW.wartosc, 0.0, 0.0
from PW where PW.typ = 39 and PW.idmg = @poz
end
else
begin
insert @tblPWM (iddw, ilosc, idpw, flagpw, datapw, idkoryg, ilmag, wartmag, ilbuf, wartbuf, ilrez, wartrez)
select PW.iddw, 0.0, 0, 0, NULL, 0, 0.0, 0.0, 0.0, 0.0, PW.ilosc, PW.wartosc
from PW join RZ on (PW.typ = 26 and PW.idmg = RZ.id) where RZ.idpozrz = @poz
end
end
else
begin
insert @tblPWA
select MIN(PW.id) as id, PW.iddw, SUM(PW.ilosc), SUM(PW.wartosc + ISNULL(PWK.wartosc, 0.0)), MZ.id as idmz, MZ.super
from PW join MZ on (PW.typ = 37 and PW.idmg = MZ.id)
left join
(select SUM(PW.wartosc) as wartosc, PW.idkoryg
from PW where PW.subtyp = 90 group by PW.idkoryg) as PWK on PWK.idkoryg = PW.id
where MZ.lp = @lp and MZ.superkoryg = @superkoryg and MZ.super < @super
group by MZ.id, MZ.super, PW.iddw
declare @tblPWB TABLE
(id int, iddw int, ilosc decimal(24,6), wartosc decimal(24,2), idmz int, super int, primary key (iddw, super))
insert @tblPWB
select PW.id, PW.iddw, PW.ilosc, PW.wartosc, MZ.id as idmz, MZ.super
from PW join MZ on (PW.typ = 39 and PW.idmg = MZ.id)
where MZ.id = @poz
declare @tblPWR TABLE
(id int, iddw int, ilosc decimal(24,6), wartosc decimal(24,2), idmz int, super int, primary key (iddw, super))
insert @tblPWR
select PW.id, PW.iddw, PW.ilosc, PW.wartosc, MZ.id as idmz, MZ.super
from PW join RZ on (PW.typ = 26 and PW.idmg = RZ.id)
join MZ on (RZ.bazadokrz = 33 and RZ.bazapozrz = 37 and RZ.idpozrz = MZ.id)
where RZ.idpozrz = @poz
insert @tblPWM (iddw, ilosc, idpw, flagpw, datapw, idkoryg, ilmag, wartmag, ilbuf, wartbuf, ilrez, wartrez)
select PWA.iddw, ISNULL(PW.ilosc, 0.0), ISNULL(PW.id, 0), ISNULL(PW.flag, 0), PW.data, ISNULL(PW3.idkoryg, 0),
ISNULL(PW3.ilosc, 0.0), ISNULL(PW3.wartosc, 0.0), ISNULL(PW1.ilosc, 0.0), ISNULL(PW1.wartosc, 0.0), ISNULL(PW2.ilosc, 0.0), ISNULL(PW2.wartosc, 0.0)
from ( select iddw from @tblPWA union select iddw from @tblPWB union select iddw from @tblPWR) as PWA
left join (select id, iddw, ilosc, wartosc from @tblPWB) as PW1 on PW1.iddw = PWA.iddw
left join PW on PW.id = PW1.id
left join (select iddw, ilosc, wartosc from @tblPWR) as PW2 on PW2.iddw = PWA.iddw
left join (select iddw, SUM(ilosc) as ilosc, SUM(wartosc) as wartosc, MIN(id) as idkoryg
from @tblPWA group by iddw) as PW3 on PW3.iddw = PWA.iddw
end
else
if @superkoryg = @super
begin
insert @tblPWM (iddw, ilosc, idpw, flagpw, datapw, idkoryg, ilmag, wartmag, ilbuf, wartbuf, ilrez, wartrez)
select PW.iddw, PW.ilosc, PW.id, PW.flag, PW.data, PW.id, PW.ilosc, PW.wartosc + ISNULL(PWK.wartosc, 0.0), 0.0, 0.0, 0.0, 0.0
from PW left join
(select PW.idkoryg, SUM(PW.wartosc) as wartosc
from PW where PW.subtyp = 90 group by PW.idkoryg) as PWK on PWK.idkoryg = PW.id
where PW.typ = 37 and PW.idmg = @poz
end
else
begin
insert @tblPWA
select MIN(PW.id) as id, PW.iddw, SUM(PW.ilosc), SUM(PW.wartosc + ISNULL(PWK.wartosc, 0.0)), MZ.id as idmz, MZ.super
from PW join MZ on (PW.typ = 37 and PW.idmg = MZ.id)
left join
(select SUM(PW.wartosc) as wartosc, PW.idkoryg
from PW where PW.subtyp = 90 group by PW.idkoryg) as PWK on PWK.idkoryg = PW.id
where MZ.lp = @lp and MZ.superkoryg = @superkoryg and MZ.super <= @super
group by MZ.id, MZ.super, PW.iddw
insert @tblPWM (iddw, ilosc, idpw, flagpw, datapw, idkoryg, ilmag, wartmag, ilbuf, wartbuf, ilrez, wartrez)
select PWA.iddw, ISNULL(PW.ilosc, 0.0), ISNULL(PW.id, 0), ISNULL(PW.flag, 0), PW.data, PWA.idkoryg, PWA.ilosc, PWA.wartosc, 0.0, 0.0, 0.0, 0.0
from (select iddw, SUM(ilosc) as ilosc, SUM(wartosc) as wartosc, MIN(id) as idkoryg
from @tblPWA group by iddw) as PWA
left join (select id, iddw from @tblPWA where idmz = @poz) as PW1 on PW1.iddw = PWA.iddw
left join PW on PW.id = PW1.id
end
select DW.id, DW.flag, DW.typ, DW.kod, DW.numer, DW.idtw, DW.data, DW.ilosc, DW.iloscdosp, DW.cena, DW.wartoscdosp, DW.magazyn, DW.iloscPz, DW.stan, DW.wartoscst, DW.iddw,
PWM.ilosc, PWM.idpw, PWM.flagpw, PWM.datapw, PWM.idkoryg, PWM.ilmag, PWM.wartmag, PWM.ilbuf, PWM.wartbuf, PWM.ilrez, PWM.wartrez, DW.krajPochodzenia
from DW join @tblPWM as PWM on DW.id = PWM.iddw
GO
IF OBJECT_ID('[HM].[hm_DW_QPowiazaniaDostawMg]', 'P') IS NOT NULL DROP PROCEDURE [HM].[hm_DW_QPowiazaniaDostawMg]
GO
CREATE PROCEDURE HM.hm_DW_QPowiazaniaDostawMg
@super int
AS
declare @superkoryg int
declare @bufor smallint
declare @przychod smallint
declare @charakter smallint
select @superkoryg = ISNULL(iddokkoryg, id), @bufor = bufor, @przychod = case when (MG.flag & 0x0100) <> 0 then 1 else 0 end, @charakter = MG.subtyp
from MG where MG.id = @super
declare @tblPWM TABLE
(id int, lp smallint, super int, iddw int, ilosc decimal(24,6), idpw int, flagpw smallint, datapw datetime, idkoryg int,
ilmag decimal(24,6), wartmag decimal(24,2), ilbuf decimal(24,6), wartbuf decimal(24,2), ilrez decimal(24,6), wartrez decimal(24,2), primary key (id, iddw))
declare @tblPWA TABLE
(id int, iddw int, ilosc decimal(24,6), wartosc decimal(24,2), idmz int, lp smallint, super int, primary key (iddw, super, lp))
declare @tblPWS TABLE
(id int, lp smallint, super int, iddw int, ilosc decimal(24,6), wartosc decimal(24,2), primary key (iddw, super, lp))
if @charakter not in (90,91)
if @bufor = 1
if @superkoryg = @super
begin
if @przychod = 1
begin
insert @tblPWM (id, lp, super, iddw, ilosc, idpw, flagpw, datapw, idkoryg, ilmag, wartmag, ilbuf, wartbuf, ilrez, wartrez)
select MZ.id, MZ.lp, MZ.super, PW.iddw, PW.ilosc, PW.id, PW.flag, PW.data, 0,
0.0, 0.0, PW.ilosc, PW.wartosc, 0.0, 0.0
from PW join MZ on (PW.typ = 39 and MZ.id = PW.idmg)
where MZ.super = @super
end
else
begin
insert @tblPWM (id, lp, super, iddw, ilosc, idpw, flagpw, datapw, idkoryg, ilmag, wartmag, ilbuf, wartbuf, ilrez, wartrez)
select MZ.id, MZ.lp, MZ.super, PW.iddw, 0.0, 0, 0, NULL, 0,
0.0, 0.0, 0.0, 0.0, PW.ilosc, PW.wartosc
from PW join RZ on (PW.typ = 26 and PW.idmg = RZ.id)
join MZ on (RZ.bazadokrz = 33 and RZ.bazapozrz = 37 and RZ.idpozrz = MZ.id)
where RZ.iddokrz = @super
end
end
else
begin
insert @tblPWA
select MIN(PW.id) as id, PW.iddw, SUM(PW.ilosc), SUM(PW.wartosc + ISNULL(PWK.wartosc, 0.0)), MZ.id as idmz, MZ.lp, MZ.super
from PW join MZ on (PW.typ = 37 and PW.idmg = MZ.id)
left join
(select SUM(PW.wartosc) as wartosc, PW.idkoryg
from PW where PW.subtyp = 90 group by PW.idkoryg) as PWK on PWK.idkoryg = PW.id
where MZ.superkoryg = @superkoryg and MZ.super < @super
group by MZ.id, MZ.lp, MZ.super, PW.iddw
declare @tblPWB TABLE
(id int, iddw int, ilosc decimal(24,6), wartosc decimal(24,2), idmz int, lp smallint, super int, primary key (iddw, super, lp))
insert @tblPWB
select PW.id, PW.iddw, PW.ilosc, PW.wartosc, MZ.id as idmz, MZ.lp, MZ.super
from PW join MZ on (PW.typ = 39 and PW.idmg = MZ.id)
where MZ.super = @super
declare @tblPWR TABLE
(id int, iddw int, ilosc decimal(24,6), wartosc decimal(24,2), idmz int, lp smallint, super int, primary key (iddw, super, lp))
insert @tblPWR
select PW.id, PW.iddw, PW.ilosc, PW.wartosc, MZ.id as idmz, MZ.lp, MZ.super
from PW join RZ on (PW.typ = 26 and PW.idmg = RZ.id)
join MZ on (RZ.bazadokrz = 33 and RZ.bazapozrz = 37 and RZ.idpozrz = MZ.id)
where RZ.iddokrz = @super
insert @tblPWS
select MIN(id), lp, super, iddw, SUM(ilosc), SUM(wartosc)
from ( select id, lp, super, iddw, ilosc, wartosc from @tblPWA
union select id, lp, super, iddw, ilosc, wartosc from @tblPWB
union select id, lp, super, iddw, ilosc, wartosc from @tblPWR) as PWA
group by lp, super, iddw
insert @tblPWM (id, lp, super, iddw, ilosc, idpw, flagpw, datapw, idkoryg, ilmag, wartmag, ilbuf, wartbuf, ilrez, wartrez)
select PWA.idmz, PWA.lp, PWA.super, PWA.iddw, ISNULL(PW.ilosc, 0.0), ISNULL(PW.id, 0), ISNULL(PW.flag, 0), PW.data, ISNULL(PW3.idkoryg, 0),
ISNULL(PW3.ilosc, 0.0), ISNULL(PW3.wartosc, 0.0), ISNULL(PW1.ilosc, 0.0), ISNULL(PW1.wartosc, 0.0), ISNULL(PW2.ilosc, 0.0), ISNULL(PW2.wartosc, 0.0)
from ( select lp, super, idmz, iddw from @tblPWA
union select lp, super, idmz, iddw from @tblPWB
union select lp, super, idmz, iddw from @tblPWR) as PWA
left join (select id, idmz, iddw, ilosc, wartosc from @tblPWB) as PW1 on (PW1.idmz = PWA.idmz and PW1.iddw = PWA.iddw)
left join PW on PW.id = PW1.id
left join (select idmz, iddw, ilosc, wartosc from @tblPWR) as PW2 on (PW2.idmz = PWA.idmz and PW2.iddw = PWA.iddw)
left join
(select PWS1.lp, PWS1.super, PWS1.iddw, SUM(PWS2.ilosc) as ilosc, SUM(PWS2.wartosc) as wartosc, MIN(PWS1.id) as idkoryg
from @tblPWS as PWS1
join @tblPWS as PWS2 on (PWS1.lp = PWS2.lp and PWS1.iddw = PWS2.iddw and PWS1.super >= PWS2.super)
group by PWS1.lp, PWS1.super, PWS1.iddw) as PW3 on PW3.lp = PWA.lp and PW3.super = PWA.super and PW3.iddw = PWA.iddw
end
else
if @superkoryg = @super
begin
insert @tblPWM (id, lp, super, iddw, ilosc, idpw, flagpw, datapw, idkoryg, ilmag, wartmag, ilbuf, wartbuf, ilrez, wartrez)
select MZ.id, MZ.lp, MZ.super, PW.iddw, PW.ilosc, PW.id, PW.flag, PW.data, PW.id,
PW.ilosc, PW.wartosc + ISNULL(PWK.wartosc, 0.0), 0.0, 0.0, 0.0, 0.0
from PW join MZ on (PW.typ = 37 and MZ.id = PW.idmg)
left join
(select PW.idkoryg, SUM(PW.wartosc) as wartosc
from PW where PW.subtyp = 90 group by PW.idkoryg) as PWK on PWK.idkoryg = PW.id
where MZ.super = @super
end
else
begin
insert @tblPWA
select MIN(PW.id) as id, PW.iddw, SUM(PW.ilosc), SUM(PW.wartosc + ISNULL(PWK.wartosc, 0.0)), MZ.id as idmz, MZ.lp, MZ.super
from PW join MZ on (PW.typ = 37 and PW.idmg = MZ.id)
left join
(select SUM(PW.wartosc) as wartosc, PW.idkoryg
from PW where PW.subtyp = 90 group by PW.idkoryg) as PWK on PWK.idkoryg = PW.id
where MZ.superkoryg = @superkoryg and MZ.super <= @super
group by MZ.id, MZ.lp, MZ.super, PW.iddw
insert @tblPWS
select MIN(id), lp, super, iddw, SUM(ilosc), SUM(wartosc)
from @tblPWA group by lp, super, iddw
insert @tblPWM (id, lp, super, iddw, ilosc, idpw, flagpw, datapw, idkoryg, ilmag, wartmag, ilbuf, wartbuf, ilrez, wartrez)
select PWA.idmz, PWA.lp, PWA.super, PWA.iddw, PW.ilosc, PW.id, PW.flag, PW.data, ISNULL(PW3.idkoryg, 0),
ISNULL(PW3.ilosc, 0.0), ISNULL(PW3.wartosc, 0.0), 0.0, 0.0, 0.0, 0.0
from (select id, lp, super, idmz, iddw from @tblPWA) as PWA
join PW on PW.id = PWA.id
left join
(select PWS1.lp, PWS1.super, PWS1.iddw, SUM(PWS2.ilosc) as ilosc, SUM(PWS2.wartosc) as wartosc, min(PWS1.id) as idkoryg
from @tblPWS as PWS1
join @tblPWS as PWS2 on (PWS1.lp = PWS2.lp and PWS1.iddw = PWS2.iddw and PWS1.super >= PWS2.super)
group by PWS1.lp, PWS1.super, PWS1.iddw) as PW3 on PW3.lp = PWA.lp and PW3.super = PWA.super and PW3.iddw = PWA.iddw
end
select DW.id, DW.flag, DW.typ, DW.kod, DW.numer, DW.idtw, DW.data, DW.ilosc, DW.iloscdosp, DW.cena, DW.wartoscdosp, DW.magazyn, DW.iloscPz, DW.stan, DW.wartoscst, DW.iddw,
PWM.lp, PWM.super, PWM.id as idmz, PWM.ilosc, PWM.idpw, PWM.flagpw, PWM.datapw, PWM.idkoryg,
PWM.ilmag, PWM.wartmag, PWM.ilbuf, PWM.wartbuf, PWM.ilrez, PWM.wartrez, DW.krajPochodzenia
from DW join @tblPWM as PWM on DW.id = PWM.iddw
order by PWM.lp, PWM.super desc, PWM.iddw desc
GO
IF OBJECT_ID('[HM].[hm_PW_ZmienPW]', 'P') IS NOT NULL DROP PROCEDURE [HM].[hm_PW_ZmienPW]
GO
CREATE PROCEDURE HM.hm_PW_ZmienPW
@idpw int,
@ilosc float,
@wartosc float,
@kod varchar(100),
@numer int,
@zatwwart smallint,
@krajPochodzenia int
as
declare @iddwo int, @idpwo int, @iddw int, @iddwm int, @idmz int
declare @return_status int
declare @iloscpw decimal(24,6), @wartoscpw decimal(24,2), @iloscpwo decimal(24,6), @wartoscpwo decimal(24,2)
declare @iloscdosp decimal(24,6), @wartoscdosp decimal(24,2), @iloscdw decimal(24,6), @wartoscdw decimal(24,2), @iloscPz decimal(24,6)
declare @new smallint, @bufor smallint, @subtyp smallint, @typ smallint
declare @datao datetime, @data datetime
declare @cena float
declare @przychod smallint, @koryg smallint
set @iloscpw = @ilosc
set @wartoscpw = @wartosc
set @cena = @wartosc / @ilosc
set @new = 0
set @koryg = 0
select @bufor = case when PW.typ = 37 then 0 else 1 end, @subtyp = PW.subtyp, @iddw = PW.iddw, @idmz = PW.idmg,
@new = case when (PW.flag & 0x08) <> 0 then 1 else 0 end, @iloscpwo = PW.ilosc, @wartoscpwo = PW.wartosc, @datao = PW.data
from PW where PW.id = @idpw
select @przychod = case when @subtyp in (74,131,75,132,81,82,83,89) then 1 else 0 end
select @iddwm = ISNULL(DW.iddw, 0) from DW where DW.id = @iddw
select @data = MZ.data from MZ where MZ.id = @idmz
if @iddw = 0
return -1
if exists ( select id from PW where idkoryg = @idpw )
set @koryg = 1
if @koryg = 0
begin
if @przychod = 1 and @new = 1
begin
if @iddwm = 0
begin
update DW set flag = @zatwwart, kod = case when @kod <> '' then @kod else DW.kod end, idkh = MG.khid,
numer = case when @numer <> 0 then @numer else DW.numer end, data = MZ.data, ilosc = @iloscpw,
cena = @cena, magazyn = MZ.magazyn, krajPochodzenia = @krajPochodzenia
from DW, MZ join MG on MZ.super = MG.id where DW.id = @iddw and MZ.id = @idmz
UPDATE IT SET idKrajuPo = @krajPochodzenia where iddw = @iddw
set @return_status = @@error
if @return_status <> 0 return @return_status
end
else
begin
update DW set data = MZ.data, ilosc = @iloscpw, magazyn = MZ.magazyn, krajPochodzenia = @krajPochodzenia
from DW, MZ where DW.id = @iddw and MZ.id = @idmz
UPDATE IT SET idKrajuPo = @krajPochodzenia where iddw = @iddw
set @return_status = @@error
if @return_status <> 0 return @return_status
end
end
if @przychod = 1
begin
set @iloscpw = -@iloscpw
set @wartoscpw = -@wartoscpw
end
else
begin
if @bufor = 0 or @iloscpw > 0
begin
select @iloscdosp = iloscdosp + @iloscpwo, @wartoscdosp = wartoscdosp + @wartoscpwo, @cena = cena
from DW where id = @iddw
if @iloscdosp = 0.0
set @wartoscpw = @iloscpw * @cena
else
set @wartoscpw = @iloscpw * @wartoscdosp / @iloscdosp
end
end
update PW set flag = (PW.flag & ~4) | case when (DW.flag & 1) <> 0 then 0x04 else 0 end,
ilosc = @iloscpw, wartosc = @wartoscpw, data = @data
from PW join DW on PW.iddw = DW.id where PW.id = @idpw
set @return_status = @@error
if @return_status <> 0 return @return_status
if @bufor = 0
begin
if @przychod = 1 and @new = 1 and @iloscpwo <> 0.0
set @wartoscpw = @iloscpw * @wartoscpwo / @iloscpwo
select @iloscdosp = iloscdosp - @iloscpw + @iloscpwo, @wartoscdosp = wartoscdosp - @wartoscpw + @wartoscpwo,
@iloscdw = stan - @iloscpw + @iloscpwo, @wartoscdw = wartoscst - @wartoscpw + @wartoscpwo,
@iloscPz = iloscPz - case when @przychod = 1 then (@iloscpw - @iloscpwo) else 0.0 end
from DW where id = @iddw
if @iloscdosp < 0.0
return -4900
if @wartoscdosp < 0.0
return -4901
update DW set iloscdosp = @iloscdosp, wartoscdosp = @wartoscdosp,
stan = @iloscdw, wartoscst = @wartoscdw, iloscPz = @iloscPz,
typ = case when @iloscdw = 0.0 then 0 else 1 end, krajPochodzenia = @krajPochodzenia
from DW where DW.id = @iddw
set @return_status = @@error
if @return_status <> 0 return @return_status
end
end
else
begin
update PW set flag = (PW.flag & ~4) | case when (DW.flag & 1) <> 0 then 0x04 else 0 end, data = @data
from PW join DW on PW.iddw = DW.id where PW.id = @idpw
set @return_status = @@error
if @return_status <> 0 return @return_status
end
if @subtyp in (82,83) and @data <> @datao
begin
set @datao = NULL
set @typ = NULL
select @datao = MIN(data), @typ = MIN(typ) from PW where typ = 37 and (flag & 0x10) <> 0 and iddw = @iddw
if @typ is NULL
select @datao = min(data), @typ = MIN(typ) from PW where typ = 39 and (flag & 0x10) <> 0 and iddw = @iddw
select @idpwo = MIN(id) from PW where typ = @typ and (flag & 0x10) <> 0 and data = @datao and iddw = @iddw
if @idpwo = @idpw
begin
update PW set flag = (flag | 8) where id = @idpw
set @return_status = @@error
if @return_status <> 0 return @return_status
update PW set flag = (flag & ~8) where id <> @idpw and iddw = @iddw and (flag & 0x10) <> 0
set @return_status = @@error
if @return_status <> 0 return @return_status
update DW set iddkpz = PW.iddkmg, data = PW.data, ilosc = -PW.ilosc from DW join PW on DW.id = PW.iddw where PW.id = @idpw
set @return_status = @@error
if @return_status <> 0 return @return_status
end
end
GO
IF OBJECT_ID('[HM].[hm_DW_QDostawyTowaru]', 'P') IS NOT NULL DROP PROCEDURE [HM].[hm_DW_QDostawyTowaru]
GO
CREATE PROCEDURE HM.hm_DW_QDostawyTowaru
@tw int,
@mg int,
@niezerowe smallint
AS
select id, flag, subtyp, typ, kod, iddw, iddkpz, idkh, iddkzk, numer, idtw, data, ilosc, iloscdosp, cena, wartoscdosp, magazyn, iloscPz, stan, wartoscst, bufor, krajPochodzenia
from DW WITH (INDEX (IDX_DW_IDTW_MAGAZYN_BUFOR))
where DW.bufor = 0 and DW.idtw = @tw and DW.magazyn = @mg and (@niezerowe = 0 or DW.iloscdosp <> 0.0)
GO
IF OBJECT_ID('[HM].[hm_IntrastatZnajdzNiespojneTowary]', 'P') IS NOT NULL DROP PROCEDURE [HM].[hm_IntrastatZnajdzNiespojneTowary]
GO
CREATE PROCEDURE HM.hm_IntrastatZnajdzNiespojneTowary
@wywoz int,
@przywoz int,
@odDate datetime,
@doDate datetime,
@kodCN varchar(10)
AS
select distinct(TW.id),
TW.kod,
case when TW.przelit = 2 then 'TAK' else 'NIE' end AS [przelitAtr],
case when TW.jmuzup = '' then 'BRAK' else TW.jmuzup end AS [jmuzup]
from IT INNER JOIN
MZ ON IT.idmg = MZ.id INNER JOIN
MG ON MZ.super = MG.id INNER JOIN
TW ON MZ.idtw = TW.id
where TW.pcn = @kodCN and MG.data >= @odDate AND MG.data < @doDate
and mg.subtyp IN (78, 88, 76, 77, 74, 75, 89, 81)
GO
SET IDENTITY_INSERT [Common].[CardIndexFilters] ON
INSERT INTO [Common].[CardIndexFilters] ([Id],[Name],[EntityType],[Irremovable],[FilterFields])
VALUES
(9120, 'Dane do deklaracji - poprzedni miesiąc', 'IntrastatCardIndexDTO', 1, 'IsPreviousMonth([PeriodDate])'),
(9121, 'Wszystkie', 'IntrastatCardIndexDTO', 1, NULL)
SET IDENTITY_INSERT [Common].[CardIndexFilters] OFF
GO
IF OBJECT_ID('[HM].[hm_IntrastatDekl]', 'P') IS NOT NULL DROP PROCEDURE [HM].[hm_IntrastatDekl]
GO
CREATE PROCEDURE [HM].[hm_IntrastatDekl]
@wywoz INT,
@poszerz INT,
@odDate DATETIME,
@doDate DATETIME,
@komasuj INT,
@wartKorekty INT
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT
MZ.superkoryg,
MZ.lp,
MZ.cena
INTO #MZCENY
FROM hm.MZ
WHERE MZ.id NOT IN
(
SELECT idpozkoryg FROM HM.MZ WHERE (idpozkoryg IS NOT NULL) AND (bufor = 0)
)
AND MZ.kompensata = 0;
IF @wywoz = 1
BEGIN
SET NOCOUNT ON;
SELECT MG.subtyp,
ISNULL(MG.iddokkoryg, MG.id) AS [iddokkoryg],
MZ.super,
MZ.lp,
SUM(MZ.iloscwp * MZC.cena + MZ.wartk) AS [wartNetto],
SUM(MZ.ilosc) AS [ilosc]
INTO #MZW
FROM HM.MG AS MG
JOIN HM.MZ AS MZ
ON MZ.super = MG.id
JOIN #MZCENY AS MZC
ON MZC.superkoryg = MZ.superkoryg
AND MZC.lp = MZ.lp
WHERE MG.data >= @odDate
AND MG.data < @doDate
GROUP BY MG.subtyp,
ISNULL(MG.iddokkoryg, MG.id),
MZ.super,
MZ.lp
HAVING (SUM(MZ.ilosc) > 0)
AND (MG.subtyp IN ( 78, 76, 88, 77 ));
SELECT DISTINCT
MZ.super,
MZ.lp,
DP.wartNetto,
DP.ilosc
INTO #DPW
FROM HM.MG AS MG
JOIN HM.MZ AS MZ
ON MZ.super = MG.id
LEFT OUTER JOIN HM.OP OPMZ
ON OPMZ.owner_id = MZ.id
LEFT OUTER JOIN HM.RO RO
ON RO.id2 = OPMZ.id
LEFT OUTER JOIN HM.OP OPDP
ON OPDP.id = RO.id1
LEFT OUTER JOIN HM.DP
ON ISNULL(DP.idpozkoryg, DP.id) = OPDP.owner_id
WHERE OPMZ.subtyp = 3
AND DP.id IS NOT NULL
AND DP.kompensata <> 1
AND DP.bufor = 0
AND DP.id NOT IN
(
SELECT idpozkoryg FROM HM.DP WHERE (idpozkoryg IS NOT NULL AND bufor = 0)
)
AND RO.flag & 0x1000 <> 0x1000
AND RO.typ = 1
AND MG.id IN
(
SELECT iddokkoryg FROM #MZW
);
IF @wartKorekty <> 0
BEGIN
SELECT MG.subtyp,
ISNULL(MG.iddokkoryg, MG.id) AS [iddokkoryg],
MZ.super,
MZ.lp,
SUM(MZ.iloscwp * MZC.cena + MZ.wartk) AS [wartNetto],
SUM(MZ.ilosc) AS [ilosc]
INTO #MZPZ
FROM HM.MG AS MG
JOIN HM.MZ AS MZ
ON MZ.super = MG.id
JOIN #MZCENY AS MZC
ON MZC.superkoryg = MZ.superkoryg
AND MZC.lp = MZ.lp
WHERE MG.data >= @odDate
AND MG.data < @doDate
GROUP BY MG.subtyp,
ISNULL(MG.iddokkoryg, MG.id),
MZ.super,
MZ.lp
HAVING (SUM(MZ.ilosc) > 0)
AND (MG.subtyp IN ( 81, 75 ));
INSERT INTO #DPW
SELECT DISTINCT
MZ.super,
MZ.lp,
ISNULL(
NULLIF(ISNULL(DP.cenawal, 0) * ISNULL(DP.iloscwp, 0) * ISNULL(DK2.kurs, 0), 0),
(ISNULL(DP.wartNetto, 0) - SIGN(ISNULL(DP.wartNetto, 0)) * ISNULL(DP.iloscjedn, 0))
) AS [wartnetto],
DP.ilosc
FROM HM.MG AS MG
JOIN HM.MZ AS MZ
ON MZ.super = MG.id
LEFT OUTER JOIN HM.OP OPMZ
ON OPMZ.owner_id = MZ.id
LEFT OUTER JOIN HM.RO RO
ON RO.id1 = OPMZ.id
LEFT OUTER JOIN HM.OP OPDP
ON OPDP.id = RO.id2
LEFT OUTER JOIN HM.DP
ON ISNULL(DP.idpozkoryg, DP.id) = OPDP.owner_id
LEFT OUTER JOIN HM.DK DK1
ON DK1.id = DP.super
LEFT OUTER JOIN HM.ZZ
ON (
ZZ.baza2 IN ( 16, 34 )
AND ZZ.typ = 151
AND ZZ.id2 = DK1.id
)
LEFT OUTER JOIN HM.DK DK2
ON DK2.id = ZZ.id1
WHERE OPMZ.subtyp = 2
AND DP.id IS NOT NULL
AND DP.kompensata <> 1
AND DP.bufor = 0
AND DP.id NOT IN
(
SELECT idpozkoryg FROM HM.DP WHERE (idpozkoryg IS NOT NULL AND bufor = 0)
)
AND RO.flag & 0x1000 <> 0x1000
AND RO.typ = 1
AND MG.id IN
(
SELECT iddokkoryg FROM #MZPZ
);
INSERT INTO #MZW
SELECT *
FROM #MZPZ;
END;
IF @poszerz = 1
BEGIN
SELECT ISNULL(SUBSTRING(TX.opis, 1, 255), TW.nazwa) AS [OpisTowaru],
'tw' AS [JakiOpis],
IT.idKrajuPo AS [KrajPochodzenia],
IT.idKrajuWy AS [KrajPrzeznaczeniaWysylki],
ISNULL(a.nip, '') AS [IdKontrahenta],
IT.dostawa AS [WarunkiDostawy],
IT.transakcja AS [RodzajTransakcji],
IT.transport AS [RodzajTransportu],
TW.pcn AS [KodTowarowy],
MAX(TW.przelit) AS [PrzelicznikIT],
SUM(pw.ilosc * ISNULL(NULLIF(TW.przelkg, 0), 0)) AS [MasaNetto],
SUM(pw.ilosc * ISNULL(NULLIF(TW.przeluz, 0), 0)) AS [IloscUzupelniajacaJm],
SUM(ISNULL(PW.ilosc*(#DPW.wartNetto/NULLIF ( #DPW.ilosc , 0 )),ROUND((#MZW.wartNetto/NULLIF(#MZW.ilosc,0))*pw.ilosc,2))) as [WartoscFaktury],
SUM(ABS(IT.wartStat)) AS [WartoscStatystyczna],
MAX(TW.id) AS [idtw]
INTO #ISTPW
FROM hm.IT
INNER JOIN hm.MZ
ON IT.idmg = MZ.id
INNER JOIN hm.MG d
ON MZ.super = d.id
INNER JOIN HM.PW
ON pw.iddkmg=mz.super AND pw.idmg=mz.id AND pw.iddw=it.iddw AND pw.ilosc<>0
LEFT JOIN hm.AD a
ON d.khadid = a.id
INNER JOIN #MZW
ON MZ.super = #MZW.super
AND MZ.lp = #MZW.lp
INNER JOIN hm.TW
ON MZ.idtw = TW.id
LEFT OUTER JOIN #DPW
ON #MZW.iddokkoryg = #DPW.super
AND #MZW.lp = #DPW.lp
LEFT OUTER JOIN hm.TX
ON TX.id = TW.idlongname
LEFT OUTER JOIN hm.MZ AS MZK
ON MZ.idpozkoryg = MZK.id
WHERE IT.idKrajuWy <> 1
GROUP BY ISNULL(SUBSTRING(TX.opis, 1, 255), TW.nazwa),
IT.idKrajuPo,
IT.idKrajuWy,
ISNULL(a.nip, ''),
IT.dostawa,
IT.transakcja,
IT.transport,
TW.pcn
IF @komasuj <> 1
BEGIN
SELECT COUNT(*) AS [LacznaLiczbaPozycji],
SUM(ROUND(WartoscFaktury, 0)) AS [LacznaWartoscFaktur],
SUM(ROUND(WartoscStatystyczna, 0)) AS [LacznaWartoscStatystyczna]
FROM #ISTPW
HAVING SUM(WartoscFaktury) <> 0;
SELECT *
FROM #ISTPW;
END;
ELSE
BEGIN
SELECT KrajPochodzenia,
KrajPrzeznaczeniaWysylki,
IdKontrahenta,
WarunkiDostawy,
RodzajTransakcji,
RodzajTransportu,
KodTowarowy,
SUM(MasaNetto) AS [MasaNetto],
SUM(IloscUzupelniajacaJm) AS [IloscUzupelniajacaJm],
SUM(WartoscFaktury) AS [WartoscFaktury],
SUM(WartoscStatystyczna) AS [WartoscStatystyczna],
COUNT(*) AS [ilepoz],
MAX(idtw) AS [idtw],
MAX(PrzelicznikIT) AS [PrzelicznikIT]
INTO #ISTPWKOM
FROM #ISTPW
GROUP BY KrajPochodzenia,
KrajPrzeznaczeniaWysylki,
IdKontrahenta,
WarunkiDostawy,
RodzajTransakcji,
RodzajTransportu,
KodTowarowy;
SELECT COUNT(*) AS [LacznaLiczbaPozycji],
SUM(ROUND(WartoscFaktury, 0)) AS [LacznaWartoscFaktur],
SUM(ROUND(WartoscStatystyczna, 0)) AS [LacznaWartoscStatystyczna]
FROM #ISTPWKOM
HAVING SUM(WartoscFaktury) <> 0;
SELECT KrajPochodzenia,
KodTowarowy,
KrajPrzeznaczeniaWysylki,
IdKontrahenta,
WarunkiDostawy,
RodzajTransakcji,
RodzajTransportu,
MasaNetto,
IloscUzupelniajacaJm,
WartoscFaktury,
WartoscStatystyczna,
CASE
WHEN (ilepoz > 1) THEN
'pcn'
ELSE
'tw'
END AS [JakiOpis],
ISNULL(SUBSTRING(TX.opis, 1, 255), TW.nazwa) AS [OpisTowaru],
PrzelicznikIT
FROM #ISTPWKOM
INNER JOIN HM.TW
ON idtw = TW.id
LEFT OUTER JOIN HM.TX
ON TX.id = TW.idlongname;
END;
END;
ELSE
BEGIN
SELECT ISNULL(SUBSTRING(TX.opis, 1, 255), TW.nazwa) AS [OpisTowaru],
'tw' AS [JakiOpis],
IT.idKrajuPo AS [KrajPochodzenia],
IT.idKrajuWy AS [KrajPrzeznaczeniaWysylki],
ISNULL(a.nip, '') AS [IdKontrahenta],
IT.transakcja AS [RodzajTransakcji],
TW.pcn AS [KodTowarowy],
MAX(TW.przelit) AS [PrzelicznikIT],
SUM(PW.ilosc * ISNULL(NULLIF(TW.przelkg, 0), 0)) AS [MasaNetto],
SUM(PW.ilosc * ISNULL(NULLIF(TW.przeluz, 0), 0)) AS [IloscUzupelniajacaJm],
SUM(ISNULL(PW.ilosc*(#DPW.wartNetto/NULLIF ( #DPW.ilosc , 0 )),ROUND((#MZW.wartNetto/NULLIF(#MZW.ilosc,0))*pw.ilosc,2))) as [WartoscFaktury],
MAX(TW.id) AS [idtw]
INTO #ISTW
FROM hm.IT
INNER JOIN hm.MZ
ON IT.idmg = MZ.id
INNER JOIN hm.MG d
ON MZ.super = d.id
INNER JOIN HM.PW
ON pw.iddkmg=mz.super AND pw.idmg=mz.id AND pw.iddw=it.iddw
LEFT JOIN HM.AD a
ON d.khadid = a.id
INNER JOIN #MZW
ON MZ.super = #MZW.super
AND MZ.lp = #MZW.lp
INNER JOIN HM.TW
ON MZ.idtw = TW.id
LEFT OUTER JOIN #DPW
ON #MZW.iddokkoryg = #DPW.super
AND #MZW.lp = #DPW.lp
LEFT OUTER JOIN HM.TX
ON TX.id = TW.idlongname
WHERE IT.idKrajuWy <> 1
GROUP BY ISNULL(SUBSTRING(TX.opis, 1, 255), TW.nazwa),
IT.idKrajuWy,
IT.idKrajuPo,
ISNULL(a.nip, ''),
IT.transakcja,
TW.pcn;
IF @komasuj <> 1
BEGIN
SELECT COUNT(*) AS [LacznaLiczbaPozycji],
SUM(ROUND(WartoscFaktury, 0)) AS [LacznaWartoscFaktur]
FROM #ISTW
HAVING SUM(WartoscFaktury) <> 0;
SELECT *
FROM #ISTW;
END;
ELSE
BEGIN
SELECT KrajPochodzenia,
KrajPrzeznaczeniaWysylki,
IdKontrahenta,
RodzajTransakcji,
KodTowarowy,
SUM(MasaNetto) AS [MasaNetto],
SUM(IloscUzupelniajacaJm) AS [IloscUzupelniajacaJm],
SUM(WartoscFaktury) AS [WartoscFaktury],
COUNT(*) AS [ilepoz],
MAX(idtw) AS [idtw],
MAX(PrzelicznikIT) AS [PrzelicznikIT]
INTO #ISTWKOM
FROM #ISTW
GROUP BY KrajPochodzenia,
KrajPrzeznaczeniaWysylki,
IdKontrahenta,
RodzajTransakcji,
KodTowarowy;
SELECT COUNT(*) AS [LacznaLiczbaPozycji],
SUM(ROUND(WartoscFaktury, 0)) AS [LacznaWartoscFaktur]
FROM #ISTWKOM
HAVING SUM(WartoscFaktury) <> 0;
SELECT KrajPochodzenia,
KodTowarowy,
KrajPrzeznaczeniaWysylki,
IdKontrahenta,
RodzajTransakcji,
MasaNetto,
IloscUzupelniajacaJm,
WartoscFaktury,
CASE
WHEN (ilepoz > 1) THEN
'pcn'
ELSE
'tw'
END AS [JakiOpis],
ISNULL(SUBSTRING(TX.opis, 1, 255), TW.nazwa) AS [OpisTowaru],
PrzelicznikIT
FROM #ISTWKOM
INNER JOIN HM.TW
ON idtw = TW.id
LEFT OUTER JOIN HM.TX
ON TX.id = TW.idlongname;
END;
END;
END;
ELSE
BEGIN
SET NOCOUNT ON;
SELECT MG.subtyp,
ISNULL(MG.iddokkoryg, MG.id) AS [iddokkoryg],
MZ.super,
MZ.lp,
SUM(MZ.iloscwp * MZC.cena + MZ.wartk) AS [wartNetto],
SUM(MZ.ilosc) AS [ilosc]
INTO #MZP
FROM hm.MG AS MG
JOIN HM.MZ AS MZ
ON MZ.super = MG.id
JOIN #MZCENY AS MZC
ON MZC.superkoryg = MZ.superkoryg
AND MZC.lp = MZ.lp
WHERE MG.data >= @odDate
AND MG.data < @doDate
GROUP BY MG.subtyp,
ISNULL(MG.iddokkoryg, MG.id),
MZ.super,
MZ.lp
HAVING (SUM(MZ.ilosc) < 0)
AND (MG.subtyp IN ( 74, 75, 89, 81 ));
SELECT DISTINCT
MZ.super,
MZ.lp,
ISNULL(
NULLIF(ISNULL(DP.cenawal, 0) * ISNULL(DP.iloscwp, 0) * ISNULL(DK2.kurs, 0), 0),
(ISNULL(DP.wartNetto, 0) - SIGN(ISNULL(DP.wartNetto, 0)) * ISNULL(DP.iloscjedn, 0))
) AS [wartnetto],
DP.ilosc
INTO #DPP
FROM HM.MG AS MG
JOIN HM.MZ AS MZ
ON MZ.super = MG.id
LEFT OUTER JOIN HM.OP OPMZ
ON OPMZ.owner_id = MZ.id
LEFT OUTER JOIN HM.RO RO
ON RO.id1 = OPMZ.id
LEFT OUTER JOIN HM.OP OPDP
ON OPDP.id = RO.id2
LEFT OUTER JOIN HM.DP
ON ISNULL(DP.idpozkoryg, DP.id) = OPDP.owner_id
LEFT OUTER JOIN HM.DK DK1
ON DK1.id = DP.super
LEFT OUTER JOIN HM.ZZ
ON (
ZZ.baza2 IN ( 16, 34 )
AND ZZ.typ = 151
AND ZZ.id2 = DK1.id
)
LEFT OUTER JOIN HM.DK DK2
ON DK2.id = ZZ.id1
WHERE OPMZ.subtyp = 2
AND DP.id IS NOT NULL
AND DP.kompensata <> 1
AND DP.bufor = 0
AND DP.id NOT IN
(
SELECT idpozkoryg FROM HM.DP WHERE (idpozkoryg IS NOT NULL AND bufor = 0)
)
AND RO.flag & 0x1000 <> 0x1000
AND RO.typ = 1
AND MG.id IN
(
SELECT iddokkoryg FROM #MZP
);
IF @wartKorekty <> 0
BEGIN
SELECT MG.subtyp,
ISNULL(MG.iddokkoryg, MG.id) AS [iddokkoryg],
MZ.super,
MZ.lp,
SUM(MZ.iloscwp * MZC.cena + MZ.wartk) AS [wartNetto],
SUM(MZ.ilosc) AS [ilosc]
INTO #MZWZ
FROM HM.MG AS MG
JOIN HM.MZ AS MZ
ON MZ.super = MG.id
JOIN #MZCENY AS MZC
ON MZC.superkoryg = MZ.superkoryg
AND MZC.lp = MZ.lp
WHERE MG.data >= @odDate
AND MG.data < @doDate
GROUP BY MG.subtyp,
ISNULL(MG.iddokkoryg, MG.id),
MZ.super,
MZ.lp
HAVING (SUM(MZ.ilosc) < 0)
AND (MG.subtyp IN ( 77, 88 ));
INSERT INTO #DPP
SELECT DISTINCT
MZ.super,
MZ.lp,
DP.wartNetto,
DP.ilosc
FROM HM.MG AS MG
JOIN HM.MZ AS MZ
ON MZ.super = MG.id
LEFT OUTER JOIN HM.OP OPMZ
ON OPMZ.owner_id = MZ.id
LEFT OUTER JOIN HM.RO RO
ON RO.id2 = OPMZ.id
LEFT OUTER JOIN HM.OP OPDP
ON OPDP.id = RO.id1
LEFT OUTER JOIN HM.DP
ON ISNULL(DP.idpozkoryg, DP.id) = OPDP.owner_id
WHERE OPMZ.subtyp = 3
AND DP.id IS NOT NULL
AND DP.kompensata <> 1
AND DP.bufor = 0
AND DP.id NOT IN
(
SELECT idpozkoryg FROM HM.DP WHERE (idpozkoryg IS NOT NULL AND bufor = 0)
)
AND RO.flag & 0x1000 <> 0x1000
AND RO.typ = 1
AND MG.id IN
(
SELECT iddokkoryg FROM #MZWZ
);
INSERT INTO #MZP
SELECT *
FROM #MZWZ;
END;
IF @poszerz = 1
BEGIN
SELECT ISNULL(SUBSTRING(TX.opis, 1, 255), TW.nazwa) AS [OpisTowaru],
'tw' AS [JakiOpis],
IT.idKrajuPo AS [KrajPochodzenia],
IT.idKrajuWy AS [KrajPrzeznaczeniaWysylki],
IT.dostawa AS [WarunkiDostawy],
IT.transakcja AS [RodzajTransakcji],
IT.transport AS [RodzajTransportu],
TW.pcn AS [KodTowarowy],
MAX(TW.przelit) AS [PrzelicznikIT],
SUM(pw.ilosc * ISNULL(NULLIF(TW.przelkg, 0), 0)) AS [MasaNetto],
SUM(pw.ilosc * ISNULL(NULLIF(TW.przeluz, 0), 0)) AS [IloscUzupelniajacaJm],
SUM(ABS(ISNULL(PW.ilosc*(#DPP.wartNetto/NULLIF ( #DPP.ilosc , 0 )),ROUND((#MZP.wartNetto/NULLIF(#MZP.ilosc,0))*pw.ilosc,2)))) as [WartoscFaktury],
SUM(ABS(IT.wartStat)) AS [WartoscStatystyczna],
MAX(TW.id) AS [idtw]
INTO #ISTPP
FROM HM.IT
INNER JOIN HM.MZ
ON IT.idmg = MZ.id
INNER JOIN hm.MG
ON MG.id=mz.super
INNER JOIN hm.PW
ON pw.iddkmg=mg.id AND pw.idmg=mz.id AND it.iddw=pw.iddw AND pw.ilosc<>0
INNER JOIN #MZP
ON MZ.super = #MZP.super
AND MZ.lp = #MZP.lp
INNER JOIN HM.TW
ON MZ.idtw = TW.id
LEFT OUTER JOIN #DPP
ON #MZP.iddokkoryg = #DPP.super
AND #MZP.lp = #DPP.lp
LEFT OUTER JOIN HM.TX
ON TX.id = TW.idlongname
LEFT OUTER JOIN HM.MZ AS MZK
ON MZ.idpozkoryg = MZK.id
WHERE IT.idKrajuWy <> 1 --AND mz.typ=0
GROUP BY ISNULL(SUBSTRING(TX.opis, 1, 255), TW.nazwa),
IT.idKrajuPo,
IT.idKrajuWy,
IT.dostawa,
IT.transakcja,
IT.transport,
TW.pcn
IF @komasuj <> 1
BEGIN
SELECT COUNT(*) AS [LacznaLiczbaPozycji],
SUM(ROUND(WartoscFaktury, 0)) AS [LacznaWartoscFaktur],
SUM(ROUND(WartoscStatystyczna, 0)) AS [LacznaWartoscStatystyczna]
FROM #ISTPP
HAVING SUM(WartoscFaktury) <> 0;
SELECT *
FROM #ISTPP;
END;
ELSE
BEGIN
SELECT KrajPochodzenia,
KrajPrzeznaczeniaWysylki,
WarunkiDostawy,
RodzajTransakcji,
RodzajTransportu,
KodTowarowy,
SUM(MasaNetto) AS [MasaNetto],
SUM(IloscUzupelniajacaJm) AS [IloscUzupelniajacaJm],
SUM(WartoscFaktury) AS [WartoscFaktury],
SUM(WartoscStatystyczna) AS [WartoscStatystyczna],
COUNT(*) AS [ilepoz],
MAX(idtw) AS [idtw],
MAX(PrzelicznikIT) AS [PrzelicznikIT]
INTO #ISTPPKOM
FROM #ISTPP
GROUP BY KrajPochodzenia,
KrajPrzeznaczeniaWysylki,
WarunkiDostawy,
RodzajTransakcji,
RodzajTransportu,
KodTowarowy,
KrajPochodzenia;
SELECT COUNT(*) AS [LacznaLiczbaPozycji],
SUM(ROUND(WartoscFaktury, 0)) AS [LacznaWartoscFaktur],
SUM(ROUND(WartoscStatystyczna, 0)) AS [LacznaWartoscStatystyczna]
FROM #ISTPPKOM
HAVING SUM(WartoscFaktury) <> 0;
SELECT KrajPochodzenia,
KodTowarowy,
KrajPrzeznaczeniaWysylki,
WarunkiDostawy,
RodzajTransakcji,
RodzajTransportu,
MasaNetto,
IloscUzupelniajacaJm,
WartoscFaktury,
WartoscStatystyczna,
CASE
WHEN (ilepoz > 1) THEN
'pcn'
ELSE
'tw'
END AS [JakiOpis],
ISNULL(SUBSTRING(TX.opis, 1, 255), TW.nazwa) AS [OpisTowaru],
PrzelicznikIT
FROM #ISTPPKOM
INNER JOIN HM.TW
ON idtw = TW.id
LEFT OUTER JOIN HM.TX
ON TX.id = TW.idlongname;
END;
END;
ELSE
BEGIN
SELECT ISNULL(SUBSTRING(TX.opis, 1, 255), TW.nazwa) AS [OpisTowaru],
'tw' AS [JakiOpis],
IT.idKrajuPo AS [KrajPochodzenia],
IT.idKrajuWy AS [KrajPrzeznaczeniaWysylki],
IT.transakcja AS [RodzajTransakcji],
TW.pcn AS [KodTowarowy],
MAX(TW.przelit) AS [PrzelicznikIT],
SUM(pw.ilosc * ISNULL(NULLIF(TW.przelkg, 0), 0)) AS [MasaNetto],
SUM(pw.ilosc * ISNULL(NULLIF(TW.przeluz, 0), 0)) AS [IloscUzupelniajacaJm],
SUM(ABS(ISNULL(PW.ilosc*(#DPP.wartNetto/NULLIF ( #DPP.ilosc , 0 )),ROUND((#MZP.wartNetto/NULLIF(#MZP.ilosc,0))*pw.ilosc,2)))) as [WartoscFaktury],
MAX(TW.id) AS [idtw]
INTO #ISTP
FROM HM.IT
INNER JOIN HM.MZ
ON IT.idmg = MZ.id
INNER JOIN hm.MG
ON MG.id=mz.super
INNER JOIN hm.PW
ON pw.iddkmg=mg.id AND pw.idmg=mz.id AND it.iddw=pw.iddw
INNER JOIN #MZP
ON MZ.super = #MZP.super
AND MZ.lp = #MZP.lp
INNER JOIN HM.TW
ON MZ.idtw = TW.id
LEFT OUTER JOIN #DPP
ON #MZP.iddokkoryg = #DPP.super
AND #MZP.lp = #DPP.lp
LEFT OUTER JOIN HM.TX
ON TX.id = TW.idlongname
WHERE IT.idKrajuWy <> 1
GROUP BY ISNULL(SUBSTRING(TX.opis, 1, 255), TW.nazwa),
IT.idKrajuPo,
IT.idKrajuWy,
IT.transakcja,
TW.pcn,
IT.idKrajuPo;
IF @komasuj <> 1
BEGIN
SELECT COUNT(*) AS [LacznaLiczbaPozycji],
SUM(ROUND(WartoscFaktury, 0)) AS [LacznaWartoscFaktur]
FROM #ISTP
HAVING SUM(WartoscFaktury) <> 0;
SELECT *
FROM #ISTP;
END;
ELSE
BEGIN
SELECT KrajPochodzenia,
KrajPrzeznaczeniaWysylki,
RodzajTransakcji,
KodTowarowy,
SUM(MasaNetto) AS [MasaNetto],
SUM(IloscUzupelniajacaJm) AS [IloscUzupelniajacaJm],
SUM(WartoscFaktury) AS [WartoscFaktury],
COUNT(*) AS [ilepoz],
MAX(idtw) AS [idtw],
MAX(PrzelicznikIT) AS [PrzelicznikIT]
INTO #ISTPKOM
FROM #ISTP
GROUP BY KrajPochodzenia,
KrajPrzeznaczeniaWysylki,
RodzajTransakcji,
KodTowarowy,
KrajPochodzenia;
SELECT COUNT(*) AS [LacznaLiczbaPozycji],
SUM(ROUND(WartoscFaktury, 0)) AS [LacznaWartoscFaktur]
FROM #ISTPKOM
HAVING SUM(WartoscFaktury) <> 0;
SELECT KrajPochodzenia,
KodTowarowy,
KrajPrzeznaczeniaWysylki,
RodzajTransakcji,
MasaNetto,
IloscUzupelniajacaJm,
WartoscFaktury,
CASE
WHEN (ilepoz > 1) THEN
'pcn'
ELSE
'tw'
END AS [JakiOpis],
ISNULL(SUBSTRING(TX.opis, 1, 255), TW.nazwa) AS [OpisTowaru],
PrzelicznikIT
FROM #ISTPKOM
INNER JOIN HM.TW
ON idtw = TW.id
LEFT OUTER JOIN HM.TX
ON TX.id = TW.idlongname;
END;
END;
END;
END;
GO
IF OBJECT_ID('[HM].[hm_IT_NaliczPoz]', 'P') IS NOT NULL DROP PROCEDURE [HM].[hm_IT_NaliczPoz]
GO
CREATE PROCEDURE HM.hm_IT_NaliczPoz
@transakcja SMALLINT,
@dostawa VARCHAR(3),
@transport SMALLINT,
@id INT,
@kraj INT,
@ueKraju SMALLINT,
@krajPolska SMALLINT
AS
DECLARE @return_status INT;
DECLARE @ue BIT;
DECLARE @przychod SMALLINT;
DECLARE @iddokkoryg INT;
SELECT @ue = CASE
WHEN
(
@ueKraju = 1
AND @krajPolska = 0
) THEN
1
ELSE
0
END;
SET @iddokkoryg = 0;
SELECT @przychod = CASE
WHEN (MG.flag & 0x0100) <> 0 THEN
1
ELSE
0
END,
@iddokkoryg = ISNULL(MG.iddokkoryg, 0)
FROM MG
WHERE MG.id = @id;
IF @ue = 0
BEGIN
EXEC @return_status = hm_IT_DeletePoz @id, 1;
RETURN @return_status;
END;
DELETE FROM IT
WHERE id IN
(
SELECT IT.id
FROM IT
JOIN MZ
ON IT.idmg = MZ.id
JOIN TW
ON TW.id = MZ.idtw
WHERE (MZ.flag & 0x1000) <> 0
OR TW.subtypext = 4
); -- pomijamy kompletopodobne i usługi marża na IST
DECLARE @tblITM TABLE
(
idmg INT,
lp SMALLINT,
wartosc DECIMAL(24, 2),
ilosc DECIMAL(24, 2),
data DATETIME,
idkoryg INT,
idkrajuPoch INT,
iddw INT
);
/*W deklaracji Intrastat jeśli jest wywóz towarów z rodzajem transakcji 11 i kodem transportu FOB
wartość statystyczna powinna być wartością z faktury*/
DECLARE @iddk INT;
SELECT @iddk=dk.id FROM hm.mg
LEFT JOIN hm.tr ON tr.owner_id=mg.id AND owner_base=33
LEFT JOIN hm.PO ON tr.id=po.id2
LEFT JOIN hm.tr trdk ON po.id1=trdk.id AND trdk.owner_base=16
LEFT JOIN hm.dk ON dk.id=trdk.owner_id
WHERE mg.id=@id;
IF (OBJECT_ID('tempdb..[#wartStatFromDP]') IS NOT NULL) DROP TABLE #wartStatFromDP
IF (@iddk IS NOT NULL AND @transakcja=11 AND @dostawa='FOB' AND @przychod<>1 )
BEGIN
SELECT dp.id AS dpid ,mz.id AS mzid, dp.wartNetto, dw.id AS dwid
INTO #wartStatFromDP
FROM hm.MZ
LEFT JOIN hm.op AS opmz ON opmz.owner_id=mz.id AND opmz.owner_base=37
LEFT JOIN hm.ro AS ro ON ro.id2=opmz.id
LEFT JOIN hm.op AS opdk ON opdk.id=ro.id1 AND opdk.owner_base=18
LEFT JOIN hm.dp AS dp ON dp.id=opdk.owner_id
LEFT JOIN hm.pw AS pw ON pw.iddkmg=mz.super and pw.idmg=mz.id AND pw.typ=37
LEFT JOIN hm.dw AS dw ON pw.iddw=dw.id
WHERE mz.super=@id
INSERT @tblITM
SELECT MZ.id, MZ.lp,
ROUND(temp.wartNetto/mz.ilosc*pw.ilosc,2) AS wartstat, ISNULL(CASE WHEN @przychod=1 THEN PW.ilosc *-1 ELSE PW.ilosc END, CASE WHEN @przychod=1 THEN (MZ.ilosc+ISNULL(MZK.ilosc, 0.0))*-1.0 ELSE MZ.ilosc+ISNULL(MZK.ilosc, 0.0)END),
MZ.data, MZ.idpozkoryg, DW.krajPochodzenia, DW.id
FROM HM.MG
JOIN HM.MZ ON MZ.super=MG.id AND MZ.kompensata=0
LEFT JOIN HM.MZ AS MZK ON MZK.super=MG.id AND MZK.kompensata=1 AND MZ.lp=MZK.lp
LEFT JOIN HM.TW AS TW ON TW.id=MZ.idtw
LEFT JOIN HM.PW AS PW ON PW.idmg=MZ.id AND PW.iddkmg=MG.id AND PW.ilosc<>0
LEFT JOIN HM.DW AS DW ON DW.id=PW.iddw
LEFT JOIN #wartStatFromDP AS temp ON temp.mzid=mz.id AND temp.dwid=DW.id
WHERE MG.id=@id AND(MZ.flag & 0x1000)=0 AND TW.subtypext<>4; -- pomijamy kompletopodobne i usługi marża na IST
END
ELSE
BEGIN
INSERT @tblITM
SELECT MZ.id,
MZ.lp,
ISNULL( CASE
WHEN @przychod = 1 THEN
ROUND( CASE
WHEN @przychod = 1 THEN
(MZ.przychod + ISNULL(MZK.przychod, 0.0)) * -1.0
ELSE
MZ.rozchod + ISNULL(MZK.rozchod, 0.0)
END / NULLIF(CASE
WHEN @przychod = 1 THEN
(MZ.ilosc + ISNULL(MZK.ilosc, 0.0)) * -1.0
ELSE
MZ.ilosc + ISNULL(MZK.ilosc, 0.0)
END, 0) * PW.ilosc,
2
) * -1
ELSE
ROUND( CASE
WHEN @przychod = 1 THEN
(MZ.przychod + ISNULL(MZK.przychod, 0.0)) * -1.0
ELSE
MZ.rozchod + ISNULL(MZK.rozchod, 0.0)
END / CASE
WHEN @przychod = 1 THEN
(MZ.ilosc + ISNULL(MZK.ilosc, 0.0)) * -1.0
ELSE
MZ.ilosc + ISNULL(MZK.ilosc, 0.0)
END * PW.ilosc,
2
)
END,
CASE
WHEN @przychod = 1 THEN
(MZ.przychod + ISNULL(MZK.przychod, 0.0)) * -1.0
ELSE
MZ.rozchod + ISNULL(MZK.rozchod, 0.0)
END
) AS wartstat,
ISNULL( CASE
WHEN @przychod = 1 THEN
PW.ilosc * -1
ELSE
PW.ilosc
END,
CASE
WHEN @przychod = 1 THEN
(MZ.ilosc + ISNULL(MZK.ilosc, 0.0)) * -1.0
ELSE
MZ.ilosc + ISNULL(MZK.ilosc, 0.0)
END
),
MZ.data,
MZ.idpozkoryg,
DW.krajPochodzenia,
DW.id
FROM HM.MG
JOIN HM.MZ
ON MZ.super = MG.id
AND MZ.kompensata = 0
LEFT JOIN HM.MZ AS MZK
ON MZK.super = MG.id
AND MZK.kompensata = 1
AND MZ.lp = MZK.lp
LEFT JOIN HM.TW AS TW
ON TW.id = MZ.idtw
LEFT JOIN HM.PW AS PW
ON PW.idmg = MZ.id
AND PW.iddkmg = MG.id
AND PW.ilosc <> 0
LEFT JOIN HM.DW AS DW
ON DW.id = PW.iddw
WHERE MG.id = @id
AND (MZ.flag & 0x1000) = 0
AND TW.subtypext <> 4; -- pomijamy kompletopodobne i usługi marża na IST
END
DECLARE @tblITpoz TABLE
(
id INT,
lp SMALLINT,
data DATETIME,
transakcja SMALLINT
);
IF @iddokkoryg <> 0
BEGIN
SELECT TOP 1
@dostawa = CASE
WHEN (dostawa <> @dostawa) THEN
dostawa
ELSE
@dostawa
END,
@transport = CASE
WHEN (transport <> @transport) THEN
transport
ELSE
@transport
END
FROM IT
JOIN MZ
ON IT.idmg = MZ.id
WHERE MZ.super = @iddokkoryg;
INSERT @tblITpoz
(
id,
lp
)
SELECT DISTINCT
MZ.id,
MZ.lp
FROM MZ
WHERE MZ.superkoryg = @iddokkoryg
AND MZ.super = MZ.superkoryg
ORDER BY lp ASC;
SET @return_status = @@error;
IF @return_status <> 0
RETURN @return_status;
UPDATE @tblITpoz
SET data = MZ.data,
transakcja = IT.transakcja
FROM @tblITpoz AS IP
JOIN MZ
ON IP.id = MZ.id
JOIN IT
ON IT.idmg = MZ.id;
SET @return_status = @@error;
IF @return_status <> 0
RETURN @return_status;
END;
SELECT TOP 1
@dostawa = CASE
WHEN (dostawa <> @dostawa) THEN
dostawa
ELSE
@dostawa
END,
@transport = CASE
WHEN (transport <> @transport) THEN
transport
ELSE
@transport
END
FROM IT
JOIN MZ
ON IT.idmg = MZ.id
WHERE MZ.super = @id;
IF @iddokkoryg <> 0
BEGIN
INSERT HM.IT
(
idmg,
idKrajuWy,
idKrajuPo,
transakcja,
dostawa,
transport,
wartStat,
iddw
)
SELECT ITM.idmg,
@kraj,
ITM.idkrajuPoch,
CASE
WHEN (IP.transakcja IN ( '11', '12', '13', '15' ))
AND (ITM.ilosc <= -0.000001) THEN
'21'
ELSE
CASE
WHEN ITK.transakcja IS NULL THEN
@transakcja
ELSE
ITK.transakcja
END
END,
@dostawa,
@transport,
ITM.wartosc,
ITM.iddw
FROM @tblITM AS ITM
LEFT JOIN HM.IT
ON ITM.idmg = IT.idmg
LEFT JOIN IT AS ITK
ON ITK.idmg = ITM.idkoryg
AND ITM.iddw = ITK.iddw
LEFT JOIN @tblITpoz AS IP
ON IP.lp = ITM.lp
WHERE IT.id IS NULL;
SET @return_status = @@error;
IF @return_status <> 0
RETURN @return_status;
END;
ELSE
BEGIN
INSERT HM.IT
(
idmg,
idKrajuWy,
idKrajuPo,
transakcja,
dostawa,
transport,
wartStat,
iddw
)
SELECT ITM.idmg,
@kraj,
ITM.idkrajuPoch,
@transakcja,
@dostawa,
@transport,
ITM.wartosc,
ITM.iddw
FROM @tblITM AS ITM
LEFT JOIN IT
ON ITM.idmg = IT.idmg
LEFT JOIN IT AS ITK
ON ITK.idmg = ITM.idkoryg
WHERE IT.id IS NULL;
SET @return_status = @@error;
IF @return_status <> 0
RETURN @return_status;
END;
GO
IF OBJECT_ID('[HM].[IntrastatCardIndexView]','V') IS NOT NULL DROP VIEW [HM].[IntrastatCardIndexView];
GO
CREATE VIEW [HM].[IntrastatCardIndexView]
AS
Select Distinct
it.id as Id,
mg.id as IdMg,
mg.kod as DocNumber,
mg.datasp as PeriodDate,
tw.nazwa as Name,
tw.kod as Kod,
tw.pcn as KodCN,
ISNULL(step.Title, ' ') as CountryP,
ISNULL(stew.Title, ' ') as CountryW,
Cast(IT.transakcja as Int) as KindTrans,
Case When mg.subtyp in (76,85,84,76,78) Then 'wywóz'
When mg.subtyp in (77,88, 75,81) And pw.ilosc >= 0 Then 'wywóz'
When mg.subtyp in (77,88, 75,81) And pw.ilosc < 0 Then 'przywóz'
When mg.subtyp in (74,83,82,75,89,81) Then 'przywóz' End as KindIntrastat,
ABS(pw.ilosc) as CountTW,
Case When mg.iddokkoryg Is Not Null And ABS(pw.ilosc) = 0 And mg.subtyp in (74,83,82,74,75,89,81) Then FORMAT(mg2.data,'yyyy-MM') Else FORMAT(mg.data,'yyyy-MM') End DateToIntrastat,
Round(ABS(pw.ilosc)*ISNULL(NULLIF(tw.przelkg,0),0),2) as WeightNetto,
ISNULL(Round(ABS(ISNULL(pw.ilosc*(dp.wartNetto/NULLIF(dp.ilosc,0)), ROUND(((mz.iloscwp * mz.cena + mz.wartk)/NULLIF(mz.ilosc,0))*pw.ilosc,2))),2),0) as ValueInvoice,
tw.jmuzup as JmComplete,
Round(ABS(pw.ilosc) * ISNULL(NULLIF(tw.przeluz,0),0),2) as QuantityCompleteJm
from hm.it
Left Join hm.mz on mz.id = it.idmg
Left Join hm.dw on dw.id = it.iddw
Left Join hm.mg on mg.id = mz.super
Left Join hm.mg as mg2 on mg.iddokkoryg = mg2.id
Left Join hm.tw on tw.id = mz.idtw
Left Join hm.pw on pw.iddkmg = mg.id and pw.idmg = mz.id and pw.iddw = it.iddw and pw.typ = 37
Left Join SSCommon.STElements as step on step.ElementId = it.idKrajuPo
Left Join SSCommon.STElements as stew on stew.ElementId = it.idKrajuWy
Left Join hm.op OpMz on OpMz.owner_id = mz.id
Left Join hm.ro on ro.id1 = OpMz.owner_id
Left Join hm.op OpDp on OpDp.id = ro.id1
Left Join hm.dp on dp.id = Opdp.owner_id
INNER JOIN HM.AppUserWarehouses as ur on ur.WarehouseId = mg.magazyn
INNER JOIN HM.DocumentTypePermission as dtp on dtp.DocumentTypeId = mg.subtyp and dtp.UserId=ur.UserId
Where stew.Shortcut <>'PL'
GO
IF OBJECT_ID('[HM].[hm_DW_PowiazaniaDostawRezerwacji]', 'P') IS NOT NULL DROP PROCEDURE [HM].[hm_DW_PowiazaniaDostawRezerwacji]
GO
CREATE PROCEDURE HM.hm_DW_PowiazaniaDostawRezerwacji
@rez int
AS
declare @tblPWR TABLE
(id int primary key not NULL)
insert @tblPWR
select PW.id from PW with (NOLOCK, index (IDX_PW_IDMG_TYP_IDDW)) where PW.typ = 26 and PW.idmg = @rez
declare @tblZZ2 TABLE
(id1 int primary key, id2 int, ilosc float)
insert @tblZZ2
select ZZ.id1, min(ZZ.id2) as id2, sum(ZZ.ilosc) as ilosc
from ZZ with (NOLOCK, index(IDX_ZZ_TYP_ID2)) where ZZ.typ = 96 and ZZ.baza1 = 36 and ZZ.id1 in (select id from @tblPWR)
group by ZZ.id1
declare @tblZZ1 TABLE
(id1 int, id2 int primary key, ilosc float)
insert @tblZZ1
select min(ZZ.id1) as id1, ZZ.id2, sum(ZZ.ilosc) + sum(PW.ilosc) as ilosc
from ZZ WITH (NOLOCK, INDEX(IDX_ZZ_TYP_ID2)) join PW on ZZ.id1 = PW.id where PW.typ = 26 and ZZ.typ = 96 and ZZ.baza2 = 36 and ZZ.id2 in (select id from @tblPWR)
group by ZZ.id2
select PW.id, PW.flag, PW.typ, PW.iddkmg, PW.idmg, PW.iddw, PW.idtw, PW.ilosc, PW.wartosc, PW.data, PW.idkoryg,
DW.id, DW.kod, DW.numer, DW.idtw, DW.data, DW.ilosc, DW.iloscdosp, DW.wartoscdosp,
ISNULL(ZZ2.ilosc, 0.0), ISNULL(ZZ2.id2, 0), ISNULL(ZZ1.ilosc, 0.0), ISNULL(ZZ1.id1, 0), DW.krajPochodzenia
from PW WITH (NOLOCK) join @tblPWR as PWR on PW.id = PWR.id
join DW on DW.id = PW.iddw
left join @tblZZ2 as ZZ2 on ZZ2.id1 = PW.id
left join @tblZZ1 as ZZ1 on ZZ1.id2 = PW.id
GO
IF EXISTS (SELECT count(*) FROM [HM].[INIFILE] WHERE plik = 'FIRMA.HM' AND sekcja = 'Intrastat')
BEGIN
IF EXISTS (SELECT * FROM [HM].[INIFILE] WHERE plik = 'FIRMA.HM' AND sekcja = 'Intrastat' AND klucz IN (8, 9, 10))
BEGIN
INSERT INTO [HM].[INIFILE] VALUES (
'FIRMA.HM',
'Intrastat',
12,
(SELECT napis FROM HM.INIFILE WHERE plik = 'FIRMA.HM' AND sekcja = 'Intrastat' AND klucz = 8))
INSERT INTO [HM].[INIFILE] VALUES (
'FIRMA.HM',
'Intrastat',
13,
(SELECT napis FROM HM.INIFILE WHERE plik = 'FIRMA.HM' AND sekcja = 'Intrastat' AND klucz = 9))
INSERT INTO [HM].[INIFILE] VALUES (
'FIRMA.HM',
'Intrastat',
14,
(SELECT napis FROM HM.INIFILE WHERE plik = 'FIRMA.HM' AND sekcja = 'Intrastat' AND klucz = 10))
END
END
GO
IF OBJECT_ID('[HM].[dbo_WarehouseDeliveriesView]','V') IS NOT NULL DROP VIEW [hm].[dbo_WarehouseDeliveriesView]
GO
CREATE VIEW [HM].[dbo_WarehouseDeliveriesView]
AS
SELECT d.[id] AS Id
,d.[kod] AS Name
,d.[krajPochodzenia] AS CountryOfOriginId
,d.[data] AS Date
,d.[idtw] AS ProductId
,d.[magazyn] AS InventoryId
,CAST(IIF(d.[typ] = 0, 1, 0) AS BIT) -- dostawa wyczerpana
AS IsExhausted
,CAST(IIF(d.[bufor] > 0, 0, 1) AS BIT) -- dostawa z dokumentu wystawionego
AS IsIssued
,CAST(d.[cena] AS DECIMAL(24,6))
AS Price
,CAST(d.[stan] AS DECIMAL(24,4)) -- stan - ilość
AS StockQuantity
,CAST(d.[wartoscst] AS DECIMAL(24,2)) -- stan wartość
AS StockValue
,CAST(d.[iloscdosp] AS DECIMAL(24,4)) -- ilość dostępna (stan - ilość zarezerwowana)
AS AvailableQuantity
,CAST(d.[wartoscdosp] AS DECIMAL(24,2)) -- wartość dostępna
AS AvailableValue
,CAST(IIF(d.[numer] < 0, 1, 0) AS BIT) -- rekord rezerwacji ilościowej
AS IsQuantityReservation
,ISNULL(guid,NEWID()) AS guid
FROM [HM].[DW] d
GO
IF OBJECT_ID('[HM].[SaveDocumentToBuffer]', 'P') IS NOT NULL DROP PROCEDURE [HM].[SaveDocumentToBuffer]
GO
CREATE PROCEDURE [HM].[SaveDocumentToBuffer]
(
@documentBaseType INT,
@id_doc INT,
@documentData AS XML,
@documentDataIn AS XML,
@documentType AS BIT, -- 1 rozchodowy (fvs, wz, zo) -- 0 przychód (zw,pz,zakup)
@currentUser AS INT,
@crc VARBINARY(200),
@documentid INT OUT
)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @ErMessage NVARCHAR(2048),
@ErSeverity INT,
@ErState INT;
/* Sage.PL.HandelBusinessLayer.Enums.DocumentBaseTypeEnum
Sale = 0,
Purchase = 1,
Store = 2,
Payment = 3,
ForeignOrder = 4,
OwnOrder = 5
*/
--Deklaracje zmiennych potrzebnych do zapisu dokumentu
DECLARE @id_dok INT,
@id_tran INT;
DECLARE @isOk BIT;
DECLARE @docGuid UNIQUEIDENTIFIER;
CREATE TABLE [#tblDTOSaleHeader]
(
[Id] INT,
[BaseType] INT,
[DepartmentId] INT,
[DocumentTypeId] INT,
[DocumentNuerationId] INT,
[KindId] INT,
[CatalogId] INT,
[Tag] INT,
[DocumentNumber] NVARCHAR(100),
[DefaultSellingPriceTypeId] INT,
[IsGross] BIT,
[CurrencyRate] DECIMAL(24, 8),
[IssueDate] DATE,
[SaleDate] DATE,
[InRegisterDate] DATE,
[Deadline] DATE,
[ContractorId] INT,
[PaymentRegisterId] INT,
[PaymentFormId] INT,
[ContractorCode] NVARCHAR(200),
[DepartmentCode] NVARCHAR(100),
[DocumentNumerationCode] NVARCHAR(100),
[DocumentNumerationId] INT,
[DocumentTypeCode] NVARCHAR(100),
[PaymentRegisterCode] NVARCHAR(100),
[ReceivedBy] NVARCHAR(40),
[ReceiverId] INT,
[PaymentFormName] NVARCHAR(100),
[PaymentFormDate] INT,
[IsDeliveryDataFromContractor] BIT,
[ReceiverCode] NVARCHAR(200),
[TableId] INT,
[SellingPriceTypeId] INT,
[AmountToPay] DECIMAL(24, 2),
[IsIssued] BIT,
[DocumentStatus] INT,
[ValueNet] DECIMAL(24, 2),
[ValueVat] DECIMAL(24, 2),
[ValueNetInCurrency] DECIMAL(24, 2),
[ValueGrossInCurrency] DECIMAL(24, 2),
[IsCanceled] BIT,
[CalculateVatMethod] SMALLINT,
[FromFiscalReceipt] BIT,
[SplitPayment] BIT,
[JpkV7] varchar(256),
[IsSettled] BIT,
[AccountingPattern] NVARCHAR(100),
[ReservationType] INT,
[PublishInventoryDocumentMode] SMALLINT,
[Description] NVARCHAR(80),
[Guid] UNIQUEIDENTIFIER
);
CREATE TABLE [#tblDTOSalePositions]
(
[Id] INT,
[ProductId] INT,
[ProductCode] NVARCHAR(200),
[ProductDescription] NVARCHAR(200),
[ProductName] NVARCHAR(200),
[UnitOfMeasurement] NVARCHAR(20),
[Quantity] DECIMAL(24, 4),
[EvidenceUnitOfMeasurement] NVARCHAR(20),
[EvidenceQuantity] DECIMAL(24, 4),
[PriceTypeId] INT,
[PriceListPriceInCurrency] DECIMAL(24, 4),
[Price] DECIMAL(24, 4),
[DiscountPercent] DECIMAL(24, 4),
[DiscountAmountInCurrency] DECIMAL(24, 4),
[ValueGross] DECIMAL(24, 2),
[ValueVat] DECIMAL(24, 2),
[ValueNet] DECIMAL(24, 2),
[VatRateId] INT,
[OrdinalNumber] INT,
[VatRegister] INT,
[ReverseCharge] BIT,
[AccountingPattern] NVARCHAR(100),
[ReservationType] SMALLINT,
[PKWiU] NVARCHAR(200),
[Guid] UNIQUEIDENTIFIER,
[JPK_V7] NVARCHAR(10)
);
CREATE TABLE [#tblDTOSaleContractorAddressData]
(
[Name] NVARCHAR(400),
[Street] NVARCHAR(400),
[HouseNumber] VARCHAR(300),
[FlatNumber] NVARCHAR(100),
[NIP] VARCHAR(100),
[PostalCode] VARCHAR(100),
[City] VARCHAR(100),
[CountrySymbol] NVARCHAR(100),
[AddressName] NVARCHAR(200)
);
CREATE TABLE [#tblDTOSaleReceiverAddressData]
(
[Name] NVARCHAR(400),
[Street] NVARCHAR(400),
[HouseNumber] VARCHAR(300),
[FlatNumber] NVARCHAR(100),
[NIP] VARCHAR(100),
[PostalCode] VARCHAR(100),
[City] VARCHAR(100),
[CountrySymbol] NVARCHAR(100),
[AddressName] NVARCHAR(200)
);
CREATE TABLE [#tblDTOForeignOrderHeader]
(
[Id] INT,
[BaseType] INT,
[DepartmentId] INT,
[DocumentTypeId] INT,
[DocumentNuerationId] INT,
[KindId] INT,
[CatalogId] INT,
[Tag] INT,
[DocumentNumber] NVARCHAR(100),
[DefaultSellingPriceTypeId] INT,
[IsGross] BIT,
[Currency] NVARCHAR(3),
[CurrencyRate] DECIMAL(24, 8),
[IssueDate] DATE,
[SaleDate] DATE,
[Deadline] DATE,
[ContractorId] INT,
[PaymentRegisterId] INT,
[PaymentFormId] INT,
[ContractorCode] NVARCHAR(200),
[DepartmentCode] NVARCHAR(100),
[DocumentNumerationCode] NVARCHAR(100),
[DocumentNumerationId] INT,
[DocumentTypeCode] NVARCHAR(100),
[PaymentRegisterCode] NVARCHAR(100),
[ReceivedBy] NVARCHAR(40),
[ReceiverId] INT,
[PaymentFormName] NVARCHAR(100),
[PaymentFormDate] INT,
[IsDeliveryDataFromContractor] BIT,
[ReceiverCode] NVARCHAR(200),
[TableId] INT,
[SellingPriceTypeId] INT,
[AmountToPay] DECIMAL(24, 2),
[IsIssued] BIT,
[DocumentStatus] INT,
[ValueNet] DECIMAL(24, 2),
[ValueVat] DECIMAL(24, 2),
[ValueNetInCurrency] DECIMAL(24, 2),
[ValueGrossInCurrency] DECIMAL(24, 2),
[IsCanceled] BIT,
[CalculateVatMethod] SMALLINT,
[FromFiscalReceipt] BIT,
[IsSettled] BIT,
[AccountingPattern] NVARCHAR(100),
[ReservationType] INT,
[Description] NVARCHAR(80),
[Guid] UNIQUEIDENTIFIER,
[SplitPayment] BIT
);
CREATE TABLE [#tblDTOForeignOrderPositions]
(
[Id] INT,
[ProductId] INT,
[ProductCode] NVARCHAR(200),
[ProductDescription] NVARCHAR(200),
[ProductName] NVARCHAR(200),
[UnitOfMeasurement] NVARCHAR(20),
[Quantity] DECIMAL(24, 4),
[EvidenceUnitOfMeasurement] NVARCHAR(20),
[EvidenceQuantity] DECIMAL(24, 4),
[PriceTypeId] INT,
[PriceListPriceInCurrency] DECIMAL(24, 4),
[Price] DECIMAL(24, 4),
[DiscountPercent] DECIMAL(24, 4),
[DiscountAmountInCurrency] DECIMAL(24, 4),
[ValueGross] DECIMAL(24, 2),
[ValueVat] DECIMAL(24, 2),
[ValueNet] DECIMAL(24, 2),
[VatRateId] INT,
[OrdinalNumber] INT,
[ReverseCharge] BIT,
[ReservationType] SMALLINT,
[Guid] UNIQUEIDENTIFIER
);
CREATE TABLE [#tblDTOForeignOrderContractorAddressData]
(
[Name] NVARCHAR(400),
[Street] NVARCHAR(400),
[HouseNumber] VARCHAR(300),
[FlatNumber] NVARCHAR(100),
[NIP] VARCHAR(100),
[PostalCode] VARCHAR(100),
[City] VARCHAR(100),
[CountrySymbol] NVARCHAR(100),
[AddressName] NVARCHAR(200)
);
CREATE TABLE [#tblDTOForeignOrderReceiverAddressData]
(
[Name] NVARCHAR(400),
[Street] NVARCHAR(400),
[HouseNumber] VARCHAR(300),
[FlatNumber] NVARCHAR(100),
[NIP] VARCHAR(100),
[PostalCode] VARCHAR(100),
[City] VARCHAR(100),
[CountrySymbol] NVARCHAR(100),
[AddressName] NVARCHAR(200)
);
CREATE TABLE [#tblDTOInventoryDocumentHeader]
(
[Id] INT,
[BaseType] INT,
--, [DepartmentId] int
[DocumentTypeId] INT,
[DocumentNuerationId] INT,
[KindId] INT,
[CatalogId] INT,
[Tag] INT,
[DocumentNumber] NVARCHAR(100),
[DefaultSellingPriceTypeId] INT,
[IsGross] BIT,
[CurrencyRate] DECIMAL(24, 8),
[IssueDate] DATE,
[OperationDate] DATE,
[Deadline] DATE,
[ContractorId] INT,
[ContractorCode] NVARCHAR(200),
[InventoryCode] NVARCHAR(100),
[InventoryId] INT,
[DocumentNumerationCode] NVARCHAR(100),
[DocumentNumerationId] INT,
[DocumentTypeCode] NVARCHAR(100),
[ReceivedBy] NVARCHAR(40),
[IsDeliveryDataFromContractor] BIT,
[TableId] INT,
[IsIssued] BIT,
[DocumentStatus] INT,
[AccountingPattern] NVARCHAR(100),
[ReservationType] INT,
[Description] NVARCHAR(80),
[Guid] UNIQUEIDENTIFIER
);
CREATE TABLE [#tblDTOInventoryDocumentPositions]
(
[id] INT,
OrdinalNumber INT,
ProductId INT,
ProductCode NVARCHAR(200),
ProductDescription NVARCHAR(400),
ProductName NVARCHAR(400),
UnitOfMeasurement NVARCHAR(24),
EvidenceUnitOfMeasurement NVARCHAR(24),
Price DECIMAL(24, 6),
Quantity DECIMAL(24, 4),
EvidenceQuantity DECIMAL(24, 4),
ValueNet DECIMAL(38, 6),
DeliveryName VARCHAR(100),
ReservationType SMALLINT,
AccountingPattern VARCHAR(20),
SettlementMethod SMALLINT,
[Guid] UNIQUEIDENTIFIER
);
CREATE TABLE [#tblDTOInventoryContractorAddressData]
(
[Name] NVARCHAR(400),
[Street] NVARCHAR(400),
[HouseNumber] VARCHAR(300),
[FlatNumber] NVARCHAR(100),
[NIP] VARCHAR(100),
[PostalCode] VARCHAR(100),
[City] VARCHAR(100),
[CountrySymbol] NVARCHAR(100),
[AddressName] NVARCHAR(200)
);
CREATE TABLE [#InventoryPositionSettlementDTO]
(
ID INT,
WarehouseDeliveryId INT,
InventoryPositionId INT,
OperationDate DATE,
Quantity DECIMAL(24, 8),
Value DECIMAL(24, 8),
OriginalQuantity DECIMAL(24, 8),
OriginalValue DECIMAL(24, 8),
WarehouseDeliveryIId INT,
WarehouseDeliveryName NVARCHAR(200),
WarehouseDeliveryCountryOfOriginId INT,
WarehouseDeliveryDate DATE,
WarehouseDeliveryIsExhausted BIT,
WarehouseDeliveryIsIssued BIT,
WarehouseDeliveryPrice DECIMAL(24, 4),
WarehouseDeliveryStockQuantity DECIMAL(24, 4),
WarehouseDeliveryStockValue DECIMAL(24, 4),
WarehouseDeliveryAvailableQuantity DECIMAL(24, 4),
WarehouseDeliveryAvailableValue DECIMAL(24, 4),
WarehouseDeliveryIsQuantitativeReservation BIT,
WarehouseDeliveryInventoryId INT,
InventoryPositionGuid UNIQUEIDENTIFIER,
ProductId INT,
WarehouseDeliveryGuid UNIQUEIDENTIFIER
);
--sprawdzamy jakiego typu jest dokument
-- Na razie skupimy się na sprzedaży ;)
IF (ISNULL(@id_doc, 0) = 0) -- nowy dokument
BEGIN
IF (@documentBaseType = 0) -- dokumenty sprzedaży
BEGIN
--Krok 1 - rozkodowujemy xml do tabel tymczasowych
BEGIN TRY
EXEC HM.SaveSaleDocumentXMLToTempTables @DocumentData = @documentData; -- xml
END TRY
BEGIN CATCH
THROW;
--RAISERROR('Błąd przy próbie rozkodowania XML', 16, 1);
END CATCH;
BEGIN
-- sprawdzamy nie obsługiwane przypadki
EXEC HM.CheckSaleDocumentConditions @isOk = @isOk OUTPUT; -- bit
IF @isOk = 0
SET @id_dok = 0;
ELSE
BEGIN
BEGIN TRY
EXEC HM.SaveSaleDocumentHeader @currentUser=@currentUser, @id_dok = @id_dok OUTPUT, -- int
@id_tran = @id_tran OUTPUT; -- int
END TRY
BEGIN CATCH
THROW; --RAISERROR('Błąd przy próbie zapisu nagłówka', 16, 1);
END CATCH;
BEGIN TRY
EXEC HM.SaveSaleDocumentPositions @id_dok, @id_tran;
END TRY
BEGIN CATCH
THROW; --RAISERROR('Błąd przy próbie zapisu pozycji', 16, 1);
END CATCH;
BEGIN TRY
EXEC HM.RecalculateSaleDocument @id_dok = @id_dok, @id_tran = @id_tran; -- int
END TRY
BEGIN CATCH
THROW; --RAISERROR('Błąd przy próbie przeliczania dokumentu', 16, 1);
END CATCH;
BEGIN TRY
EXECUTE HM.SerializedSaleDocumentAdditionalFieldsSave @documentData,
@id_dok;
END TRY
BEGIN CATCH
THROW; --RAISERROR('Błąd przy próbie zapisu pól dodatkowych nagłowka dokumentu', 16, 1);
END CATCH;
BEGIN TRY
EXECUTE HM.SerializedSaleDocumentSalePositionAdditionalFieldsSave @documentData,
@id_dok;
END TRY
BEGIN CATCH
THROW; --RAISERROR('Błąd przy próbie zapisu pól dodatkowych pozycji dokumentu', 16, 1);
END CATCH;
END;
END;
SET @documentid = @id_dok;
SELECT @docGuid = [Guid]
FROM #tblDTOSaleHeader;
END;
IF (@documentBaseType = 2) -- dokumenty magazynowe /*Release to WZ - receive to POZ
BEGIN
BEGIN TRY
EXEC HM.SaveInventoryDocumentXMLToTempTables @DocumentData = @documentData; -- xml
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN
EXEC HM.CheckInventoryDocumentConditions @isOk = @isOk OUTPUT; -- bit
IF @isOk = 0
SET @id_dok = 0;
ELSE
BEGIN
IF (@documentType = 1)
BEGIN
BEGIN TRY
EXEC HM.SaveInventoryDocumentHeader @currentUser=@currentUser, @id_dok = @id_dok OUTPUT, -- int
@id_tran = @id_tran OUTPUT; -- int
END TRY
BEGIN CATCH
THROW; --RAISERROR('Błąd przy próbie zapisu nagłówka', 16, 1);
END CATCH;
BEGIN TRY
EXEC HM.SaveInventoryDocumentPositions @id_dok = @id_dok, -- int
@id_tran = @id_tran; -- int
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.[RecalculateInventoryDocument] @id_dok, @id_tran;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.SerializedInventoryDocumentAdditionalFieldsSave @documentData,
@id_dok;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.SerializedInventoryDocumentPositionAdditionalFieldsSave @documentData,
@id_dok;
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
IF (@documentType = 0)
BEGIN
BEGIN TRY
EXEC HM.SaveInventoryReceiveDocumentHeader @currentUser=@currentUser, @id_dok = @id_dok OUTPUT, -- int
@id_tran = @id_tran OUTPUT; -- int
END TRY
BEGIN CATCH
THROW; --RAISERROR('Błąd przy próbie zapisu nagłówka', 16, 1);
END CATCH;
BEGIN TRY
EXEC HM.SaveInventoryReceiveDocumentPositions @id_dok = @id_dok, -- int
@id_tran = @id_tran; -- int
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.[RecalculateInventoryReceiveDocument] @id_dok, @id_tran;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.SerializedInventoryDocumentAdditionalFieldsSave @documentData,
@id_dok;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.SerializedInventoryDocumentPositionAdditionalFieldsSave @documentData,
@id_dok;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.SaveSuppliesXMLToTempTables @DocumentData = @documentData; -- xml
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.SaveSuppliesForDocument @id_dok = @id_dok; -- int
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.[SerializedInventoryDeliveryDocumentPositionAdditionalFieldsSave] @documentData,
@id_dok;
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
END;
END;
SET @documentid = @id_dok;
SELECT @docGuid = [Guid]
FROM #tblDTOInventoryDocumentHeader;
END;
IF (@documentBaseType = 4)
BEGIN
BEGIN TRY
EXEC HM.SaveForeignOrderXMLToTempTables @DocumentData = @documentData;
END TRY
BEGIN CATCH
--SELECT @ErMessage = N'Błąd przy próbie rozkodowania xml zamówienia obcego :' + ERROR_MESSAGE(),
-- @ErSeverity = ERROR_SEVERITY(),
-- @ErState = ERROR_STATE();
--RAISERROR(@ErMessage, @ErSeverity, @ErState);
THROW;
END CATCH;
BEGIN
EXEC HM.CheckForeignOrderDocumentConditions @isOk = @isOk OUTPUT;
IF @isOk = 0
SET @id_dok = 0;
ELSE
BEGIN
BEGIN TRY
EXEC HM.SaveForeignOrderDocumentHeader @currentUser=@currentUser, @id_dok = @id_dok OUTPUT, -- int
@id_tran = @id_tran OUTPUT; -- int
END TRY
BEGIN CATCH
--SELECT @ErMessage = N'Błąd przy próbie zapisania nagłówka zamówienia :' + ERROR_MESSAGE(),
-- @ErSeverity = ERROR_SEVERITY(),
-- @ErState = ERROR_STATE();
--RAISERROR(@ErMessage, @ErSeverity, @ErState);
THROW;
END CATCH;
BEGIN TRY
EXECUTE HM.SaveForeignOrderDocumentPositions @id_dok, @id_tran;
END TRY
BEGIN CATCH
--SELECT @ErMessage = N'Błąd przy próbie zapisania pozycji zamówienia :' + ERROR_MESSAGE(),
-- @ErSeverity = ERROR_SEVERITY(),
-- @ErState = ERROR_STATE();
--RAISERROR(@ErMessage, @ErSeverity, @ErState);
THROW;
END CATCH;
BEGIN TRY
EXEC [HM].[RecalculateForeignOrderDocument] @id_dok, @id_tran;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC [HM].[SerializedForeignOrderDocumentAdditionalFieldsSave] @documentData,
@id_dok;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC [HM].[SerializedForeignOrderDocumentSalePositionAdditionalFieldsSave] @documentData,
@id_dok;
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
END;
SET @documentid = @id_dok;
SELECT @docGuid = [Guid]
FROM #tblDTOForeignOrderHeader;
END;
--jak wszystko się udało to spróbujemy zapisać do historii operacji xml
INSERT INTO HM.XmlHistory
(
documentBaseType,
id_doc,
documentData,
documentDataIn,
documentType,
id_doc_out,
Operation_description,
Crc
)
VALUES
( @documentBaseType, -- documentBaseType - int
@id_doc, -- id_doc - int
@documentData, -- documentData - xml
@documentDataIn,
@documentType, -- documentType - bit
@documentid, 'Nowy dokument',@crc);
END;
ELSE
-- jak jest id to update
BEGIN
IF (@documentBaseType = 0) -- dokumenty sprzedaży
BEGIN
BEGIN TRY
EXEC HM.SaveSaleDocumentXMLToTempTables @DocumentData = @documentData; -- xml
END TRY
BEGIN CATCH
--RAISERROR('Bład przy próbie rozkodowania XML ', 16, 1);
THROW;
END CATCH;
BEGIN
-- sprawdzamy nie obsługiwane przypadki
EXEC HM.CheckSaleDocumentConditions @isOk = @isOk OUTPUT; -- bit
IF @isOk = 0
SET @id_doc = 0;
ELSE
BEGIN
BEGIN TRY
EXEC HM.UpdateSaleDocumentHeader @currentUser,@id_doc;
END TRY
BEGIN CATCH
SELECT @ErMessage = N'Błąd przy próbie aktualizacji danych nagłówka :' + ERROR_MESSAGE(),
@ErSeverity = ERROR_SEVERITY(),
@ErState = ERROR_STATE();
RAISERROR(@ErMessage, @ErSeverity, @ErState);
--THROW;
END CATCH;
BEGIN TRY
EXEC HM.UpdateSaleDocumentPositions @id_doc;
END TRY
BEGIN CATCH
--RAISERROR('Błąd przy próbie aktualizacji danych pozycji',16,1);
THROW;
END CATCH;
BEGIN TRY
SELECT @id_tran = id
FROM HM.TR
WHERE owner_id = @id_doc
AND owner_base = 16 AND typ = 1;
EXEC HM.[RecalculateUpdatedSaleDocument] @id_doc, @id_tran;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXECUTE HM.SerializedSaleDocumentAdditionalFieldsSave @documentData,
@id_doc;
END TRY
BEGIN CATCH
THROW; --RAISERROR('Błąd przy próbie zapisu pól dodatkowych nagłowka dokumentu', 16, 1);
END CATCH;
BEGIN TRY
EXECUTE HM.SerializedSaleDocumentSalePositionAdditionalFieldsSave @documentData,
@id_doc;
END TRY
BEGIN CATCH
THROW; --RAISERROR('Błąd przy próbie zapisu pól dodatkowych pozycji dokumentu', 16, 1);
END CATCH;
END;
END;
SET @documentid = @id_doc;
SELECT @docGuid = [Guid]
FROM #tblDTOSaleHeader;
END;
IF (@documentBaseType = 2)
BEGIN
BEGIN TRY
EXEC HM.SaveInventoryDocumentXMLToTempTables @DocumentData = @documentData; -- xml
END TRY
BEGIN CATCH
-- RAISERROR('Bład przy próbie rozkodowania XML ', 16, 1);
THROW;
END CATCH;
BEGIN
EXEC HM.CheckInventoryDocumentConditions @isOk = @isOk OUTPUT;
IF @isOk = 0
SET @id_doc = 0;
ELSE
BEGIN
IF (@documentType = 1) --przychod
BEGIN
BEGIN TRY
EXEC HM.[UpdateInventoryReleaseDocumentHeader] @currentUser, @id_doc;
END TRY
BEGIN CATCH
RAISERROR('err', 16, 1);
END CATCH;
BEGIN TRY
EXEC HM.UpdateInventoryReleaseDocumentPositions @id_doc;
END TRY
BEGIN CATCH
THROW;
END CATCH;
SELECT @id_tran = id
FROM HM.TR
WHERE owner_id = @id_doc
AND owner_base = 33 AND typ = 1;
--EXEC HM.RecalculateUpdatedInventoryReceiveDocument @id_doc, @id_tran;
EXEC HM.RecalculateUpdatedInventoryReleaseDocument @id_doc, @id_tran;
BEGIN TRY
EXEC HM.SerializedInventoryDocumentAdditionalFieldsSave @documentData,
@id_doc;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.SerializedInventoryDocumentPositionAdditionalFieldsSave @documentData,
@id_doc;
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
IF (@documentType = 0) --rozchod
BEGIN
BEGIN TRY
EXEC HM.[UpdateInventoryReceiveDocumentHeader] @currentUser,@id_doc;
END TRY
BEGIN CATCH
RAISERROR('err', 16, 1);
END CATCH;
EXEC HM.[UpdateInventoryReceiveDocumentPositions] @id_doc;
SELECT @id_tran = id
FROM HM.TR
WHERE owner_id = @id_doc
AND owner_base = 33 AND typ = 1;
--EXEC HM.RecalculateUpdatedInventoryReleaseDocument @id_doc, @id_tran;
EXEC HM.RecalculateUpdatedInventoryReceiveDocument @id_doc, @id_tran;
BEGIN TRY
EXEC HM.SerializedInventoryDocumentAdditionalFieldsSave @documentData,
@id_doc;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.SerializedInventoryDocumentPositionAdditionalFieldsSave @documentData,
@id_doc;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.SaveSuppliesXMLToTempTables @DocumentData = @documentData; -- xml
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.UpadateSuppliesForDocument @id_doc = @id_doc; -- int
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.[SerializedInventoryDeliveryDocumentPositionAdditionalFieldsSave] @documentData,
@id_doc;
END TRY
BEGIN CATCH
THROW;
END CATCH;
--SET @id_doc = 0;
END;
END;
END;
SET @documentid = @id_doc;
SELECT @docGuid = [Guid]
FROM #tblDTOInventoryDocumentHeader;
END;
IF (@documentBaseType = 4)
BEGIN
BEGIN TRY
EXEC HM.SaveForeignOrderXMLToTempTables @DocumentData = @documentData; -- xml
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN
EXEC HM.CheckForeignOrderDocumentConditions @isOk = @isOk OUTPUT;
IF @isOk = 0
SET @id_doc = 0;
ELSE
BEGIN
BEGIN TRY
EXEC HM.[UpdateForeignOrderDocumentHeader] @currentUser,@id_doc;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXEC HM.[UpdateForeignOrderDocumentPositions] @id_doc;
END TRY
BEGIN CATCH
THROW;
END CATCH;
SELECT @id_tran = id
FROM HM.TR
WHERE owner_id = @id_doc
AND owner_base = 45 AND typ = 0;
BEGIN TRY
EXEC HM.RecalculateUpdatedForeignOrderDocument @id_doc, @id_tran;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXECUTE HM.[SerializedForeignOrderDocumentAdditionalFieldsSave] @documentData,
@id_doc;
END TRY
BEGIN CATCH
THROW;
END CATCH;
BEGIN TRY
EXECUTE HM.[SerializedForeignOrderDocumentSalePositionAdditionalFieldsSave] @documentData,
@id_doc;
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
END;
SET @documentid = @id_doc;
SELECT @docGuid = [Guid]
FROM #tblDTOForeignOrderHeader;
END;
--jak wszystko się udało to spróbujemy zapisać do historii operacji xml
INSERT INTO HM.XmlHistory
(
documentBaseType,
id_doc,
documentData,
documentDataIn,
documentType,
id_doc_out,
Operation_description,
Crc
)
VALUES
( @documentBaseType, -- documentBaseType - int
@id_doc, -- id_doc - int
@documentData, -- documentData - xml
@documentDataIn,
@documentType, -- documentType - bit
@documentid, 'Edycja dokumentu',@crc);
END;
-- Ustawiamy status dokumentu w rezerwacjach na "zapisany"
IF (@documentid <> 0 AND @documentType = 1)
BEGIN
EXEC [HM].[hm_UpdateReservationStatus] @docGuid, 1;
END;
--czyścimy śmieci
IF (OBJECT_ID('tempdb..[#tblDTOSaleHeader]') IS NOT NULL)
DROP TABLE #tblDTOSaleHeader;
IF (OBJECT_ID('tempdb..#tblDTOSalePositions') IS NOT NULL)
DROP TABLE #tblDTOSalePositions;
IF (OBJECT_ID('tempdb..#tblDTOSaleContractorAddressData') IS NOT NULL)
DROP TABLE #tblDTOSaleContractorAddressData;
IF (OBJECT_ID('tempdb..#tblDTOSaleReceiverAddressData') IS NOT NULL)
DROP TABLE #tblDTOSaleReceiverAddressData;
IF (OBJECT_ID('tempdb..#temp_positions') IS NOT NULL)
DROP TABLE #temp_positions;
IF (OBJECT_ID('tempdb..[#tblDTOForeignOrderHeader]') IS NOT NULL)
DROP TABLE #tblDTOForeignOrderHeader;
IF (OBJECT_ID('tempdb..#tblDTOForeignOrderPositions') IS NOT NULL)
DROP TABLE #tblDTOForeignOrderPositions;
IF (OBJECT_ID('tempdb..#tblDTOForeignOrderContractorAddressData') IS NOT NULL)
DROP TABLE #tblDTOForeignOrderContractorAddressData;
IF (OBJECT_ID('tempdb..#tblDTOForeignOrderReceiverAddressData') IS NOT NULL)
DROP TABLE #tblDTOForeignOrderReceiverAddressData;
IF (OBJECT_ID('tempdb..#temp_positions') IS NOT NULL)
DROP TABLE #temp_positions;
IF (OBJECT_ID('tempdb..#tblDTOInventoryDocumentHeader') IS NOT NULL)
DROP TABLE [#tblDTOInventoryDocumentHeader];
IF (OBJECT_ID('tempdb..#tblDTOInventoryDocumentPositions') IS NOT NULL)
DROP TABLE [#tblDTOInventoryDocumentPositions];
IF (OBJECT_ID('tempdb..#tblDTOInventoryContractorAddressData') IS NOT NULL)
DROP TABLE [#tblDTOInventoryContractorAddressData];
IF (OBJECT_ID('tempdb..[#InventoryPositionSettlementDTO]') IS NOT NULL)
DROP TABLE #InventoryPositionSettlementDTO;
--rozgłaszamy wszem i wobec że udało nam się czego nie zepsuć i dodaliśmy dokument ;)
IF (@documentid <> 0)
BEGIN
INSERT INTO HM.LOG_BASE
(
baseName,
rec_id,
operation,
term
)
VALUES
( 'DK', -- baseName - varchar(19)
@id_dok, -- rec_id - int
3, -- operation - smallint
@@spid -- term - int
);
END;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
--jak się nie udało to spróbujemy zapisać do historii operacji xml
INSERT INTO HM.XmlHistory
(
documentBaseType,
id_doc,
documentData,
documentDataIn,
documentType,
id_doc_out,
Operation_description,
Crc
)
VALUES
( @documentBaseType, -- documentBaseType - int
@id_doc, -- id_doc - int
@documentData, -- documentData - xml
@documentDataIn,
@documentType, -- documentType - bit
@documentid, 'ERROR',@crc);
THROW;
END;
END CATCH;
END;
GO
IF OBJECT_ID('[HM].[SaveSuppliesXMLToTempTables]', 'P') IS NOT NULL DROP PROCEDURE [HM].[SaveSuppliesXMLToTempTables]
GO
CREATE PROCEDURE hm.SaveSuppliesXMLToTempTables
(@DocumentData AS XML)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @handle INT,
@PrepareXmlStatus INT;
--rozbieramy xml do tabel
EXEC @PrepareXmlStatus = sp_xml_preparedocument @handle OUTPUT,
@DocumentData;
INSERT INTO #InventoryPositionSettlementDTO
SELECT ID,
WarehouseDeliveryId,
InventoryPositionId,
CAST(OperationDate AS DATE) AS OperationDate,
Quantity,
Value,
OriginalQuantity,
OriginalValue,
WarehouseDeliveryIId,
WarehouseDeliveryName,
WarehouseDeliveryCountryOfOriginId,
CAST(WarehouseDeliveryDate AS DATE) AS WarehouseDeliveryDate,
WarehouseDeliveryIsExhausted,
WarehouseDeliveryIsIssued,
WarehouseDeliveryPrice,
WarehouseDeliveryStockQuantity,
WarehouseDeliveryStockValue,
WarehouseDeliveryAvailableQuantity,
WarehouseDeliveryAvailableValue,
WarehouseDeliveryIsQuantitativeReservation,
WarehouseDeliveryInventoryId,
InventoryPositionGuid,
ProductId,
WarehouseDeliveryGuid
FROM
OPENXML(@handle,
'/InventoryDocumentDTO/Positions/InventoryPositionDTO/InventoryPositionSettlements/InventoryPositionSettlementDTO'
)
WITH
(
ID INT 'Id',
WarehouseDeliveryId INT 'WarehouseDeliveryId',
InventoryPositionId INT 'InventoryPositionId',
OperationDate DATETIMEOFFSET 'OperationDate',
Quantity DECIMAL(24, 8) 'Quantity',
Value DECIMAL(24, 8) 'Value',
OriginalQuantity DECIMAL(24, 8) 'OriginalQuantity',
OriginalValue DECIMAL(24, 8) 'OriginalValue',
WarehouseDeliveryIId INT 'WarehouseDelivery/Id',
WarehouseDeliveryName NVARCHAR(200) 'WarehouseDelivery/Name',
WarehouseDeliveryCountryOfOriginId INT 'WarehouseDelivery/CountryOfOriginId',
WarehouseDeliveryDate DATETIMEOFFSET 'WarehouseDelivery/Date',
WarehouseDeliveryIsExhausted BIT 'WarehouseDelivery/IsExhausted',
WarehouseDeliveryIsIssued BIT 'WarehouseDelivery/IsIssued',
WarehouseDeliveryPrice DECIMAL(24, 4) 'WarehouseDelivery/Price',
WarehouseDeliveryStockQuantity DECIMAL(24, 4) 'WarehouseDelivery/StockQuantity',
WarehouseDeliveryStockValue DECIMAL(24, 4) 'WarehouseDelivery/StockValue',
WarehouseDeliveryAvailableQuantity DECIMAL(24, 4) 'WarehouseDelivery/AvailableQuantity',
WarehouseDeliveryAvailableValue DECIMAL(24, 4) 'WarehouseDelivery/AvailableValue',
WarehouseDeliveryIsQuantitativeReservation BIT 'WarehouseDelivery/IsQuantitativeReservation',
WarehouseDeliveryInventoryId INT 'WarehouseDelivery/InventoryId',
InventoryPositionGuid UNIQUEIDENTIFIER '../../Guid',
ProductId INT '../../ProductId',
WarehouseDeliveryGuid UNIQUEIDENTIFIER 'WarehouseDelivery/Guid'
);
EXEC sp_xml_removedocument @handle;
END;
GO
IF OBJECT_ID('[HM].[UpadateSuppliesForDocument]', 'P') IS NOT NULL DROP PROCEDURE [HM].[UpadateSuppliesForDocument]
GO
CREATE PROCEDURE [HM].[UpadateSuppliesForDocument]
(@id_doc INT)
AS
BEGIN
DECLARE @numerDW INT;
SELECT @numerDW = ISNULL(MAX(numer), 0) + 1
FROM HM.DW;
MERGE INTO HM.DW AS target
USING
(
SELECT dost.id,
mag.khid,
temp.WarehouseDeliveryId,
temp.InventoryPositionId,
temp.OperationDate,
temp.Quantity,
temp.Value,
temp.OriginalQuantity,
temp.OriginalValue,
temp.WarehouseDeliveryIId,
temp.WarehouseDeliveryName,
temp.WarehouseDeliveryCountryOfOriginId,
temp.WarehouseDeliveryDate,
temp.WarehouseDeliveryIsExhausted,
temp.WarehouseDeliveryIsIssued,
temp.WarehouseDeliveryPrice,
temp.WarehouseDeliveryStockQuantity,
temp.WarehouseDeliveryStockValue,
temp.WarehouseDeliveryAvailableQuantity,
temp.WarehouseDeliveryAvailableValue,
temp.WarehouseDeliveryIsQuantitativeReservation,
temp.WarehouseDeliveryInventoryId,
temp.ProductId,
temp.WarehouseDeliveryGuid
FROM #InventoryPositionSettlementDTO temp
LEFT JOIN HM.DW dost
ON dost.id = temp.WarehouseDeliveryIId
LEFT JOIN HM.MG mag
ON mag.id = @id_doc
) AS source
ON target.id = source.id
AND target.iddkpz = @id_doc
WHEN MATCHED THEN
UPDATE SET target.ilosc = source.Quantity,
target.cena = source.WarehouseDeliveryPrice,
target.magazyn = source.WarehouseDeliveryInventoryId,
target.idtw = source.ProductId,
target.kod = source.WarehouseDeliveryName,
target.data = source.WarehouseDeliveryDate,
target.krajPochodzenia = source.WarehouseDeliveryCountryOfOriginId
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
flag,
subtyp,
typ,
kod,
iddw,
iddkpz,
idkh,
iddkzk,
numer,
idtw,
data,
ilosc,
iloscdosp,
cena,
wartoscdosp,
magazyn,
iloscPz,
stan,
wartoscst,
bufor,
krajPochodzenia,
Guid
)
VALUES
(0, 0, 0, source.WarehouseDeliveryName, NULL, @id_doc, source.khid, NULL, @numerDW,
source.ProductId, CONVERT(DATE, source.WarehouseDeliveryDate), source.Quantity, 0, source.WarehouseDeliveryPrice, 0,
source.WarehouseDeliveryInventoryId, 0, 0, 0, 1,source.WarehouseDeliveryCountryOfOriginId, source.WarehouseDeliveryGuid);
--WHEN NOT MATCHED BY SOURCE AND target.iddkpz = @id_doc THEN
-- DELETE;
WITH cte AS
(
SELECT poz.id,poz.iddkpz,numer,hm.GetNextNumberInSupplies(poz.idtw)+ROW_NUMBER() OVER (PARTITION BY poz.idtw ORDER BY poz.guid) AS numer_nowy FROM hm.dw poz WHERE poz.iddkpz=@id_doc
)
UPDATE cte
SET numer=cte.numer_nowy
WHERE cte.iddkpz=@id_doc
--teraz sobie posprzątamy w powiązaniach z DW
DECLARE @flag SMALLINT = 0;
SET @flag = @flag + 0x08 + 0x10 + 0x04;
MERGE INTO HM.PW AS target
USING
(
SELECT mag.subtyp AS docsubtyp,
dost.id AS iddwnew,
poz.id AS idpoz,
pow.id,
pow.flag,
pow.subtyp,
pow.typ,
pow.iddkmg,
pow.idmg,
pow.iddw,
pow.idtw,
pow.ilosc,
pow.wartosc,
pow.ilosczreal,
pow.idkoryg,
pow.data,
temp.WarehouseDeliveryId,
temp.InventoryPositionId,
temp.OperationDate,
temp.Quantity,
temp.Value,
temp.OriginalQuantity,
temp.OriginalValue,
temp.WarehouseDeliveryIId,
temp.WarehouseDeliveryName,
temp.WarehouseDeliveryCountryOfOriginId,
temp.WarehouseDeliveryDate,
temp.WarehouseDeliveryIsExhausted,
temp.WarehouseDeliveryIsIssued,
temp.WarehouseDeliveryPrice,
temp.WarehouseDeliveryStockQuantity,
temp.WarehouseDeliveryStockValue,
temp.WarehouseDeliveryAvailableQuantity,
temp.WarehouseDeliveryAvailableValue,
temp.WarehouseDeliveryIsQuantitativeReservation,
temp.WarehouseDeliveryInventoryId,
temp.InventoryPositionGuid,
temp.ProductId,
temp.WarehouseDeliveryGuid
FROM #InventoryPositionSettlementDTO temp
LEFT JOIN HM.PW pow
ON pow.iddw = temp.WarehouseDeliveryIId
LEFT JOIN HM.MG mag
ON mag.id = @id_doc
LEFT JOIN HM.MZ poz
ON poz.guid = temp.InventoryPositionGuid
AND poz.super = @id_doc
LEFT JOIN HM.DW dost
ON dost.Guid = temp.WarehouseDeliveryGuid
) AS source
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET target.subtyp = source.docsubtyp,
target.idtw = source.ProductId,
target.ilosc = source.Quantity * -1,
target.wartosc = source.Value * -1
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
flag,
subtyp,
typ,
iddkmg,
idmg,
iddw,
idtw,
ilosc,
wartosc,
ilosczreal,
idkoryg,
data
)
VALUES
(@flag, source.docsubtyp, 39, @id_doc, source.idpoz, source.iddwnew, source.ProductId,
source.Quantity * -1, source.Value * -1, 0.0, NULL, CONVERT(DATE, source.WarehouseDeliveryDate))
;
-- DELETE FROM hm.dw WHERE id NOT IN (SELECT WarehouseDeliveryIId FROM #InventoryPositionSettlementDTO) AND iddkpz=@id_doc
DELETE FROM hm.pw WHERE idmg NOT IN (SELECT id FROM hm.mz WHERE super=@id_doc) AND iddkmg=@id_doc;
DELETE FROM hm.dw WHERE iddkpz=@id_doc AND guid NOT IN (SELECT WarehouseDeliveryGuid FROM #InventoryPositionSettlementDTO);
END;
GO
CREATE PROCEDURE [HM].[ChangeOriginCountryDW]
@CoutryId INT,
@List varchar(MAX)
AS
BEGIN
UPDATE DW SET krajPochodzenia = @CoutryId
FROM [HM].[fn_string_na_tabele] (@List) DWID
INNER JOIN HM.DW ON DWID.item = DW.id
UPDATE IT SET idKrajuPo = @CoutryId
FROM [HM].[fn_string_na_tabele] (@List) DWID
INNER JOIN HM.IT ON IT.iddw = DWID.item
END
GO
IF OBJECT_ID('[HM].[SaveSuppliesForDocument]', 'P') IS NOT NULL DROP PROCEDURE [HM].[SaveSuppliesForDocument]
GO
CREATE PROCEDURE [HM].[SaveSuppliesForDocument]
(@id_dok INT)
AS
BEGIN
INSERT INTO HM.DW
(
flag,
subtyp,
typ,
kod,
iddw,
iddkpz,
idkh,
iddkzk,
numer,
idtw,
data,
ilosc,
iloscdosp,
cena,
wartoscdosp,
magazyn,
iloscPz,
stan,
wartoscst,
bufor,
guid,
krajPochodzenia
)
SELECT 0 AS flag,
0 AS subtyp,
0 AS typ,
WarehouseDeliveryName AS kod,
NULL AS iddw,
@id_dok AS iddkpz,
mag.khid AS idkh,
NULL AS iddkzk,
hm.GetNextNumberInSupplies(poz.idtw)+ROW_NUMBER() OVER (PARTITION BY poz.idtw ORDER BY poz.id) AS numer,
ProductId AS idtw,
CONVERT(DATE, WarehouseDeliveryDate) AS data,
Quantity AS ilosc,
0 AS iloscdosp,
WarehouseDeliveryPrice AS cena,
0 AS wartoscdosp,
WarehouseDeliveryInventoryId AS magazyn,
0 AS iloscPz,
0 AS stan,
0 AS wartoscst,
1 AS bufor,
WarehouseDeliveryGuid AS guid,
settlementDTO.WarehouseDeliveryCountryOfOriginId
FROM #InventoryPositionSettlementDTO settlementDTO
LEFT JOIN HM.MG mag
ON mag.id = @id_dok
LEFT JOIN HM.MZ poz
ON poz.super = mag.id
AND poz.guid = settlementDTO.InventoryPositionGuid;
DECLARE @flag SMALLINT = 0;
SET @flag = @flag + 0x08 + 0x10 + 0x04;
INSERT INTO HM.PW
(
flag,
subtyp,
typ,
iddkmg,
idmg,
iddw,
idtw,
ilosc,
wartosc,
ilosczreal,
idkoryg,
data
)
SELECT @flag AS flag, -- flag - smallint
dok.subtyp AS subtyp, -- subtyp - smallint
39 AS typ, -- typ - smallint
dok.id AS iddkmg, -- iddkmg - int
poz.id AS idmg, -- idmg - int
dost.id, -- iddw - int
poz.idtw, -- idtw - int
settlementDTO.Quantity * -1 AS ilosc, -- ilosc - float
settlementDTO.Value * -1 AS wartosc, -- wartosc - float
0.0, -- ilosczreal - float
NULL, -- idkoryg - int
CONVERT(DATE, WarehouseDeliveryDate) -- data - datetime
FROM #InventoryPositionSettlementDTO settlementDTO
JOIN HM.MG dok
ON dok.id = @id_dok
JOIN HM.MZ poz
ON poz.super = dok.id
AND poz.guid = settlementDTO.InventoryPositionGuid
JOIN HM.DW dost
ON dost.guid = settlementDTO.WarehouseDeliveryGuid;
END;
GO
IF EXISTS(SELECT * FROM sys.key_constraints where [name] like 'UC_IT_IDMG' and parent_object_id = OBJECT_ID(N'HM.IT'))
ALTER TABLE [HM].[IT] DROP CONSTRAINT [UC_IT_IDMG]
GO
-- Akutalizacja kraju dostaw
UPDATE DW SET krajPochodzenia = COALESCE(DW.krajPochodzenia, IT.idKrajuPo)
FROM HM.IT
INNER JOIN HM.MZ MZ ON MZ.id = IT.idmg
INNER JOIN HM.PW PW ON PW.idmg = MZ.id AND PW.ilosc <= 0
INNER JOIN HM.DW DW ON DW.id = PW.iddw
GO
UPDATE DW SET krajPochodzenia = COALESCE(DW.krajPochodzenia, KS.ElementId)
FROM HM.DW
INNER JOIN SSCommon.STContractors KH ON KH.Id = DW.idkh
INNER JOIN SSCommon.STPostOfficeAddresses AD ON KH.ContactGuid = AD.ContactGuid AND AddressName = 'adres domyślny'
INNER JOIN SSCommon.STElements KS ON KS.Shortcut = AD.Country
WHERE DW.idkh IS NOT NULL AND DW.krajPochodzenia IS NULL
GO
UPDATE DW SET krajPochodzenia = COALESCE(DW.krajPochodzenia, KS.ElementId)
FROM HM.DW
INNER JOIN HM.DW DW2 ON DW2.id = DW.iddw AND DW2.idkh IS NOT NULL
INNER JOIN SSCommon.STContractors KH ON KH.Id = DW2.idkh
INNER JOIN SSCommon.STPostOfficeAddresses AD ON KH.ContactGuid = AD.ContactGuid AND AddressName = 'adres domyślny'
INNER JOIN SSCommon.STElements KS ON KS.Shortcut = AD.Country
WHERE DW.iddw IS NOT NULL AND DW.krajPochodzenia IS NULL
GO
UPDATE DW SET krajPochodzenia = COALESCE(DW.krajPochodzenia, DW2.krajPochodzenia)
FROM HM.DW DW
INNER JOIN HM.MG ON MG.id = DW.iddkpz
INNER JOIN HM.TR ON MG.id = TR.owner_id
INNER JOIN HM.PO ON PO.id1 = TR.id
INNER JOIN HM.TR TR2 ON PO.id2 = TR2.id
INNER JOIN HM.MG MG2 ON TR2.owner_id = MG2.id
INNER JOIN HM.PW ON PW.iddkmg = MG2.id
INNER JOIN HM.DW DW2 ON DW2.id = PW.iddw
WHERE DW.krajPochodzenia IS NULL
GO
UPDATE HM.IT SET it.iddw = dw.id
FROM HM.IT
JOIN HM.MZ ON it.idmg = mz.id
JOIN HM.PW ON pw.idmg =mz.id And pw.typ = 37
JOIN HM.DW ON dw.id = pw.iddw
GO
IF EXISTS (SELECT * FROM sys.objects WHERE [type] IN (N'P', N'PC') AND OBJECT_ID = OBJECT_ID(N'[Common].[UpdateContractorFromXML]'))
DROP PROCEDURE [Common].[UpdateContractorFromXML];
GO
-- Procedura umożliwiająca zapisanie danych kontrahenta z postaci XML
CREATE PROCEDURE [Common].[UpdateContractorFromXML] (@contractorData XML, @moduleCode nvarchar(255), @contextInfo varchar(128), @contextInfoP varchar(128))
AS
BEGIN
SET NOCOUNT ON; -- Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
SET XACT_ABORT ON; -- Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
--Ustawienie CONTEXT_INFO z danymi potrzebnymi trigerom do zapisu historii zmian w tabelach z sufiksem 'Rep'
declare @ContextInfoBinary as varbinary(128)
SET @ContextInfoBinary = convert(varbinary(128), @contextInfo)
SET CONTEXT_INFO @ContextInfoBinary;
declare @ContextInfoBinaryP as varbinary(128)
SET @ContextInfoBinaryP = convert(varbinary(128), @contextInfoP)
BEGIN TRY
BEGIN TRANSACTION;
-- tabela tymczasowa na dane kontrahenta z xml
CREATE TABLE [#tblContractor]
(
[Id] INT,
[Guid] UNIQUEIDENTIFIER,
[Name] NVARCHAR(150),
[Shortcut] NVARCHAR(50),
[Nip] NVARCHAR(20),
[NipUE] NVARCHAR(20),
[Pesel] NVARCHAR(15),
[Regon] NVARCHAR(20),
[LinkedUnit] BIT,
[PersonKind] TINYINT,
[VATPayerActive] BIT,
[VIES] BIT,
-- exAttribute
[RequestMppTransfers] NVARCHAR(10), -- splitPayment
[PaymentFormId] INT, -- formaPlatnosci
[PayRegistryId] INT, -- rejestr
[SaleKindId] INT, -- rodzajCeny
[PriceTypeId] INT, -- typCeny
[AccountantParameter] NVARCHAR(20), -- parametrFK
[IdFk] INT, -- idFK
[PositionFk] INT, -- pozycja FK
-- additional data
[CatalogId] NVARCHAR(100),
[ContractorKindId] INT,
[MarkerId] INT,
[Status] BIT,
-- sale conditions
[IsLimitActive] BIT,
[LimitCurrency] NVARCHAR(20),
[LimitValue] FLOAT,
[PriceNegotiation] BIT,
-- main person
[FirstName] NVARCHAR(50),
[Surname] NVARCHAR(50)
)
-- tabela tymczasowa na dane kontaktowe kontrahenta z xml
CREATE TABLE [#tblContact]
(
[Id] INT,
[Guid] UNIQUEIDENTIFIER,
[FirstName] NVARCHAR(50),
[Surname] NVARCHAR(50),
[Email] NVARCHAR(100),
[Facebook] NVARCHAR(100),
[Fax] NVARCHAR(50),
[Telephone1] NVARCHAR(50),
[Telephone2] NVARCHAR(50),
[Telex] NVARCHAR(50),
[Website] NVARCHAR(100)
)
-- tabela tymczasowa na dane adresu domyślnego kontrahenta z xml
CREATE TABLE [#tblMainAddress]
(
[Id] INT,
[Guid] UNIQUEIDENTIFIER,
[Name] NVARCHAR(100),
[Post] NVARCHAR(50),
[PostalCode] NVARCHAR(10),
[Place] NVARCHAR(50),
[Street] NVARCHAR(50),
[HouseNo] NVARCHAR(15),
[ApartmentNo] NVARCHAR(15),
[Province] NVARCHAR(50),
[Country] NVARCHAR(50)
)
-- tabela tymczasowa na dane adresu korespondencyjnego kontrahenta z xml
CREATE TABLE [#tblCorespondenceAddress]
(
[Id] INT,
[Guid] UNIQUEIDENTIFIER,
[Name] NVARCHAR(100),
[Post] NVARCHAR(50),
[PostalCode] NVARCHAR(10),
[Place] NVARCHAR(50),
[Street] NVARCHAR(50),
[HouseNo] NVARCHAR(15),
[ApartmentNo] NVARCHAR(15),
[Province] NVARCHAR(50),
[Country] NVARCHAR(50)
)
-- tabela tymczasowa na dane handlowe kontrahenta z xml
CREATE TABLE [#tblTradeData]
(
[DiscountPercent] FLOAT,
[Receivables] FLOAT,
[Liabilities] FLOAT
)
-- tabela tymczasowa na pola z rachunkami bankowymi
CREATE TABLE [#tblBankAccounts]
(
[Id] INT,
[Guid] UNIQUEIDENTIFIER,
[AccountNo] NVARCHAR(50),
[AccountName] NVARCHAR(100),
[IsDefault] BIT,
[IsActive] BIT,
[WhiteList] BIT,
[WhiteListDate] DATETIME2,
[SWIFT_BIC] VARCHAR(11),
[BankName] NVARCHAR(100),
-- extended attributes
[IsElectronic] BIT,
[IsVatAccount] BIT
)
-- tabela tymczasowa na pola z rachunkami bankowymi
CREATE TABLE [#tblCustomFields]
(
[FieldName] NVARCHAR(100),
[Value] NVARCHAR(MAX),
[ColumnName] NVARCHAR(128),
[Type] NVARCHAR(250)
)
-- tabela tymczasowa na ElementExAttributes
CREATE TABLE [#ExtAttributes]
(
[ElementId] INT,
[Name] NVARCHAR(50),
[StringValue] NVARCHAR(1024),
[OriginalCLRType] NVARCHAR(400),
[ElementExAttrId] INT IDENTITY(1,1),
[Guid] UNIQUEIDENTIFIER,
[Title] NVARCHAR(200),
[AccessFlag] BIT,
[InheritedFlag] BIT
)
DECLARE @extAttElementId int
DECLARE @extAttTitle nvarchar (200)
DECLARE @extAttActive bit
DECLARE @extAttAccountNo nvarchar (40)
DECLARE @extAttDescription nvarchar (4000)
DECLARE @extAttShortcut nvarchar (100)
DECLARE @extAttPosition int
DECLARE @extAttExtAttributes nvarchar(max)
DECLARE @extAttExtObjVer int
DECLARE @extAttExtWriteAction int
DECLARE @extAttExtOtherThenOriginal smallint
-- przygotowanie xml
DECLARE @handle INT, @prepareXmlStatus INT
EXEC @prepareXmlStatus = sp_xml_preparedocument @handle OUTPUT, @contractorData
-- deserializacja danych kontaktowych z xml
INSERT INTO [#tblContact]
SELECT
ISNULL([Id], 0), CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
[FirstName], [SurName], [Email], [Facebook], [Fax], [Telephone1], [Telephone2], [Telex], [Website]
FROM
OPENXML(@handle, '/Contractor/ContactPerson')
WITH
(
[Id] INT 'Id',
[Guid] NVARCHAR(36) 'Guid',
[FirstName] NVARCHAR(50) 'FirstName',
[Surname] NVARCHAR(50) 'SurName',
[Email] NVARCHAR(100) 'Email',
[Facebook] NVARCHAR(100) 'Facebook',
[Fax] NVARCHAR(50) 'Fax',
[Telephone1] NVARCHAR(50) 'Telephone1',
[Telephone2] NVARCHAR(50) 'Telephone2',
[Telex] NVARCHAR(50) 'Tlf',
[Website] NVARCHAR(100) 'Website'
)
-- wyczyszczenie pustych elementów
DELETE FROM [#tblContact]
WHERE [FirstName] = '' AND [Surname] = '' AND [Email] = '' AND [Facebook] = '' AND [Fax] = '' AND [Telephone1] = '' AND [Telephone2] = '' AND [Telex] = '' AND [Website] = ''
AND [Guid] = '00000000-0000-0000-0000-000000000000' --dla nowych kontaktów, dla istniejących chcemy wyczyścić pola w bazie
--select * from #tblContact
-- deserializacja danych adresu domyślnego z xml
INSERT INTO [#tblMainAddress]
SELECT
ISNULL([Id], 0),
CASE WHEN [Guid] IS NULL OR [Guid] = '' THEN '00000000-0000-0000-0000-000000000000' ELSE [GUID] END,
[Name], [Post], [PostalCode], [Place], [Street], [HouseNo], [ApartmentNo], [Province], [Country]
FROM
OPENXML(@handle, '/Contractor/MainAddress')
WITH
(
[Id] INT 'Id',
[Guid] NVARCHAR(36) 'Guid',
[Name] NVARCHAR(100) 'Name',
[Post] NVARCHAR(50) 'Post',
[PostalCode] NVARCHAR(10) 'PostalCode',
[Place] NVARCHAR(50) 'Place',
[Street] NVARCHAR(50) 'Street',
[HouseNo] NVARCHAR(15) 'HouseNo',
[ApartmentNo] NVARCHAR(15) 'ApartmentNo',
[Province] NVARCHAR(50) 'Province',
[Country] NVARCHAR(50) 'Country'
)
-- wyczyszczenie pustych elementów
DELETE FROM [#tblMainAddress]
WHERE [Name] = '' AND [Post] = '' AND [PostalCode] = '' AND [Place] = '' AND [Street] = '' AND [HouseNo] = '' AND [ApartmentNo] = '' AND [Province] = '' AND [Country] = ''
--select * from [#tblMainAddress]
-- deserializacja danych adresu korespondencyjnego z xml
INSERT INTO [#tblCorespondenceAddress]
SELECT
ISNULL([Id], 0), CASE WHEN [Guid] IS NULL OR [Guid] = '' THEN '00000000-0000-0000-0000-000000000000' ELSE [GUID] END,
[Name], [Post], [PostalCode], [Place], [Street], [HouseNo], [ApartmentNo], [Province], [Country]
FROM
OPENXML(@handle, '/Contractor/CorespondenceAddress')
WITH
(
[Id] INT 'Id',
[Guid] NVARCHAR(36) 'Guid',
[Name] NVARCHAR(100) 'Name',
[Post] NVARCHAR(50) 'Post',
[PostalCode] NVARCHAR(10) 'PostalCode',
[Place] NVARCHAR(50) 'Place',
[Street] NVARCHAR(50) 'Street',
[HouseNo] NVARCHAR(15) 'HouseNo',
[ApartmentNo] NVARCHAR(15) 'ApartmentNo',
[Province] NVARCHAR(50) 'Province',
[Country] NVARCHAR(50) 'Country'
)
-- wyczyszczenie pustych elementów
DELETE FROM [#tblCorespondenceAddress]
WHERE [Name] = '' AND [Post] = '' AND [PostalCode] = '' AND [Place] = '' AND [Street] = '' AND [HouseNo] = '' AND [ApartmentNo] = '' AND [Province] = '' AND [Country] = ''
--select * from [#tblCorespondenceAddress]
-- deserializacja danych handlowych z xml
INSERT INTO [#tblTradeData]
SELECT
[DiscountPercent], [Receivables], [Liabilities]
FROM
OPENXML(@handle, '/Contractor')
WITH
(
[DiscountPercent] FLOAT 'DiscountPercent',
[Receivables] FLOAT 'Receivables',
[Liabilities] FLOAT 'Liabilities'
)
DELETE FROM [#tblTradeData]
WHERE [DiscountPercent] = 0 AND [Receivables] = 0 AND [Liabilities] = 0
--select * from [#tblTradeData]
-- deserializacja danych rachunków bankowych z xml
INSERT INTO [#tblBankAccounts]
SELECT
[Id],CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
[AccountNo], [AccountName], [IsDefault], [IsActive],
CASE WHEN [WhiteList] IS NULL OR [WhiteList] = '' THEN NULL ELSE (CASE WHEN [WhiteList] = 'true' THEN 1 ELSE 0 END) END [WhiteList],
CASE WHEN [WhiteListDate] IS NULL OR [WhiteListDate] = '' THEN NULL ELSE cast([WhiteListDate] as DATETIME2) END [WhiteListDate],
[SWIFT_BIC], [BankName],
-- extended attributes
[IsElectronic], [IsVatAccount]
FROM
OPENXML(@handle, '/Contractor/BankAccounts/BankAccount')
WITH
(
[Id] INT 'Id',
[Guid] NVARCHAR(36) 'Guid',
[AccountNo] NVARCHAR(50) 'AccountNo',
[AccountName] NVARCHAR(100) 'AccountName',
[IsDefault] BIT 'IsDefault',
[IsActive] BIT 'IsActive',
[WhiteList] NVARCHAR(5) 'IsInTaxPayerRegistry',
[WhiteListDate] NVARCHAR(40) 'VerificationDate',
[SWIFT_BIC] VARCHAR(11) 'SwiftBicNumber',
[BankName] NVARCHAR(100) 'BankName',
-- extended attributes
[IsElectronic] BIT 'IsElectronic',
[IsVatAccount] BIT 'IsVatAccount'
)
DELETE FROM [#tblBankAccounts]
WHERE ([AccountNo] IS NULL OR [AccountNo] = '') AND ([AccountName] IS NULL OR [AccountName] = '') AND ([IsDefault] IS NULL OR [IsDefault] = 0) AND ([IsActive] IS NULL OR [IsActive] = 0)
AND [WhiteList] = NULL AND [WhiteListDate] = NULL AND ([SWIFT_BIC] IS NULL OR [SWIFT_BIC] = '') AND ([BankName] IS NULL OR [BankName] = '')
AND ([IsElectronic] IS NULL OR [IsElectronic] = 0)AND ([IsVatAccount] IS NULL OR [IsVatAccount] = 0)
--select * from [#tblBankAccounts]
-- deserializacja danych kontrahenta z xml
INSERT INTO [#tblContractor]
SELECT
[Id], CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
[Name], [Shortcut], [Nip], [NipUE], [Pesel], [Regon], [LinkedUnit],
(CASE WHEN [PersonKind] LIKE 'Osoba fizyczna (firma)' THEN 1 ELSE (CASE WHEN [PersonKind] = 'Osoba fizyczna' THEN 2 ELSE 0 END) END) AS [Subject],
[VATPayerActive], [VIES],
-- exAttribute
[RequestMppTransfers], [PaymentFormId], [PayRegistryId], [SaleKindId], [PriceTypeId], [AccountantParameter], [IdFk], [PositionFk],
-- additional data
[CatalogId], [ContractorKindId], [MarkerId], (CASE WHEN [Status] IS NULL OR [Status] LIKE '' OR [Status] LIKE 'Aktywny' THEN 1 ELSE 0 END) AS [Status],
-- sale conditions
[IsLimitActive], [LimitCurrency], [LimitValue], [PriceNegotiation],
-- main person
[FirstName], [Surname]
FROM
OPENXML(@handle, '/Contractor')
WITH
(
[Id] INT 'Id',
[Guid] NVARCHAR(36) 'Guid',
[Name] NVARCHAR(150) 'Name',
[Shortcut] NVARCHAR(50) 'Shortcut',
[Nip] NVARCHAR(20) 'Nip',
[NipUE] NVARCHAR(20) 'NipUE',
[Pesel] NVARCHAR(15) 'Pesel',
[Regon] NVARCHAR(20) 'Regon',
[LinkedUnit] BIT 'LinkedUnit',
[PersonKind] NVARCHAR(100) 'Subject',
[VATPayerActive] BIT 'VatPayerActive',
[VIES] BIT 'Vies',
-- exAttribute
[RequestMppTransfers] NVARCHAR(10) 'RequestMppTransfers', -- splitPayment
[PaymentFormId] INT 'PaymentFormId', -- formaPlatnosci
[PayRegistryId] INT 'PayRegistryId', -- rejestr
[SaleKindId] INT 'SaleKindId', -- rodzajCeny
[PriceTypeId] INT 'PriceTypeId', -- typCeny
[AccountantParameter] NVARCHAR(20) 'AccountantParameter', -- parametrFK
[IdFk] INT 'IdFk', -- idFK
[PositionFk] INT 'PositionFk', -- pozycja FK
-- additional data
[CatalogId] NVARCHAR(100) 'CatalogId',
[ContractorKindId] INT 'ContractorKindId',
[MarkerId] INT 'MarkerId',
[Status] NVARCHAR(10) 'Status',
-- sale conditions
[IsLimitActive] BIT 'IsLimitActive',
[LimitCurrency] NVARCHAR(20) 'LimitCurrency',
[LimitValue] FLOAT 'LimitValue',
[PriceNegotiation] BIT 'PriceNegotiation',
-- main person
[FirstName] NVARCHAR(50) 'FirstName',
[Surname] NVARCHAR(50) 'SurName'
)
--select * from [#tblContractor]
-- ustawienie domyślnego znacznika; wpp. bug 195961
UPDATE [#tblContractor]
SET [MarkerId] = (SELECT [ElementId] FROM [SSCommon].[STElements] WHERE [Guid] = 'D205CFCC-1AF4-4E6B-919C-9AE3A2192F81')
WHERE 0 = ISNULL([MarkerId], 0);
-- deserializacja pól własnych kontrahenta z xml
DECLARE @classificationTableGuid UNIQUEIDENTIFIER
DECLARE @classificationTableName NVARCHAR(255)
SELECT
@classificationTableName = ISNULL([TableNamePrefix], '') + [TableName],
@classificationTableGuid = [Guid]
FROM [SSCommon].[STClasifications]
WHERE [TypeTable] LIKE 'Contractors' AND [Owner] = @moduleCode
IF @classificationTableName IS NULL -- jeżeli nie pobrano na podstawie kodu modułu to ustaw nazwę wspólnej tabeli kontrahentów
SET @classificationTableName = 'STContractorsClassification'
DECLARE @customFieldsDefinitions TABLE([ColumnName] NVARCHAR(128),[Symbol] NVARCHAR(128),[Type] NVARCHAR(250))
INSERT INTO @customFieldsDefinitions
SELECT
[Common].[GetNormalizedColumnNameForCustomField](dim.[Symbol]),
[Common].[GetNormalizedSymbolForCustomField](dim.[Symbol]),
dim.[DimCLRType]
from [SSCommon].[STClasifications] clas
INNER JOIN [SSCommon].[STClasificationDims] clasDim ON clasDim.[ClasificationsDefId] = clas.[ClasificationsDefId]
INNER JOIN [SSCommon].[STDimensions] dim ON dim.[Id] = clasDim.[DimensionsId]
where clas.[Guid] like @classificationTableGuid
--SELECT * FROM @customFieldsDefinitions
-- czy są zdefiniowane pola własne
IF EXISTS (SELECT * FROM @customFieldsDefinitions)
BEGIN
SELECT * INTO #temporaryContractorData
FROM OPENXML(@handle, '/Contractor')
;WITH documentFields as (
SELECT c0.[parentid] AS [Id], c0.[localname] AS [FieldName], c1.[text] AS [Value]
FROM #temporaryContractorData c0
INNER JOIN #temporaryContractorData c1 ON c0.[id] = c1.[parentid]
WHERE c0.[parentid] = 0 AND c0.[nodetype] = 1 AND c1.[nodetype] = 3 -- nodetype int Identifies the node type. 1 = Element node, 2 = Attribute node, 3 = Text node
)
INSERT INTO [#tblCustomFields]
SELECT customFieldDefinitions.[Symbol] AS [FieldName], documentFields.[Value] AS [Value], customFieldDefinitions.[ColumnName] AS [ColumnName], customFieldDefinitions.[Type] AS [Type]
FROM documentFields
RIGHT OUTER JOIN @customFieldsDefinitions customFieldDefinitions ON customFieldDefinitions.[Symbol] = documentFields.[FieldName]
IF (OBJECT_ID('tempdb..#temporaryContractorData') IS NOT NULL) DROP TABLE #temporaryContractorData
--SELECT * FROM [#tblCustomFields]
END
DECLARE @contractorId INT, @contractorGuid UNIQUEIDENTIFIER
SELECT @contractorId = [Id], @contractorGuid = CASE WHEN [Guid] IS NULL OR [Guid] LIKE '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [Guid] END FROM [#tblContractor]
DECLARE @contactId INT
DECLARE @contactGuid UNIQUEIDENTIFIER
SET @contactGuid = NULL
DECLARE @bankingInfoGuid UNIQUEIDENTIFIER
SET @bankingInfoGuid = NULL
IF ((@contactId IS NULL OR @contactId = 0) AND @contractorId IS NOT NULL AND @contractorId <> 0)
BEGIN
SELECT
@contactGuid = ctr.[ContactGuid],
@contactId = ct.[Id],
@bankingInfoGuid = [BankingInfoGuid]
FROM [SSCommon].[STContractors] ctr
INNER JOIN [SSCommon].[STContacts] ct ON ct.[Guid] = ctr.[ContactGuid]
WHERE ctr.[Id] = @contractorId
END
-- zapis informacji do tabeli z danymi kontaktowymi
-- tabela tymczasowa na TeleItContacts
CREATE TABLE [#TeleItContacts]
(
[Id] INT IDENTITY(1,1),
[Guid] UNIQUEIDENTIFIER,
[Name] NVARCHAR(50),
[ContactGuid] UNIQUEIDENTIFIER,
[Telephone1] NVARCHAR(50),
[Telephone2] NVARCHAR(50),
[Telephone3] NVARCHAR(50),
[Fax] NVARCHAR(50),
[Fax2] NVARCHAR(50),
[Telex] NVARCHAR(50),
[Email1] NVARCHAR(100),
[Email2] NVARCHAR(100),
[WWW] NVARCHAR(100),
[Communicator1] NVARCHAR(50),
[Communicator2] NVARCHAR(50),
[Description] NVARCHAR(3000),
[Facebook] NVARCHAR(100)
)
-- tabela tymczasowa na PostOfficeAddresses
CREATE TABLE [#PostOfficeAddresses]
(
[Id] INT IDENTITY(1,1),
[AddressName] NVARCHAR(64),
[Guid] UNIQUEIDENTIFIER,
[Street] NVARCHAR(50),
[Commune] NVARCHAR(50),
[District] NVARCHAR(50),
[Place] NVARCHAR(50),
[PostCode] NVARCHAR(10),
[PostOffice] NVARCHAR(50),
[ApartmentNo] NVARCHAR(15),
[HouseNo] NVARCHAR(15),
[ContactGuid] UNIQUEIDENTIFIER,
[Province] NVARCHAR(50),
[Country] NVARCHAR(50),
[PostOfficeBox] NVARCHAR(50)
)
DECLARE @contactName NVARCHAR(100) = ''
DECLARE @contactMainPostOfficeAddress UNIQUEIDENTIFIER
DECLARE @contactMainTeleITContact UNIQUEIDENTIFIER
DECLARE @contactPostOfficeAddresses NVARCHAR(max)
DECLARE @contactTeleItContacts NVARCHAR(max)
DECLARE @contactWriteAction INT = 0
IF EXISTS (SELECT * FROM [#tblContact])
BEGIN
IF (@contactId IS NULL OR @contactId = 0) -- nowy kontakt
BEGIN
IF (@contactGuid IS NULL OR @contactGuid LIKE '00000000-0000-0000-0000-000000000000')
SELECT @contactGuid = CASE WHEN [Guid] IS NOT NULL AND [Guid] NOT LIKE '00000000-0000-0000-0000-000000000000' THEN [Guid] ELSE NEWID() END FROM [#tblContact]
SELECT
@contactName = '',
@contactMainTeleITContact = NEWID(),
@contactWriteAction = 1
END
ELSE -- istniejący kontakt
BEGIN
SELECT
@contactName = [Name],
@contactWriteAction = 2
FROM [SSCommon].[STContacts]
WHERE [Guid] = @contactGuid
SELECT @contactMainTeleITContact = [Guid] FROM [SSCommon].[STTeleItContacts] WHERE [ContactGuid] LIKE @contactGuid
IF (@contactMainTeleITContact IS NULL)
SET @contactMainTeleITContact = NEWID()
END
INSERT INTO [#TeleItContacts]([Guid], [Name], [ContactGuid], [Telephone1], [Telephone2], [Telephone3], [Fax], [Fax2], [Email1], [Email2], [WWW],
[Communicator1], [Communicator2], [Description], [Telex], [Facebook])
SELECT @contactMainTeleITContact, 'adres domyślny', @contactGuid, ISNULL(c.[Telephone1], ''), ISNULL(c.[Telephone2], ''), '', ISNULL(c.[Fax], ''), '', ISNULL(c.[Email], ''), '', ISNULL(c.[Website], ''),
'', '', '', ISNULL(c.[Telex], ''), ISNULL(c.[Facebook], '')
FROM [#tblContact] c
END
-- zapis informacji do tabeli z danymi adresu domyślnego
IF EXISTS (SELECT * FROM [#tblMainAddress])
BEGIN
IF (@contactGuid IS NULL)
SET @contactGuid = NEWID()
DECLARE @postOfficeAddressName NVARCHAR(64)
SELECT @contactMainPostOfficeAddress = [Guid], @postOfficeAddressName = [Name] FROM [#tblMainAddress]
IF (@contactId IS NULL OR @contactId = 0) -- nowy kontakt
BEGIN
SELECT @contactId = [Id] FROM [SSCommon].[STContacts] WHERE [Guid] LIKE @contactGuid
IF (@contactMainPostOfficeAddress IS NULL OR @contactMainPostOfficeAddress LIKE '00000000-0000-0000-0000-000000000000')
SET @contactMainPostOfficeAddress = NEWID()
END
ELSE -- istniejący kontakt
BEGIN
IF EXISTS(SELECT * FROM [SSCommon].[STPostOfficeAddresses] WHERE [ContactGuid] LIKE @contactGuid AND [AddressName] LIKE @postOfficeAddressName)
SELECT @contactMainPostOfficeAddress = [Guid] FROM [SSCommon].[STPostOfficeAddresses] WHERE [ContactGuid] LIKE @contactGuid AND [AddressName] LIKE @postOfficeAddressName
ELSE
IF (@contactMainPostOfficeAddress IS NULL OR @contactMainPostOfficeAddress LIKE '00000000-0000-0000-0000-000000000000')
SET @contactMainPostOfficeAddress = NEWID()
END
INSERT INTO [#PostOfficeAddresses]([AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo],
[HouseNo], [ContactGuid], [Province], [Country], [PostOfficeBox])
SELECT CASE WHEN a.[Name] IS NULL OR a.[Name] = '' THEN 'adres domyślny' ELSE a.[Name] END, @contactMainPostOfficeAddress, a.[Street], '', '', a.[Place], a.[PostalCode], a.[Post], a.[ApartmentNo],
a.[HouseNo], @contactGuid, a.[Province], a.[Country], ''
FROM [#tblMainAddress] a
END
-- zapis informacji do tabeli z danymi adresu korespondencyjnego
IF EXISTS (SELECT * FROM [#tblCorespondenceAddress])
BEGIN
DECLARE @corespondenceAddressId INT
DECLARE @corespondenceAddressGuid UNIQUEIDENTIFIER
DECLARE @corespondenceAddressName NVARCHAR(64)
SELECT @corespondenceAddressId = [Id], @corespondenceAddressGuid = [Guid], @corespondenceAddressName = [Name] FROM [#tblCorespondenceAddress]
IF (@corespondenceAddressId IS NULL OR @corespondenceAddressId = 0) -- nowy adres korespondencyjny
IF (@corespondenceAddressGuid IS NULL OR @corespondenceAddressGuid LIKE '00000000-0000-0000-0000-000000000000')
SET @corespondenceAddressGuid = NEWID()
ELSE
BEGIN
IF EXISTS (SELECT * FROM [SSCommon].[STPostOfficeAddresses] WHERE [ContactGuid] LIKE @contactGuid AND [AddressName] LIKE @corespondenceAddressName)
SELECT @corespondenceAddressGuid = [Guid] FROM [SSCommon].[STPostOfficeAddresses] WHERE [ContactGuid] LIKE @contactGuid AND [AddressName] LIKE @corespondenceAddressName
ELSE
IF (@corespondenceAddressGuid IS NULL OR @corespondenceAddressGuid LIKE '00000000-0000-0000-0000-000000000000')
SET @corespondenceAddressGuid = NEWID()
END
INSERT INTO [#PostOfficeAddresses]([AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo],
[HouseNo], [ContactGuid], [Province], [Country], [PostOfficeBox])
SELECT CASE WHEN a.[Name] IS NULL OR a.[Name] = '' THEN 'adres korespondencyjny' ELSE a.[Name] END, @corespondenceAddressGuid, a.[Street], '', '', a.[Place], a.[PostalCode], a.[Post], a.[ApartmentNo],
a.[HouseNo], @contactGuid, a.[Province], a.[Country], ''
FROM [#tblCorespondenceAddress] a
END
-- odczytanie pozostałych adresów podpietych pod kontakt (np. adresy dostaw)
INSERT INTO [#PostOfficeAddresses]([AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo],
[HouseNo], [ContactGuid], [Province], [Country], [PostOfficeBox])
SELECT [AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo],
[HouseNo], [ContactGuid], [Province], [Country], [PostOfficeBox]
FROM [SSCommon].[STPostOfficeAddresses]
WHERE [ContactGuid] = @contactGuid AND [AddressName] NOT LIKE 'adres domyślny' AND [AddressName] NOT LIKE 'adres korespondencyjny'
SET @contactTeleItContacts = (SELECT * FROM #TeleItContacts FOR XML PATH('TeleItContacts'), root ('DocumentElement'))
SET @contactPostOfficeAddresses = (SELECT * FROM #PostOfficeAddresses FOR XML PATH('PostOfficeAddresses'), root ('DocumentElement'))
EXEC [SSCommon].[sp_SaveContactWithDetails]
@contactId,
@contactGuid,
@contactName,
@contactMainPostOfficeAddress,
@contactMainTeleITContact,
@contactPostOfficeAddresses,
@contactTeleItContacts,
@contactWriteAction,
@contextInfo
-- zapis informacji do tabeli z danymi bankowymi
-- TODO wykorzystać procedurę sp_InsertBankingInfoAndAccounts, sp_UpdateBankingInfoAndAccounts, sp_InsertElementAndAttrs, sp_SaveElementAndAttrs, sp_UpdateElementAndAttrs jeżeli jest taka możliwość
DECLARE @removingElementsTable TABLE([ElementId] INT, [AccountId] INT) -- tymczasowa tabela na elementy do usunięcia
IF EXISTS (SELECT * FROM [#tblBankAccounts])
BEGIN
-- tabela tymczasowa na Rachunki bankowe
CREATE TABLE [#Accounts]
(
[Id] INT IDENTITY(1,1),
[Guid] UNIQUEIDENTIFIER,
[BankingInfosGuid] UNIQUEIDENTIFIER,
[AccountNo] NVARCHAR(50),
[BankName] NVARCHAR(100),
[AccountName] NVARCHAR(100),
[ElementId] INT,
[MainElementGuid] UNIQUEIDENTIFIER,
[SWIFT_BIC] NVARCHAR(10),
[Active] BIT,
[WhiteList] BIT,
[WhiteListDate] DATETIME2
)
DECLARE @bankAccountsEKId INT
DECLARE @bankAccountsEKGuid UNIQUEIDENTIFIER = '4849512E-9024-4B7C-BD06-737BF4FF3A77'
SELECT @bankAccountsEKId = [ElementKindId] FROM [SSCommon].[STElementKinds] WHERE [Guid] LIKE @bankAccountsEKGuid
DECLARE @minBankAccountsPosition INT
DECLARE @bankingId INT
DECLARE @bankingMainElement UNIQUEIDENTIFIER
DECLARE @bankingAccounts NVARCHAR(MAX)
-- sprawdzenie czy kontrahent ma BankingInfo
IF (@bankingInfoGuid IS NULL) -- nowe banking info
BEGIN
SET @bankingInfoGuid = NEWID()
EXEC [SSCommon].[sp_InsertBankingInfoAndAccounts]
0,
@bankingInfoGuid,
'',
'',
@contextInfo,
NULL
END
-- usunięcie kont bankowych
INSERT INTO @removingElementsTable
SELECT cmnElements.[ElementId], cmnAccounts.[Id] FROM [SSCommon].[STElements] cmnElements
INNER JOIN [SSCommon].[STAccounts] cmnAccounts ON cmnAccounts.[MainElementGuid] = cmnElements.[Guid]
LEFT OUTER JOIN [#tblBankAccounts] tAccount ON tAccount.[Id] = cmnAccounts.[Id]
WHERE tAccount.[Id] IS NULL AND cmnAccounts.[BankingInfosGuid] LIKE @bankingInfoGuid
IF EXISTS(SELECT * FROM @removingElementsTable)
BEGIN
DELETE FROM [SSCommon].[STElementExAttributes]
WHERE [ElementId] IN (SELECT [ElementId] FROM @removingElementsTable)
UPDATE [SSCommon].[STBankingInfos]
SET [MainAccount] = NULL
FROM [SSCommon].[STBankingInfos] bi
INNER JOIN #tblBankAccounts tba ON bi.[MainAccount] = tba.[Guid]
WHERE bi.[Guid] = @bankingInfoGuid
BEGIN TRY
DELETE FROM [SSCommon].[STAccounts]
WHERE [Id] IN (SELECT [AccountId] FROM @removingElementsTable)
DELETE FROM [SSCommon].[STElements]
WHERE [ElementId] IN (SELECT [ElementId] FROM @removingElementsTable)
END TRY
BEGIN CATCH
IF (ERROR_NUMBER() = 547)
THROW 50000, N'Nie można usunąć rachunku bankowego. Istnieją dokumenty powiązane z danym rachunkiem.', 1
ELSE
THROW 50000, N'Nie powiodło się usuwanie rachunków bankowych z bazy danych', 1
END CATCH
END
-- aktualizacja rachunków bankowych
SELECT @minBankAccountsPosition = ISNULL(MIN([Position]), 0) FROM [SSCommon].[STElements] WHERE [ElementKindId] = @bankAccountsEKId
BEGIN TRY
--aktualizacja STElements
SELECT
@extAttAccountNo = '',
@extAttDescription = '',
@extAttExtAttributes = '',
@extAttExtOtherThenOriginal=0
DECLARE @counter INT = 0
DECLARE @accountToHandle NVARCHAR(50)
SELECT @accountToHandle = [Guid]
FROM #tblBankAccounts
ORDER BY [Id] ASC
OFFSET @counter ROWS
FETCH NEXT 1 ROWS ONLY
WHILE @accountToHandle IS NOT NULL
BEGIN
SELECT
@extAttElementId = CASE WHEN elem.[ElementId] IS NULL OR elem.[ElementId] = 0 THEN -1 ELSE elem.[ElementId] END,
@extAttTitle = tAcc.[Guid],
@extAttActive = tAcc.[IsActive],
@extAttShortcut = tAcc.[Guid],
@extAttPosition = @minBankAccountsPosition - 1,
@minBankAccountsPosition = @minBankAccountsPosition - 1,
@extAttExtWriteAction = CASE WHEN elem.[ObjVer] IS NULL THEN 1 ELSE 2 END,
@extAttExtObjVer = CASE WHEN elem.[ElementId] IS NULL OR elem.[ElementId] = 0 THEN 0 ELSE elem.[ObjVer] + 1 END
FROM #tblBankAccounts tAcc
LEFT OUTER JOIN [SSCommon].[STElements] elem ON tAcc.[Guid] = elem.[Guid]
WHERE tAcc.[Guid] = @accountToHandle
EXEC [SSCommon].[sp_SaveElementAndAttrs]
@extAttElementId ,
@accountToHandle,
@extAttTitle,
@extAttActive,
@extAttAccountNo,
@extAttDescription,
@bankAccountsEKId,
@extAttShortcut,
@extAttPosition,
@extAttExtObjVer,
@extAttExtAttributes,
@bankAccountsEKGuid,
@extAttExtWriteAction,
@extAttExtOtherThenOriginal,
@contextInfo
SET @accountToHandle = NULL
SELECT @accountToHandle = [Guid],
@counter = @counter + 1
FROM #tblBankAccounts
ORDER BY [Id] ASC
OFFSET @counter ROWS
FETCH NEXT 1 ROWS ONLY
END
SELECT @bankingMainElement = [Guid]
FROM #tblBankAccounts
WHERE [IsDefault] = 1
--aktualizacja danych konkretnych rachunków bankowych
INSERT INTO #Accounts ([Guid], [BankingInfosGuid], [AccountNo], [BankName],
[Active], [SWIFT_BIC], [WhiteList], [WhiteListDate])
SELECT
CASE WHEN tAccount.[Guid] IS NULL OR tAccount.[Guid] LIKE '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE tAccount.[Guid] END,
@bankingInfoGuid, tAccount.[AccountNo], ISNULL(tAccount.[BankName], ''),
ISNULL(tAccount.[IsActive], 1), ISNULL(tAccount.[SWIFT_BIC], ''),
tAccount.[WhiteList], tAccount.[WhiteListDate]
FROM #tblBankAccounts tAccount
LEFT OUTER JOIN [SSCommon].[STAccounts] sa ON tAccount.[Guid] = sa.[Guid]
UPDATE #Accounts
SET [AccountName] = [Guid],
[MainElementGuid] = [Guid]
FROM #Accounts
SET @bankingAccounts = (SELECT * FROM #Accounts FOR XML PATH('Accounts'), root ('DocumentElement'))
EXEC [SSCommon].[sp_UpdateBankingInfoAndAccounts]
@bankingId,
@bankingInfoGuid,
'',
@bankingAccounts,
@contextInfo,
@bankingMainElement
END TRY
BEGIN CATCH
THROW 50000, N'Nie powiodła się aktualizacja rachunków bankowych', 1
END CATCH
END
ELSE -- jeżeli istniały rachunki bankowe to zostały usunięte (UWAGA: przypisane rachunki bankowe np. do dokumentów nie zostaną usunięte)
BEGIN
IF (@bankingInfoGuid IS NOT NULL AND @bankingInfoGuid NOT LIKE '00000000-0000-0000-0000-000000000000')
BEGIN
BEGIN TRY
-- usunięcie kont bankowych
INSERT INTO @removingElementsTable
SELECT cmnElements.[ElementId], cmnAccounts.[Id] FROM [SSCommon].[STElements] cmnElements
INNER JOIN [SSCommon].[STAccounts] cmnAccounts ON cmnAccounts.[MainElementGuid] = cmnElements.[Guid]
LEFT OUTER JOIN [#tblBankAccounts] tAccount ON tAccount.[Id] = cmnAccounts.[Id]
WHERE tAccount.[Id] IS NULL AND cmnAccounts.[BankingInfosGuid] LIKE @bankingInfoGuid
IF EXISTS(SELECT * FROM @removingElementsTable)
BEGIN
DELETE FROM [SSCommon].[STElementExAttributes]
WHERE [ElementId] IN (SELECT [ElementId] FROM @removingElementsTable)
UPDATE [SSCommon].[STBankingInfos]
SET [MainAccount] = NULL
WHERE [Guid] = @bankingInfoGuid
DELETE FROM [SSCommon].[STAccounts]
WHERE [Id] IN (SELECT [AccountId] FROM @removingElementsTable)
DELETE FROM [SSCommon].[STElements]
WHERE [ElementId] IN (SELECT [ElementId] FROM @removingElementsTable)
UPDATE [SSCommon].[STContractors]
SET [BankingInfoGuid] = NULL
WHERE [Id] = @contractorId
DELETE FROM [SSCommon].[STBankingInfos]
WHERE [Guid] LIKE @bankingInfoGuid
SET @bankingInfoGuid = NULL
END
END TRY
BEGIN CATCH
IF (ERROR_NUMBER() = 547)
THROW 50000, N'Nie można usunąć rachunku bankowego. Istnieją dokumenty powiązane z danym rachunkiem.', 1
ELSE
THROW 50000, N'Nie powiodło się usuwanie rachunków bankowych z bazy danych', 1
END CATCH
END
END
IF EXISTS (SELECT * FROM [#tblContractor])
BEGIN
DECLARE @contractorMainElement UNIQUEIDENTIFIER
DECLARE @mainPersonGuid UNIQUEIDENTIFIER
SET @mainPersonGuid = NULL
DECLARE @contractorElementKindGuid UNIQUEIDENTIFIER = 'BC090993-0768-4487-BE6F-E44E9501734C'
DECLARE @contractorElementKindId INT = NULL
DECLARE @firstname NVARCHAR(50), @surname NVARCHAR(50)
SELECT @firstname = [FirstName], @surname = [Surname] FROM [#tblContractor]
IF (@firstname IS NULL OR @firstname LIKE '')
SELECT @firstname = [FirstName] FROM [#tblContact]
IF (@surname IS NULL OR @surname LIKE '')
SELECT @surname = [Surname] FROM [#tblContact]
IF (@contractorId IS NULL OR @contractorId = 0)
BEGIN
SET @contractorMainElement = NEWID()
IF EXISTS(SELECT * FROM [SSCommon].[STElements] elem INNER JOIN [#tblContractor] tContractor ON elem.[Shortcut] LIKE tContractor.[Shortcut] INNER JOIN [SSCommon].[STElementKinds] ekinds ON elem.[ElementKindId] = ekinds.[ElementKindId] WHERE ekinds.[Name] LIKE 'Kontrahenci')
THROW 50000, N'Kontrahent o podanym skrócie istnieje w bazie danych', 1
BEGIN TRY
SELECT
@extAttElementId = NULL,
@extAttAccountNo = 'COMDA#RESETVALIDACCOUNTNO2008',
@extAttDescription = '',
@extAttPosition = NULL,
@extAttExtObjVer = 0,
@extAttExtWriteAction = 1,
@extAttExtOtherThenOriginal = 0
IF @moduleCode LIKE 'FKF'
BEGIN
SELECT
@extAttAccountNo = 'COMDA#GENERATEVALIDACCOUNTNO2008',
@extAttPosition = (SELECT [PositionFk] FROM #tblContractor WHERE [Id] = @contractorId)
END
SELECT @extAttTitle = tContractor.[Name],
@extAttActive = ISNULL(tContractor.[Status], 1),
@extAttShortcut = ISNULL(tContractor.[Shortcut], '')
FROM [#tblContractor] tContractor
-- exAttributes
-- splitPayment
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'splitPayment', (SELECT CASE WHEN [RequestMppTransfers] LIKE 'Wymagaj' THEN 2 ELSE (CASE WHEN [RequestMppTransfers] LIKE 'Blokada' THEN 1 ELSE 0 END) END FROM [#tblContractor]),
'System.Int16, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'splitPayment', 0, 0
-- idFK
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'idFK', (SELECT CASE WHEN [IdFk] = 0 THEN '' ELSE CAST([IdFk] AS NVARCHAR(1024)) END FROM [#tblContractor]),
'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'idFK', 0, 0
-- parametrFK
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'parametrFK', (SELECT ISNULL([AccountantParameter], '') FROM [#tblContractor]),
'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'parametrFK', 0, 0
-- typCeny
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'typCeny', (SELECT CASE WHEN [PriceTypeId] IS NULL OR [PriceTypeId] < 0 THEN 0 ELSE [PriceTypeId] END FROM [#tblContractor]),
'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'typCeny', 0, 0
-- rodzajCeny
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'rodzajCeny', (SELECT ISNULL([SaleKindId],0) FROM [#tblContractor]),
'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'rodzajCeny', 0, 0
-- rejestr
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'rejestr', (SELECT ISNULL([PayRegistryId], 0) FROM [#tblContractor]),
'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'rejestr', 0, 0
-- formaPlatnosci
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'formaPlatnosci', (SELECT ISNULL([PaymentFormId], 0) FROM [#tblContractor]),
'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'formaPlatnosci', 0, 0
SET @extAttExtAttributes = (SELECT * FROM #ExtAttributes FOR XML PATH('ElementExAttributes'), root ('DocumentElement'))
EXEC [SSCommon].[sp_SaveElementAndAttrs]
@extAttElementId ,
@contractorMainElement,
@extAttTitle,
@extAttActive,
@extAttAccountNo,
@extAttDescription,
@contractorElementKindId,
@extAttShortcut,
@extAttPosition,
@extAttExtObjVer,
@extAttExtAttributes,
@contractorElementKindGuid,
@extAttExtWriteAction,
@extAttExtOtherThenOriginal,
@contextInfo
IF ((@surname IS NOT NULL AND @surname NOT LIKE '') OR (@firstname IS NOT NULL AND @firstname NOT LIKE ''))
BEGIN
SET CONTEXT_INFO @ContextInfoBinaryP;
SET @mainPersonGuid = NEWID()
INSERT INTO [SSCommon].[STPersons]([Firstname],[SecondName],[Surname],[BirthDate],[Guid],[StringIdent],
[DualStringIdent1],[DualStringIdent2],[Note],[ContactGuid],[BankingInfoGuid],[DataSourceName],[DataSourceAddress],[DataProcessingAgree],[DataMarketingProcessingAgree],
[MainElement],[ExcludedFromAnonymization],[Anonymized])
SELECT ISNULL(@firstname, ''), '', ISNULL(@surname, ''), CAST('1753-01-01 00:00:00.000' AS DATETIME2), @mainPersonGuid, ISNULL(tContractor.[Name], ''),
ISNULL(tContractor.[Pesel],''), ISNULL(tContractor.[Nip],''), '', @contactGuid, @bankingInfoGuid, '', '', 0, 0,
@contractorMainElement, 0, 0
FROM [#tblContractor] tContractor
INSERT INTO [SSCommon].[STPersonsClassification]([Guid], [ElementId], [CDim_UserPerson], [CDim_EmployeePerson], [CDim_ContractorContactPerson])
VALUES(NEWID(), @mainPersonGuid, 0, 0, 1)
SET CONTEXT_INFO @ContextInfoBinary;
END
INSERT INTO [SSCommon].[STContractors]([Guid],
[Shortcut],[Name],[NIP],[PESEL],[REGON],[NIP_UE],
[ContactGuid],[BankingInfoGuid],[LimitFlag],[LimitAmount],[LimitCurrency],
[VIES],[Negotiation],[MainElement],[MainPerson],[VATPayerActive],
[NaturalPerson],[LinkedUnit])
SELECT
@contractorGuid, tContractor.[Shortcut], tContractor.[Name], ISNULL(tContractor.[Nip], ''), ISNULL(tContractor.[Pesel], ''), ISNULL(tContractor.[Regon], ''), ISNULL(tContractor.[NipUE], ''),
@contactGuid, @bankingInfoGuid, ISNULL(tContractor.[IsLimitActive], 0), ISNULL(tContractor.[LimitValue], 0), ISNULL(tContractor.[LimitCurrency], ''),
ISNULL(tContractor.[VIES], 0), ISNULL(tContractor.[PriceNegotiation], 0), @contractorMainElement, @mainPersonGuid, ISNULL(tContractor.[VATPayerActive], 0),
ISNULL(tContractor.[PersonKind], 0), ISNULL(tContractor.[LinkedUnit], 0)
FROM [#tblContractor] tContractor
SELECT @contractorId = [Id] FROM [SSCommon].[STContractors] WHERE Shortcut = @extAttShortcut
IF ((@surname IS NOT NULL AND @surname NOT LIKE '') OR (@firstname IS NOT NULL AND @firstname NOT LIKE ''))
BEGIN
SET CONTEXT_INFO @ContextInfoBinaryP;
INSERT INTO [SSCommon].[STPersonsContractors]([PersonGuid],[ContractorGuid],[PersonOrder],[Guid])
VALUES(@mainPersonGuid, @contractorGuid, 0, NEWID())
SET CONTEXT_INFO @ContextInfoBinary;
END
END TRY
BEGIN CATCH
THROW 50000, N'Nie powiodło się dodawanie kontrahenta', 1
END CATCH
END
ELSE
BEGIN
BEGIN TRY
SELECT @mainPersonGuid = [MainPerson], @contractorMainElement = [MainElement], @contractorGuid = [Guid] FROM [SSCommon].[STContractors] WHERE [Id] = @contractorId
SET CONTEXT_INFO @ContextInfoBinaryP;
IF ((@surname IS NOT NULL AND @surname NOT LIKE '') OR (@firstname IS NOT NULL AND @firstname NOT LIKE ''))
BEGIN
IF (@mainPersonGuid IS NULL)
BEGIN
SET @mainPersonGuid = NEWID()
INSERT INTO [SSCommon].[STPersons]([Firstname],[SecondName],[Surname],[BirthDate],[Guid],[StringIdent],
[DualStringIdent1],[DualStringIdent2],[Note],[ContactGuid],[BankingInfoGuid],[DataSourceName],[DataSourceAddress],[DataProcessingAgree],[DataMarketingProcessingAgree],
[MainElement],[ExcludedFromAnonymization],[Anonymized])
SELECT ISNULL(@firstname, ''), '', ISNULL(@surname, ''), CAST('1753-01-01 00:00:00.000' AS DATETIME2), @mainPersonGuid, tContractor.[Name],
ISNULL(tContractor.[Pesel],''), ISNULL(tContractor.[Nip],''), '', @contactGuid, @bankingInfoGuid, '', '', 0, 0,
@contractorMainElement, 0, 0
FROM [#tblContractor] tContractor
INSERT INTO [SSCommon].[STPersonsClassification]([Guid], [ElementId], [CDim_UserPerson], [CDim_EmployeePerson], [CDim_ContractorContactPerson])
VALUES(NEWID(), @mainPersonGuid, 0, 0, 1)
INSERT INTO [SSCommon].[STPersonsContractors]([PersonGuid],[ContractorGuid],[PersonOrder],[Guid])
VALUES(@mainPersonGuid, @contractorGuid, 0, NEWID())
END
ELSE
BEGIN
UPDATE [SSCommon].[STPersons]
SET [Firstname] = ISNULL(@firstname, ''), [Surname] = ISNULL(@surname, '')
FROM [#tblContractor] tContractor
WHERE [SSCommon].[STPersons].Guid LIKE @mainPersonGuid
END
END
ELSE
BEGIN
UPDATE [SSCommon].[STContractors]
SET [MainPerson] = NULL
WHERE [Id] = @contractorId
DELETE FROM [SSCommon].[STPersons]
WHERE [Guid] LIKE @mainPersonGuid
SET @mainPersonGuid = NULL
END
SET CONTEXT_INFO @ContextInfoBinary;
UPDATE [SSCommon].[STContractors]
SET
[Shortcut] = ISNULL(tContractor.[Shortcut], ''),
[Name] = ISNULL(tContractor.[Name], ''),
[NIP] = ISNULL(tContractor.[Nip], ''),
[PESEL] = ISNULL(tContractor.[Pesel], ''),
[REGON] = ISNULL(tContractor.[Regon], ''),
[NIP_UE] = ISNULL(tContractor.NipUE, ''),
[ContactGuid] = @contactGuid,
[BankingInfoGuid] = @bankingInfoGuid,
[LimitFlag] = ISNULL(tContractor.[IsLimitActive], 0),
[LimitAmount] = ISNULL(tContractor.[LimitValue], 0),
[LimitCurrency] = ISNULL(tContractor.[LimitCurrency], ''),
[VIES] = ISNULL(tContractor.[VIES], 0),
[Negotiation] = ISNULL(tContractor.[PriceNegotiation], 0),
[MainPerson] = @mainPersonGuid,
[VATPayerActive] = ISNULL(tContractor.[VATPayerActive], 0),
[NaturalPerson] = ISNULL(tContractor.[PersonKind], 0),
[LinkedUnit] = ISNULL(tContractor.[LinkedUnit], 0)
FROM [SSCommon].[STContractors] cmnCtrs
INNER JOIN [#tblContractor] tContractor ON tContractor.[Id] = cmnCtrs.[Id]
WHERE cmnCtrs.[Id] = @contractorId
SELECT @extAttElementId = elem.[ElementId]
FROM [SSCommon].[STElements] elem
WHERE elem.[Guid] = @contractorMainElement
SELECT
@extAttAccountNo = 'COMDA#UPDATEVALIDACCOUNTNO2008',
@extAttDescription = [Description],
@extAttPosition = NULL,
@extAttExtObjVer = [ObjVer] + 1,
@extAttExtWriteAction = 2,
@extAttExtOtherThenOriginal = 0
FROM [SSCommon].[STElements]
WHERE [ElementId] = @extAttElementId
IF @moduleCode LIKE 'FKF'
SET @extAttPosition = (SELECT [PositionFk] FROM #tblContractor WHERE [Id] = @contractorId)
SELECT @extAttTitle = ISNULL(tContractor.[Name], ''),
@extAttActive = ISNULL(tContractor.[Status], 1),
@extAttShortcut = ISNULL(tContractor.[Shortcut], '')
FROM [#tblContractor] tContractor
-- exAttributes
DECLARE @exAttributeId INT
DECLARE @exAttributeGuid UNIQUEIDENTIFIER
DECLARE @exAttributeCLRType NVARCHAR(400)
DECLARE @exAttributeTitle NVARCHAR(200)
DECLARE @exAttributeAccessFlag BIT
DECLARE @exAttributeInheritedFlag BIT
-- splitPayment
SET @exAttributeId = NULL
SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
FROM [SSCommon].[STElementExAttributes] ex
INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'splitPayment'
IF (@exAttributeId IS NOT NULL)
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @exAttributeId, 'splitPayment', (SELECT CASE WHEN [RequestMppTransfers] LIKE 'Wymagaj' THEN 2 ELSE (CASE WHEN [RequestMppTransfers] LIKE 'Blokada' THEN 1 ELSE 0 END) END FROM [#tblContractor]),
@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
END
ELSE
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'splitPayment', (SELECT CASE WHEN [RequestMppTransfers] LIKE 'Wymagaj' THEN 2 ELSE (CASE WHEN [RequestMppTransfers] LIKE 'Blokada' THEN 1 ELSE 0 END) END FROM [#tblContractor]),
'System.Int16, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'splitPayment', 0, 0
END
-- idFK
SET @exAttributeId = NULL
SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
FROM [SSCommon].[STElementExAttributes] ex
INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'idFK'
IF (@exAttributeId IS NOT NULL)
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @exAttributeId, 'idFK', (SELECT CASE WHEN ISNULL([IdFk], 0) = 0 THEN '' ELSE CAST([IdFk] AS NVARCHAR(1024)) END FROM [#tblContractor]),
@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
END
ELSE
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'idFK', (SELECT CASE WHEN ISNULL([IdFk], 0) = 0 THEN '' ELSE CAST([IdFk] AS NVARCHAR(1024)) END FROM [#tblContractor]),
'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'idFK', 0, 0
END
-- parametrFK
SET @exAttributeId = NULL
SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
FROM [SSCommon].[STElementExAttributes] ex
INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'parametrFK'
IF (@exAttributeId IS NOT NULL)
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @exAttributeId, 'parametrFK', (SELECT ISNULL([AccountantParameter], '') FROM [#tblContractor]),
@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
END
ELSE
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'parametrFK', (SELECT ISNULL([AccountantParameter], '') FROM [#tblContractor]),
'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'parametrFK', 0, 0
END
-- typCeny
SET @exAttributeId = NULL
SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
FROM [SSCommon].[STElementExAttributes] ex
INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'typCeny'
IF (@exAttributeId IS NOT NULL)
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @exAttributeId, 'typCeny', (SELECT CASE WHEN [PriceTypeId] IS NULL OR [PriceTypeId] < 0 THEN 0 ELSE [PriceTypeId] END FROM [#tblContractor]),
@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
END
ELSE
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'typCeny', (SELECT CASE WHEN [PriceTypeId] IS NULL OR [PriceTypeId] < 0 THEN 0 ELSE [PriceTypeId] END FROM [#tblContractor]),
'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'typCeny', 0, 0
END
-- rodzajCeny
SET @exAttributeId = NULL
SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
FROM [SSCommon].[STElementExAttributes] ex
INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'rodzajCeny'
IF (@exAttributeId IS NOT NULL)
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @exAttributeId, 'rodzajCeny', (SELECT ISNULL([SaleKindId],0) FROM [#tblContractor]),
@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
END
ELSE
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'rodzajCeny', (SELECT ISNULL([SaleKindId],0) FROM [#tblContractor]),
'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'rodzajCeny', 0, 0
END
-- rejestr
SET @exAttributeId = NULL
SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
FROM [SSCommon].[STElementExAttributes] ex
INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'rejestr'
IF (@exAttributeId IS NOT NULL)
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @exAttributeId, 'rejestr', (SELECT ISNULL([PayRegistryId], 0) FROM [#tblContractor]),
@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
END
ELSE
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'rejestr', (SELECT ISNULL([PayRegistryId], 0) FROM [#tblContractor]),
'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'rejestr', 0, 0
END
-- formaPlatnosci
SET @exAttributeId = NULL
SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
FROM [SSCommon].[STElementExAttributes] ex
INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'formaPlatnosci'
IF (@exAttributeId IS NOT NULL)
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @exAttributeId, 'formaPlatnosci', (SELECT ISNULL([PaymentFormId], 0) FROM [#tblContractor]),
@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
END
ELSE
BEGIN
INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
SELECT @extAttElementId, 'formaPlatnosci', (SELECT ISNULL([PaymentFormId], 0) FROM [#tblContractor]),
'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'formaPlatnosci', 0, 0
END
SET @extAttExtAttributes = (SELECT * FROM #ExtAttributes FOR XML PATH('ElementExAttributes'), root ('DocumentElement'))
EXEC [SSCommon].[sp_SaveElementAndAttrs]
@extAttElementId ,
@contractorMainElement,
@extAttTitle,
@extAttActive,
@extAttAccountNo,
@extAttDescription,
@contractorElementKindId,
@extAttShortcut,
@extAttPosition,
@extAttExtObjVer,
@extAttExtAttributes,
@contractorElementKindGuid,
@extAttExtWriteAction,
@extAttExtOtherThenOriginal,
@contextInfo
END TRY
BEGIN CATCH
THROW 50000, N'Nie powiodła się aktualizacja kontrahenta', 1
END CATCH
END
END
IF EXISTS (SELECT * FROM [#tblTradeData])
BEGIN
DECLARE @discountPercent FLOAT
SELECT @discountPercent = [DiscountPercent] FROM [#tblTradeData]
IF (@discountPercent IS NOT NULL AND @discountPercent <> 0)
BEGIN
IF EXISTS (SELECT * FROM [HM].[ContractorDiscounts] WHERE [ContractorId] = @contractorId)
BEGIN
UPDATE [HM].[CN]
SET [cena] = @discountPercent
WHERE [typ] = 15 and [idpd] = @contractorId
END
ELSE
BEGIN
INSERT INTO [HM].[CN]([idpd], [cena], [typ], [flag], [subtyp], [idpm], [typpd], [idck], [typpar], [parametr], [typceny], [waluta], [aktywna], [flagcen], [datastart], [dataend], [createdBy])
VALUES (@contractorId, @discountPercent, 15, 0, 0, NULL, 0, NULL, 0, 0, 0, '%', 0, 0, NULL, NULL, NULL)
END
END
ELSE
BEGIN
DELETE FROM [HM].[CN]
WHERE [idpd] = @contractorId AND [typ] = 15
END
END
DECLARE @markerId INT
DECLARE @contractorKindId INT
DECLARE @catalogId NVARCHAR(100)
SELECT @catalogId = [CatalogId], @contractorKindId = ISNULL([ContractorKindId], 0), @markerId = ISNULL([MarkerId], 0)
FROM [#tblContractor]
DECLARE @contractorKindGuid UNIQUEIDENTIFIER
IF (@contractorKindId <> 0)
SELECT @contractorKindGuid = [Guid] FROM [SSCommon].[STElements] WHERE [ElementId] = @contractorKindId
DECLARE @catalogGuid UNIQUEIDENTIFIER
IF (@catalogId IS NOT NULL AND @catalogId NOT LIKE '')
SELECT @catalogGuid = [Guid] FROM [SSCommon].[STElements] WHERE [Shortcut] = @catalogId
DECLARE @markerGuid UNIQUEIDENTIFIER
IF (@markerId IS NOT NULL AND @markerId <> 0)
SELECT @markerGuid = [Guid] FROM [SSCommon].[STElements] WHERE [ElementId] = @markerId
IF EXISTS (SELECT * FROM [#tblCustomFields])
BEGIN
--SELECT * FROM [#tblCustomFields]
DECLARE @valueStatement NVARCHAR(MAX)
DECLARE @newLine NVARCHAR(10)
SET @newLine = char(13)+char(10)
DECLARE @setStatement NVARCHAR(MAX)
SELECT @setStatement = COALESCE(@setStatement + ', ', '') +
'[' + [ColumnName] + '] = ' +
CASE WHEN [Value] IS NULL THEN 'NULL'
ELSE (CASE WHEN ([Type] = 'System.Boolean, mscorlib') THEN (CASE WHEN [Value] = 'True' THEN '1' ELSE '0' END)
ELSE (CASE WHEN ([Type] = 'System.DateTime, mscorlib') THEN 'CAST(''' + [Value] + '''AS datetime2)'
ELSE (CASE WHEN ([Type] = 'System.String, mscorlib') THEN '''' + [Value] + ''''
ELSE [Value] END) END) END) END
FROM [#tblCustomFields]
--SELECT @setStatement
DECLARE @insertColumnsStatement NVARCHAR(MAX)
DECLARE @insertValuesStatement NVARCHAR(MAX)
SELECT @insertColumnsStatement = COALESCE(@insertColumnsStatement + ', ', '') + '[' + [ColumnName] + ']',
@insertValuesStatement = COALESCE(@insertValuesStatement + ', ', '') +
CASE WHEN [Value] IS NULL THEN 'NULL'
ELSE (CASE WHEN ([Type] = 'System.Boolean, mscorlib') THEN (CASE WHEN [Value] = 'True' THEN '1' ELSE '0' END)
ELSE (CASE WHEN ([Type] = 'System.DateTime, mscorlib') THEN 'CAST(''' + [Value] + '''AS datetime2)'
ELSE (CASE WHEN ([Type] = 'System.String, mscorlib') THEN '''' + [Value] + ''''
ELSE [Value] END) END) END) END
FROM [#tblCustomFields]
--SELECT @insertColumnsStatement
--SELECT @insertValuesStatement
SET @valueStatement = CONCAT('IF EXISTS(SELECT * FROM [SSCommon].[',@classificationTableName,'] WHERE [ElementId] = ',@contractorId,')',
@newLine,'BEGIN',
@newLine,'UPDATE [SSCommon].[',@classificationTableName,']',
@newLine,'SET ',
@newLine,@setStatement,
@newLine,'WHERE [ElementId] = ',@contractorId,
@newLine,'END',
@newLine,'ELSE',
@newLine,'BEGIN',
@newLine,'INSERT INTO [SSCommon].[',@classificationTableName,']([ElementId], [Guid],',@insertColumnsStatement,')',
@newLine,'VALUES(',@contractorId,',''',NEWID(),''',',@insertValuesStatement,')',
@newLine,'END')
--SELECT @valueStatement
EXEC (@valueStatement)
END
ELSE
BEGIN
DECLARE @deleteStatement NVARCHAR(MAX)
SET @deleteStatement = CONCAT('DELETE FROM [SSCommon].[', @classificationTableName,'] WHERE [ElementId] = ''', @contractorId, '''')
--SELECT @deleteStatement
EXEC (@deleteStatement)
END
IF NOT EXISTS(SELECT * FROM [SSCommon].[STContractorsClassification] WHERE [ElementId] LIKE @contractorGuid)
BEGIN
INSERT INTO [SSCommon].[STContractorsClassification]([Guid],[ElementId],[CDim_ZnacznikKh],[CDim_RodzajKh],[CDim_KatalogKh])
VALUES (NEWID(), @contractorGuid, @markerGuid, @contractorKindGuid, @catalogGuid)
END
ELSE
BEGIN
UPDATE [SSCommon].[STContractorsClassification]
SET [CDim_ZnacznikKh] = @markerGuid, [CDim_RodzajKh] = @contractorKindGuid, [CDim_KatalogKh] = @catalogGuid
WHERE [ElementId] LIKE @contractorGuid
END
--czyścimy śmieci
IF (OBJECT_ID('tempdb..[#tblContractor]') IS NOT NULL) DROP TABLE [#tblContractor]
IF (OBJECT_ID('tempdb..[#tblContact]') IS NOT NULL) DROP TABLE [#tblContact]
IF (OBJECT_ID('tempdb..[#tblMainAddress]') IS NOT NULL) DROP TABLE [#tblMainAddress]
IF (OBJECT_ID('tempdb..[#tblCorespondenceAddress]') IS NOT NULL) DROP TABLE [#tblCorespondenceAddress]
IF (OBJECT_ID('tempdb..[#tblTradeData]') IS NOT NULL) DROP TABLE [#tblTradeData]
IF (OBJECT_ID('tempdb..[#tblBankAccounts]') IS NOT NULL) DROP TABLE [#tblBankAccounts]
IF (OBJECT_ID('tempdb..[#tblCustomFields]') IS NOT NULL) DROP TABLE [#tblCustomFields]
exec [Common].[UpdateContractorRIFromXML] @contractorId, @contractorData, @moduleCode , @contextInfo, @contextInfoP
COMMIT TRANSACTION;
exec [Common].[ReadContractorAsXML] @contractorId, @moduleCode
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END
END CATCH
END