a
    0i                    @   s   d Z ddlmZmZ ddlZddlZdZdZdZ	dZ
dd	 Zd
d Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd ZdS )u   
Módulo de consultas SQL para la actualización del Score.
CAMBIOS EN ESTA VERSIÓN:
- Manejo robusto de errores de conexión al ERP
- Validación de conexión antes de ejecutar queries
- Mensajes de error más claros y accionables
    )get_connectionget_connectionERPNu< 
SELECT
	CAST(Erp.OrderHed.OrderNum AS INT) AS OrderNum,
	
	CAST(
	CASE
		WHEN Erp.OrderDtl.SalesCatID = 'VDPE' THEN 'Matriz'
		WHEN Erp.OrderDtl.SalesCatID = 'VPEX' THEN 'Export. EUA'
		WHEN Erp.OrderDtl.SalesCatID = 'VPIG' THEN 'Mca Wichai'
		WHEN Erp.OrderDtl.SalesCatID = 'VPJD' THEN 'Distribuidores'
		WHEN Erp.OrderDtl.SalesCatID = 'VPLA' THEN 'Export. Latam.'
		WHEN Erp.OrderDtl.SalesCatID = 'VINTER' THEN 'Export. EUA'
		WHEN Erp.SalesCat.Description <> '' THEN Erp.SalesCat.Description
		WHEN Erp.OrderDtl.SalesCatID IS NULL THEN
	CASE
		WHEN Erp.OrderHed_UD.ShortChar01 <> '' THEN Erp.SalesCat.Description
		WHEN Erp.OrderHed_UD.ShortChar01 IS NULL THEN 'Sin área de venta'
	END
	END AS NVARCHAR(250)
    ) AS Departamento,
	CAST(UPPER(Erp.SalesRep.Name) AS NVARCHAR(150)) AS Vendedor,
	CAST(Erp.Customer.Name AS NVARCHAR(100)) AS Name,
	CAST(Erp.OrderHed.TotalLines AS INT) AS TotalLines,
    CAST(Erp.OrderDtl.OrderLine AS INT) AS OrderLine,
    CAST(Erp.OrderDtl.PartNum AS NVARCHAR(50)) AS PartNum,
    CAST(Erp.OrderDtl.LineDesc AS NVARCHAR(4000)) AS LineDesc,
	CAST(
		CASE
			WHEN Erp.OrderDtl.PartNum LIKE 'EP1%' THEN Erp.Part.RefCategory
			WHEN (Erp.OrderDtl.PartNum LIKE 'EC%' AND Erp.OrderDtl.LineDesc LIKE '%CONTENEDOR%') THEN ''
			WHEN Erp.OrderDtl.PartNum LIKE 'ET1%' THEN '' 
			WHEN Erp.OrderDtl.PartNum LIKE 'RB1%' THEN '' 
			WHEN Erp.OrderDtl.PartNum LIKE 'RS1%' THEN '' 
		END AS NVARCHAR(10)
	) AS Capacidad,
	CAST(
		CASE
			WHEN Erp.OrderDtl.PartNum NOT LIKE 'EP1%' THEN 'N/A'
			WHEN CHARINDEX('[V', Erp.OrderDtl.OrderComment) > 0 AND CHARINDEX('V]', Erp.OrderDtl.OrderComment) > CHARINDEX('[V', Erp.OrderDtl.OrderComment) 
			THEN 
				SUBSTRING(
					Erp.OrderDtl.OrderComment,
					CHARINDEX('[V', Erp.OrderDtl.OrderComment) + 2,
					CHARINDEX('V]', Erp.OrderDtl.OrderComment) - CHARINDEX('[V', Erp.OrderDtl.OrderComment) - 2
				)
			ELSE 'Agregar Voltaje'
		END AS NVARCHAR(50)
	) AS Voltaje,
	CAST(
		CASE
			WHEN Erp.OrderDtl.OrderComment LIKE '%[//' + '[Ee]' + '[Ss]' + '[Pp]' + '[Ee]' + '[Cc]' + '[Ii]' + '[Aa]' + '[Ll]' + '//]%' THEN 'Especial'
			ELSE 'Estandar'
		END AS NVARCHAR(50)
	) AS Tipo,
	CAST(Erp.OrderDtl.RevisionNum AS NVARCHAR(12)) AS RevisionNum,
	CAST('' AS NVARCHAR(10)) AS ProdCode,
	CAST(Erp.ProdGrup.Description AS NVARCHAR(30)) AS DecriptionProd,
	CAST(
		CASE
			WHEN Erp.OrderDtl.PartNum NOT LIKE 'EP1%' THEN 'N/A'
			WHEN Erp.OrderDtl.OrderComment LIKE '%[%' + '[Cc]' + '[Aa]' + '[Ss]' + '[Ee]' + '[Tt]' + '[Aa]' + '%]%' THEN 'Cerrada'
			ELSE 'Abierta'
		END AS NVARCHAR(50)
	) AS Caseta,
	CAST(Erp.OrderHed.OrderDate AS DATE) AS OrderDate,
    CAST(Erp.OrderHed.NeedByDate AS DATE) AS NeedByDate,
	CAST(
    CASE 
         WHEN CHARINDEX('[¿', Erp.OrderHed.OrderComment) > 0 AND CHARINDEX('¿]', Erp.OrderHed.OrderComment) > CHARINDEX('[¿', Erp.OrderHed.OrderComment)
        THEN SUBSTRING(
            Erp.OrderHed.OrderComment,
            CHARINDEX('[¿', Erp.OrderHed.OrderComment) + 2,
            CHARINDEX('¿]', Erp.OrderHed.OrderComment) - CHARINDEX('[¿', Erp.OrderHed.OrderComment) - 2
        )
        ELSE NULL
    END AS NVARCHAR(1000)
) AS FechaAnticipo,
	CAST(
    CASE 
         WHEN CHARINDEX('[¡', Erp.OrderHed.OrderComment) > 0 AND CHARINDEX('¡]', Erp.OrderHed.OrderComment) > CHARINDEX('[¡', Erp.OrderHed.OrderComment)
        THEN SUBSTRING(
            Erp.OrderHed.OrderComment,
            CHARINDEX('[¡', Erp.OrderHed.OrderComment) + 2,
            CHARINDEX('¡]', Erp.OrderHed.OrderComment) - CHARINDEX('[¡', Erp.OrderHed.OrderComment) - 2
        )
        ELSE NULL
    END AS NVARCHAR(1000)
) AS LiberacionCXP,

CAST(
    CASE 
         WHEN CHARINDEX('[?', Erp.OrderHed.OrderComment) > 0 AND CHARINDEX('?]', Erp.OrderHed.OrderComment) > CHARINDEX('[?', Erp.OrderHed.OrderComment)
        THEN SUBSTRING(
            Erp.OrderHed.OrderComment,
            CHARINDEX('[?', Erp.OrderHed.OrderComment) + 2,
            CHARINDEX('?]', Erp.OrderHed.OrderComment) - CHARINDEX('[?', Erp.OrderHed.OrderComment) - 2
        )
        ELSE NULL
    END AS NVARCHAR(2000)
) AS Terminos,
	CAST(CONCAT(Erp.OrderHed.OrderNum, Erp.OrderDtl.OrderLine) AS INT) AS 'OrderNum&Line',
	CAST(Erp.OrderDtl.ProjectID AS NVARCHAR(25)) AS ProjectID,
	CAST(Erp.JobProd.JobNum AS NVARCHAR(14)) AS 'JobNum2',
	CAST(
		CASE
			WHEN Erp.OrderHed.OpenOrder = 0 THEN 'Orden Cerrada'
			WHEN Erp.OrderHed.VoidOrder = 1 THEN 'Orden Nula'
			WHEN Erp.OrderHed.OrderHeld = 1 THEN 'Orden Retenida'
			WHEN Erp.OrderHed.CreditOverride = 1 THEN 'Orden ret. por CxC'
			WHEN Erp.OrderDtl.VoidLine = 1 THEN 'Linea de OV Nula'
			WHEN Erp.OrderDtl.OpenLine = 0 THEN 'Linea de OV Cerrada'
			ELSE 'ok'
			END AS NVARCHAR(100)
			) AS 'Revision_OV',
	CAST(
			CASE
			WHEN Erp.OrderDtl.ProjectID = '' THEN 'Linea de OV sin Proyecto'
			WHEN Erp.OrderDtl.ProjectID <> Erp.OrderHed.OrderNum THEN 'Proyecto de OV diferente a #OV'
			ELSE 'ok'
			END AS NVARCHAR(100)
			) AS 'Revision_Project',
	CAST(
			CASE
				WHEN Erp.JobProd.JobNum IS NOT NULL THEN
			CASE
				WHEN COUNT(CONCAT(Erp.JobProd.OrderNum, Erp.JobProd.OrderLine)) OVER (PARTITION BY CONCAT(Erp.JobProd.OrderNum, Erp.JobProd.OrderLine)) = 1 THEN
			CASE
				WHEN Erp.JobHead.JobComplete = 1 THEN 'Trabajo Completado'
				WHEN Erp.JobHead.JobFirm = 0 THEN 'Trabajo no Firme'
				WHEN Erp.JobHead.JobClosed = 1 THEN 'Trabajo Cerrado'
				WHEN Erp.JobHead.JobEngineered = 0 THEN 'Sin Ingeniería liberada'
				WHEN Erp.JobHead.JobReleased = 0 THEN 'Listo para liberar'
				WHEN Erp.JobHead.JobReleased = 1 THEN 'Liberada a produccion'
				WHEN Erp.JobProd.JobNum LIKE 'ERP%' THEN 'Revisar'
			END
				ELSE 'Más de un Job asignado'
				END
				ELSE 'Sin Job asignado'
				END AS NVARCHAR(100)
	) AS 'Revision_Job', 
	CAST('' AS NVARCHAR(4000) ) AS FechaVentas,
	CAST('' AS NVARCHAR(4000) ) AS  ComentarioSyC,
	CAST('' AS NVARCHAR(50) ) AS Chk,
	CAST('' AS date ) AS FechaSyC,
	CAST('' AS date ) AS FechaSimulaciones,
	CAST('' AS NVARCHAR(200) ) AS  ConsideradaPreasignacion,
	CAST('' AS NVARCHAR(200) ) AS ConsideradaMateriales,
	CAST('' AS date ) AS FechaProducción,
	CAST('' AS NVARCHAR(4000) ) AS ComentarioProducción,
	CAST(0 AS NUMERIC(18,4) ) AS AvanceProducción,
	CAST('' AS DATE ) AS FechaPlaneación,
	CAST('' AS NVARCHAR(100) ) AS EstadoFecha,
	CAST('' AS NVARCHAR(4000) ) AS ComentarioPlaneación,
	CAST('' AS DATE ) AS FechaMG,
	CAST('' AS NVARCHAR(200) ) AS FechaMat,
	CAST(0 AS NUMERIC(18,4) ) AS AvanceDeSurtimiento,
	CAST('' AS NVARCHAR(4000) ) AS MaterialSinSurtir,
	CAST('' AS NVARCHAR(100) ) AS AvisoDeTerminacion,
	CAST('' AS NVARCHAR(1000) ) AS TerminadoConFaltante,
	CAST('' AS DATE ) AS FechaActualización,
	CAST('' AS NVARCHAR(4000) ) AS MaterialFaltante,
	CAST( 
		CASE
			WHEN Erp.JobProd.JobNum IS NULL THEN NULL
			WHEN Erp.JobProd.JobNum <> '' THEN
				(SELECT CAST(Erp.JobHead.ClosedDate AS DATE) -- Fecha [Fecha cierre:]
				FROM Erp.JobHead
				WHERE Erp.JobHead.Company = 'IGSA'
				AND Erp.JobHead.JobNum = Erp.JobProd.JobNum)
		END AS DATE
	) AS FechaDeCierre,
	CAST('' AS NVARCHAR(4000) ) AS Auxiliar1,
		CAST('' AS NVARCHAR(1000) ) AS Auxiliar2,
	CAST('' AS NVARCHAR(1000) ) AS ComentarioCalidad,
	CAST((SELECT COUNT(Erp.JobMtl.PartNum) FROM Erp.JobMtl LEFT JOIN Erp.Part ON Erp.JobMtl.Company = Erp.Part.Company AND Erp.JobMtl.PartNum = Erp.Part.PartNum LEFT JOIN Erp.PartClass ON Erp.Part.Company = Erp.PartClass.Company AND Erp.Part.ClassID = Erp.PartClass.ClassID LEFT JOIN Erp.ProdGrup ON Erp.Part.Company = Erp.ProdGrup.Company AND Erp.Part.ProdCode = Erp.ProdGrup.ProdCode WHERE Erp.JobMtl.Company = 'IGSA' AND Erp.Part.ProdCode NOT LIKE 'EM%' AND Erp.Part.ProdCode NOT LIKE 'EG%' AND Erp.JobMtl.JobNum = Erp.JobProd.JobNum GROUP BY Erp.JobMtl.JobNum) AS INT) AS InsumosDemandados,
	CAST((SELECT SUM(CAST(Erp.JobMtl.IssuedComplete AS INT)) FROM Erp.JobMtl LEFT JOIN Erp.Part ON Erp.JobMtl.Company = Erp.Part.Company AND Erp.JobMtl.PartNum = Erp.Part.PartNum LEFT JOIN Erp.PartClass ON Erp.Part.Company = Erp.PartClass.Company AND Erp.Part.ClassID = Erp.PartClass.ClassID LEFT JOIN Erp.ProdGrup ON Erp.Part.Company = Erp.ProdGrup.Company AND Erp.Part.ProdCode = Erp.ProdGrup.ProdCode WHERE Erp.JobMtl.Company = 'IGSA' AND Erp.Part.ProdCode NOT LIKE 'EM%' AND Erp.Part.ProdCode NOT LIKE 'EG%' AND Erp.JobMtl.JobNum = Erp.JobProd.JobNum GROUP BY Erp.JobMtl.JobNum) AS INT)AS InsumosEmitidos,
	CAST(0 AS NUMERIC(18,4) ) AS AvaceDeEmisiones,
	CAST(CASE
		WHEN Erp.JobProd.JobNum IS NULL THEN NULL
		WHEN Erp.JobProd.JobNum <> '' THEN
			(SELECT	Erp.JobHead.JobCompletionDate FROM Erp.JobHead	WHERE Erp.JobHead.Company ='IGSA'AND Erp.JobHead.JobNum = Erp.JobProd.JobNum)
	END AS DATE
	) AS FechaDeTermino,
