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

Informe de pagos

Hola

Alguien que me pueda ayudar

tengo la siguiente consulta que me muestra el historial de pagos de un cliente

pero no he logrado sacar dos columnas mas a ese informe

una que me diga el salgo pendiente por pagar y otra que me diga el saldo ya vencido

Le agradezco mucho al que me pueda echar una manito en cualquier cosa

Gracias

DECLARE @FACTOR1 AS NVARCHAR(50)

DECLARE @FACTOR2 AS NVARCHAR(50)

DECLARE @FACTOR3 AS NVARCHAR(50)

DECLARE @FACTOR4 AS NVARCHAR(50)

DECLARE @FACTOR5 AS NVARCHAR(50)

DECLARE @FACTOR6 AS NVARCHAR(50)

SET @FACTOR1 = (SELECT F0.NAME FROM .[@Q_FACTORES_COMISION] F0 WHERE F0.CODE = '1')

SET @FACTOR2 = (SELECT F0.NAME FROM .[@Q_FACTORES_COMISION] F0 WHERE F0.CODE = '2')

SET @FACTOR3 = (SELECT F0.NAME FROM .[@Q_FACTORES_COMISION] F0 WHERE F0.CODE = '3')

SET @FACTOR4 = (SELECT F0.NAME FROM .[@Q_FACTORES_COMISION] F0 WHERE F0.CODE = '4')

SET @FACTOR5 = (SELECT F0.NAME FROM .[@Q_FACTORES_COMISION] F0 WHERE F0.CODE = '5')

SET @FACTOR6 = (SELECT F0.NAME FROM .[@Q_FACTORES_COMISION] F0 WHERE F0.CODE = '6')

DECLARE @PRECIO1 AS NVARCHAR(50)

DECLARE @PRECIO2 AS NVARCHAR(50)

DECLARE @PRECIO3 AS NVARCHAR(50)

DECLARE @PRECIO4 AS NVARCHAR(50)

DECLARE @PRECIO5 AS NVARCHAR(50)

DECLARE @PRECIO6 AS NVARCHAR(50)

SET @PRECIO1 = (SELECT H0.NAME FROM .[@Q_PRECIOS_COMISION] H0 WHERE H0.CODE = '1')

SET @PRECIO2 = (SELECT H0.NAME FROM .[@Q_PRECIOS_COMISION] H0 WHERE H0.CODE = '2')

SET @PRECIO3 = (SELECT H0.NAME FROM .[@Q_PRECIOS_COMISION] H0 WHERE H0.CODE = '3')

SET @PRECIO4 = (SELECT H0.NAME FROM .[@Q_PRECIOS_COMISION] H0 WHERE H0.CODE = '4')

SET @PRECIO5 = (SELECT H0.NAME FROM .[@Q_PRECIOS_COMISION] H0 WHERE H0.CODE = '5')

SET @PRECIO6 = (SELECT H0.NAME FROM .[@Q_PRECIOS_COMISION] H0 WHERE H0.CODE = '6')

DECLARE @PORCENTAJE1 AS NVARCHAR(50)

DECLARE @DIAINIC1 AS NVARCHAR(50)

DECLARE @DIAFIN1 AS NVARCHAR(50)

DECLARE @PORCENTAJE2 AS NVARCHAR(50)

DECLARE @DIAINIC2 AS NVARCHAR(50)

DECLARE @DIAFIN2 AS NVARCHAR(50)

DECLARE @PORCENTAJE3 AS NVARCHAR(50)

DECLARE @DIAINIC3 AS NVARCHAR(50)

DECLARE @DIAFIN3 AS NVARCHAR(50)

DECLARE @PORCENTAJE4 AS NVARCHAR(50)

DECLARE @DIAINIC4 AS NVARCHAR(50)

DECLARE @DIAFIN4 AS NVARCHAR(50)

DECLARE @PORCENTAJE5 AS NVARCHAR(50)

DECLARE @DIAINIC5 AS NVARCHAR(50)

DECLARE @DIAFIN5 AS NVARCHAR(50)

SET @PORCENTAJE1 = (SELECT G0.NAME FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '1')

SET @DIAINIC1 = (SELECT G0.U_DIAINIC FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '1')

SET @DIAFIN1 = (SELECT G0.U_DIAFIN FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '1')

SET @PORCENTAJE2 = (SELECT G0.NAME FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '2')

SET @DIAINIC2 = (SELECT G0.U_DIAINIC FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '2')

