87 lines
1.7 KiB
SQL
87 lines
1.7 KiB
SQL
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
CREATE TABLE #VentasPrueba
|
|
(
|
|
CveVenta int NOT NULL PRIMARY KEY
|
|
);
|
|
|
|
INSERT INTO #VentasPrueba
|
|
(
|
|
CveVenta
|
|
)
|
|
SELECT DISTINCT
|
|
VD.CveVenta
|
|
FROM dbo.VentasDetalle AS VD
|
|
WHERE VD.CveSolicitud IS NOT NULL
|
|
OR VD.CveCostoTramite IS NOT NULL
|
|
OR VD.EsFondoInicial = 1;
|
|
|
|
SELECT
|
|
V.CveVenta,
|
|
V.Serie,
|
|
V.Folio,
|
|
V.Fecha,
|
|
V.SubTotal,
|
|
V.Descuento,
|
|
V.IVA,
|
|
V.Total
|
|
FROM dbo.Ventas AS V
|
|
INNER JOIN #VentasPrueba AS VP
|
|
ON VP.CveVenta = V.CveVenta
|
|
ORDER BY V.CveVenta;
|
|
|
|
IF EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.Vta_Pagos AS P
|
|
INNER JOIN #VentasPrueba AS VP
|
|
ON VP.CveVenta = P.CveVenta
|
|
)
|
|
BEGIN
|
|
THROW 50002, 'La selección incluye ventas con pagos; la limpieza de prueba se canceló.', 1;
|
|
END;
|
|
|
|
IF EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.Fac_CfdiDocumentos AS D
|
|
INNER JOIN #VentasPrueba AS VP
|
|
ON VP.CveVenta = D.CveOrigen
|
|
WHERE D.TipoOrigen = 'VENTA'
|
|
)
|
|
BEGIN
|
|
THROW 50003, 'La selección incluye ventas con documentos CFDI; la limpieza de prueba se canceló.', 1;
|
|
END;
|
|
|
|
DELETE VDI
|
|
FROM dbo.VentasDetalleImpuestos AS VDI
|
|
INNER JOIN dbo.VentasDetalle AS VD
|
|
ON VD.CveVentaDetalle = VDI.CveVentaDetalle
|
|
INNER JOIN #VentasPrueba AS VP
|
|
ON VP.CveVenta = VD.CveVenta;
|
|
|
|
DELETE VD
|
|
FROM dbo.VentasDetalle AS VD
|
|
INNER JOIN #VentasPrueba AS VP
|
|
ON VP.CveVenta = VD.CveVenta;
|
|
|
|
DELETE SP
|
|
FROM dbo.Tra_SolicitudesPagos AS SP
|
|
INNER JOIN #VentasPrueba AS VP
|
|
ON VP.CveVenta = SP.CveVenta;
|
|
|
|
DELETE V
|
|
FROM dbo.Ventas AS V
|
|
INNER JOIN #VentasPrueba AS VP
|
|
ON VP.CveVenta = V.CveVenta;
|
|
|
|
SELECT
|
|
@@ROWCOUNT AS VentasQueSeEliminarian;
|
|
|
|
ROLLBACK TRANSACTION;
|
|
|
|
-- Cambiar únicamente a COMMIT TRANSACTION después de revisar la selección.
|