---------------------------------------------------------------------------------------------------------------------------------------------------- Parte 2
	Cast(Case
		When Erp.OrderDtl.PartNum not like 'EP%' then ''
		When Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A') then
				(select
					Erp.JobMtl.PartNum
				from Erp.JobMtl
				where Erp.JobMtl.Company='IGSA'
				and Erp.JobMtl.PartNum like 'EM%'
				and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
		(select
			Erp.JobMtl.PartNum
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EM%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as NVARCHAR) as PartNum_M,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then 'Ov que no necesita motor'
		When	Erp.JobProd.JobNum is null then 'Sin Job asignado'
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
				and
				(select
					Erp.JobMtl.Description
				from Erp.JobMtl
				where Erp.JobMtl.Company='IGSA'
				and Erp.JobMtl.PartNum like 'EM%'
				and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1)) is null
		then 'Ingeniería no ha cargado motor'
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='C' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=8 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='C' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=8 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='S' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=8 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='F')
				and
				(select
					Erp.JobMtl.Description
				from Erp.JobMtl
				where Erp.JobMtl.Company='IGSA'
				and Erp.JobMtl.PartNum like 'EM%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum) is null
		then 'Ingeniería no ha cargado motor'
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
		(select
			Erp.JobMtl.Description
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EM%'
		and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
		(select
			Erp.JobMtl.Description
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EM%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as nvarchar(4000)) as Description_M,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then ''
		When	Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
		(select
			Erp.PartClass.Description
		from Erp.JobMtl
		left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
		left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
		where Erp.JobMtl.Company='IGSA'
		and Erp.Part.ProdCode like 'EM%'
		and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
		(select
			Erp.PartClass.Description
		from Erp.JobMtl
		left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
		left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
		where Erp.JobMtl.Company='IGSA'
		and Erp.Part.ProdCode like 'EM%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as nvarchar) as PartClass_M,
	/*
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then ''
		When	Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
		(select
			Cast (Erp.Part.RefCategory as int)
		from Erp.JobMtl
		left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
		where Erp.JobMtl.Company='IGSA'
		and Erp.Part.ProdCode like 'EM%'
		and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
		(select
			Cast (Erp.Part.RefCategory as int)
		from Erp.JobMtl
		left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EM%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as nvarchar) as RefCategory_M,
	*/
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then ''
		When	Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
		(select
			Cast (Erp.JobMtl.QtyPer as int)
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EM%'
		and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
		(select
			Cast (Erp.JobMtl.QtyPer as int)
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EM%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as numeric (18,4)) as QtyPer_M,

	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then ''
		When	Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
		(select
			Cast (Erp.JobMtl.IssuedQty as int)
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EM%'
		and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
		(select
			Cast (Erp.JobMtl.IssuedQty as int)
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EM%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as numeric (18,4)) as IssuedQty_M,

	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then ''
		When	Erp.JobProd.JobNum is null then ''
		When
			(Case
				When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
						Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
						Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
				then
				(select
					Cast (Erp.JobMtl.IssuedQty as int)
				from Erp.JobMtl
				where Erp.JobMtl.Company='IGSA'
				and Erp.JobMtl.PartNum like 'EM%'
				and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
				else
				(select
					Cast (Erp.JobMtl.IssuedQty as int)
				from Erp.JobMtl
				where Erp.JobMtl.Company='IGSA'
				and Erp.JobMtl.PartNum like 'EM%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
			End) = 1
		then 0
		else 1
	End as numeric (18,4)) as Demandado_M,

	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then ''
		When	Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then

			(select
				Case
					When	(Select Sum (Erp.PartBin.OnhandQty) as OnhandQty from Erp.PartBin
							where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum
							) <= 0
							then 0 else
								Cast ((Select Sum (Erp.PartBin.OnhandQty) as OnhandQty from Erp.PartBin
								where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum) as int)
				End as OnhandQty
			from Erp.JobMtl
			left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
			where Erp.JobMtl.Company='IGSA'
			and Erp.JobMtl.PartNum like 'EM%'
			and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
			(select
				Case
					When	(Select Sum (Erp.PartBin.OnhandQty) as OnhandQty from Erp.PartBin
							where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum
							) <= 0
							then 0 else
								Cast ((Select Sum (Erp.PartBin.OnhandQty) as OnhandQty from Erp.PartBin
								where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum) as int)
				End as OnhandQty
			from Erp.JobMtl
			left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
			where Erp.JobMtl.Company='IGSA'
			and Erp.JobMtl.PartNum like 'EM%'
			and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as numeric (18,4)) as OnhandQty_M,

	Cast('' as int) as Preasignado_M,
	Cast('' as nvarchar) as NoSerie_M,
	Cast('' as nvarchar) as Faltante_M,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then null
		When	Erp.JobProd.JobNum is null then null
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
				(select
					Sum(Erp.PODetail.OrderQty) - Sum (Erp.PORel.ReceivedQty)
				from Erp.PODetail
				left join Erp.POHeader on Erp.PODetail.Company = Erp.POHeader.Company and Erp.PODetail.PONum = Erp.POHeader.PONum
				left join Erp.PORel on Erp.PODetail.Company = Erp.PORel.Company and Erp.PODetail.PONum = Erp.PORel.PONum and Erp.PODetail.POLine = Erp.PORel.POLine
				where Erp.PODetail.Company='IGSA'
				and Erp.POHeader.OpenOrder=1
				and Erp.POHeader.Approve=1
				and Erp.POHeader.VoidOrder=0
				and (Erp.POHeader.ApprovalStatus ='A' or Erp.POHeader.ApprovalStatus ='P')
				and Erp.PODetail.OpenLine =1
				and Erp.PODetail.VoidLine =0
				and Erp.PODetail.OrderQty - (select
												Sum (Erp.PORel.ReceivedQty) as ReceivedQty
											from Erp.PORel
											where Erp.PORel.Company='IGSA'
											and Erp.PORel.PONum=Erp.PODetail.PONUM
											and Erp.PORel.POLine=Erp.PODetail.POLine
											Group by Erp.PORel.PONum, Erp.PORel.POLine) > 0
				and Erp.PODetail.PartNum = 	(select
												Erp.JobMtl.PartNum
											from Erp.JobMtl
											where Erp.JobMtl.Company='IGSA'
											and Erp.JobMtl.PartNum like 'EM%'
											and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
				Group By Erp.PODetail.PartNum)
		else
				(select
					Sum(Erp.PODetail.OrderQty) - Sum (Erp.PORel.ReceivedQty)
				from Erp.PODetail
				left join Erp.POHeader on Erp.PODetail.Company = Erp.POHeader.Company and Erp.PODetail.PONum = Erp.POHeader.PONum
				left join Erp.PORel on Erp.PODetail.Company = Erp.PORel.Company and Erp.PODetail.PONum = Erp.PORel.PONum and Erp.PODetail.POLine = Erp.PORel.POLine
				where Erp.PODetail.Company='IGSA'
				and Erp.POHeader.OpenOrder=1
				and Erp.POHeader.Approve=1
				and Erp.POHeader.VoidOrder=0
				and (Erp.POHeader.ApprovalStatus ='A' or Erp.POHeader.ApprovalStatus ='P')
				and Erp.PODetail.OpenLine =1
				and Erp.PODetail.VoidLine =0
				and Erp.PODetail.OrderQty - (select
												Sum (Erp.PORel.ReceivedQty) as ReceivedQty
											from Erp.PORel
											where Erp.PORel.Company='IGSA'
											and Erp.PORel.PONum=Erp.PODetail.PONUM
											and Erp.PORel.POLine=Erp.PODetail.POLine
											Group by Erp.PORel.PONum, Erp.PORel.POLine) > 0
				and Erp.PODetail.PartNum = 	(select
												Erp.JobMtl.PartNum
											from Erp.JobMtl
											where Erp.JobMtl.Company='IGSA'
											and Erp.JobMtl.PartNum like 'EM%'
											and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
				Group By Erp.PODetail.PartNum)
	End as numeric (18,4)) as En_PO_M,

	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then null
		When	Erp.JobProd.JobNum is null then null
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
				stuff ((Select
						Concat (Erp.PODetail.PONUM,' / ')
					from Erp.PODetail
					left join Erp.POHeader on Erp.PODetail.Company = Erp.POHeader.Company and Erp.PODetail.PONum = Erp.POHeader.PONum
					where Erp.PODetail.Company='IGSA'
					and Erp.POHeader.OpenOrder=1
					and Erp.POHeader.Approve=1
					and Erp.POHeader.VoidOrder=0
					and (Erp.POHeader.ApprovalStatus ='A' or Erp.POHeader.ApprovalStatus ='P')
					and Erp.PODetail.OpenLine =1
					and Erp.PODetail.VoidLine =0
					and Erp.PODetail.OrderQty - (select
													Sum (Erp.PORel.ReceivedQty) as ReceivedQty
												from Erp.PORel
												where Erp.PORel.Company='IGSA'
												and Erp.PORel.PONum=Erp.PODetail.PONUM
												and Erp.PORel.POLine=Erp.PODetail.POLine
												Group by Erp.PORel.PONum, Erp.PORel.POLine) > 0
					and Erp.PODetail.PartNum = (select
													Erp.JobMtl.PartNum
												from Erp.JobMtl
												where Erp.JobMtl.Company='IGSA'
												and Erp.JobMtl.PartNum like 'EM%'
												and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
					Order By Erp.PODetail.PONUM desc
					for xml path ('')),1,0,'')
		else
				stuff ((Select
						Concat (Erp.PODetail.PONUM,' / ')
					from Erp.PODetail
					left join Erp.POHeader on Erp.PODetail.Company = Erp.POHeader.Company and Erp.PODetail.PONum = Erp.POHeader.PONum
					where Erp.PODetail.Company='IGSA'
					and Erp.POHeader.OpenOrder=1
					and Erp.POHeader.Approve=1
					and Erp.POHeader.VoidOrder=0
					and (Erp.POHeader.ApprovalStatus ='A' or Erp.POHeader.ApprovalStatus ='P')
					and Erp.PODetail.OpenLine =1
					and Erp.PODetail.VoidLine =0
					and Erp.PODetail.OrderQty - (select
													Sum (Erp.PORel.ReceivedQty) as ReceivedQty
												from Erp.PORel
												where Erp.PORel.Company='IGSA'
												and Erp.PORel.PONum=Erp.PODetail.PONUM
												and Erp.PORel.POLine=Erp.PODetail.POLine
												Group by Erp.PORel.PONum, Erp.PORel.POLine) > 0
					and Erp.PODetail.PartNum = (select
													Erp.JobMtl.PartNum
												from Erp.JobMtl
												where Erp.JobMtl.Company='IGSA'
												and Erp.JobMtl.PartNum like 'EM%'
												and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
					Order By Erp.PODetail.PONUM desc
					for xml path ('')),1,0,'')
	End as nvarchar) as NoPO_M,

	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then null
		When	Erp.JobProd.JobNum is null then null
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
				(select
					Sum (Erp.ReqDetail.OrderQty)
				from Erp.ReqDetail
				left join Erp.ReqHead on Erp.ReqDetail.Company = Erp.ReqHead.Company and Erp.ReqDetail.ReqNum = Erp.ReqHead.ReqNum
				where Erp.ReqDetail.Company ='IGSA'
				and Erp.ReqHead.OpenReq = 1
				and Erp.ReqDetail.OpenLine = 1
				and Erp.ReqDetail.PartNum = (select
												Erp.JobMtl.PartNum
											from Erp.JobMtl
											where Erp.JobMtl.Company='IGSA'
											and Erp.JobMtl.PartNum like 'EM%'
											and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
				group by Erp.ReqDetail.PartNum)
		else
				(select
					Sum (Erp.ReqDetail.OrderQty)
				from Erp.ReqDetail
				left join Erp.ReqHead on Erp.ReqDetail.Company = Erp.ReqHead.Company and Erp.ReqDetail.ReqNum = Erp.ReqHead.ReqNum
				where Erp.ReqDetail.Company ='IGSA'
				and Erp.ReqHead.OpenReq = 1
				and Erp.ReqDetail.OpenLine = 1
				and Erp.ReqDetail.PartNum = (select
												Erp.JobMtl.PartNum
											from Erp.JobMtl
											where Erp.JobMtl.Company='IGSA'
											and Erp.JobMtl.PartNum like 'EM%'
											and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
				group by Erp.ReqDetail.PartNum)
	End as numeric (18,4)) as EnRequisicion_M,
	
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then null
		When	Erp.JobProd.JobNum is null then null
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
				stuff ((select
						Concat (Erp.ReqHead.ReqNum,' / ')
					from Erp.ReqDetail
					left join Erp.ReqHead on Erp.ReqDetail.Company = Erp.ReqHead.Company and Erp.ReqDetail.ReqNum = Erp.ReqHead.ReqNum
					where Erp.ReqDetail.Company ='IGSA'
					and Erp.ReqHead.OpenReq = 1
					and Erp.ReqDetail.OpenLine = 1
					and Erp.ReqDetail.PartNum = (select
													Erp.JobMtl.PartNum
												from Erp.JobMtl
												where Erp.JobMtl.Company='IGSA'
												and Erp.JobMtl.PartNum like 'EM%'
												and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
					Order by Erp.ReqHead.ReqNum desc
					for xml path ('')),1,0,'')
		else
				stuff ((select
						Concat (Erp.ReqHead.ReqNum,' / ')
					from Erp.ReqDetail
					left join Erp.ReqHead on Erp.ReqDetail.Company = Erp.ReqHead.Company and Erp.ReqDetail.ReqNum = Erp.ReqHead.ReqNum
					where Erp.ReqDetail.Company ='IGSA'
					and Erp.ReqHead.OpenReq = 1
					and Erp.ReqDetail.OpenLine = 1
					and Erp.ReqDetail.PartNum = (select
													Erp.JobMtl.PartNum
												from Erp.JobMtl
												where Erp.JobMtl.Company='IGSA'
												and Erp.JobMtl.PartNum like 'EM%'
												and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
					Order by Erp.ReqHead.ReqNum desc
					for xml path ('')),1,0,'')
	End as nvarchar) as NoRequisicion_M,
	Cast('' as nvarchar) as Alternativa_M,
	Cast('' as nvarchar) as En_PO_Altern_M,
	Cast('' as nvarchar) as NoPO_Alern_M,
	Cast('' as nvarchar) as Req_M,
	Cast('' as nvarchar) as Comment_M,
	Cast('' as Date) as FechaReq_M,
	Cast('' as Int) as PO_M,
	Cast('' as Int) as Cantidad_Pedida_M,
	Cast('' as nvarchar) as Estatus_M,
	Cast('' as Date) as Fecha_Llegada_M,
	
----------------------------------------------------------------------------------------------------------------------------------------------------------------- Parte 3
	Cast(Case
		When Erp.OrderDtl.PartNum not like 'EP%' then ''
		When Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A') then
				(select
					Erp.JobMtl.PartNum
				from Erp.JobMtl
				where Erp.JobMtl.Company='IGSA'
				and Erp.JobMtl.PartNum like 'EG%'
				and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
		(select
			Erp.JobMtl.PartNum
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EG%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as nvarchar) as PartNum_G,

	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then 'Ov que no necesita motor'
		When	Erp.JobProd.JobNum is null then 'Sin Job asignado'
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
				and
				(select
					Erp.JobMtl.Description
				from Erp.JobMtl
				where Erp.JobMtl.Company='IGSA'
				and Erp.JobMtl.PartNum like 'EG%'
				and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1)) is null
		then 'Ingeniería no ha cargado motor'
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='C' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=8 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='C' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=8 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='S' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=8 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='F')
				and
				(select
					Erp.JobMtl.Description
				from Erp.JobMtl
				where Erp.JobMtl.Company='IGSA'
				and Erp.JobMtl.PartNum like 'EG%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum) is null
		then 'Ingeniería no ha cargado motor'
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
		(select
			Erp.JobMtl.Description
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EG%'
		and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
		(select
			Erp.JobMtl.Description
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EG%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as nvarchar(4000)) as Description_G,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then ''
		When	Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
		(select
			Erp.PartClass.Description
		from Erp.JobMtl
		left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
		left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
		where Erp.JobMtl.Company='IGSA'
		and Erp.Part.ProdCode like 'EG%'
		and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
		(select
			Erp.PartClass.Description
		from Erp.JobMtl
		left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
		left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
		where Erp.JobMtl.Company='IGSA'
		and Erp.Part.ProdCode like 'EG%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as nvarchar) as PartClass_G,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then ''
		When	Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
		(select
			Cast (Erp.Part.RefCategory as int)
		from Erp.JobMtl
		left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
		where Erp.JobMtl.Company='IGSA'
		and Erp.Part.ProdCode like 'EG%'
		and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
		(select
			Cast (Erp.Part.RefCategory as int)
		from Erp.JobMtl
		left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EG%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as nvarchar) as RefCategory_G,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then ''
		When	Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
		(select
			Cast (Erp.JobMtl.QtyPer as int)
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EG%'
		and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
		(select
			Cast (Erp.JobMtl.QtyPer as int)
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EG%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as numeric (18,4)) as QtyPer_G,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then ''
		When	Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
		(select
			Cast (Erp.JobMtl.IssuedQty as int)
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EG%'
		and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
		(select
			Cast (Erp.JobMtl.IssuedQty as int)
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EG%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as numeric (18,4)) as IssuedQty_G,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then ''
		When	Erp.JobProd.JobNum is null then ''
		When
			(Case
				When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
						Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
						Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
				then
				(select
					Cast (Erp.JobMtl.IssuedQty as int)
				from Erp.JobMtl
				where Erp.JobMtl.Company='IGSA'
				and Erp.JobMtl.PartNum like 'EG%'
				and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
				else
				(select
					Cast (Erp.JobMtl.IssuedQty as int)
				from Erp.JobMtl
				where Erp.JobMtl.Company='IGSA'
				and Erp.JobMtl.PartNum like 'EG%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
			End) = 1
		then 0
		else 1
	End as numeric (18,4)) as Demandado_G,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then ''
		When	Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then

			(select
				Case
					When	(Select Sum (Erp.PartBin.OnhandQty) as OnhandQty from Erp.PartBin
							where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum
							) <= 0
							then 0 else
								Cast ((Select Sum (Erp.PartBin.OnhandQty) as OnhandQty from Erp.PartBin
								where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum) as int)
				End as OnhandQty
			from Erp.JobMtl
			left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
			where Erp.JobMtl.Company='IGSA'
			and Erp.JobMtl.PartNum like 'EG%'
			and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
		else
			(select
				Case
					When	(Select Sum (Erp.PartBin.OnhandQty) as OnhandQty from Erp.PartBin
							where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum
							) <= 0
							then 0 else
								Cast ((Select Sum (Erp.PartBin.OnhandQty) as OnhandQty from Erp.PartBin
								where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum) as int)
				End as OnhandQty
			from Erp.JobMtl
			left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
			where Erp.JobMtl.Company='IGSA'
			and Erp.JobMtl.PartNum like 'EG%'
			and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End as numeric (18,4)) as OnhandQty_G,
	Cast('' as int) as Preasignado_G,
	Cast('' as nvarchar) as NoSerie_G,
	Cast('' as nvarchar) as Faltante_G,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then null
		When	Erp.JobProd.JobNum is null then null
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
				(select
					Sum(Erp.PODetail.OrderQty) - Sum (Erp.PORel.ReceivedQty)
				from Erp.PODetail
				left join Erp.POHeader on Erp.PODetail.Company = Erp.POHeader.Company and Erp.PODetail.PONum = Erp.POHeader.PONum
				left join Erp.PORel on Erp.PODetail.Company = Erp.PORel.Company and Erp.PODetail.PONum = Erp.PORel.PONum and Erp.PODetail.POLine = Erp.PORel.POLine
				where Erp.PODetail.Company='IGSA'
				and Erp.POHeader.OpenOrder=1
				and Erp.POHeader.Approve=1
				and Erp.POHeader.VoidOrder=0
				and (Erp.POHeader.ApprovalStatus ='A' or Erp.POHeader.ApprovalStatus ='P')
				and Erp.PODetail.OpenLine =1
				and Erp.PODetail.VoidLine =0
				and Erp.PODetail.OrderQty - (select
												Sum (Erp.PORel.ReceivedQty) as ReceivedQty
											from Erp.PORel
											where Erp.PORel.Company='IGSA'
											and Erp.PORel.PONum=Erp.PODetail.PONUM
											and Erp.PORel.POLine=Erp.PODetail.POLine
											Group by Erp.PORel.PONum, Erp.PORel.POLine) > 0
				and Erp.PODetail.PartNum = 	(select
												Erp.JobMtl.PartNum
											from Erp.JobMtl
											where Erp.JobMtl.Company='IGSA'
											and Erp.JobMtl.PartNum like 'EG%'
											and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
				Group By Erp.PODetail.PartNum)
		else
				(select
					Sum(Erp.PODetail.OrderQty) - Sum (Erp.PORel.ReceivedQty)
				from Erp.PODetail
				left join Erp.POHeader on Erp.PODetail.Company = Erp.POHeader.Company and Erp.PODetail.PONum = Erp.POHeader.PONum
				left join Erp.PORel on Erp.PODetail.Company = Erp.PORel.Company and Erp.PODetail.PONum = Erp.PORel.PONum and Erp.PODetail.POLine = Erp.PORel.POLine
				where Erp.PODetail.Company='IGSA'
				and Erp.POHeader.OpenOrder=1
				and Erp.POHeader.Approve=1
				and Erp.POHeader.VoidOrder=0
				and (Erp.POHeader.ApprovalStatus ='A' or Erp.POHeader.ApprovalStatus ='P')
				and Erp.PODetail.OpenLine =1
				and Erp.PODetail.VoidLine =0
				and Erp.PODetail.OrderQty - (select
												Sum (Erp.PORel.ReceivedQty) as ReceivedQty
											from Erp.PORel
											where Erp.PORel.Company='IGSA'
											and Erp.PORel.PONum=Erp.PODetail.PONUM
											and Erp.PORel.POLine=Erp.PODetail.POLine
											Group by Erp.PORel.PONum, Erp.PORel.POLine) > 0
				and Erp.PODetail.PartNum = 	(select
												Erp.JobMtl.PartNum
											from Erp.JobMtl
											where Erp.JobMtl.Company='IGSA'
											and Erp.JobMtl.PartNum like 'EG%'
											and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
				Group By Erp.PODetail.PartNum)
	End as numeric (18,4)) as En_PO_G,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then null
		When	Erp.JobProd.JobNum is null then null
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
				stuff ((Select
						Concat (Erp.PODetail.PONUM,' / ')
					from Erp.PODetail
					left join Erp.POHeader on Erp.PODetail.Company = Erp.POHeader.Company and Erp.PODetail.PONum = Erp.POHeader.PONum
					where Erp.PODetail.Company='IGSA'
					and Erp.POHeader.OpenOrder=1
					and Erp.POHeader.Approve=1
					and Erp.POHeader.VoidOrder=0
					and (Erp.POHeader.ApprovalStatus ='A' or Erp.POHeader.ApprovalStatus ='P')
					and Erp.PODetail.OpenLine =1
					and Erp.PODetail.VoidLine =0
					and Erp.PODetail.OrderQty - (select
													Sum (Erp.PORel.ReceivedQty) as ReceivedQty
												from Erp.PORel
												where Erp.PORel.Company='IGSA'
												and Erp.PORel.PONum=Erp.PODetail.PONUM
												and Erp.PORel.POLine=Erp.PODetail.POLine
												Group by Erp.PORel.PONum, Erp.PORel.POLine) > 0
					and Erp.PODetail.PartNum = (select
													Erp.JobMtl.PartNum
												from Erp.JobMtl
												where Erp.JobMtl.Company='IGSA'
												and Erp.JobMtl.PartNum like 'EG%'
												and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
					Order By Erp.PODetail.PONUM desc
					for xml path ('')),1,0,'')
		else
				stuff ((Select
						Concat (Erp.PODetail.PONUM,' / ')
					from Erp.PODetail
					left join Erp.POHeader on Erp.PODetail.Company = Erp.POHeader.Company and Erp.PODetail.PONum = Erp.POHeader.PONum
					where Erp.PODetail.Company='IGSA'
					and Erp.POHeader.OpenOrder=1
					and Erp.POHeader.Approve=1
					and Erp.POHeader.VoidOrder=0
					and (Erp.POHeader.ApprovalStatus ='A' or Erp.POHeader.ApprovalStatus ='P')
					and Erp.PODetail.OpenLine =1
					and Erp.PODetail.VoidLine =0
					and Erp.PODetail.OrderQty - (select
													Sum (Erp.PORel.ReceivedQty) as ReceivedQty
												from Erp.PORel
												where Erp.PORel.Company='IGSA'
												and Erp.PORel.PONum=Erp.PODetail.PONUM
												and Erp.PORel.POLine=Erp.PODetail.POLine
												Group by Erp.PORel.PONum, Erp.PORel.POLine) > 0
					and Erp.PODetail.PartNum = (select
													Erp.JobMtl.PartNum
												from Erp.JobMtl
												where Erp.JobMtl.Company='IGSA'
												and Erp.JobMtl.PartNum like 'EG%'
												and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
					Order By Erp.PODetail.PONUM desc
					for xml path ('')),1,0,'')
	End as nvarchar) as NoPO_G,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then null
		When	Erp.JobProd.JobNum is null then null
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
				(select
					Sum (Erp.ReqDetail.OrderQty)
				from Erp.ReqDetail
				left join Erp.ReqHead on Erp.ReqDetail.Company = Erp.ReqHead.Company and Erp.ReqDetail.ReqNum = Erp.ReqHead.ReqNum
				where Erp.ReqDetail.Company ='IGSA'
				and Erp.ReqHead.OpenReq = 1
				and Erp.ReqDetail.OpenLine = 1
				and Erp.ReqDetail.PartNum = (select
												Erp.JobMtl.PartNum
											from Erp.JobMtl
											where Erp.JobMtl.Company='IGSA'
											and Erp.JobMtl.PartNum like 'EG%'
											and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
				group by Erp.ReqDetail.PartNum)
		else
				(select
					Sum (Erp.ReqDetail.OrderQty)
				from Erp.ReqDetail
				left join Erp.ReqHead on Erp.ReqDetail.Company = Erp.ReqHead.Company and Erp.ReqDetail.ReqNum = Erp.ReqHead.ReqNum
				where Erp.ReqDetail.Company ='IGSA'
				and Erp.ReqHead.OpenReq = 1
				and Erp.ReqDetail.OpenLine = 1
				and Erp.ReqDetail.PartNum = (select
												Erp.JobMtl.PartNum
											from Erp.JobMtl
											where Erp.JobMtl.Company='IGSA'
											and Erp.JobMtl.PartNum like 'EG%'
											and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
				group by Erp.ReqDetail.PartNum)
	End as numeric (18,4)) as EnRequisicion_G,
	Cast(Case
		When	Erp.OrderDtl.PartNum not like 'EP%' then null
		When	Erp.JobProd.JobNum is null then null
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A')
		then
				stuff ((select
						Concat (Erp.ReqHead.ReqNum,' / ')
					from Erp.ReqDetail
					left join Erp.ReqHead on Erp.ReqDetail.Company = Erp.ReqHead.Company and Erp.ReqDetail.ReqNum = Erp.ReqHead.ReqNum
					where Erp.ReqDetail.Company ='IGSA'
					and Erp.ReqHead.OpenReq = 1
					and Erp.ReqDetail.OpenLine = 1
					and Erp.ReqDetail.PartNum = (select
													Erp.JobMtl.PartNum
												from Erp.JobMtl
												where Erp.JobMtl.Company='IGSA'
												and Erp.JobMtl.PartNum like 'EG%'
												and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1))
					Order by Erp.ReqHead.ReqNum desc
					for xml path ('')),1,0,'')
		else
				stuff ((select
						Concat (Erp.ReqHead.ReqNum,' / ')
					from Erp.ReqDetail
					left join Erp.ReqHead on Erp.ReqDetail.Company = Erp.ReqHead.Company and Erp.ReqDetail.ReqNum = Erp.ReqHead.ReqNum
					where Erp.ReqDetail.Company ='IGSA'
					and Erp.ReqHead.OpenReq = 1
					and Erp.ReqDetail.OpenLine = 1
					and Erp.ReqDetail.PartNum = (select
													Erp.JobMtl.PartNum
												from Erp.JobMtl
												where Erp.JobMtl.Company='IGSA'
												and Erp.JobMtl.PartNum like 'EG%'
												and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
					Order by Erp.ReqHead.ReqNum desc
					for xml path ('')),1,0,'')
	End as nvarchar) as NoRequisicion_G,
	Cast('' as nvarchar) as Alternativa_G,
	Cast('' as nvarchar) as En_PO_Altern_G,
	Cast('' as nvarchar) as NoPO_Alern_G,
	Cast('' as nvarchar) as Req_G,
	Cast('' as nvarchar) as Comment_G,
	Cast('' as Date) as FechaReq_G,
	Cast('' as int) as PO_G,
	Cast('' as int) as Cantidad_Pedida_G,
	Cast('' as nvarchar) as Estatus_G,
	Cast('' as Date) as Fecha_Llegada_G,
-------------------------------------------------------------------------------------------------------------------- Parte 4
	Case
		When Erp.JobProd.JobNum is null then ''
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) is null
				then	'n/a'
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) = 1
				then	(select
							Erp.JobMtl.PartNum
						from Erp.JobMtl
						left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
						left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
						left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
						where Erp.JobMtl.Company='IGSA'
						and Erp.Part.ProdCode like 'ET1%'
						and Erp.JobMtl.Description like '%TRANSFERENCIA%'
						and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) > 1
				then	'Varios'
	End as PartNum_T,
	CAST(Left(
	Case
		When Erp.JobProd.JobNum is null then 'Sin Job asignado'
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) is null
				then	'Sin transferencia cargada por Ingeniería'
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) = 1
				then	(select
							Erp.JobMtl.Description
						from Erp.JobMtl
						left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
						left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
						left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
						where Erp.JobMtl.Company='IGSA'
						and Erp.Part.ProdCode like 'ET1%'
						and Erp.JobMtl.Description like '%TRANSFERENCIA%'
						and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) > 1
				then	'Varios'
	End,3990) as NVARCHAR(3990))as Description_T,
	Case
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) is null
				then	''
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) = 1
				then	(select
							Erp.PartClass.Description
						from Erp.JobMtl
						left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
						left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
						left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
						where Erp.JobMtl.Company='IGSA'
						and Erp.Part.ProdCode like 'ET1%'
						and Erp.JobMtl.Description like '%TRANSFERENCIA%'
						and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) > 1
				then	'Varios'
	End as PartClass_T,
	Case
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) is null
				then	''
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) = 1
				then	(select
							Erp.Part.RefCategory
						from Erp.JobMtl
						left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
						left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
						left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
						where Erp.JobMtl.Company='IGSA'
						and Erp.Part.ProdCode like 'ET1%'
						and Erp.JobMtl.Description like '%TRANSFERENCIA%'
						and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) > 1
				then	''
	End as RefCategory_T,
	Case
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) is null
				then	''
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) >= 1
				then	(select
							Sum (Cast (Erp.JobMtl.QtyPer as int))
						from Erp.JobMtl
						left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
						left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
						left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
						where Erp.JobMtl.Company='IGSA'
						and Erp.Part.ProdCode like 'ET1%'
						and Erp.JobMtl.Description like '%TRANSFERENCIA%'
						and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
						group by Erp.JobMtl.JobNum)
	End as QtyPer_T,
	Case
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) is null
				then	''
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) >= 1
				then	(select
							Sum (Cast (Erp.JobMtl.IssuedQty as int))
						from Erp.JobMtl
						left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
						left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
						left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
						where Erp.JobMtl.Company='IGSA'
						and Erp.Part.ProdCode like 'ET1%'
						and Erp.JobMtl.Description like '%TRANSFERENCIA%'
						and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
						group by Erp.JobMtl.JobNum)
	End as IssuedQty_T,
	Case
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) is null
				then	''
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) = 1
				then	(select
							Case
								When	(Select Sum (Cast (Erp.PartBin.OnhandQty as int)) as OnhandQty from Erp.PartBin
										where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum
										) <= 0
										then 0

								When 	(Select Sum (Cast (Erp.PartBin.OnhandQty as int)) as OnhandQty from Erp.PartBin
										where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum
										) > 0
										then	(Select Sum (Cast (Erp.PartBin.OnhandQty as int)) as OnhandQty from Erp.PartBin
												where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum)
								When 	(Select Sum (Cast (Erp.PartBin.OnhandQty as int)) as OnhandQty from Erp.PartBin
										where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum
										) is null
										then 0
							End as OnhandQty
						from Erp.JobMtl
						left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
						left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
						left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
						where Erp.JobMtl.Company='IGSA'
						and Erp.Part.ProdCode like 'ET1%'
						and Erp.JobMtl.Description like '%TRANSFERENCIA%'
						and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
		when	(select
					Count (Erp.JobMtl.PartNum) as CantidadDeTranferencias
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'ET1%'
				and Erp.JobMtl.Description like '%TRANSFERENCIA%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
				group by Erp.JobMtl.JobNum) > 1
				then	'0'
	End as OnhandQty_T,
	(select
		Erp.JobMtl.PartNum
	from Erp.JobMtl
	left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
	left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
	left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
	where Erp.JobMtl.Company='IGSA'
	and Erp.Part.ProdCode like 'RR1%'
	and Erp.JobMtl.JobNum = Erp.JobProd.JobNum) as PartNum_R,
	CAST(Left(
	Case
		When Erp.JobProd.JobNum is null then 'Sin Job asignado'
		When (select
					Erp.JobMtl.Description
				from Erp.JobMtl
				left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
				left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
				left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
				where Erp.JobMtl.Company='IGSA'
				and Erp.Part.ProdCode like 'RR1%'
				and Erp.JobMtl.JobNum = Erp.JobProd.JobNum) is null then 'Sin Radiador cargado por Ingeniería'
		else
		(select
			Erp.JobMtl.Description
		from Erp.JobMtl
		left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
		left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
		left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
		where Erp.JobMtl.Company='IGSA'
		and Erp.Part.ProdCode like 'RR1%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum)
	End,3990) AS NVARCHAR(3990))as Description_R,
	(select
		Erp.PartClass.Description
	from Erp.JobMtl
	left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
	left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
	left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
	where Erp.JobMtl.Company='IGSA'
	and Erp.Part.ProdCode like 'RR1%'
	and Erp.JobMtl.JobNum = Erp.JobProd.JobNum) as PartClass_R,
	(select
		Cast (Erp.Part.RefCategory as int)
	from Erp.JobMtl
	left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
	left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
	left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
	where Erp.JobMtl.Company='IGSA'
	and Erp.Part.ProdCode like 'RR1%'
	and Erp.JobMtl.JobNum = Erp.JobProd.JobNum) as RefCategory_R,
	(select
		Cast (Erp.JobMtl.QtyPer as int)
	from Erp.JobMtl
	left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
	left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
	left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
	where Erp.JobMtl.Company='IGSA'
	and Erp.Part.ProdCode like 'RR1%'
	and Erp.JobMtl.JobNum = Erp.JobProd.JobNum) as QtyPer_R,
	(select
		Cast (Erp.JobMtl.IssuedQty as int)
	from Erp.JobMtl
	left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
	left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
	left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
	where Erp.JobMtl.Company='IGSA'
	and Erp.Part.ProdCode like 'RR1%'
	and Erp.JobMtl.JobNum = Erp.JobProd.JobNum) as IssuedQty_R,
	(select
		Case
			When	(Select Sum (Erp.PartBin.OnhandQty) as OnhandQty from Erp.PartBin
					where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum
					) <= 0
					then 0 else
						Cast ((Select Sum (Erp.PartBin.OnhandQty) as OnhandQty from Erp.PartBin
						where Erp.PartBin.Company = 'IGSA' and Erp.PartBin.PartNum = Erp.Part.PartNum group by Erp.PartBin.PartNum) as int)
		End as OnhandQty
	from Erp.JobMtl
	left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
	left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
	left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
	where Erp.JobMtl.Company='IGSA'
	and Erp.Part.ProdCode like 'RR1%'
	and Erp.JobMtl.JobNum = Erp.JobProd.JobNum) as OnhandQty_R,
	CAST (Left(Erp.OrderDtl.OrderComment ,3990)AS nvarchar(3990))as ComentarioLINE,


-- Extrae el texto después del número (ej. Server Farm)
CAST(
    CASE 
        WHEN Erp.OrderDtl.OrderComment LIKE '%~%~%~%' 
        THEN 
            SUBSTRING(
                Erp.OrderDtl.OrderComment,
                CHARINDEX('~', Erp.OrderDtl.OrderComment, CHARINDEX('~', Erp.OrderDtl.OrderComment) + 1) + 1,
                CHARINDEX('~', Erp.OrderDtl.OrderComment, CHARINDEX('~', Erp.OrderDtl.OrderComment, CHARINDEX('~', Erp.OrderDtl.OrderComment) + 1) + 1) 
                - CHARINDEX('~', Erp.OrderDtl.OrderComment, CHARINDEX('~', Erp.OrderDtl.OrderComment) + 1) - 1
            )
        ELSE NULL
    END AS NVARCHAR(1000)
) AS OvEEUU,

CAST(
    CASE 
        WHEN Erp.OrderDtl.OrderComment LIKE '%~%~%~%~%' 
        THEN 
            SUBSTRING(
                Erp.OrderDtl.OrderComment,
                CHARINDEX('~', Erp.OrderDtl.OrderComment, CHARINDEX('~', Erp.OrderDtl.OrderComment, CHARINDEX('~', Erp.OrderDtl.OrderComment) + 1) + 1) + 1,
                CHARINDEX('~', Erp.OrderDtl.OrderComment, CHARINDEX('~', Erp.OrderDtl.OrderComment, CHARINDEX('~', Erp.OrderDtl.OrderComment, CHARINDEX('~', Erp.OrderDtl.OrderComment) + 1) + 1) + 1)
                - CHARINDEX('~', Erp.OrderDtl.OrderComment, CHARINDEX('~', Erp.OrderDtl.OrderComment, CHARINDEX('~', Erp.OrderDtl.OrderComment) + 1) + 1) - 1
            )
        ELSE NULL
    END AS NVARCHAR(1000)
) AS ClienteEEUU

--Erp.OrderHed.OrderComment as ComentarioPrice,


FROM Erp.OrderHed
INNER JOIN Erp.OrderHed_UD ON Erp.OrderHed.SysRowID = Erp.OrderHed_UD.ForeignSysRowID
RIGHT JOIN Erp.OrderDtl ON Erp.OrderHed.Company = Erp.OrderDtl.Company AND Erp.OrderHed.OrderNum = Erp.OrderDtl.OrderNum
LEFT JOIN Erp.SalesCat ON Erp.OrderDtl.Company = Erp.SalesCat.Company AND Erp.OrderDtl.SalesCatID = Erp.SalesCat.SalesCatID AND Erp.OrderHed_UD.ShortChar01 = Erp.SalesCat.SalesCatID
LEFT JOIN Erp.Customer ON Erp.OrderHed.Company = Erp.Customer.Company AND Erp.OrderHed.CustNum = Erp.Customer.CustNum
LEFT JOIN Erp.ProdGrup ON Erp.OrderDtl.Company = Erp.ProdGrup.Company AND Erp.OrderDtl.ProdCode = Erp.ProdGrup.ProdCode
LEFT JOIN Erp.SalesRep ON Erp.OrderHed.Company = Erp.SalesRep.Company AND Erp.OrderHed.SalesRepList = Erp.SalesRep.SalesRepCode
LEFT JOIN Erp.SerialNo ON Erp.OrderDtl.Company = Erp.SerialNo.Company AND Erp.OrderDtl.OrderNum = Erp.SerialNo.OrderNum AND Erp.OrderDtl.OrderLine = Erp.SerialNo.OrderLine
LEFT JOIN Erp.JobProd ON Erp.OrderDtl.Company = Erp.JobProd.Company AND Erp.OrderDtl.OrderNum = Erp.JobProd.OrderNum AND Erp.OrderDtl.OrderLine = Erp.JobProd.OrderLine
LEFT JOIN Erp.JobHead ON Erp.JobProd.Company = Erp.JobHead.Company AND Erp.JobProd.JobNum = Erp.JobHead.JobNum
LEFT JOIN Erp.Part ON Erp.JobProd.Company = Erp.Part.Company AND Erp.JobProd.PartNum = Erp.Part.PartNum
where Erp.OrderHed.Company='IGSA'
and Erp.OrderHed.OpenOrder='1'
and Erp.OrderHed.VoidOrder='0'
and Erp.OrderHed.OrderHeld='0'
--and Erp.OrderHed.OrderNum = '125628'
--and Erp.OrderHed.CreditOverride='0' --No usamos filtros por que son OV nuevas que no han pagado anticipo por lo que no se ha liberado por credito y cobranza para su fabricación
and Erp.OrderDtl.VoidLine ='0'
and Erp.OrderDtl.OpenLine='1'
and (Erp.OrderDtl.PartNum like 'EP1%'
or (Erp.OrderDtl.PartNum like 'EC%' and Erp.OrderDtl.LineDesc like '%CONTENEDOR%')
or (Erp.OrderDtl.PartNum like 'EC1%')
or Erp.OrderDtl.PartNum like 'ET1%'
or Erp.OrderDtl.PartNum like 'RB1%'
or Erp.OrderDtl.PartNum like 'RS1%')
and ((Erp.SalesCat.SalesCatID <> 'VDAE')
or (Erp.OrderDtl.SalesCatID <> 'VDPR'))
order by Erp.OrderHed.OrderNum desc



--================================================================================================================
-- =================================================================================================================

--Script aurik

SELECT
	CAST(Erp.OrderHed.OrderNum AS INT) AS OrderNum,
	CAST(Erp.OrderHed.TotalLines AS INT) AS TotalLines,
    CAST(Erp.OrderDtl.OrderLine AS INT) AS OrderLine,
	CAST(CONCAT(Erp.OrderHed.OrderNum, Erp.OrderDtl.OrderLine) AS INT) AS 'OrderNum&Line',
	CAST(Erp.OrderDtl.ProjectID AS NVARCHAR(25)) AS ProjectID,
	CAST(Erp.JobProd.JobNum AS NVARCHAR(14)) AS 'JobNum2',
	CAST(
			CASE
				WHEN Erp.JobProd.JobNum IS NOT NULL THEN
			CASE
				WHEN COUNT(CONCAT(Erp.JobProd.OrderNum, Erp.JobProd.OrderLine)) OVER (PARTITION BY CONCAT(Erp.JobProd.OrderNum, Erp.JobProd.OrderLine)) = 1 THEN
			CASE
				WHEN Erp.JobHead.JobComplete = 1 THEN 'Trabajo Completado'
				WHEN Erp.JobHead.JobFirm = 0 THEN 'Trabajo no Firme'
				WHEN Erp.JobHead.JobClosed = 1 THEN 'Trabajo Cerrado'
				WHEN Erp.JobHead.JobEngineered = 0 THEN 'Sin Ingeniería liberada'
				WHEN Erp.JobHead.JobReleased = 0 THEN 'Listo para liberar'
				WHEN Erp.JobHead.JobReleased = 1 THEN 'Liberada a produccion'
				WHEN Erp.JobProd.JobNum LIKE 'ERP%' THEN 'Revisar'
			END
				ELSE 'Más de un Job asignado'
				END
				ELSE 'Sin Job asignado'
				END AS NVARCHAR(100)
	) AS 'Revision_Job',
---------------------------------------------------------------------------------------------------------------------------------------------------- Parte 2
	Cast(Case
		When Erp.OrderDtl.PartNum not like 'EP%' then ''
		When Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A') then
				(select
					Count(Erp.JobMtl.PartNum)
				from Erp.JobMtl
				where Erp.JobMtl.Company='IGSA'
				and Erp.JobMtl.PartNum like 'EM%'
				and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1)
				Group by Erp.JobMtl.JobNum
				)
		else
		(select
			Count(Erp.JobMtl.PartNum)
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EM%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
		Group by Erp.JobMtl.JobNum
		)
	End as NVARCHAR) as CantidadDeMotores,

	Cast(Case
		When Erp.OrderDtl.PartNum not like 'EP%' then ''
		When Erp.JobProd.JobNum is null then ''
		When	(Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=11 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=10 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A' or
				Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))=9 and Right (LTRIM(RTRIM(Erp.JobProd.JobNum)),1)='A') then
				(select
					Count(Erp.JobMtl.PartNum)
				from Erp.JobMtl
				where Erp.JobMtl.Company='IGSA'
				and Erp.JobMtl.PartNum like 'EG%'
				and Erp.JobMtl.JobNum = Left (LTRIM(RTRIM(Erp.JobProd.JobNum)),Len(LTRIM(RTRIM(Erp.JobProd.JobNum)))-1)
				Group by Erp.JobMtl.JobNum
				)
		else
		(select
			Count(Erp.JobMtl.PartNum)
		from Erp.JobMtl
		where Erp.JobMtl.Company='IGSA'
		and Erp.JobMtl.PartNum like 'EG%'
		and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
		Group by Erp.JobMtl.JobNum
		)
	End as nvarchar) as CantidadDeGeneradores,

	(select
		Count(Erp.JobMtl.PartNum)
	from Erp.JobMtl
	left join Erp.Part on Erp.JobMtl.Company = Erp.Part.Company and Erp.JobMtl.PartNum = Erp.Part.PartNum
	left join Erp.PartClass on Erp.Part.Company = Erp.PartClass.Company and Erp.Part.ClassID = Erp.PartClass.ClassID
	left join Erp.ProdGrup on Erp.Part.Company = Erp.ProdGrup.Company and Erp.Part.ProdCode = Erp.ProdGrup.ProdCode
	where Erp.JobMtl.Company='IGSA'
	and Erp.Part.ProdCode like 'RR1%'
	and Erp.JobMtl.JobNum = Erp.JobProd.JobNum
	Group by Erp.JobMtl.JobNum
	) as PartNum_R

FROM Erp.OrderHed
INNER JOIN Erp.OrderHed_UD ON Erp.OrderHed.SysRowID = Erp.OrderHed_UD.ForeignSysRowID
RIGHT JOIN Erp.OrderDtl ON Erp.OrderHed.Company = Erp.OrderDtl.Company AND Erp.OrderHed.OrderNum = Erp.OrderDtl.OrderNum
LEFT JOIN Erp.SalesCat ON Erp.OrderDtl.Company = Erp.SalesCat.Company AND Erp.OrderDtl.SalesCatID = Erp.SalesCat.SalesCatID AND Erp.OrderHed_UD.ShortChar01 = Erp.SalesCat.SalesCatID
LEFT JOIN Erp.Customer ON Erp.OrderHed.Company = Erp.Customer.Company AND Erp.OrderHed.CustNum = Erp.Customer.CustNum
LEFT JOIN Erp.ProdGrup ON Erp.OrderDtl.Company = Erp.ProdGrup.Company AND Erp.OrderDtl.ProdCode = Erp.ProdGrup.ProdCode
LEFT JOIN Erp.SalesRep ON Erp.OrderHed.Company = Erp.SalesRep.Company AND Erp.OrderHed.SalesRepList = Erp.SalesRep.SalesRepCode
LEFT JOIN Erp.SerialNo ON Erp.OrderDtl.Company = Erp.SerialNo.Company AND Erp.OrderDtl.OrderNum = Erp.SerialNo.OrderNum AND Erp.OrderDtl.OrderLine = Erp.SerialNo.OrderLine
LEFT JOIN Erp.JobProd ON Erp.OrderDtl.Company = Erp.JobProd.Company AND Erp.OrderDtl.OrderNum = Erp.JobProd.OrderNum AND Erp.OrderDtl.OrderLine = Erp.JobProd.OrderLine
LEFT JOIN Erp.JobHead ON Erp.JobProd.Company = Erp.JobHead.Company AND Erp.JobProd.JobNum = Erp.JobHead.JobNum
LEFT JOIN Erp.Part ON Erp.JobProd.Company = Erp.Part.Company AND Erp.JobProd.PartNum = Erp.Part.PartNum
where Erp.OrderHed.Company='IGSA'
and Erp.OrderHed.OpenOrder='1'
and Erp.OrderHed.VoidOrder='0'
and Erp.OrderHed.OrderHeld='0'
--and Erp.OrderHed.OrderNum = '125628'
--and Erp.OrderHed.CreditOverride='0' --No usamos filtros por que son OV nuevas que no han pagado anticipo por lo que no se ha liberado por credito y cobranza para su fabricación
and Erp.OrderDtl.VoidLine ='0'
and Erp.OrderDtl.OpenLine='1'
and (Erp.OrderDtl.PartNum like 'EP1%'
or (Erp.OrderDtl.PartNum like 'EC%' and Erp.OrderDtl.LineDesc like '%CONTENEDOR%')
or (Erp.OrderDtl.PartNum like 'EC1%')
or Erp.OrderDtl.PartNum like 'ET1%'
or Erp.OrderDtl.PartNum like 'RB1%'
or Erp.OrderDtl.PartNum like 'RS1%')
and ((Erp.SalesCat.SalesCatID <> 'VDAE')
or (Erp.OrderDtl.SalesCatID <> 'VDPR'))
order by Erp.OrderHed.OrderNum desc
zd
SELECT 
    ColumnName,
    DataType
FROM CM_DataTypeValidator
WHERE Active = 1
ORDER BY CreatedAt
zX
SELECT COUNT(*) as existe
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CM_Score'
zS
SELECT name 
FROM sys.tables 
WHERE name LIKE 'ScoreHistory_%'
ORDER BY name DESC
c                  C   s  d} zzt d t d t d t } | du r8tdt d t d t d t d tt| }t d	 t d
t|  t dt|j  |W W | durz|   t d W S  ty } z t dt	| d W Y d}~S d}~0 0 S  t
jy } z|jr|jd nd}t	|}t d|  t d| d d|v s\d|v s\d|v rltd| nfd| v sd|v rtd| n>d| v sd|v rtd| ntd| d| dW Y d}~n~d}~0  tjjy" } z td t	| d!W Y d}~nBd}~0  tyZ } z td"t	| d#W Y d}~n
d}~0 0 W | durz|   t d W n: ty } z t dt	| d W Y d}~n
d}~0 0 n\| durz|   t d W n: ty } z t dt	| d W Y d}~n
d}~0 0 0 dS )$u,  
    Ejecuta el query del Score en el ERP y retorna DataFrame
    
    ⚠️  VERSIÓN MEJORADA CON MANEJO ROBUSTO DE ERRORES
    
    Returns:
        pd.DataFrame: DataFrame con las 70 columnas ERP
        
    Raises:
        Exception: Si hay error en la conexión o query con mensaje claro
    Nz<============================================================zCONECTANDO AL SERVIDOR ERP...u+  
❌ ERROR CRÍTICO: get_connectionERP() retornó None

La función de conexión no pudo establecer conexión con el ERP.

🔍 DIAGNÓSTICO:
1. Verifique que el servidor SQL Server del ERP esté encendido
2. Verifique que pueda hacer ping al servidor ERP
3. Verifique que SQL Server acepte conexiones remotas
4. Verifique que Named Pipes esté habilitado en SQL Server
5. Verifique que no haya firewall bloqueando el puerto 1433
6. Revise el archivo de configuración de conexión

📝 Contacte al administrador del servidor ERP si el problema persiste.u-   ✓ Conexión al ERP establecida exitosamentez!
Ejecutando consulta del Score...uB   ⏱️  ADVERTENCIA: Esta consulta puede tardar entre 2-10 minutoszPor favor espere...u!   
✓ Query ejecutado exitosamenteu   ✓ Filas obtenidas del ERP: u   ✓ Columnas obtenidas: u+   ✓ Conexión al ERP cerrada correctamente
u1   ⚠️  Warning: No se pudo cerrar la conexión: 
r   UNKNOWNu   
❌ ERROR DE PYODBC: 
Detalles: Z08001zNamed PipeszCould not openu  
🔌 ERROR DE CONEXIÓN AL ERP (Código 08001)

