เทมเพลตข้อมูล Hire to Retire ด้านการจัดการตำแหน่งงาน

/*
This extraction uses the Dynamics 365 Data Management Framework. The 'query' is defined by configuring an export project via the user interface, not by running a script directly against the database.

A post-processing script is required to transform the output of this configuration into a final event log.
*/

-- Data Export Project Configuration --

Project Name: PositionManagement_EventLog_Export
Data Format: CSV

-- Entity 1: Positions --
Source Entity: HcmPositionV2
Fields to Export:
  - PositionId
  - CreatedDateTime (Used for 'Position Created In HR System' event)
  - ActivationDate (Used for 'Position Activated' event)
  - RetirementDate (Used for 'Position Deactivated' / 'Position Closed' event)
  - ModifiedDateTime (Can be used for 'Position Attributes Modified' if SysDatabaseLog is not available)
  - JobId (Used for 'Position Reclassified' event and 'JobTitle' attribute)
  - DepartmentNumber (Used for 'DepartmentName' attribute)
  - [Other fields for attributes like CostCenter, PositionStatus]

-- Entity 2: Workflow History --
Source Entity: WorkflowTrackingStatusTable
Fields to Export:
  - ContextRecId (The record ID, used to link back to the HcmPosition record)
  - ContextTableName (Filter this for 'HcmPosition')
  - WorkflowTrackingStatus (Values like 'Submitted', 'Approved', 'Rejected')
  - CreatedDateTime (Timestamp for the workflow event)
  - UserId (The user who performed the action)
  - [Workflow step name or ID field if available, to differentiate approval types]

-- Entity 3: Recruitment Requests --
Source Entity: HcmRecruitingRequest
Fields to Export:
  - PositionId
  - CreatedDateTime (Used for 'Hiring Process Started' event)
  - RecruitingId

-- Entity 4: Database Change Log (Optional) --
Source Entity: SysDatabaseLog
Fields to Export:
  - RefRecId (The record ID of the changed record)
  - RefTableId (The table ID, filter for HcmPosition)
  - CreatedDateTime (Timestamp of the change)
  - [Fields indicating the old and new values, if available]

SELECT
    p.POSITIONID AS PositionId,
    'Position Request Initiated' AS ActivityName,
    w.CREATEDDATETIME AS EventTime,
    w.CREATEDDATETIME AS EndTime,
    w.USERID AS UserName,
    dept.NAME AS DepartmentName,
    pd.DESCRIPTION AS JobTitle,
    'Initiated' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM WorkflowTrackingStatusTable w
JOIN HcmPositionV2 p ON w.REFRECID = p.RECID
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE w.TRACKINGSTATUS = 1 -- Submitted
AND w.CONTEXT LIKE '%Create position request%'
AND p.DATAREAID = '[YourCompanyId]'
AND w.CREATEDDATETIME >= '[StartDate]'

UNION ALL

SELECT
    p.POSITIONID AS PositionId,
    'Position Request Approved By Manager' AS ActivityName,
    w.CREATEDDATETIME AS EventTime,
    w.CREATEDDATETIME AS EndTime,
    w.USERID AS UserName,
    dept.NAME AS DepartmentName,
    pd.DESCRIPTION AS JobTitle,
    'Pending Budget' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM WorkflowTrackingStatusTable w
JOIN HcmPositionV2 p ON w.REFRECID = p.RECID
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE w.TRACKINGSTATUS = 5 -- Approval
AND w.CONTEXT LIKE '%Manager approval%'
AND p.DATAREAID = '[YourCompanyId]'
AND w.CREATEDDATETIME >= '[StartDate]'

UNION ALL

SELECT
    p.POSITIONID AS PositionId,
    'Position Budget Approved' AS ActivityName,
    w.CREATEDDATETIME AS EventTime,
    w.CREATEDDATETIME AS EndTime,
    w.USERID AS UserName,
    dept.NAME AS DepartmentName,
    pd.DESCRIPTION AS JobTitle,
    'Pending HR' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM WorkflowTrackingStatusTable w
