SELECT
     s.APP_PKID,
     c.NLS AS uuid,
     s.TIMEZONE AS timeShift,
     s.TARIFFPLAN_LOCALSYSTEMID AS tariffId,
     c.DTYPE AS isJuridical,
     st.PARENT_LOCALSYSTEMID AS isPostpaid,
     c.EMAIL,
     d.LOCALSYSTEMID AS filialId,
     s.LOCALSYSTEMID AS imsi,
     s.IMSI AS imsi_57,
     c.LOCALSYSTEMID AS clientId,
     c.APP_PKID AS instId,
     c.nls AS nls,
     c.TITLE AS companyName,
     c.INN AS companyINN,
     a.REGION AS companyRegion,
     a.PARSED_REGION AS companyRegion_parse,
     a.LOCALITY AS companyCity,
     a.POSTCODE AS companyPostcode,
     a.TITLE AS companyAddress,
     c.PHONE AS companyPhone
FROM
    MDM6.CLIENT c,
    MDM6.SUBSCRIBER s,
    MDM6.SETTLEMENTTYPE st,
    MDM6.DEPARTMENT d,
    MDM6.ADDRESS a
WHERE 1=1
    AND c.NLS = '318001007675'
    AND s.DTYPE = 'SubscriberEco'
    AND c.DTYPE IN ('PhysicalClient','Client')
    AND c.DELETED < 1
    AND s.DELETED < 1
    AND s.VERSION = 0
    AND c.VERSION = 0
    AND d.VERSION = 0
    AND a.VERSION = 0
    AND c.SETTLEMENTTYPE_LOCALSYSTEMID = st.LOCALSYSTEMID
    AND c.SETTLEMENTTYPE_APP_PKID = st.APP_PKID
--  AND st.PARENT_APP_PKID = st.APP_PKID
--  AND st.PARENT_LOCALSYSTEMID = st.LOCALSYSTEMID
    AND c.DEPARTMENT_LOCALSYSTEMID = d.LOCALSYSTEMID
    AND c.DEPARTMENT_APP_PKID = d.APP_PKID
    AND c.DEPARTMENT_VERSION = d.VERSION
    AND c.LOCALSYSTEMID = s.CLIENT_LOCALSYSTEMID
    AND c.APP_PKID = s.CLIENT_APP_PKID
    AND c.VERSION = s.CLIENT_VERSION
    AND c.LAWADDRESS_LOCALSYSTEMID = a.LOCALSYSTEMID
    AND c.LAWADDRESS_APP_PKID = a.APP_PKID
    AND c.LAWADDRESS_VERSION = a.VERSION
--  AND c.POSTADDRESS_APP_PKID = a.APP_PKID
--  AND c.POSTADDRESS_LOCALSYSTEMID = a.LOCALSYSTEMID
--  AND c.POSTADDRESS_VERSION = a.VERSION
ORDER BY TRUNC (s.PERSISTDATE) DESC