No se pudo establecer conexión con el servidor SQL Server del ERP.

🔍 CAUSAS POSIBLES:
1. El servidor SQL Server del ERP está apagado
2. El servidor ERP no es accesible por red
3. Named Pipes Provider no está habilitado
4. Firewall bloqueando la conexión
5. Configuración incorrecta de SQL Server

✅ SOLUCIONES:
1. Verificar que SQL Server esté corriendo en el servidor ERP
2. Hacer ping al servidor ERP para verificar conectividad
3. Habilitar Named Pipes en SQL Server Configuration Manager
4. Verificar reglas de firewall (puerto 1433)
5. Verificar que SQL Server Browser esté corriendo

📋 Error técnico: timeoutzLogin timeoutu{  
⏱️  TIMEOUT AL CONECTAR AL ERP

El servidor ERP no respondió en el tiempo esperado (30 segundos).

🔍 CAUSAS POSIBLES:
1. El servidor ERP está muy ocupado
2. Problemas de red lenta
3. El servidor está iniciándose

✅ SOLUCIONES:
1. Espere unos minutos e intente nuevamente
2. Verifique la carga del servidor ERP
3. Verifique la velocidad de red

📋 Error técnico: loginZ18456u6  
🔐 ERROR DE AUTENTICACIÓN

Las credenciales para conectar al ERP son incorrectas.

✅ SOLUCIONES:
1. Verificar usuario y contraseña en el archivo de conexión
2. Verificar que el usuario tenga permisos en SQL Server
3. Verificar que la autenticación de SQL Server esté habilitada

📋 Error técnico: u8   
❌ ERROR DESCONOCIDO DE SQL SERVER

Código de error: z
Detalles: z-

Contacte al administrador de base de datos.uN   
❌ ERROR AL EJECUTAR QUERY SQL

Pandas no pudo ejecutar el query.
Detalles: u)   

Verifique que el query SQL sea válido.u6   
❌ ERROR INESPERADO

Ocurrió un error no previsto: z#

Contacte al equipo de desarrollo.)printr   	Exceptionpdread_sqlQUERY_SCORE_COMPLETOlencolumnsclosestrpyodbcErrorargslowererrorsDatabaseError)conndfe
error_code	error_msg r   L/var/www/html/src/Consultas_SQL/SupYCtrol/IngDeControl/ActualizarScoreSQL.pyobtener_datos_score_erp  s    S*

,
r   c               
   C   s   z4t  } tt| }|   tdt| d |W S  ty| } z0tdt|  tdt| W Y d}~n
d}~0 0 dS )zF
    Obtiene la estructura de columnas desde CM_DataTypeValidator
    u'   ✓ Estructura del validador obtenida: 	 columnasu/   ✗ Error al obtener estructura del validador: z)Error al consultar CM_DataTypeValidator: N)	r   r   r   "QUERY_OBTENER_ESTRUCTURA_VALIDADORr   r	   r   r
   r   )r   r   r   r   r   r   obtener_estructura_validador3  s    r"   c               
   C   s   zXt  } |  }|t | }|d dk}|  |   td|rJdnd  |W S  ty } z0tdt|  tdt| W Y d}~n
d}~0 0 dS )z5
    Verifica si la tabla CM_Score existe en VPS
    r   u   ✓ Tabla CM_Score existez	NO existeu'   ✗ Error al verificar tabla CM_Score: zError al verificar tabla: N)	r   cursorexecuteQUERY_VERIFICAR_TABLA_CM_SCOREfetchoner   r	   r
   r   )r   r$   resultr#   r   r   r   r   verificar_tabla_cm_scoreD  s    
r)   c               
   C   s   zt  } tt| }|   t|dkr6td W dS g }|d D ]6}z t|dd }|	| W qB   Y qBY qB0 qB|sW dS t
|}td|  |W S  ty } z0tdt|  tdt| W Y d	}~n
d	}~0 0 d	S )
uH   
    Obtiene el número de versión más reciente de ScoreHistory_X
    r   u)   ✓ No hay tablas ScoreHistory existentesname_   u&   ✓ Última versión de ScoreHistory: u'   ✗ Error al obtener última versión: zError al consultar versiones: N)r   r   r   QUERY_VERIFICAR_SCOREHISTORYr   r   r	   intsplitappendmaxr
   r   )r   r   Z	versionesZnombre_tablaversionZmax_versionr   r   r   r   obtener_ultima_version_history\  s,    r3   c              
   C   s   zNt  }d|  }t||}|  |j }td|  dt| d |W S  ty } z6td|  dt	|  tdt	| W Y d}~n
d}~0 0 dS )	z:
    Obtiene los nombres de las columnas de una tabla
    zSELECT TOP 1 * FROM u   ✓ Columnas de z obtenidas: r    u!   ✗ Error al obtener columnas de : z&Error al obtener estructura de tabla: N)
r   r   r   r   r   tolistr	   r   r
   r   )
tabla_namer   queryr   columnasr   r   r   r   obtener_columnas_tabla}  s    

r9   c              
   C   s   zPt  }| }t| d}td || |  |  |  td W dS  ty } z0tdt|  tdt| W Y d}~n
d}~0 0 dS )zF
    Crea la tabla CM_Score basada en la estructura del validador
    CM_ScorezCreando tabla CM_Score...u&   ✓ Tabla CM_Score creada exitosamenteTu#   ✗ Error al crear tabla CM_Score: zError al crear tabla: N	r   r$   generar_create_table_dinamicor	   r%   commitr   r
   r   )estructura_dfr   r$   
create_sqlr   r   r   r   crear_tabla_cm_score  s    

r@   c              
   C   s   zjd|  }t  }| }t||}td| d || |  |  |  td| d W dS  ty } z6td|  dt|  td	t| W Y d
}~n
d
}~0 0 d
S )z-
    Crea una nueva tabla ScoreHistory_X
    ScoreHistory_zCreando tabla ...
   ✓ Tabla z creada exitosamenteTu&   ✗ Error al crear tabla ScoreHistory_r4   z#Error al crear tabla de historial: Nr;   )r2   r>   r6   r   r$   r?   r   r   r   r   crear_tabla_scorehistory  s    


rD   c              
   C   s   z\t  }| }td|  d |d|   |  |  |  td|  d W dS  ty } z6td|  dt|  td	t| W Y d
}~n
d
}~0 0 d
S )z*
    Limpia una tabla usando TRUNCATE
    zLimpiando tabla rB   zTRUNCATE TABLE rC   z limpiada exitosamenteTu   ✗ Error al limpiar tabla r4   zError al truncar tabla: N)r   r$   r	   r%   r=   r   r
   r   )r6   r   r$   r   r   r   r   truncate_tabla  s    rE   c              
      s  zddl }ddlmm m t }| }tdt|  d| d |  }td |j	D ]}|| j
dkr||  d	}t|dkrt|jd rtd
| d ||  fdd||< tjj|| r^td| d d||< q^|j	 }g }	|D ]8}d|v s"d|v r6|	d| d n
|	| q
ddd |D }
d|	}d| d| d|
 d}td g }| D ]\}}g }|D ]v}|du st|r|d nNt| fr|| n0t|tr| dkr|d n
|| q|t| qd}d}td| d tdt||D ]^}||||  }||| |  |t|7 }t|dkrPtd| d t| d! qP|  |  td"t|  d#|  W d$S  |j y } z|j!r|j!d nd%}t|}td& td'|  td(| d) d*|v sHd+|v r`t"d,| d-| d.n0d/|v r|t"d0| d1nt"d2| d3| W Y d}~nXd}~0  t"y } z6td4| d3t|  t"d5t| W Y d}~n
d}~0 0 dS )6u  
    Inserta DataFrame en tabla usando bulk insert con pyodbc
    
    ✅ VERSIÓN CORREGIDA: Convierte columnas TIME a DATETIME automáticamente
    
    Args:
        df (pd.DataFrame): DataFrame a insertar
        tabla_name (str): Nombre de la tabla destino
        
    Returns:
        bool: True si se insertó correctamente
        
    Raises:
        Exception: Si hay error al insertar
    r   N)datetimedatetimezInsertando z
 filas en rB   z  Limpiando tipos de datos...objectr,   u!     ✓ Convirtiendo columna TIME 'u"   ' → DATETIME (1900-01-01 + hora)c                    s*   t | r&| d ur& ddd| S d S )Nil  r,   )
isinstancecombine)xrG   rF   rH   r   r   <lambda>      z%insertar_bulk_score.<locals>.<lambda>u&     ✓ Convirtiendo columna TIMEDELTA 'u
   ' → NULL& []z, c                 S   s   g | ]}d qS )?r   ).0r+   r   r   r   
<listcomp>  rO   z'insertar_bulk_score.<locals>.<listcomp>z
        INSERT INTO  (z)
        VALUES (z
)
        u%     Preparando datos para inserción... i  z  Insertando en lotes de u       → /z filas insertadas...u   ✓ z" filas insertadas exitosamente en Tr   u$   
❌ ERROR DE SQL SERVER AL INSERTARu	   Código: r   r   Z22018z
type clashuv   
⚠️  ERROR DE TIPO DE DATOS

Hay un conflicto de tipos de datos entre el DataFrame y la tabla SQL.
Tabla destino: u   

Detalles técnicos: zJ

Verifique que los tipos de datos en CM_DataTypeValidator sean correctos.z(String or binary data would be truncatedue   
⚠️  ERROR DE LONGITUD DE DATOS

Algún valor es demasiado largo para su columna.
Tabla destino: u>   

Verifique los tamaños de las columnas NVARCHAR en la tabla.zError al insertar datos en r4   u   ✗ Error al insertar datos en zError al insertar datos: )#r   rF   rG   rH   r   r$   r	   r   copyr   dtypedropnaheadrJ   ilocapplyr   apitypesis_timedelta64_dtyper5   r0   joiniterrowsisnar   striptuplerangeexecutemanyr=   r   r   r   r
   )r   r6   r   r   r$   Zdf_cleancolZfirst_validr8   Zcolumnas_sqlZplaceholdersZcolumnas_strinsert_queryZdata_to_insertidxrowZrow_dataval
batch_sizetotal_insertedibatchr   r   r   r   rM   r   insertar_bulk_score  s    




(rs   c                 C   s   d| dg}| d |  D ]L\}}|d }|d }d|v sHd|v rTd| d	}| d
| d| d q | d | d | d | d | d | d d|S )u3   
    Genera SQL de CREATE TABLE dinámicamente
    zCREATE TABLE rW   z%    ID INT IDENTITY(1,1) PRIMARY KEY,
ColumnNameDataTyperP   rQ   rR   rS   z    ,z2    CreatedAt DATETIME NOT NULL DEFAULT GETDATE(),z+    CreatedBy NVARCHAR(6) DEFAULT 'SYSTEM',z)    UpdatedAt DATETIME DEFAULT GETDATE(),z+    UpdatedBy NVARCHAR(6) DEFAULT 'SYSTEM',z!    Active BIT NOT NULL DEFAULT 1)r   )r0   rd   rc   )r>   r6   Z	sql_partsr+   rm   col_name	data_typer   r   r   r<   r  s    






r<   )__doc__Consultas_SQL.conexionr   r   pandasr   r   r   r!   r&   r-   r   r"   r)   r3   r9   r@   rD   rE   rs   r<   r   r   r   r   <module>   s@                	 ! 