Skip to Content
author's profile photo Former Member
Former Member

Relacionar en SQL facturas de clientes vs notas de credito

Hola a todos que tal,

No soy muy diestro con SQL pero logro hacer consultas basicas, me estan solicitando un query que me muestre las facturas generadas en un periodo de tiempo establecido y saber si estas facturas estan pagadas o no y por cuanto, para esto realicé una consulta a nivel detalle de documento que me indica de una forma muy sencilla el numero de factura, el concepto en cada linea y si ya fue pagado, esto es sencillo ya que la tabla INV1 vienen esos datos. Pero de este Query lamentablemente me arroja tambien facturas que fueron canceladas con nota de credito, quisiera saberque tablas relacionan una factura y una nota de credito, para poder mostrarlo en mi Query. Anexo el query que realicé para mostrar facturas vs pagos:

SELECT ORCT.DocNum as NumPago,

ORCT.DocDate as Fecha_Pago,

ORCT.CardCode as Socio,

ORCT.DocTotal as Total_Pago,

OINV.DocNum as Factura_SAP,

OINV.DocDate as Fecha_Factura_SAP,

OITB.ItmsGrpNam as Grupo,

OITM.ItemName as Concepto,

INV1.Price as Precio_Concepto,

INV1.Quantity as Cantidad,

INV1.LineTotal as Importe,

INV1.VatSum as IVA,

INV1.PriceAfVAT as Total_Articulo,

RCT2.SumApplied as Pagado

FROM ORCT, RCT2, OINV, INV1, OITM, OITB, OITR, ITR1

WHERE ORCT.DocNum=RCT2.DocNum and RCT2.DocEntry=OINV.DocEntry and OITM.ItmsGrpCod=OITB.ItmsGrpCod AND

OINV.DocEntry=INV1.DocEntry and INV1.ItemCode=OITM.ItemCode

AND ORCT.DocDate between '01/03/2014 00:00:00:00' and '31/03/2014 23:59:59:59'

ORDER BY Oinv.DocNum ASC

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Jul 14, 2014 at 09:14 PM

    Hola Carlos,

    al parecer lo que necesitas es un informe de ventas (Facturas venta -vs- notas crédito), te dejo este Query, revisa los cruces de las tablas y los campos que necesitas para eliminar o añadir según tus necesidades, espero te sirva

    DECLARE @FECHAINI AS DATETIME

    DECLARE @FECHAFIN AS DATETIME

    SET DATEFORMAT DMY

    SET @FECHAINI ={?Fecha-inical}

    SET @FECHAFIN ={?Fecha-final}

    select

    I3.CardCode 'COD. S/N', I3.CardName 'NOMBRE S/N', I3.LicTradNum 'NIT S/N', I3.City'CIUDAD', I3.Phone1'TELEFONO',

    I3.Address'DIRECCION', I1.ItemCode'COD. ARTICULO', I1.Dscription'NOMBRE ARTICULO',

    I1.Quantity'CANTIDAD', I1.Price 'PRECIO', I1.Quantity*I1.Price 'TOTAL',I1.Currency, I3.SlpCode'COD. VENDEDOR',I4.SlpName'NOMBRE VENDEDOR'

    , I0.DocNum'No. DOCUMENTO', I0.NumAtCard'No REF. DEDUDOR'

    from OINV I0

    INNER JOIN INV1 I1 ON I0.DocEntry = I1.DocEntry

    INNER JOIN OADM I2 ON 1=1

    INNER JOIN OCRD I3 ON I0.CardCode = I3.CardCode

    INNER JOIN OSLP I4 ON I0.SlpCode = I4.SlpCode

    INNER JOIN OITM I5 ON I1.ItemCode = I5.ItemCode

    WHERE I0.DocSubType = '--'

    AND I0.[DocDate] Between @FECHAINI AND @FECHAFIN

    UNION ALL

    SELECT

    I3.CardCode, I3.CardName, I3.LicTradNum, I3.City, I3.Phone1, I3.Address, I1.ItemCode, I1.Dscription,

    -I1.Quantity, -I1.Price, -I1.Quantity*I1.Price 'TOTAL',I1.Currency,I3.SlpCode,I4.SlpName,I0.DocNum, I0.NumAtCard

    FROM ORIN I0

    INNER JOIN RIN1 I1 ON I0.DocEntry = I1.DocEntry

    INNER JOIN OADM I2 ON 1=1

    INNER JOIN OCRD I3 ON I0.CardCode = I3.CardCode

    INNER JOIN OSLP I4 ON I0.SlpCode = I4.SlpCode

    INNER JOIN OITM I5 ON I1.ItemCode = I5.ItemCode

    WHERE I0.[DocDate] Between @FECHAINI AND @FECHAFIN

    Saludos,

    Iván Fernando Vanegas

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Carlos, que bueno que te funciono te sugiero en tus consultas trata de usar "alias" para que no tengas confusiones y estas no salgan tan extensas.

      No olvides, cerrar la discusión marcándola como respondida para que otras personas se puedan guiar de la solución.

      Saludos,

      Iván Fernando Vanegas

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.