Who's On?

	Membership:
	Latest: John Bonin
	Past 24 Hours: 0
	Prev. 24 Hours: 0
	Overall: 5270

	People Online:
	Visitors: 122
	Members: 0
	Total: 122

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1375
Roger French	1315
mark.cook	1244

Dependents - before and after changes on a report

6 Replies

0 Subscribed to this topic

68 Subscribed to this forum

Sort:

Author

Messages

SueS

Basic Member Send Private Message

Posts: 20

4/20/2012 1:24 PM

Our benefits area would like an automated report that shows the before and after values of certain fields. By this I mean, if an employee changed their dependent's birthdate from 2/1/1992 to 2/1/2002 they would like to see both values on the report. Since changes to the dependent table are not logged in history I am uncertain how to do this. Has anybody else had to do this? Any suggestions are appreciated.

Paul Berkowitz

Basic Member Send Private Message

Posts: 14

4/20/2012 4:29 PM

Hi Sue,

I have a similar request from our Benefit Department. We are looking developing a Oracle Trigger to record all the identified emdepden data to a new table. After the data is stored, a crystal report would be developed to read the file.

A short term suggestion is to run a automated query to copy the employee number, dependent number and the birth date to a flat file each night and compare the the two files for a changes.

Paul

Shane Jones

Veteran Member Send Private Message

Posts: 460

4/22/2012 11:56 AM

Sue,
If you have process flow integrator with JDBC enabled I could show you a solution that runs weekly to find any changes in a table. (Technically, it can be used with any table that does not have logging enabled.)

SueS

Basic Member Send Private Message

Posts: 20

4/23/2012 11:48 AM

Hi Shane,

I am told that we have process flow integrator with JDBC enabled. Anything that you can provide would be great.

Thanks,
Sue

John Henley

Posts: 3351

4/25/2012 12:33 PM

Another option is Smart Notifications, which is perfect for tracking before/after changes in datasets.

SueS

Basic Member Send Private Message

Posts: 20

4/25/2012 1:11 PM

My understanding of smart notes is that you can only capture the latest data for the report and not both the before and after data. Is there a way to capture both before and after data on a smart note?

John Henley

Posts: 3351

4/25/2012 9:08 PM

The way LSN works with infosets makes it a decent tool for tracking and notifying/reporting changes. Here’s a quick example I put together to show how it “noticed” the change in this dependent’s social number: