CREATE MATERIALIZED VIEW SALESFORCE.CONCILIATION_TEST (
COD_PERSONA_CLIMAPRO_I,
LOPD_COMS_COMERCIALES,
FLAG_UNIVERSO_CONTACTABLE
)
TABLESPACE SALESFORCE_DAT
BUILD IMMEDIATE
USING NO INDEX
REFRESH FORCE ON COMMIT
WITH PRIMARY KEY
AS
	SELECT r.COD_PERSONA_CLIMAPRO_I, p.LOPD_COMS_COMERCIALES, 1 AS FLAG_ORIGEN
	FROM SLF_ILT_DIM_RELATIONS_P_V r, SLF_ILT_DIM_PERSONS p, SLF_ILT_DIM_VEHICLES v
	WHERE r.COD_VEHICULO_CLIMAPRO = v.COD_VEHICULO_CLIMAPRO(+)
    AND r.COD_PERSONA_CLIMAPRO_I = p.COD_PERSONA_CLIMAPRO_I
	AND v.COD_MARCA = '36V'
	AND r.FLAG_REGISTRO_INACTIVO = 0
	AND r.FLAG_UNIVERSO_CONTACTABLE = 1
    AND p.FLAG_ES_VW = 1
	UNION ALL
	SELECT pd.COD_CONDUCTOR, p.LOPD_COMS_COMERCIALES, 2 AS FLAG_ORIGEN
	FROM SLF_ILT_DIM_ORDERS pd, SLF_ILT_DIM_RELATIONS_P_V r, SLF_ILT_DIM_PERSONS p
	WHERE pd.cod_vehiculo_climapro = r.cod_vehiculo_climapro(+)
    AND r.cod_persona_climapro_i = p.cod_persona_climapro_i(+)
    AND  	pd.COD_MARCA = '36V'
    AND pd.FLAG_REGISTRO_INACTIVO = 0
    AND pd.FLAG_UNIVERSO_CONTACTABLE = 1
    AND p.FLAG_ES_VW = 1
	AND (pd.COD_PROPIETARIO IS NOT NULL OR pd.COD_CONDUCTOR IS NOT NULL);