KYC顧客オンボーディング用データテンプレート

/*
 This is a logical representation of the configuration needed in the Fenergo Reporting & Analytics module.
 The module uses a graphical interface, but this query structure illustrates the required data sources, filters, and unions.
 Fields like [CaseLifecycleData].[CaseID] are placeholders for actual Fenergo fields selected in the UI.
*/

-- Base data selection for common attributes
WITH CaseAttributes AS (
    SELECT
        C.CaseID AS CustomerApplication,
        C.SlaTargetDate AS SlaTargetDate,
        C.FinalRiskScore AS RiskScore,
        C.CurrentStatus AS ApplicationStatus
    FROM [CaseDetails] C
    WHERE C.CaseType = 'KYC Customer Onboarding'
)

-- 1. Case Created
SELECT
    A.CustomerApplication,
    'Case Created' AS ActivityName,
    L.CreationTimestamp AS EventStartTime,
    L.CompletionTimestamp AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseLifecycleEvents] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.EventType = 'CASE_CREATED'
  AND L.CreationTimestamp >= '[StartDate]' AND L.CreationTimestamp <= '[EndDate]'

UNION ALL

-- 2. Initial Screening Performed
SELECT
    A.CustomerApplication,
    'Initial Screening Performed' AS ActivityName,
    L.CompletionTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseLifecycleEvents] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.TaskName = 'Initial Screening' AND L.TaskStatus = 'Completed'
  AND L.CompletionTimestamp >= '[StartDate]' AND L.CompletionTimestamp <= '[EndDate]'

UNION ALL

-- 3. Data & Documents Requested
SELECT
    A.CustomerApplication,
    'Data & Documents Requested' AS ActivityName,
    L.CreationTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseLifecycleEvents] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.EventType = 'CUSTOMER_COMMUNICATION' AND L.TemplateName = 'Initial Document Request'
  AND L.CreationTimestamp >= '[StartDate]' AND L.CreationTimestamp <= '[EndDate]'

UNION ALL

-- 4. Documents Received
SELECT
    A.CustomerApplication,
    'Documents Received' AS ActivityName,
    L.StatusChangeTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseStatusHistory] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.NewStatus = 'Pending Review'
  AND L.StatusChangeTimestamp >= '[StartDate]' AND L.StatusChangeTimestamp <= '[EndDate]'

UNION ALL

-- 5. Document Review Completed
SELECT
    A.CustomerApplication,
    'Document Review Completed' AS ActivityName,
    L.CompletionTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseLifecycleEvents] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.TaskName = 'Document Verification' AND L.TaskStatus = 'Completed'
  AND L.CompletionTimestamp >= '[StartDate]' AND L.CompletionTimestamp <= '[EndDate]'

UNION ALL

-- 6. Background Checks Initiated
SELECT
    A.CustomerApplication,
    'Background Checks Initiated' AS ActivityName,
    L.CreationTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseLifecycleEvents] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.EventType = 'EXTERNAL_CHECK_INITIATED'
  AND L.CreationTimestamp >= '[StartDate]' AND L.CreationTimestamp <= '[EndDate]'

UNION ALL

-- 7. Risk Assessment Completed
SELECT
    A.CustomerApplication,
    'Risk Assessment Completed' AS ActivityName,
    L.CompletionTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseLifecycleEvents] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.TaskName = 'Risk Assessment' AND L.TaskStatus = 'Completed'
  AND L.CompletionTimestamp >= '[StartDate]' AND L.CompletionTimestamp <= '[EndDate]'

UNION ALL

-- 8. Compliance Review Initiated
SELECT
    A.CustomerApplication,
    'Compliance Review Initiated' AS ActivityName,
    L.StatusChangeTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseStatusHistory] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.NewStatus = 'Pending Compliance Review'
  AND L.StatusChangeTimestamp >= '[StartDate]' AND L.StatusChangeTimestamp <= '[EndDate]'

UNION ALL

-- 9. Additional Information Requested
SELECT
    A.CustomerApplication,
    'Additional Information Requested' AS ActivityName,
    L.CreationTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseLifecycleEvents] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.EventType = 'CUSTOMER_COMMUNICATION' AND L.TemplateName = 'Additional Information Request'
  AND L.CreationTimestamp >= '[StartDate]' AND L.CreationTimestamp <= '[EndDate]'

UNION ALL

-- 10. Compliance Review Completed
SELECT
    A.CustomerApplication,
    'Compliance Review Completed' AS ActivityName,
    L.CompletionTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseLifecycleEvents] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.TaskName = 'Compliance Review' AND L.TaskStatus = 'Completed'
  AND L.CompletionTimestamp >= '[StartDate]' AND L.CompletionTimestamp <= '[EndDate]'

UNION ALL

-- 11. Application Approved
SELECT
    A.CustomerApplication,
    'Application Approved' AS ActivityName,
    L.StatusChangeTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseStatusHistory] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.NewStatus = 'Approved'
  AND L.StatusChangeTimestamp >= '[StartDate]' AND L.StatusChangeTimestamp <= '[EndDate]'

UNION ALL

-- 12. Application Rejected
SELECT
    A.CustomerApplication,
    'Application Rejected' AS ActivityName,
    L.StatusChangeTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseStatusHistory] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.NewStatus = 'Rejected'
  AND L.StatusChangeTimestamp >= '[StartDate]' AND L.StatusChangeTimestamp <= '[EndDate]'

UNION ALL

-- 13. Account Activated
SELECT
    A.CustomerApplication,
    'Account Activated' AS ActivityName,
    L.StatusChangeTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseStatusHistory] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.NewStatus = 'Active'
  AND L.StatusChangeTimestamp >= '[StartDate]' AND L.StatusChangeTimestamp <= '[EndDate]'

UNION ALL

-- 14. Case Closed
SELECT
    A.CustomerApplication,
    'Case Closed' AS ActivityName,
    L.StatusChangeTimestamp AS EventStartTime,
    NULL AS EventEndTime,
    L.EventUser AS InitiatingUser,
    U.Department AS UserDepartment,
    A.ApplicationStatus,
    A.SlaTargetDate,
    A.RiskScore,
    'Fenergo' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM [CaseStatusHistory] L
JOIN CaseAttributes A ON L.CaseID = A.CustomerApplication
LEFT JOIN [Users] U ON L.EventUser = U.UserID
WHERE L.NewStatus = 'Closed'
  AND L.StatusChangeTimestamp >= '[StartDate]' AND L.StatusChangeTimestamp <= '[EndDate]'