SET @DIAFIN2 = (SELECT G0.U_DIAFIN FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '2')

SET @PORCENTAJE3 = (SELECT G0.NAME FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '3')

SET @DIAINIC3 = (SELECT G0.U_DIAINIC FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '3')

SET @DIAFIN3 = (SELECT G0.U_DIAFIN FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '3')

SET @PORCENTAJE4 = (SELECT G0.NAME FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '4')

SET @DIAINIC4 = (SELECT G0.U_DIAINIC FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '4')

SET @DIAFIN4 = (SELECT G0.U_DIAFIN FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '4')

SET @PORCENTAJE5 = (SELECT G0.NAME FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '5')

SET @DIAINIC5 = (SELECT G0.U_DIAINIC FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '5')

SET @DIAFIN5 = (SELECT G0.U_DIAFIN FROM .[@Q_DIAS_COMISION] G0 WHERE G0.CODE = '5')

/SELECT FROM .[OINV] P1/

DECLARE @FECHA_INICIAL AS DATETIME

DECLARE @FECHA_FINAL AS DATETIME

/* WHERE */

SET DATEFORMAT DMY

SET @FECHA_INICIAL = '01/01/2008'

SET @FECHA_FINAL = GETDATE()

/SELECT FROM .[OCRD] P0/declare @SN1 as nvarchar(300)/* WHERE /set @SN1 = / P0.CardName */ '[%2]'

DECLARE @COMISIONES AS NVARCHAR(50)

DECLARE @USUARIO AS NVARCHAR(50)

SET @COMISIONES = 'SI'

SET @USUARIO = 'El Duro'

SELECT E0.DOCNUM, E0.SLPNAME, E0.CARDNAME, E0.TIPO,

F0.INSTLMNTID AS 'CUOTA', F0.DUEDATE AS 'FECHA_VENCIMIENTO',E2.FECHA_PAGO,(DATEDIFF(DAY,F0.DUEDATE,E2.FECHA_PAGO)) 'DIAS_MORA', F0.INSTOTAL

FROM

