cancel
Showing results for 
Search instead for 
Did you mean: 

I wanna know where this fields change me Vendor liabilites aging results

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

hdolenec
Contributor
0 Kudos

Hello,

Check SAP Note 1552673.

Former Member
0 Kudos

Hi Hrvoje, thank you very much for the help.

I read the note and now i must locate the exchange rate field for the report.

Where i can locate it?

I need it this field to add it in my SQL query and show it in other SAP Tool.

Regards.

hdolenec
Contributor

It's ORTT table under Administration => Exchange Rates and Indexes

Former Member
0 Kudos

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.
Former Member
0 Kudos

Thank you very much again Hrvoje.

Finally my query is working. A last thing, in what table i can obtain the field to show zero balance values?

Kind regards.

hdolenec
Contributor
0 Kudos

In BP Master data table OCRD, I think the field is named Balance

Former Member
0 Kudos

How can i close this thread? Yuo give it to me the answer.

Regards.

hdolenec
Contributor
0 Kudos

To be honest I don't know. You have to mark one of my answers as accepted or as best answer, but I don't know how it's done. Try clicking Actions button on one of the comments

Answers (0)