vw_conciliacion_ns_extended

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

sfm_serial_number

text

2147483647

√

null

ns_serial_number

varchar

√

null

sfm_productonom

text

2147483647

√

null

ns_productonom

varchar

√

null

sfm_cust_id

varchar

√

null

ns_cust_id

varchar

√

null

sfm_cust_name

varchar

256

√

null

ns_cust_name

varchar

2147483647

√

null

sfm_service

text

2147483647

√

null

ns_service

varchar

√

null

sfm_phone_no

varchar

√

null

ns_phone_no

varchar

√

null

sfm_vehicle_id

varchar

√

null

ns_vehicle_id

varchar

√

null

reason_serialnumber

text

2147483647

√

null

reason_productonom

text

2147483647

√

null

reason_custid

text

2147483647

√

null

reason_servicestatus

text

2147483647

√

null

reason_phonenum

text

2147483647

√

null

fecha_ultima_posicion

timestamptz

35,6

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH q AS (
         SELECT vw_conciliacion_ns.sfm_serial_number,
            vw_conciliacion_ns.ns_serial_number,
            vw_conciliacion_ns.sfm_productonom,
            vw_conciliacion_ns.ns_productonom,
            vw_conciliacion_ns.sfm_cust_id,
            vw_conciliacion_ns.ns_cust_id,
            vw_conciliacion_ns.sfm_cust_name,
            vw_conciliacion_ns.ns_cust_name,
            vw_conciliacion_ns.sfm_service,
            vw_conciliacion_ns.ns_service,
            vw_conciliacion_ns.sfm_phone_no,
            vw_conciliacion_ns.ns_phone_no,
            vw_conciliacion_ns.sfm_vehicle_id,
            vw_conciliacion_ns.ns_vehicle_id,
            ( SELECT a.a[1] AS reason_serialnumber
                   FROM string_to_array(vw_conciliacion_ns."serialnumber,productonom,cust_id,service_status,phone_number", ','::text) a(a)) AS reason_serialnumber,
            ( SELECT a.a[2] AS reason_productonom
                   FROM string_to_array(vw_conciliacion_ns."serialnumber,productonom,cust_id,service_status,phone_number", ','::text) a(a)) AS reason_productonom,
            ( SELECT a.a[3] AS reason_custid
                   FROM string_to_array(vw_conciliacion_ns."serialnumber,productonom,cust_id,service_status,phone_number", ','::text) a(a)) AS reason_custid,
            ( SELECT a.a[4] AS reason_servicestatus
                   FROM string_to_array(vw_conciliacion_ns."serialnumber,productonom,cust_id,service_status,phone_number", ','::text) a(a)) AS reason_servicestatus,
            ( SELECT a.a[5] AS reason_phonenum
                   FROM string_to_array(vw_conciliacion_ns."serialnumber,productonom,cust_id,service_status,phone_number", ','::text) a(a)) AS reason_phonenum,
            vw_conciliacion_ns.lastgpsdate
           FROM vw_conciliacion_ns
        )
 SELECT q.sfm_serial_number,
    q.ns_serial_number,
    q.sfm_productonom,
    q.ns_productonom,
    q.sfm_cust_id,
    q.ns_cust_id,
    q.sfm_cust_name,
    q.ns_cust_name,
    q.sfm_service,
    q.ns_service,
    q.sfm_phone_no,
    q.ns_phone_no,
    q.sfm_vehicle_id,
    q.ns_vehicle_id,
    q.reason_serialnumber,
    q.reason_productonom,
    q.reason_custid,
    q.reason_servicestatus,
    q.reason_phonenum,
    q.lastgpsdate AS fecha_ultima_posicion
   FROM q
  WHERE ((NOT ((COALESCE(q.sfm_service, ''::text) = 'Inactive'::text) AND (COALESCE(q.reason_serialnumber, ''::text) = 'ns_missing_serialnumber'::text))) AND (NOT ((COALESCE((q.sfm_cust_id)::text, ''::text) = 'MX000000'::text) AND (COALESCE((q.ns_cust_id)::text, ''::text) = '000000'::text))) AND (NOT ((length(COALESCE(q.reason_serialnumber, ''::text)) = 0) AND (length(COALESCE(q.reason_custid, ''::text)) = 0) AND (length(COALESCE(q.reason_phonenum, ''::text)) = 0) AND (length(COALESCE(q.reason_servicestatus, ''::text)) = 0) AND (COALESCE(q.reason_productonom, ''::text) = 'sfm_diff_productonom'::text) AND (COALESCE(q.sfm_productonom, ''::text) = 'SH'::text) AND (COALESCE((q.ns_productonom)::text, ''::text) = 'ARMOR'::text))) AND (NOT ((length(COALESCE(q.reason_serialnumber, ''::text)) = 0) AND (length(COALESCE(q.reason_custid, ''::text)) = 0) AND ((length(COALESCE(q.reason_servicestatus, ''::text)) > 0) OR (length(COALESCE(q.reason_productonom, ''::text)) > 0) OR (length(COALESCE(q.reason_phonenum, ''::text)) > 0)) AND (COALESCE((q.sfm_cust_id)::text, ''::text) = '000000'::text) AND (COALESCE((q.ns_cust_id)::text, ''::text) = '000000'::text))) AND (NOT ((COALESCE((q.sfm_cust_id)::text, ''::text) = 'CO000000'::text) AND (COALESCE((q.ns_cust_id)::text, ''::text) = '000000'::text))) AND (NOT (COALESCE(q.sfm_serial_number, ''::text) IN ( SELECT ovmrecon_testingdevices.serialnumber
           FROM staging.ovmrecon_testingdevices))) AND (NOT ((length(COALESCE(q.reason_serialnumber, ''::text)) = 0) AND (length(COALESCE(q.reason_custid, ''::text)) = 0) AND (length(COALESCE(q.reason_phonenum, ''::text)) = 0) AND (length(COALESCE(q.reason_servicestatus, ''::text)) = 0) AND (COALESCE(q.reason_productonom, ''::text) = 'sfm_diff_productonom'::text) AND (((COALESCE(q.sfm_productonom, ''::text) = 'FSSV2'::text) AND (COALESCE((q.ns_productonom)::text, ''::text) = 'FSSV3'::text)) OR ((COALESCE(q.sfm_productonom, ''::text) = 'FSS'::text) AND (COALESCE((q.ns_productonom)::text, ''::text) = 'FSSV3'::text))))) AND (NOT (((COALESCE(q.ns_serial_number, ''::character varying))::text ~~ '25%'::text) OR ((COALESCE(q.ns_serial_number, ''::character varying))::text ~~ 'MXQ%'::text))));

Possibly Referenced Tables/Views

vw_conciliacion_ns