NEED querry to create Alert for OHEM updates

[Former Member]

Hi experts

Am using sap b1 8.82 v, i need a query to create alert Based on Employee Master Data

am having total 200-210 Fields in Employee Master data including Standard and UDF

in this all table if the user update any table in an alert mentioning the specified Fields has updated

this is the out put i required

Empid           Updated Feild           New value            Old value           Updated Date

1010             Salary                         1000                     2000                12/01/2016

1031             Nationality                   IN                         SA                  10/01/2016

1041             U_EmailID                  1041@g.com        abc@g.com       01/01/2016

Like this i need it

pls help me to sort out this issue

Regards  

[former_member227598]

Hi Shahan,

I think you have to mention which feild are updating in AHEM table that should condering as a 1 column for eaxmple i am conder MiddleNmae in below query

Please try the below query 

SELECT distinct T0.[empID],

T2.[MiddleName] as Newvalue, T1.[MiddleName] as oldvalue ,T0.[UpdateDate]

FROM AHEM T0 left join AHEM T1 on T1.[empID] = T0.[empID] and t1.loginstanc= t0.loginstanc-1

left join OHEM T2 on T0.[empID] = T0.[empID]

where t0.empid = 3 

group by T0.[empID], T2.[MiddleName], T1.[MiddleName],T0.[UpdateDate] order by T0.[UpdateDate]

Thanks.

Rgds,

Kamlesh Naware

[Former Member]

thanks so much for your replay

the above said query am having,,

what i need is as i said am having around 200-210 fields which include standard field and UDF

if suppose a user update 5 fields in an employee master data,, only that updated fields should be appear in the query

Like this way

*******************************************************************************************************

Empid           Updated Feild Name           New value                     Old value                            Updated Date

1010             Salary                                     1000                                2000                                      12/01/2016

1031             Nationality                                IN                                  SA                                           10/01/2016

1041             U_EmailID                               1041@g.com        abc@g.com                      01/01/2016

1041              FirstName                               abc                               cde                                           01/01/2016

1041              passport No                        f152451                            h154215                                01/01/2016

*********************************************************************************************************

Regards

[former_member227598]

Hi Shahan,

Then in case you have move to Crystal Report and use formula . In that feilds formula ....if New Value & Old are  equal then not display in reports .

Rgds,.

Kamlesh Naware

[Former Member]

Hi experts

does any one can get me a query in such a way,,

[former_member227598]

Hi Shahan,

Please try the below query  and select UDF & other fields which is required.

SELECT *  FROM [dbo].[OHEM]  T0 INNER JOIN AHEM T1 ON T0.[empID] = T1.[empID]

Thanks.

Rgds,

Kamlesh Naware

[Former Member]

Thanks for the support

Kamlesh

but i need in the consolidate format,,

Regards

[former_member319241]

Hi,

You can use OHEM table along with AHEM which is log table for employee master data.

Thanks.

[Former Member]

Hi Saurabh

Am not much familiar with query, pls could u help me to Get the query

Regards