USE [dbmov]
GO
/****** Object:  StoredProcedure [dbo].[paQryFacturasFmt]    Script Date: 16/02/2021 8:06:42 ******/
DROP PROCEDURE [dbo].[paQryFacturasFmt]
GO
/****** Object:  StoredProcedure [dbo].[paQryFacturasFmt]    Script Date: 16/02/2021 8:06:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************************
-- =============================================
-- Author:	Heiman Daza
-- Create date(dd/MM/yyyy): 16/08/2018
-- Description:	Impresión de factura a crédito de transporte para facturación electrónica de syscom 30

* History ------------------------------------------------------------------------
* Author           	|Date        	|Version	|Reason
HEDINVER		2018/11/16		1			Se agregaron campos que hacian falta y se renombraron para facilitar el manejo
HEDINVER		2018/11/27		2			Se agregaron campos Orden Cargue, Retecree, Placa
HEDINVER		2020/03/01		3			Se agrega campo de centro de costo al detalle
HEDINVER		2020/05/09		4			Se agregan campos (VrAutRetICA,VrAutRetFte)
HEDINVER		2020/06/05		5			Se agregan campos de remitente y destinatario
HEDINVER		2020/09/15		6			Se agregan campos de tasas para conversion a dolares en el formato
HEDINVER		2020/09/17		7			Se agrega tabla vehiculos y Nit para traer el conductor
HEDINVER		2020/10/16		8			Se agrega tabla TIPOSVEH para traer el tipo de vehiculo
HEDINVER		2020/11/10		9			Se agregan campos de agencia 
HEDINVER		2020/11/21		10			Se agrega campo (CON_CFE AS CodigoFE) para indicar el codigo del concepto de facturacion
-- =============================================
***********************************************************************************/
CREATE PROCEDURE [dbo].[paQryFacturasFmt] @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2)
 AS 