(

SELECT D0.DOCNUM, D0.SLPNAME, D0.CARDNAME, D0.INSTALLMNT, SUM(D0.TOTAL1) 'TOTALES', D0.TIPO

FROM

(

SELECT C0.DOCNUM, C0.SLPNAME, C0.CARDNAME, C0.INSTALLMNT, C0.TIPO, C0.TOTAL 'TOTAL1'

FROM

(

SELECT B0.INSTALLMNT, B0.DOCUMENTO, B0.TAXDATE, B0.DOCNUM, B0.SLPNAME, B0.CARDNAME, B0.ITEMCODE, B0.ITEMNAME, B0.TIPO,

B0.TOTAL

FROM

(

SELECT M0.INSTALLMNT, M0.DOCUMENTO, M0.PRICELIST, M0.TAXDATE, M0.DOCNUM, M0.SLPNAME, M0.CARDNAME, M0.ITEMCODE, M0.ITEMNAME, M0.TIPO,

M0.PRECIO1, M0.PRECIO2, M0.PRECIO3, M0.PRECIO4, M0.PRECIO5, M0.PRECIO6, M0.PRECIOVENTA, SUM(M0.CANTIDAD) 'CANTIDAD', SUM(M0.TOTAL) 'TOTAL'

FROM

(

SELECT A0_0.INSTALLMNT, A0_0.DOCNUM 'DOCUMENTO', A0_2.PRICELIST, A0_0.TAXDATE, A0_0.DOCNUM, A0_3.SLPNAME, A0_0.CARDNAME, A0.ITEMCODE, A0_1.ITEMNAME, 'FACTURA' AS 'TIPO',

CASE

WHEN @PRECIO1 = 1 AND ISNULL(A0.U_PRECIOPISO,0) = 0

THEN A0_2.PRICE

WHEN @PRECIO1 = 1 AND ISNULL(A0.U_PRECIOPISO,0) <> 0

THEN A0.U_PRECIOPISO

WHEN @PRECIO1 <> 1 AND ISNULL(A0.U_PRECIOPISO,0) = 0

THEN A0_2.PRICE+(A0_2.PRICE*@PRECIO1)

WHEN @PRECIO1 <> 1 AND ISNULL(A0.U_PRECIOPISO,0) <> 0

THEN A0.U_PRECIOPISO+(A0.U_PRECIOPISO*@PRECIO1)

END 'PRECIO1',

CASE WHEN ISNULL(A0.U_PRECIOPISO,0) = 0 THEN A0_2.PRICE(A0_2.PRICE*@PRECIO2) ELSE A0.U_PRECIOPISO(A0.U_PRECIOPISO*@PRECIO2) END 'PRECIO2',

CASE WHEN ISNULL(A0.U_PRECIOPISO,0) = 0 THEN A0_2.PRICE(A0_2.PRICE*@PRECIO3) ELSE A0.U_PRECIOPISO(A0.U_PRECIOPISO*@PRECIO3) END 'PRECIO3',

CASE WHEN ISNULL(A0.U_PRECIOPISO,0) = 0 THEN A0_2.PRICE(A0_2.PRICE*@PRECIO4) ELSE A0.U_PRECIOPISO(A0.U_PRECIOPISO*@PRECIO4) END 'PRECIO4',

CASE WHEN ISNULL(A0.U_PRECIOPISO,0) = 0 THEN A0_2.PRICE(A0_2.PRICE*@PRECIO5) ELSE A0.U_PRECIOPISO(A0.U_PRECIOPISO*@PRECIO5) END 'PRECIO5',

CASE WHEN ISNULL(A0.U_PRECIOPISO,0) = 0 THEN A0_2.PRICE(A0_2.PRICE*@PRECIO6) ELSE A0.U_PRECIOPISO(A0.U_PRECIOPISO*@PRECIO6) END 'PRECIO6',

CASE

WHEN SALUNITMSR = 'UN'

THEN A0.U_Q_PRECIOKG

WHEN SALUNITMSR = 'KG'

THEN A0.PRICE

END 'PRECIOVENTA',

A0.QUANTITY 'CANTIDAD', A0.LINETOTAL 'TOTAL'

FROM INV1 A0

INNER JOIN OINV A0_0 ON A0.DOCENTRY = A0_0.DOCENTRY

LEFT OUTER JOIN OITM A0_1 ON A0.ITEMCODE = A0_1.ITEMCODE

LEFT OUTER JOIN ITM1 A0_2 ON A0.ITEMCODE = A0_2.ITEMCODE

INNER JOIN OSLP A0_3 ON A0_0.SLPCODE = A0_3.SLPCODE

WHERE A0_2.PRICELIST = '1' AND A0_0.DOCTYPE = 'I'

UNION ALL

-- NOTAS CREDITO - DESCUENTO DE COMISIONES - UNICAMENTE DE ARTICULOS YA QUE LA FORMULACION DISEÑADA SE BASA EN LOS PRECIOS PISO DE ARTICULOS

SELECT A0_0.INSTALLMNT, A0_0.DOCNUM 'DOCUMENTO', A0_2.PRICELIST, A0_0.TAXDATE, A0_0.DOCNUM, A0_3.SLPNAME, A0_0.CARDNAME, A0.ITEMCODE, A0_1.ITEMNAME, 'FACTURA' AS 'TIPO',

CASE

WHEN @PRECIO1 = 1 AND ISNULL(A0.U_PRECIOPISO,0) = 0

THEN A0_2.PRICE

WHEN @PRECIO1 = 1 AND ISNULL(A0.U_PRECIOPISO,0) <> 0

THEN A0.U_PRECIOPISO

WHEN @PRECIO1 <> 1 AND ISNULL(A0.U_PRECIOPISO,0) = 0

THEN A0_2.PRICE+(A0_2.PRICE*@PRECIO1)

WHEN @PRECIO1 <> 1 AND ISNULL(A0.U_PRECIOPISO,0) <> 0

THEN A0.U_PRECIOPISO+(A0.U_PRECIOPISO*@PRECIO1)

END 'PRECIO1',

CASE WHEN ISNULL(A0.U_PRECIOPISO,0) = 0 THEN A0_2.PRICE(A0_2.PRICE*@PRECIO2) ELSE A0.U_PRECIOPISO(A0.U_PRECIOPISO*@PRECIO2) END 'PRECIO2',

CASE WHEN ISNULL(A0.U_PRECIOPISO,0) = 0 THEN A0_2.PRICE(A0_2.PRICE*@PRECIO3) ELSE A0.U_PRECIOPISO(A0.U_PRECIOPISO*@PRECIO3) END 'PRECIO3',

CASE WHEN ISNULL(A0.U_PRECIOPISO,0) = 0 THEN A0_2.PRICE(A0_2.PRICE*@PRECIO4) ELSE A0.U_PRECIOPISO(A0.U_PRECIOPISO*@PRECIO4) END 'PRECIO4',

CASE WHEN ISNULL(A0.U_PRECIOPISO,0) = 0 THEN A0_2.PRICE(A0_2.PRICE*@PRECIO5) ELSE A0.U_PRECIOPISO(A0.U_PRECIOPISO*@PRECIO5) END 'PRECIO5',

CASE WHEN ISNULL(A0.U_PRECIOPISO,0) = 0 THEN A0_2.PRICE(A0_2.PRICE*@PRECIO6) ELSE A0.U_PRECIOPISO(A0.U_PRECIOPISO*@PRECIO6) END 'PRECIO6',

CASE

WHEN SALUNITMSR = 'UN'

THEN A0.U_Q_PRECIOKG

WHEN SALUNITMSR = 'KG'

THEN A0.PRICE

END 'PRECIOVENTA',

-A0_4.QUANTITY 'CANTIDAD', -A0_4.LINETOTAL 'TOTAL'

FROM INV1 A0

INNER JOIN OINV A0_0 ON A0.DOCENTRY = A0_0.DOCENTRY

LEFT OUTER JOIN OITM A0_1 ON A0.ITEMCODE = A0_1.ITEMCODE

LEFT OUTER JOIN ITM1 A0_2 ON A0.ITEMCODE = A0_2.ITEMCODE

INNER JOIN OSLP A0_3 ON A0_0.SLPCODE = A0_3.SLPCODE

INNER JOIN RIN1 A0_4 ON A0_0.DOCENTRY = A0_4.BASEENTRY AND A0.ITEMCODE = A0_4.ITEMCODE AND A0_4.BASETYPE = '13'

INNER JOIN ORIN A0_5 ON A0_4.DOCENTRY = A0_5.DOCENTRY

WHERE A0_2.PRICELIST = '1' AND A0_0.DOCTYPE = 'I'

)M0 GROUP BY

M0.INSTALLMNT, M0.DOCUMENTO, M0.PRICELIST, M0.TAXDATE, M0.DOCNUM, M0.SLPNAME, M0.CARDNAME, M0.ITEMCODE, M0.ITEMNAME, M0.TIPO,

M0.PRECIO1, M0.PRECIO2, M0.PRECIO3, M0.PRECIO4, M0.PRECIO5, M0.PRECIO6, M0.PRECIOVENTA

)B0

)C0

)D0 GROUP BY D0.DOCNUM, D0.SLPNAME, D0.CARDNAME, D0.INSTALLMNT, D0.TIPO

)E0

