Estimados:
Estoy elaborando el sgte query que me muestra el estado de cuenta de los socios de negocios. pero en este query me muestra el total de la factura por decir de 1000 y sus pagos acuenta de 100 y 200, en el mismo SAP cuando veo el saldo del socio de negocio hay una colunma que dice saldo vencido que en este caso seria solo 700 pues quisiera saber donde saco este campo para agregarlo a mi query y solo mostrar el saldo de la factura si la tuviera en ver de que me muestre el total y luego resto los pagos acuenta.
SELECT
T0.SHORTNAME AS 'Codigo',
T1.CARDNAME as 'Socio de Negocio',
T0.REFDATE as 'FecConta',
T0.DUEDATE as 'FecVenci',
CASE T0.REF2 WHEN 'Canje Letras' then T0.LINEMEMO ELSE T0.REF2 END as 'Documento',
CASE T0.TRANSTYPE
WHEN '14' THEN T0.FCCREDIT*-1
WHEN '24' THEN T0.FCCREDIT*-1 ELSE T0.FCDebit
END as 'Total USD',
CASE T0.TRANSTYPE
WHEN '14' THEN T0.CREDIT*-1
WHEN '24' THEN T0.CREDIT*-1 ELSE T0.Debit
END AS 'Total SOL'
FROM
JDT1 T0, OCRD T1
WHERE
T0.SHORTNAME = T1.CARDCODE
AND T0.SHORTNAME BETWEEN 'C000001' AND 'C009999'
AND ( T0.BalDueDeb <> 0.000000 OR T0.BalDueCred <> 0.000000 OR T0.BalFcDeb <> 0.000000 OR T0.BalFcCred <> 0.000000)
AND T0.TRANSTYPE = 13
AND T0.REF2 is not null
AND (LINEMEMO NOT LIKE '%A CTA%' AND LINEMEMO NOT LIKE 'Entrada de pagos%')
AND T0.ShortName LIKE [%0] AND ( T0.RefDate <= [%1])
UNION
SELECT
T0.SHORTNAME AS 'Codigo',
T1.CARDNAME as 'Socio de Negocio',
T0.REFDATE as 'FecConta',
T0.DUEDATE as 'FecVenci',
CASE T0.REF2 WHEN 'Canje Letras' then T0.LINEMEMO ELSE T0.REF2 END as 'Documento',
CASE T0.TRANSTYPE
WHEN '14' THEN T0.FCCREDIT*-1
WHEN '24' THEN T0.FCCREDIT*-1 ELSE T0.FCDebit
END as 'Total USD',
CASE T0.TRANSTYPE
WHEN '14' THEN T0.CREDIT*-1
WHEN '24' THEN T0.CREDIT*-1 ELSE T0.Debit
END AS 'Total SOL'
FROM
JDT1 T0, OCRD T1
WHERE
T0.SHORTNAME = T1.CARDCODE
AND T0.SHORTNAME BETWEEN 'C000001' AND 'C009999'
AND ( T0.BalDueDeb <> 0.000000 OR T0.BalDueCred <> 0.000000 OR T0.BalFcDeb <> 0.000000 OR T0.BalFcCred <> 0.000000)
AND T0.TRANSTYPE = 30
AND (LINEMEMO NOT LIKE '%A CTA%' AND LINEMEMO NOT LIKE 'Entrada de pagos%')
AND T0.REF2 is not null AND T0.REF2 NOT LIKE 'INTERG%' AND T0.REF1 NOT LIKE 'BCD%'
AND T0.ShortName LIKE [%0] AND ( T0.RefDate <= [%1])
UNION
SELECT
T0.SHORTNAME AS 'Codigo',
T1.CARDNAME as 'Socio de Negocio',
T0.REFDATE as 'FecConta',
T0.DUEDATE as 'FecVenci',
CASE T0.REF2 WHEN 'Canje Letras' then T0.LINEMEMO ELSE T0.REF2 END as 'Documento',
CASE TRANSTYPE
WHEN '14' THEN T0.[FCDebit]*-1
WHEN '24' THEN T0.[FCDebit]-1 ELSE T0.FCcredit-1
END as 'Total USD',
CASE TRANSTYPE
WHEN '14' THEN T0.[Debit]*-1
WHEN '24' THEN T0.[Debit]-1 ELSE T0.credit-1
END AS 'Total SOL'
FROM
JDT1 T0, OCRD T1
WHERE
T0.SHORTNAME = T1.CARDCODE
AND T0.SHORTNAME BETWEEN 'C000001' AND 'C009999'
AND ( T0.BalDueDeb <> 0.000000 OR T0.BalDueCred <> 0.000000 OR T0.BalFcDeb <> 0.000000 OR T0.BalFcCred <> 0.000000)
AND (T0.credit <> 0.00000 OR T0.SYScred <> 0.00000 or t0.fccredit <> 0.00000)
and T0.REF1 NOT LIKE '%AJUSTE%'
AND TRANSTYPE IN ('24','30')
AND T0.REF2 is not null AND T0.REF2 NOT LIKE 'LC%' and t0.ref1 not like 'LC%'
and T0.LINEMEMO NOT LIKE 'COMPENSACIN ACTUALIZACIN TRANSACCIN%'
AND T0.ShortName LIKE [%0] AND ( T0.RefDate <= [%1])
UNION
SELECT
T0.SHORTNAME AS 'Codigo',
T1.CARDNAME as 'Socio de Negocio',
T0.REFDATE as 'FecConta',
T0.DUEDATE as 'FecVenci',
CASE T0.REF2 WHEN 'Canje Letras' then T0.LINEMEMO ELSE T0.REF2 END as 'Documento',
CASE TRANSTYPE
WHEN '14' THEN T0.FCCREDIT*-1
WHEN '24' THEN T0.FCCREDIT*-1 ELSE T0.FCDebit
END as 'Total USD',
CASE TRANSTYPE
WHEN '14' THEN T0.CREDIT*-1
WHEN '24' THEN T0.CREDIT*-1 ELSE T0.Debit
END AS 'Total SOL'
FROM [dbo].[JDT1] T0, OCRD T1
WHERE
T0.SHORTNAME = T1.CARDCODE AND
T0.IntrnMatch = '0' AND T0.SHORTNAME BETWEEN 'C000001' AND 'C009999'
AND T0.TRANSTYPE IN ('14','24')
AND T0.REF1 NOT LIKE '%AJUSTE%' AND T0.REF2 is not null and T0.LINEMEMO NOT LIKE 'COMPENSACIN ACTUALIZACIN TRANSACCIN%' AND
T0.ShortName LIKE [%0] AND ( T0.RefDate <= [%1])
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.