CREATE MATERIALIZED VIEW SALESFORCE.CONCILIATION_TEST (
    COD_PERSONA_CLIMAPRO_I,
    LOPD_COMS_COMERCIALES
)
TABLESPACE SALESFORCE_DAT
NOCACHE
NOPARALLEL
BUILD IMMEDIATE
USING NO INDEX
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
SELECT
  PRS.COD_PERSONA_CLIMAPRO_I AS COD_PERSONA_CLIMAPRO_I,
  PRS.LOPD_COMS_COMERCIALES AS LOPD_COMS_COMERCIALES
FROM SLF_ILT_DIM_PERSONS PRS
WHERE PRS.COD_PERSONA_CLIMAPRO_I IN (
    SELECT
        RPV.COD_PERSONA_CLIMAPRO_I AS COD_PERSONA_CLIMAPRO_I
    FROM
        SLF_ILT_DIM_RELATIONS_P_V RPV
    JOIN
        SLF_ILT_DIM_VEHICLES VCS on RPV.COD_VEHICULO_CLIMAPRO = VCS.COD_VEHICULO_CLIMAPRO
    WHERE
        VCS.COD_MARCA = '36V'
        AND RPV.FLAG_REGISTRO_INACTIVO = 0
        AND RPV.FLAG_UNIVERSO_CONTACTABLE = 1
    UNION ALL
    SELECT
        NVL(ORD.COD_CONDUCTOR, ORD.COD_PROPIETARIO) AS COD_PERSONA_CLIMAPRO_I
    FROM
        SLF_ILT_DIM_ORDERS ORD
    WHERE
        ORD.COD_MARCA = '36V'
        AND ORD.FLAG_REGISTRO_INACTIVO = 0
        AND ORD.FLAG_UNIVERSO_CONTACTABLE = 1
        AND (ORD.COD_PROPIETARIO IS NOT NULL
        OR ORD.COD_CONDUCTOR IS NOT NULL)
)
AND PRS.FLAG_ES_VW = 1
AND PRS.FLAG_CLIENTE_INACTIVO IS NULL;