SELECT * FROM
(
  SELECT
    ast.ROW_ID                  AS "ROW_ID",
    ax.ATTRIB_12            AS "LAST_SRV_DT",
    ast.SERIAL_NUM               AS "VIN",
    ac.RELATION_TYPE_CD     AS "RELATION_TYPE_CD",
    ac.CONTACT_ID           AS "CLIENT_ID",
    con.BU_ID           AS "BU_ID",
    con.EMAIL_ADDR          AS "EMAIL_ADDR",
    con.FST_NAME                AS "FST_NAME",
    con.HOME_PH_NUM         AS "HOME_PH_NUM",
    con.CELL_PH_NUM         AS "CELL_PH_NUM",
    NVL(ax.SERVICE_NUM, 0)          AS "SERVICE_NUM",
    cc.row_id               AS "SEGMENT3_CAMP_CON_ID",
    atx.FIRST_SALE_DT       AS "FIRST_SALE_DT",
    ast.x_sara_start_dt AS "SARA_START_DT",
    ast.x_sara_end_dt AS "SARA_END_DT",
    ast.PROD_ID AS "PROD_ID"
    --,org.NAME,con.SEMINAR_INVIT_FLG,ac.RELATION_TYPE_CD,ac.PAYOR_FLG,con.EMAIL_ADDR,con.SUPPRESS_EMAIL_FLG,cx.ATTRIB_06,cx.ATTRIB_40
  FROM
    SIEBEL.S_ASSET ast
    --left
    join (SELECT par_row_id, MAX(ATTRIB_12) AS ATTRIB_12, COUNT(ATTRIB_12) AS SERVICE_NUM
      FROM SIEBEL.S_ASSET_XM ax  WHERE ax.ATTRIB_12 IS NOT NULL AND ax.TYPE = 'Maintenance History' AND (ax.name LIKE 'HMCIS%' OR ax.name LIKE 'Genesis%'
    OR ax.name LIKE 'KMR%' OR ax.name LIKE '1%' OR ax.name LIKE 'C%' OR ax.name LIKE 'S%' OR ax.name LIKE 'P:%'  )
    GROUP BY par_row_id ) ax ON ax.par_row_id = ast.row_id
    JOIN SIEBEL.S_ASSET_CON ac ON ast.row_id = ac.ASSET_ID
    JOIN SIEBEL.S_CONTACT con ON ac.CONTACT_ID = con.PAR_ROW_ID
    JOIN SIEBEL.S_ORG_EXT org ON con.BU_ID = org.ROW_ID
    JOIN SIEBEL.S_PARTY par ON con.PAR_ROW_ID = par.ROW_ID
    JOIN SIEBEL.S_CONTACT_PSX conp ON par.ROW_ID = conp.PAR_ROW_ID
    JOIN SIEBEL.S_CONTACT_X cx ON par.ROW_ID = cx.PAR_ROW_ID
    JOIN SIEBEL.S_ASSET_ATX atx ON atx.row_id = ast.row_id
    left join SIEBEL.S_CAMP_CON cc ON cc.con_per_id=ac.CONTACT_ID AND cc.x_vin=ast.SERIAL_NUM AND cc.X_SEGMENT_TYPE='Customer intention survey'
  WHERE
    ast.SERIAL_NUM IS NOT NULL
    AND atx.FIRST_SALE_DT IS NOT NULL
    AND org.NAME = 'KMR'
    AND con.SEMINAR_INVIT_FLG IS NOT NULL
    AND con.SUSP_WTCH_FLG IS NOT NULL
    AND ac.RELATION_TYPE_CD = 'владелец'
    AND ac.PAYOR_FLG = 'Y'
    AND con.PRIV_FLG= 'N'
    AND par.PARTY_TYPE_CD <> 'Suspect'
    AND con.MEMBER_FLG = 'N'
    AND conp.ATTRIB_01 <> 'Y'
    AND con.EMAIL_ADDR IS NOT NULL
    AND (con.HOME_PH_NUM <> '+79999999999' OR con.HOME_PH_NUM IS NULL)
    AND (con.CELL_PH_NUM <> '+79999999999' OR con.CELL_PH_NUM IS NULL)
    AND con.SUPPRESS_EMAIL_FLG = 'N'
    AND cx.ATTRIB_06 = 'Да'
    AND cx.ATTRIB_40 = 'Да'
) T1,
  SIEBEL.S_PROD_INT_X T2

 WHERE
  (
    TRUNC(T1.LAST_SRV_DT) = TRUNC(SYSDATE - 7)
    AND NOT (
      T1.SARA_END_DT IS NOT NULL AND
      T1.SARA_END_DT > TRUNC(SYSDATE + 32)
      OR (
        T2.ATTRIB_34 = 'KH Quoris'
        OR T2.ATTRIB_34 = 'Stinger'
        OR T2.ATTRIB_34 = 'HM 07'
        OR T2.ATTRIB_34 = 'K9 RJ'
      )
      AND T1.FIRST_SALE_DT > DATE '2018-11-30'
      AND T1.FIRST_SALE_DT > TRUNC(SYSDATE - 365 * 3 + 32)
      OR (T2.ATTRIB_34 = 'KH Quoris')
      AND T1.FIRST_SALE_DT < DATE '2018-01-02'
      AND T1.FIRST_SALE_DT > TRUNC(SYSDATE - 365 * 7 + 32)
      OR NOT (
        (
          T2.ATTRIB_34 = 'KH Quoris'
          OR T2.ATTRIB_34 = 'Stinger'
          OR T2.ATTRIB_34 = 'HM 07'
          OR T2.ATTRIB_34 = 'K9 RJ'
        )
      )
      AND T1.FIRST_SALE_DT > TRUNC(SYSDATE - 365 + 32)
    )
  )
  AND t1.PROD_ID = t2.row_id
  AND VIN = 'KNABX512BCT054401'

  /*select ATTRIB_12, ax.* from s_asset_xm ax where ax.type = 'Maintenance History' and (ax.name like 'HMCIS%' or ax.name like 'Genesis%'
    or ax.name like 'KMR%' or ax.name like '1%' or ax.name like 'C%' or ax.name like 'S%' or ax.name like 'P:%'  )
    and par_row_id = '1-F15-1614' */