USE [dbmov]
GO
/****** Object:  StoredProcedure [dbo].[paQryNotas_Cr]    Script Date: 16/02/2021 8:06:42 ******/
DROP PROCEDURE [dbo].[paQryNotas_Cr]
GO
/****** Object:  StoredProcedure [dbo].[paQryNotas_Cr]    Script Date: 16/02/2021 8:06:42 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/**********************************************************************************
-- =============================================
-- Author:	Heiman Daza -- Create date(dd/MM/yyyy): 16/08/2018
-- Description:	Impresión de notas para facturación electrónica de syscom 30
* History ------------------------------------------------------------------------
* Author           	|Date        	|Version	|Reason
HEDINVER		16/08/2018			1			Impresión de factura a crédito para facturación electrónica de syscom 30
HEDINVER		19/11/2018			2			Se agregan alias a los campos
HEDINVER		06/12/2018			3			Se agregan campos de FACE
HEDINVER		25/11/2020			4			Se agregan campos de cliente y agencias
HEDINVER		28/12/2020			5			Se agregan campos de factura relacionada(FecValidaFACT,CufeFcr)
-- =============================================
***********************************************************************************/
CREATE PROCEDURE [dbo].[paQryNotas_Cr] @pmTipDoc VARCHAR(3),@pmNumNotaIni INT,@pmNumNotaFin INT,@pmIdCia CHAR(2)
 AS 
SELECT NOTAS.NOT_TIP AS TipDocumento,NOTAS.NOT_NUM AS NumDocumento,NOTAS.NOT_CIA AS CiaDocumento,NOTAS.NOT_FEC AS FechaDoc,NOTAS.NOT_CON AS CodConcepto
,NOTAS.NOT_CLI AS NitCliente,NOTAS.NOT_VCE AS FechaVence,NOTAS.NOT_REF AS Referencia,NOTAS.NOT_TDC AS TipoDoc,NOTAS.NOT_DOC AS NroDoc
,NOTAS.NOT_CDC AS CiaDoc,NOTAS.NOT_VAL AS Valor,NOTAS.NOT_IVA AS VrIva,NOTAS.NOT_RET AS VrReteFte,NOTAS.NOT_NET AS VrNeto,NOTAS.NOT_CMS AS PorComision
,NOTAS.NOT_SAN AS VrSancion,CONCEPTOS.CON_DES AS Concepto,CIUDADES_1.CIU_DES AS Ciudad,NIT_1.NIT_NOM AS Vendedor
,TIPOS_DOC.TIP_DES AS DescDocumento,INFDOC.DOC_CMP AS TipComprobante,INFDOC.DOC_NCP AS NroComprobante,INFDOC.DOC_DEV AS Devuelto
,INFDOC.DOC_NDV AS NroDevolucion,INFDOC.DOC_MUL AS MultiplesPagos,INFDOC.DOC_OBS AS Observacion
--Cliente 25-11-2020
	,NIT.NIT_NIT AS NitCliente,NIT.NIT_NOM AS NomCliente,NIT.NIT_DV AS DigVerificacion,NIT.NIT_DIR AS DirCliente
	,NIT.NIT_TEL AS TelCliente,NIT_1.NIT_NIT AS NitVendedor,NIT_1.NIT_NOM AS NomVendedor,CIUDADES_1.CIU_DES AS CiuCliente, NIT.NIT_EML AS EmailCliente
--Agencias 25-11-2020
	,AGENCIAS.AGE_NOM AS NomAgencia, AGENCIAS.AGE_DIR AgeDireccion, AGENCIAS.AGE_TEL AgeTelefono, AGENCIAS.AGE_EML AgeEmail, CA.CIU_DES AgeCiudad
--Contable
,MOVIMIENTO.MOV_CUE AS CodCuenta,MOVIMIENTO.MOV_DET AS NomCuenta,MOVIMIENTO.MOV_DEB AS VrDebito,MOVIMIENTO.MOV_CRE AS VrCredito,CONDPAGO.CDP_PLA 
AS Plazo,USUARIOS.USU_NOM AS Usuario
--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
,TFF.Prefijo AS PrefNot, TFF.NumFace AS NumNota,TFF.CUFE CufeFcr,TFF.FechaValidacion FecValidaFACT
FROM dbmov.dbo.NOTAS NOTAS LEFT JOIN dbpar.dbo.CONCEPTOS CONCEPTOS ON NOTAS.NOT_CON = CONCEPTOS.CON_COD
LEFT JOIN dbpar.dbo.CIUDADES CIUDADES_1 ON NOTAS.NOT_CIU = CIUDADES_1.CIU_COD
LEFT JOIN dbpar.dbo.NIT NIT ON NOTAS.NOT_CLI = NIT.NIT_NIT
LEFT JOIN dbpar.dbo.NIT NIT_1 ON NOTAS.NOT_VEN = NIT_1.NIT_NIT
LEFT JOIN dbpar.dbo.TIPOS_DOC TIPOS_DOC ON NOTAS.NOT_TIP = TIPOS_DOC.TIP_COD
LEFT JOIN dbmov.dbo.INFDOC INFDOC ON NOTAS.NOT_TIP = INFDOC.DOC_TIP AND NOTAS.NOT_NUM = INFDOC.DOC_NUM AND NOTAS.NOT_CIA = INFDOC.DOC_CIA
LEFT JOIN dbpar.dbo.AGENCIAS AGENCIAS ON NOTAS.NOT_AGE = AGENCIAS.AGE_COD AND NOTAS.NOT_CLI = AGENCIAS.AGE_NIT
LEFT JOIN DBPAR.dbo.CIUDADES CA ON AGENCIAS.AGE_CIU=CA.CIU_COD -- Ciudad de la agencia 25-11-2020
LEFT JOIN dbcon.dbo.MOVIMIENTO MOVIMIENTO ON INFDOC.DOC_CMP = MOVIMIENTO.MOV_TIP AND INFDOC.DOC_NCP = MOVIMIENTO.MOV_NUM AND INFDOC.DOC_CIA = MOVIMIENTO.MOV_CIA
LEFT JOIN dbpar.dbo.CONDPAGO CONDPAGO ON INFDOC.DOC_PAG = CONDPAGO.CDP_NUM
LEFT JOIN dbacc.dbo.USUARIOS USUARIOS ON INFDOC.DOC_USU = USUARIOS.USU_ID
LEFT JOIN DBMOV.dbo.Trn_Face Trn_Face ON NOTAS.NOT_TIP = Trn_Face.TipDoc AND NOTAS.NOT_NUM = Trn_Face.Documento AND NOTAS.NOT_CIA = Trn_Face.IdCia
LEFT JOIN DBMOV.dbo.Trn_Face TFF ON NOTAS.NOT_TDC = TFF.TipDoc AND NOTAS.NOT_DOC = TFF.Documento AND NOTAS.NOT_CDC = TFF.IdCia
 WHERE NOTAS.NOT_TIP=@pmTipDoc AND NOTAS.NOT_NUM BETWEEN @pmNumNotaIni AND @pmNumNotaFin AND NOTAS.NOT_CIA=@pmIdCia
ORDER BY  NOTAS.NOT_CIA,NOTAS.NOT_NUM

GO
