on 06-03-2018 11:00 PM
Hello everyone.
I'm looking where i can get this fields from a query (yellow marked).
What tables contained? I used the SQL Profiller but i didn't see how that values can be generated. If someone has the information note of "Translate Leading Currency at Aging Date" (The most urgent field for my SQL) i really will be apreciated.
Regards.
Hello,
Check SAP Note 1552673.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
It's ORTT table under Administration => Exchange Rates and Indexes
Thank you very much Hrvoje. I'm trying to add the value from the table but is giving to me an error. The Query is the following
select T1.CardCode AS Codigo,
Convert(char(40),T1.cardname) AS Nombre,
case T0.transtype
when '13' then 'Factura de Deudores'
when '14' then 'Nota Credito'
when '24' then 'Pagos'
when '30' then 'Asientos Contables'
else 'Otro'
end 'Tipo Trans',
T0.Ref1 AS Referencia,
T2.TransCode AS 'Codigo tRANS',
T0.transtype,
RIGHT(YEAR(T0.RefDate), 4) AS 'Año',
Case
when DATEPART(MM, T0.RefDate)='1' then '01'
when DATEPART(MM, T0.RefDate)='2' then '02'
when DATEPART(MM, T0.RefDate)='3' then '03'
when DATEPART(MM, T0.RefDate)='4' then '04'
when DATEPART(MM, T0.RefDate)='5' then '05'
when DATEPART(MM, T0.RefDate)='6' then '06'
when DATEPART(MM, T0.RefDate)='7' then '07'
when DATEPART(MM, T0.RefDate)='8' then '08'
when DATEPART(MM, T0.RefDate)='9' then '09'
when DATEPART(MM, T0.RefDate)='10' then '10'
when DATEPART(MM, T0.RefDate)='11' then '11'
when DATEPART(MM, T0.RefDate)='12' then '12' END Mes,
CONVERT(VARCHAR(10), T0.RefDate, 103) AS 'Fecha Docto',
CONVERT(VARCHAR(10), T0.DueDate, 103) 'Vence Docto',
DATEDIFF(day,T0.refdate,T0.duedate) as 'Dias',
SUM(BalDueDeb-BalDueCred) "Saldo L",
SUM(BalScDeb-BalScCred) "Saldo E",
T3.Rate, --->This is the field
COALESCE((CASE
when (DATEDIFF(dd,T0.RefDate,current_timestamp))+1 < 31
then
case
when syscred <> 0 then isnull(-BALDUECRED,0)
else isnull(sysdeb,0)
end
end),0) '0-30 Dias',
------
COALESCE((CASE
when (DATEDIFF(dd,T0.RefDate,current_timestamp))+1 < 31 and DATEPART(m, T0.RefDate) = DATEPART(m, DATEADD(m, -1, getdate()))--(DATEDIFF(mm,T0.RefDate,current_timestamp))-1<(DATEPART(MM, T0.RefDate))
then
case
when syscred <> 0 then isnull(-BALDUECRED,0)
else isnull(sysdeb,0)
end
end),0) 'Mes Ant 0-30',
------
COALESCE((case when ((datediff(dd,t0.RefDate,current_timestamp))+1 > 30
and (datediff(dd,t0.RefDate,current_timestamp))+1< 61)
then
case
when syscred <> 0 then isnull(-BALDUECRED,0)
else isnull(sysdeb,0)
end
end),0) '31 to 60 Dias',
-----
COALESCE((case when ((datediff(dd,t0.RefDate,current_timestamp))+1 > 30 --AND DATEPART(m, T0.RefDate) = DATEPART(m, DATEADD(m, -1, getdate())) and DATEPART(yyyy,T0.RefDate) = DATEPART(yyyy, DATEADD(yyyy, -1, getdate()))
and (datediff(dd,t0.RefDate,current_timestamp))+1< 61) AND MONTH(T0.RefDate) = (MONTH(getdate())-1) --AND year(T0.refdate) = (year(getdate())-1)--DATEPART(m, T0.RefDate) = DATEPART(m, DATEADD(m, -1, getdate())) and DATEPART(yyyy,T0.RefDate) = DATEPART(yyyy, DATEADD(yyyy, -1, getdate()))
then
case
when syscred <> 0 then isnull(-BALDUECRED,0)
else isnull(sysdeb,0)
end
end),0) 'Mes Ant 31-60',
COALESCE((case when ((datediff(dd,t0.RefDate,current_timestamp))+1 > 60
and (datediff(dd,t0.RefDate,current_timestamp))+1< 91)
then
case
when syscred <> 0 then isnull(-BALDUECRED,0)
else isnull(sysdeb,0)
end
end),0) '61 to 90 Dias',
--
COALESCE((case when ((datediff(dd,t0.RefDate,current_timestamp))+1 > 60
and (datediff(dd,t0.RefDate,current_timestamp))+1< 91) AND MONTH(T0.RefDate) = (MONTH(getdate())-1)
then
case
when syscred <> 0 then isnull(-BALDUECRED,0)
else isnull(sysdeb,0)
end
end),0) 'Mes Ant 61-90',
--
COALESCE((CASE
when (DATEDIFF(dd,t0.RefDate,current_timestamp))+1 > 90
then
case
when syscred= 0 then isnull(sysdeb,0)
when sysdeb= 0 then isnull(-BALDUECRED,0)
end
end),0) '90 + Dias',
--
COALESCE((CASE
when (DATEDIFF(dd,t0.RefDate,current_timestamp))+1 > 90 AND MONTH(T0.RefDate) = (MONTH(getdate())-1)
then
case
when syscred= 0 then isnull(sysdeb,0)
when sysdeb= 0 then isnull(-BALDUECRED,0)
end
end),0) 'Mes Ant 90 + Dias'
from JDT1 T0
INNER JOIN OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'C'
inner join ojdt t2 on t0.transid=t2.transid where T0.intrnmatch = '0' and T1.CardCode='C123456789' and T0.BALDUEDEB != T0.BALDUECRED
INNER JOIN ORTT T3 ON T2.TransCurr=T3.Currency
Group by T1.CardCode, T1.cardname, T1.Phone1, T0.transtype, T0.Ref1, T2.TransCode, T0.refdate,T0.duedate, T0.SYSCred, T0.BalDueCred, T0.SYSDeb
Thanks again.
User | Count |
---|---|
88 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.