public.vw_conciliacion_ns

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

serialnumber,productonom,cust_id,service_status,phone_number

text

2147483647

√

null

lastgpsdate

timestamptz

35,6

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH q1 AS (
         SELECT ltrim((d.serialnumber)::text, '0'::text) AS sfm_serial_number,
            ns.serialnumber AS ns_serial_number,
                CASE
                    WHEN (d.idmodeldevice = 7) THEN 'TT Guardian'::text
                    WHEN ((d.idmodeldevice = 4) AND (c.idprivilegegroup = ANY (ARRAY[4, 5, 6, 7, 8]))) THEN 'FS'::text
                    WHEN ((t.idproduct = 1) AND (d.idmodeldevice = 1)) THEN 'AEF'::text
                    WHEN (c.idclient = ANY (ARRAY[55, 159, 194, 81, 88, 61, 12, 92, 114])) THEN 'SH'::text
                    WHEN ((t.idproduct = 2) AND (d.idmodeldevice = 4)) THEN 'SH'::text
                    WHEN (((p.description)::text = 'sbs'::text) AND (t.idproduct = 3)) THEN 'ARMOR'::text
                    WHEN ((p.description)::text = 'sbs'::text) THEN 'SH'::text
                    WHEN ((d.idmodeldevice = 4) AND ((p.description)::text = 'aef'::text) AND ((c.clientcode)::text ~~ 'co%'::text)) THEN 'AEF'::text
                    WHEN (((p.description)::text ~~* '%fs%'::text) AND (d.idmodeldevice = 4)) THEN 'FS'::text
                    WHEN (d.idmodeldevice = 4) THEN 'FS'::text
                    WHEN ((p.description)::text = 'aef'::text) THEN 'AEF'::text
                    WHEN ((p.description)::text = 'armor'::text) THEN 'ARMOR'::text
                    ELSE NULL::text
                END AS sfm_productonom,
            (
                CASE
                    WHEN ((ns.productonom)::text ~~* 'FS%'::text) THEN 'FS'::character varying
                    ELSE ns.productonom
                END)::character varying(50) AS ns_productonom,
            c.erpid AS sfm_cust_id,
            (
                CASE ns.clientenum
                    WHEN '000000MXP'::text THEN '000000'::character varying
                    ELSE ns.clientenum
                END)::character varying(25) AS ns_cust_id,
            c.name AS sfm_cust_name,
            vc.nombre AS ns_cust_name,
                CASE
                    WHEN ((d.enabled = true) AND (d.status = 1)) THEN 'Active'::text
                    WHEN ((d.enabled IS NULL) AND (d.status IS NULL)) THEN NULL::text
                    ELSE 'Inactive'::text
                END AS sfm_service,
            ns.servicio AS ns_service,
            d.unit_name AS sfm_phone_no,
            ns.phonenumber AS ns_phone_no,
            v.serialnumber AS sfm_vehicle_id,
            ns.vehicleid AS ns_vehicle_id,
            d.lastgpsdate
           FROM ((((((device d
             FULL JOIN staging.vw_qcsntoday_cc ns ON (((ns.serialnumber)::text = ltrim((d.serialnumber)::text, '0'::text))))
             LEFT JOIN technicalaccount t ON ((t.idtechnicalaccount = d.idtechnicalaccount)))
             LEFT JOIN client c ON ((c.idclient = t.idclient)))
             LEFT JOIN staging.vw_cliente_cc vc ON (((vc.clientenum)::text = (ns.clientenum)::text)))
             LEFT JOIN vehicle v ON ((v.idunit = d.idunit)))
             LEFT JOIN privilegegroup p ON ((p.idprivilegegroup = c.idprivilegegroup)))
          WHERE ((length((d.serialnumber)::text) <= 12) OR (d.serialnumber IS NULL))
        ), q2 AS (
         SELECT q1.sfm_serial_number,
            q1.ns_serial_number,
            q1.sfm_productonom,
            q1.ns_productonom,
            q1.sfm_cust_id,
            q1.ns_cust_id,
            q1.sfm_cust_name,
            q1.ns_cust_name,
            q1.sfm_service,
            q1.ns_service,
            q1.sfm_phone_no,
            q1.ns_phone_no,
            q1.sfm_vehicle_id,
            q1.ns_vehicle_id,
            ((((
                CASE
                    WHEN ((q1.sfm_serial_number IS NOT NULL) AND (q1.ns_serial_number IS NULL)) THEN 'ns_missing_serialnumber,'::text
                    WHEN ((q1.sfm_serial_number IS NULL) AND (q1.ns_serial_number IS NOT NULL)) THEN 'sfm_missing_serialnumber,'::text
                    ELSE ','::text
                END ||
                CASE
                    WHEN (q1.sfm_productonom IS NULL) THEN 'sfm_missing_productonom,'::text
                    WHEN (q1.ns_productonom IS NULL) THEN 'ns_missing_productonom,'::text
                    WHEN (COALESCE(q1.sfm_productonom, ''::text) <> (COALESCE(q1.ns_productonom, ''::character varying))::text) THEN 'sfm_diff_productonom,'::text
                    ELSE ','::text
                END) ||
                CASE
                    WHEN ((q1.sfm_cust_id IS NULL) AND (q1.ns_cust_id IS NOT NULL)) THEN 'sfm_missing_cust_id,'::text
                    WHEN (((COALESCE(q1.sfm_cust_id, ''::character varying))::text <> (COALESCE(q1.ns_cust_id, ''::character varying))::text) AND (NOT (((q1.sfm_cust_id)::text = 'MX000000'::text) AND ((q1.ns_cust_id)::text = '000000'::text)))) THEN 'sfm_diff_cust_id,'::text
                    ELSE ','::text
                END) ||
                CASE
                    WHEN ((q1.sfm_service = 'Active'::text) AND ((q1.ns_service)::text = 'Inactive'::text)) THEN 'sfm_active_service,'::text
                    WHEN ((q1.sfm_service = 'Inactive'::text) AND ((q1.ns_service)::text = 'Active'::text)) THEN 'sfm_inactive_service,'::text
                    ELSE ','::text
                END) ||
                CASE
                    WHEN (((q1.sfm_phone_no IS NULL) OR (length(btrim((q1.sfm_phone_no)::text)) = 0)) AND (q1.ns_phone_no IS NOT NULL)) THEN 'sfm_missing_phone_number'::text
                    WHEN ((q1.sfm_phone_no IS NOT NULL) AND ((q1.ns_phone_no IS NULL) OR (length(btrim((q1.ns_phone_no)::text)) = 0))) THEN 'ns_missing_phone_number'::text
                    WHEN ((q1.sfm_phone_no)::text <> (q1.ns_phone_no)::text) THEN 'sfm_diff_phone_number'::text
                    ELSE ','::text
                END) AS reason,
            q1.lastgpsdate
           FROM q1
        )
 SELECT q2.sfm_serial_number,
    q2.ns_serial_number,
    q2.sfm_productonom,
    q2.ns_productonom,
    q2.sfm_cust_id,
    q2.ns_cust_id,
    q2.sfm_cust_name,
    q2.ns_cust_name,
    q2.sfm_service,
    q2.ns_service,
    q2.sfm_phone_no,
    q2.ns_phone_no,
    q2.sfm_vehicle_id,
    q2.ns_vehicle_id,
    q2.reason AS "serialnumber,productonom,cust_id,service_status,phone_number",
    q2.lastgpsdate
   FROM q2
  WHERE (length(btrim(q2.reason, ','::text)) > 0)
  ORDER BY q2.ns_cust_id, q2.ns_serial_number;

public.vw_conciliacion_ns

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views