Se modifica la vista de TIMBRADO.
hay que ejecutarlo en todas las bases
USE [Retail One]
GO
/** Object: View [dbo].[SO1_VI_01FACTURASTIMBRADO] Script Date: 11/07/2019 10:15:07 a. m. **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[SO1_VI_01FACTURASTIMBRADO]
AS
/-FACTURAS---/ SELECT CONVERT(BIT, 1) AS Seleccion, 'Factura' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado,
ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo
FROM [@SO1_01VENTA] A JOIN
[@SO1_01FACTURA] B ON (A.Name = B.U_SO1_FOLIOVENTA) LEFT JOIN
OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE)
WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO IN ('CA', 'AN', 'FA') AND A.U_SO1_PAGOCONTRAENT = 'N' AND ISNULL(A.U_SO1_FOLIODESTINO, '') NOT LIKE '__RF%'
UNION
/-NO FACTURA TIMBRADO---/ SELECT CONVERT(BIT, 1) AS Seleccion, 'Factura' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe,
'' AS Estado, ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo
FROM [@SO1_01VENTA] A JOIN
[@SO1_01FACTURA] B ON (A.Name = B.U_SO1_FOLIOVENTA) LEFT JOIN
OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE)
WHERE A.U_SO1_TIMANTICINOFAC = 'Y' AND A.U_SO1_STATUS <> 'C' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO IN ('AN')
UNION
SELECT CONVERT(BIT, 1) AS Seleccion, 'Factura' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado,
ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo
FROM [@SO1_01VENTA] A JOIN
[@SO1_01FACTURA] B ON (A.Name = B.U_SO1_FOLIOCREDITO) LEFT JOIN
OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE)
WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND B.U_SO1_STATUS != 'P' AND A.U_SO1_TIPO IN ('CR')
UNION
/-REFACTURACION---/ SELECT CONVERT(BIT, 1) AS Seleccion, 'Refacturación' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe,
'' AS Estado, ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo
FROM [@SO1_01VENTA] A JOIN
[@SO1_01FACTURA] B ON (A.Name = B.U_SO1_FOLIOVENTA) LEFT JOIN
OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE)
WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO IN ('RF')
UNION
/-NOTAS CREDITO---/ SELECT CONVERT(BIT, 1) AS Seleccion, 'Nota de Credito' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe,
'' AS Estado, ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo
FROM [@SO1_01DEVOLUCION] A JOIN
[@SO1_01NOTACREDITO] B ON (A.Name = B.U_SO1_FOLIODEVOL) LEFT JOIN
OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE)
WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO = 'DE'
UNION
SELECT CONVERT(BIT, 1) AS Seleccion, 'Nota de Credito' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado,
ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo
FROM [@SO1_01VENTA] A JOIN
[@SO1_01NOTACREDITO] B ON (A.Name = B.U_SO1_FOLIODEVOL) LEFT JOIN
OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE) LEFT JOIN
[@SO1_01VENTA] D ON (D .Name = A.U_SO1_FOLIODESTINO)
WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO IN ('NC', 'DD', 'AA', 'NR') AND ISNULL(D .U_SO1_PAGOCONTRAENT, 'N')
= 'N'
UNION
/-PAGOS RECIBIDOS---/ SELECT CONVERT(BIT, 1) AS Seleccion, 'Pago recibido' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe,
'' AS Estado, ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo
FROM [@SO1_01VENTA] A JOIN
[@SO1_01FACTURA] B ON (A.Name = B.U_SO1_FOLIOVENTA AND B.U_SO1_CFDIMETODOPAGO = 'PUE') LEFT JOIN
OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE)
WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO = 'CC' AND A.Name NOT IN
(SELECT C.U_SO1_FOLIO
FROM [@SO1_01COBRANZADET] C JOIN
[@SO1_01FACTURA] F ON (C.U_SO1_FOLIOOPERACION = F.U_SO1_FOLIOCREDITO) AND F.U_SO1_CFDIMETODOPAGO = 'PUE')
UNION
SELECT CONVERT(BIT, 1) AS Seleccion, 'Nota de Credito' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado,
ISNULL(D .E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo
FROM [@SO1_01DEVOLUCION] A JOIN
[@SO1_01NOTACREDITO] B ON (A.Name = B.U_SO1_FOLIODEVOL) JOIN
[@SO1_01VENTA] C ON (A.U_SO1_FOLIOCAJA = C.Name) LEFT JOIN
OCRD D ON (B.U_SO1_CODIGOCLIENTE = D .CARDCODE)
WHERE ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO = 'CP' AND C.U_SO1_TIMANTICINOFAC = 'Y'
UNION
SELECT CONVERT(BIT, 1) AS Seleccion, 'Nota de Credito' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado,
ISNULL(D .E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo
FROM [@SO1_01DEVOLUCION] A JOIN
[@SO1_01NOTACREDITO] B ON (A.Name = B.U_SO1_FOLIODEVOL) JOIN
[@SO1_01VENTA] C ON (A.U_SO1_FOLIOCAJA = C.Name) LEFT JOIN
OCRD D ON (B.U_SO1_CODIGOCLIENTE = D .CARDCODE)
WHERE ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO = 'CP' AND C.U_SO1_TIMANTICINOFAC = 'N'
/-Traspasos---/ UNION
SELECT CONVERT(BIT, 1) AS Seleccion, 'Traspaso' AS TipoDocumento, A.U_SO1_FOLIO AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, 0 AS Importe, '' AS Estado, '' AS Correo, 'TR' AS Tipo
FROM [@SO1_01TRASPASO] A JOIN
[@SO1_01TRASPASOTIMBR] B ON (A.U_SO1_FOLIO = B.Name)
WHERE ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO IN ('S', 'X')
/-Venta Domicilio-/ UNION
SELECT CONVERT(BIT, 1) AS Seleccion, 'Factura' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado,
ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo
FROM [@SO1_01VENTA] A JOIN
[@SO1_01FACTURA] B ON (A.Name = B.U_SO1_FOLIOMOSTRADOR) LEFT JOIN
OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE)
WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO IN ('DO')
GO
Se modifica la vista de TIMBRADO. hay que ejecutarlo en todas las bases
USE [Retail One] GO
/** Object: View [dbo].[SO1_VI_01FACTURASTIMBRADO] Script Date: 11/07/2019 10:15:07 a. m. **/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
ALTER VIEW [dbo].[SO1_VI_01FACTURASTIMBRADO] AS /-FACTURAS---/ SELECT CONVERT(BIT, 1) AS Seleccion, 'Factura' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado, ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo FROM [@SO1_01VENTA] A JOIN [@SO1_01FACTURA] B ON (A.Name = B.U_SO1_FOLIOVENTA) LEFT JOIN OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE) WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO IN ('CA', 'AN', 'FA') AND A.U_SO1_PAGOCONTRAENT = 'N' AND ISNULL(A.U_SO1_FOLIODESTINO, '') NOT LIKE '__RF%' UNION /-NO FACTURA TIMBRADO---/ SELECT CONVERT(BIT, 1) AS Seleccion, 'Factura' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado, ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo FROM [@SO1_01VENTA] A JOIN [@SO1_01FACTURA] B ON (A.Name = B.U_SO1_FOLIOVENTA) LEFT JOIN OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE) WHERE A.U_SO1_TIMANTICINOFAC = 'Y' AND A.U_SO1_STATUS <> 'C' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO IN ('AN') UNION SELECT CONVERT(BIT, 1) AS Seleccion, 'Factura' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado, ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo FROM [@SO1_01VENTA] A JOIN [@SO1_01FACTURA] B ON (A.Name = B.U_SO1_FOLIOCREDITO) LEFT JOIN OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE) WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND B.U_SO1_STATUS != 'P' AND A.U_SO1_TIPO IN ('CR') UNION /-REFACTURACION---/ SELECT CONVERT(BIT, 1) AS Seleccion, 'Refacturación' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado, ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo FROM [@SO1_01VENTA] A JOIN [@SO1_01FACTURA] B ON (A.Name = B.U_SO1_FOLIOVENTA) LEFT JOIN OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE) WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO IN ('RF') UNION /-NOTAS CREDITO---/ SELECT CONVERT(BIT, 1) AS Seleccion, 'Nota de Credito' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado, ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo FROM [@SO1_01DEVOLUCION] A JOIN [@SO1_01NOTACREDITO] B ON (A.Name = B.U_SO1_FOLIODEVOL) LEFT JOIN OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE) WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO = 'DE' UNION SELECT CONVERT(BIT, 1) AS Seleccion, 'Nota de Credito' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado, ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo FROM [@SO1_01VENTA] A JOIN [@SO1_01NOTACREDITO] B ON (A.Name = B.U_SO1_FOLIODEVOL) LEFT JOIN OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE) LEFT JOIN [@SO1_01VENTA] D ON (D .Name = A.U_SO1_FOLIODESTINO) WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO IN ('NC', 'DD', 'AA', 'NR') AND ISNULL(D .U_SO1_PAGOCONTRAENT, 'N') = 'N' UNION /-PAGOS RECIBIDOS---/ SELECT CONVERT(BIT, 1) AS Seleccion, 'Pago recibido' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado, ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo FROM [@SO1_01VENTA] A JOIN [@SO1_01FACTURA] B ON (A.Name = B.U_SO1_FOLIOVENTA AND B.U_SO1_CFDIMETODOPAGO = 'PUE') LEFT JOIN OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE) WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO = 'CC' AND A.Name NOT IN (SELECT C.U_SO1_FOLIO FROM [@SO1_01COBRANZADET] C JOIN [@SO1_01FACTURA] F ON (C.U_SO1_FOLIOOPERACION = F.U_SO1_FOLIOCREDITO) AND F.U_SO1_CFDIMETODOPAGO = 'PUE') UNION SELECT CONVERT(BIT, 1) AS Seleccion, 'Nota de Credito' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado, ISNULL(D .E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo FROM [@SO1_01DEVOLUCION] A JOIN [@SO1_01NOTACREDITO] B ON (A.Name = B.U_SO1_FOLIODEVOL) JOIN [@SO1_01VENTA] C ON (A.U_SO1_FOLIOCAJA = C.Name) LEFT JOIN OCRD D ON (B.U_SO1_CODIGOCLIENTE = D .CARDCODE) WHERE ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO = 'CP' AND C.U_SO1_TIMANTICINOFAC = 'Y' UNION SELECT CONVERT(BIT, 1) AS Seleccion, 'Nota de Credito' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado, ISNULL(D .E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo FROM [@SO1_01DEVOLUCION] A JOIN [@SO1_01NOTACREDITO] B ON (A.Name = B.U_SO1_FOLIODEVOL) JOIN [@SO1_01VENTA] C ON (A.U_SO1_FOLIOCAJA = C.Name) LEFT JOIN OCRD D ON (B.U_SO1_CODIGOCLIENTE = D .CARDCODE) WHERE ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO = 'CP' AND C.U_SO1_TIMANTICINOFAC = 'N' /-Traspasos---/ UNION SELECT CONVERT(BIT, 1) AS Seleccion, 'Traspaso' AS TipoDocumento, A.U_SO1_FOLIO AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, 0 AS Importe, '' AS Estado, '' AS Correo, 'TR' AS Tipo FROM [@SO1_01TRASPASO] A JOIN [@SO1_01TRASPASOTIMBR] B ON (A.U_SO1_FOLIO = B.Name) WHERE ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO IN ('S', 'X') /-Venta Domicilio-/ UNION SELECT CONVERT(BIT, 1) AS Seleccion, 'Factura' AS TipoDocumento, A.Name AS FolioDoc, CONVERT(Varchar(10), A.U_SO1_FECHA, 103) AS Fecha, A.U_SO1_TOTALNETOMONED AS Importe, '' AS Estado, ISNULL(C.E_MAIL, '') AS Correo, A.U_SO1_TIPO AS Tipo FROM [@SO1_01VENTA] A JOIN [@SO1_01FACTURA] B ON (A.Name = B.U_SO1_FOLIOMOSTRADOR) LEFT JOIN OCRD C ON (B.U_SO1_CODIGOCLIENTE = C.CARDCODE) WHERE A.U_SO1_FACTURA = 'Y' AND ISNULL(B.U_SO1_FOLIOOPER, '') = '' AND ISNULL(B.U_SO1_FECHA, '') > '20140101' AND A.U_SO1_TIPO IN ('DO') GO