if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_IRPPackLoad_Magadan]') and xtype = N'P')
drop procedure [dbo].[sp_IRPPackLoad_Magadan];
go
create procedure [dbo].[sp_IRPPackLoad_Magadan]
@FileId uniqueidentifier = null,
@FileName varchar(max) = null,
@Worker uniqueidentifier = null,
@x xml = null
AS 
BEGIN 

SELECT
	NewId() as [IrpID],
    T.c.value('./ZGLV[1]/VERSION[1]', 'varchar(5)') AS [VERSION],
    T.c.value('./ZGLV[1]/DATA[1]', 'datetime') AS [DATA],
    T.c.value('./ZGLV[1]/YEAR[1]', 'int') AS [YEAR],
    T.c.value('./ZGLV[1]/MONTH[1]', 'int') AS [MONTH],
    T.c.value('./ZGLV[1]/DAY[1]', 'int') AS [DAY],
    T.c.value('./ZGLV[1]/TIME[1]', 'varchar(7)') AS [TIME],
    T.c.value('./ZGLV[1]/SMO[1]', 'varchar(5)') AS [SMO],
    T.c.value('./ZGLV[1]/FILENAME[1]', 'varchar(26)') AS [FILENAME]
 into #IRPF_ZGLV
 FROM   @x.nodes('/IRP_LIST') T(c)

