15.5 Caso de Uso: Oportunidades atrasadas por vendedor (FUP)

Quando usar

• Cobrar follow-up do time: quem está com FUP atrasado aparece primeiro.
• Centralizar contexto: leia o último parecer (OCLG) direto no card.
• Filtrar por status, datas, território, grupo de PN e fonte.

SQL do Kanban (OV atrasadas por vendedor)

Este SQL agrupa por vendedor (LastSlp), mostra o último parecer da OV e filtra apenas OVs com Próx. contato < hoje.

WITH last_activity AS (
    SELECT 
        A."U_BaseEntry",
        MAX(C."ClgCode") AS "LastClgCode"
    FROM "@DWU_ATENDIMENTO" A
    JOIN "@DWU_ATEND_PARECER" P
      ON P."DocEntry" = A."DocEntry"
    JOIN "OCLG" C
      ON C."ClgCode" = P."U_NumAtividade"
    GROUP BY A."U_BaseEntry"
),
last_stage AS (
    SELECT 
        r1."OpprId",
        r1."Step_Id",
        r1."ObjType"  AS "LkBaseType",
        r1."DocId" AS "LkBaseEntry"
    FROM "OOPR" h
    JOIN "OPR1" r1
      ON r1."OpprId" = h."OpprId"
      AND r1."Line" = (SELECT MAX(r11."Line") FROM OPR1 r11 WHERE r11."OpprId" = h."OpprId")
),
doc_values AS (
    SELECT 23 AS "ObjType", q."DocEntry" AS "DocEntry", q."DocTotal" AS "DocValor" FROM "OQUT" q
    UNION ALL
    SELECT 17 AS "ObjType", r."DocEntry", r."DocTotal" FROM "ORDR" r
    UNION ALL
    SELECT 15 AS "ObjType", d."DocEntry", d."DocTotal" FROM "ODLN" d
    UNION ALL
    SELECT 13 AS "ObjType", i."DocEntry", i."DocTotal" FROM "OINV" i
)
SELECT 
    SUBSTRING(T2."SlpName", 1, 15) AS "Agrupador",
    '#E9E9E9' AS "CorAgrupador",

    T0."OpprId" AS "IdDoc",
    '97' AS "TipoObjeto",
    T0."OpprId" || ' - ' || T0."CardCode" || ' - ' || SUBSTRING(T1."CardName", 1, 30) AS "Titulo",
    T0."OpenDate" AS "Data1",
    T0."PredDate" AS "Data2",
    T5."U_DtProxContato" AS "Data3",
    (SELECT 
      TO_VARCHAR(T11."CntctDate", 'DD/MM/YYYY') || ' às ' ||
      SUBSTRING(LPAD(TO_VARCHAR("CntctTime"), 4, '0'), 1, 2) || ':' ||
      SUBSTRING(LPAD(TO_VARCHAR("CntctTime"), 4, '0'), 3, 2) ||
      ' - ' || T22."U_NAME" || ' disse: ' || CHAR(10) || CHAR(10) || T11."Notes"
     FROM OCLG T11 LEFT JOIN OUSR T22 ON T22."USERID" = T11."AttendUser"
     WHERE T11."ClgCode" = C."ClgCode") AS "Observacao",

    T2."SlpName" AS "Responsavel",
    IFNULL(T7."Descript", 'Alto') AS "Prioridade",
    CASE 
        WHEN IFNULL(T7."Descript", 'Alto') = 'Alto'  THEN '#FF6347'
        WHEN IFNULL(T7."Descript", 'Alto') = 'Médio' THEN '#1E90FF'
        WHEN IFNULL(T7."Descript", 'Alto') = 'Baixo' THEN '#7DD78C'
        ELSE '#FFF'
    END AS "CorPrioridade",

    SUBSTRING(T2."SlpName", 1, 24) AS "Classificacao",
    '#000' AS "CorClassificacao",

    T0."MaxSumLoc" AS "Valor1",
    T0."MaxSumLoc" * (T4."CloPrcnt"/100) AS "Valor2",
    IFNULL(dv."DocValor",0) AS "Valor3",
    CAST(T4."CloPrcnt" AS INT) AS "Progresso",

    CASE T0."Status"
        WHEN 'L' THEN 'Perdida'
        WHEN 'W' THEN 'Vencida'
        WHEN 'O' THEN 'Aberta'
        ELSE 'Desconhecido'
    END AS "CampoUsuario1",
    T0."Name" AS "CampoUsuario2",

    CASE 
        WHEN T0."Status" = 'L' THEN '#FF6347'
        WHEN T0."Status" = 'W' THEN '#7DD78C'
        WHEN T0."Status" = 'O' THEN '#1E90FF'
        ELSE '#FF6347'
    END AS "CorDoCard",
    CASE 
        WHEN T0."Status" = 'L' THEN '#F9D9D9'
        WHEN T0."Status" = 'W' THEN '#D7FDDE'
        WHEN T0."Status" = 'O' THEN '#DEEEFF'
        ELSE '#E9E9E9'
    END AS "CorDoCardTotal"

