if exists ( select * from sys.all_objects ao where ao.name = 'sp_LoadZlListFinish' ) DROP PROCEDURE [dbo].[sp_LoadZlListFinish]
GO

CREATE PROCEDURE [dbo].[sp_LoadZlListFinish]
    @ID uniqueidentifier
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    update t1
        set SNILS = t0.SNILS
    from iszl.T_INSURED_POLICY_RECORD_DUMMY t0
    join iszl.T_INSURED t1 on t0.Insured = t1.InsuredID
    where InsuredPolicyRecordDummyID = @ID
    and t1.SNILS is null and isnull(rtrim(t0.SNILS),'') != ''

    --прикрепление
    
    INSERT INTO [ISZL].[T_MO_ATTACHMENT]
        ([MoAttachmentID]
        ,[Mo]
        ,[PODR]
        ,[UCH]
        ,[AttachDate]
        ,[CreationDateTime]
        ,[SNILS_VR]
        ,[Cause]
        ,[Status]
        ,[Insured]
        ,[ActualDate])
    select
    newid()
    ,aAttach.OrganizationID
    ,t0.[PODR]
    ,t0.[UCH]
    ,t0.ATTACH_DATE
    ,getdate()
    ,null
    ,t0.[Cause]
    ,t0.[Status]
    ,t0.[Insured]
    ,null as [ActualDate]
    from iszl.T_INSURED_POLICY_RECORD_DUMMY t0
    cross apply
    (
        select top 1 t100.OrganizationID OrganizationID
        FROM ISZL.T_MO t100 
        JOIN ISZL.T_ORGANIZATION t101 ON t101.OrganizationID = t100.OrganizationID
        JOIN ISZL.T_F003 t102 ON t102.MCOD = t101.Code
        JOIN ISZL.T_INSURED_POLICY_RECORD_DUMMY iprd on iprd.MO = t101.Code
    ) aAttach
    left join iszl.T_MO_ATTACHMENT t1 on t1.Insured = t0.Insured
    and t0.ATTACH_DATE = t1.AttachDate
    and t0.CAUSE = t1.Cause
    and aAttach.OrganizationID = t1.Mo
    and t0.PODR = t1.PODR
    and t0.UCH = t1.UCH
    and t0.STATUS = t1.Status
    where t1.MoAttachmentID is null
    and InsuredPolicyRecordDummyID = @ID

    --документ
    INSERT INTO [ISZL].[T_IDENTITY_DOCUMENT]
           ([IdentityDocumentID]
           ,[DocSeries]
           ,[DocNumber]
           ,[EditDate]
           ,[F011DocumentType]
           ,[InsuredPolicyRecord])
    select newid(), t.DOCSER, t.DOCNUM, getdate(), t.F011DocumentType, t.InsuredPolicyRecord
    from (
        select
              t0.DOCSER
            , t0.DOCNUM
            , t0.DOCTYPE as [F011DocumentType]
            , t0.InsuredPolicyRecord
        from iszl.T_INSURED_POLICY_RECORD_DUMMY t0
        left join iszl.T_IDENTITY_DOCUMENT t1 on 
            t0.DOCTYPE = t1.F011DocumentType
            and t0.DOCNUM = t1.DocNumber
            and isnull(t0.DOCSER,'') = isnull(t1.DocSeries,'')
        where t1.IdentityDocumentID is null
            and InsuredPolicyRecordDummyID = @ID
            and t0.InsuredPolicyRecord is not null
            and not exists(
                select *
                from ISZL.T_IDENTITY_DOCUMENT d
                where d.InsuredPolicyRecord = t0.InsuredPolicyRecord
            )
        group by t0.DOCSER, t0.DOCNUM, t0.DOCTYPE, t0.InsuredPolicyRecord
    ) t
    
    
    --контакты
    INSERT INTO [ISZL].[T_INSURED_CONTACT]
           ([InsuredContactID]
           ,[Contact]
           ,[ContactType]
           ,[Insured])
     select
         newid() as [InsuredContactID]
        ,t1.CONTACT
        ,t3.ContactTypeID
        ,t0.[Insured]
    from iszl.T_INSURED_POLICY_RECORD_DUMMY t0
        join iszl.T_INSURED_POLICY_RECORD_DUMMY_CONTACT t1 on t0.InsuredPolicyRecordDummyID = t1.InsuredPolicyRecordDummy
        join iszl.T_CONTACT_TYPE t3 on t3.Name = t1.CONTACT_TYPE
        left join [ISZL].[T_INSURED_CONTACT] t2 on t2.Insured = t0.Insured and t2.Contact = t1.CONTACT
    where t2.InsuredContactID is null 
            and 
        InsuredPolicyRecordDummyID = @ID

END
