Diagnóstico SQL Server - DWSGDASQL2

Resumen Ejecutivo SQL

RAM libre10.53 GB
RAM total60 GB
CPU promedio19.15 %
Conexiones SQL658
Disco promedio582.53 %
Bloqueos activos0
Bases de datos20
Transacciones abiertas30
Tareas en cola (Q)24

Servidor SQL: SRVCLSGDEA\SGDEAPRY,1633

Host: DWSGDASQL2

Fecha: 05/26/2026 10:01:18

Alertas

Recomendaciones

Información del sistema

CSNameCaptionVersionLastBootUpTimeTotalRAMGBFreeRAMGB
DWSGDASQL2Microsoft Windows Server 2019 Standard10.0.177635/24/2026 12:01:11 AM6010.53

CPU

NameNumberOfCoresNumberOfLogicalProcessorsMaxClockSpeed
Intel(R) Xeon(R) Platinum 8268 CPU @ 2.90GHz222900
Intel(R) Xeon(R) Platinum 8268 CPU @ 2.90GHz222900
Intel(R) Xeon(R) Platinum 8268 CPU @ 2.90GHz222900
Intel(R) Xeon(R) Platinum 8268 CPU @ 2.90GHz222900
Intel(R) Xeon(R) Platinum 8268 CPU @ 2.90GHz222900
Intel(R) Xeon(R) Platinum 8268 CPU @ 2.90GHz222900

Discos

DeviceIDVolumeNameSizeGBFreeGBFreePercent
C:179.4121.7267.85
D:TEMP299.9879.1426.38
E:DATA5222.38754.8514.45
F:LOG299.98276.1392.05
G:BACKUPS499.98321.9164.38
Q:New Volume19.9819.3696.91
S:Spool299.98298.3599.46

Contadores de rendimiento - muestra 1 minuto

CounterMinMaxAverage
\\dwsgdasql2\processor(_total)\% processor time7.6852.619.15
\\dwsgdasql2\memory\available mbytes106601082310796.83
\\dwsgdasql2\system\processor queue length020.33
\\dwsgdasql2\physicaldisk(_total)\% disk time121.141448.33582.53
\\dwsgdasql2\physicaldisk(_total)\current disk queue length519745.25

Información SQL Server

ServerNameEditionProductLevelProductVersionCollationStartTime
SRVCLSGDEA\SGDEAPRYEnterprise Edition: Core-based Licensing (64-bit)RTM15.0.4316.3SQL_Latin1_General_CP1_CI_AS5/26/2026 4:08:50 AM

Memoria SQL

PhysicalMemoryMBCommittedMBCommittedTargetMBPageLifeExpectancySecBufferCacheHitRatio
61439449994500097207930

Bases de datos

DatabaseNameStateRecoveryModelCompatibilityLevelSizeMBCreateDate
OpheliaSuiteONLINEFULL15044224181/25/2023 11:42:38 AM
tempdbONLINESIMPLE1502243785/26/2026 4:09:15 AM
DMSONLINEFULL150929973/9/2024 11:45:05 PM
DriveONLINEFULL1503466811/9/2023 7:00:50 PM
StageONLINEFULL150317015/8/2023 11:39:48 AM
DMS_bk_040923ONLINEFULL15050119/4/2023 11:54:28 AM
DMS_BK_20ONLINEFULL15021956/21/2023 3:24:03 PM
AgoraSSBONLINEFULL15054012/1/2023 10:18:55 PM
msdbONLINEFULL1502639/24/2019 2:21:42 PM
EstructuraImportacionONLINEFULL1501122/24/2024 11:16:01 AM
ImperiumReportCacheONLINEFULL1501122/28/2026 8:36:43 PM
AgoraSSB_OLDONLINEFULL150825/29/2024 3:42:38 PM
DMS_2ONLINEFULL150811/31/2023 8:44:52 AM
modelONLINEFULL150804/8/2003 9:13:36 AM
DBAONLINEFULL150809/29/2023 9:16:16 AM
DMSGDEAONLINEFULL150766/23/2023 10:34:45 AM
DWMaintenanceONLINEFULL150352/6/2023 4:32:54 PM
ProcessTableONLINEFULL150191/25/2023 11:41:51 AM
CalendarioONLINEFULL150111/25/2023 11:40:30 AM
masterONLINESIMPLE15064/8/2003 9:13:36 AM

Conexiones por aplicación

program_namehost_namelogin_nameconnections
MicroSQLDWSGDAAPP3ophelia131
MicroSQLDWSGDAAPP1ophelia97
ODKDWSGDAAPP3ophelia64
MicroSQLDWSGDAAPP3opheliadms63
ODKDWSGDAAPP1ophelia62
MicroSQLDWSGDAAPP2ophelia46
MicroSQLDWSGDAAPP1opheliadms36
ODKDWSGDAAPP2ophelia19
Core Microsoft SqlClient Data ProviderDWSGDAAPP3opheliadms19
Core Microsoft SqlClient Data ProviderDWSGDAAPP1opheliadms17
MicroSQLDWSGDAAPP4opheliadms15
DWSGDAAPP1ophelia10
DWSGDAAPP2ophelia7
DWSGDAAPP3ophelia7
Core Microsoft SqlClient Data ProviderDWSGDAAPP2opheliadms7
MicroSQLDWSGDAAPP2opheliadms6
.Net SqlClient Data ProviderFASECOLDAVMmonitoreosaas5
Core Microsoft SqlClient Data ProviderDWSGDAAPP1ophelia5
Core .Net SqlClient Data ProviderDWSGDAAPP1ophelia3
Core Microsoft SqlClient Data ProviderDWSGDAAPP3ophelia3
Core .Net SqlClient Data ProviderDWSGDAAPP3ophelia3
EFCore/10.0.5 (Microsoft Windows 10.0.17763 X64)DWSGDAAPP1ophelia3
EFCore/10.0.5 (Microsoft Windows 10.0.17763 X64)DWSGDAAPP3ophelia3
Core Microsoft SqlClient Data ProviderDWSGDAMON2opheliadms2
EFCore/10.0.3 (Microsoft Windows 10.0.17763 X64)DWSGDAAPP1ophelia2
EFCore/10.0.3 (Microsoft Windows 10.0.17763 X64)DWSGDAAPP2ophelia2
EFCore/10.0.3 (Microsoft Windows 10.0.17763 X64)DWSGDAAPP3ophelia2
Core Microsoft SqlClient Data ProviderDWSGDAAPP2ophelia2
Core .Net SqlClient Data ProviderDWSGDAAPP3Opheliasuitebi1
Core Microsoft SqlClient Data ProviderDWSGDAAPP4ophelia1
Core .Net SqlClient Data ProviderDWSGDAAPP1Opheliasuitebi1
Core .Net SqlClient Data ProviderDWSGDAAPP2ophelia1
.Net SqlClient Data ProviderDWSGDASQL2ophelia1
Microsoft SQL Server Management Studio - ConsultaDW-P10961ophelia1
Microsoft® Windows® Operating SystemDWSGDASQL2NT AUTHORITY\SYSTEM1
MicroSQLDWSGDAAPP11
ODKDWSGDAAPP31
PythonDWSGDASQL1Opheliasuitebi1
SQLAgent - Contained AGSRVCLSGDEADIGITALWARE\SCVSGDA-AGENT1
SQLAgent - Email LoggerSRVCLSGDEADIGITALWARE\SCVSGDA-AGENT1
SQLAgent - Generic RefresherSRVCLSGDEADIGITALWARE\SCVSGDA-AGENT1
SQLAgent - Job invocation engineSRVCLSGDEADIGITALWARE\SCVSGDA-AGENT1
SQLAgent - TSQL JobStep (Job 0xEE83636B927D094E85469C764C60119E : Step 1)SRVCLSGDEADIGITALWARE\SCVSGDA-AGENT1
SQLServerCEIPSRVCLSGDEANT SERVICE\SQLTELEMETRY$SGDEAPRY1
Zabbix agent 2 MSSQL pluginDWSGDASQL2zbx_monitor1

Sesiones activas

session_idlogin_namehost_nameprogram_namestatuscommandwait_typewait_timecpu_timelogical_readsDatabaseName
321opheliaDWSGDAAPP3Core .Net SqlClient Data ProviderrunningINSERTCXPACKET57362270171619DMS
971DWSGDAAPP3ODKrunningSELECTCXPACKET15331156180DMS
612OpheliasuitebiDWSGDASQL1PythonrunningSELECTCXCONSUMER7564952750DMS
589DIGITALWARE\SCVSGDA-AGENTSRVCLSGDEASQLAgent - TSQL JobStep (Job 0xEE83636B927D094E85469C764C60119E : Step 1)runningINSERTPAGEIOLATCH_SH1084455283890Stage
338opheliadmsDWSGDAAPP1Core Microsoft SqlClient Data ProviderrunningCONDITIONAL067140622DMS
587opheliaDWSGDASQL2.Net SqlClient Data ProviderrunningSELECT0522master
585DWSGDAAPP1MicroSQLrunningSELECTASYNC_NETWORK_IO27478AgoraSSB
56NT AUTHORITY\SYSTEMDWSGDASQL2Microsoft® Windows® Operating SystemrunningEXECUTESP_SERVER_DIAGNOSTICS_SLEEP192385master
1280opheliaDWSGDAAPP3EFCore/10.0.3 (Microsoft Windows 10.0.17763 X64)runningUPDATEPAGEIOLATCH_EX8074DMS
644opheliaDWSGDAAPP3runningUPDATEPAGEIOLATCH_EX86014OpheliaSuite
639opheliaDWSGDAAPP1runningSELECTPAGEIOLATCH_SH10804OpheliaSuite
636opheliaDWSGDAAPP3runningINSERTPAGEIOLATCH_EX116011OpheliaSuite
634opheliaDWSGDAAPP1runningINSERTPAGEIOLATCH_EX11509OpheliaSuite
630opheliaDWSGDAAPP1runningINSERTPAGEIOLATCH_EX8303OpheliaSuite
626opheliaDWSGDAAPP1runningINSERTPAGEIOLATCH_EX101019OpheliaSuite
616opheliaDWSGDAAPP3runningINSERTPAGEIOLATCH_EX100031OpheliaSuite

Bloqueos activos

No hay datos.

Transacciones abiertas (> 5 seg)

