ローン組成データテンプレート

/*
This is a configuration and transformation guide, not a single executable query.
The process involves creating two reports in Salesforce, exporting them, and then transforming the data.
*/

-- Step 1: Configuration for Report 1: 'Loan Creation Report'
-- Report Type: Loans (LLC_BI__Loan__c)
-- Columns:
--   LLC_BI__Loan__c.Name  (renamed to LoanApplicationId)
--   LLC_BI__Loan__c.CreatedDate (used for EventTime)
--   LLC_BI__Loan__c.CreatedById (user lookup, for AssignedUser)
--   LLC_BI__Loan__c.LLC_BI__Product_Package__c (for LoanProductType)
--   LLC_BI__Loan__c.LLC_BI__Amount__c (for LoanAmount)
--   LLC_BI__Loan__c.LLC_BI__Stage__a (for initial state)
--   [Other required attributes]
-- Filter:
--   CreatedDate >= [Your Start Date] AND CreatedDate <= [Your End Date]

-- Step 2: Configuration for Report 2: 'Loan History Report'
-- Report Type: Loans with Loan History (Custom Report Type)
-- Columns:
--   LLC_BI__Loan__c.Name (renamed to LoanApplicationId)
--   LoanHistory.Field (maps to 'Field/Event')
--   LoanHistory.OldValue
--   LoanHistory.NewValue
--   LoanHistory.CreatedDate (renamed to EventTime)
--   LoanHistory.CreatedById (user lookup, for AssignedUser)
--   [Other required attributes from the parent Loan object]
-- Filter:
--   Loan:CreatedDate >= [Your Start Date] AND Loan:CreatedDate <= [Your End Date]
--   AND LoanHistory.Field IN ('Stage', 'Status', '[Your Risk Rating Field]', '[Your Credit Check Field]')

-- Step 3: Post-Export Transformation Logic (to be applied with Python, Excel, etc.)
-- This logic transforms the raw report data into a final event log.

SELECT
    LoanApplicationId,
    'Loan Application Created' AS ActivityName,
    EventTime,
    AssignedUser,
    LoanProductType,
    LoanAmount,
    -- ... other attributes
FROM [Exported Loan Creation Report]

UNION ALL

SELECT
    LoanApplicationId,
    CASE
        WHEN Field = 'Stage' AND NewValue = 'Initial Review' THEN 'Initial Review Performed'
        WHEN Field = 'Stage' AND NewValue = 'Underwriting' THEN 'Underwriting Commenced'
        WHEN Field = 'Stage' AND OldValue = 'Underwriting' THEN 'Underwriting Completed'
        WHEN Field = 'Stage' AND NewValue = 'Booked' THEN 'Funds Disbursed'
        WHEN Field = 'Stage' AND NewValue = 'Declined' THEN 'Loan Declined'
        WHEN Field = 'Stage' AND NewValue = 'Withdrawn' THEN 'Application Withdrawn'
        WHEN Field = '[Your Risk Rating Field]' AND OldValue IS NULL THEN 'Risk Assessment Performed'
        WHEN Field = '[Your Credit Check Field]' AND NewValue = 'Complete' THEN 'Credit Check Completed'
        WHEN Field = 'Status' AND NewValue = 'Approved' THEN 'Loan Decision Rendered'
        WHEN Field = 'Status' AND NewValue = 'Offer Generated' THEN 'Loan Offer Generated'
        WHEN Field = 'Status' AND NewValue = 'Offer Accepted' THEN 'Loan Offer Accepted'
        WHEN Field = 'Status' AND NewValue = 'Agreement Signed' THEN 'Loan Agreement Signed'
        -- Note: Document activities are difficult to track this way and may require a separate report.
        -- These are placeholders assuming a status field is used.
        WHEN Field = 'Status' AND NewValue = 'Docs Requested' THEN 'Supporting Documents Requested'
        WHEN Field = 'Status' AND NewValue = 'Docs Received' THEN 'Supporting Documents Received'
    END AS ActivityName,
    EventTime,
    AssignedUser,
    -- ... other attributes can be joined from the Loan Creation Report by LoanApplicationId
    [LoanProductType],
    [LoanAmount]
FROM [Exported Loan History Report]
WHERE ActivityName IS NOT NULL; -- Filter out unmapped field changes

SELECT
    Loan.Name AS LoanApplicationId,
    'Loan Application Created' AS ActivityName,
    Loan.CreatedDate AS EventTime,
    Loan.LastModifiedDate AS EventEndTime,
    Loan.Owner.Name AS AssignedUser,
    Loan.LLC_BI__lookupKey__c AS AssignedDepartment, -- Or another relevant department field
    Loan.LLC_BI__Status__c AS DecisionOutcome,
    Loan.LLC_BI__Product_Line__c AS LoanProductType,
    Loan.LLC_BI__Amount__c AS LoanAmount,
    Loan.LLC_BI__Application_Channel__c AS ApplicationChannel, -- Placeholder for custom channel field
    'nCino' AS SourceSystemName,
    SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__c Loan
WHERE Loan.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND Loan.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Initial Review Performed' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.NewValue = '[Initial Review Stage Name]'
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Supporting Documents Requested' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.NewValue = '[Document Collection Stage Name]'
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Supporting Documents Received' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.OldValue = '[Document Collection Stage Name]' AND h.NewValue != '[Document Collection Stage Name]'
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Credit Check Completed' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.NewValue = '[Credit Check Stage Name]'
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SZZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Underwriting Commenced' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.NewValue = '[Underwriting Stage Name]'
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Risk Assessment Performed' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Risk_Rating__c' AND h.NewValue != NULL
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Underwriting Completed' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.OldValue = '[Underwriting Stage Name]'
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Loan Decision Rendered' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.NewValue IN ('[Approved Stage Name]', '[Declined Stage Name]')
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Loan Offer Generated' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.NewValue = '[Offer Generated Stage Name]'
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Loan Offer Accepted' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.NewValue = '[Offer Accepted Stage Name]'
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Loan Agreement Signed' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.NewValue = '[Closing Stage Name]'
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Funds Disbursed' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.NewValue = '[Booked Stage Name]'
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Loan Declined' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.NewValue = '[Declined Stage Name]'
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ

UNION ALL

SELECT
    Parent.Name AS LoanApplicationId,
    'Application Withdrawn' AS ActivityName,
    h.CreatedDate AS EventTime,
    NULL AS EventEndTime,
    h.CreatedBy.Name AS AssignedUser,
    Parent.LLC_BI__lookupKey__c AS AssignedDepartment,
    Parent.LLC_BI__Status__c AS DecisionOutcome,
    Parent.LLC_BI__Product_Line__c AS LoanProductType,
    Parent.LLC_BI__Amount__c AS LoanAmount,
    Parent.LLC_BI__Application_Channel__c AS ApplicationChannel,
    'nCino' AS SourceSystemName,
    Parent.SystemModstamp AS LastDataUpdateTimestamp
FROM LLC_BI__Loan__cHistory h
WHERE h.Field = 'LLC_BI__Stage__c' AND h.NewValue = '[Withdrawn Stage Name]'
AND h.CreatedDate >= YYYY-MM-DDTHH:MM:SSZ AND h.CreatedDate <= YYYY-MM-DDTHH:MM:SSZ