You'll be much better served by asking subject matter experts in dedicated forum over here.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql
(please don't forget to mark helpful replies as answer)
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I have this sp, they ask me to insert a column at the end that says yes or no
if UUID_COMP_17! = '' And Imp_Cargo! = 0 is yes
if UUID_COMP_17 = '' and Imp_Cargo! = 0 is yes
and if UUID_COMP_17 = '' and Imp_Cargo = 0 is no
lines in bold add it but when executing it it shows me errors that the column Proc_Comp that I want to insert is invalid and that the provided values do not match the definition of the table, I did it this way because according to me it is like that maybe it should do it separately as the cases that come there but I don't know how
CREATE PROCEDURE [dbo].[Sp_Consulta_sUP]
@PNumProveedor INT
,@PNumPago INT
,@PFechaIniMovto VARCHAR(8)
,@PFechaFinMovto VARCHAR(8)
AS
BEGIN
-- CONSECUTIVO 000003
-- -----------------------------------------------------------------------------------------
-- CONSECUTIVO : 000003
-- BASE DE DATOS : USE AdmRecCFDIProdDB
-- MODULO : Reporte de UUIDS Complementos
-- OBJETIVO : Obtener UUIDS de Complementos
--
-- COMENTARIOS :
--
--
-- CONSECUTIVO AUTOR FECHA MODIFICACION
-- -------------------------------------------------------------------------------------------------------------------
-- 000001 Victor Hugo Sanchez Morales 2019.09.09 Creacion
-- 000002 Ismael Ruiz Torres 2019.10.07 Modificación Se agregan columnas Nuevas--
-- Solicidas Por el usuario tomando encuenta la nueva columna de complemento 17
-- 000003 Ismael Ruiz Torres 2019.10.21 Modificación Se agregan NUEVOS PARAMETROS
---@PNumProveedor INT ,@PNumPago INT,@PFechaIniMovto VARCHAR(8),@PFechaFinMovto VARCHAR(8)
-- -------------------------------------------------------------------------------------------------------------------
-- Pruebas Unitarias
---
----1500080095 552521
-- EXEC dbo.Sp_Consulta_sUP 40022,0,'',''
-- EXEC dbo.Sp_Consulta_sUP 0,1500013375,'',''
-- EXEC dbo.Sp_Consulta_sUP 0,0,'20190909','20190909'
-- EXEC dbo.Sp_Consulta_sUP 40022,0,'20190901','20200120'
-- EXEC dbo.Sp_Consulta_sUP 0,1500013375,'20190917','20191017'
-- EXEC dbo.Sp_Consulta_sUP 0,0,'',''
-- EXEC dbo.Sp_Consulta 552521
SET NOCOUNT ON -- Inhibe el env-o de DONE_IN_PROC al cliente
SET XACT_ABORT ON
-- -----------------------------------------------------------------------------------------------
-- VALIDA TABLA PARAMETRO
-- -----------------------------------------------------------------------------------------------
-- -----------------------------------------------------------------------------------------------
-- DECLARACION DE VARIABLES
-- -----------------------------------------------------------------------------------------------
---DECLARE
-- -----------------------------------------------------------------------------------------------
-- Variable de Trabajo
-- -----------------------------------------------------------------------------------------------
DECLARE @strParametros VARCHAR(500)
-- -----------------------------------------------------------------------------------------------
-- CREACION DE TABLAS TEMPORALES
-- -----------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PagoCP_Prov'))
DROP TABLE #PagoCP_Prov
CREATE TABLE #PagoCP_Prov (
Sociedad_Pago VARCHAR(4) NOT NULL,
Ejercicio_Pago SMALLINT NOT NULL,
Id_Num_Pago BIGINT NOT NULL,
UUID_Comp VARCHAR(36) NOT NULL,
Id_Num_Prov INT NOT NULL,
Fec_Pago DATETIME NOT NULL,
Imp_Cargo MONEY NOT NULL,
Imp_CargoProv MONEY NOT NULL,
Imp_Fact MONEY NOT NULL,
Imp_NC MONEY NOT NULL,
Imp_Total MONEY NOT NULL,
Bit_NC_OK BIT NOT NULL,
Id_Estatus SMALLINT NOT NULL,
Fec_Movto DATETIME NOT NULL,
UUID_Comp_17 VARCHAR(36) NOT NULL,
)
IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PagoCP_ProvConRS'))
DROP TABLE #PagoCP_ProvConRS
CREATE TABLE #PagoCP_ProvConRS
(
Id_Num_Prov INTEGER NOT NULL
,Cant_Pagos INTEGER NOT NULL
,Imp_TotPago MONEY NOT NULL
,Cant_CPRecibido INTEGER NOT NULL
,Imp_TotPago_CPRecibido MONEY NOT NULL
,Id_FEV_RazonSocial SMALLDATETIME NOT NULL
,FSV_RazonSocial SMALLDATETIME NOT NULL
)
------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PagoCP_ProvConRS_vFinal'))
DROP TABLE #PagoCP_ProvConRS_vFinal
CREATE TABLE #PagoCP_ProvConRS_vFinal (
Id_Num_Prov INTEGER NOT NULL
,Cant_Pagos INTEGER NOT NULL
,Imp_TotPago MONEY NOT NULL
,Cant_CPRecibido INTEGER NOT NULL
,Imp_TotPago_CPRecibido MONEY NOT NULL
,Id_FEV_RazonSocial SMALLDATETIME NOT NULL
,FSV_RazonSocial SMALLDATETIME NOT NULL
,RazonSocial VARCHAR(70) NOT NULL
,RFC CHAR(13) NOT NULL
)
IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PagoCP_ProvTotPagos'))
DROP TABLE #PagoCP_ProvTotPagos
CREATE TABLE #PagoCP_ProvTotPagos (
Id_Num_Prov INTEGER NOT NULL
, Imp_TotPago MONEY NOT NULL
, Cant_Pagos INTEGER NOT NULL
)
------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PagoCP_ProvResumen'))
DROP TABLE #PagoCP_ProvResumen
CREATE TABLE #PagoCP_ProvResumen (
Id_Num_Prov INTEGER NOT NULL
,Cant_Pagos INTEGER NOT NULL
,Imp_TotPago MONEY NOT NULL
,Cant_CPRecibido INTEGER NOT NULL
,Imp_TotPago_CPRecibido MONEY NOT NULL
)
IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PagoCP_ProvCP'))
DROP TABLE #PagoCP_ProvCP
CREATE TABLE #PagoCP_ProvCP (
Id_Num_Prov INTEGER NOT NULL
, Imp_TotPago MONEY NOT NULL
, Cant_Pagos INTEGER NOT NULL
)
------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#PAgoCP_ProvDetalle'))
DROP TABLE #PAgoCP_ProvDetalle
CREATE TABLE #PAgoCP_ProvDetalle(
Sociedad_Pago VARCHAR(4) NOT NULL,
Ejercicio_Pago SMALLINT NOT NULL,
Id_Num_Pago BIGINT NOT NULL,
UUID_Comp VARCHAR(36) NOT NULL,
Id_Num_Prov INT NOT NULL,
Fec_Pago DATETIME NOT NULL,
Imp_Cargo MONEY NOT NULL,
Imp_CargoProv MONEY NOT NULL,
Imp_Fact MONEY NOT NULL,
Imp_NC MONEY NOT NULL,
Imp_Total MONEY NOT NULL,
Bit_NC_OK BIT NOT NULL,
Id_Estatus SMALLINT NOT NULL,
Fec_Movto DATETIME NOT NULL,
UUID_Comp_17 VARCHAR(36) NOT NULL,
)
------------------------------------------------------------------------------------------------
IF EXISTS( SELECT * FROM tempdb..sysobjects WHERE id =object_id('tempdb..#Tmp_PagoCP'))
DROP TABLE #Tmp_PagoCP
CREATE TABLE #Tmp_PagoCP(
Id_Num_Prov2 INTEGER NULL --Id_Num_Prov
,Periodo VARCHAR(12) NULL --Fec_Pago
,Cant_CP INTEGER NULL
,Imp_CPRecibido MONEY NULL
,Cant_SinCP MONEY NULL
,Imp_SinCP MONEY NULL
,Cant_CP_17 INTEGER NULL
,Total_Complementos INTEGER NULL
,ImporteTot_CP_17 MONEY NULL
,Sociedad_Pago VARCHAR(4) NOT NULL
,Ejercicio_Pago SMALLINT NOT NULL
,Id_Num_Pago BIGINT NOT NULL
,UUID_Comp VARCHAR(36) NOT NULL
,Imp_Cargo MONEY NOT NULL
,Imp_CargoProv MONEY NOT NULL
,Imp_Fact MONEY NOT NULL
,Imp_NC MONEY NOT NULL
,Imp_Total MONEY NOT NULL
,Bit_NC_OK BIT NOT NULL
,Id_Estatus SMALLINT NOT NULL
,Fec_Movto DATETIME NOT NULL
,UUID_Comp_17 VARCHAR(36) NOT NULL
,Proc_Comp varchar(36) not null
)
BEGIN TRY
------------------------------------------------------------------------------------------------
----Cuerpo PROCEDIMIENTO-----------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------[4]---[Consultar dbo.CFDI_PagoComp e insertar en -->#PagoCP_Prov]----
--IF(@PNumProveedor <>0 OR @PNumPago <>0 OR @PFechaIniMovto <>'')
--BEGIN
INSERT INTO #PagoCP_Prov
SELECT
Sociedad_Pago
,Ejercicio_Pago
,Id_Num_Pago
,UUID_Comp
,Id_Num_Prov
,Fec_Pago
,Imp_Cargo
,Imp_CargoProv
,Imp_Fact
,Imp_NC
,Imp_Total
,Bit_NC_OK
,Id_Estatus
,Fec_Movto
,UUID_Comp_17
FROM dbo.CFDI_PagoComp cp ( NOLOCK )
WHERE (Id_Num_Prov = @PNumProveedor OR @PNumProveedor = 0)
AND (Id_Num_Pago = @PNumPago OR @PNumPago = 0)
AND (CAST(Fec_Pago AS DATE) BETWEEN @PFechaIniMovto AND @PFechaFinMovto OR @PFechaIniMovto = '' )
GROUP BY Sociedad_Pago ,Ejercicio_Pago ,Id_Num_Pago ,UUID_Comp ,Id_Num_Prov ,Fec_Pago ,Imp_Cargo ,Imp_CargoProv ,Imp_Fact ,Imp_NC ,Imp_Total ,Bit_NC_OK ,Id_Estatus ,Fec_Movto ,UUID_Comp_17
--END ------------------------[FINALIZA]
--ELSE
--BEGIN
--INSERT INTO #PagoCP_Prov
-- SELECT
-- Sociedad_Pago
-- ,Ejercicio_Pago
-- ,Id_Num_Pago
-- ,UUID_Comp
-- ,Id_Num_Prov
-- ,Fec_Pago
-- ,Imp_Cargo
-- ,Imp_CargoProv
-- ,Imp_Fact
-- ,Imp_NC
-- ,Imp_Total
-- ,Bit_NC_OK
-- ,Id_Estatus
-- ,Fec_Movto
-- ,UUID_Comp_17
-- FROM dbo.CFDI_PagoComp cp ( NOLOCK )
-- GROUP BY Sociedad_Pago ,Ejercicio_Pago ,Id_Num_Pago ,UUID_Comp ,Id_Num_Prov ,Fec_Pago ,Imp_Cargo ,Imp_CargoProv ,Imp_Fact ,Imp_NC ,Imp_Total ,Bit_NC_OK ,Id_Estatus ,Fec_Movto ,UUID_Comp_17
--END
------[5]---[Consultar #PagoCP_Prov e insertar en -->#PAgoCP_ProvDetalle]----
INSERT INTO #PAgoCP_ProvDetalle
SELECT
pcp.Sociedad_Pago
,pcp.Ejercicio_Pago
,pcp.Id_Num_Pago
,pcp.UUID_Comp
,pcp.Id_Num_Prov
,pcp.Fec_Pago
,pcp.Imp_Cargo
,pcp.Imp_CargoProv
,pcp.Imp_Fact
,pcp.Imp_NC
,pcp.Imp_Total
,pcp.Bit_NC_OK
,pcp.Id_Estatus
,pcp.Fec_Movto
,pcp.UUID_Comp_17
FROM
#PagoCP_Prov pcp ( NOLOCK )
JOIN AdmCxPProdDB.dbo.pagPago_CFDI p ( NOLOCK ) ON pcp.Id_Num_Prov = p.Id_Num_Prov
AND pcp.Id_Num_Pago = p.Num_Pago
AND pcp.Fec_Pago = p.Fec_Pago
JOIN AdmCxPProdDB.dbo.pagPago pa ( NOLOCK ) ON p.Id_Num_Prov = pa.Id_Num_Prov
AND p.Num_Pago = pa.Id_Num_Pago
AND p.Fec_Pago = pa.Fec_Pago
------[6]---[Consultar #PAgoCP_ProvDetalle e insertar en -->#PagoCP_ProvTotPagos]----
INSERT INTO #PagoCP_ProvTotPagos
SELECT
Id_Num_Prov,
SUM( Imp_Total) Imp_TotPago,
COUNT(*) Cant_Pagos
FROM #PAgoCP_ProvDetalle pd (NOLOCK )
GROUP BY Id_Num_Prov
------[7]---[Consultar #PagoCP_ProvCP e insertar en -->#PAgoCP_ProvDetalle]----
INSERT INTO #PagoCP_ProvCP
SELECT
Id_Num_Prov,
SUM( Imp_Total) Imp_TotPago,
COUNT(*) Cant_Pagos
FROM #PAgoCP_ProvDetalle pd (NOLOCK )
WHERE UUID_Comp != ''
GROUP BY Id_Num_Prov
-----[8]---[Consultar #PagoCP_ProvTotPagos e insertar en -->#PagoCP_ProvResumen]----
INSERT INTO #PagoCP_ProvResumen
SELECT
pt.Id_Num_Prov,
pt.Cant_Pagos,
pt.Imp_TotPago,
ISNULL( psc.Cant_Pagos , 0 ) Cant_CPRecibido,
ISNULL( psc.Imp_TotPago, 0 )
Imp_TotPago_CPRecibido
FROM
#PagoCP_ProvTotPagos pt
LEFT JOIN #PagoCP_ProvCP psc ON pt.Id_Num_Prov = psc.Id_Num_Prov
-----[9]---[Consultar #PagoCP_ProvResumen e insertar en -->#PagoCP_ProvConRS]----
INSERT INTO #PagoCP_ProvConRS
SELECT pcp.Id_Num_Prov,
Cant_Pagos,
Imp_TotPago,
Cant_CPRecibido,
Imp_TotPago_CPRecibido,
MAX( Id_FEV_RazonSocial) Id_FEV_RazonSocial,
MAX( FSV_RazonSocial ) FSV_RazonSocial
FROM #PagoCP_ProvResumen pcp
INNER JOIN FactECorpProdDB.dbo.Prov_RazonSocial_d p WITH (NOLOCK) ON pcp.Id_Num_Prov = p.Id_Num_Prov
and getdate() between Id_FEV_RazonSocial and FSV_RazonSocial
GROUP BY pcp.Id_Num_Prov, Cant_Pagos, Imp_TotPago, Cant_CPRecibido, Imp_TotPago_CPRecibido
-----[10]---[Consultar #PagoCP_ProvConRS e insertar en -->#PagoCP_ProvConRS_vFinal]----
INSERT INTO #PagoCP_ProvConRS_vFinal
SELECT
--pcp.*,
pcp.Id_Num_Prov
,pcp.Cant_Pagos
,pcp.Imp_TotPago
,pcp.Cant_CPRecibido
,pcp.Imp_TotPago_CPRecibido
,pcp.Id_FEV_RazonSocial
,pcp.FSV_RazonSocial
,p.RazonSocial
,p.RFC
FROM
#PagoCP_ProvConRS pcp
INNER JOIN FactECorpProdDB.dbo.Prov_RazonSocial_d p WITH (NOLOCK) ON pcp.Id_Num_Prov = p.Id_Num_Prov
AND pcp.Id_FEV_RazonSocial = p.Id_FEV_RazonSocial
AND pcp.FSV_RazonSocial = p.FSV_RazonSocial
WHERE
GETDATE() +1 BETWEEN pcp.Id_FEV_RazonSocial
AND pcp.FSV_RazonSocial OR pcp.FSV_RazonSocial IS NULL
-----[11]---[Consultar ##PAgoCP_ProvDetalle e insertar en -->#Tmp_PagoCP]----
INSERT INTO #Tmp_PagoCP
SELECT
Id_Num_Prov --Id_Num
_Prov
,SUBSTRING( CONVERT( CHAR(08), Fec_Pago, 112 ) , 1, 6 ) Periodo --Fec_Pago
,CASE
WHEN UUID_COMP != '' THEN COUNT()
ELSE 0
END Cant_CP
,CASE
WHEN UUID_COMP !='' THEN SUM( Imp_Total )
ELSE 0
END Imp_CPRecibido
,CASE
WHEN UUID_COMP = '' THEN COUNT()
ELSE 0
END Cant_SinCP
,CASE
WHEN UUID_COMP = '' THEN SUM( Imp_Total )
ELSE 0
END Imp_SinCP
,CASE
WHEN UUID_COMP_17 != '' THEN count()
ELSE 0
END Cant_CP_17
,CASE
WHEN UUID_COMP_17 != '' THEN count()
ELSE 0
END Total_Complementos
,CASE
WHEN UUID_COMP_17 !='' THEN SUM( Imp_Cargo )
ELSE 0
END ImporteTot_CP_17
,CASE
WHEN UUID_COMP_17 !='' AND Imp_Cargo != 0 THEN 'Si'
ELSE 0
END Proc_Comp
,CASE
WHEN UUID_COMP_17 ='' AND Imp_Cargo != 0 THEN 'Si'
ELSE 0
END Proc_Comp
,CASE
WHEN UUID_COMP_17 ='' AND Imp_Cargo = 0 THEN 'No'
ELSE 0
END Proc_Comp
,Sociedad_Pago
,Ejercicio_Pago
,Id_Num_Pago
,UUID_Comp
,Imp_Cargo
,Imp_CargoProv
,Imp_Fact
,Imp_NC
,Imp_Total
,Bit_NC_OK
,Id_Estatus
,Fec_Movto
,UUID_Comp_17
FROM #PAgoCP_ProvDetalle
GROUP BY Sociedad_Pago ,Ejercicio_Pago ,Id_Num_Pago,UUID_Comp,Id_Num_Prov,SUBSTRING( CONVERT( CHAR(08), Fec_Pago, 112 ) , 1, 6 ),Imp_Cargo,Imp_CargoProv,Imp_Fact,Imp_NC,Imp_Total,Bit_NC_OK,Id_Estatus,Fec_Movto,UUID_Comp_17
-----[12]---[Consultar #Tmp_PagoCP ]----------------------------------------------
SELECT -- top 100
TMP.Id_Num_Prov2 AS [Núm. de proveedor], --, TMP.Id_Num_Prov
TMP.Periodo Periodo --, TMP.Fec_Pago
,SUM( TMP.Cant_CP ) + SUM( Cant_SinCP ) AS [Total de pagos ]
,SUM( Cant_SinCP ) AS [Pagos con CP]
,SUM( TMP.Cant_CP ) AS 'Cant_CPRecibido 03'
,SUM( TMP.Cant_CP_17) AS 'Cant.Comp. Recibidos 17'
,SUM( TMP.Total_Complementos+TMP.Cant_CP ) AS 'Cant.Comp. Recibidos 03 Y 17'
,SUM( TMP.ImporteTot_CP_17) AS 'Importe Total. 17'
,SUM( TMP.Cant_CP ) * 100 /( SUM( Cant_CP ) + SUM( Cant_SinCP ) ) AS [Pagos sin CP ]
,SUM( TMP.Imp_CPRecibido ) + SUM( Imp_SinCP ) AS [Importe total de pago 03]
,SUM( TMP.Imp_CPRecibido ) AS [Importe de CP recibidos]
,SUM( TMP.Imp_SinCP ) AS [Importe sin CP]
,SUM( TMP.Imp_CPRecibido ) * 100 / NULLIF( SUM( Imp_CPRecibido ) + SUM( Imp_SinCP ),0)
, TMP.Sociedad_Pago
, TMP.Ejercicio_Pago
, TMP.Id_Num_Pago
, TMP.Imp_Total
, TMP.Id_Estatus
, TMP.Imp_Cargo
, TMP.Imp_CargoProv
, TMP.Imp_Fact
, TMP.Imp_NC
, TMP.Bit_NC_OK
, TMP.Fec_Movto
,TMP.UUID_COMP
,TMP.UUID_COMP_17
,TMP.Proc_Comp
FROM #Tmp_PagoCP TMP
WHERE --CAST(TMP.Fec_Movto AS DATE) >= '201909'
TMP.Periodo >= '201909'
GROUP BY TMP.Id_Num_Prov2,TMP.Periodo, TMP.Sociedad_Pago,TMP.Ejercicio_Pago,TMP.Id_Num_Pago,TMP.UUID_Comp,TMP.Imp_Total, TMP.Id_Estatus,TMP.Imp_Cargo,TMP.Imp_CargoProv,TMP.Imp_Fact,TMP.Imp_NC,TMP.Bit_NC_OK,TMP.Fec_Movto,UUID_COMP,UUID_COM
P_17,TMP.Cant_CP_17,TMP.ImporteTot_CP_17, TMP.Total_Complementos ORDER BY Id_Num_Prov2,Periodo,Id_Num_Pago,Imp_Total,Id_Estatus
SET NOCOUNT OFF
-----------------------------------------------------------------------------------------------
-- FIN
------------------------------------------------------------------------------------------------
RETURN 0
END TRY
BEGIN CATCH
------------------------------------------------------------------------------------------------
-- MANEJO DE ERRORES
------------------------------------------------------------------------------------------------
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION
END
SELECT @strParametros = '@PNumProveedor=' + CONVERT(VARCHAR,@PNumProveedor)
SELECT @strParametros = + @strParametros + ',@PNumPago=' + CONVERT(VARCHAR,@PNumPago)
SELECT @strParametros = + @strParametros + ',@PFechaIniMovto=' + @PFechaIniMovto
SELECT @strParametros = + @strParametros + ',@PFechaFinMovto=' + @PFechaFinMovto
-------------------------------------
-- Graba y NO marca Error --
-------------------------------------
-- EXECUTE CFDI_LogError_iUP @pRaiserror = 0
-------------------------------------
-- Graba y SI marca Error --
-------------------------------------
EXECUTE CFDI_LogError_iUP @pRaiserror = 1, @pParametro = @strParametros
END CATCH -- BEGIN CREATE PROCEDURE
END
You'll be much better served by asking subject matter experts in dedicated forum over here.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql
(please don't forget to mark helpful replies as answer)