IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RDS_DirectionAccessXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_DirectionAccessXml]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_RDS_DirectionAccessXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[sp_RDS_DirectionAccessXml]
GO

--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:        Vitacore (с)
---- Create date: 2014-03-01
---- Description:    Загрузка разрешений на выдачу рецептов из RDS 
---- =============================================
--CREATE PROCEDURE [APS].[sp_RDS_DirectionAccessXml]
--    @Xml xml = null,    
--    @QueueName varchar(100) = 'RDSSync'
--AS
--BEGIN
--SET NOCOUNT ON;

--    IF (@Xml is not null)
--    BEGIN

--        DELETE FROM [APS].[T_DLO_ACCESSED_POSTS]                     

--        -- Generate table #DLO_ACCESSED_POSTS
--        select
--                 T.c.value('./ID[1]', 'uniqueidentifier') as ID
--                ,T.c.value('./PostCode[1]', 'varchar(255)') as PostCode
--                ,T.c.value('./LastName[1]', 'varchar(32)') as LastName
--                ,T.c.value('./FirstName[1]', 'varchar(32)') as FirstName
--                ,T.c.value('./FatherName[1]', 'varchar(32)') as FatherName
--                ,T.c.value('./Category[1]', 'int') as Category
--                ,convert(datetime, t.c.value('./BeginAccessDate[1]', 'varchar(10)'), 104) as BeginAccessDate
--                ,convert(datetime, t.c.value('./EndAccessDate[1]', 'varchar(10)'), 104) as EndAccessDate
--                ,convert(datetime, t.c.value('./ExpulsionDate[1]', 'varchar(10)'), 104) as ExpulsionDate
--                ,convert(datetime, t.c.value('./BeginWorkDate[1]', 'varchar(10)'), 104) as BeginWorkDate
--                ,convert(datetime, t.c.value('./CertificateDate[1]', 'varchar(10)'), 104) as CertificateDate
--                ,T.c.value('./MedicalSpecialityCode[1]', 'varchar(16)') as MedicalSpecialityCode
--                ,T.c.value('./PostType[1]', 'varchar(4)') as PostType
--                ,T.c.value('./Speciality[1]', 'varchar(3)') as Speciality
--                ,lm.MCOD as LpuMain
--                ,T.c.value('./Snils[1]', 'char(14)') as Snils
--            INTO #DLO_ACCESSED_POSTS
--        from @Xml.nodes('ACCESSES/body/DLO_ACCESSED_POSTS') T(c)
--            join [APS].T_LPU_MAIN lm on lm.MCOD = T.c.value('./LpuMain[1]', 'varchar(6)')

--        -- INSERT [APS].T_DLO_ACCESSED_POSTS
--        INSERT INTO [APS].[T_DLO_ACCESSED_POSTS]
--           ([DloAccessedPostsID]
--           ,[LpuMain]
--           ,[PostCode]
--           ,[LastName]
--           ,[FirstName]
--           ,[FatherName]
--           ,[Speciality]
--           ,[PostType]
--           ,[Category]
--           ,[BeginAccessDate]
--           ,[EndAccessDate]
--           ,[BeginWorkDate]
--           ,[CertificateDate]
--           ,[MedicalSpecialityCode]
--           ,ExpulsionDate
--           ,Snils)
--         SELECT
--           dap.ID--(<DloAccessedPostsID, uniqueidentifier,>
--           ,dap.LpuMain--,<LpuMain, uniqueidentifier,>
--           ,dap.PostCode--,<PostCode, varchar(255),>
--           ,dap.LastName--,<LastName, varchar(32),>
--           ,dap.FirstName--,<FirstName, varchar(32),>
--           ,dap.FatherName--,<FatherName, varchar(32),>
--           ,dap.Speciality--,<Speciality, uniqueidentifier,>
--           ,dap.PostType--,<PostType, uniqueidentifier,>
--           ,dap.Category--,<Category, int,>
--           ,dap.BeginAccessDate--,<BeginAccessDate, datetime,>
--           ,dap.EndAccessDate--,<EndAccessDate, datetime,>
--           ,dap.BeginWorkDate--,<BeginWorkDate, datetime,>
--           ,dap.CertificateDate--,<CertificateDate, datetime,>
--           ,dap.MedicalSpecialityCode--,<MedicalSpecialityCode, varchar(16),>)
--           ,dap.ExpulsionDate
--           ,dap.Snils
--        FROM #DLO_ACCESSED_POSTS dap
                      
--    END                                     
--END                                                    
