if exists ( select * from sys.procedures p where p.name = 'sp_F018BankPayOrgLoad' ) DROP PROCEDURE [dbo].[sp_F018BankPayOrgLoad]
GO
CREATE PROCEDURE [dbo].[sp_F018BankPayOrgLoad]
 @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

  SELECT 
  T.c.value('./idzap[1]', 'int') AS idzap
  ,T.c.value('./tf_okato[1]','varchar(5)') as tf_okato
  ,T.c.value('./orgcod[1]', 'int') AS orgcod
  ,T.c.value('./billcod[1]', 'varchar(50)') AS billcod
  ,T.c.value('./accounttype[1]', 'int') AS accounttype
  ,T.c.value('./accountnum[1]', 'varchar(500)') AS accountnum
  ,T.c.value('./bankbik[1]', 'varchar(255)') AS bankbik
  ,T.c.value('./corraccount[1]', 'varchar(255)') AS corraccount
  ,T.c.value('./bankname[1]', 'varchar(500)') AS bankname
  ,T.c.value('./inn[1]', 'varchar(12)') AS inn
  ,T.c.value('./kpp[1]', 'varchar(9)') AS kpp
  ,T.c.value('./sta_character[1]', 'varchar(255)') AS sta_character
  ,T.c.value('./tofk_bik[1]', 'varchar(255)') AS tofk_bik
  ,T.c.value('./tofk_code[1]', 'varchar(255)') AS tofk_code
  ,T.c.value('./tofk_name[1]', 'varchar(255)') AS tofk_name
  ,T.c.value('./personal_account[1]', 'varchar(500)') AS personal_account
  ,T.c.value('./function_personal_account[1]', 'varchar(500)') AS function_personal_account
  ,T.c.value('./name_agency[1]', 'varchar(500)') AS name_agency
  ,T.c.value('./personal_account_income_admin[1]', 'varchar(500)') AS personal_account_income_admin
  ,T.c.value('./inn_income_admin[1]', 'varchar(500)') AS inn_income_admin
  ,T.c.value('./name_income_admin[1]', 'varchar(1000)') AS name_income_admin
  ,T.c.value('./code_tofk[1]', 'varchar(100)') AS code_tofk
  ,T.c.value('./kpp_income_admin[1]', 'varchar(9)') AS kpp_income_admin
  ,case when T.c.value('./datebeg[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./datebeg[1]', 'varchar(20)'),103) end AS datebeg
  ,case when T.c.value('./dateend[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./dateend[1]', 'varchar(20)'),103) end AS dateend
  into #temp_f018
  FROM   @Xml.nodes('/packet/zap') T(c)

UPDATE t1
	SET DateEnd = t2.dateend
FROM [IES].T_F018_BANK_PAY_ORG t1
join #temp_f018 t2 on t2.idzap = t1.idzap

INSERT INTO [IES].T_F018_BANK_PAY_ORG
			(F018BankPayOrgID
			, IDZAP			
			, TF_OKATO
			, OrgCod
			, BillCod
			, AccountType
			, AccountNum
			, BankBik
			, CorrAccount			
			, BankName
			, Inn
			, KPP
			, Sta_Character
			, TofkBik			
			, TofkCode
			, TofkName
			, PersonalAccount
			, FunctionPersonalAccount
			, NameAgency
			, PersonalAccountIncomeAdmin
			, InnIncomeAdmin
			, NameIncomeAdmin
			, CodeTofk
			, KppIncomeAdmin			
			, DateBeg
			, DateEnd)
	select
			NEWID()
			, t1.IDZAP			
			, t1.TF_OKATO
			, ab.F019PersAccOrgID
			, aa.F017BillTypesID
			, t1.AccountType
			, t1.AccountNum
			, t1.BankBik
			, t1.CorrAccount			
			, t1.BankName
			, t1.Inn
			, t1.KPP
			, t1.Sta_Character
			, t1.tofk_bik
			, t1.tofk_code
			, t1.tofk_name
			, t1.personal_account
			, t1.function_personal_account
			, t1.name_agency
			, t1.personal_account_income_admin
			, t1.inn_income_admin
			, t1.name_income_admin
			, t1.code_tofk
			, t1.kpp_income_admin
			, t1.DateBeg
			, t1.DateEnd
FROM #temp_f018 t1
left join [IES].T_F018_BANK_PAY_ORG t2 on t2.IDZAP = t1.idzap
cross apply(select top 1 * from ies.T_F017_BILL_TYPES f017 where f017.Billcod = t1.billcod) aa
cross apply(select top 1 * from ies.T_F019_PERS_ACC_ORG f019 where f019.ORG_COD = t1.orgcod) ab
where t2.F018BankPayOrgID is null

drop table #temp_f018

END