--------------------------------------------IRP
SELECT
    NewId() as [PersonInfoIdZ],
    NewId() as [PersonInfoIdIn],
    T.c.value('./TF_ID[1]', 'varchar(36)')                  AS [ItemID], --TF_ID--
    T.c.value('./N_IRP[1]', 'varchar(36)')					AS [N_IRP],
    T.c.value('./DATE_CREATE[1]', 'datetime')				AS [DATE_CREATE],
    T.c.value('./TIME_CREATE[1]', 'varchar(5)')				AS [TIME_CREATE],
    T.c.value('./IRP_TYPE[1]', 'int')						AS [IRP_TYPE],
    T.c.value('./HOW[1]', 'int')							AS [HOW],
    T.c.value('./D_WR[1]', 'datetime')						AS [D_WR],
    T.c.value('./ADD_B[1]', 'int')							AS [ADD_B],
    T.c.value('./WAY[1]', 'int')							AS [WAY],
    T.c.value('./WAY_N[1]', 'int')							AS [WAY_N],
    T.c.value('./THEME[1]', 'varchar(7)')					AS [THEME],
    T.c.value('./C_ORG[1]', 'varchar(6)')					AS [C_ORG],
    T.c.value('./COVID[1]', 'int')							AS [COVID],
    T.c.value('./COVID_V[1]', 'int')						AS [COVID_V],
    T.c.value('./TEXT[1]', 'varchar(1000)')					AS [TEXT],
    T.c.value('./PR_OUT[1]', 'int')							AS [PR_OUT],
    T.c.value('./DATE_CROSS[1]', 'datetime')				AS [DATE_CROSS],
    T.c.value('./TIME_CROSS[1]', 'varchar(5)')				AS [TIME_CROSS],
    T.c.value('./OTV_T[1]', 'int')							AS [OTV_T],
    T.c.value('./OTV_KON[1]', 'varchar(8)')					AS [OTV_KON],
    T.c.value('./LINE_1[1]', 'int')							AS [LINE_1],
    T.c.value('./EMPLOYEE_1[1]', 'varchar(36)')				AS [EMPLOYEE_1],
    T.c.value('./LINE_2[1]', 'int')							AS [LINE_2],
    T.c.value('./EMPLOYEE_IT[1]', 'varchar(36)')			AS [EMPLOYEE_IT],
    T.c.value('./DATA_PLAN[1]', 'datetime')					AS [DATA_PLAN],
    T.c.value('./DATE_CLOSE[1]', 'datetime')				AS [DATE_CLOSE],
    T.c.value('./RESULT[1]', 'int')							AS [RESULT],
    T.c.value('./RESULT_T[1]', 'varchar(4000)')				AS [RESULT_T],
    T.c.value('./STATUS[1]', 'int')							AS [STATUS],

        -- Z_SV: applicant information --
        T.c.value('./Z_SV[1]/Z_F[1]', 'varchar(40)')			AS [Z_F],
        T.c.value('./Z_SV[1]/Z_I[1]', 'varchar(40)')			AS [Z_I],
        T.c.value('./Z_SV[1]/Z_O[1]', 'varchar(40)')			AS [Z_O],
        T.c.value('./Z_SV[1]/Z_DR[1]', 'datetime')				AS [Z_DR],
        T.c.value('./Z_SV[1]/Z_ENP[1]', 'varchar(16)')			AS [Z_ENP],
        T.c.value('./Z_SV[1]/Z_SMO[1]', 'varchar(5)')			AS [Z_SMO],
        T.c.value('./Z_SV[1]/Z_DOCTYPE[1]', 'varchar(2)')		AS [Z_DOCTYPE],
        T.c.value('./Z_SV[1]/Z_DOCSER[1]', 'varchar(10)')		AS [Z_DOCSER],
        T.c.value('./Z_SV[1]/Z_DOCNUM[1]', 'varchar(20)')		AS [Z_DOCNUM],
        T.c.value('./Z_SV[1]/ADR[1]', 'varchar(120)')			AS [ADR],
        T.c.value('./Z_SV[1]/PHONE[1]', 'varchar(20)')			AS [PHONE],
        T.c.value('./Z_SV[1]/E-MAIL[1]', 'varchar(40)')			AS [EMAIL],

        -- IN_SV: information about person the appeal is about --
        T.c.value('./IN_SV[1]/IN_F[1]', 'varchar(40)')			AS [IN_F],
        T.c.value('./IN_SV[1]/IN_I[1]', 'varchar(40)')			AS [IN_I],
        T.c.value('./IN_SV[1]/IN_O[1]', 'varchar(40)')			AS [IN_O],
        T.c.value('./IN_SV[1]/IN_DR[1]', 'datetime')			AS [IN_DR],
        T.c.value('./IN_SV[1]/IN_ENP[1]', 'varchar(16)')		AS [IN_ENP],
        T.c.value('./IN_SV[1]/IN_SMO[1]', 'varchar(5)')			AS [IN_SMO],
        T.c.value('./IN_SV[1]/IN_DOCTYPE[1]', 'varchar(2)')		AS [IN_DOCTYPE],
        T.c.value('./IN_SV[1]/IN_DOCSER[1]', 'varchar(10)')		AS [IN_DOCSER],
        T.c.value('./IN_SV[1]/IN_DOCNUM[1]', 'varchar(20)')		AS [IN_DOCNUM],

        -- ZH: block for complaint brief info and full complaint info --
        T.c.value('./ZH_D[1]/Z_F[1]', 'varchar(3)')				AS [ZH_D],
        T.c.value('./ZH_D[1]/ZH_IN[1]', 'int')					AS [ZH_IN],
        T.c.value('./ZH_D[1]/SOL[1]', 'int')					AS [SOL],
        T.c.value('./ZH_D[1]/DATE_RETURN[1]', 'datetime')		AS [DATE_RETURN],
        T.c.value('./ZH_D[1]/SUM_RETURN[1]', 'varchar(15)')		AS [SUM_RETURN],
        T.c.value('./ZH_D[1]/ORG_RETURN[1]', 'varchar(6)')		AS [ORG_RETURN],
        0 as [MATCHES]
into #IRP_ITEM
FROM @x.nodes('/IRP_LIST/IRP') T(c)

BEGIN

create table #Errors
(
	IM_POL varchar(10),
	BAS_EL varchar(10),
	OSHIB int,
    N_ZAP varchar(36),
	COMMENT varchar(250)
)

BEGIN
	insert into #Errors (IM_POL, BAS_EL, COMMENT, N_ZAP)
	select 'N_IRP', 'NRP', 'Обнаружены дубли записей по полю N_IRP!', N_IRP
	from #IRP_ITEM s
	group by s.N_IRP 
	having  count(N_IRP) > 1