SessionIdHostAplicacionUsuarioInicioTransaccionSegundosAbiertaTipoTransaccionUltimaConsulta
403DWSGDAAPP2ophelia5/26/2026 8:48:01 AM4397Read/Write(@ SMALLINT,@_1 VARCHAR(36))SELECT CAS_DESC,USU_CODI,CAS_PRIO,CAS_FECI,CAS_HORI,CAS_FLIM,CAS_HLIM FROM WF_CASOS WHERE EMP_CODI= @ AND CAS_CONT= @_1
1353DWSGDAAPP2ophelia5/26/2026 8:48:02 AM4396Read/Write(@ SMALLINT,@_1 VARCHAR(36),@_2 VARCHAR(20))SELECT PTL_REGI FROM WF_FPLAN WHERE EMP_CODI= @ AND CAS_CONT= @_1 AND PTL_CODI= @_2
1358DWSGDAAPP2ophelia5/26/2026 8:48:02 AM4396Read/Write(@ SMALLINT,@_1 VARCHAR(36))SELECT CAS_DESC,USU_CODI,CAS_PRIO,CAS_FECI,CAS_HORI,CAS_FLIM,CAS_HLIM FROM WF_CASOS WHERE EMP_CODI= @ AND CAS_CONT= @_1
200DWSGDAAPP1MicroSQLophelia5/26/2026 9:19:16 AM2522Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
548DWSGDAAPP1MicroSQLophelia5/26/2026 9:22:59 AM2299Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
351DWSGDAAPP3MicroSQLophelia5/26/2026 9:23:17 AM2281Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
76DWSGDAAPP1MicroSQLophelia5/26/2026 9:27:08 AM2050Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
242DWSGDAAPP1MicroSQLophelia5/26/2026 9:28:25 AM1973Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
683DWSGDAAPP1MicroSQLophelia5/26/2026 9:28:34 AM1964Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
237DWSGDAAPP1MicroSQLophelia5/26/2026 9:31:07 AM1811Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
275DWSGDAAPP3MicroSQLophelia5/26/2026 9:31:46 AM1772Read/Write(@p0 varchar(32),@p1 varchar(16),@p2 varchar(9),@p3 varchar(16),@p4 varchar(16),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
681DWSGDAAPP1MicroSQLophelia5/26/2026 9:32:14 AM1744Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
459DWSGDAAPP3MicroSQLophelia5/26/2026 9:36:14 AM1504Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
98DWSGDAAPP2MicroSQLophelia5/26/2026 9:38:21 AM1377Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
377DWSGDAAPP3MicroSQLophelia5/26/2026 9:39:11 AM1327Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
382DWSGDAAPP3MicroSQLophelia5/26/2026 9:39:11 AM1327Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
487DWSGDAAPP1MicroSQLophelia5/26/2026 9:39:15 AM1323Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
300DWSGDAAPP3MicroSQLophelia5/26/2026 9:43:56 AM1042Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
102DWSGDAAPP2MicroSQLophelia5/26/2026 9:43:56 AM1042Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
491DWSGDAAPP2MicroSQLophelia5/26/2026 9:44:39 AM999Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
407DWSGDAAPP3MicroSQLophelia5/26/2026 9:50:19 AM659Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
139DWSGDAAPP3MicroSQLophelia5/26/2026 9:53:49 AM449Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
371DWSGDAAPP3MicroSQLophelia5/26/2026 9:58:32 AM166Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
725DWSGDAAPP1MicroSQLophelia5/26/2026 9:59:03 AM135Read/Write(@p0 varchar(32),@p1 varchar(36),@p2 varchar(9),@p3 varchar(36),@p4 varchar(36),@p5 decimal(2,1),@p6 varchar(8000),@p7 varchar(32),@p8 bit,@p9 datetime,@p10 varchar(8000))INSERT INTO [DRIVE_FOLDER] ([_id], [Code], [ParentCode], [Name], [Description], [Size], [Extensions], [BucketName], [IsPublic], [CreationDate], [MicroAppCode]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
612DWSGDASQL1PythonOpheliasuitebi5/26/2026 10:00:02 AM76Read/Write(@0 varchar(8000),@1 varchar(8000),@2 varchar(8000))select RequestFiles . FileNumber , convert ( VARCHAR , RequestFiles . FiledDate ) Z from RequestFiles left join RequestFileHistories on RequestFiles . Id = RequestFileHistories . RequestFileId and RequestFileHistories . CreationDate = ( select MAX ( CreationDate ) from dms . dbo . RequestFileHistories A where A . RequestFileId = RequestFileHistories . RequestFileId and A . Status not in ( @0 ) ) left join TypeDetail on TypeDetail . Id = RequestFileHistories . Status where RequestFileHistories . UserName = @1 and TypeDetail . Name < > @2
589SRVCLSGDEASQLAgent - TSQL JobStep (Job 0xEE83636B927D094E85469C764C60119E : Step 1)DIGITALWARE\SCVSGDA-AGENT5/26/2026 10:00:02 AM76Read/WriteCREATE PROCEDURE [dbo].[VentanillaUnica] AS /* SP REFACTORIZADO POR JUAN DIEGO GARZÓN 2/24/2025: MEJORAS: - Eliminación de MERGE , CTE y DELETE despues de la consulta principal - Reducción de tiempos con la utilización del CTE FilteredFileRequestHistories_CTE previo a la consulta principal 2 Minutos 25 Segundos => 30 Segundos - Reducción de codigo gracias a INTO y DROP TABLE - Escritura según las buenas prácticas en los alias de los campos - Modificación de CONVERT por CAST - Eliminación de valores NULL => 'Aun no se ha registrado numero de radicado' en el campo Radicado - Eliminación Radicados repetidos en la tabla final - */ BEGIN -- Verificar si la tabla existe IF OBJECT_ID('dbo.VentanillaUnicaFinal', 'U') IS NOT NULL BEGIN -- Si la tabla existe, eliminar los datos existentes TRUNCATE TABLE dbo.VentanillaUnicaFinal END ELSE BEGIN -- Si la tabla no existe, crearla CREATE TABLE [dbo].[VentanillaUnicaFinal]( [Id Tarea] [varchar](40) NULL, [Radicado] [varchar](100) NULL, [Fecha Radicacion] [date] NULL, [Hora Radicacion] [time](7) NULL, [Tipo de Documento] [varchar](500) NULL, [Tipificacion] [varchar](652) NULL, [Usuario Actual] [nvarchar](511) NULL, [Vicepresidencia] [varchar](500) NULL, [Dependencia Actual] [varchar](500) NULL, [Asunto] [varchar](max) NULL, [Medio de Recepcion] [varchar](2000) NULL, [Tipo Remitente] [varchar](2000) NULL, [Remitente] [varchar](801) NULL, [Dependencia Radicacion] [varchar](500) NULL, [Tipo Documento Remitente] [varchar](2000) NULL, [Documento Remitente] [varchar](20) NULL, [Direccion Remitente] [varchar](500) NULL, [Celular] [varchar](50) NULL, [Telefono] [varchar](50) NULL, [Tipo Radicado] [varchar](2000) NULL, [Ciudad] [varchar](500) NULL, [Departamento] [varchar](500) NULL, [Email] [varchar](500) NULL, [Estado Tarea] [varchar](17) NULL, [Fecha Vencimiento] [date] NULL, [Usuario Radicador] [varchar](201) NULL, [Dias Habiles de Respuesta] [int] NULL, [Proceso] [varchar](2000) NULL, [AñoFil] [int] NULL, [MesFil] [int] NULL, [ProcesoFil] [varchar](40) NULL, [DependenciaFil] [varchar](40) NULL, [RN] [int] NOT NULL ) END END; -- Insertar los datos en la tabla [VentanillaUnicaFinal] INSERT INTO VentanillaUnicaFinal ( [Id Tarea] ,Radicado ,[Fecha Radicacion] ,[Hora Radicacion] ,[Tipo de Documento] ,[Tipificacion] ,[Usuario Actual] ,[Vicepresidencia] ,[Dependencia Actual] ,[Asunto] ,[Medio de Recepcion] ,[Tipo Remitente] ,Remitente ,[Dependencia Radicacion] ,[Tipo Documento Remitente] ,[Documento Remitente] ,[Direccion Remitente] ,[Celular] ,[Telefono] ,[Tipo Radicado] ,[Ciudad] ,[Departamento] ,[Email] ,[Estado Tarea] ,[Fecha Vencimiento] ,[Usuario Radicador] ,[Dias Habiles de Respuesta] ,[Proceso] ,[AñoFil] ,[MesFil] ,[ProcesoFil] ,[DependenciaFil] ,[RN] ) SELECT * FROM ( SELECT WF_SEGUI_PEN.CAS_CONT AS [Id Tarea], RequestFiles.FileNumber AS [Radicado], CAST(RequestFileHistories.CreationDate AS DATE) AS [Fecha Radicacion], CAST(RequestFileHistories.CreationDate AS TIME) AS [Hora Radicacion], ISNULL(DocumentType.Name,'No Definido') [Tipo de Documento], CONCAT(NameType.Name,' ' ,ProcedureType.Name,' ' ,SpecificationType.Name ) AS [Tipificacion], CONCAT(Users_Stage.Name, ' ',Users_Stage.Surnames) AS [Usuario Actual], COALESCE( CASE WHEN DependenciesPrincipal2.Description LIKE 'VICEPRESIDENCIA%' THEN DependenciesPrincipal2.Description ELSE NULL END ,CASE WHEN DependenciesPrincipal1.Description LIKE 'VICEPRESIDENCIA%' THEN DependenciesPrincipal1.Description ELSE NULL END ,CASE WHEN DependenciesPrincipal.Description LIKE 'VICEPRESIDENCIA%' THEN DependenciesPrincipal.Description ELSE NULL END ,CASE WHEN Dependencies.name LIKE 'VICEPRESIDENCIA%' THEN Dependencies.name ELSE NULL END ) AS [Vicepresidencia], Dependencies.Name AS [Dependencia Actual], RequestFiles.Subject AS [Asunto], CANAL.Name AS [Medio de Recepcion], TipoRemitente.Name AS [Tipo Remitente], CASE WHEN TipoRemitente.Name IN ('Persona Natural', 'Apoderado / Representante Legal') THEN CASE WHEN Contacto.Names IS NOT NULL THEN CONCAT(Contacto.Names, Contacto.Surnames) WHEN Contacto.Names IS NULL AND Clients.NamesClients IS NOT NULL THEN CONCAT(Clients.NamesClients,' ',Clients.SurNames) ELSE CAST(ISNULL(Contacto.BusinessName,Clients.BusinessName) AS VARCHAR(160)) END WHEN TipoRemitente.Name = 'Persona Jurídica' THEN CASE WHEN Contacto.BusinessName IS NOT NULL THEN Contacto.BusinessName WHEN Contacto.BusinessName IS NULL AND Clients.BusinessName IS NOT NULL THEN Clients.BusinessName WHEN Contacto.BusinessName IS NULL AND Clients.BusinessName IS NULL THEN CONCAT(Contacto.Names, Contacto.Surnames) WHEN Contacto.BusinessName IS NULL AND Clients.BusinessName IS NULL AND Contacto.Names IS NULL THEN CONCAT(Clients.NamesClients,' ',Clients.SurNames) END ELSE CAST(ISNULL(Contacto.BusinessName,Clients.BusinessName) AS VARCHAR(160)) END [Remitente], Dependencies.Name AS [Dependencia Radicacion], TIPODOCUMENTOREMITENTE.Name AS [Tipo Documento Remitente], ISNULL(Contacto.NumberIdentification, Clients.NumberIdentification) AS [Documento Remitente], Clients.Address AS [Direccion Remitente], Clients.Mobile AS [Celular], Clients.Phone AS [Telefono],TIPORADICADO.Name AS [Tipo Radicado], CITY.Description [Ciudad], DEPARTMENT.Description AS [Departamento], Clients.Email AS [Email], CASE WHEN WF_SEGUI_PEN.SEG_FATI >= GETDATE() THEN 'Tareas a tiempo' WHEN WF_SEGUI_PEN.SEG_FLIM <= GETDATE() THEN 'Tareas vencidas' ELSE 'Tareas por vencer' END AS [Estado Tarea], CAST(RequestFiles.ExperationDate AS DATE) AS [Fecha Vencimiento], CONCAT(Users1.Name,' ', Users1.Surnames ) AS [Usuario Radicador], DMS_Procedures.ResponseTime AS [Dias Habiles de Respuesta], PROCESO.Name AS [Proceso], YEAR(RequestFiles.FiledDate) AS [AñoFil], MONTH(RequestFiles.FiledDate) AS [MesFil], ISNULL(ESTADO.Code,0) [ProcesoFil], ISNULL(Dependencies.Code,0) AS [DependenciaFil], ROW_NUMBER() OVER (PARTITION BY RequestFiles.FileNumber ORDER BY RequestFileHistories.CreationDate DESC) AS RN FROM OpheliaSuite.dbo.WF_SEGUI_PEN INNER JOIN OpheliaSuite.dbo.WF_SEGUI ON WF_SEGUI.CAS_CONT=WF_SEGUI_PEN.CAS_CONT AND WF_SEGUI.SEG_CONT=WF_SEGUI_PEN.SEG_CONT AND WF_SEGUI_PEN.SEG_SUBJ NOT LIKE '%VISUALIZAR INCONSISTENCIA%' LEFT JOIN DMS.DBO.RequestFileHistories ON RequestFileHistories.CaseId=WF_SEGUI_PEN.CAS_CONT AND RequestFileHistories.CreationDate = (SELECT MAX(CreationDate) FROM DMS.DBO.RequestFileHistories A WHERE A.CaseId=WF_SEGUI_PEN.CAS_CONT) LEFT JOIN DMS.DBO.RequestFiles ON RequestFiles.Id=RequestFileHistories.RequestFileId LEFT JOIN Users_Stage on Users_Stage.UserName = WF_SEGUI_PEN.SEG_UENC LEFT JOIN ( SELECT Dependencies.Id ,UserId ,MIN(Dependencies.Name) OVER (PARTITION BY UserId) Name ,MIN(CASE WHEN (Dependencies.Name) =Dependencies.Name THEN Dependencies.Code END) OVER (PARTITION BY UserId) Code ,MIN(CASE WHEN (Dependencies.Name) =Dependencies.Name THEN Dependencies.TopSection END) OVER (PARTITION BY UserId) TopSection ,UsersCompany.State FROM DMS.DBO.Users INNER JOIN DMS.DBO.UsersCompany ON Users.Id=UsersCompany.UserId INNER JOIN DMS.DBO.Dependencies ON Dependencies.Id=UsersCompany.DependenceId INNER JOIN DMS.DBO.TypeDetail ON UsersCompany.State=TypeDetail.Id AND TypeDetail.Code = (SELECT MIN(TypeDetail.Code) FROM DMS.DBO.UsersCompany A INNER JOIN DMS.DBO.TypeDetail ON A.State=TypeDetail.Id WHERE UsersCompany.UserId=A.UserId GROUP BY A.UserId ) ) Dependencies ON Users_Stage.Id = Dependencies.UserId LEFT JOIN dms.dbo.TypeDetail ESTADO ON CAST(ESTADO.Id AS VARCHAR(40)) = RequestFileHistories.Status LEFT JOIN dms.dbo.TypeDetail PROCESO ON CAST(PROCESO.Id AS VARCHAR(40)) = RequestFileHistories.Status AND PROCESO.Name != 'Digitalizado' LEFT JOIN DMS.DBO.DocumentType ON DocumentType.Id=RequestFiles.DocumentTypeId LEFT JOIN DMS.DBO.DMS_Procedures ON DMS_Procedures.Id=RequestFiles.ProcedureId LEFT JOIN DMS.DBO.PQRSDDetailRequest ProcedureType ON ProcedureType.Id=DMS_Procedures.ProcedureTypeId LEFT JOIN DMS.DBO.PQRSDTypeRequest NameType ON NameType.Id=DMS_Procedures.NameTypeId LEFT JOIN DMS.DBO.PQRSDRequestSpecification SpecificationType ON SpecificationType.Id=DMS_Procedures.SpecificationTypeId LEFT JOIN DMS.DBO.TypeDetail CANAL ON CANAL.Id=RequestFiles.ChannelId LEFT JOIN DMS.DBO.Clients ON RequestFiles.ClientId=Clients.Id LEFT JOIN DMS.DBO.Contacts Contacto ON RequestFiles.ContactId=Contacto.Id LEFT JOIN DMS.DBO.TypeDetail TipoRemitente ON TipoRemitente.Id= ISNULL(Contacto.TypeContactId,Clients.PersonTypeId) LEFT JOIN DMS.DBO.TypeDetail TIPODOCUMENTOREMITENTE ON Clients.DocumentTypeId=TIPODOCUMENTOREMITENTE.Id LEFT JOIN DMS.DBO.TypeDetail TIPORADICADO ON RequestFiles.RequestTypeId =TIPORADICADO.Id LEFT JOIN DMS.DBO.GeographicsLocation CITY ON Clients.CityId=CITY.Id LEFT JOIN DMS.DBO.GeographicsLocation DEPARTMENT ON Clients.DepartamentId=DEPARTMENT.Id LEFT JOIN DMS.DBO.Users Users1 ON Users1.UserName=RequestFileHistories.UserName LEFT JOIN DMS.DBO.Dependencies DependenciesPrincipal ON DependenciesPrincipal.Id=Dependencies.TopSection LEFT JOIN DMS.DBO.Dependencies DependenciesPrincipal1 ON DependenciesPrincipal1.Id=DependenciesPrincipal.TopSection LEFT JOIN DMS.DBO.Dependencies DependenciesPrincipal2 ON DependenciesPrincipal2.Id=DependenciesPrincipal1.TopSection ) AS C WHERE RN = 1
321DWSGDAAPP3Core .Net SqlClient Data Providerophelia5/26/2026 10:00:13 AM65Read/Write( @iCompanyId VARCHAR(50) = NULL, @iDependencyId VARCHAR(50) = NULL, @iSeriesId VARCHAR(50) = NULL, @iSubseriesId VARCHAR(50) = NULL, @iDocTypeId VARCHAR(50) = NULL, @iProcedureId VARCHAR(50) = NULL, @iInitialDate VARCHAR(20) = NULL, @iFinalDate VARCHAR(20) = NULL, @iText VARCHAR(50) = NULL, @iFileNumber VARCHAR(20) = NULL, @iOriginId VARCHAR(50) = NULL, @iRequestingUser VARCHAR(100) = NULL, @iDestinationUser VARCHAR(100) = NULL, @iManagementArchiveStay VARCHAR(10) = NULL, @iCoreArchiveStay VARCHAR(10) = NULL, @iDispositionTypeId VARCHAR(50) = NULL, @iStatusId VARCHAR(50) = NULL, @iTextIsCodeReference BIT = 0, @iVersionCCD VARCHAR(10) = NULL, @iVersionTRD VARCHAR(10) = NULL ) INSERT INTO ##TemReferences SELECT DISTINCT DR.Id , DR.Code , DR.CreationDate , DR.CloseDate , ISNULL(DR.NameReferencies, '') , C.Name , D.Name , S.Name , ISNULL(SS.Name, '') , ST.Id , ISNULL(ST.Name, '') , ST.Code , ISNULL(AB.Name, '') , ISNULL(RS.TotalFolios, DR.Folios) , S.ManagementArchiveStay , S.CoreArchiveStay , S.DispositionTypeId , SS.ManagementArchiveStay , SS.CoreArchiveStay , SS.DispositionTypeId , ISNULL(DR.CompanyId, D.CompaniesId) , D.Id , S.Id , SS.Id , RF.FileNumber , RF.OriginId , RF.SenderName , DR.Description , DR.Tomo , DR.Version , DR.VolumeCount , DR.DateVersion , DR.Location , D.Code , SS.Code , S.Code , DR.NoteScope , DR.PrimaryValue , Dr.SecundaryValue , SP.Id , SP.Name , DR.InitialDate , DR.EndDate , DR.size , box.Id , ISNULL(box.NumberBox,'') , AB.Id , DR.ParentId , CONCAT(DR.Code , ' - ', DR.NameReferencies) , DI.Id , CAST(MR.Value AS VARCHAR(MAX)) AS metadataValue , DR.VersionCCD , DR.VersionTRD , CONCAT(U.Name, ' ' , U.Surnames) AS creationUserName , U.UserName AS creationUser FROM DMS_References DR WITH(NOLOCK) LEFT JOIN ReferencesRequestFile RRF WITH(NOLOCK) ON (RRF.ReferencesId = DR.Id AND RRF.TypeDocumentId = ISNULL(@iDocTypeId, RRF.TypeDocumentId)) LEFT JOIN RequestFiles RF WITH(NOLOCK) ON (RF.Id = RRF.RequestFileId AND RF.ProcedureId = ISNULL(@iProcedureId, RF.ProcedureId) ) LEFT JOIN DMS_Indexes DI WITH(NOLOCK) ON DI.ReferenceRFId = RRF.Id INNER JOIN Dependencies D WITH(NOLOCK) ON D.Id = DR.DependencyId INNER JOIN Companies C WITH(NOLOCK) ON C.Id = DR.CompanyId INNER JOIN Series S WITH(NOLOCK) ON S.Id = DR.SeriesId LEFT JOIN Subseries SS WITH(NOLOCK) ON (SS.Id = DR.SubseriesId AND SS.Id = ISNULL(@iSubseriesId, SS.Id)) LEFT JOIN TypeDetail ST WITH(NOLOCK) ON ST.Id = DR.StateId LEFT JOIN TypeDetail AB WITH(NOLOCK) ON AB.Id = DR.AvailabilityId LEFT JOIN TypeDetail SP WITH(NOLOCK) ON CAST(SP.Id AS varchar(50)) = DR.Support LEFT JOIN Box box WITH(NOLOCK) ON box.Id = DR.BoxId LEFT JOIN MetadataReference MR WITH(NOLOCK) ON MR.ReferenceId = DR.Id LEFT JOIN Users U WITH(NOLOCK) ON DR.CreationUser = U.Id LEFT JOIN ( SELECT ReferenceId, MAX(Tomo) AS TotalTomo, SUM(CONVERT(INT,Folios)) AS TotalFolios FROM DMS_ReferencesSummary WITH (NOLOCK) GROUP BY ReferenceId ) RS ON RS.ReferenceId = DR.Id WHERE 1 = 1 AND DR.CompanyId = @iCompanyId AND DR.DependencyId = @iDependencyId
591DWSGDAAPP3ophelia5/26/2026 10:01:06 AM12Read/Write(@ SMALLINT,@_1 INT,@_2 SMALLINT)SELECT PSW_CONT,PSW_TVAL,PSW_VALO,PTL_CODI,PTL_VERS,DPT_CODI,DPT_CODG,PSW_TABL, PSW_CAMP,PSW_NCAM,PAR_CODE,PAR_TYPE,PAR_FORM,PAR_REQU,PAR_FATH FROM WF_PSWET WHERE PAR_TYPE='HEADER' AND EMP_CODI= @ AND FLU_CONT= @_1 AND ETA_CONT= @_2
616DWSGDAAPP3ophelia5/26/2026 10:01:12 AM6Read/Write(@ SMALLINT,@_1 VARCHAR(36),@_2 VARCHAR(36),@_3 VARCHAR(36),@_4 INT,@_5 INT,@_6 VARCHAR(44),@_7 VARCHAR(1),@_8 DATETIME2(7),@_9 DATETIME2(7),@_10 DATETIME2(7),@_11 DATETIME2(7),@_12 FLOAT,@_13 VARCHAR(1),@_14 VARCHAR(1),@_15 VARCHAR(15),@_16 VARCHAR(15),@_17 VARCHAR(MAX),@_18 VARCHAR(1),@_19 VARCHAR(MAX),@_20 VARCHAR(1),@_21 VARCHAR(1),@_22 VARCHAR(15),@_23 DATETIME2(7),@_24 VARCHAR(1),@_25 INT,@_26 DATETIME2(7),@_27 VARCHAR(13))INSERT INTO WF_SEGUI (EMP_CODI,CAS_CONT,SEG_CONT,SEG_CONA,FLU_CONT,ETA_CONT, SEG_SUBJ,SEG_PRIO,SEG_FREC,SEG_HREC,SEG_FLIM,SEG_HLIM,SEG_DIAE,SEG_ESTC, SEG_ABRE,SEG_UORI,SEG_UENC,SEG_COME,SEG_ESTE,SEG_ERRO,SEG_RECO,AUD_ESTA,AUD_USUA,AUD_UFAC,SEG_ALER,SEG_INTE,SEG_FATI,SEG_IPAD) VALUES ( @ , @_1 , @_2 , @_3 , @_4 , @_5 , @_6 , @_7 , @_8 , @_9 , @_10 , @_11 , @_12 , @_13 , @_14 , @_15 , @_16 , @_17 , @_18 , @_19 , @_20 , @_21 , @_22 , @_23 , @_24 , @_25 , @_26 , @_27 )
644DWSGDAAPP3ophelia5/26/2026 10:01:12 AM6Read/Write(@ SMALLINT,@_1 INT,@_2 SMALLINT)SELECT WF_RUTAS.RUT_CONT,WF_RUTAS.ETA_COND,WF_ETAPA.GAB_CONT, WF_RUTAS.RUT_PTMX,WF_RUTAS.RUT_PTMY,WF_RUTAS.ACC_CONT, WF_ETAPA.ETA_ACOR,WF_ETAPA.ETA_MCOR,WF_ETAPA.ETA_ASUN,WF_ACCIO.ACC_NOMB FROM WF_RUTAS INNER JOIN WF_ETAPA WITH (NOLOCK) ON WF_RUTAS.EMP_CODI=WF_ETAPA.EMP_CODI AND WF_RUTAS.FLU_CONT=WF_ETAPA.FLU_CONT AND WF_RUTAS.ETA_COND=WF_ETAPA.ETA_CONT LEFT JOIN WF_ACCIO ON WF_RUTAS.EMP_CODI=WF_ACCIO.EMP_CODI AND WF_RUTAS.FLU_CONT=WF_ACCIO.FLU_CONT AND WF_RUTAS.ETA_CONT=WF_ACCIO.ETA_CONT AND WF_RUTAS.ACC_CONT=WF_ACCIO.ACC_CONT WHERE WF_RUTAS.EMP_CODI= @ AND WF_RUTAS.FLU_CONT= @_1 AND WF_RUTAS.ETA_CONT= @_2

Wait Stats

wait_typewaiting_tasks_countwait_time_msAvgWaitMssignal_wait_time_ms
SOS_WORK_DISPATCHER34154953355564746982.002704212
CXPACKET1029495719731227319.003541058
CXCONSUMER188578601316286636.009918704
PAGEIOLATCH_SH24815555719746423.00447005
PAGEIOLATCH_EX6717101480754822.0052187
SOS_SCHEDULER_YIELD542084780305011.008011713
BACKUPBUFFER125459562686544.00166444
ASYNC_NETWORK_IO396670624606615.00241940
IO_COMPLETION394052455892211.0040211
BPSORT59766725914404.00402536
BACKUPIO33030124030427.0031856
LCK_M_X27222463628258.00236
ASYNC_IO_COMPLETION87220552025350.0035
LATCH_EX41172619418924.00275304
LCK_M_IS68123838618211.0025
LCK_M_SCH_M35120144634327.0021
HTDELETE38010101045126.0028225
WRITELOG859007275748.0074416
PARALLEL_REDO_WORKER_WAIT_WORK1072836958106.003124
PAGELATCH_SH7825785078830.0030504

Top Queries CPU

ExecutionsCPUTimeMsAvgCPUMsElapsedMsLogicalReadsQueryText
376354152545138215761350672033INSERT INTO dbo.DiasHabiles (Id, DiasHabiles) SELECT R.Id, (COUNT(D.Fecha) * CASE WHEN R.ExperationDate >= CAST(GETDATE() AS DATE) THEN 1 ELSE -1 END) - 1 AS DiasHabiles FROM dms.dbo.RequestFiles AS R LEFT JOIN ( SELECT ROW_NUMBER() OVER (PARTITION BY RequestFileId ORDER BY CreationDate DESC) AS MaxReg ,RequestFileId ,CreationDate ,DependencyId ,CaseId ,UserName ,Status FROM dms.dbo.RequestFileHistories WHERE Status NOT IN ('31B6159D-DE9D-4CBA-9508-4D9D4EE2FAF7','C143C3ED-F4F1-4524-AD59-80FF0F35CB9C' ,'9337A841-5E78-4C45-B1BE-9607B0833F5C','56D07A62-76F6-4AB3-A26F-E18C949CBA60' ,'59536473-5BE9-4D7D-9CD8-D3FCB7A8D652','9BD808F4-6E9F-4710-B789-19FE1CE8C55A' ,'4139c0b6-68ff-4e79-9796-36c04a9891c8','6a4c1604-0097-48e4-8c4c-ae1b735ed425' --estados de fraude ,'8d6acd5a-d128-45b0-b1a5-f9c0fef90708','EF7B7E43-9151-422A-9A2C-6E3B6C53BC85') ) AS RequestFileHistories ON RequestFileHistories.RequestFileId=R.Id AND RequestFileHistories.MaxReg = 1 AND RequestFileHistories.DependencyId IS NOT NULL LEFT JOIN #DiasHabiles AS D ON D.Fecha BETWEEN CASE WHEN R.ExperationDate >= CAST(GETDATE() AS DATE) THEN CAST(GETDATE() AS DATE) ELSE R.ExperationDate END AND CASE WHEN R.ExperationDate >= CAST(GETDATE() AS DATE) THEN R.ExperationDate ELSE CAST(GETDATE() AS DATE) END WHERE RequestFileHistories.Status NOT IN ('e6d67e4a-f545-4d62-b882-5a38a0fc35e2', '80878642-df5b-4a9c-b42b-3f8a3682fcb0') AND R.OriginId='2A1B3A5A-6FEC-4234-A24E-B87A1710ECE7' GROUP BY R.Id, R.ExperationDate
4852570756530087894839911267SELECT DISTINCT RF.IdRadicado, RF.Radicado, RF.Fecha, CN.[Name] AS 'Canal', RF.[Tipo de persona], TD.Name AS 'Tipo de identificación', CL.NumberIdentification AS 'Número de identificación', RF.Entidad, RF.Destinatario AS 'Nombres y apellidos remitente', CL.Id AS 'Id Cliente', CT.Id AS 'Id Contacto', RF.País, RF.Departamento, RF.Ciudad, RF.Dirección, RF.[Correo electrónico], RF.Asunto, RF.Folios, RFD.Attachments AS 'Número de anexos', RF.[Descripción de anexos], RF.Compañía, RF.Dependencia, CONCAT(DP.NameSolicitud, ' / ', LTRIM(RTRIM(DP.NameDetalle)), IIF(ISNULL(DP.NameEspecificacion, '0') = '0', NULL, ' / '), DP.NameEspecificacion) AS 'Trámite', RFD.UserName AS 'Usuario Radicador', RF.FuncionarioResponsable 'Responsable', 'https://tinyurl.com/5e6yz5u9' AS QR FROM GETDATABYRADICATE_VW RF INNER JOIN REQUESTFILE_VW RFD ON RFD.Id = RF.IdRadicado INNER JOIN CANAL_VW CN ON CN.Id = RFD.ChannelId INNER JOIN CLIENTS_VW CL ON CL.Id = RFD.ClientId INNER JOIN CONTACTS_VW CT ON CT.Id = RFD.ContactId INNER JOIN TypeDetail TD ON TD.Id = CL.DocumentTypeId INNER JOIN DMSProcedureNew_VW DP ON RFD.ProcedureId = DP.IdProcedure WHERE RF.Radicado = @Radicado
11508569150856980120893315297INSERT INTO Stage.dbo.RadicacionVentUnica SELECT RequestFiles.Id as [RequestFilesId], RequestFiles.FileNumber AS [Radicado], -- Número de radicación CAST(RequestFiles.FiledDate AS DATETIME) AS [Fecha y Hora Radicacion], CAST(RequestFiles.FiledDate AS DATE) AS [Fecha Radicacion], -- Fecha de radicación CAST(RequestFiles.FiledDate AS TIME(0)) AS [Hora Radicacion], -- Hora de radicación TIPORADICADO.Name AS [Tipo Radicado], -- Tipo de radicación -- Determinar el usuario actual IIF(Users.Name + Users.Surnames IS NULL, 'La información del usuario en el sistema ' + COALESCE(WF_SEGUI_PEN.SEG_UENC, RequestFileHistories.UserName, Users1.UserName) + ' no es correcta', CONCAT(Users.Name, ' ', Users.Surnames) ) AS [Usuario Actual], dep.Vicepresidencia AS [Vicepresidencia], -- Vicepresidencia dep.Dependencia AS [Dependencia Actual], -- Dependencia actual ESTADO.Name AS [PROCESO], -- Estado del proceso ISNULL(DocumentType.Name, 'No Definido') AS [Tipo de Documento], -- Tipo de documento -- Definir el medio de recepción CASE WHEN TIPORADICADO.Name = 'Comunicación Interna' THEN 'Correo electrónico' ELSE CANAL.Name END AS [Medio de Recepcion], --Determinar el tipo de remitente ISNULL(TYPEPERSON_VW.Name, TYPEPERSON_VW1.Name) AS [Tipo Remitente], --Determinar el remitente CASE WHEN TYPEPERSON_VW.Name = 'Anónimo' OR TYPEPERSON_VW1.Name = 'Anónimo' THEN 'Anónimo' WHEN TYPEPERSON_VW.Name IN ('Persona Natural', 'Apoderado / Representante Legal') --OR TYPEPERSON_VW1.Name IN ('Persona Natural', 'Apoderado / Representante Legal') --THEN IIF(CONCAT(Contacto.Names, ' ', Contacto.Surnames) IS NULL, CONCAT(Clients.NamesClients, ' ', Clients.SurNames), CONCAT(Clients1.NamesClients, ' ', Clients1.SurNames)) --113839 Aranda 12-09-2025 donde se evidencia error en remitente por lo cual se realiza validación que priorice el dato de contacto THEN COALESCE(IIF (Contacto.Names IS NOT NULL OR Contacto.SurNames IS NOT NULL, CONCAT(Contacto.Names, ' ', Contacto.SurNames),NULL), IIF(Clients.NamesClients IS NOT NULL OR Clients.SurNames IS NOT NULL, CONCAT(Clients.NamesClients, ' ', Clients.SurNames),NULL), IIF(Clients1.NamesClients IS NOT NULL OR Clients1.SurNames IS NOT NULL, CONCAT(Clients1.NamesClients, ' ', Clients1.SurNames),NULL) ) ELSE CASE WHEN Contacto.BusinessName IS NOT NULL THEN Contacto.BusinessName WHEN Clients.BusinessName IS NOT NULL THEN Clients.BusinessName WHEN Clients1.BusinessName IS NOT NULL THEN Clients1.BusinessName ELSE IIF(CONCAT(Contacto.Names, ' ', Contacto.Surnames) IS NULL, CONCAT(Clients.NamesClients, ' ', Clients.SurNames), CONCAT(Clients1.NamesClients, ' ', Clients1.SurNames)) END END AS [Remitente], TIPODOCUMENTOREMITENTE.Name AS [Tipo Documento Remitente], -- Tipo de documento del remitente ISNULL(Contacto.NumberIdentification, Clients1.NumberIdentification) AS [Documento Remitente], -- Número de identificación del remitente ISNULL(Contacto.Address, Clients.Address) AS [Direccion Remitente], -- Dirección del remitente ISNULL(Contacto.Mobile, Clients.Mobile) AS [Celular], -- Celular del remitente ISNULL(Contacto.Telephone, Clients.Phone) AS [Telefono], -- Teléfono del remitente CITY.Description AS [Ciudad], -- Ciudad del remitente DEPARTMENT.Description AS [Departamento], -- Departamento del remitente ISNULL(Contacto.Email, Clients1.Email) AS [Email], -- Email del remitente -- Información sobre la radicación CONCAT(Users1.Name, ' ', Users1.Surnames) AS [Usuario Radicador], -- Usuario que radicó Dependencies1.Name AS [Dependencia Radicacion], -- Dependencia donde se radicó CAST(RequestFiles.ExperationDate AS DATE) AS [Fecha Vencimiento], -- Fecha de vencimiento CAST(RequestFiles.ExperationDate AS Time(0)) AS [Hora Vencimiento], -- Hora de vencimiento ORIGEN.Name AS [Tipo Comunicacion], -- Tipo de comunicación DMS_Procedures.ResponseTime AS [Dias Habiles de Respuesta], -- Días hábiles para respuesta -- Documentos adjuntos RequestFiles.Pages AS [Folios], -- Cantidad de folios RequestFiles.Attachments AS [Anexos], -- Cantidad de anexos -- Tipificación del procedimiento CONCAT(NameType.Name, ' ', ProcedureType.Name, ' ', SpecificationType.Name) AS [Tipificacion], -- Información del asunto RequestFiles.Subject AS [Asunto], -- Asunto del radicado -- Estado del radicado COALESCE( CASE WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL AND CONVERT(DATE,RequestFilesRespuestaDefinitiva.FiledDate) <=CONVERT(DATE,RequestFiles.ExperationDate)--22/10/2024 Se cambia campo RequestFilesExpirationDate.ExpirationDateFinal THEN 'En Tiempo'--'TRAMITADO OPORTUNAMENTE' WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL AND CONVERT(DATE,RequestFilesRespuestaDefinitiva.FiledDate)>CONVERT(DATE,RequestFiles.ExperationDate) THEN 'Vencido'--'TRAMITADO EXTEMPORALMENTE' END ,CASE WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NULL AND CONVERT(DATE,RequestFiles.ExperationDate) < GETDATE()-1 THEN 'Vencido' WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NULL AND DATEDIFF(DAY,GETDATE(),CONVERT(DATE,RequestFiles.ExperationDate)) IN (0,1,2,3) THEN 'Proximo a Vencer' WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NULL AND DATEDIFF(DAY,GETDATE(),CONVERT(DATE,RequestFiles.ExperationDate)) >3 THEN 'En Tiempo' END ,CASE WHEN ESTADO.Name NOT IN ('Finalizado','Envío electrónico','Comunicación pendiente por clasificar','Comunicación Clasificada','Pendiente en la dependencia','Finalizado por Solicitud del Usuario') AND TIPORADICADO.Name='Salida' THEN 'Elaboración' END )[Estado Radicado], --COALESCE( -- -- Si existe fecha de radicación, evaluamos si fue en tiempo o vencido -- CASE -- WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL -- AND CAST(RequestFilesRespuestaDefinitiva.FiledDate AS DATE) <= CAST(RequestFiles.ExperationDate AS DATE) -- THEN 'En Tiempo' -- Tramitado oportunamente -- WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL -- AND CAST(RequestFilesRespuestaDefinitiva.FiledDate AS DATE) > CAST(RequestFiles.ExperationDate AS DATE) -- THEN 'Vencido' -- Tramitado extemporáneamente -- END, -- -- Si no existe fecha de radicación, evaluamos su estado según la fecha de expiración -- CASE -- WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NULL AND CAST(RequestFiles.ExperationDate AS DATE) < DATEADD(DAY, -1, GETDATE()) -- THEN 'Vencido' -- La expiración ya pasó -- WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NULL AND RequestFiles.ExperationDate - GETDATE() BETWEEN 0 AND 3 -- THEN 'Próximo a Vencer' -- Expira en los próximos 3 días -- WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NULL AND RequestFiles.ExperationDate - GETDATE() > 3 -- THEN 'En Tiempo' -- Todavía en plazo -- END, -- -- Si el estado no es final y es un radicado de salida, se considera en "Elaboración" -- CASE -- WHEN ESTADO.Name NOT IN ( -- 'Finalizado', 'Envío electrónico', 'Comunicación pendiente por clasificar', -- 'Comunicación Clasificada', 'Pendiente en la dependencia', 'Finalizado por Solicitud del Usuario' -- ) -- AND TIPORADICADO.Name = 'Salida' -- THEN 'Elaboración' -- END --) AS [Estado Radicado], -- Información adicional Users1.UserName AS UsuarioFiltro, CAST(MAX(RequestFilesRespuestaParcial.FileNumber) OVER(PARTITION BY RequestFiles.FileNumber) AS VARCHAR(30)) AS [Respuesta Parcial], CAST(MAX(RequestFilesRespuestaParcial.FiledDate) OVER(PARTITION BY RequestFiles.FiledDate) AS DATE) AS [Fecha Respuesta Parcial], -- Validaciones de respuestas finales CASE WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL AND RequestFiles.RequestTypeId = '5449808C-16FF-4BDE-98C7-4C04C76B221B' THEN CAST(MAX(RequestFilesRespuestaDefinitiva.FileNumber) OVER (PARTITION BY RequestFiles.FileNumber) AS VARCHAR(30)) ELSE NULL END AS [Respuesta Final], CASE WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL AND RequestFiles.RequestTypeId = '5449808C-16FF-4BDE-98C7-4C04C76B221B' THEN CAST(MAX(RequestFilesRespuestaDefinitiva.FiledDate) OVER (PARTITION BY RequestFiles.FiledDate) AS DATE) ELSE NULL END AS [Fecha Respuesta Final], -- Información sobre finalización CASE WHEN ESTADO.Name IN('Finalizado', 'Finalizado por Solicitud del Usuario') THEN CAST(RequestFileHistories.CreationDate AS DATE) ELSE NULL END AS [Fecha Finalizado], CASE WHEN ESTADO.Name IN('Finalizado','Finalizado por Solicitud del Usuario') THEN CAST(RequestFileHistories.CreationDate AS Time(0)) ELSE NULL END [Hora Finalizado], CASE WHEN ESTADO.Name IN('Finalizado', 'Finalizado por Solicitud del Usuario') THEN RequestFileHistories.Reason ELSE NULL END AS [Observación Finalizado], RequestFilesRespuestaDefinitiva.ChannelId AS Canal_Respuesta_Final, RequestFilesRespuestaParcial.ChannelId AS Canal_Respuesta_Parcial, Users.Id AS USERID -- Identificador del usuario FROM dms.dbo.RequestFiles LEFT JOIN dms.dbo.RequestFileHistories ON RequestFileHistories.RequestFileId = RequestFiles.Id AND EXISTS (SELECT 1 FROM [Stage].[dbo].[RequestFileHistories_Stage] WHERE RequestFileHistories_Stage.RequestFileHistoriesId = RequestFileHistories.Id AND RequestFileHistories_Stage.RequestPosition = 1) LEFT JOIN dms.dbo.RequestFileHistories RequestFileHistories1 ON RequestFileHistories1.RequestFileId = RequestFiles.Id AND EXISTS (SELECT 1 FROM [Stage].[dbo].[RequestFileHistories_Stage] WHERE RequestFileHistories_Stage.RequestFileHistoriesId = RequestFileHistories1.Id AND RequestFileHistories_Stage.RequestPosition = 0) LEFT JOIN [Stage].[dbo].[Users_Stage] Users1 ON Users1.UserName = RequestFileHistories1.UserName --ok LEFT JOIN OpheliaSuite.dbo.WF_SEGUI_PEN ON WF_SEGUI_PEN.CAS_CONT = RequestFileHistories.CaseId --ok AND WF_SEGUI_PEN.SEG_SUBJ NOT LIKE '%VISUALIZAR INCONSISTENCIA%' LEFT JOIN [Stage].[dbo].[Users_Stage] Users ON Users.UserName = COALESCE(WF_SEGUI_PEN.SEG_UENC,RequestFileHistories.UserName,Users1.UserName) --ok --LEFT JOIN [Stage].[dbo].[Depentencias_Vicepresidencia] Dep ON RequestFileHistories.DependencyId = Dep.id --ok LEFT JOIN (SELECT Dependencies.Id, Dependencies.Name AS Dependencia, CASE WHEN Dependencies.Name in ('DIRECCIÓN SARLAFT', 'UNIDAD DE CONTROL INTERNO DISCIPLINARIO', 'AUDITORIA CORPORATIVA','GERENCIA DE RIESGOS') THEN Dependencies.Name WHEN Dependencies.Name = 'PRESIDENCIA' THEN 'PRESIDENCIA' WHEN N1.Name = 'PRESIDENCIA' THEN Dependencies.Name WHEN N1.Name like '%VICEPRESIDENCIA %' THEN N1.Name WHEN N2.Name like '%VICEPRESIDENCIA %' THEN N2.Name WHEN N3.Name like '%VICEPRESIDENCIA %' THEN N3.Name ELSE '' END AS Vicepresidencia FROM [DMS].[dbo].[Dependencies] LEFT JOIN dms.dbo.Dependencies N1 ON Dependencies.TopSection = N1.Id LEFT JOIN dms.dbo.Dependencies N2 ON N1.TopSection = N2.Id LEFT JOIN dms.dbo.Dependencies N3 ON N2.TopSection = N3.Id where Dependencies.State = '57DC632C-79D5-458A-845B-76F4859F3E75' ) Dep ON COALESCE(RequestFileHistories.DependencyId, RequestFileHistories1.DependencyId) = Dep.id LEFT JOIN ( SELECT Users.UserName, Dependencies.Name, ROW_NUMBER() OVER (PARTITION BY Users.UserName ORDER BY Dependencies.Name ASC) AS Rn FROM [Stage].[dbo].[Users_Stage] Users INNER JOIN DMS.DBO.UsersCompany ON Users.Id=UsersCompany.UserId INNER JOIN DMS.DBO.Dependencies ON Dependencies.Id=UsersCompany.DependenceId INNER JOIN DMS.DBO.TypeDetail ON UsersCompany.State=TypeDetail.Id AND TypeDetail.Code = (SELECT MIN(TypeDetail.Code) FROM DMS.DBO.UsersCompany A INNER JOIN DMS.DBO.TypeDetail ON A.State=TypeDetail.Id WHERE UsersCompany.UserId = A.UserId GROUP BY A.UserId)) Dependencies1 ON RequestFileHistories1.UserName = Dependencies1.UserName --ok AND Dependencies1.Rn = '1' LEFT JOIN STAGE.DBO.RequestFilesExpirationDate ON RequestFilesExpirationDate.FileNumber=RequestFiles.FileNumber --OK LEFT JOIN DMS.DBO.TYPEORIGIN_VW ORIGEN ON RequestFiles.OriginId =ORIGEN.Id LEFT JOIN DMS.DBO.TYPEORIGIN_VW TIPORADICADO ON RequestFiles.RequestTypeId =TIPORADICADO.Id LEFT JOIN dms.dbo.TypeDetail ESTADO ON CAST(ESTADO.Id AS VARCHAR(40))= COALESCE(RequestFileHistories.status, RequestFileHistories1.status) --OK LEFT JOIN DMS.DBO.DocumentType ON DocumentType.Id=RequestFiles.DocumentTypeId LEFT JOIN DMS.DBO.DMS_Procedures ON DMS_Procedures.Id=RequestFiles.ProcedureId --OK LEFT JOIN DMS.DBO.PQRSDTypeRequest NameType ON NameType.Id=DMS_Procedures.NameTypeId --OK LEFT JOIN DMS.DBO.PQRSDDetailRequest ProcedureType ON ProcedureType.Id=DMS_Procedures.ProcedureTypeId --OK LEFT JOIN DMS.DBO.PQRSDRequestSpecification SpecificationType ON SpecificationType.Id=DMS_Procedures.SpecificationTypeId --OK LEFT JOIN DMS.DBO.CANAL_VW CANAL ON CANAL.Id=RequestFiles.ChannelId LEFT JOIN DMS.DBO.Contacts Contacto ON Contacto.Id = RequestFiles.ContactId --OK LEFT JOIN DMS.DBO.Clients ON RequestFiles.ClientId=Clients.Id --OK LEFT JOIN DMS.DBO.Clients Clients1 ON Clients1.Id=Contacto.ClientId --OK LEFT JOIN DMS.DBO.TYPEPERSON_VW ON TYPEPERSON_VW.Id=Contacto.TypeContactId --OK LEFT JOIN DMS.DBO.TYPEPERSON_VW TYPEPERSON_VW1 ON TYPEPERSON_VW1.Id=Clients1.PersonTypeId --OK LEFT JOIN DMS.DBO.TYPEIDENTI_VW TIPODOCUMENTOREMITENTE ON Clients1.DocumentTypeId=TIPODOCUMENTOREMITENTE.Id --OK LEFT JOIN DMS.DBO.GeographicsLocationMun_VW CITY ON Contacto.CityId=CITY.Id --OK LEFT JOIN DMS.DBO.GeographicsLocatioDep_VW DEPARTMENT ON Contacto.DepartamentId = DEPARTMENT.Id --OK LEFT JOIN (SELECT CC.Id, AA.FiledDate, AA.FileNumber, AA.ChannelId, ROW_NUMBER() OVER (PARTITION BY BB.ParentId ORDER BY AA.FiledDate DESC) AS RN FROM dms.dbo.RequestFiles AA INNER JOIN dms.dbo.RelatedRequestFiles BB ON BB.RequestFileId=AA.Id INNER JOIN dms.dbo.RequestFiles CC ON BB.ParentId=CC.Id WHERE AA.RequestTypeId='956FE4FE-E0C0-4F50-B742-DB431F9F536B' AND AA.ResposnseText=1 ) RequestFilesRespuestaDefinitiva ON RequestFiles.Id = RequestFilesRespuestaDefinitiva.Id AND RequestFilesRespuestaDefinitiva.RN = '1' LEFT JOIN (SELECT CC.Id, AA.FiledDate, AA.FileNumber, AA.ChannelId, ROW_NUMBER() OVER (PARTITION BY BB.ParentId ORDER BY AA.FiledDate DESC) AS RN FROM dms.dbo.RequestFiles AA INNER JOIN dms.dbo.RelatedRequestFiles BB ON BB.RequestFileId=AA.Id INNER JOIN dms.dbo.RequestFiles CC ON BB.ParentId=CC.Id WHERE AA.RequestTypeId='956FE4FE-E0C0-4F50-B742-DB431F9F536B' AND AA.ResposnseText=2 ) RequestFilesRespuestaParcial ON RequestFiles.Id = RequestFilesRespuestaParcial.Id AND RequestFilesRespuestaParcial.RN = '1' WHERE RequestFileHistories1.Status != 'e6d67e4a-f545-4d62-b882-5a38a0fc35e2' AND RequestFileHistories.Status != 'e6d67e4a-f545-4d62-b882-5a38a0fc35e2' AND (RequestFileHistories.ProcessCode != 'Combinación de Correspondencia - ' AND RequestFileHistories.ProcessName != 'Respuesta Parcial')
11007393100739323937919755908SELECT FileNumber --,MAX(F.FechaTermino)ExpirationDate ,MAX(ISNULL(F1.FechaTermino,[FechaRadicacion]))ExpirationDateInitial --,CASE WHEN ExperationDate >= [FechaRadicacion] THEN ExperationDate ELSE MAX(ISNULL(F1.FechaTermino,[FechaRadicacion]))END ExpirationDateInitial --Se realiza ajuste a campo de acuerdo a validación con Julio INTO FECHAINICIALVENCIMIENTOTEMP FROM ( SELECT DISTINCT RequestFiles.FileNumber ,MIN(RequestFiles.FiledDate) [FechaRadicacion] ,MAX(CASE WHEN RequestFiles1.ResposnseText=2 THEN RequestFiles1.FiledDate END ) [FechaRespuestaParcialMaxima] ,MAX(CASE WHEN RequestFiles1.ResposnseText=1 THEN RequestFiles1.FiledDate END ) [FechaRespuestaFinalMaxima] ,MAX(DMS_Procedures.ResponseTime) ResponseTime --,RequestFiles.ExperationDate --,MAX(F1.FechaTermino) [ExpirationDateInitial] --INTO #FECHAINICIALVENCIMIENTO FROM DMS.dbo.RequestFiles LEFT JOIN DMS.dbo.DMS_Procedures ON DMS_Procedures.Id=RequestFiles.ProcedureId LEFT JOIN DMS.dbo.RequestFileHistories ON RequestFileHistories.RequestFileId=RequestFiles.Id AND RequestFileHistories.CreationDate=(SELECT MAX(CreationDate) FROM DMS.dbo.RequestFileHistories A WHERE A.RequestFileId=RequestFileHistories.RequestFileId) LEFT JOIN DMS.dbo.Dependencies ON Dependencies.Id=RequestFileHistories.DependencyId LEFT JOIN dms.dbo.RelatedRequestFiles ON RelatedRequestFiles.ParentId =RequestFiles.Id LEFT JOIN dms.dbo.RequestFiles RequestFiles1 ON RelatedRequestFiles.requestfileId =CONVERT(VARCHAR(40),RequestFiles1.Id) --WHERE RequestFiles.OriginId='2A1B3A5A-6FEC-4234-A24E-B87A1710ECE7' --WHERE RequestFileHistories.CreationDate >= DATEADD(MONTH, -6, GETDATE()) --AND RequestFiles.OriginId='2A1B3A5A-6FEC-4234-A24E-B87A1710ECE7' --AND RequestFiles.FileNumber ='20230321376732' WHERE RequestFileHistories.Status <>'E6D67E4A-F545-4D62-B882-5A38A0FC35E2' --AND RequestFileHistories.CreationDate >= DATEADD(MONTH, -6, GETDATE()) --AND RequestFiles.FileNumber IN ('20240323449482','20241073468712','20241013458352') --AND RequestFiles.FileNumber IN ('20241014144082') --AND YEAR(RequestFiles.FiledDate) = 2024 --AND MONTH(RequestFiles.FiledDate) = 10 --AND DAY(RequestFiles.FiledDate) = 30 --AND RequestFiles.FiledDate <> '2024-10-29' --AND RequestFiles.FileNumber <> 0 GROUP BY RequestFiles.FileNumber --,RequestFiles.ExperationDate ,RequestFiles.FiledDate )Vencimiento --CROSS APPLY DBO.FechaTerminoSinDiasInhabiles (CONVERT(date,[FechaRespuestaParcialMaxima]+1),15) F CROSS APPLY DBO.FechaTerminoSinDiasInhabiles (CONVERT(DATE,[FechaRadicacion]+1),ResponseTime) F1 GROUP BY FileNumber
10999578691353349673714923SELECT FORMAT(RF.FiledDate, 'dd-MM-yyyy HH:mm') AS 'Fecha radicado' , RF.FileNumber AS 'Radicado' , RF.[Subject] AS 'Asunto' FROM [dbo].[REQUESTFILE_VW] RF INNER JOIN [dbo].[CLIENTS_VW] C ON C.Id = RF.ClientId WHERE C.NumberIdentification = @NIdentification ORDER BY Radicado DESC
1824260824260179003840648SELECT DISTINCT RF.Id AS id , RF.FileNumber AS fileNumber , RF.FiledDate AS filedDate , RF.Subject AS subject , DP.Id AS procedureId , REPLACE(ISNULL(CONCAT(NameType.name,'',' / ',ISNULL(ProcedureType.NameDetalle,''),' / ',ISNULL(Specification.NameEspecificacion,UPPER(DP.name))),''), ' / / ','') AS 'procedure' , Cl.Id AS clientId --, ISNULL(COALESCE(CLUpdate.BusinessName, CL.BusinessName), CONCAT(COALESCE(CLUpdate.NamesClients, CL.NamesClients),' ',ISNULL(COALESCE(CLUpdate.Surnames, CL.Surnames),''))) AS client , ISNULL(CT.BusinessName,CONCAT(ISNULL(CT.Names,''),' ',ISNULL(CT.Surnames,''),'')) AS client     , CT.Id AS contactId , ISNULL(COALESCE(CLUpdate.NumberIdentification, CL.NumberIdentification), '') AS numberIdentificatioClient , CT.NumberIdentification AS numberIdentificatioContact , CASE WHEN CT.Id IS NULL THEN '' WHEN CT.Surnames IS NULL THEN CT.Names ELSE CONCAT(CT.Names, '', CT.Surnames) END AS contact , C.Id AS companyId , ISNULL(C.Name, '') AS company , D.Id AS dependencyId , ISNULL(COALESCE(CollaboratorDependency.Name,D.Name), '') AS dependency , S.Id AS serieId , ISNULL(S.Name, '') AS serie , SS.Id AS subSerieId , ISNULL(SS.Name, '') AS subSerie , DT.Id AS documentTypeId , ISNULL(DT.Name, '') AS documentType , '' AS referenceName , TDS.Id AS stateId , ISNULL(TDS.Name, '') AS state , TDO.Id AS originId , ISNULL(TDO.Name, '') AS origin , ISNULL(CONCAT(U.Name,'',ISNULL(U.Surnames,'')),'') AS responsible , ISNULL(RF.ExperationDate,'') AS experationDate , RF.CaseId AS caseId FROM RequestFiles RF WITH(NOLOCK) LEFT JOIN DocumentType DT WITH(NOLOCK) ON DT.Id = RF.DocumentTypeId LEFT JOIN TypeDetail TDO WITH(NOLOCK) ON TDO.Id = RF.OriginId LEFT JOIN Clients CL WITH(NOLOCK) ON CL.Id = RF.ClientId LEFT JOIN ClientsUpdate CLUpdate WITH(NOLOCK) ON CLUpdate.Id = RF.ClientId LEFT JOIN Dependencies D WITH(NOLOCK) ON D.Id = RF.DependencyId LEFT JOIN Dependencies CollaboratorDependency WITH(NOLOCK) ON CollaboratorDependency.Id = RF.CollaboratorDependencyId LEFT JOIN CompanyStructures CS WITH(NOLOCK) ON CS.DependenciesId = D.Id LEFT JOIN Companies C WITH(NOLOCK) ON C.Id = CS.CompanyId LEFT JOIN Series S WITH(NOLOCK) ON S.Id = RF.SeriesId LEFT JOIN Subseries SS WITH(NOLOCK) ON SS.Id = RF.SubseriesId LEFT JOIN Users U WITH(NOLOCK) ON U.UserName = RF.ReceiverName LEFT JOIN DMS_Procedures DP WITH(NOLOCK) ON DP.Id = RF.ProcedureId LEFT JOIN TypeDetail TDS WITH(NOLOCK) ON TDS.Id = RF.StatusId LEFT JOIN Contacts CT WITH(NOLOCK) ON CT.Id = RF.ContactId LEFT JOIN REQUESTS_VW NameType ON NameType.IdSolicitud = DP.NameTypeId LEFT JOIN DRDETAILREQUEST_VW ProcedureType ON ProcedureType.IdDetalle = DP.ProcedureTypeId LEFT JOIN SPECIFICATIONTYPE_VW Specification ON Specification.IdEspecificacion = DP.SpecificationTypeId ORDER BY RF.FiledDate OFFSET (@iPageNum -1 ) * @iPageSize ROWS FETCH NEXT @iPageSize ROWS ONLY
5540770682133071634630946955SET @DiferenciaDias = ( SELECT COUNT(DISTINCT D.DIA_NOTR) FROM OpheliaSuite.dbo.GN_DIASN D INNER JOIN OpheliaSuite.dbo.GN_CCALE C ON D.CCA_CONT = C.CCA_CONT AND C.CCA_NOMB = 'CALENDARIO SGDEA' WHERE CONVERT(DATE, D.DIA_NOTR) BETWEEN @FechaInicial AND @FechaFinal )
238864278926993500425548740SELECT TOP(1) [r].[Id], [r].[DependencyId], [r].[FileNumber], [r].[ResponseDate], [r].[StatusId], [r].[VersionCCD], [r].[VersionTRD] FROM [RequestFiles] AS [r] WHERE [r].[FileNumber] = @request_FileNumber
16090976090972836686130236165SELECT * FROM dbo.V_RPTG_Radicados WHERE Radicado <> '0'
69006004008777595861616421SELECT TOP(@__p_2) [r].[Id], [r].[CaseCode], [r].[CompanyCode], [r].[CompletedAt], [r].[CreatedAt], [r].[DependencyCode], [r].[ErrorMessage], [r].[FileNumber], [r].[JobId], [r].[LastErrorAt], [r].[MaxRetries], [r].[NextRetryAt], [r].[ProcessCode], [r].[ProcessName], [r].[ProcessingServer], [r].[Reason], [r].[ReassignBPM], [r].[ReassignDMS], [r].[RetryCount], [r].[StartedAt], [r].[Status], [r].[StatusId], [r].[TrackingCode], [r].[UserExecutor], [r].[UserToReassign] FROM [ReassignmentTask] AS [r] WHERE [r].[ProcessingServer] = @__serverIp_0 AND ([r].[Status] = N'Pending' OR ([r].[Status] = N'Failed' AND [r].[RetryCount] < [r].[MaxRetries] AND [r].[NextRetryAt] IS NOT NULL AND [r].[NextRetryAt] <= @__now_1)) ORDER BY [r].[CreatedAt]
149176449176462098244469500INSERT INTO dbo.SmartSupervisionMom2 SELECT RequestFiles.Id AS RequestFilesId ,RequestFiles.FileNumber AS [RADICADO FIDUGESTOR] -- Número de radicado ,CAST(CAST(RequestFiles.FiledDate AS DATE) AS VARCHAR) AS [FECHA DE RADICACION] -- Fecha de radicación ,FORMAT(RequestFiles.FiledDate, 'h:mm tt') AS [HORA_RADICACION] -- Hora de radicación en formato AM/PM ,CONCAT(DATENAME(MONTH, RequestFiles.FiledDate),' - ',YEAR(RequestFiles.FiledDate)) AS [MES/AÑO] -- Mes y año en español --Tipo de PQR extraído del motivo de reclasificación o tomado por defecto ,COALESCE( SUBSTRING( RequestFileHistoriesReclas.Reason, CHARINDEX('Se reclasificó el tipo de PQRSD así: de', RequestFileHistoriesReclas.Reason) + LEN('Se reclasificó el tipo de PQRSD así: de'), CHARINDEX(' a ', RequestFileHistoriesReclas.Reason) - CHARINDEX('Se reclasificó el tipo de PQRSD así: de', RequestFileHistoriesReclas.Reason) - LEN('Se reclasificó el tipo de PQRSD así: de') ), PqrsType.Name ) AS [TIPO_DE_PQR] --Clasificaciones, canal, motivos, tipo y detalle de solicitud ,producto.Name AS [CLASIFICACION SFC PRODUCTO] ,canalpqr.Name AS [CANAL] ,Motivo.Name AS [MACROMOTIVO] ,NameType.Name AS [TIPO DE SOLICITUD] ,ProcedureType.Name AS [DETALLE DE LA SOLICITUD] ,SpecificationType.Name AS [ESPECIFICACIÓN DE LA SOLICITUD] -- Información relacionada con la transmisión a la SFC ,CONCAT(512,RequestFiles.FileNumber) AS [RADICADO SFC FIDUGESTOR] ,CAST(CAST(RequestFiles.FiledDate AS DATE) AS VARCHAR) AS [FECHA DEL ENVIO A SFC] ,'Recibida' AS [ESTADO SFC MOMENTO 2] ,CASE WHEN smartprocesslog.Status IN ('FINALIZADO','EXITOSO') THEN 'Enviada' ELSE 'No Enviada' END AS [ESTADO DE LA TRASMISION] ,CASE WHEN smartprocesslog.Status IN ('FINALIZADO','EXITOSO') THEN CAST(CAST(smartprocesslog.RegistrationDate AS DATE) AS VARCHAR) END AS [FECHA DE TRASMISION] ,CASE WHEN smartprocesslog.Status IN ('FINALIZADO','EXITOSO') THEN 'N/A' ELSE CAST(smartprocesslog.Observations AS NVARCHAR(MAX)) END AS [TIPO DE ERROR] -- Información de funcionarios y dependencias que gestionan y responden ,CONCAT(Users1.Name,' ', Users1.Surnames ) [FUNCIONARIO QUE GESTIONA] ,Dependencies1.Name [DEPENDENCIA QUE GESTIONA] ,CAST(CAST(RequestFileHistories1.CreationDate AS DATE) AS VARCHAR) AS [FECHA DE LA GESTION] ,CASE WHEN CONCAT(UsersFinalizador.Name,' ', UsersFinalizador.Surnames ) <>'' THEN CONCAT(UsersFinalizador.Name,' ', UsersFinalizador.Surnames ) ELSE CONCAT(Users.Name,' ', Users.Surnames ) END AS [FUNCIONARIO QUE RESPONDE] ,Dependencies.Name AS [DEPENDENCIA QUE RESPONDE] -- Información sobre respuestas parciales o definitivas ,CASE WHEN MAX(RequestFilesRespuestaParcial.FileNumber) OVER(PARTITION BY RequestFiles.FileNumber) IS NOT NULL AND MAX(RequestFilesRespuestaDefinitiva.FiledDate) OVER(PARTITION BY RequestFiles.FileNumber) IS NULL THEN 'Respuesta Parcial' WHEN MAX(RequestFilesRespuestaDefinitiva.FileNumber) OVER(PARTITION BY RequestFiles.FileNumber) IS NOT NULL THEN 'Respuesta Definitiva' END AS [TIPO DE RESPUESTA] ,MAX(ISNULL(RequestFilesRespuestaDefinitiva.FileNumber,RequestFilesRespuestaParcial.FileNumber)) OVER(PARTITION BY RequestFiles.FileNumber) AS [RADICADO DE RESPUESTA (MOMENTO 3)] ,MAX(CONVERT(VARCHAR,CONVERT(DATE,ISNULL(RequestFilesRespuestaDefinitiva.FiledDate, RequestFilesRespuestaParcial.FiledDate)))) OVER(PARTITION BY RequestFiles.FileNumber) AS [FECHA DE RESPUESTA] ,MAX(RIGHT(CONVERT(DATETIME, ISNULL(RequestFilesRespuestaDefinitiva.FiledDate,RequestFilesRespuestaParcial.FiledDate), 108),8)) OVER(PARTITION BY RequestFiles.FileNumber) AS [HORA DE RESPUESTA] -- Estado de envío al momento 3 ,CASE WHEN MIN(smartprocesslogMom3.Status) OVER (PARTITION BY smartprocesslogMom3.FileNumber,smartprocesslogMom3.ClientDocumentNumber)='EXITOSO' THEN 'Enviada' ELSE 'No Enviada' END AS [SE ENVIO MOMENTO 3] ,CASE WHEN MIN(smartprocesslogMom3.Status) OVER (PARTITION BY smartprocesslogMom3.FileNumber,smartprocesslogMom3.ClientDocumentNumber)='EXITOSO' THEN 'N/A' WHEN smartprocesslogMom3.RegistrationDate IS NULL THEN 'No ha sido Enviada' ELSE CAST(smartprocesslogMom3.Observations AS VARCHAR(8000)) END AS [TIPO DE ERROR MOMENTO 3] ,CAST(CAST(smartprocesslogMom3.RegistrationDate AS DATE) AS VARCHAR) AS [FECHA DE TRASMISION MOMENTO 3] -- Estado final de la solicitud ,CASE WHEN MAX(RequestFilesRespuestaDefinitiva.FiledDate) OVER(PARTITION BY RequestFiles.FileNumber) IS NOT NULL AND MIN(smartprocesslogMom3.Status) OVER (PARTITION BY smartprocesslogMom3.FileNumber,smartprocesslogMom3.ClientDocumentNumber)='EXITOSO' THEN 'Cerrado' WHEN smartprocesslog.Id IS NOT NULL AND MIN(smartprocesslogMom3.Status) OVER (PARTITION BY smartprocesslogMom3.FileNumber,smartprocesslogMom3.ClientDocumentNumber)='EXITOSO' THEN 'Recibida' ELSE 'Abierto' END AS [ESTADO ACTUAL MOMENTO 3] -- Información adicional de reclasificación y seguimiento ,CASE WHEN RequestFileHistoriesReclas.Id IS NOT NULL THEN 'Si' ELSE 'No' END AS [EL RADICADO TUVO RECLASIFICACION] ,RequestFileHistoriesReclas.Reason AS [TIPO DE PQRS ANTES DE RECLASIFICAR] ,PqrsType.Name AS [TIPO DE PQRS DESPUES DE RECLASIFICAR] ,Admision.Name AS [ADMISION] ,SmartAddicionalRequestFiles.ComingFromProcedure AS [PROCEDENTE] ,Favorabilidad.Name AS [FAVORABILIDAD] ,SmartAddicionalRequestFiles.FavorConsumerProcedure AS [A FAVOR DE] ,CASE WHEN SmartAddicionalRequestFiles.RefusedEntityProcedure='1' THEN 'Si' ELSE 'No' END AS [INADMITIDA O RECHAZADA POR LA ENTIDAD] ,SmartAddicionalRequestFiles.SuperFRemittedProcedure AS [TRASLADO A LA SUPERINTENDENCIA] ,AFavorDe.Name AS [ACEPTACION] ,Rectificacion.Name AS [RECTIFICACION] ,Desistimiento.Name AS [DESISTIMIENTO] ,clients.NumberIdentification AS [REMITENTE] ,CASE WHEN Clients.AffiliatedFomag='1' THEN 'Si' ELSE 'No' END AS [AFILIADO AL FOMAG] ,AffiliateType.Code AS [TIPO DE AFILIADO] ,RequestFiles.Subject AS [ASUNTO] ,CASE WHEN Clients.OriginRegistry='SmartSupervision' AND RequestFiles.ReportedSmart='1' THEN 'Si' WHEN Clients.OriginRegistry NOT IN ('SmartSupervision') THEN 'No' END AS [ACTUALIZO MOMENTO 4] ,CASE WHEN RequestFiles.ReportedSmart='1' THEN CONVERT(VARCHAR,CONVERT(DATE,Clients.ModificationDate)) END AS [FECHA ACTUALIZACION] -- Campos auxiliares para filtrado por año y mes ,CAST(YEAR(RequestFiles.FiledDate) AS int) AS AñoFil ,MONTH(RequestFiles.FiledDate) AS MesFil ,ISNULL(Dependencies.code,0) AS DependeciaFil -- Add campos Circular 19 ,Circular19.[TIPO DE FRAUDE] AS [TIPO DE FRAUDE] ,Circular19.[MODALIDAD DE FRAUDE] AS [MODALIDAD DE FRAUDE] ,Circular19.[MONTO RECLAMADO] AS [MONTO RECLAMADO] ,Circular19.[MONTO RECONOCIDO] AS [MONTO RECONOCIDO] -- Tabla principal de radicados FROM DMS.DBO.RequestFiles -- Unión para identificar el último usuario que gestionó el radicado LEFT JOIN ( SELECT RequestFileHistories.UserName, RequestFileHistories.RequestFileId, RequestFileHistories.Status, RequestFileHistories.processcode, ROW_NUMBER() OVER (PARTITION BY RequestFileId ORDER BY CreationDate DESC) AS rn FROM DMS.DBO.RequestFileHistories WHERE Status NOT IN (--Se agregan los siguientes estados por caso SAC 960781 '4139c0b6-68ff-4e79-9796-36c04a9891c8','6a4c1604-0097-48e4-8c4c-ae1b735ed425' ,'8d6acd5a-d128-45b0-b1a5-f9c0fef90708','EF7B7E43-9151-422A-9A2C-6E3B6C53BC85') AND ProcessCode !='615' ) RequestFileHistories ON RequestFileHistories.RequestFileId = RequestFiles.Id AND RequestFileHistories.rn = 1 -- Unión con la información del cliente LEFT JOIN DMS.DBO.clients ON clients.Id = RequestFiles.clientid -- Unión para obtener el último registro del Momento 2 con subproceso REPORTE_QUEJA LEFT JOIN ( SELECT smartprocesslog.Id, smartprocesslog.Status, smartprocesslog.FileNumber, smartprocesslog.RegistrationDate, smartprocesslog.Observations, smartprocesslog.ClientDocumentNumber, ROW_NUMBER() OVER (PARTITION BY FileNumber, ClientDocumentNumber ORDER BY RegistrationDate DESC) AS rn FROM DMS.DBO.smartprocesslog WHERE Process = 'MOMENTO_2' AND SubProcess = 'REPORTE_QUEJA' --AND smartprocesslog.FileNumber = '20241011326782' ) smartprocesslog ON smartprocesslog.FileNumber = RequestFiles.FileNumber AND smartprocesslog.ClientDocumentNumber = Clients.NumberIdentification AND smartprocesslog.rn = 1 -- Unión para obtener el último registro del Momento 3 con subproceso REPORTE_QUEJA LEFT JOIN ( SELECT smartprocesslog.Id, smartprocesslog.FileNumber, smartprocesslog.ClientDocumentNumber, smartprocesslog.RegistrationDate, smartprocesslog.Status, smartprocesslog.Observations, ROW_NUMBER() OVER ( PARTITION BY FileNumber ORDER BY -- Prioriza los EXITOSO más recientes, luego cualquier otro estado CASE WHEN Status = 'EXITOSO' THEN 1 ELSE 2 END, RegistrationDate DESC ) AS rn FROM DMS.DBO.smartprocesslog WHERE Process = 'MOMENTO_3' AND SubProcess = 'REPORTE_QUEJA' AND Status IN ('EXITOSO', 'FINALIZADO', 'FALLIDO') ) smartprocesslogMom3 ON smartprocesslogMom3.FileNumber = RequestFiles.FileNumber --AND smartprocesslogMom3.Id = ( -- SELECT TOP 1 AA.Id -- FROM DMS.DBO.smartprocesslog AA -- WHERE AA.FileNumber = smartprocesslogMom3.FileNumber -- AND AA.Process = 'MOMENTO_3' -- AND AA.SubProcess = 'REPORTE_QUEJA' -- ORDER BY AA.RegistrationDate DESC --) AND smartprocesslogMom3.rn = 1 -- Historial más antiguo con dependencia asignada LEFT JOIN ( SELECT RequestFileHistories.RequestFileId, RequestFileHistories.CreationDate, RequestFileHistories.UserName, RequestFileHistories.ProcessCode, ROW_NUMBER() OVER (PARTITION BY RequestFileId ORDER BY CreationDate ASC) AS rn FROM DMS.DBO.RequestFileHistories WHERE DependencyId IS NOT NULL AND ProcessCode !='615' --AND RequestFileId ='FCF1BB64-614E-4D69-9E4A-D8BED126A1DC' ) RequestFileHistories1 ON RequestFileHistories1.RequestFileId = RequestFiles.Id AND RequestFileHistories1.rn = 1 -- Último historial con estado 'Finalizado' LEFT JOIN ( SELECT AAA.RequestFileId, AAA.CreationDate, AAA.UserName, ROW_NUMBER() OVER (PARTITION BY AAA.RequestFileId ORDER BY AAA.CreationDate DESC) AS rn FROM DMS.DBO.RequestFileHistories AAA INNER JOIN DMS.DBO.TYPESTATEREQUEST_VW BBB ON CONVERT(VARCHAR(40), AAA.Status) = CONVERT(VARCHAR(40), BBB.Id) WHERE BBB.Name = 'Finalizado' ) RequestFileHistoriesUsuarioFinalizador ON RequestFileHistoriesUsuarioFinalizador.RequestFileId = RequestFiles.Id AND RequestFileHistoriesUsuarioFinalizador.rn = 1 -- Usuario que finalizó el radicado LEFT JOIN DMS.DBO.Users UsersFinalizador ON UsersFinalizador.UserName = RequestFileHistoriesUsuarioFinalizador.UserName -- Historial de reclasificación LEFT JOIN (SELECT RequestFileHistories.Id, RequestFileHistories.RequestFileId, RequestFileHistories.Reason, ROW_NUMBER() OVER (PARTITION BY RequestFileId ORDER BY CreationDate DESC) AS rn FROM DMS.DBO.RequestFileHistories WHERE RequestFileHistories.Status = '31b6159d-de9d-4cba-9508-4d9d4ee2faf7' ) AS RequestFileHistoriesReclas ON RequestFileHistoriesReclas.RequestFileId = RequestFiles.Id AND RequestFileHistoriesReclas.rn = 1 -- Usuario que respondió LEFT JOIN DMS.DBO.Users Users ON Users.UserName = RequestFileHistories.UserName -- Usuario asociado al historial más antiguo con dependencia LEFT JOIN DMS.DBO.Users Users1 ON Users1.UserName = RequestFileHistories1.UserName -- Información adicional del radicado (producto, canal, motivo, etc.) LEFT JOIN DMS.DBO.SmartAddicionalRequestFiles ON SmartAddicionalRequestFiles.RequestFilesId = RequestFiles.Id LEFT JOIN DMS.DBO.TypeDetail producto ON producto.Id = SmartAddicionalRequestFiles.ProductCode LEFT JOIN DMS.DBO.TypeDetail canalpqr ON canalpqr.Id = SmartAddicionalRequestFiles.Channel LEFT JOIN DMS.DBO.TypeDetail Motivo ON Motivo.Id = SmartAddicionalRequestFiles.MacroReasonCode LEFT JOIN DMS.DBO.TypeDetail Admision ON Admision.Id = SmartAddicionalRequestFiles.Admission LEFT JOIN DMS.DBO.TypeDetail Favorabilidad ON Favorabilidad.Id = SmartAddicionalRequestFiles.Favorability LEFT JOIN DMS.DBO.TypeDetail AFavorDe ON AFavorDe.Id = SmartAddicionalRequestFiles.Acceptance LEFT JOIN DMS.DBO.TypeDetail Rectificacion ON Rectificacion.Id = SmartAddicionalRequestFiles.Rectification LEFT JOIN DMS.DBO.TypeDetail Desistimiento ON Desistimiento.Id = SmartAddicionalRequestFiles.ComplaintWithdrawal -- Tipo de afiliado del cliente LEFT JOIN DMS.DBO.TYPEAFFILIATE_VW AffiliateType ON Clients.AffiliateTypeId = CONVERT(VARCHAR(40), AffiliateType.Id) -- Procedimiento asociado al radicado LEFT JOIN DMS.DBO.DMS_Procedures ON DMS_Procedures.Id = RequestFiles.ProcedureId LEFT JOIN DMS.DBO.PQRSDTypeRequest NameType ON NameType.Id = DMS_Procedures.NameTypeId LEFT JOIN DMS.DBO.PQRSDDetailRequest ProcedureType ON ProcedureType.Id = DMS_Procedures.ProcedureTypeId LEFT JOIN DMS.DBO.PQRSDRequestSpecification SpecificationType ON SpecificationType.Id = DMS_Procedures.SpecificationTypeId -- Tipo PQRSD del radicado LEFT JOIN DMS.dbo.PQRSDType PqrsType ON PqrsType.Id = RequestFiles.PqrsTypeId -- Verifica si tiene respuesta parcial LEFT JOIN ( SELECT CC.Id, AA.FiledDate, AA.FileNumber, AA.ChannelId, ROW_NUMBER() OVER (PARTITION BY BB.ParentId ORDER BY AA.FiledDate DESC) AS RN FROM DMS.dbo.RequestFiles AA INNER JOIN DMS.dbo.RelatedRequestFiles BB ON BB.RequestFileId = AA.Id INNER JOIN DMS.dbo.RequestFiles CC ON BB.ParentId = CC.Id WHERE AA.RequestTypeId = '956FE4FE-E0C0-4F50-B742-DB431F9F536B' AND AA.ResposnseText = 2 ) RequestFilesRespuestaParcial ON RequestFiles.Id = RequestFilesRespuestaParcial.Id AND RequestFilesRespuestaParcial.RN = '1' -- Verifica si tiene respuesta definitiva LEFT JOIN ( SELECT CC.Id, AA.FiledDate, AA.FileNumber, AA.ChannelId, ROW_NUMBER() OVER (PARTITION BY BB.ParentId ORDER BY AA.FiledDate DESC) AS RN FROM DMS.dbo.RequestFiles AA INNER JOIN DMS.dbo.RelatedRequestFiles BB ON BB.RequestFileId = AA.Id INNER JOIN DMS.dbo.RequestFiles CC ON BB.ParentId = CC.Id WHERE AA.RequestTypeId = '956FE4FE-E0C0-4F50-B742-DB431F9F536B' AND AA.ResposnseText = 1 ) RequestFilesRespuestaDefinitiva ON RequestFiles.Id = RequestFilesRespuestaDefinitiva.Id AND RequestFilesRespuestaDefinitiva.RN = '1' --Dependencia del usuario finalizador o de quien respondió LEFT JOIN ( SELECT * FROM ( SELECT UsersCompany.UserId, Dependencies.Id AS DependencyId, Dependencies.Name, Dependencies.Code, Dependencies.TopSection, UsersCompany.State, ROW_NUMBER() OVER (PARTITION BY UsersCompany.UserId ORDER BY TypeDetail.Code ASC) AS rn FROM DMS.DBO.UsersCompany INNER JOIN DMS.DBO.Dependencies ON Dependencies.Id = UsersCompany.DependenceId INNER JOIN DMS.DBO.TypeDetail ON UsersCompany.State = TypeDetail.Id ) RankedDependencies WHERE rn = 1 ) Dependencies ON ISNULL(UsersFinalizador.id, Users.id) = Dependencies.UserId --Consulta adiciona los campos de la actualización circular 19 LEFT JOIN ( SELECT RequestFilesId, FraudTypeName.Name AS [Tipo de Fraude], FraudModalityName.Name AS [Modalidad de Fraude], FORMAT(ISNULL(ClaimedAmount, 0), 'N0', 'es-CO') AS [Monto Reclamado], FORMAT(ISNULL(RecognizedAmount, 0), 'N0', 'es-CO') AS [Monto Reconocido] FROM DMS.dbo.SmartAddicionalRequestFiles LEFT JOIN DMS.dbo.TypeDetail AS FraudTypeName ON SmartAddicionalRequestFiles.FraudType = FraudTypeName.Id AND FraudTypeName.TypeHeadId = '6B6708AE-6E99-488D-9E3E-23F42D5EB754' LEFT JOIN DMS.dbo.TypeDetail AS FraudModalityName ON SmartAddicionalRequestFiles.FraudModality = FraudModalityName.Id AND FraudModalityName.TypeHeadId = '6B6708AE-6E99-488D-9E3E-23F42D5EB758') AS Circular19 ON RequestFiles.Id = Circular19.RequestFilesId -- Dependencia asociada al primer usuario con historial LEFT JOIN ( SELECT * FROM ( SELECT UsersCompany.UserId, Dependencies.Id AS DependencyId, Dependencies.Name, Dependencies.Code, Dependencies.TopSection, UsersCompany.State, ROW_NUMBER() OVER (PARTITION BY UsersCompany.UserId ORDER BY TypeDetail.Code ASC) AS rn FROM DMS.DBO.UsersCompany INNER JOIN DMS.DBO.Dependencies ON Dependencies.Id = UsersCompany.DependenceId INNER JOIN DMS.DBO.TypeDetail ON UsersCompany.State = TypeDetail.Id ) RankedDependencies WHERE rn = 1 ) Dependencies1 ON Users1.Id = Dependencies1.UserId -- Filtros principales WHERE RequestFiles.OriginId = '2A1B3A5A-6FEC-4234-A24E-B87A1710ECE7' -- Origen de radicados AND RequestFiles.StatusId <> 'E6D67E4A-F545-4D62-B882-5A38A0FC35E2' -- Excluir anulados AND RequestFileHistories.Status NOT IN ('4139c0b6-68ff-4e79-9796-36c04a9891c8','6a4c1604-0097-48e4-8c4c-ae1b735ed425') AND RequestFiles.PqrsTypeId NOT IN ( 'B48BF430-F3F7-4431-A375-3B9DBC1441E4', -- QuejEx '496B613B-8905-4496-A201-5AF1235DA91C' -- QejSFC ) AND RequestFileHistories.ProcessCode !='615'
5347478889581636401788142SELECT FORMAT(RF.FiledDate, 'dd-MM-yyyy HH:mm') AS 'Fecha radicado' , RF.FileNumber AS 'Radicado' , RF.[Subject] AS 'Asunto' FROM [dbo].[REQUESTFILE_VW] RF INNER JOIN [dbo].[CLIENTS_VW] C ON C.Id = RF.ClientId WHERE C.NumberIdentification = @NIdentification ORDER BY Radicado DESC
57939232067790882710067473INSERT INTO [dbo].[RequestFiles]            ([Id],[ClientId],[ProcedureId],[FileNumber],[FiledDate],[RequestTypeId],[StatusId],[Subject],[ResponseDate],[DestinyNameId],[ResponseTime]            ,[DependencyId],[Pages],[Description],[UserName],[Attachments],[NamesCustomer],[Phone],[ChannelId],[MessageBody],[SenderName],[Postal]            ,[ReceiverName],[ReceiverCompany],[ReceiverDependency],[SeriesId],[SubseriesId],[DestinyName],[Reason],[ApproverName],[ResposnseText],[ResponseName]            ,[CourrierName],[CourrierNumber],[PqrsTypeId],[Cellphone],[AttachDescription],[OriginId],[CaseId],[DocumentTypeId],[ExperationDate],[ContactId]            ,[Location],[DaysPendingManagement],[TermDeadline],[ThemeId],[ReasonId],[PerformanceId],[PQRSDAnalysisDecisionId],[SPCTypeId],[AssumeCost]            ,[InformedId],[Observations],[PriorityId],[NotesIncompleteInformation],[SIGEPDirectoryId],[Ruling],[AppealRulingApplies],[AcceptTerms],[ExternalId]            ,[MassiveConsecutive],[CrossReferenceNumber],[CrossReferenceResponsibleId],[CrossReferenceDate],[AnnexTypeId],[DocumentDate],[SignatureTypeId]            ,[FileName],[AttachmentTypeId],[ReceivingInstanceId],[UsersId],[ComplaintAttached],[ComplaintOrigin],[ReportedSmart],[ReportedAttachedSmart]            ,[BeginProcessCE],[Ip],[Captcha],[CollaborativeWorkName],[CollaborativeWorkBody],[RepresentativeId],[ClientRepresentativeRelatedId],[CollaboratorDependencyId],[ValidateCase], [RadicateOrion],[VersionCCD],[VersionTRD])      SELECT @IdReqFile,[ClientId],[ProcedureId],[FileNumber],[FiledDate],[RequestTypeId],[StatusId],[Subject],[ResponseDate],[DestinyNameId]       ,[ResponseTime],[DependencyId],[Pages],[Description],[UserName],[Attachments],[NamesCustomer],[Phone],[ChannelId],[MessageBody]       ,[SenderName],[Postal],[ReceiverName],[ReceiverCompany],[ReceiverDependency],[SeriesId],[SubseriesId],[DestinyName],[Reason]       ,[ApproverName],[ResposnseText],[ResponseName],[CourrierName],[CourrierNumber],[PqrsTypeId],[Cellphone],[AttachDescription],[OriginId]       ,[CaseId],[DocumentTypeId],[ExperationDate],[ContactId],[Location],[DaysPendingManagement],[TermDeadline],[ThemeId],[ReasonId]       ,[PerformanceId],[PQRSDAnalysisDecisionId],[SPCTypeId],[AssumeCost],[InformedId],[Observations],[PriorityId],[NotesIncompleteInformation]       ,[SIGEPDirectoryId],[Ruling],[AppealRulingApplies],[AcceptTerms],[ExternalId],[MassiveConsecutive],[CrossReferenceNumber],[CrossReferenceResponsibleId]       ,[CrossReferenceDate],[AnnexTypeId],[DocumentDate],[SignatureTypeId],[FileName],[AttachmentTypeId],[ReceivingInstanceId],[UsersId]       ,[ComplaintAttached],[ComplaintOrigin],[ReportedSmart],[ReportedAttachedSmart],[BeginProcessCE],[Ip],[Captcha],[CollaborativeWorkName],[CollaborativeWorkBody] ,[RepresentativeId],[ClientRepresentativeRelatedId],[CollaboratorDependencyId],[ValidateCase],[RadicateOrion],[VersionCCD],[VersionTRD]       FROM [dbo].[RadicateInfo_tmp]       WHERE [Id] = @IdRadicateTmp
5793901416735296699998679IF EXISTS ( SELECT 1 FROM dbo.RequestFiles r INNER JOIN inserted i ON rtrim(Ltrim(r.FileNumber)) = rtrim(Ltrim(i.FileNumber)) WHERE r.Id <> i.Id -- Excluir el mismo registro recién insertado (actualización) )
136214436214416901053326644INSERT INTO [dbo].[pqrsdConsolidated] ([RADICADO], [FECHA_RADICADO], [HORA_RADICADO], [MEDIO_DE_RECEPCION], [DEPENDENCIA_ASIGNADA], [DEPENDENCIA_DE_RADICACION], [USUARIO_RADICADOR], [TIPO_DE_PQR], [CAUSAL], [DETALLE_CAUSAL], [DETALLE_DESAGREGADO_CAUSAL], [NOMBRE_REMITENTE], [CONDICION_ESPECIAL], [TIPO_PERSONA], [TIPO_DE_DOCUMENTO_REMITENTE], [DOCUMENTO_DE_REMITENTE], [DIRECCION_REMITENTE], [BARRIO_REMITENTE], [CIUDAD_REMITENTE], [DEPARTAMENTO_REMITENTE], [EMAIL_REMITENTE], [TELEFONO_REMITENTE], [CELULAR_REMITENTE], [USUARIO_FOMAG], [ENTE_REMITENTE], [ASUNTO_RADICADO], [FUNCIONARIO_ACTUAL], [DEPENDENCIA_ACTUAL], [FECHA_DE_TRAMITE_PQR], [TRAMITE_PROCEDENTE], [TRAMITE_A_FAVOR_DEL_CONSUMIDOR_O_LA_ENTIDAD], [TRMTE_ACEPTADO_POR_LA_ENTIDAD], [TRMTE_RECHAZADO_POR_LA_ENTIDAD], [TRMTE_REMTDO_A_SUPERFINANCIERA], [TRMTE_RECTIFICADO_POR_ENTIDAD], [TRAMITE_DESISTIDO], [RADICADO_RESPUESTA_FINAL], [FECHA_DE_CONTESTACION], [MEDIO_DE_CONTESTACION], [DEPENDENCIA_QUE_CONTESTA], [USUARIO_QUE_CONTESTA], [ESTADO_ACTUAL], [TOTAL_DIAS_TRAMITE], [FECHA_DE_VENCIMIENTO], [MES/AÑO], [ESTADO_DEL_TRAMITE], [GESTION], [PROCESO], [USUARIO_QUE_ARCHIVA], [FECHA_RESPUESTA_PARCIAL], [TIPO DE RESPUESTA], [DIAS_RESPUESTA_PARCIAL], [FECHA_DE_VENCIMIENTO_FINAL], [RADICADO_RESPUESTA_PARCIAL], [REVISION], [APROBACION], [AREA], [AñoFil], [MesFil], [DependenciaFil], [UsuarioFil], [RowNum], [TIPO_DE_FRAUDE], [MODALIDAD_DE_FRAUDE], [MONTO_RECLAMADO], [MONTO_RECONOCIDO]) SELECT * FROM ( SELECT CAST(RequestFiles.FileNumber AS VARCHAR(30)) AS [RADICADO] ,CAST(RequestFiles.FiledDate AS DATE) AS [FECHA_RADICADO] ,CONVERT(VARCHAR(8), RequestFiles.FiledDate, 108) AS [HORA_RADICADO] ,CAST(CANAL.Name AS VARCHAR(30)) AS [MEDIO_DE_RECEPCION] ,CAST(COALESCE(Dependencies.Name, Dependencies3.Name, CONCAT(Users.Name, ' ', Users.Surnames)) AS VARCHAR(100)) AS [DEPENDENCIA_ASIGNADA] ,CAST(COALESCE(IIF(Users1.UserName='DEFENSOR','GERENCIA DE SERVICIO AL CLIENTE', Dependencies1.Name), Dependencies3.Name, CONCAT(Users.Name, ' ', Users.Surnames)) AS VARCHAR(100)) AS [DEPENDENCIA_DE_RADICACION] ,CAST(CONCAT(Users1.Name, ' ', Users1.Surnames) AS VARCHAR(50)) AS [USUARIO_RADICADOR] ,CAST(PqrsType.Name AS VARCHAR(40)) AS [TIPO_DE_PQR] ,CAST(NameType.Name AS VARCHAR(140)) AS [CAUSAL] ,CAST(ProcedureType.Name AS VARCHAR(140)) AS [DETALLE_CAUSAL] ,CAST(REPLACE(REPLACE(SpecificationType.Name, CHAR(13), ''), CHAR(10), '') AS VARCHAR(140)) AS [DETALLE_DESAGREGADO_CAUSAL] ,CASE WHEN TipoPersona.Name IN ('Persona Natural', 'Apoderado / Representante Legal') THEN CASE WHEN Contacto.Names IS NOT NULL THEN CONCAT(Contacto.Names, Contacto.Surnames) WHEN Contacto.Names IS NULL AND Clients.NamesClients IS NOT NULL THEN CONCAT(Clients.NamesClients,' ',Clients.SurNames) ELSE CAST(ISNULL(Contacto.BusinessName,Clients.BusinessName) AS VARCHAR(250)) END WHEN TipoPersona.Name = 'Persona Jurídica' THEN CASE WHEN Contacto.BusinessName IS NOT NULL THEN Contacto.BusinessName WHEN Contacto.BusinessName IS NULL AND Clients.BusinessName IS NOT NULL THEN Clients.BusinessName WHEN Contacto.BusinessName IS NULL AND Clients.BusinessName IS NULL THEN CONCAT(Contacto.Names, Contacto.Surnames) WHEN Contacto.BusinessName IS NULL AND Clients.BusinessName IS NULL AND Contacto.Names IS NULL THEN CONCAT(Clients.NamesClients,' ',Clients.SurNames) END WHEN TipoPersona.Name = 'Anónimo' THEN 'Anónimo' ELSE CAST(ISNULL(Contacto.BusinessName,Clients.BusinessName) AS VARCHAR(250)) END AS [NOMBRE_REMITENTE] ,SpecialCondition.Name AS [CONDICION_ESPECIAL] ,CAST(ISNULL(TipoPersona.Name, TP.Name) AS VARCHAR(40)) AS [TIPO_PERSONA] ,CAST(TIPODOCUMENTOREMITENTE.Name AS VARCHAR(80)) AS [TIPO_DE_DOCUMENTO_REMITENTE] ,ISNULL(Contacto.NumberIdentification, Clients.NumberIdentification) AS [DOCUMENTO_DE_REMITENTE] ---Se actualiza para resolver caso aranda 55437 JULIOCF ,CAST(Contacto.Address AS VARCHAR(160)) AS [DIRECCION_REMITENTE] ,CAST(NeighBorhood.Description AS VARCHAR(80)) AS [BARRIO_REMITENTE] ,CAST(ISNULL(C.Description, CITY.Description) AS VARCHAR(60)) AS [CIUDAD_REMITENTE] ,CAST(ISNULL(D.Description, DEPARTMENT.Description) AS VARCHAR(80)) AS [DEPARTAMENTO_REMITENTE] --,CAST(ISNULL(Contacto.Email, Clients.Email) AS VARCHAR(80)) AS [EMAIL_REMITENTE] ,CASE WHEN TipoPersona.Name != 'Anónimo' THEN CAST(ISNULL(Contacto.Email, Clients.Email) AS VARCHAR(80)) WHEN TipoPersona.Name = 'Anónimo' AND RequestFilesRespuestaDefinitiva.FileNumber IS NOT NULL THEN CAST( ISNULL(ContactoRespDef.Email, ClienteRespDef.Email) AS VARCHAR(80) ) WHEN TipoPersona.Name = 'Anónimo' AND RequestFilesRespuestaParcial.FileNumber IS NOT NULL THEN CAST( ISNULL(ContactoRespPar.Email, ClienteRespPar.Email) AS VARCHAR(80) ) WHEN TipoPersona.Name = 'Anónimo' THEN 'servicioalcliente@fiduprevisora.com.co' END AS [EMAIL_REMITENTE] ,CAST(Contacto.Telephone AS VARCHAR(15)) AS [TELEFONO_REMITENTE] ,CAST(Contacto.Mobile AS VARCHAR(15)) AS [CELULAR_REMITENTE] ,CAST(ISNULL(AffiliateTypeC.Code, AffiliateType.Code) AS VARCHAR(15)) AS [USUARIO_FOMAG] ,CAST(ReceivingInstance.Description AS VARCHAR(50)) AS [ENTE_REMITENTE] ,CAST(REPLACE(REPLACE(RequestFiles.Subject, CHAR(13), ''), CHAR(10), '') AS VARCHAR(700)) AS [ASUNTO_RADICADO] ,CAST(IIF(CONCAT(Users.Name, ' ', Users.Surnames) = '', CONCAT(Users1.Name, ' ', Users1.Surnames), CONCAT(Users.Name, ' ', Users.Surnames)) AS VARCHAR(50)) AS [FUNCIONARIO_ACTUAL] ,CAST(COALESCE(Dependencies4.Name, Dependencies.Name, Dependencies3.Name, CONCAT(Users.Name, ' ', Users.Surnames)) AS VARCHAR(100)) AS [DEPENDENCIA_ACTUAL] ,CAST(SmartAddicionalRequestFiles.CreationDateSmart AS DATE) AS [FECHA_DE_TRAMITE_PQR] ,CAST(SmartAddicionalRequestFiles.ComingFromProcedure AS VARCHAR(2)) AS [TRAMITE_PROCEDENTE] ,CAST(SmartAddicionalRequestFiles.FavorConsumerProcedure AS VARCHAR(30)) AS [TRAMITE_A_FAVOR_DEL_CONSUMIDOR_O_LA_ENTIDAD] ,CAST(Acceptance.Name AS VARCHAR(80)) AS [TRMTE_ACEPTADO_POR_LA_ENTIDAD] ,CAST(SmartAddicionalRequestFiles.RefusedEntityProcedure AS VARCHAR(2)) AS [TRMTE_RECHAZADO_POR_LA_ENTIDAD] ,CAST(SmartAddicionalRequestFiles.SuperFRemittedProcedure AS VARCHAR(2)) AS [TRMTE_REMTDO_A_SUPERFINANCIERA] ,CAST(rectification.Name AS VARCHAR(100)) AS [TRMTE_RECTIFICADO_POR_ENTIDAD] ,CAST(ComplaintWithdrawal.Description AS VARCHAR(40)) AS [TRAMITE_DESISTIDO] ,CAST(RequestFilesRespuestaDefinitiva.FileNumber AS VARCHAR(30)) AS [RADICADO_RESPUESTA_FINAL] ,CAST(ISNULL(RequestFilesRespuestaDefinitiva.FiledDate, RequestFilesRespuestaParcial.FiledDate) AS DATE) AS [FECHA_DE_CONTESTACION] ,CAST(MAX(CANAL1.Name) OVER (PARTITION BY RequestFiles.FileNumber) AS VARCHAR(30)) AS [MEDIO_DE_CONTESTACION] ,CAST(COALESCE(Dependencies4.Name, Dependencies.Name, Dependencies3.Name, CONCAT(Users.Name, ' ', Users.Surnames)) AS VARCHAR(100)) AS [DEPENDENCIA_QUE_CONTESTA] ,CAST(IIF(CONCAT(Users.Name, ' ', Users.Surnames) = '', CONCAT(Users1.Name, ' ', Users1.Surnames), CONCAT(Users.Name, ' ', Users.Surnames)) AS VARCHAR(50)) AS [USUARIO_QUE_CONTESTA] ,CASE WHEN COALESCE( CASE WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL AND CAST(RequestFilesRespuestaDefinitiva.FiledDate AS DATE) <= CAST(RequestFiles.ExperationDate AS DATE) THEN 'TRAMITADO OPORTUNAMENTE' WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL AND CAST(RequestFilesRespuestaDefinitiva.FiledDate AS DATE) > CAST(RequestFiles.ExperationDate AS DATE) THEN 'TRAMITADO EXTEMPORALMENTE' END, CASE WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NULL AND CAST(RequestFiles.ExperationDate AS DATE) < GETDATE() - 1 THEN 'VENCIDO' WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NULL --AND DATEDIFF(DAY, GETDATE(), CAST(RequestFiles.ExperationDate AS DATE)) IN (0,1,2,3) AND DA.[DiasHabiles] IN (0, 1, 2, 3) --Se realiza ajuste donde se tiene en cuenta solo los días laborales 966848 THEN 'PROXIMO A VENCER' WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NULL --AND DATEDIFF(DAY, GETDATE(), CAST(RequestFiles.ExperationDate AS DATE)) > 3 AND DA.[DiasHabiles] >3 --Se realiza ajuste donde se tiene en cuenta solo los días laborales 966848 THEN 'EN TIEMPO' END ) IN ('VENCIDO', 'TRAMITADO EXTEMPORALMENTE') THEN 'INOPORTUNO' ELSE 'OPORTUNO' END AS [ESTADO_ACTUAL] ,RequestFilesExpirationDate.ProcedureDays AS [TOTAL_DIAS_TRAMITE] ,CAST(RequestFiles.ExperationDate AS DATE)[FECHA_DE_VENCIMIENTO] ,CAST(CONCAT(DATENAME(MONTH, DATEADD(MONTH, MONTH(RequestFiles.FiledDate) - 1, '1900-01-01')), ' - ', YEAR(RequestFiles.FiledDate)) AS VARCHAR(20)) AS [MES/AÑO] ,COALESCE( CASE WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL AND CAST(RequestFilesRespuestaDefinitiva.FiledDate AS DATE) <= CAST(RequestFiles.ExperationDate AS DATE) THEN 'TRAMITADO OPORTUNAMENTE' WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL AND CAST(RequestFilesRespuestaDefinitiva.FiledDate AS DATE) > CAST(RequestFiles.ExperationDate AS DATE) THEN 'TRAMITADO EXTEMPORALMENTE' END, CASE WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NULL AND CAST(RequestFiles.ExperationDate AS DATE) < GETDATE() - 1 THEN 'VENCIDO' WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NULL --AND DATEDIFF(DAY, GETDATE(), CAST(RequestFiles.ExperationDate AS DATE)) IN ( 0, 1, 2, 3) AND DA.[DiasHabiles] IN (0, 1, 2, 3) --Se realiza ajuste donde se tiene en cuenta solo los días laborales 966848 THEN 'PROXIMO A VENCER' WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NULL --AND DATEDIFF(DAY, GETDATE(), CAST(RequestFiles.ExperationDate AS DATE)) > 3 AND DA.[DiasHabiles] > 3 --Se realiza ajuste donde se tiene en cuenta solo los días laborales 966848 THEN 'EN TIEMPO' END ) AS [ESTADO_DEL_TRAMITE] ,CASE WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL THEN 'Tramitado' ELSE 'Pendiente' END AS [GESTION] ,ESTADO.Name AS [PROCESO] ,CAST(CASE WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL THEN MAX(IIF(CONCAT(Users2.Name, ' ', Users2.Surnames) = '', NULL, CONCAT(Users2.Name, ' ', Users2.Surnames))) OVER (PARTITION BY RequestFiles.FileNumber) END AS VARCHAR(50)) AS [USUARIO_QUE_ARCHIVA] ,CAST(RequestFilesRespuestaParcial.FiledDate AS DATE) AS [FECHA_RESPUESTA_PARCIAL] ,CASE WHEN RequestFilesRespuestaParcial.FiledDate IS NOT NULL AND RequestFilesRespuestaDefinitiva.FiledDate IS NULL THEN 'Respuesta Parcial' WHEN RequestFilesRespuestaDefinitiva.FiledDate IS NOT NULL THEN 'Respuesta Definitiva' END AS [TIPO_DE_RESPUESTA] ,CASE WHEN RequestFilesRespuestaParcial.FiledDate IS NOT NULL AND Users1.UserName ='DEFENSOR' THEN 8 WHEN RequestFilesRespuestaParcial.FiledDate IS NOT NULL THEN 15 END AS [DIAS_RESPUESTA_PARCIAL] ,CAST(RequestFiles.ExperationDate AS DATE) AS [FECHA_DE_VENCIMIENTO_FINAL] ,CAST(RequestFilesRespuestaParcial.FileNumber AS VARCHAR(30)) AS [RADICADO_RESPUESTA_PARCIAL] ,USuarioRevision.Funcionario AS [REVISION] ,USuarioAprobacion.Funcionario AS [APROBACION] ,CAST(COALESCE(Dependencies4.Name, Dependencies.Name, Dependencies3.Name, CONCAT(Users.Name, ' ', Users.Surnames)) AS VARCHAR(100)) AS [AREA] ,CAST(YEAR(RequestFiles.FiledDate) AS INT) AS [AñoFil] ,CAST(MONTH(RequestFiles.FiledDate) AS INT) AS [MesFil] ,MAX(ISNULL(Dependencies.Code, '0')) OVER (PARTITION BY RequestFiles.FileNumber) AS [DependenciaFil] ,Users.UserName AS [UsuarioFil] ,ROW_NUMBER() OVER (PARTITION BY RequestFiles.FileNumber ORDER BY RequestFiles.FiledDate DESC) AS RowNum -- Add campos Circular 19 ,Circular19.[TIPO_DE_FRAUDE] AS [TIPO_DE_FRAUDE] ,Circular19.[MODALIDAD_DE_FRAUDE] AS [MODALIDAD_DE_FRAUDE] ,Circular19.[MONTO_RECLAMADO] AS [MONTO_RECLAMADO] ,Circular19.[MONTO_RECONOCIDO] AS [MONTO_RECONOCIDO] FROM dms.dbo.RequestFiles WITH (NOLOCK) --Unión con RequestFileHistories para obtener la historia más reciente LEFT JOIN ( SELECT ROW_NUMBER() OVER (PARTITION BY RequestFileId ORDER BY CreationDate DESC) AS MaxReg ,RequestFileId ,CreationDate ,DependencyId ,CaseId ,UserName ,Status FROM dms.dbo.RequestFileHistories WHERE Status NOT IN ('31B6159D-DE9D-4CBA-9508-4D9D4EE2FAF7','C143C3ED-F4F1-4524-AD59-80FF0F35CB9C' ,'9337A841-5E78-4C45-B1BE-9607B0833F5C','56D07A62-76F6-4AB3-A26F-E18C949CBA60','59536473-5BE9-4D7D-9CD8-D3FCB7A8D652' ,'9BD808F4-6E9F-4710-B789-19FE1CE8C55A', --Se agregan los siguientes estados por caso SAC 960781 '4139c0b6-68ff-4e79-9796-36c04a9891c8','6a4c1604-0097-48e4-8c4c-ae1b735ed425' ,'8d6acd5a-d128-45b0-b1a5-f9c0fef90708','EF7B7E43-9151-422A-9A2C-6E3B6C53BC85') AND (ProcessCode != 'Combinación de Correspondencia - ' AND ProcessName != 'Respuesta Parcial') AND ProcessCode !='615' ) AS RequestFileHistories ON RequestFileHistories.RequestFileId=RequestFiles.Id AND RequestFileHistories.MaxReg = 1 AND RequestFileHistories.DependencyId IS NOT NULL ------ Unión con RequestFileHistories1 para obtener la historia más antigua LEFT JOIN ( SELECT ROW_NUMBER() OVER (PARTITION BY RequestFileId ORDER BY CreationDate ASC) AS MinReg ,RequestFileId ,CreationDate ,DependencyId ,UserName FROM dms.dbo.RequestFileHistories WHERE ProcessCode !='615' ) AS RequestFileHistories1 ON RequestFileHistories1.RequestFileId=RequestFiles.Id AND RequestFileHistories1.MinReg = 1 LEFT JOIN OpheliaSuite.dbo.WF_SEGUI_PEN ON WF_SEGUI_PEN.CAS_CONT=RequestFileHistories.CaseId AND WF_SEGUI_PEN.SEG_SUBJ NOT LIKE '%VISUALIZAR INCONSISTENCIA%' AND FLU_CONT !=100 LEFT JOIN [Stage].[dbo].[Users_Stage] Users ON Users.UserName=ISNULL(WF_SEGUI_PEN.SEG_UENC,RequestFileHistories.UserName) LEFT JOIN [Stage].[dbo].[Users_Stage] Users1 ON Users1.UserName=RequestFileHistories1.UserName LEFT JOIN dms.dbo.Dependencies Dependencies3 ON Dependencies3.Id=RequestFileHistories.DependencyId LEFT JOIN ( --Subconsulta para obtener el nombre de la dependencia asociada al usuario SELECT UserId ,Dependencies.Name ,CASE WHEN (Dependencies.Name) =Dependencies.Name THEN Dependencies.Code END Code ,ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Code) NUMROW FROM [Stage].[dbo].[Users_Stage] Users INNER JOIN DMS.DBO.UsersCompany ON Users.Id=UsersCompany.UserId INNER JOIN DMS.DBO.Dependencies ON Dependencies.Id=UsersCompany.DependenceId )Dependencies ON Users.Id=Dependencies.UserId AND Dependencies.NUMROW=1 LEFT JOIN dms.dbo.Dependencies Dependencies1 ON Dependencies1.Id=RequestFileHistories1.DependencyId LEFT JOIN dms.dbo.Dependencies Dependencies4 ON RequestFileHistories.DependencyId = Dependencies4.Id LEFT JOIN dms.dbo.TypeDetail ESTADO ON CAST(ESTADO.Id AS VARCHAR(40))=RequestFileHistories.Status LEFT JOIN dms.dbo.TypeDetail CANAL ON CANAL.Id=RequestFiles.ChannelId LEFT JOIN dms.dbo.Clients ON RequestFiles.ClientId=Clients.Id LEFT JOIN DMS.DBO.Contacts Contacto ON Contacto.Id = RequestFiles.ContactId LEFT JOIN DMS.dbo.TypeDetail SpecialCondition ON Clients.SpecialConditionId = SpecialCondition.ID LEFT JOIN dms.dbo.TypeDetail TIPODOCUMENTOREMITENTE ON Clients.DocumentTypeId=TIPODOCUMENTOREMITENTE.Id LEFT JOIN dms.dbo.GeographicsLocation CITY ON Clients.CityId=CITY.Id LEFT JOIN dms.dbo.GeographicsLocation C ON Contacto.CityId = C.ID LEFT JOIN dms.dbo.GeographicsLocation DEPARTMENT ON Clients.DepartamentId=DEPARTMENT.Id LEFT JOIN dms.dbo.GeographicsLocation D ON Contacto.DepartamentId = D.ID LEFT JOIN dms.dbo.GeographicsLocation NeighBorhood ON Clients.NeighBorhoodId=NeighBorhood.Id LEFT JOIN dms.dbo.DMS_Procedures ON DMS_Procedures.Id=RequestFiles.ProcedureId LEFT JOIN DMS.DBO.PQRSDTypeRequest NameType ON NameType.Id=DMS_Procedures.NameTypeId LEFT JOIN DMS.DBO.PQRSDDetailRequest ProcedureType ON ProcedureType.Id=DMS_Procedures.ProcedureTypeId LEFT JOIN DMS.DBO.PQRSDRequestSpecification SpecificationType ON SpecificationType.Id=DMS_Procedures.SpecificationTypeId LEFT JOIN DMS.dbo.PQRSDType PqrsType ON PqrsType.Id=RequestFiles.PqrsTypeId LEFT JOIN dms.dbo.TypeDetail AffiliateTypeC ON Contacto.AffiliateTypeId = AffiliateTypeC.ID LEFT JOIN dms.dbo.TypeDetail AffiliateType ON Clients.AffiliateTypeId=CAST(AffiliateType.Id AS VARCHAR(40)) LEFT JOIN dms.dbo.TypeDetail ReceivingInstance ON RequestFiles.ReceivingInstanceId=CAST(ReceivingInstance.Id AS VARCHAR(40)) LEFT JOIN dms.dbo.SmartAddicionalRequestFiles ON SmartAddicionalRequestFiles.RequestFilesId=RequestFiles.Id LEFT JOIN dms.dbo.TypeDetail Acceptance ON SmartAddicionalRequestFiles.Acceptance=CAST(Acceptance.Id AS VARCHAR(40)) LEFT JOIN dms.dbo.TypeDetail ComplaintWithdrawal ON SmartAddicionalRequestFiles.ComplaintWithdrawal=CAST(ComplaintWithdrawal.Id AS VARCHAR(40)) LEFT JOIN dms.dbo.TypeDetail Rectification ON SmartAddicionalRequestFiles.Rectification = CAST(Rectification .Id AS VARCHAR(40)) LEFT JOIN RequestFilesExpirationDate ON RequestFilesExpirationDate.FileNumber=RequestFiles.FileNumber LEFT JOIN (--LEFT JOIN con RequestFileHistoriesRevision para obtener la última revisión de la respuesta SELECT UserName,RequestFileId,ROW_NUMBER() OVER(PARTITION BY RequestFileId ORDER BY CreationDate DESC,RequestFileId,UserName)NumberFile FROM dms.dbo.RequestFileHistories A inner JOIN dms.dbo.TypeDetail ESTADO ON CAST(ESTADO.Id AS VARCHAR(40))=a.Status AND ESTADO.name IN ('Respuesta en revisión') )RequestFileHistoriesRevision ON RequestFiles.Id=RequestFileHistoriesRevision.RequestFileId AND RequestFileHistoriesRevision.NumberFile=1 LEFT JOIN (--LEFT JOIN con RequestFileHistoriesAprobacion para obtener la última aprobación de la respuesta SELECT UserName,RequestFileId,ROW_NUMBER() OVER(PARTITION BY RequestFileId ORDER BY CreationDate DESC,RequestFileId,UserName)NumberFile FROM dms.dbo.RequestFileHistories A inner JOIN dms.dbo.TypeDetail ESTADO ON CAST(ESTADO.Id AS VARCHAR(40))=a.Status AND ESTADO.name IN ('Respuesta aprobada') )RequestFileHistoriesAprobacion ON RequestFiles.Id=RequestFileHistoriesAprobacion.RequestFileId AND RequestFileHistoriesAprobacion.NumberFile=1 LEFT JOIN dms.dbo.USERS_VW USuarioRevision ON USuarioRevision.UserName= RequestFileHistoriesRevision.UserName LEFT JOIN dms.dbo.USERS_VW USuarioAprobacion ON USuarioAprobacion.UserName= RequestFileHistoriesAprobacion.UserName LEFT JOIN (--LEFT JOIN con RequestFilesRespuestaParcial y RequestFilesRespuestaDefinitiva para obtener las respuestas parciales y definitivas SELECT B.ParentId,C.FiledDate,C.FileNumber,ChannelId,ContactId,ClientId,UserName,ROW_NUMBER() OVER(PARTITION BY B.ParentId ORDER BY C.FiledDate ASC,C.FileNumber,B.ParentId,ChannelId,UserName)NumberFile FROM dms.dbo.RelatedRequestFiles B INNER JOIN dms.dbo.RequestFiles C ON B.RequestFileId=C.Id WHERE C.RequestTypeId='956FE4FE-E0C0-4F50-B742-DB431F9F536B' AND C.ResposnseText=2)RequestFilesRespuestaParcial ON RequestFiles.Id=RequestFilesRespuestaParcial.ParentId AND RequestFilesRespuestaParcial.NumberFile=1 LEFT JOIN (-- LEFT JOIN con otras respuestas definitivas para obtener la última respuesta definitiva SELECT B.ParentId,C.FiledDate,C.FileNumber,ChannelId,ContactId,ClientId,UserName,ROW_NUMBER() OVER(PARTITION BY B.ParentId ORDER BY C.FiledDate DESC,C.FileNumber,B.ParentId,ChannelId,UserName)NumberFile FROM dms.dbo.RelatedRequestFiles B INNER JOIN dms.dbo.RequestFiles C ON B.RequestFileId=C.Id WHERE C.RequestTypeId='956FE4FE-E0C0-4F50-B742-DB431F9F536B' AND C.ResposnseText=1) RequestFilesRespuestaDefinitiva ON RequestFiles.Id=RequestFilesRespuestaDefinitiva.ParentId AND RequestFilesRespuestaDefinitiva.NumberFile=1 -- Contacto respuesta definitiva LEFT JOIN dms.dbo.Contacts ContactoRespDef ON ContactoRespDef.Id = RequestFilesRespuestaDefinitiva.ContactId LEFT JOIN dms.dbo.Clients ClienteRespDef ON ClienteRespDef.Id = RequestFilesRespuestaDefinitiva.ClientId -- Contacto respuesta parcial LEFT JOIN dms.dbo.Contacts ContactoRespPar ON ContactoRespPar.Id = RequestFilesRespuestaParcial.ContactId LEFT JOIN dms.dbo.Clients ClienteRespPar ON ClienteRespPar.Id = RequestFilesRespuestaParcial.ClientId -- LEFT JOIN dms.dbo.TypeDetail CANAL1 ON CANAL1.Id=ISNULL(RequestFilesRespuestaDefinitiva.ChannelId,RequestFilesRespuestaParcial.ChannelId) LEFT JOIN [Stage].[dbo].[Users_Stage] Users2 ON Users2.UserName=ISNULL(RequestFilesRespuestaParcial.UserName,RequestFilesRespuestaDefinitiva.UserName) LEFT JOIN DMS.DBO.TypeDetail TipoPersona ON TipoPersona.Id=Clients.PersonTypeId LEFT JOIN DMS.DBO.TypeDetail TP ON Contacto.TypeContactId = TP.ID --Consulta adiciona los campos de la actualización circular 19 LEFT JOIN ( SELECT RequestFilesId, FraudTypeName.Name AS [TIPO_DE_FRAUDE], FraudModalityName.Name AS [MODALIDAD_DE_FRAUDE], FORMAT(ISNULL(ClaimedAmount, 0), 'N0', 'es-CO') AS [MONTO_RECLAMADO], FORMAT(ISNULL(RecognizedAmount, 0), 'N0', 'es-CO') AS [MONTO_RECONOCIDO] FROM DMS.DBO.SmartAddicionalRequestFiles LEFT JOIN DMS.DBO.TypeDetail AS FraudTypeName ON SmartAddicionalRequestFiles.FraudType = FraudTypeName.Id AND FraudTypeName.TypeHeadId = '6B6708AE-6E99-488D-9E3E-23F42D5EB754' LEFT JOIN DMS.DBO.TypeDetail AS FraudModalityName ON SmartAddicionalRequestFiles.FraudModality = FraudModalityName.Id AND FraudModalityName.TypeHeadId = '6B6708AE-6E99-488D-9E3E-23F42D5EB758') AS Circular19 ON RequestFiles.Id = Circular19.RequestFilesId --Tabla de días habiles para calcular el campo de Estado_Tramite LEFT JOIN STAGE.DBO.DiasHabiles DA ON RequestFiles.id = DA.id WHERE --RequestFilesExpirationDate.FileNumber IS NOT NULL RequestFiles.OriginId='2A1B3A5A-6FEC-4234-A24E-B87A1710ECE7' AND ESTADO.name NOT IN ('Anulado','Solicitud de anulación') ) AS CF WHERE CF.RowNum = 1
11233936530301416283670832SELECT FORMAT(RF.FiledDate, 'dd-MM-yyyy HH:mm') AS 'Fecha radicado' , RF.FileNumber AS 'Radicado' , RF.[Subject] AS 'Asunto' FROM [dbo].[REQUESTFILE_VW] RF INNER JOIN [dbo].[CLIENTS_VW] C ON C.Id = RF.ClientId WHERE C.NumberIdentification = @NIdentification ORDER BY Radicado DESC
28320307114391414831921605SELECT DISTINCT RF.FiledDate AS 'FechaRadicacion', RF.ExperationDate AS 'Fvencimiento', RF.UserName AS 'Radicador', CONVERT(VARCHAR, RF.ReportedSmart) AS 'ReportedSmart2', RF.ComplaintOrigin AS 'OrigenSmart', IIF(PR.Code IS NULL, NULL, CONCAT(PR.Code, '|', PR.[Name])) AS 'Precepción', -- PUNTO DE RECEPCION IIF(TP.Code IS NULL, NULL, CONCAT(TP.Code, '|', TP.[Name])) AS 'Tpersona', IIF(TD.Code IS NULL, NULL, CONCAT(TD.Code, '|', TD.[Name])) AS 'Tident', CL.NumberIdentification AS 'Identificacion', CT.BusinessName AS 'Empresa', CT.Names AS 'Nombres', CL.SurNames AS 'Apellidos', IIF(SX.Code IS NULL, NULL, CONCAT(SX.Code, '|', SX.[Name])) AS 'Sexo', IIF(CL.LGBTIQCommunity IS NULL, 'false', IIF(CONVERT(VARCHAR(10), CL.LGBTIQCommunity) = '1', 'true', 'false')) AS 'LGTBI', IIF(CE.Code IS NULL, NULL, CONCAT(CE.Code, '|', CE.[Name])) AS 'Cespecial', IIF(CL.AffiliatedFomag IS NULL, '2', IIF(CONVERT(VARCHAR(10), CL.AffiliatedFomag) = '0', '2', '1')) AS 'Afiliado', IIF(TA.Code IS NULL, NULL, CONCAT(TA.Code, '|', TA.[Name])) AS 'Tafiliado', IIF(IR.Code IS NULL, NULL, CONCAT(IR.Code, '|', IR.[Name])) AS 'Irecepcion', IIF(PA.IataCode IS NULL, NULL, CONCAT(PA.IataCode, '|', PA.[Description])) AS 'Pais', IIF(DE.IataCode IS NULL, NULL, CONCAT(DE.IataCode, '|', DE.[Description])) AS 'Departamento', IIF(CI.IataCode IS NULL, NULL, CONCAT(CI.IataCode, '|', CI.[Description])) AS 'Ciudad', IIF(BA.IataCode IS NULL, NULL, CONCAT(BA.IataCode, '|', BA.[Description])) AS 'Barrio', CT.[Address] AS 'Direccion', CT.PostalCode AS 'Postal', ISNULL(CT.Telephone, CL.Phone) AS 'Tel', ISNULL(CT.Mobile, CL.Mobile) AS 'Celular', ISNULL(CT.Email, CL.Email) AS 'Correo', APVC.NumberIdentification AS 'IdentificacionApoderado', IIF(APTP.Code IS NULL, NULL, CONCAT(APTP.Code, '|', APTP.[Name])) AS 'TipoApoderado', AP.Names AS 'NombreCompletoApoderado', IIF(PAA.IataCode IS NULL, NULL, CONCAT(PAA.IataCode, '|', PAA.[Description])) AS 'PaisApo', IIF(DEA.IataCode IS NULL, NULL, CONCAT(DEA.IataCode, '|', DEA.[Description])) AS 'DepartamentoApo', IIF(CIA.IataCode IS NULL, NULL, CONCAT(CIA.IataCode, '|', CIA.[Description])) AS 'CiudadApo', IIF(BAA.IataCode IS NULL, NULL, CONCAT(BAA.IataCode, '|', BAA.[Description])) AS 'BarrioApo', AP.[Address] AS 'DireccionResiApo', AP.AddressOfficeAttorney AS 'DireccionOfiApo', AP.Telephone AS 'TelApo', AP.Mobile AS 'CelApo', AP.Email AS 'CorreoApo', IIF(TPQR.Code IS NULL, NULL, CONCAT(TPQR.Code, '|', TPQR.[Name])) AS 'TIPOPQR', IIF(RF.ProcedureId IS NULL, NULL, CONCAT(RF.ProcedureId , '|', [NameSolicitud], ' / ', [NameDetalle], ' / ', [NameEspecificacion])) AS 'Tramite', RF.ResponseTime AS 'DiasGestion', IIF(DP.Code IS NULL, NULL, CONCAT(DP.Code, '|', DP.[Name])) AS 'Dependencia', CONCAT(SDE.[Responsible_UserName], '|', SDE.[Responsable_Funcionario]) AS 'FunionarioResponsable', RF.[Subject] AS 'Asunto', RF.MessageBody AS 'Observaciones', RF.DocumentDate AS 'FechaDoc', RF.Pages AS 'Folios', RF.Attachments AS 'Anexos', TAX.Code AS 'TAnexo', RF.AttachDescription AS 'DAnexos', RF.CourrierName AS 'Courrier', RF.CourrierNumber AS 'Guia', RF.[Location] AS 'Location', IIF(RFH.Id IS NOT NULL, '1', '0') AS 'Reclasificado' FROM [dbo].[RequestFiles] RF WITH (NOLOCK) LEFT JOIN [dbo].[DEPENDENCIE_VW] DP WITH (NOLOCK) ON RF.DependencyId = DP.Id LEFT JOIN [dbo].[DMSProcedureNew_VW] SDE WITH (NOLOCK) ON RF.ProcedureId = SDE.IdProcedure LEFT JOIN [dbo].TypeDetail PR WITH (NOLOCK) ON PR.Id = RF.ChannelId LEFT JOIN [dbo].[CLIENTS_VW] CL WITH (NOLOCK) ON CL.Id = RF.ClientId LEFT JOIN [dbo].[CONTACTS_VW] CT WITH (NOLOCK) ON CT.Id = RF.ContactId LEFT JOIN [dbo].TypeDetail TP WITH (NOLOCK) ON TP.Id = CL.PersonTypeId LEFT JOIN [dbo].[TYPEIDENTI_VW] TD WITH (NOLOCK) ON TD.Id = CL.DocumentTypeId LEFT JOIN [dbo].[TYPEGENDER_VW] SX WITH (NOLOCK) ON SX.Id = CL.GenderId LEFT JOIN [dbo].TypeDetail CE WITH (NOLOCK) ON CE.Id = CL.SpecialConditionId LEFT JOIN [dbo].TypeDetail TA WITH (NOLOCK) ON TA.Id = CL.AffiliateTypeId LEFT JOIN [dbo].TypeDetail IR WITH (NOLOCK) ON IR.Id = RF.ReceivingInstanceId LEFT JOIN [dbo].[GEOGRAPHICSLOCATIOPAIS_VW] PA WITH (NOLOCK) ON PA.Id = CT.CountryId LEFT JOIN [dbo].[GEOGRAPHICSLOCATIODEP_VW] DE WITH (NOLOCK) ON DE.Id = CT.DepartamentId LEFT JOIN [dbo].[GEOGRAPHICSLOCATIONMUN_VW] CI WITH (NOLOCK) ON CI.Id = CT.CityId LEFT JOIN [dbo].[GEOGRAPHICSLOCATIOBARRIO_VW] BA WITH (NOLOCK) ON BA.Id = CT.NeighBorhoodId LEFT JOIN [dbo].Representatives AP WITH (NOLOCK) ON AP.Id = RF.RepresentativeId LEFT JOIN [dbo].[CLIENTS_VW] APVC WITH (NOLOCK) ON AP.ClientId = APVC.Id LEFT JOIN [dbo].[TYPEPERSON_VW] APTP WITH (NOLOCK) ON AP.TypeRepresentativeId = APTP.Id LEFT JOIN [dbo].[TYPEIDENTI_VW] TDA WITH (NOLOCK) ON TDA.Id = AP.DocumentTypeId LEFT JOIN [dbo].[GEOGRAPHICSLOCATIOPAIS_VW] PAA WITH (NOLOCK) ON PAA.Id = AP.CountryId LEFT JOIN [dbo].[GEOGRAPHICSLOCATIODEP_VW] DEA WITH (NOLOCK) ON DEA.Id = AP.DepartamentId LEFT JOIN [dbo].[GEOGRAPHICSLOCATIONMUN_VW] CIA WITH (NOLOCK) ON CIA.Id = AP.CityId LEFT JOIN [dbo].[GEOGRAPHICSLOCATIOBARRIO_VW] BAA WITH (NOLOCK) ON BAA.Id = AP.NeighBorhoodId LEFT JOIN [dbo].[TYPEPQRSD_VW] TPQR WITH (NOLOCK) ON TPQR.Id = RF.PqrsTypeId LEFT JOIN [dbo].TypeDetail TAX WITH (NOLOCK) ON TAX.Id = RF.AttachmentTypeId LEFT JOIN [dbo].[RequestFileHistories] RFH ON RF.Id = RFH.RequestFileId AND RFH.[Status] IN ('A50A8C7E-C6C9-432A-B104-45BDC9AC0172','AFAB971F-C0F8-4707-84C9-F8F3E9B1CF7E') WHERE RF.FileNumber = @NoRadicado
9229730432311296594641936SELECT DISTINCT RF.Radicado, RF.Fecha, RF.[Tipo de persona], RF.Entidad, RF.Destinatario, RF.País, RF.Departamento, RF.Ciudad, RF.Dirección, RF.[Correo electrónico], RF.Asunto, RF.[Cuerpo del mensaje], RF.Folios, RF.[Descripción de anexos], RF.[Canal de envío], RF.Elaboró, RF.Revisó, RF.Aprobó, RF.Compañía, RF.Dependencia, RF.Funcionario, RF.Cargo, RF.TFirma, RF.[Firma Firmante], RF.[Cargo destinatario], ISNULL (RF.CollaborativeWorkName, '1') AS 'CollaborativeWorkName', RF.CollaborativeWorkBody FROM GETDATABYRADICATE_VW RF WHERE RF.Radicado = @Radicado
526349252698294881819269030select RequestFiles . FileNumber , convert ( VARCHAR , RequestFiles . FiledDate ) Z from RequestFiles left join RequestFileHistories on RequestFiles . Id = RequestFileHistories . RequestFileId and RequestFileHistories . CreationDate = ( select MAX ( CreationDate ) from dms . dbo . RequestFileHistories A where A . RequestFileId = RequestFileHistories . RequestFileId and A . Status not in ( @0 ) ) left join TypeDetail on TypeDetail . Id = RequestFileHistories . Status where RequestFileHistories . UserName = @1 and TypeDetail . Name < > @2
452609955799724231479969SELECT FORMAT(RF.FiledDate, 'dd-MM-yyyy HH:mm') AS 'Fecha radicado' , RF.FileNumber AS 'Radicado' , RF.[Subject] AS 'Asunto' FROM [dbo].[REQUESTFILE_VW] RF INNER JOIN [dbo].[CLIENTS_VW] C ON C.Id = RF.ClientId WHERE C.NumberIdentification = @NIdentification ORDER BY Radicado DESC

Archivos SQL más pesados

DatabaseNameLogicalNamePhysicalNameFileTypeSizeMBMaxSizeMBIsPercentGrowth
OpheliaSuiteOpheliaSuiteDMSE:\SGDEA\OpheliaSuite.mdfROWS4421766-1False
DMSDMSE:\SGDEA\DMS.mdfROWS49903-1False
DMSDMS_BE:\SGDEA\DMS_B.ndfROWS41418-1False
DriveDriveE:\SGDEA\Drive.mdfROWS34484-1False
tempdbtemp6D:\TEMPDB\temp6.mdfROWS31071-1False
tempdbtemp7D:\TEMPDB\temp7.mdfROWS30755-1False
tempdbtemp5D:\TEMPDB\temp5.mdfROWS30735-1False
tempdbtemp10D:\TEMPDB\temp10.mdfROWS30709-1False
tempdbtemp8D:\TEMPDB\temp8.mdfROWS29267-1False
tempdbtemp9D:\TEMPDB\temp9.mdfROWS23991-1False
tempdbtemp4D:\TEMPDB\temp4.mdfROWS21358-1False
StageStage_logF:\LOG\Stage_log.ldfLOG183792097152False
StageStageE:\SGDEA\Stage.mdfROWS13321-1False
tempdbtemp11D:\TEMPDB\temp11.mdfROWS13169-1False
tempdbtempdevD:\TEMPDB\tempdev.mdfROWS12644-1False
DMS_bk_040923DMSE:\SGDEA\DMS_bk_040923.mdfROWS5000-1False
DMS_BK_20DMSE:\SGDEA\DMS_BK_20.mdfROWS2184-1False
DMSDMS_logF:\LOG\DMS_log.ldfLOG16752097152False
OpheliaSuiteOpheliaSuiteDMS_logF:\LOG\OpheliaSuite_log.ldfLOG6512097152False
AgoraSSBAgoraE:\SGDEA\Agora.mdfROWS436-1False

Errores recientes SQL

LogDateProcessInfoText
5/26/2026 4:09:53 AMLogonError: 18456, Severity: 14, State: 38.
5/26/2026 4:09:53 AMLogonLogin failed for user 'ophelia'. Reason: Failed to open the explicitly specified database 'DMS'. [CLIENT: 10.238.99.150]
5/26/2026 4:09:53 AMLogonError: 18456, Severity: 14, State: 38.
5/26/2026 4:09:53 AMLogonLogin failed for user 'ophelia'. Reason: Failed to open the explicitly specified database 'DMS'. [CLIENT: 10.238.99.171]
5/26/2026 4:09:53 AMLogonError: 18456, Severity: 14, State: 38.
5/26/2026 4:09:53 AMLogonLogin failed for user 'ophelia'. Reason: Failed to open the explicitly specified database 'DMS'. [CLIENT: 10.238.99.151]
5/26/2026 4:09:52 AMLogonError: 18456, Severity: 14, State: 38.
5/26/2026 4:09:52 AMLogonLogin failed for user 'ophelia'. Reason: Failed to open the explicitly specified database 'DMS'. [CLIENT: 10.238.99.153]
5/26/2026 4:09:30 AMLogonError: 18456, Severity: 14, State: 38.
5/26/2026 4:09:30 AMLogonLogin failed for user 'ophelia'. Reason: Failed to open the explicitly specified database 'DMS'. [CLIENT: 10.238.99.150]
5/26/2026 4:09:30 AMLogonError: 18456, Severity: 14, State: 38.
5/26/2026 4:09:30 AMLogonLogin failed for user 'ophelia'. Reason: Failed to open the explicitly specified database 'DMS'. [CLIENT: 10.238.99.151]
5/26/2026 4:09:29 AMLogonError: 18456, Severity: 14, State: 38.
5/26/2026 4:09:29 AMLogonLogin failed for user 'ophelia'. Reason: Failed to open the explicitly specified database 'DMS'. [CLIENT: 10.238.99.153]
5/26/2026 4:09:23 AMLogonError: 18456, Severity: 14, State: 38.
5/26/2026 4:09:23 AMLogonLogin failed for user 'ophelia'. Reason: Failed to open the explicitly specified database 'DMS'. [CLIENT: 10.238.99.171]
5/26/2026 4:09:16 AMLogonError: 18456, Severity: 14, State: 38.
5/26/2026 4:09:16 AMLogonLogin failed for user 'ophelia'. Reason: Failed to open the explicitly specified database 'DMS'. [CLIENT: 10.238.99.150]
5/26/2026 4:09:15 AMLogonError: 18456, Severity: 14, State: 38.
5/26/2026 4:09:15 AMLogonLogin failed for user 'ophelia'. Reason: Failed to open the explicitly specified database 'DMS'. [CLIENT: 10.238.99.151]
5/26/2026 4:09:15 AMLogonError: 18456, Severity: 14, State: 38.
5/26/2026 4:09:15 AMLogonLogin failed for user 'ophelia'. Reason: Failed to open the explicitly specified database 'DMS'. [CLIENT: 10.238.99.153]
5/26/2026 4:08:52 AMLogonError: 18456, Severity: 14, State: 38.
5/26/2026 4:08:52 AMLogonLogin failed for user 'ophelia'. Reason: Failed to open the explicitly specified database 'Drive'. [CLIENT: 10.238.99.150]
5/26/2026 4:08:52 AMServerThe SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SRVCLSGDEA.DigitalWare.com.co:SGDEAPRY ] for the SQL Server service. Windows return code: 0x2098, state: 20. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
5/26/2026 4:08:52 AMServerThe SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SRVCLSGDEA.DigitalWare.com.co:1633 ] for the SQL Server service. Windows return code: 0x2098, state: 20. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
5/26/2026 4:08:50 AMServerRegistry startup parameters: -d E:\DATA\MSSQL15.SGDEAPRY\MSSQL\DATA\master.mdf -e E:\DATA\MSSQL15.SGDEAPRY\MSSQL\Log\ERRORLOG -l E:\DATA\MSSQL15.SGDEAPRY\MSSQL\DATA\mastlog.ldf
5/26/2026 4:08:50 AMServerLogging SQL Server messages in file 'E:\DATA\MSSQL15.SGDEAPRY\MSSQL\Log\ERRORLOG'.

Tareas Ophelia en cola por servidor (estado Q)

ServidorIPCantidad
10.238.99.1508
10.238.99.1515
10.238.99.15311