SELECT FACTURAS.FAC_TIP AS TipoFact,FACTURAS.FAC_NUM AS NumFact,FACTURAS.FAC_CIA AS CiaFact,FACTURAS.FAC_FEC AS Fecha,FACTURAS.FAC_VCE AS FechaVence
,INFDOC.DOC_FEC AS FecElabora,    /*NUMRAD.IngresoId AS NumRadicadoRNDC,*/     FACTURAS.FAC_PED AS Pedido,FACTURAS.FAC_REM AS Remision,FACTURAS.FAC_VAL AS SubTotal,FACTURAS.FAC_IVA AS VrIva,FACTURAS.FAC_DCT AS VrDescuento
,FACTURAS.FAC_ARI AS VrAutRetICA,FACTURAS.FAC_ARF AS VrAutRetFte,FACTURAS.FAC_RET AS VrRet,FACTURAS.FAC_ICA AS VrIca,FACTURAS.FAC_FLE AS VrFletes
,FACTURAS.FAC_FAL AS Faltantes,FACTURAS.FAC_NET AS VrNeto,FACTURAS.FAC_RIV AS VrReteIva,TIPOS_DOC.TIP_PRE AS Prefijo,INFDOC.DOC_MUL AS MultiplesPlazos
,INFDOC.DOC_OBS AS Observacion,INFDOC.DOC_BAS AS BaseIva,INFDOC.DOC_BA1 AS BaseRetfte,INFDOC.DOC_BA2 AS Base2,INFDOC.DOC_POR AS PorcentajeRet
,INFDOC.DOC_PO1 AS PorcentajeIca, FACTURAS.FAC_RTC AS VrReteCree,FACTURAS.FAC_TRC AS TarifaCree, FACTURAS.FAC_ICO AS VrImpConsumo, FACTURAS.FAC_COS CostoFac
--Información del cliente
,NIT.NIT_NIT AS IdCliente,NIT.NIT_NOM AS NomCliente,NIT.NIT_DV AS DvCliente,NIT.NIT_DIR AS DireccionCliente,NIT.NIT_TEL AS TelefonoCliente
,CIUDADES_2.CIU_DES AS CiuCliente
--Agencia -- 10-11-2020
,AG.AGE_NOM AS Agencia, AG.AGE_COD IdAgencia, AG.AGE_DIR DirAgencia, AG.AGE_EML AgeEmail, CA.CIU_DES AgeCiudad,AG.AGE_TEL AgeTelefono
--Información del Vendedor
,NIT_1.NIT_NIT AS IdVendedor,NIT_1.NIT_NOM AS NomVendedor
-- Remitente y Destinatario
,TR.NIT_NOM NomRemitente, TR.NIT_DIR DirRemitente,TR.NIT_TEL TelRemitente,TR.NIT_TLC CelRemitente,TD.NIT_NOM NomDestinrio,TD.NIT_DIR DirDestinrio
,TD.NIT_TEL TelDestinrio,TD.NIT_TLC CelDestinrio
--detales
,DM.DET_ITM AS Item, DM.DET_ORD AS Remesa, DM.DET_COD AS CiaRemesa, DM.DET_LIN AS LineaRem,DM.DET_FDC AS FechaRem
,DM.DET_DES AS NombreProducto,DM.DET_CAN AS Volumen,DM.DET_PES AS Peso,DM.DET_REF AS Referencia,DM.DET_RF1 AS Referencia2
,DM.DET_RF3 AS Referencia3,DM.DET_VAL AS TarifaCobro,DM.DET_PED AS PedidoDet,INFDOC.DOC_CMP AS TipoComprobante,INFDOC.DOC_NCP AS 
NumeroComprobante, 
INFDOC.DOC_OBS AS NumeroRadicadoRNDC,   
INFDOC.DOC_DEV AS Devuelto,INFDOC.DOC_NDV AS NumeroDevolucion,CONDPAGO.CDP_PLA AS Plazo,CIUDADES_1.CIU_DES AS CiuDestino
,CIUDADES.CIU_DES AS CiuOrigen,DM.DET_ORI IdCiuOrigen,REMESAS.REM_PLA AS Manifiesto,REMESAS.REM_CPL AS CiaMuc,PLATRANS.PLA_ODP AS OrdenPago
,REMESAS.REM_ORD AS OrdCargue, REMESAS.REM_COR AS CiaOrdCargue,DM.DET_VEH AS Vehiculo,CONCEPTOSFAC.CON_DES AS ConceptoFact
,CONCEPTOSFAC.CON_COD AS CdConFac,CONCEPTOSFAC.CON_IVA AS CodigoIva, TABLAPOR.POR_VAL AS TarIVA,DM.DET_IVA AS VrIVADet,CONCEPTOSFAC.CON_SER AS ConFacServ
,DM.DET_VRF AS ValorRetDet,DM.DET_PRF AS PorRetDet, DM.DET_CPR AS CodRetDet, DM.DET_CC IDCCosto, CC.CCT_DES CC_Descrp, U.UNI_DES AS Unidades
,T.TAS_VAL VrDolar, T.TAS_FEC FecDolar, NIT_2.NIT_NOM Conductor, CONCEPTOSFAC.CON_CFE AS CodigoFE, TV.TIP_DES TipoVehiculo, DM.DET_COS AS TarifaPago
--Factura Electronica
,Trn_Face.Prefijo AS PrefijoFace,Trn_Face.NumFace,Trn_Face.CUFE,Trn_Face.CUFE_QR,Trn_Face.Resolucion,Trn_Face.RangoNum,Trn_Face.FecVigencia, Trn_Face.FechaValidacion
,USUARIOS.USU_NOM AS Usuario
FROM dbmov.dbo.FACTURAS FACTURAS INNER JOIN dbpar.dbo.TIPOS_DOC TIPOS_DOC ON FACTURAS.FAC_TIP = TIPOS_DOC.TIP_COD
INNER JOIN dbmov.dbo.DETMOV DM ON FACTURAS.FAC_TIP = DM.DET_TIP AND FACTURAS.FAC_NUM = DM.DET_NUM AND FACTURAS.FAC_CIA = DM.DET_CIA
LEFT JOIN DBMOV.dbo.REMESAS REMESAS ON DM.DET_ORD = REMESAS.REM_NUM AND DM.DET_TDC = REMESAS.REM_TIP AND DM.DET_COD = REMESAS.REM_CIA
INNER JOIN dbpar.dbo.NIT NIT ON FACTURAS.FAC_CLI = NIT.NIT_NIT
INNER JOIN dbpar.dbo.NIT NIT_1 ON FACTURAS.FAC_VEN = NIT_1.NIT_NIT
LEFT JOIN dbpar.dbo.AGENCIAS AG ON FACTURAS.FAC_AGE = AG.AGE_COD AND FACTURAS.FAC_CLI = AG.AGE_NIT
INNER JOIN dbmov.dbo.INFDOC INFDOC ON FACTURAS.FAC_TIP = INFDOC.DOC_TIP AND FACTURAS.FAC_NUM = INFDOC.DOC_NUM AND FACTURAS.FAC_CIA = INFDOC.DOC_CIA
INNER JOIN dbpar.dbo.CIUDADES CIUDADES_1 ON DM.DET_CDE = CIUDADES_1.CIU_COD
INNER JOIN DBPAR.dbo.CIUDADES CIUDADES ON DM.DET_ORI = CIUDADES.CIU_COD
INNER JOIN DBACC.dbo.USUARIOS USUARIOS ON INFDOC.DOC_USU = USUARIOS.USU_ID
INNER JOIN dbpar.dbo.CONDPAGO CONDPAGO ON INFDOC.DOC_PAG = CONDPAGO.CDP_NUM
INNER JOIN dbpar.dbo.CIUDADES CIUDADES_2 ON NIT.NIT_CIU = CIUDADES_2.CIU_COD
INNER JOIN dbpar.dbo.CONCEPTOSFAC CONCEPTOSFAC ON DM.DET_CON = CONCEPTOSFAC.CON_COD
LEFT JOIN dbpar.dbo.TABLAPOR TABLAPOR ON CONCEPTOSFAC.CON_IVA=TABLAPOR.POR_COD
LEFT JOIN DBMOV.dbo.PLATRANS PLATRANS ON REMESAS.REM_PLA = PLATRANS.PLA_NUM AND REMESAS.REM_CPL = PLATRANS.PLA_CIA
LEFT JOIN DBMOV.dbo.Trn_Face Trn_Face ON FACTURAS.FAC_TIP = Trn_Face.TipDoc AND FACTURAS.FAC_NUM = Trn_Face.Documento AND FACTURAS.FAC_CIA = Trn_Face.IdCia
LEFT JOIN DBPAR.dbo.CENTROCOSTO CC ON DM.DET_CC=CC.CCT_COD --Se agrega CC en el detalle 01-03-2020
LEFT JOIN DBPAR.dbo.NIT TR ON DM.DET_REM=TR.NIT_NIT LEFT JOIN DBPAR.dbo.NIT TD ON DM.DET_DTN=TD.NIT_NIT --Se agregan tablas terceros rem y des 05-06-2020
LEFT JOIN DBPAR.dbo.UNIDADES U ON DM.DET_UNI=U.UNI_COD --Se agrega tabla de Unidades
LEFT JOIN DBPAR.dbo.Tasas T ON FACTURAS.FAC_FEC=T.TAS_FEC --Se agrega tabla de tasas
LEFT JOIN DBPAR.dbo.VEHICULOS V ON DM.DET_VEH=V.VEH_PLA LEFT JOIN dbpar.dbo.NIT NIT_2 ON V.VEH_CON=NIT_2.NIT_NIT --Se agrega tabla vehiculos y Nit para traer el conductor
LEFT JOIN dbpar.dbo.TIPOSVEH TV ON V.VEH_TIP=TV.TIP_COD --16-10-2020 Se agrega tabla TIPOSVEH
LEFT JOIN DBPAR.dbo.CIUDADES CA ON AG.AGE_CIU=CA.CIU_COD -- Ciudad de la agencia -10-11-2020,

--LEFT JOIN DBPAR.dbo.CIUDADES CA ON AG.AGE_CIU=CA.CIU_COD -- Ciudad de la agencia -10-11-2020,

WHERE FACTURAS.FAC_TIP=@pmTipDoc AND FACTURAS.FAC_NUM BETWEEN @pmFacturaIni AND @pmFacturaFin AND FACTURAS.FAC_CIA=@pmIdCia
ORDER BY FACTURAS.FAC_CIA,FACTURAS.FAC_NUM,DM.DET_ITM
GO