JOIN HcmPositionV2 p ON w.REFRECID = p.RECID
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE w.TRACKINGSTATUS = 5 -- Approval
AND w.CONTEXT LIKE '%Budget approval%'
AND p.DATAREAID = '[YourCompanyId]'
AND w.CREATEDDATETIME >= '[StartDate]'

UNION ALL

SELECT
    p.POSITIONID AS PositionId,
    'Position Request Approved By HR' AS ActivityName,
    w.CREATEDDATETIME AS EventTime,
    w.CREATEDDATETIME AS EndTime,
    w.USERID AS UserName,
    dept.NAME AS DepartmentName,
    pd.DESCRIPTION AS JobTitle,
    'Approved' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM WorkflowTrackingStatusTable w
JOIN HcmPositionV2 p ON w.REFRECID = p.RECID
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE w.TRACKINGSTATUS = 5 -- Approval
AND w.CONTEXT LIKE '%HR approval%'
AND p.DATAREAID = '[YourCompanyId]'
AND w.CREATEDDATETIME >= '[StartDate]'

UNION ALL

SELECT
    p.POSITIONID AS PositionId,
    'Position Request Rejected' AS ActivityName,
    w.CREATEDDATETIME AS EventTime,
    w.CREATEDDATETIME AS EndTime,
    w.USERID AS UserName,
    dept.NAME AS DepartmentName,
    pd.DESCRIPTION AS JobTitle,
    'Rejected' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM WorkflowTrackingStatusTable w
JOIN HcmPositionV2 p ON w.REFRECID = p.RECID
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE w.TRACKINGSTATUS = 3 -- Rejection
AND p.DATAREAID = '[YourCompanyId]'
AND w.CREATEDDATETIME >= '[StartDate]'

UNION ALL

SELECT
    p.POSITIONID AS PositionId,
    'Position Created In HR System' AS ActivityName,
    p.CREATEDDATETIME AS EventTime,
    p.CREATEDDATETIME AS EndTime,
    p.CREATEDBY AS UserName,
    dept.NAME AS DepartmentName,
    j.DESCRIPTION AS JobTitle,
    'Created' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM HcmPositionV2 p
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN HcmJob j ON p.JOBID = j.JOBID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE p.DATAREAID = '[YourCompanyId]'
AND p.CREATEDDATETIME >= '[StartDate]'

UNION ALL

SELECT
    p.POSITIONID AS PositionId,
    'Position Attributes Modified' AS ActivityName,
    p.MODIFIEDDATETIME AS EventTime,
    p.MODIFIEDDATETIME AS EndTime,
    p.MODIFIEDBY AS UserName,
    dept.NAME AS DepartmentName,
    j.DESCRIPTION AS JobTitle,
    'Modified' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM HcmPositionV2 p
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN HcmJob j ON p.JOBID = j.JOBID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE p.MODIFIEDDATETIME > p.CREATEDDATETIME
AND p.DATAREAID = '[YourCompanyId]'
AND p.MODIFIEDDATETIME >= '[StartDate]'

UNION ALL

SELECT
    p.POSITIONID AS PositionId,
    'Position Reviewed For Compliance' AS ActivityName,
    pd.MODIFIEDDATETIME AS EventTime,
    pd.MODIFIEDDATETIME AS EndTime,
    pd.MODIFIEDBY AS UserName,
    dept.NAME AS DepartmentName,
    j.DESCRIPTION AS JobTitle,
    'Compliance Reviewed' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM HcmPositionV2 p
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN HcmJob j ON p.JOBID = j.JOBID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE pd.[YourComplianceStatusField] = 'Reviewed' -- This requires a custom field indicating compliance review
AND p.DATAREAID = '[YourCompanyId]'
AND pd.MODIFIEDDATETIME >= '[StartDate]'

UNION ALL

SELECT
    p.POSITIONID AS PositionId,
    'Position Reclassified' AS ActivityName,
    p.MODIFIEDDATETIME AS EventTime,
    p.MODIFIEDDATETIME AS EndTime,
    p.MODIFIEDBY AS UserName,
    dept.NAME AS DepartmentName,
    j.DESCRIPTION AS JobTitle,
    'Reclassified' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM HcmPositionV2 p
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN HcmJob j ON p.JOBID = j.JOBID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE p.MODIFIEDDATETIME > p.CREATEDDATETIME -- This is an inference. See known limitations.
AND p.DATAREAID = '[YourCompanyId]'
AND p.MODIFIEDDATETIME >= '[StartDate]'

UNION ALL

SELECT
    p.POSITIONID AS PositionId,
    'Position Activated' AS ActivityName,
    pd.VALIDFROM AS EventTime,
    pd.VALIDFROM AS EndTime,
    pd.MODIFIEDBY AS UserName,
    dept.NAME AS DepartmentName,
    j.DESCRIPTION AS JobTitle,
    'Active' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM HcmPositionV2 p
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN HcmJob j ON p.JOBID = j.JOBID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE pd.VALIDFROM >= '[StartDate]'
AND p.DATAREAID = '[YourCompanyId]'

UNION ALL

SELECT
    hr.POSITIONID AS PositionId,
    'Hiring Process Started' AS ActivityName,
    hr.CREATEDDATETIME AS EventTime,
    hr.CREATEDDATETIME AS EndTime,
    hr.CREATEDBY AS UserName,
    dept.NAME AS DepartmentName,
    j.DESCRIPTION AS JobTitle,
    'Recruiting' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM HcmRecruitingRequest hr
JOIN HcmPositionV2 p ON hr.POSITIONID = p.POSITIONID
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN HcmJob j ON p.JOBID = j.JOBID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE hr.DATAREAID = '[YourCompanyId]'
AND hr.CREATEDDATETIME >= '[StartDate]'

UNION ALL

SELECT
    p.POSITIONID AS PositionId,
    'Position Frozen' AS ActivityName,
    pd.MODIFIEDDATETIME AS EventTime, -- Assuming a status change triggers modification time
    pd.MODIFIEDDATETIME AS EndTime,
    pd.MODIFIEDBY AS UserName,
    dept.NAME AS DepartmentName,
    j.DESCRIPTION AS JobTitle,
    'Frozen' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM HcmPositionV2 p
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN HcmJob j ON p.JOBID = j.JOBID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE p.[YourPositionStatusField] = 'Frozen' -- Requires a dedicated status field on the position
AND p.DATAREAID = '[YourCompanyId]'
AND pd.MODIFIEDDATETIME >= '[StartDate]'

UNION ALL

SELECT
    p.POSITIONID AS PositionId,
    'Position Deactivated' AS ActivityName,
    pd.VALIDTO AS EventTime,
    pd.VALIDTO AS EndTime,
    pd.MODIFIEDBY AS UserName,
    dept.NAME AS DepartmentName,
    j.DESCRIPTION AS JobTitle,
    'Inactive' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM HcmPositionV2 p
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN HcmJob j ON p.JOBID = j.JOBID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE pd.VALIDTO < '2154-12-31' -- D365 often uses this far-future date for 'never expires'
AND pd.VALIDTO >= '[StartDate]'
AND p.DATAREAID = '[YourCompanyId]'

UNION ALL

SELECT
    p.POSITIONID AS PositionId,
    'Position Closed' AS ActivityName,
    pd.MODIFIEDDATETIME AS EventTime, -- Assuming a status change triggers modification time
    pd.MODIFIEDDATETIME AS EndTime,
    pd.MODIFIEDBY AS UserName,
    dept.NAME AS DepartmentName,
    j.DESCRIPTION AS JobTitle,
    'Closed' AS PositionStatus,
    pd.DEFAULTDIMENSIONDISPLAYVALUE AS CostCenter
FROM HcmPositionV2 p
JOIN HcmPositionDetail pd ON p.POSITIONID = pd.POSITIONID
LEFT JOIN HcmJob j ON p.JOBID = j.JOBID
LEFT JOIN OMOperatingUnit dept ON pd.DEPARTMENT = dept.OMOPERATINGUNITNUMBER
WHERE p.[YourPositionStatusField] = 'Closed' -- Requires a dedicated status field on the position
AND p.DATAREAID = '[YourCompanyId]'
AND pd.MODIFIEDDATETIME >= '[StartDate]'