/*
This is a sample script to extract Action Information using CMO DWH tables.

DATE: 31-July-2020
*/

select 
    da.ActionNumber,
    dou.OrgUnitName,
    de.EntityName,
    da.DueDateTime,
    dcu.FirstName + ' '+ dcu.LastName as [Action Responsible],
    dets.EventTemplateSegmentLabel,
    da.ActionText,
    da.ActionRiskName,
    da.ActionRiskScore,
    das.ActionStatusName
from DimAction da
left join DimOrgUnit dou on dou.DimOrgUnitId = da.DimOrgUnitId
left join DimEntity de on de.DimEntityId = da.DimEntityId
left join DimEventSegment des on des.DimEventSegmentId = da.DimEventSegmentId
left join DimEventTemplateSegment dets on des.DimEventTemplateSegmentId = dets.DimEventTemplateSegmentId
left join DimActionStatus das on das.DimActionStatusId = da.DimActionStatusId
left join BridgeActionActionCategory baac on baac.DimActionId = da.DimActionId
left join DimActionCategory dac on dac.DimActionCategoryId = baac.DimActionCategoryId
left join DimCompanyUser dcu on dcu.DimCompanyUserId = da.DimResponsibleUserId
order by da.CreatedDateTime desc