9iVDKyaZ

1DROP TABLE IF EXISTS tmp_hm_account_org_person;
2CREATE TEMPORARY TABLE tmp_hm_account_org_person AS
3SELECT 
4	account_guid, 
5	org_person_guid
6FROM
7	shm.hm_account_org_person --Связь ФЛ и его ЛС
8WHERE
9	--entity_type = 'CITIZEN'  --  CITIZEN(гражданин), ORGANIZATION(организация)
10	--AND link_status = 'ACTIVE'
11	COALESCE(link_status, 'ACTIVE') = 'ACTIVE'
12	AND org_person_guid = '002560ff-c151-4b4b-94ee-8a61abba1ee1'
13	AND added_manually is false;
14
15drop table if exists tmp_hm_default_premise;
16create temporary table tmp_hm_default_premise
17as 
18select hm_house_guid from shm.hm_apartments 
19where guid in (select default_premise_guid from sppa.ppa_citizens where guid = '002560ff-c151-4b4b-94ee-8a61abba1ee1');
20
21
22SELECT DISTINCT
23	pers.org_person_guid AS individual_person_guid,
24	pers.account_guid AS individual_account_guid,
25	acc.account_type, --management  / solid_municipal_waste  / supplying  /  capital_repair  /  billing_center authority
26	acc.account_number,
27	acc_h.organization_guid,
28	acc.house_guid,
29	COALESCE( hm_ap.fias_house_guid, dw.fias_house_guid) fias_house_guid,
30	COALESCE( hm_ap.fias_house_code, dw.fias_house_code) fias_house_code,
31	CASE WHEN def.hm_house_guid IS NOT NULL THEN true ELSE FALSE END AS is_default_apartment,
32	COALESCE( hm_ap.fias_area_code, dw.fias_area_code) fias_area_code,
33	COALESCE( hm_ap.fias_city_code, dw.fias_city_code) fias_city_code,
34	COALESCE( hm_ap.fias_ctar_code, dw.fias_ctar_code) fias_ctar_code,
35	COALESCE( hm_ap.fias_place_code, dw.fias_place_code) fias_place_code,
36	COALESCE( hm_ap.fias_street_code, dw.fias_street_code) fias_street_code,
37	COALESCE( hm_ap.fias_region_code, dw.fias_region_code) fias_region_code
38FROM
39	tmp_hm_account_org_person pers
40	JOIN shm.hm_accounts acc ON acc.guid =  pers.account_guid
41	JOIN shm.hm_accounts acc_h ON acc_h.house_guid = acc.house_guid AND acc_h.account_status = 'ACTUAL' AND acc.account_status = 'ACTUAL' 
42	LEFT JOIN shm.hm_houses_apartment hm_ap ON hm_ap.guid = acc.house_guid AND hm_ap.entity_is_actual
43	LEFT JOIN shm.hm_houses_dwelling dw ON dw.guid = acc.house_guid AND dw.entity_is_actual
44 	--помещение по умолчанию 
45        left join tmp_hm_default_premise def on def.hm_house_guid = hm_ap.guid or def.hm_house_guid = dw.guid