-- Tables

/***** Object:  Table AKUZ.T_ORG_ADDRESS *****/
CREATE TABLE AKUZ.T_ORG_ADDRESS
(
    "orgaddressid" integer NOT NULL,
    "house" varchar(8) NULL,
    "block" varchar(8) NULL,
    "flat" varchar(8) NULL,
    "zipcode" varchar(10) NULL,
    "freeaddressstring" varchar(256) NULL,
    "city" uuid NULL,
    "street" uuid NULL,
    "politicaldistrict" uuid NULL,
    "district" uuid NULL,
    "location" integer NULL,
    "aoguid" uuid NULL,
    "houseguid" uuid NULL
);

-- Alters

ALTER TABLE AKUZ.T_ORG_ADDRESS ADD CONSTRAINT PK_T_ORG_ADDRESS
PRIMARY KEY (OrgAddressID);

ALTER TABLE AKUZ.T_ORG_ADDRESS ADD CONSTRAINT FK_T_CITY_T_ORG_ADDRESS_City
FOREIGN KEY("city") REFERENCES AKUZ.T_CITY (CityID);

ALTER TABLE AKUZ.T_ORG_ADDRESS ADD CONSTRAINT FK_T_STREET_T_ORG_ADDRESS_Street
FOREIGN KEY("street") REFERENCES AKUZ.T_STREET (StreetID);

ALTER TABLE AKUZ.T_ORG_ADDRESS ADD CONSTRAINT FK_T_POLITICAL_DISTRICT_T_ORG_ADDRESS_PoliticalDistrict
FOREIGN KEY("politicaldistrict") REFERENCES AKUZ.T_POLITICAL_DISTRICT (PoliticalDistrictID);

ALTER TABLE AKUZ.T_ORG_ADDRESS ADD CONSTRAINT FK_T_DISTRICT_T_ORG_ADDRESS_District
FOREIGN KEY("district") REFERENCES AKUZ.T_DISTRICT (DistrictID);


-- AKUZ.T_CDA_PARTICIPANT_DEATH_CERT
ALTER TABLE AKUZ.T_CDA_PARTICIPANT_DEATH_CERT DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_PARTICIPANT_DEATH_CERT_FullADDR2;
ALTER TABLE AKUZ.T_CDA_PARTICIPANT_DEATH_CERT DROP COLUMN "fulladdr2";

-- AKUZ.T_CDA_PAYMENT_SOURCE_INFO
ALTER TABLE AKUZ.T_CDA_PAYMENT_SOURCE_INFO DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_PAYMENT_SOURCE_INFO_FullADDR2;
ALTER TABLE AKUZ.T_CDA_PAYMENT_SOURCE_INFO DROP COLUMN "fulladdr2";

-- AKUZ.T_CDA_SECTION_DEADPATINFO_PERINATAL
ALTER TABLE AKUZ.T_CDA_SECTION_DEADPATINFO_PERINATAL DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_SECTION_DEADPATINFO_PERINATAL_Dea_DF54F761;
ALTER TABLE AKUZ.T_CDA_SECTION_DEADPATINFO_PERINATAL DROP COLUMN "deathplacefulladdr2";

-- добавление адреса
ALTER TABLE AKUZ.T_CDA_SECTION_DEADPATINFO_PERINATAL ADD "birthplaceaddress" integer NULL;
ALTER TABLE AKUZ.T_CDA_SECTION_DEADPATINFO_PERINATAL ADD CONSTRAINT FK_T_ORG_ADDRESS_T_CDA_SECTION_DEADPATINFO_PERINATAL_B_3B560F9C
FOREIGN KEY("birthplaceaddress") REFERENCES AKUZ.T_ORG_ADDRESS (OrgAddressID);

ALTER TABLE AKUZ.T_CDA_SECTION_DEADPATINFO_PERINATAL ADD "deathplaceaddress" integer NULL;
ALTER TABLE AKUZ.T_CDA_SECTION_DEADPATINFO_PERINATAL ADD CONSTRAINT FK_T_ORG_ADDRESS_T_CDA_SECTION_DEADPATINFO_PERINATAL_D_6E5F9E6A
FOREIGN KEY("deathplaceaddress") REFERENCES AKUZ.T_ORG_ADDRESS (OrgAddressID);

-- AKUZ.T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS__85299762;
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS DROP COLUMN "fulladdr2";
-- добавление адреса
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS ADD "orgaddress" integer NULL;
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS ADD CONSTRAINT FK_T_ORG_ADDRESS_T_CDA_SECTION_DOCINFO_ABSENCE_CONTACT_308B8883
FOREIGN KEY("orgaddress") REFERENCES AKUZ.T_ORG_ADDRESS (OrgAddressID);

-- AKUZ.T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_SECTION_DOCINFO_CHEMICAL_POISONIN_C878275E;
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING DROP COLUMN "fulladdr2";
-- добавление адреса
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING ADD "orgaddress" integer NULL;
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING ADD CONSTRAINT FK_T_ORG_ADDRESS_T_CDA_SECTION_DOCINFO_CHEMICAL_POISON_71F02B6F
FOREIGN KEY("orgaddress") REFERENCES AKUZ.T_ORG_ADDRESS (OrgAddressID);

-- AKUZ.T_CDA_SECTION_DOCINFO_COUPON2
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_COUPON2 DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_SECTION_DOCINFO_COUPON2_FullADDR2;
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_COUPON2 DROP COLUMN "fulladdr2";

-- AKUZ.T_CDA_SECTION_MOTHINFO
ALTER TABLE AKUZ.T_CDA_SECTION_MOTHINFO DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_SECTION_MOTHINFO_FullADDR2;
ALTER TABLE AKUZ.T_CDA_SECTION_MOTHINFO DROP COLUMN "fulladdr2";

-- AKUZ.T_CDA_SECTION_NBINFO
ALTER TABLE AKUZ.T_CDA_SECTION_NBINFO DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_SECTION_NBINFO_FullADDR2;
ALTER TABLE AKUZ.T_CDA_SECTION_NBINFO DROP COLUMN "fulladdr2";

-- AKUZ.T_CDA_SECTION_WORK_MED_CERTIFICATE
ALTER TABLE AKUZ.T_CDA_SECTION_WORK_MED_CERTIFICATE DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_SECTION_WORK_MED_CERTIFICATE_FullADDR2;
ALTER TABLE AKUZ.T_CDA_SECTION_WORK_MED_CERTIFICATE DROP COLUMN "fulladdr2";
-- добавление адреса
ALTER TABLE AKUZ.T_CDA_SECTION_WORK_MED_CERTIFICATE ADD "orgaddress" integer NULL;
ALTER TABLE AKUZ.T_CDA_SECTION_WORK_MED_CERTIFICATE ADD CONSTRAINT FK_T_ORG_ADDRESS_T_CDA_SECTION_WORK_MED_CERTIFICATE_OrgAddress
FOREIGN KEY("orgaddress") REFERENCES AKUZ.T_ORG_ADDRESS (OrgAddressID);

-- AKUZ.T_CDA_WORKPLACE
ALTER TABLE AKUZ.T_CDA_WORKPLACE DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_WORKPLACE_FullADDR2;
ALTER TABLE AKUZ.T_CDA_WORKPLACE DROP COLUMN "fulladdr2";
-- добавление адреса
ALTER TABLE AKUZ.T_CDA_WORKPLACE ADD "orgaddress" integer NULL;
ALTER TABLE AKUZ.T_CDA_WORKPLACE ADD CONSTRAINT FK_T_ORG_ADDRESS_T_CDA_WORKPLACE_OrgAddress
FOREIGN KEY("orgaddress") REFERENCES AKUZ.T_ORG_ADDRESS (OrgAddressID);