END 

Begin
    insert into #Errors (IM_POL, BAS_EL, COMMENT, N_ZAP)
	select 'WAY_N', 'NRP', 'Не заполнена организация поступления для обращения!', N_IRP
    from #IRP_ITEM s
	where s.WAY in (3,4,5)
    and s.WAY_N is Null
end

Begin
    insert into #Errors (IM_POL, BAS_EL, COMMENT, N_ZAP)
	select 'C_ORG', 'NRP', 'Не заполнена объект обращения(организации) для жалобы!', N_IRP
    from #IRP_ITEM s
	where s.IRP_TYPE = 2
    and s.C_ORG is Null
end

Begin
    insert into #Errors (IM_POL, BAS_EL, COMMENT, N_ZAP)
	select 'D_WR', 'NRP', 'Не заполнена дата письменного обращения!', N_IRP
    from #IRP_ITEM s
	where s.HOW in (2,3,4)
    and s.D_WR is Null
end

if (select count(*) from #Errors) = 0
begin

-- id пользователя
DECLARE @UserId uniqueidentifier
SET @UserId = (select top 1 tu.[UserID] from VCCC.T_USERS tu
join ISZL.T_USERS u on u.UserID = tu.[UserID]
join ISZL.T_POST p on u.Post = p.PostID
where Worker = @Worker)

-- если грузим из папки, то пустышка будет
if(@UserId is null)
begin
set @UserId = (select u.UserID from vclib.T_IDENTITY i
join VCLib.T_USERS u on u.[Identity] = i.[IdentityID]
where i.[IdentityName] = 'iszl_service_user')
end


INSERT INTO [VCCC].[T_APPEAL_TO_CALL_CENTER]
    ([AppealToCallCenterID]
    ,[Number]
    ,[NumberWriting]
    ,[DateTimeCreate]
    ,[DateTimeWriting]
    ,[WayOrganizationID]
    ,[How]
    ,[Theme]
    ,[Text]
    ,[Result]
    ,[Zh_d]
    ,[Otv_t]
    ,[Otv_kon]
    ,[DataPlan]
    ,[DataPlan2]
    ,[DateClose]
    ,[Way]
    ,[ADR]
    ,[PHONE]
    ,[EMAIL]
    ,[Comments]
    ,[IN_RD]
    ,[Deleted]
    ,[DeleteReason]
    ,[Redirected]
    ,[Solution]
    ,[Initiator]
    ,[DateReturn]
    ,[ReturningOrganization]
    ,[ReturningMOSMOID]
    ,[SumReturn]
    ,[OrganizationID]
    ,[ResultChoice]
    ,[AddressedToBoss]
    ,[Matches]
    ,[AcceptedUser]
    ,[ResponsibleUser]
    ,[AcceptedAppeal]
    ,[PR_OUT]
    ,[DATE_TIME_CROSS]
    ,[IsCovid]
    ,[VaccinationCOVID]
    ,[CallbackPlatform]
    ,[AppealType]
    ,[type_]
    ,[InformedBox]
    ,[StatusModificationDate]
    ,[Status]
    ,[NumberSogaz]
    ,[Source]
    ,[ChangedUser]
    ,[SocialCategory])
SELECT
    ii.ItemID--(<AppealToCallCenterID, uniqueidentifier,>
    ,ii.N_IRP--,<Number, varchar(36),>
    ,null--,<NumberWriting, varchar(36),>
    ,ii.DATE_CREATE--,<DateTimeCreate, datetime,>
    ,ii.D_WR--,<DateTimeWriting, datetime,>
    ,way_org.OrganizationID--,<WayOrganizationID, uniqueidentifier,>
    ,ii.HOW--,<How, int,>
    ,ath.AppealThemeID--,<Theme, uniqueidentifier,>
    ,ii.[TEXT]--,<Text, varchar(1000),>
    ,ii.[RESULT_T]--,<Result, varchar(4000),>
    ,ii.ZH_D--,<Zh_d, int,>
    ,ii.OTV_T--,<Otv_t, int,>
    ,ii.OTV_KON--,<Otv_kon, varchar(8),>
    ,ii.DATA_PLAN--,<DataPlan, datetime,>
    ,null--,<DataPlan2, datetime,>
    ,ii.DATE_CLOSE--,<DateClose, datetime,>
    ,apw.AppealWayID--,<Way, int,>
    ,ii.ADR--,<ADR, varchar(120),>
    ,ii.PHONE--,<PHONE, varchar(20),>
    ,ii.EMAIL--,<EMAIL, varchar(40),>
    ,null--,<Comments, varchar(max),>
    ,null--,<IN_RD, int,>
    ,0--,<Deleted, bit,>
    ,null--,<DeleteReason, varchar(255),>
    ,null--,<Redirected, int,>
    ,ii.[SOL]--,<Solution, int,>
    ,0--,<Initiator, int,>
    ,ii.DATE_RETURN--,<DateReturn, datetime,>
    ,null--,<ReturningOrganization, int,>
    ,ret_org.OrganizationID--,<ReturningMOSMOID, uniqueidentifier,>
    ,ii.SUM_RETURN--,<SumReturn, decimal(15,2),>
    ,null--,<OrganizationID, uniqueidentifier,>
    ,ii.RESULT--,<ResultChoice, int,>
    ,ISNULL(ii.ADD_B, 0)--,<AddressedToBoss, bit,>
    ,0--,<Matches, bit,>
    ,@UserId--,<AcceptedUser, uniqueidentifier,>
    ,@UserId--,<ResponsibleUser, uniqueidentifier,>
    ,null--,<AcceptedAppeal, uniqueidentifier,>
    ,ii.PR_OUT--,<PR_OUT, int,>
    ,ii.DATE_CROSS--,<DATE_TIME_CROSS, datetime,>
    ,ISNULL(ii.COVID, 0)--,<IsCovid, int,>
    ,ISNULL(ii.COVID_V, 0)--,<VaccinationCOVID, int,>
    ,0--,<CallbackPlatform, bit,>
    ,apt.AppealTypeID--,<AppealType, uniqueidentifier,>
    ,e.EntityID--,<type_, int,>
    ,0--,<InformedBox, bit,>
    ,GETDATE()--,<StatusModificationDate, datetime,>
    ,ii.[STATUS]--,<Status, int,>
    ,null--,<NumberSogaz, varchar(255),>
    ,null--,<Source, int,>
    ,@UserId--,<ChangedUser, uniqueidentifier,>
    ,null--,<SocialCategory, int,>)
from #IRP_ITEM ii
left join iszl.T_ORGANIZATION way_org on way_org.Code = ii.WAY_N
left join iszl.T_ORGANIZATION ret_org on ret_org.Code = ii.ORG_RETURN
left join vccc.T_APPEAL_TYPE apt on apt.IrpType = ii.IRP_TYPE
left join vccc.T_APPEAL_THEME ath on ath.Code = ii.THEME
left join VCCC.T_APPEAL_WAY apw on apw.Code = ii.WAY
join VCLIB.T_ENTITIES e on e.[EntName] = 'APPEAL_TO_CALL_CENTER' and e.[Schema] = 'ISZL'

INSERT INTO [ISZL].[T_APPEAL_TO_CALL_CENTER]
    ([AppealToCallCenterID]
    ,[Number]
    ,[IrpType]
    ,[DateTimeCreate]
    ,[How]
    ,[Text]
    ,[Zh_d]
    ,[Otv_t]
    ,[Otv_kon]
    ,[DataPlan]
    ,[DateClose]
    ,[InsuredPolicyRecordZ]
    ,[InsuredPolicyRecordIn]
    ,[InsuredZ]
    ,[InsuredIn]
    ,[Z_F]
    ,[Z_I]
    ,[Z_O]
    ,[Z_DR]
    ,[Z_ENP]
    ,[Z_SMO]
    ,[Z_DOCTYPE]
    ,[Z_DOCSER]
    ,[Z_DOCNUM]
    ,[ADR]
    ,[PHONE]
    ,[EMAIL]
    ,[IN_F]
    ,[IN_I]
    ,[IN_O]
    ,[IN_DR]
    ,[IN_ENP]
    ,[IN_SMO]
    ,[IN_DOCTYPE]
    ,[IN_DOCSER]
    ,[IN_DOCNUM]
    ,[Result]
    ,[Way]
    ,[Comments]
    ,[DataPlan2]
    ,[NumberWriting]
    ,[DateTimeWriting]
    ,[IN_RD]
    ,[Theme]
    ,[Deleted]
    ,[DeleteReason]
    ,[Redirected]
    ,[Solution]
    ,[Initiator]
    ,[DateReturn]
    ,[ReturningOrganization]
    ,[SumReturn]
    ,[ResultChoice]
    ,[AddressedToBoss]
    ,[Matches]
    ,[AcceptedUser]
    ,[ResponsibleUser]
    ,[AcceptedAppeal]
    ,[PR_OUT]
    ,[DATE_TIME_CROSS]
    ,[IsCovid]
    ,[VaccinationCOVID]
    ,[CallbackPlatform]
    ,[AppealType])
SELECT
    ii.ItemID--<AppealToCallCenterID, uniqueidentifier,>
   ,ii.N_IRP--,<Number, varchar(36),>
   ,ii.IRP_TYPE--,<IrpType, int,>
   ,ii.DATE_CREATE--,<DateTimeCreate, datetime,>
   ,ii.HOW--,<How, int,>
   ,ii.[TEXT]--,<Text, varchar(1000),>
   ,ii.ZH_D--,<Zh_d, int,>
   ,ii.OTV_T--,<Otv_t, int,>
   ,ii.OTV_KON--,<Otv_kon, varchar(8),>
   ,ii.DATA_PLAN--,<DataPlan, datetime,>
   ,ii.DATE_CLOSE--,<DateClose, datetime,>
   ,null--,<InsuredPolicyRecordZ, uniqueidentifier,>
   ,null--,<InsuredPolicyRecordIn, uniqueidentifier,>
   ,null--,<InsuredZ, uniqueidentifier,>
   ,null--,<InsuredIn, uniqueidentifier,>
   ,ii.Z_F--,<Z_F, varchar(40),>
   ,ii.Z_I--,<Z_I, varchar(40),>
   ,ii.Z_O--,<Z_O, varchar(40),>
   ,ii.Z_DR--,<Z_DR, datetime,>
   ,ii.Z_ENP--,<Z_ENP, varchar(16),>
   ,ii.Z_SMO--,<Z_SMO, varchar(5),>
   ,ii.Z_DOCTYPE--,<Z_DOCTYPE, varchar(2),>
   ,ii.Z_DOCSER--,<Z_DOCSER, varchar(10),>
   ,ii.Z_DOCNUM--,<Z_DOCNUM, varchar(20),>
   ,ii.ADR--,<ADR, varchar(120),>
   ,ii.PHONE--,<PHONE, varchar(20),>
   ,ii.EMAIL--,<EMAIL, varchar(40),>
   ,ii.IN_F--,<IN_F, varchar(40),>
   ,ii.IN_I--,<IN_I, varchar(40),>
   ,ii.IN_O--,<IN_O, varchar(40),>
   ,ii.IN_DR--,<IN_DR, datetime,>
   ,ii.IN_ENP--,<IN_ENP, varchar(16),>
   ,ii.IN_SMO--,<IN_SMO, varchar(5),>
   ,ii.IN_DOCTYPE--,<IN_DOCTYPE, varchar(2),>
   ,ii.IN_DOCSER--,<IN_DOCSER, varchar(10),>
   ,ii.IN_DOCNUM--,<IN_DOCNUM, varchar(20),>
   ,ii.RESULT_T--,<Result, varchar(4000),>
   ,apw.AppealWayID--,<Way, int,>
   ,null--,<Comments, varchar(max),>
   ,null--,<DataPlan2, datetime,>
   ,null--,<NumberWriting, varchar(36),>
   ,null--,<DateTimeWriting, datetime,>
   ,null--,<IN_RD, int,>
   ,ath.AppealThemeID--,<Theme, uniqueidentifier,>
   ,0--,<Deleted, bit,>
   ,null--,<DeleteReason, varchar(255),>
   ,null--,<Redirected, int,>
   ,null--,<Solution, int,>
   ,null--,<Initiator, int,>
   ,ii.DATE_RETURN--,<DateReturn, datetime,>
   ,null--,<ReturningOrganization, int,>
   ,ii.SUM_RETURN--,<SumReturn, decimal(15,2),>
   ,ii.RESULT--,<ResultChoice, int,>
   ,ISNULL(ii.ADD_B, 0)--,<AddressedToBoss, bit,>
   ,ii.MATCHES--,<Matches, bit,>
   ,@UserId--,<AcceptedUser, uniqueidentifier,>
   ,@UserId--,<ResponsibleUser, uniqueidentifier,>
   ,null--,<AcceptedAppeal, uniqueidentifier,>
   ,ii.PR_OUT--,<PR_OUT, int,>
   ,ii.DATE_CROSS--,<DATE_TIME_CROSS, datetime,>
   ,ISNULL(ii.COVID, 0)--,<IsCovid, int,>
   ,ISNULL(ii.COVID_V, 0)--,<VaccinationCOVID, int,>
   ,0--,<CallbackPlatform, bit,>
   ,apt.AppealTypeID--,<AppealType, uniqueidentifier,>
from #IRP_ITEM as ii
join [VCCC].[T_APPEAL_TO_CALL_CENTER] atc on atc.AppealToCallCenterID = ii.ItemID
left join vccc.T_APPEAL_TYPE apt on apt.IrpType = ii.IRP_TYPE
left join vccc.T_APPEAL_THEME ath on ath.Code = ii.THEME
left join VCCC.T_APPEAL_WAY apw on apw.Code = ii.WAY

INSERT INTO [VCCC].[T_PERSON_INFO]
    ([PersonInfoID]
    ,[FAM]
    ,[IM]
    ,[OT]
    ,[DR]
    ,[ENP]
    ,[SMOID]
    ,[MOID]
    ,[DOCTYPEID]
    ,[DOCSER]
    ,[DOCNUM]
    ,[AppealToCallCenter]
    ,[type_])
SELECT
    ii.PersonInfoIdZ--(<PersonInfoID, uniqueidentifier,>
    ,ii.Z_F--<FAM, varchar(40),>
    ,ii.Z_I--<IM, varchar(40),>
    ,ii.Z_O--<OT, varchar(40),>
    ,ii.Z_DR--<DR, datetime,>
    ,ii.Z_ENP--<ENP, varchar(16),>
    ,ii.Z_SMO--<SMOID, varchar(5),>
    ,null--<MOID, varchar(6),>
    ,ii.Z_DOCTYPE--<DOCTYPEID, varchar(3),>
    ,ii.Z_DOCSER--<DOCSER, varchar(10),>
    ,ii.Z_DOCNUM--<DOCNUM, varchar(20),>
    ,ii.ItemID--<AppealToCallCenter, uniqueidentifier,>
    ,(select EntityID from VCLIB.T_ENTITIES e where e.[EntName] = 'PERSON_INFO_Z' and e.[Schema] = 'ISZL')--<type_, int,>
from #IRP_ITEM ii

INSERT INTO [ISZL].[T_PERSON_INFO_Z]
    ([PersonInfoID])
SELECT
    ii.PersonInfoIdZ --(<PersonInfoID, uniqueidentifier,>
from #IRP_ITEM ii

INSERT INTO [VCCC].[T_PERSON_INFO]
    ([PersonInfoID]
    ,[FAM]
    ,[IM]
    ,[OT]
    ,[DR]
    ,[ENP]
    ,[SMOID]
    ,[MOID]
    ,[DOCTYPEID]
    ,[DOCSER]
    ,[DOCNUM]
    ,[AppealToCallCenter]
    ,[type_])
SELECT
    ii.PersonInfoIdIn--(<PersonInfoID, uniqueidentifier,>
    ,ii.IN_F--<FAM, varchar(40),>
    ,ii.IN_I--<IM, varchar(40),>
    ,ii.IN_O--<OT, varchar(40),>
    ,ii.IN_DR--<DR, datetime,>
    ,ii.IN_ENP--<ENP, varchar(16),>
    ,ii.IN_SMO--<SMOID, varchar(5),>
    ,null--<MOID, varchar(6),>
    ,ii.IN_DOCTYPE--<DOCTYPEID, varchar(3),>
    ,ii.IN_DOCSER--<DOCSER, varchar(10),>
    ,ii.IN_DOCNUM--<DOCNUM, varchar(20),>
    ,ii.ItemID--<AppealToCallCenter, uniqueidentifier,>
    ,(select EntityID from VCLIB.T_ENTITIES e where e.[EntName] = 'PERSON_INFO_IN' and e.[Schema] = 'ISZL')--<type_, int,>
from #IRP_ITEM ii

INSERT INTO [ISZL].[T_PERSON_INFO_IN]
    ([PersonInfoID])
SELECT
    ii.PersonInfoIdIn --(<PersonInfoID, uniqueidentifier,>
from #IRP_ITEM ii

insert into VCCC.T_APPEAL_COMPLAINTS
    (AppealComplaintsID,
    AppealToCallCenter,
    ComplaintOrganizationId,
    type_)
select
    NewId(),
    ii.ItemID,
    org.OrganizationID,
    (select EntityID from VCLIB.T_ENTITIES e where e.[EntName] = 'APPEAL_COMPLAINTS' and e.[Schema] = 'ISZL')
from #IRP_ITEM ii
join iszl.T_ORGANIZATION org on org.Code = ii.C_ORG

	end
END

select (case 
        when (select COUNT(*) from #Errors) = 0 
        then cast(1 as bit)
        else cast(0 as bit)
    end) as IsOk,
    '<?xml version="1.0" encoding="Windows-1251"?>' 
    + 
    (
        SELECT 
            IM_POL as 'IM_POL'
			,OSHIB as 'OSHIB'
			,BAS_EL as 'BAS_EL'
            ,COMMENT as 'COMMENT'
            ,N_ZAP as 'N_ZAP'
        FROM #Errors 
        FOR XML PATH('ERR'),ROOT('ROOT')
    ) as Content,
    (case 
        when (select COUNT(*) from #Errors) = 0 
        then cast(0 as bit)
        else cast(1 as bit)
    end) as IsSingleXmlFlk,
    'FLK_'+ @FileName+'.xml'  as [Filename],
    'FLK' as [TargetDirectory],
    null as [CodePage],
    cast(null as varchar(max)) as lexem
union all
select 
    null, --IsOk--
    null, --Content--
    null, --IsSingleXmlFlk--
    null, --Filename--
    null, --TargetDirectory--
    null, --CodePage--
    'new Process("ProvideInsuredProcess", new Apartment().GetInstance("ISZL.PERSON_INFO_Z", Cast("'+ cast(z.PersonInfoIdZ as varchar(36)) +'", "Guid"))).$Run()'
from #IRP_ITEM z
where not exists(select 1/0 from #Errors)
union all
select 
    null, --IsOk--
    null, --Content--
    null, --IsSingleXmlFlk--
    null, --Filename--
    null, --TargetDirectory--
    null, --CodePage--
    'new Process("ProvideInsuredProcess", new Apartment().GetInstance("ISZL.PERSON_INFO_IN", Cast("'+ cast(z.PersonInfoIdIn as varchar(36)) +'", "Guid"))).$Run()'
from #IRP_ITEM z
where not exists(select 1/0 from #Errors)

DROP TABLE #IRPF_ZGLV
DROP TABLE #IRP_ITEM
END