Need some E1 or SQL help with a data request...

pbare

Well Known Member
My business has asked us to provide them with a list of employees that have had a changed in PAST in 2014 and at the same time show which HMCU they were in at that particular time.

This is just a one time audit request so a custom report is not possible at this time.

I am trying to query against the HR History table (F08042) for records in 2014 with a change to PAST; then I need to find the HMCU value that corresponds to that time frame.

E1 does have a point in time process but that appears to be something that can be run for a specific date not a date range :-( And it is for Employee Master records (not the history of that change)

Would anyone know if it is possible to get the following information
Employee Number (AN8)
PAST Value (HSTD) Where DTAI = PAST
EFTO PAST
HMCU Value (HSTD) where DTAI = HMCU
EFTO HMCU - this needs to correspond the range that the PAST value is for


This is a time sensitive request so I would appreciate any help that someone can provide

Thanks
Pam
 
Since the F08042 table has one record for each field changed, you will have to do it in two steps.

1: Extract all DTAI = PAST records with the date effective (EFFF I believe) for all of 2014
2: For each of the records extracted in step one, run a second extract against the F08042 to pull the DTAI = HCMU with an Effective Date less than the records PAST record's Effective Date. Make sure you ORDER BY or Data Sequence by the Effective Date in the 2nd extract.
3: Select the last record Extracted to get the HMCU for each record in step one. In many cases, there may be only one and that was created when the Employee is hired.

I would suggest you do this with a Report Object/UBE. It is much easier. I know, I have done the exact same exercise
 
Pam,

Even if it is one time request, it will be much easier to do by UBE.
 
Hi All,

for date translations I use following:
1) sysdate to JDE format: (select (to_char ( sysdate,'YYYY')-1900)*1000 +to_char ( sysdate,'DDD') from dual)
2) JDE format to normal: to_date(trdj+1900000,'YYYYDDD')
3) JDE format to normal if 0 can be in database: CASE WHEN TRDJ =0 THEN null ELSE to_date(trdj+1900000,'YYYYDDD') END

It uses standard SQL and there is no problem. Only if I need to add some days to JDE format, I need to convert it to standard format, add requested days and then the result convert back to JDE format.

Petr
 
All,

Here is a function that will work in Access or Excel to convert JDE Julian dates. All you need to do is pass your Julian jde date into this function. Don't forget to change the field format (mm/dd/yy,....) in Excel to your choice. Hope this helps.

Function JDEDate(JDate As String) As Long
Dim TheYear As Integer
Dim TheDay As Integer
Dim TheDate As Long
Dim TheJul As Long


If JDate > 0 Then
TheYear = CInt(Mid(JDate, 2, 2))
If TheYear < 30 Then
TheYear = TheYear + 2000
Else
TheYear = TheYear + 1900
End If

TheDay = CInt(Right(JDate, 3))
TheDate = DateSerial(TheYear, 1, TheDay)
JDEDate = TheDate
Else
JDEDate = 0
End If

End Function
 
Last edited:
I'd write in JDE

Create it in your default project and use OCMs to point to wherever a good data set is.
Then bin your new UBE
 
Back
Top