Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [GI_RYCHLY_START]
- GO
- /****** Object: StoredProcedure [gist].[gsp_io_dim_odberatel] Script Date: 13.12.2018 8:30:33 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- -- =============================================
- -- Author: DR
- -- Create date: 13.9.2018
- -- =============================================
- ALTER PROCEDURE [gist].[gsp_io_dim_odberatel]
- @id_pump_out int, -- ID pumpy OUT
- @io_table varchar(255),/*VM tato tabulka musí existovcat v DB CO a může se plnit postupně z více zdrojů (např. více DB HEG)
- -- nelze to použít jako odakz na tabulku nebo VIEW ve zdrojové DB. Název zdrojového VIEW nutno použít (jako string) v samotném SELECT níže*/
- @tmp_yes smallint, -- 1/0 = ANO/NE pro použití TMP tabulek
- @parent_process varchar(50)
- AS
- DECLARE @iddim int, @dim_name varchar(50), @txt_sql varchar(8000), @process varchar(50),
- @text varchar(255),@error varchar(8000),@errmsg varchar(8000),@errno int,
- @idhier int,@sp_name varchar(255),@type_target varchar(10),@id_target int,@id_attr int
- /*VM Obecná sekce pro informace o dimenzi podle @id_pump_out*/
- SELECT @sp_name=sp_name,@type_target=type_target ,@id_target=id_target,@id_attr=id_attr
- FROM s4v_pumps_out
- WHERE id_pump_out=@id_pump_out
- SET @iddim=@id_target
- SET @process =@sp_name
- --nacteme nazev dimeze
- SELECT @dim_name= ISNULL(name,LTRIM(RTRIM(STR(@id_target))))
- FROM sys_dimension
- WHERE iddim = @id_target
- /*VM Konec - Obecná sekce pro informace o dimenzi podle @id_pump_out*/
- --zapis zacatku zpracovani do logu
- SET @text ='Start datového přenosu z tabulky "' + @io_table + '" do dimeze '+ @dim_name
- EXEC gcsp_write_log 'Information','Pumpa pro plnění dimenze',@text, 0, @parent_process,@sp_name,'Datove_prenosy_detail'
- --kontroly
- IF @type_target<>'DIM' BEGIN
- SET @error ='Procedura nemůže zpracovat data pro typ cíle type_target="' + @type_target+ '",
- protože je určena jen pro type_target="DIM" . Transakce se neprovede!!!'
- GOTO error
- END
- IF @id_attr <>0 BEGIN
- SET @error ='Procedura nemůže zpracovat data pro typ atribut dimenze id_attr="' + ltrim(str(@id_attr)) + '",
- protože je určena jen pro prvky dimenzí" . Transakce se neprovede!!!'
- GOTO error
- END
- IF not exists (SELECT * FROM sys_dimension WHERE iddim=@id_target) BEGIN
- SET @error ='Procedura nemůže zpracovat data pro dimenzi IDdim="' + ltrim(str(@id_target)) + '",
- protože v modulu CO není taková dimenze známá" . Transakce se neprovede!!!'
- GOTO error
- END
- --zpracovávaná dimenze
- SET @iddim=@id_target
- --/---------------------------------------------------------------------ZACIATOK UPRAV PROCKY
- -- drop table #tmp_ta_dim1
- CREATE TABLE #tmp_ta_dim1 (
- [parent_ext] [varchar](30) COLLATE database_default NOT NULL,
- [code_ext] [varchar](30) COLLATE database_default NOT NULL,
- [code_name] [varchar](150) COLLATE database_default NOT NULL,
- [idhier] [int] NOT NULL,
- [list] [int] NULL,
- [ord] [int] NULL)
- /*dle OZ - 53103 */
- -- uzly
- INSERT INTO #tmp_TA_DIM1 ([parent_ext], [code_ext], [code_name],[idhier],[list],[ord])
- SELECT distinct '@nechat@kde@je@2',id_obch_zast,obch_zast,53103,0,0
- FROM gist.cgist_temp2
- -- listy
- INSERT INTO #tmp_TA_DIM1 ([parent_ext], [code_ext], [code_name],[idhier],[list],[ord])
- SELECT distinct id_obch_zast,cislo_odber,cislo_odber+' '+nazev_odber,53103,1,0
- FROM gist.cgist_temp2
- /*dle Zemí - 53104 */
- INSERT INTO #tmp_TA_DIM1 ([parent_ext], [code_ext], [code_name],[idhier],[list],[ord])
- SELECT distinct '@nechat@kde@je@2',id_zeme,zeme,53104,0,0
- FROM gist.cgist_temp2
- INSERT INTO #tmp_TA_DIM1 ([parent_ext], [code_ext], [code_name],[idhier],[list],[ord])
- SELECT distinct id_zeme,cislo_odber,cislo_odber+' '+nazev_odber,53104,1,0
- FROM gist.cgist_temp2
- /*dle Skupin - 53105 */
- INSERT INTO #tmp_TA_DIM1 ([parent_ext], [code_ext], [code_name],[idhier],[list],[ord])
- SELECT distinct '@nechat@kde@je@2',id_skupina_odb,skupina_odb,53105,0,0
- FROM gist.cgist_temp2
- INSERT INTO #tmp_TA_DIM1 ([parent_ext], [code_ext], [code_name],[idhier],[list],[ord])
- SELECT distinct id_skupina_odb,cislo_odber,cislo_odber+' '+nazev_odber,53105,1,0
- FROM gist.cgist_temp2
- select * from gist.cgist_temp2
- ---------/-------------------------------------------------------------------------KONIEC Uziv. casti
- --vlozeni do ta_dim
- INSERT INTO ta_dim (dim_name,hier_name,parent_ext,code_ext,code_name,ord,list,iddim,idhier,attr0)
- SELECT 'x' as dim_name,'x' as hier_name, parent_ext,code_ext,code_name,ord,list,@iddim,idhier,0
- FROM #tmp_ta_dim1
- IF @@error <> 0 BEGIN
- SET @error = 'INSERT INTO ta_dim.....'
- GOTO error
- END
- --doplneni atributu nazev aktivity-----------------------------------------------------------------
- update e
- set e.attribute_16 = t.cislo_odber,
- e.attribute_17 = t.nazev_odber,
- e.attribute_18 = t.skupina_odb,
- e.attribute_19 = t.obch_zast,
- e.attribute_20 = t.zeme,
- e.attribute_21 = t.region
- from gist.cgist_temp2 t
- join gist.dim_element_533 e on (e.attribute_13 = t.cislo_odber)
- -----drop tmp dim1-------
- IF OBJECT_ID('temmpdb..#tmp_ta_dim1') IS NOT NULL
- DROP TABLE #tmp_ta_dim1
- --zapis do logu
- SET @text ='Konec datového přenosu z tabulky "' + @io_table + '" do dimeze '+ @dim_name
- EXEC gcsp_write_log 'Information','Pumpa pro plnění dimenze',@text, 0, @parent_process,@sp_name,'Datove_prenosy_detail'
- RETURN 0
- ERROR:
- SELECT @errno = 13000, @errmsg = 'Uložená procedura "' + @process + '" skončila chybou - ' + @error
- RAISERROR (@errmsg, 16, 1)
- EXEC gcsp_write_log 'Error','Pumpa pro plnění dimenze',@text, 0, @parent_process,@sp_name,'Datove_prenosy_detail'
- RETURN -1
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement