Consultas SQL - Facturación.

 

Quien creó y quien vendió una factura:

DECLARE @serie String = 'A';
DECLARE @folio Long = 1;
SELECT A.usuarioCreo, F.usuarioVendedor
FROM DbFactura AS F
INNER JOIN DbFactura_A AS A ON A.empresa = F.empresa AND A.sucursal = F.sucursal AND A.serieFactura = F.serieFactura AND A.folioFactura = F.folioFactura
WHERE F.empresa = @empresa AND F.sucursal = @sucursal AND F.serieFactura = @serie AND F.folioFactura = @folio

Quien creó y modificó un depósito:

DECLARE @folio Long = 1;
SELECT usuarioCreo, usuarioModifico
FROM DbDeposito
WHERE empresa = @empresa AND sucursal = @sucursal AND folioDeposito = @folio

Consulta de facturas:

DECLARE @mes Long = 201609;
DECLARE @moneda String = 'MXN';
SELECT F.folioPedido AS Pedido, F.serieFactura AS Serie, F.folioFactura AS Folio, F.razonSocial AS RazónSocial, RATING(F.estatus;;Sello Pendiente;Pendiente;Cancelada;Pagada) AS Estatus, RATING(F.tipoContable;;Ingreso;Egreso;Traslado) AS Tipo, F.fechaFactura AS Fecha, F.fechaPagoProbable AS ProbablePago, F.importeTotal AS ImporteTotal, F.saldo AS Saldo, F.usuarioVendedor AS Vendedor, F.cliente AS NoCliente, F.clasificacion AS Clasificacion
FROM DbFactura AS F
WHERE F.empresa = @empresa AND F.sucursal = @sucursal AND F.mes = @mes AND $F.moneda = @moneda
TAMPAG 100

Reporte de facturación por Mes:

DECLARE @mes Long = 201609;
SELECT F.serieFactura AS Serie, F.folioFactura AS Folio, F.fechaFactura AS Fecha, F.razonSocial AS [Razón social], FA.montoBase AS Subtotal, FA.montoDescuento AS Descuento, FA.montoIVA AS IVA, F.importeTotal AS Importe, RATING(F.estatus;;Sello Pendiente;Pendiente;Cancelada;Pagada) AS Estatus, RATING(F.tipoContable;;Ingreso;Egreso;Traslado) AS Tipo, F.uuid AS UUID, F.moneda AS Moneda, FA.paridad AS Paridad
FROM DbFactura AS F
INNER JOIN DbFactura_A AS FA ON FA.empresa = F.empresa AND FA.sucursal = F.sucursal AND FA.serieFactura = F.serieFactura AND FA.folioFactura = F.folioFactura
WHERE F.empresa = @empresa AND F.sucursal = @sucursal AND F.mes = @mes

TAMPAG 100


Ventas por Mes:

DECLARE @mes Long = 201610;
SELECT F.empresa AS Empresa, F.sucursal AS Sucursal, F.serieFactura AS Serie, F.folioFactura AS Folio, F.moneda AS Moneda, D.renglon AS Renglón, D.producto AS Producto, D.descripcion AS Descripción, D.cantidad AS Cantidad, D.costoPromedio AS [Costo promedio], D.montoIEPS as IEPS, D.precio AS Precio, D.subTotal AS SubTotal, A.montoDescuento AS [Descuento global], F.fechaFactura AS Fecha, F.cliente AS IdCliente, F.razonSocial AS Cliente, F.usuarioVendedor AS Vendedor,RATING(F.estatus;;Sello Pendiente;Pendiente;Cancelada;Pagada) AS Estatus, D.cancelada AS Cancelada, F.diaCancelada AS [Día cancelada], RATING(F.tipoContable;;Ingreso;Egreso;Traslado) AS Tipo, A.paridad AS Paridad, A.rfcCliente AS [RFC cliente], C.codigoAuxiliar AS [CodAux cliente], C.nombreComercial AS [Nombre comercial], D.unidad AS Unidad, D.lineaProducto AS [Linea de producto], P.marca AS Marca, P.tipoProducto AS [Tipo producto], P.grupoProducto AS [Grupo producto], CA.zona AS Zona, CA.clasificacion AS Clasificación, P.precioUltimaCompra AS [Precio última compra]
FROM DbFactura AS F
INNER JOIN DbFactura_A AS A ON A.empresa = F.empresa AND A.sucursal = F.sucursal AND A.serieFactura = F.serieFactura AND A.folioFactura = F.folioFactura
INNER JOIN DbFacturaDet AS D ON D.empresa = F.empresa AND D.sucursal = F.sucursal AND D.serieFactura = F.serieFactura AND D.folioFactura = F.folioFactura
INNER JOIN DbCliente AS C ON C.empresa = F.empresa AND C.cliente = F.cliente
INNER JOIN DbCliente_A AS CA ON CA.empresa = F.empresa AND CA.cliente = F.cliente
INNER JOIN DbProducto AS P ON P.empresa = D.empresa AND P.producto = D.producto
WHERE F.empresa = @empresa AND F.sucursal = @sucursal AND F.mes = @mes
TAMPAG 100