-- AKUZ.T_HOSP_LANAM_SOCANAM_EDU
-- добавление адреса
ALTER TABLE AKUZ.T_HOSP_LANAM_SOCANAM_EDU ADD "orgaddress" integer NULL;
ALTER TABLE AKUZ.T_HOSP_LANAM_SOCANAM_EDU ADD CONSTRAINT FK_T_ORG_ADDRESS_T_HOSP_LANAM_SOCANAM_EDU_OrgAddress
FOREIGN KEY("orgaddress") REFERENCES AKUZ.T_ORG_ADDRESS (OrgAddressID);

--AKUZ.T_CDA_MSE_REFERRAL
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_LegalFullADDR2;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL DROP COLUMN "legalfulladdr2";

ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_RealFullADDR2;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL DROP COLUMN "realfulladdr2";

--AKUZ.T_CDA_MSE_REFERRAL_GUARDIAN
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_GUARDIAN DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_GUARDIAN_FullADDR2;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_GUARDIAN DROP COLUMN "fulladdr2";

-- AKUZ.T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG_FullADDR2;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG DROP COLUMN "fulladdr2";
-- добавление адреса
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG ADD "orgaddress" integer NULL;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG ADD CONSTRAINT FK_T_ORG_ADDRESS_T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG_OrgAddress
FOREIGN KEY("orgaddress") REFERENCES AKUZ.T_ORG_ADDRESS (OrgAddressID);

-- AKUZ.T_CDA_MSE_REFERRAL_SECTION_EDU
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_EDU DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_SECTION_EDU_FullADDR2;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_EDU DROP COLUMN "fulladdr2";
-- добавление адреса
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_EDU ADD "orgaddress" integer NULL;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_EDU ADD CONSTRAINT FK_T_ORG_ADDRESS_T_CDA_MSE_REFERRAL_SECTION_EDU_OrgAddress
FOREIGN KEY("orgaddress") REFERENCES AKUZ.T_ORG_ADDRESS (OrgAddressID);

-- AKUZ.T_CDA_MSE_REFERRAL_SECTION_SCOPORG
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_SCOPORG DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_SECTION_SCOPORG_FullADDR2;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_SCOPORG DROP COLUMN "fulladdr2";
-- добавление адреса
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_SCOPORG ADD "orgaddress" integer NULL;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_SCOPORG ADD CONSTRAINT FK_T_ORG_ADDRESS_T_CDA_MSE_REFERRAL_SECTION_SCOPORG_OrgAddress
FOREIGN KEY("orgaddress") REFERENCES AKUZ.T_ORG_ADDRESS (OrgAddressID);

-- AKUZ.T_CDA_MSE_REFERRAL_SECTION_WORK
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_WORK DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_SECTION_WORK_FullADDR2;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_WORK DROP COLUMN "fulladdr2";
-- добавление адреса
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_WORK ADD "orgaddress" integer NULL;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_WORK ADD CONSTRAINT FK_T_ORG_ADDRESS_T_CDA_MSE_REFERRAL_SECTION_WORK_OrgAddress
FOREIGN KEY("orgaddress") REFERENCES AKUZ.T_ORG_ADDRESS (OrgAddressID);

-- AKUZ.T_CDA_MSE_REFERRAL_SOURCE_PAYMENT
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SOURCE_PAYMENT DROP CONSTRAINT FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_SOURCE_PAYMENT_FullADDR2;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SOURCE_PAYMENT DROP COLUMN "fulladdr2";
-- добавление адреса
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SOURCE_PAYMENT ADD "orgaddress" integer NULL;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SOURCE_PAYMENT ADD CONSTRAINT FK_T_ORG_ADDRESS_T_CDA_MSE_REFERRAL_SOURCE_PAYMENT_OrgAddress
FOREIGN KEY("orgaddress") REFERENCES AKUZ.T_ORG_ADDRESS (OrgAddressID);

