I may have mentioned something about this in the past but, I have gotten a bit further, and still need some help!

I wrote a report that shows employee job changes (job code) using the PERSACTHISTORY table, but it only shows the current job code and the date stamp it occurred. How can I get all of the employee job codes to display?

I am pulling the job code field from the JOBCODE table. Is this correct?

Here's my selection:
{HRHISTORY.FLD_NBR} = 19.00 and
{PERSACTHST.ACTION_CODE} > " "and
not ({EMPLOYEE.EMP_STATUS} in ["UR", "US", "XF", "XI", "XP", "XR", "XU", "XV", "XZ"]) and
{PERSACTHST.DATE_STAMP} = {?DatRange}

Thanks everyone! :bow:

Milo,

Is this the same report you described in your post "PA340 Report, Report that reflects employee changes"?

Kate

I'm not sure I understand what you're trying to list. Are you trying to list all of the job changes over a reporting period and the if an employee has changed job codes more than once over the reporting period, you are only seeing the last one?

I think you want to get the job code from the A-Value field in the HRHISTORY file. But as I said, I may be misunderstanding your objective.

Karen

Yes, sort of. I am listing all JC Changes per Employee for a date range, but need to see every Job Code that the Employee had. For Example:

For Employee # 1, there are 4 JCode changes within a Date Range:

Empl#1, Name, JCode1, Action Code (JC Change), DateStamp
Empl#1, Name, JCode2, Action Code (JC Change), DateStamp
Empl#1, Name, JCode3, Action Code (JC Change), DateStamp
Empl#1, Name, JCode4, Action Code (JC Change), DateStamp--most recent

B)

To see every job code an employee has ever held you will need to look at either PAEMPPOS or HRHISTORY field #19 (assuming you track history on job code on HR10). PAEMMPOS tracks effective date to end date and HRHISTORY indicates the date the job code on the employee changed.

I noticed in HR10 on the Topic: "Employee Master" we do not have an "x" in the History column.

Are there other areas where the Job Code History should be set up?
:tyson:

Milo,

I know that the HR Writer tool will return the Prior Value of a Data Item; HR65.1 Report Type = H and Detail Topic = HI. On HR65.2 the Prior Value and Alpha Field Value data items show field values pre- and post-change.
I'm not sure how HRW does this -- the data file text for HRHISTORY does not include a Prior Value field...

Re HRHISTORY file:
-Regardless of HR10, system logs all changes made via personnel actions to HRHISTORY. HRH-ACT-OBJ-ID links to PERSACTHST for these.
-HRH-DATE-STAMP (used for As Of Date queries from HR Writer).

hth

Kate

Here's a sample query (for Oracle) that should provide what you are asking for...

Btw, looks like you are using Crystal Reports. If your database is oracle, just paste the query (include the schema in front of each table name) into a command object.

I tried adding this command:

SELECT "employee.company", "employee.employee", "employee.last_name", "employee.first_name", "employee.middle_init", "employee.emp_status", "hrhistory.beg_date", "hrhistory.date_stamp", "persacthst.action_code", "hrhistory.a_value as historical_job_code", "hrhistory.seq_nbr", "jobcode.description as historical_job_description"
FROM "hrhistory", "persacthst", "employee", "jobcode"
WHERE "hrhistory.act_obj_id" = "persacthst.obj_id"
AND "hrhistory.company" = "employee.company"
AND "hrhistory.employee" = "employee.employee"
AND "employee.company" = "jobcode.company"
AND "hrhistory.a_value" = "jobcode.job_code"
AND "hrhistory.fld_nbr" = "19"
ORDER BY "employee.employee", "hrhistory.beg_date", "hrhistory.date_stamp"

but get the following error:
Query Engine Error: 'ORA-00972: identifier is too long'
Not Supported. Details: Fail to execute SQL statement.
OCI Call: OCIStmtExecute

?? :merc:

Guess it didn't like my aliases. Try this...

SELECT "employee.company", "employee.employee", "employee.last_name", "employee.first_name", "employee.middle_init", "employee.emp_status", "hrhistory.beg_date", "hrhistory.date_stamp", "persacthst.action_code", "hrhistory.a_value", "hrhistory.seq_nbr", "jobcode.description"
FROM "hrhistory", "persacthst", "employee", "jobcode"
WHERE "hrhistory.act_obj_id" = "persacthst.obj_id"
AND "hrhistory.company" = "employee.company"
AND "hrhistory.employee" = "employee.employee"
AND "employee.company" = "jobcode.company"
AND "hrhistory.a_value" = "jobcode.job_code"
AND "hrhistory.fld_nbr" = "19"
ORDER BY "employee.employee", "hrhistory.beg_date", "hrhistory.date_stamp"

are these table naming conventions 'case sensitive' ??

Also, I tried the newer query and got this error message (different):

Query Engine: ORA-00942: Table or View does not exist
Query Engine error: Fail to execute SQL statement. OCI Call: OCIStmtExecute


?????? Am I missing something? :werd:

Table names are not case-sensitive.

You'll need to include the schema of the tables (ie yourlawsonschema.tablename) in the from clause. Let's say your db schema where the tables reside is lawdev, then your query would be...

SELECT e.company, e.employee, e.last_name, e.first_name, e.middle_init, e.emp_status, y.beg_date, y.date_stamp, h.action_code, y.a_value as historical_job_code, y.seq_nbr, j.description as historical_job_description
FROM lawdev.hrhistory y, lawdev.persacthst h, lawdev.employee e, lawdev.jobcode j
WHERE y.act_obj_id = h.obj_id(+)
AND y.company = e.company
AND y.employee = e.employee
AND e.company = j.company
AND y.a_value = j.job_code
AND y.fld_nbr = 19
ORDER BY e.employee, y.beg_date, y.date_stamp

If you want to make your life easier, run the sql through a tool like TOAD (my favorite but potentially cost-prohibitive), Winql (free/simple), or Oracle's new/free tool called SQL Developer. After you fine tine your sql, then you can copy/paste into a command object in Crystal. :thumb:

Ok, I got it to return data! Here is my Query - with NO quotes, and the schema = LAWSON

SELECT EMPLOYEE.COMPANY, EMPLOYEE.EMPLOYEE, EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME, EMPLOYEE.MIDDLE_INIT, EMPLOYEE.EMP_STATUS, HRAHISTORY.BEG_DATE, HRHISTORY.DATE_STAMP, PERSACTHST.ACTION_CODE, HRHISTORY.A_VALUE, HRHISTORY.SEQ_NBR, JOBECODE.DESCRIPTION
FROM LAWSON.HRHISTORY HRHISTORY, LAWSON.PERSACTHST PERSACTHST, LAWSON.EMPLOYEE EMPLOYEE, LAWOSN.JOBCODE JOBCODE
WHERE HRHISTORY.ACT_OBJ_ID = PERSACTHST.OBJ_ID
AND HRHISTORY.COMPANY = EMPLOYEE.COMPANY
AND HRHISTORY.EMPLOYEE = EMPLOYEE.EMPLOYEE
AND EMPLOYEE.COMPANY = JOBCODE.COMPANY
AND HRHISTORY.A_VALUE = JOBCODE.JOB_CODE
AND HRHISTORY.FLD_NBR = 19
ORDER BY EMPLOYEE.EMPLOYEE, HRHISTORY.BEG_DATE, HRHISTORY.DATE_STAMP

Thanks CJMART - ROCK ON! :werd: :afro: