SQL - User Lifecycle Report

 SELECT     U.USERNAME,

           U.FIRSTNAME,

           U.LASTNAME,

           U.EMAIL,

           U.EMPLOYEETYPE,

           U.CUSTOMPROPERTY9  AS 'JOINER DAT',

           U.CUSTOMPROPERTY10 AS 'LOA START DAT',

           U.CUSTOMPROPERTY8  AS 'MOVER DAT',

           U.CUSTOMPROPERTY13 AS 'TERMINATION DAT',

           U.CUSTOMPROPERTY17 AS 'CORPORATE ID',

           E.DISPLAYNAME      AS 'APPLICATION NAME',

           AT.ACCOUNTNAME,

           AT.TASKKEY,

           AT.TASKDATE 'TASK CREATED ON',

           CASE

                      WHEN AT.TASKTYPE = 1 THEN 'Add Access'

                      WHEN AT.TASKTYPE = 2 THEN 'Remove Access'

                      WHEN AT.TASKTYPE = 2 AND AT.ENTITLEMENT_VALUEKEY IS NULL THEN 'Remove Account'

                      WHEN AT.TASKTYPE = 3 THEN 'New Account'

                      WHEN AT.TASKTYPE = 4 THEN 'Role'

                      WHEN AT.TASKTYPE = 5 THEN 'Change Password'

                      WHEN AT.TASKTYPE = 6 THEN 'Enable Account'

                      WHEN AT.TASKTYPE = 7 THEN 'Claim Account'

                      WHEN AT.TASKTYPE = 8 THEN 'Delete Account'

                      WHEN AT.TASKTYPE = 9 THEN 'Update User'

                      WHEN AT.TASKTYPE = 12 THEN 'Update Account'

                      WHEN AT.TASKTYPE = 13 THEN 'Proposed Entitlement Owner'

                      WHEN AT.TASKTYPE = 14 THEN 'Disable Account'

                      WHEN AT.TASKTYPE = 23 THEN 'Modify Privilege'

                      WHEN AT.TASKTYPE = 24 THEN 'Create Entitlement'

                      WHEN AT.TASKTYPE = 25 THEN 'Add Access Entitlement'

                      WHEN AT.TASKTYPE = 26 THEN 'Remove Access Entitlement'

                      WHEN AT.TASKTYPE = 27 THEN 'Update Entitlement'

                      WHEN AT.TASKTYPE = 28 THEN 'Delete Entitlement'

                      WHEN AT.TASKTYPE = 29 THEN 'Grant Firefighter ID'

                      WHEN AT.TASKTYPE = 30 THEN 'Revoke Firefighter ID'

                      WHEN AT.TASKTYPE = 31 THEN 'Update Access End Date'

                      WHEN AT.TASKTYPE = 32 THEN 'Lock Account'

                      WHEN AT.TASKTYPE = 33 THEN 'Unlock Account'

                      WHEN AT.TASKTYPE = 34 THEN 'FireFighter Instance Grant Access'

                      WHEN AT.TASKTYPE = 35 THEN 'FireFighter Instance Revoke Access'

                      WHEN AT.TASKTYPE = 36 THEN 'Emergency Access ID Alert'

                      WHEN AT.TASKTYPE = 37 THEN 'Create Organization'

                      WHEN AT.TASKTYPE = 38 THEN 'Update Organization'

                      ELSE AT.TASKTYPE

           END                  AS 'TASK TYPES',

           EV.ENTITLEMENT_VALUE AS 'ACCESS',

           CASE WHEN AT.TASKTYPE IN (3,12,32,33,6,14)  THEN 'ACCOUNT' ELSE

ET.DISPLAYNAME END AS 'ACCESS TYPE',

           CASE

                      WHEN SOURCE = 'ZERODAY' THEN 'Birthright'

                      WHEN SOURCE = 'ANALYTICS_V2' THEN 'Report'

                      WHEN SOURCE = 'CERTIFICATION' THEN 'Certification'

                      WHEN SOURCE = 'ONECLICKDISABLE' THEN 'HR Initiated Termination'

                      WHEN SOURCE = 'PROVRULE' THEN 'Lifecycle Rules'

                      WHEN SOURCE = 'REQUEST' THEN 'Access Request'

                      WHEN SOURCE = 'SOD' THEN 'SoD Rules'

                      WHEN SOURCE = 'BASELINE_TASK' THEN 'Application Request'

                      WHEN SOURCE = 'UPDATE_ROLE' THEN 'Lifecycle Rules'

                      WHEN AT.TASKTYPE = 5 THEN 'Self Service Request'

                      ELSE SOURCE

           END AS SOURCE,

           CASE

                      WHEN AT.STATUS = 1 THEN 'Open/New'

                      WHEN AT.STATUS = 2 THEN 'In Progress'

                      WHEN AT.STATUS = 3 THEN 'Completed'

                      WHEN AT.STATUS = 4 THEN 'Discontinued'

                      WHEN AT.STATUS = 8 THEN 'Error'

                      WHEN AT.STATUS = 9 THEN 'No Action Required'

                      ELSE NULL

           END AS 'TASK STATUS',

           CASE

                      WHEN AT.STATUS IN ( 3,

                                         4,

                                         8,

                                         9 ) THEN (AT.UPDATEDATE)

                      ELSE NULL

           END 'TASK COMPLETION DAT'

FROM       USERS U

INNER JOIN ARSTASKS AT

ON         AT.USERKEY = U.USERKEY

INNER JOIN ENDPOINTS E

ON         E.ENDPOINTKEY = AT.ENDPOINT

LEFT JOIN  ENTITLEMENT_VALUES EV

ON         AT.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY

LEFT JOIN  ENTITLEMENT_TYPES ET

ON         ET.ENTITLEMENTTYPEKEY= EV.ENTITLEMENTTYPEKEY

WHERE      U.USERNAME = '${Workday_ID}'

AND        DATE(AT.TASKDATE) BETWEEN ${Task_Created_From} AND        ${Task_Created_To}

AND     ((E.ENDPOINTNAME IN ('Aiia Azure AD','Azure AD Mastertest','Azure_AD_MasterTest-Primary Accounts','Azure_AD_MasterTest-Test Accounts') AND ((ET.ENTITLEMENTNAME = 'AADGroup' AND EV.CUSTOMPROPERTY8 = 'false' AND EV.CUSTOMPROPERTY9 IS NULL AND EV.CUSTOMPROPERTY10 = 'true' AND EV.CUSTOMPROPERTY11 IS NULL) OR EV.CUSTOMPROPERTY11 = 'unified' ) AND ET.ENTITLEMENTNAME='AADGroup') OR E.ENDPOINTNAME NOT IN ('Aiia Azure AD','Azure AD Mastertest','Azure_AD_MasterTest-Primary Accounts','Azure_AD_MasterTest-Test Accounts') )