/*
AB1A verzia nn.mm.04.780
INTRASTAT CZ

Spustit skript v požadované databáze na serveru MS SQL

Naplnení chýbějících hodnot pro pole IS země původu v Kmenových datech artiklu a v řádcích dokladu.  

*/

declare @FieldID int;
delete from "@DFX_IOST" where (U_DFX_KOD='CW' and U_DFX_OST=53) or (U_DFX_KOD='SX' and U_DFX_OST=210);

SELECT 0 rownum,CONVERT(nvarchar(3),'-') Kod,CONVERT(nvarchar(50),'-') Popis into #dfx_t1;
insert into #dfx_t1 SELECT row_number() OVER (ORDER BY U_DFX_KOD) AS rownum,U_DFX_KOD Kod,SUBSTRING(U_DFX_DSC,1,50) Popis FROM [@DFX_IOST];

select COUNT(1) from ufd1; 
begin tran
select @FieldID=FieldID from CUFD where AliasID='DFX_KPOV' and TableID='OITM';
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'OITM',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'SITM',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'UITM',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'AITM',@FieldID,rownum,Kod,Popis,null from #dfx_t1;

select @FieldID=FieldID from CUFD where AliasID='DFX_KPOV' and TableID='INV1';
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'ADO1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'OEI1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'CIN1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'CPI1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'CPV1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'CSI1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'CSV1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'DLN1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'DPI1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'DPO1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'DRF1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'IEI1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'IGE1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'IGN1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'INV1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'PCH1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'PDN1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'POR1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'PQT1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'PRQ1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'PRR1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'QUT1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'RDN1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'RDR1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'RIN1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'RPC1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'RPD1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'RRR1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'SFC1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'SFI1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'WTQ1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
insert into UFD1 (TableID,FieldID,IndexID,FldValue,Descr,FldDate) select 'WTR1',@FieldID,rownum,Kod,Popis,null from #dfx_t1;
select COUNT(1) from ufd1;
commit
drop table #dfx_t1