FROM "OOPR" T0
LEFT JOIN "OCRD" T1 ON T1."CardCode" = T0."CardCode"
LEFT JOIN "OSLP" T2 ON T2."SlpCode" = T0."LastSlp"
LEFT JOIN "OOST" T4 ON T0."StepLast" = T4."StepId"
LEFT JOIN "@DWU_ATENDIMENTO" T5 ON T5."U_BaseEntry" = TO_NVARCHAR(T0."OpprId") AND T5."U_BaseType" = '33' AND T5."U_Status" = 'O'
LEFT JOIN "OTER" T6 ON T6."territryID" = T1."Territory"
LEFT JOIN "OOIR" T7 ON T0."IntRate" = T7."Num"
LEFT JOIN last_activity LA ON LA."U_BaseEntry" = TO_NVARCHAR(T0."OpprId")
LEFT JOIN "OCLG" C ON C."ClgCode" = LA."LastClgCode"
LEFT JOIN last_stage LS ON LS."OpprId" = T0."OpprId"
LEFT JOIN doc_values dv ON dv."ObjType" = LS."LkBaseType" AND dv."DocEntry" = LS."LkBaseEntry"
WHERE 1=1
    AND T0."SlpCode" IN ({[Texto1CRMOne][Vendedor][SELECT O."SlpCode", O."SlpName" FROM OSLP O WHERE O."SlpCode" IN (#SlpCodePermissao#)][Checkbox][Obrigatorio]})
    AND T0."Status" IN ({[Texto1CRMOne][Status][SELECT 'O' AS "Code", 'Aberta' AS "Name" FROM DUMMY UNION ALL SELECT 'W', 'Vencida' FROM DUMMY UNION ALL SELECT 'L', 'Perdida' FROM DUMMY][Checkbox]})
    AND T0."PredDate" BETWEEN '{[Data1CRMOne][Prev. encerramento inicial]}' AND '{[Data2CRMOne][Prev. encerramento final]}'
    AND T0."OpenDate" BETWEEN '{[Data3CRMOne][Abertura inicial]}' AND '{[Data4CRMOne][Abertura final]}'
    AND (T0."CloseDate" BETWEEN '{[Data5CRMOne][Encerramento inicial]}' AND '{[Data6CRMOne][Encerramento final]}' OR T0."CloseDate" IS NULL)
    AND T5."U_CodTipoAtendimento" IN ({[Texto1CRMOne][Tipo][SELECT "Code","Name" FROM "@DWU_TIPO_ATEND" WHERE "U_CategAtend" = '2'][Checkbox]})
    AND T0."Industry" IN ({[Texto1CRMOne][Setor industrial][SELECT "IndCode","IndName" FROM "OOND"][Checkbox]})
    AND T0."Source" IN ({[Texto1CRMOne][Fonte da informação][SELECT CAST("Num" AS NVARCHAR), "Descript" FROM OOSR][Checkbox]})
    AND T0."CardCode" IN ({[Texto1CRMOne][Parceiro de Negócio][SELECT "CardCode" AS "Code", "CardName" AS "Name" FROM OCRD WHERE "CardType" IN ('L', 'C') AND "SlpCode" IN (#SlpCodePermissao#)][Checkbox]})
    AND T1."Territory" IN ({[Texto1CRMOne][Território][SELECT C."territryID" AS "Code", CASE WHEN P3."territryID" IS NOT NULL THEN P3."descript" || ' / ' || P2."descript" || ' / ' || P1."descript" || ' / ' || C."descript" WHEN P2."territryID" IS NOT NULL THEN P2."descript" || ' / ' || P1."descript" || ' / ' || C."descript" WHEN P1."territryID" IS NOT NULL THEN P1."descript" || ' / ' || C."descript" ELSE C."descript" END AS "Name", C."parent" AS "Parent" FROM "OTER" C LEFT JOIN "OTER" P1 ON P1."territryID" = C."parent" LEFT JOIN "OTER" P2 ON P2."territryID" = P1."parent" LEFT JOIN "OTER" P3 ON P3."territryID" = P2."parent" WHERE C."inactive" = 'N'][Checkbox]})
    AND T1."GroupCode" IN ({[Texto1CRMOne][Grupo de PN][SELECT "GroupCode", "GroupName" FROM OCRG WHERE "GroupType" = 'C' ORDER BY "GroupName"][Checkbox]})
    AND T0."StepLast" IN ({[Texto1CRMOne][Etapa OV][SELECT "StepId", "Descript" FROM OOST WHERE "Canceled" = 'N' AND "SalesStage" = 'Y' ORDER BY "StepId"][Checkbox]})
    AND T5."U_DtProxContato" < CURRENT_DATE
ORDER BY SUBSTRING(T2."SlpName", 1, 15), T5."U_DtProxContato";

Filtros (UI → SQL)

AND T0."SlpCode" IN ({[Texto1CRMOne][Vendedor]...})

AND T0."Status" IN ({[Texto1CRMOne][Status]...})

AND T0."PredDate" BETWEEN '{[Data1...]}' AND '{[Data2...]}'
AND T0."OpenDate" BETWEEN '{[Data3...]}' AND '{[Data4...]}'
AND (T0."CloseDate" BETWEEN '{[Data5...]}' AND '{[Data6...]}' OR T0."CloseDate" IS NULL)

AND T5."U_DtProxContato" BETWEEN '{[Data7...]}' AND '{[Data8...]}'

AND T5."U_CodTipoAtendimento" IN ({[Texto1CRMOne][Tipo]...})

AND T0."Industry" IN ({[Texto1CRMOne][Setor industrial]...})
AND T0."Source" IN ({[Texto1CRMOne][Fonte da informação]...})

AND T0."CardCode" IN ({[Texto1CRMOne][Parceiro de Negócio]...})
AND T1."Territory" IN ({[Texto1CRMOne][Território]...})
AND T1."GroupCode" IN ({[Texto1CRMOne][Grupo de PN]...})

AND T0."StepLast" IN ({[Texto1CRMOne][Etapa OV]...})