Expense approver information

ERP 10
1

I’ve been asked to get the Expense approver name on a report.

unfortunately, even though time and expense entry shows the “approved by”, text box has no DB field, so it is “calculated”.

I found that the Task Table stores all the Task related information but haven’t found a way to link EmpExpense table to Task table to get the value of the ChangedDcdUserID (which is the field with the expense approver name).

Any suggestion??

2

you can always add a UD Field, and record the logged in user ID when the expense is approved

1 Like
3

Open Time & Expense entry. Turn on tracing, full dataset, return values. Clear the log.
Find your record.

Look in the trace and you’ll probably see the connection, or post the trace here.

2 Likes
4

Thank you both of you… in the end… I just managed to get it done… IDK if it is the best way to do it or if it is the ortodox way but it seems to be working and throwing the desired results…

EmpExpense table needs to link to Task table on EmpID = Key1 and and ForeignSysRowID = RelatedToSysRowID.

Also, you will need to filter the Task table: RelatedToFile = ‘EmpExpense’ and Approved = 1 and Complete = 1 and TaskID =‘suprappr’

with that said… this is my query…

select EE.EmpID
, EB.Name
, EE.Company
, EE.ExpenseDate
, (Select Name from EmpBasic where EmpID = EB.SupervisorID and EmpStatus = 'A') as 'Approver'--EB.SupervisorID
, t.ChangeDcdUserID as ApprovedBy
, EE.ExpenseStatus
, EE.ApprovedDate
, EE.Character01
, ee.DocClaimAmt as 'Expense Amount'
, (select Description from erp.PurMisc where company = ee.company and MiscCode = ee.MiscCode) as 'Expense Type'
, EE.InvoiceNum
, APIH.InvoiceDate
, EE.Invoiced
, APIH.ApplyDate
, EE.Character02 as 'Date'
, APT.CheckNum
, APT.TranDate
, APIH.InvoiceAmt
, EE.ProjectID
--, ee.SysRowID

from empexpense EE
Inner JOIN EmpBasic EB On EE.EmpID = EB.EmpID and EE.Company = EB.Company
Inner JOIN APInvHed APIH On EE.EmpID = APIH.EmpID and EE.Company = APIH.Company and EE.InvoiceNum = APIH.InvoiceNum
INNER JOIN erp.APTran APT ON APIH.InvoiceNum = APT.InvoiceNum and APIH.Company = APT.Company
left outer join erp.Task T on ee.EmpID = t.Key1 and ee.Company = t.Company and ee.ApprovedDate = t.CompleteDate
where EE.ExpenseDate >= @StartDate 
and EE.ExpenseDate <= @EndDate 
--and ee.MiscCode <> 'PD'
and EE.Character01 <> 'TSheets'
--and eb.SupervisorID = t.SalesRepCode 
and ee.ForeignSysRowID = t.RelatedToSysRowID
and EB.EmpStatus = 'A'
and t.RelatedToFile	= 'EmpExpense' and t.Approved = 1 and t.Complete = 1 and t.TaskID ='suprappr'
Group By EE.EmpID, EB.Name, EE.Company, EB.SupervisorID, EE.ExpenseDate,  EE.ExpenseStatus, EE.ApprovedDate, EE.Character01, EE.InvoiceNum, EE.Invoiced, APIH.InvoiceDate
, APIH.ApplyDate
, EE.Character02 
, APT.CheckNum
, APT.TranDate
, APIH.InvoiceAmt
, EE.ProjectID
, T.ChangeDcdUserID
, ee.DocClaimAmt
, ee.MiscCode