CREATE TABLE BM_ADDRESS_OBJECT
(
  ADDRESS_OBJECT_ID       NUMBER                NOT NULL,
  ADDRESS_OBJECT_TYPE_ID  NUMBER,
  PARENT_OBJECT_ID        NUMBER,
  NAME                    VARCHAR2(255 BYTE),
  KLADR_CODE              VARCHAR2(255 BYTE),
  PARENT_KLADR_CODE       VARCHAR2(255 BYTE)
);
CREATE TABLE BM_ADDRESS_OBJECT_TYPE
(
  ADDRESS_OBJECT_TYPE_ID  NUMBER                NOT NULL,
  SHORT_NAME              VARCHAR2(255 BYTE)    NOT NULL,
  NAME                    VARCHAR2(255 BYTE)    NOT NULL,
  KLADR_CODE              NUMBER                NOT NULL,
  PARENT_KLADR_CODE       NUMBER,
  ADDRESS_LEVEL           NUMBER
);

select *
from BM_ADDRESS_OBJECT AO
join BM_ADDRESS_OBJECT_TYPE AT on (AT.ADDRESS_OBJECT_TYPE_ID = AO.ADDRESS_OBJECT_TYPE_ID)
connect by AO.ADDRESS_OBJECT_ID = prior AO.PARENT_OBJECT_ID
start with AO.ADDRESS_OBJECT_ID = <address_id>

select client.client_id, adr.city, adr.street, adr.building
from client
join <запрос из КЛАДР> adr on (adr.address_id = client.address_id)

select S.SERVICE_ID, C.CUSTOMER_ID, CC.VALUE as CLIENT, to_number(CA.VALUE) as ADDRESS_ID
from SERVICES S
join CUSTOMERS C on (C.CUSTOMER_ID = S.CUSTOMER_ID)
join BM_CUSTOMER_CONTACT CC on (CC.CUSTOMER_ID = C.CUSTOMER_ID and CC.CONTACT_DICT_ID = 100)
join BM_CUSTOMER_CONTACT CA on (CA.CUSTOMER_ID = C.CUSTOMER_ID and CA.CONTACT_DICT_ID = 90)
where S.TYPE_ID = 204
and S.STATUS != -20
and months_between(trunc(sysdate), S.RECKONING_DATE) < 10

select *
from BM_ADDRESS_OBJECT AO
join BM_ADDRESS_OBJECT_TYPE AT on (AT.ADDRESS_OBJECT_TYPE_ID = AO.ADDRESS_OBJECT_TYPE_ID)
connect by AO.ADDRESS_OBJECT_ID = prior AO.PARENT_OBJECT_ID
start with AO.ADDRESS_OBJECT_ID = 1084321916

select S.SERVICE_ID, C.CUSTOMER_ID, to_number(CA.VALUE) as ADDRESS_ID
, AO.*, AT.*
from SERVICES S
join CUSTOMERS C on (C.CUSTOMER_ID = S.CUSTOMER_ID)
join BM_CUSTOMER_CONTACT CC on (CC.CUSTOMER_ID = C.CUSTOMER_ID and CC.CONTACT_DICT_ID = 100)
join BM_CUSTOMER_CONTACT CA on (CA.CUSTOMER_ID = C.CUSTOMER_ID and CA.CONTACT_DICT_ID = 90)
join BM_ADDRESS_OBJECT AO on (AO.ADDRESS_OBJECT_ID = to_number(CA.VALUE))
join BM_ADDRESS_OBJECT_TYPE AT on (AT.ADDRESS_OBJECT_TYPE_ID = AO.ADDRESS_OBJECT_TYPE_ID)
where S.TYPE_ID = 204
and S.STATUS != -20
and months_between(trunc(sysdate), S.RECKONING_DATE) < 10

select S.SERVICE_ID, C.CUSTOMER_ID, to_number(CA.VALUE) as ADDRESS_ID
, AO.*, AT.*
from SERVICES S
join CUSTOMERS C on (C.CUSTOMER_ID = S.CUSTOMER_ID)
join BM_CUSTOMER_CONTACT CC on (CC.CUSTOMER_ID = C.CUSTOMER_ID and CC.CONTACT_DICT_ID = 100)
join BM_CUSTOMER_CONTACT CA on (CA.CUSTOMER_ID = C.CUSTOMER_ID and CA.CONTACT_DICT_ID = 90)
join BM_ADDRESS_OBJECT AO on (AO.ADDRESS_OBJECT_ID = to_number(CA.VALUE))
join BM_ADDRESS_OBJECT_TYPE AT on (AT.ADDRESS_OBJECT_TYPE_ID = AO.ADDRESS_OBJECT_TYPE_ID)
where S.TYPE_ID = 204
and S.STATUS != -20
and months_between(trunc(sysdate), S.RECKONING_DATE) < 10
connect by AO.ADDRESS_OBJECT_ID = prior AO.PARENT_OBJECT_ID
start with AO.ADDRESS_OBJECT_ID = to_number(CA.VALUE)

select *
from BM_ADDRESS_OBJECT AO
join BM_ADDRESS_OBJECT_TYPE AT on (AT.ADDRESS_OBJECT_TYPE_ID = AO.ADDRESS_OBJECT_TYPE_ID)
connect by AO.ADDRESS_OBJECT_ID = prior AO.PARENT_OBJECT_ID
start with AO.ADDRESS_OBJECT_ID in
(
select to_number(CA.VALUE) as ADDRESS_ID
from SERVICES S
join CUSTOMERS C on (C.CUSTOMER_ID = S.CUSTOMER_ID)
join BM_CUSTOMER_CONTACT CC on (CC.CUSTOMER_ID = C.CUSTOMER_ID and CC.CONTACT_DICT_ID = 100)
join BM_CUSTOMER_CONTACT CA on (CA.CUSTOMER_ID = C.CUSTOMER_ID and CA.CONTACT_DICT_ID = 90)
where S.TYPE_ID = 204
and S.STATUS != -20
and months_between(trunc(sysdate), S.RECKONING_DATE) < 10
)