-- перенос адресов из FullADDR в OrgAddress (для избавления от ссылок на FullADDR)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- создание таблички #tableOrgAddr
DROP TABLE IF EXISTS TableOrgAddr;
CREATE TEMPORARY table TableOrgAddr(FreeAddrString varchar(256), AOGUID uuid, HouseGUID uuid);

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_WORKPLACE
insert into TableOrgAddr (FreeAddrString, AOGUID, HouseGUID)
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_WORKPLACE work
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = work.FullADDR
where work.FullADDR is not null and work.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_SECTION_WORK_MED_CERTIFICATE
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_SECTION_WORK_MED_CERTIFICATE medCert
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = medCert.FullADDR
where medCert.FullADDR is not null and medCert.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_SECTION_DEADPATINFO_PERINATAL
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_SECTION_DEADPATINFO_PERINATAL deadpatinfo
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = deadpatinfo.DeathPlaceFullADDR
where deadpatinfo.DeathPlaceFullADDR is not null and deadpatinfo.DeathPlaceAddress is null

union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_SECTION_DEADPATINFO_PERINATAL deadpatinfo
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = deadpatinfo.BirthPlaceFullADDR
where deadpatinfo.BirthPlaceFullADDR is not null and deadpatinfo.BirthPlaceAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS absContInf
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = absContInf.FullADDR
where absContInf.FullADDR is not null and absContInf.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING chemPo
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = chemPo.FullADDR
where chemPo.FullADDR is not null and chemPo.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_HOSP_LANAM_SOCANAM_EDU
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_HOSP_LANAM_SOCANAM_EDU socEdu
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = socEdu.FullADDR
where socEdu.FullADDR is not null and socEdu.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG reprOrg
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = reprOrg.FullADDR
where reprOrg.FullADDR is not null and reprOrg.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_MSE_REFERRAL_SECTION_EDU
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_MSE_REFERRAL_SECTION_EDU edu
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = edu.FullADDR
where edu.FullADDR is not null and edu.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_MSE_REFERRAL_SECTION_SCOPORG
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_MSE_REFERRAL_SECTION_SCOPORG ent
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = ent.FullADDR
where FullADDR is not null and OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_MSE_REFERRAL_SECTION_WORK
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_MSE_REFERRAL_SECTION_WORK ent
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = ent.FullADDR
where FullADDR is not null and OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_MSE_REFERRAL_SOURCE_PAYMENT
union 
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_MSE_REFERRAL_SOURCE_PAYMENT ent
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = ent.FullADDR
where FullADDR is not null and OrgAddress is null;
----------------------------------------------------------------
--добавление в t_entity_ids
if not exists(select * from dbo.T_ENTITY_IDS where EntName = 'AKUZ.ORG_ADDRESS')
then
    insert into dbo.t_entity_ids (EntName, FieldName, id) values('AKUZ.ORG_ADDRESS', 'PrimaryKey', 0);
end if;
-- объявление переменной для проставления primary keys элементов таблицы
DO $$
declare
    i int = (select COALESCE(id, 0) from dbo.T_ENTITY_IDS where EntName = 'AKUZ.ORG_ADDRESS' limit 1);
-- заполнение таблички t_org_address из #tableOrgAddr
begin
insert into akuz.t_org_address (OrgAddressID, FreeAddressString, AOGUID, HouseGUID)
select (ROW_NUMBER() over (order by tab.FreeAddrString))+i, tab.FreeAddrString, tab.AOGUID, tab.HouseGUID
	from tableOrgAddr tab
	where
		(not exists(select 1 from akuz.t_org_address where (FreeAddressString = tab.FreeAddrString and AOGUID = tab.AOGUID) or HouseGUID = tab.HouseGUID));
-- обновление int-айдишника в табличке T_ENTITY_IDS
update dbo.T_ENTITY_IDS d
set d.id = (select max(OrgAddressID) from akuz.T_ORG_ADDRESS)
where EntName = 'AKUZ.ORG_ADDRESS';
end
$$;
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_WORKPLACE
update akuz.t_cda_workplace ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
		from fias.T_FullAddr fullAddr 
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null and fullAddr.AddrID = ent.FullADDR;
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_SECTION_WORK_MED_CERTIFICATE
update akuz.T_CDA_SECTION_WORK_MED_CERTIFICATE ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
		from fias.T_FullAddr fullAddr 
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null and fullAddr.AddrID = ent.FullADDR;
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_SECTION_DEADPATINFO_PERINATAL (для DeathPlaceAddress)
update akuz.T_CDA_SECTION_DEADPATINFO_PERINATAL ent
set ent.DeathPlaceAddress = orgaddress.OrgAddressID, ent.DeathPlaceFullADDR = null
		from fias.T_FullAddr fullAddr 
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.DeathPlaceAddress is null and fullAddr.AddrID = ent.FullADDR;
-- проставление новых адресов в сущность CDA_SECTION_DEADPATINFO_PERINATAL (для BirthPlaceAddress)
update akuz.T_CDA_SECTION_DEADPATINFO_PERINATAL ent
set ent.BirthPlaceAddress = orgaddress.OrgAddressID, ent.BirthPlaceFullADDR = null
		from fias.T_FullAddr fullAddr 
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.BirthPlaceAddress is null and fullAddr.AddrID = ent.FullADDR;
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS
update akuz.T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
		from fias.T_FullAddr fullAddr 
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null and fullAddr.AddrID = ent.FullADDR;
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_SECTION_DOCINFO_CHEMICAL_POISONING
update akuz.T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
		from fias.T_FullAddr fullAddr
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null and fullAddr.AddrID = ent.FullADDR;
----------------------------------------------------------------
-- проставление новых адресов в сущность HOSP_LANAM_SOCANAM_EDU
update akuz.T_HOSP_LANAM_SOCANAM_EDU ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
		from fias.T_FullAddr fullAddr 
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null and fullAddr.AddrID = ent.FullADDR;
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_MSE_REFERRAL_SCOPORG_REPR_ORG
update akuz.T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
		from fias.T_FullAddr fullAddr
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null and fullAddr.AddrID = ent.FullADDR;
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_MSE_REFERRAL_SECTION_EDU
update akuz.T_CDA_MSE_REFERRAL_SECTION_EDU ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
		from fias.T_FullAddr fullAddr
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null and  on fullAddr.AddrID = ent.FullADDR;
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_MSE_REFERRAL_SECTION_SCOPORG
update akuz.T_CDA_MSE_REFERRAL_SECTION_SCOPORG ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
		from fias.T_FullAddr fullAddr
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null and fullAddr.AddrID = ent.FullADDR;
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_MSE_REFERRAL_SECTION_WORK
update akuz.T_CDA_MSE_REFERRAL_SECTION_WORK ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
		from fias.T_FullAddr fullAddr 
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null and fullAddr.AddrID = ent.FullADDR;
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_MSE_REFERRAL_SOURCE_PAYMENT
update akuz.T_CDA_MSE_REFERRAL_SOURCE_PAYMENT ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
		from fias.T_FullAddr fullAddr
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null and fullAddr.AddrID = ent.FullADDR;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- обновление города в добавленных адресах в ORG_ADDRESS (в конце скрипта после всех сущностей скорее всего)
update orgaddr
set orgaddr.City = (select top 1 CityID from akuz.t_city where OKATO = addrobj.OKATO)
	from akuz.T_ORG_ADDRESS orgaddr
		join fias.t_fulladdr fulladdr on fulladdr.AOGUID =  orgaddr.AOGUID and orgaddr.HouseGUID = fulladdr.HouseGuid
		join fias.t_addrobj addrobj on fulladdr.AOGUID = addrobj.AOGUID
	where orgaddr.City is null and addrobj.CODE is not null;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
																					-- избавление от FullAddr
-- drop колонки fullADDR из таблицы T_CDA_WORKPLACE
ALTER TABLE AKUZ.T_CDA_WORKPLACE DROP CONSTRAINT FK_T_FULLADDR_T_CDA_WORKPLACE_FullADDR;
ALTER TABLE AKUZ.T_CDA_WORKPLACE DROP COLUMN "fulladdr";
-- drop колонки fullADDR из таблицы T_CDA_SECTION_WORK_MED_CERTIFICATE
ALTER TABLE AKUZ.T_CDA_SECTION_WORK_MED_CERTIFICATE DROP CONSTRAINT FK_T_FULLADDR_T_CDA_SECTION_WORK_MED_CERTIFICATE_FullADDR;
ALTER TABLE AKUZ.T_CDA_SECTION_WORK_MED_CERTIFICATE DROP COLUMN "fulladdr";
-- drop колонки fullADDR из таблицы T_CDA_SECTION_DEADPATINFO_PERINATAL
ALTER TABLE AKUZ.T_CDA_SECTION_DEADPATINFO_PERINATAL DROP CONSTRAINT FK_T_FULLADDR_T_CDA_SECTION_DEADPATINFO_PERINATAL_Birt_13D62292;
ALTER TABLE AKUZ.T_CDA_SECTION_DEADPATINFO_PERINATAL DROP COLUMN "birthplacefulladdr";
ALTER TABLE AKUZ.T_CDA_SECTION_DEADPATINFO_PERINATAL DROP CONSTRAINT FK_T_FULLADDR_T_CDA_SECTION_DEADPATINFO_PERINATAL_Deat_475D7C2A;
ALTER TABLE AKUZ.T_CDA_SECTION_DEADPATINFO_PERINATAL DROP COLUMN "deathplacefulladdr";
-- drop колонки fullADDR из таблицы T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS DROP CONSTRAINT FK_T_FULLADDR_T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_I_CCCD9679;
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS DROP COLUMN "fulladdr";
-- drop колонки fullADDR из таблицы T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING DROP CONSTRAINT FK_T_FULLADDR_T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING_FullADDR;
ALTER TABLE AKUZ.T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING DROP COLUMN "fulladdr";
-- drop колонки fullADDR из таблицы T_HOSP_LANAM_SOCANAM_EDU
ALTER TABLE AKUZ.T_HOSP_LANAM_SOCANAM_EDU DROP CONSTRAINT FK_T_FULLADDR_T_HOSP_LANAM_SOCANAM_EDU_FullADDR;
ALTER TABLE AKUZ.T_HOSP_LANAM_SOCANAM_EDU DROP COLUMN "fulladdr";
-- drop колонки fullADDR из таблицы T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG DROP CONSTRAINT FK_T_FULLADDR_T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG_FullADDR;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG DROP COLUMN "fulladdr";
-- drop колонки fullADDR из таблицы T_CDA_MSE_REFERRAL_SECTION_EDU
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_EDU DROP CONSTRAINT FK_T_FULLADDR_T_CDA_MSE_REFERRAL_SECTION_EDU_FullADDR;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_EDU DROP COLUMN "fulladdr";
-- drop колонки fullADDR из таблицы T_CDA_MSE_REFERRAL_SECTION_SCOPORG
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_SCOPORG DROP CONSTRAINT FK_T_FULLADDR_T_CDA_MSE_REFERRAL_SECTION_SCOPORG_FullADDR;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_SCOPORG DROP COLUMN "fulladdr";
-- drop колонки fullADDR из таблицы T_CDA_MSE_REFERRAL_SECTION_WORK
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_WORK DROP CONSTRAINT FK_T_FULLADDR_T_CDA_MSE_REFERRAL_SECTION_WORK_FullADDR;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SECTION_WORK DROP COLUMN "fulladdr";
-- drop колонки fullADDR из таблицы T_CDA_MSE_REFERRAL_SOURCE_PAYMENT
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SOURCE_PAYMENT DROP CONSTRAINT FK_T_FULLADDR_T_CDA_MSE_REFERRAL_SOURCE_PAYMENT_FullADDR;
ALTER TABLE AKUZ.T_CDA_MSE_REFERRAL_SOURCE_PAYMENT DROP COLUMN "fulladdr";