LEFT OUTER JOIN

(

SELECT A0.DOCNUM, A1.INSTLMNTID, MIN(A1.DUEDATE) AS 'DUEDATE', MAX(A1.INSTOTAL) AS 'INSTOTAL', MAX(A1.PAIDTODATE) 'PAGO_FECHA'

FROM OINV A0 INNER JOIN INV6 A1 ON A0.DOCENTRY = A1.DOCENTRY

WHERE A0.DOCTYPE = 'I'

GROUP BY A0.DOCNUM, A1.INSTLMNTID

)F0 ON E0.DOCNUM = F0.DOCNUM INNER JOIN OCRD F3 ON F3.CARDNAME=E0.CARDNAME LEFT JOIN

(SELECT C0.DOCNUM, C0.INSTID, C0.FECHA_PAGO, C0.ND

FROM

(

SELECT B0.DOCNUM, B0.INSTID, B0.FECHA_PAGO, B0.ND,

ROW_NUMBER() OVER(PARTITION BY B0.DOCNUM, B0.INSTID, B0.ND ORDER BY B0.DOCNUM, B0.INSTID, B0.ND, B0.FECHA_PAGO DESC) 'CUENTA'

FROM

(

SELECT A0.DOCNUM, A1.INSTID, ISNULL(A3.CHECKNUM,'') 'CHECKNUM', A2.DOCDATE 'FECHA_PAGO', 0 AS 'ND'

FROM OINV A0

LEFT OUTER JOIN RCT2 A1 ON A0.DOCENTRY = A1.DOCENTRY

LEFT OUTER JOIN ORCT A2 ON A1.DOCNUM = A2.DOCENTRY

LEFT OUTER JOIN RCT1 A3 ON A2.DOCNUM = A3.DOCNUM

WHERE A0.DOCTYPE = 'I' AND A3.CHECKNUM IS NULL

UNION ALL

SELECT DISTINCT A0.DOCNUM, A1.INSTID, A3.CHECKNUM,

CASE WHEN ISNULL(A3.CHECKNUM ,'') = '' THEN A2.DOCDATE ELSE A6.DEPOSDATE END 'FECHA_PAGO', 0 AS 'ND'

FROM OINV A0

LEFT OUTER JOIN RCT2 A1 ON A0.DOCENTRY = A1.DOCENTRY

LEFT OUTER JOIN ORCT A2 ON A1.DOCNUM = A2.DOCENTRY

LEFT OUTER JOIN RCT1 A3 ON A2.DOCNUM = A3.DOCNUM

LEFT OUTER JOIN OCHH A4 ON A3.CHECKNUM = A4.CHECKNUM

LEFT OUTER JOIN DPS1 A5 ON A4.CHECKKEY = A5.CHECKKEY

LEFT OUTER JOIN ODPS A6 ON A5.DEPOSITID = A6.DEPOSID

WHERE A0.DOCTYPE = 'I' AND A3.CHECKNUM IS NOT NULL

AND ISNULL(A1.U_NOCHEQUE,'') = ''

UNION ALL

SELECT DISTINCT A0.DOCNUM, A1.INSTID, A3.CHECKNUM,

CASE WHEN ISNULL(A3.CHECKNUM ,'') = '' THEN A2.DOCDATE ELSE A6.DEPOSDATE END 'FECHA_PAGO', 0 AS 'ND'

FROM OINV A0

LEFT OUTER JOIN RCT2 A1 ON A0.DOCENTRY = A1.DOCENTRY

LEFT OUTER JOIN ORCT A2 ON A1.DOCNUM = A2.DOCENTRY

LEFT OUTER JOIN RCT1 A3 ON A2.DOCNUM = A3.DOCNUM AND A1.U_NOCHEQUE = A3.CHECKNUM

LEFT OUTER JOIN OCHH A4 ON A3.CHECKNUM = A4.CHECKNUM

LEFT OUTER JOIN DPS1 A5 ON A4.CHECKKEY = A5.CHECKKEY

LEFT OUTER JOIN ODPS A6 ON A5.DEPOSITID = A6.DEPOSID

WHERE A0.DOCTYPE = 'I' AND A3.CHECKNUM IS NOT NULL

AND ISNULL(A1.U_NOCHEQUE,'') <> ''

UNION ALL

SELECT A0.DOCNUM, A4.REF3LINE 'CUOTA', '' AS 'CHECKNUM', A2.RECONDATE, 0 AS 'ND'

FROM OINV A0

INNER JOIN ITR1 A1 ON A0.DOCENTRY = A1.SRCOBJABS AND A0.OBJTYPE = A1.SRCOBJTYP

INNER JOIN OITR A2 ON A1.RECONNUM = A2.RECONNUM

INNER JOIN OJDT A3 ON A0.TransId = A3.TransId

LEFT JOIN JDT1 A4 ON A3.TransId = A4.TransId AND A1.TRANSROWID = A4.LINE_ID

WHERE A2.RECONTYPE = 0 AND ISNULL(A2.CANCELABS,0) = 0

)B0

)C0 WHERE C0.CUENTA = 1)E2 ON E0.DOCNUM=E2.DOCNUM AND E2.INSTID=F0.INSTLMNTID LEFT JOIN ORIN E3 ON E3.U_FACN=E0.DOCNUM

WHERE (F0.INSTOTAL - F0.PAGO_FECHA) <= 0 AND E0.CARDNAME=@SN1 AND CASE WHEN E3.U_FACN<>'' THEN 'SI' ELSE 'NO' END='NO'

ORDER BY E0.DOCNUM,F0.INSTLMNTID

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Aug 17, 2011 at 07:11 PM

    Hola estimado

    El saldo pendiente se encuantra restando 2 campos en las facturas, N/C, N/D, facturas proveedor y demases

    DocTotal-PaidToDate

    El saldo vencido puedes obtenerlo restado el total del documento y los importes reconciliados a la fecha de corte, dentro de los importes recociliados estan los pagos, N/C y cualquier otro documento que se pueda reconciliar

    Aqui esta como hacerlo, en la parte del CASE donde se calcula el saldo

    Slds

    Edited by: Floyola on Aug 17, 2011 3:14 PM

    Add a comment
    10|10000 characters needed characters exceeded

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.