login and Certman AAF Integration changes

[portal.git] / ecomp-portal-BE-common / src / main / webapp / WEB-INF / fusion / orm / EP.hbm.xml

diff --git a/ecomp-portal-BE-common/src/main/webapp/WEB-INF/fusion/orm/EP.hbm.xml b/ecomp-portal-BE-common/src/main/webapp/WEB-INF/fusion/orm/EP.hbm.xml
index becfe7c..e4b8144 100644 (file)
--- a/ecomp-portal-BE-common/src/main/webapp/WEB-INF/fusion/orm/EP.hbm.xml
+++ b/ecomp-portal-BE-common/src/main/webapp/WEB-INF/fusion/orm/EP.hbm.xml
@@ -1306,9 +1306,12 @@ where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_y
           where a.user_id =:userId  and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
                        ) A 
                left outer join
-                       (select  distinct
-                       c.AFFECTED_RECORD_ID, c.AUDIT_DATE 
-                       from FN_AUDIT_LOG c
+                       (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE 
+                       from FN_APP LEFT JOIN FN_AUDIT_LOG 
+                       on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID  
+                       where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
+                       and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE() 
+                       group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID
                         )B
                        on A.app_id = B.Affected_record_id
                        order by AUDIT_DATE DESC;
@@ -1349,9 +1352,12 @@ where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_y
                        
                        ) A 
                left outer join
-                       (select  distinct
-                       c.AFFECTED_RECORD_ID, c.AUDIT_DATE 
-                       from FN_AUDIT_LOG c
+                       (select  FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE 
+                       from FN_APP LEFT JOIN FN_AUDIT_LOG 
+                       on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID  
+                       where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
+                       and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE() 
+                       group by  FN_AUDIT_LOG.AFFECTED_RECORD_ID 
                         )B
                        on A.app_id = B.Affected_record_id
                        order by AUDIT_DATE DESC;