Mẫu dữ liệu quản lý hợp đồng

// This is a configuration guide for building the necessary Salesforce Reports.
// It is not an executable query but a structured definition for the UI-based Report Builder.

// ======== Report 1: Core Contract Status Changes ========
// Report Type: Contract Agreement History
// Format: Tabular
// --- Columns ---
// Contract Agreement: Contract ID -> Map to ContractId
// Edit Date -> Map to EventTimestamp
// Field/Event
// Old Value
// New Value
// Edited By -> Map to ContractOwner (or a more suitable user lookup)
// --- Filters ---
// 1. Edit Date = Last 12 Months (or your desired range)
// 2. Field/Event = [Your Contract Status Field Name]
// --- Transformation Logic ---
// Create ActivityName based on the 'New Value' of the status.
// Example: IF 'New Value' = 'In Internal Review', THEN ActivityName = 'Internal Review Started'.
// Activities Captured: Contract Drafted, Internal Review Started, Legal Review Conducted, Contract Activated, Counterparty Approval Received, Contract Terminated.

// ======== Report 2: Contract Creation and Execution ========
// Report Type: Contract Agreement
// Format: Tabular
// --- Columns ---
// Contract ID -> Map to ContractId
// Created Date -> Timestamp for 'Contract Request Initiated'
// [Executed Date Field] -> Timestamp for 'Contract Executed/Signed'
// [Effective Date Field] -> Timestamp for 'Contract Activated' (if status is not used)
// Owner -> Map to ContractOwner
// [Contract Type Field] -> Map to ContractType
// [Contract Value Field] -> Map to ContractValue
// Status -> Map to ContractStatus
// [Expiration Date Field] -> Map to ExpirationDate
// --- Filters ---
// 1. Created Date = Last 12 Months (or your desired range)

// ======== Report 3: Contract Revisions ========
// Report Type: [Contracts with Versions] (or similar custom report type)
// Format: Tabular
// --- Columns ---
// Contract Agreement: Contract ID -> Map to ContractId
// Version: Created Date -> Map to EventTimestamp for 'Contract Redlined/Revised'
// --- Filters ---
// 1. Version: Created Date = Last 12 Months (or your desired range)

// ======== Report 4: Amendment Requests ========
// Report Type: [Contracts with Amendments] (or similar custom report type)
// Format: Tabular
// --- Columns ---
// Contract Agreement: Contract ID -> Map to ContractId
// Amendment: Created Date -> Map to EventTimestamp for 'Amendment Requested'
// --- Filters ---
// 1. Amendment: Created Date = Last 12 Months (or your desired range)

// ======== Report 5: Approval and Other Tasks ========
// Report Type: Activities (with Contract Agreement)
// Format: Tabular
// --- Columns ---
// Related To ID (Contract ID) -> Map to ContractId
// Subject -> Use to identify ActivityName
// Created Date / Completed Date -> Map to EventTimestamp
// --- Filters ---
// 1. Activity Date = Last 12 Months (or your desired range)
// 2. Subject CONTAINS 'Sent to Counterparty', 'Compliance Review', 'Internal Approval'
// --- Transformation Logic ---
// Create ActivityName based on 'Subject'. This is highly dependent on your configuration.
// Activities Captured: Contract Sent To Counterparty, Internal Approvals Obtained, Compliance Review Performed, Contract Renewed (if tracked as a task).

// ======== Post-Export Step: Generate Expired Events ========
// For contracts where ExpirationDate < TODAY and status is not 'Terminated' or 'Expired':
// Create a new row with:
// ActivityName = 'Contract Expired'
// EventTimestamp = ExpirationDate

SELECT Id AS ContractId, 'Contract Request Initiated' AS ActivityName, CreatedDate AS EventTimestamp, CreatedDate AS EventEndTime, Owner.Name AS ContractOwner, Apttus__Agreement_Category__c AS ContractType, Apttus__Total_Contract_Value__c AS ContractValue, Apttus__Status__c AS ContractStatus, Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__APTS_Agreement__c WHERE CreatedDate >= [Start Date] AND CreatedDate <= [End Date]
UNION ALL
SELECT ParentId AS ContractId, 'Contract Drafted' AS ActivityName, CreatedDate AS EventTimestamp, CreatedDate AS EventEndTime, Parent.Owner.Name AS ContractOwner, Parent.Apttus__Agreement_Category__c AS ContractType, Parent.Apttus__Total_Contract_Value__c AS ContractValue, Parent.Apttus__Status__c AS ContractStatus, Parent.Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, Parent.SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__APTS_Agreement__History WHERE Field = 'Apttus__Status__c' AND NewValue = '[Your Draft Status]' AND CreatedDate >= [Start Date] AND CreatedDate <= [End Date]
UNION ALL
SELECT ParentId AS ContractId, 'Internal Review Started' AS ActivityName, CreatedDate AS EventTimestamp, CreatedDate AS EventEndTime, Parent.Owner.Name AS ContractOwner, Parent.Apttus__Agreement_Category__c AS ContractType, Parent.Apttus__Total_Contract_Value__c AS ContractValue, Parent.Apttus__Status__c AS ContractStatus, Parent.Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, Parent.SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__APTS_Agreement__History WHERE Field = 'Apttus__Status__c' AND NewValue = '[Your Internal Review Status]' AND CreatedDate >= [Start Date] AND CreatedDate <= [End Date]
UNION ALL
SELECT ParentId AS ContractId, 'Legal Review Conducted' AS ActivityName, CreatedDate AS EventTimestamp, CreatedDate AS EventEndTime, Parent.Owner.Name AS ContractOwner, Parent.Apttus__Agreement_Category__c AS ContractType, Parent.Apttus__Total_Contract_Value__c AS ContractValue, Parent.Apttus__Status__c AS ContractStatus, Parent.Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, Parent.SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__APTS_Agreement__History WHERE Field = 'Apttus__Status__c' AND NewValue = '[Your Legal Review Complete Status]' AND CreatedDate >= [Start Date] AND CreatedDate <= [End Date]
UNION ALL
SELECT ParentId AS ContractId, 'Contract Sent To Counterparty' AS ActivityName, CreatedDate AS EventTimestamp, CreatedDate AS EventEndTime, Parent.Owner.Name AS ContractOwner, Parent.Apttus__Agreement_Category__c AS ContractType, Parent.Apttus__Total_Contract_Value__c AS ContractValue, Parent.Apttus__Status__c AS ContractStatus, Parent.Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, Parent.SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__APTS_Agreement__History WHERE Field = 'Apttus__Status__c' AND NewValue = '[Your Sent to Counterparty Status]' AND CreatedDate >= [Start Date] AND CreatedDate <= [End Date]
UNION ALL
SELECT Apttus__Agreement__c AS ContractId, 'Contract Redlined/Revised' AS ActivityName, CreatedDate AS EventTimestamp, CreatedDate AS EventEndTime, Apttus__Agreement__r.Owner.Name AS ContractOwner, Apttus__Agreement__r.Apttus__Agreement_Category__c AS ContractType, Apttus__Agreement__r.Apttus__Total_Contract_Value__c AS ContractValue, Apttus__Agreement__r.Apttus__Status__c AS ContractStatus, Apttus__Agreement__r.Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, Apttus__Agreement__r.SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__Document_Version__c WHERE Apttus__Agreement__c != NULL AND CreatedDate >= [Start Date] AND CreatedDate <= [End Date]
UNION ALL
SELECT TargetObjectId AS ContractId, 'Internal Approvals Obtained' AS ActivityName, CompletedDate AS EventTimestamp, CompletedDate AS EventEndTime, TargetObject.Owner.Name AS ContractOwner, TargetObject.Apttus__Agreement_Category__c AS ContractType, TargetObject.Apttus__Total_Contract_Value__c AS ContractValue, TargetObject.Apttus__Status__c AS ContractStatus, TargetObject.Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, TargetObject.SystemModstamp AS LastDataUpdateTimestamp FROM ProcessInstance WHERE Status = 'Approved' AND TargetObject.Type = 'Apttus__APTS_Agreement__c' AND CompletedDate >= [Start Date] AND CompletedDate <= [End Date]
UNION ALL
SELECT ParentId AS ContractId, 'Counterparty Approval Received' AS ActivityName, CreatedDate AS EventTimestamp, CreatedDate AS EventEndTime, Parent.Owner.Name AS ContractOwner, Parent.Apttus__Agreement_Category__c AS ContractType, Parent.Apttus__Total_Contract_Value__c AS ContractValue, Parent.Apttus__Status__c AS ContractStatus, Parent.Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, Parent.SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__APTS_Agreement__History WHERE Field = 'Apttus__Status__c' AND NewValue = '[Your Counterparty Approved Status]' AND CreatedDate >= [Start Date] AND CreatedDate <= [End Date]
UNION ALL
SELECT ParentId AS ContractId, 'Contract Executed/Signed' AS ActivityName, CreatedDate AS EventTimestamp, CreatedDate AS EventEndTime, Parent.Owner.Name AS ContractOwner, Parent.Apttus__Agreement_Category__c AS ContractType, Parent.Apttus__Total_Contract_Value__c AS ContractValue, Parent.Apttus__Status__c AS ContractStatus, Parent.Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, Parent.SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__APTS_Agreement__History WHERE Field = 'Apttus__Status__c' AND NewValue = '[Your Executed Status]' AND CreatedDate >= [Start Date] AND CreatedDate <= [End Date]
UNION ALL
SELECT ParentId AS ContractId, 'Contract Activated' AS ActivityName, CreatedDate AS EventTimestamp, CreatedDate AS EventEndTime, Parent.Owner.Name AS ContractOwner, Parent.Apttus__Agreement_Category__c AS ContractType, Parent.Apttus__Total_Contract_Value__c AS ContractValue, Parent.Apttus__Status__c AS ContractStatus, Parent.Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, Parent.SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__APTS_Agreement__History WHERE Field = 'Apttus__Status__c' AND NewValue = '[Your Active Status]' AND CreatedDate >= [Start Date] AND CreatedDate <= [End Date]
UNION ALL
SELECT WhatId AS ContractId, 'Compliance Review Performed' AS ActivityName, CompletedDateTime AS EventTimestamp, CompletedDateTime AS EventEndTime, What.Owner.Name AS ContractOwner, What.Apttus__Agreement_Category__c AS ContractType, What.Apttus__Total_Contract_Value__c AS ContractValue, What.Apttus__Status__c AS ContractStatus, What.Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, What.SystemModstamp AS LastDataUpdateTimestamp FROM Task WHERE What.Type = 'Apttus__APTS_Agreement__c' AND Subject = '[Your Compliance Review Task Subject]' AND Status = 'Completed' AND CompletedDateTime >= [Start Date] AND CompletedDateTime <= [End Date]
UNION ALL
SELECT Id AS ContractId, 'Amendment Requested' AS ActivityName, CreatedDate AS EventTimestamp, CreatedDate AS EventEndTime, Owner.Name AS ContractOwner, Apttus__Agreement_Category__c AS ContractType, Apttus__Total_Contract_Value__c AS ContractValue, Apttus__Status__c AS ContractStatus, Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__APTS_Agreement__c WHERE Apttus__Agreement_Category__c = 'Amendment' AND CreatedDate >= [Start Date] AND CreatedDate <= [End Date]
UNION ALL
SELECT Id AS ContractId, 'Contract Renewed' AS ActivityName, CreatedDate AS EventTimestamp, CreatedDate AS EventEndTime, Owner.Name AS ContractOwner, Apttus__Agreement_Category__c AS ContractType, Apttus__Total_Contract_Value__c AS ContractValue, Apttus__Status__c AS ContractStatus, Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__APTS_Agreement__c WHERE Apttus__Agreement_Category__c = 'Renewal' AND CreatedDate >= [Start Date] AND CreatedDate <= [End Date]
UNION ALL
SELECT ParentId AS ContractId, 'Contract Terminated' AS ActivityName, CreatedDate AS EventTimestamp, CreatedDate AS EventEndTime, Parent.Owner.Name AS ContractOwner, Parent.Apttus__Agreement_Category__c AS ContractType, Parent.Apttus__Total_Contract_Value__c AS ContractValue, Parent.Apttus__Status__c AS ContractStatus, Parent.Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, Parent.SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__APTS_Agreement__History WHERE Field = 'Apttus__Status__c' AND NewValue = '[Your Terminated Status]' AND CreatedDate >= [Start Date] AND CreatedDate <= [End Date]
UNION ALL
SELECT Id AS ContractId, 'Contract Expired' AS ActivityName, Apttus__End_Date__c AS EventTimestamp, Apttus__End_Date__c AS EventEndTime, Owner.Name AS ContractOwner, Apttus__Agreement_Category__c AS ContractType, Apttus__Total_Contract_Value__c AS ContractValue, Apttus__Status__c AS ContractStatus, Apttus__End_Date__c AS ExpirationDate, 'Conga CLM' AS SourceSystemName, SystemModstamp AS LastDataUpdateTimestamp FROM Apttus__APTS_Agreement__c WHERE Apttus__Status__c = '[Your Expired Status]' AND Apttus__End_Date__c >= [Start Date] AND Apttus__End_Date__c <= [End Date]

import pandas as pd
from simple_salesforce import Salesforce, SalesforceLogin
from datetime import datetime
import json

class=class="hl-string">"hl-comment"># --- 1. CONFIGURATION --- 
class=class="hl-string">"hl-comment"># Replace with your Salesforce credentials and Connected App details
SF_CONNECTION = {
    class="hl-string">"username": class="hl-string">"your_salesforce_username@example.com",
    class="hl-string">"password": class="hl-string">"your_salesforce_password",
    class="hl-string">"security_token": class="hl-string">"your_salesforce_security_token",
    class="hl-string">"instance_url": class="hl-string">"https://your_instance.my.salesforce.com"
}

class=class="hl-string">"hl-comment"># Define the date range for the extraction
START_DATE = class="hl-string">'2023-01-01T00:00:00Z'
END_DATE = datetime.utcnow().strftime(class="hl-string">'%Y-%m-%dT%H:%M:%SZ')

class=class="hl-string">"hl-comment"># Define the source system name
SOURCE_SYSTEM = class="hl-string">'Conga CLM'

class=class="hl-string">"hl-comment"># --- 2. AUTHENTICATION --- 
def connect_to_salesforce():
    class="hl-string">"""Connects to Salesforce and returns a session object."""
    try:
        session_id, instance = SalesforceLogin(
            username=SF_CONNECTION[class="hl-string">'username'],
            password=SF_CONNECTION[class="hl-string">'password'],
            security_token=SF_CONNECTION[class="hl-string">'security_token'],
            instance_url=SF_CONNECTION[class="hl-string">'instance_url']
        )
        sf = Salesforce(session_id=session_id, instance=instance)
        print(class="hl-string">"Successfully connected to Salesforce.")
        return sf
    except Exception as e:
        print(fclass="hl-string">"Salesforce connection failed: {e}")
        return None

class=class="hl-string">"hl-comment"># --- 3. DATA EXTRACTION QUERIES --- 
class=class="hl-string">"hl-comment"># NOTE: Replace placeholder statuses like class="hl-string">'[Your Status for Drafted]' with actual values from your Conga CLM.
def get_soql_queries():
    class="hl-string">"""Returns a dictionary of SOQL queries for each activity."""
    queries = {
        class="hl-string">'Contract Request Initiated': fclass="hl-string">"""
            SELECT Id, CreatedDate, Owner.Name, Apttus__Agreement_Type__c, Apttus__Total_Contract_Value__c, Apttus__Status_Category__c, Apttus__Contract_End_Date__c
            FROM Apttus__APTS_Agreement__c
            WHERE CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Contract Drafted': fclass="hl-string">"""
            SELECT ParentId, CreatedDate, NewValue
            FROM Apttus__APTS_Agreement__History
            WHERE Field = 'Apttus__Status_Category__c' AND NewValue = '[Your Status for Drafted]'
            AND CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Internal Review Started': fclass="hl-string">"""
            SELECT ParentId, CreatedDate, NewValue
            FROM Apttus__APTS_Agreement__History
            WHERE Field = 'Apttus__Status_Category__c' AND NewValue = '[Your Status for Internal Review]'
            AND CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Legal Review Conducted': fclass="hl-string">"""
            SELECT ParentId, CreatedDate, NewValue
            FROM Apttus__APTS_Agreement__History
            WHERE Field = 'Apttus__Status_Category__c' AND NewValue = '[Your Status for Legal Review Complete]'
            AND CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Contract Sent To Counterparty': fclass="hl-string">"""
             SELECT ParentId, CreatedDate, NewValue
             FROM Apttus__APTS_Agreement__History
             WHERE Field = 'Apttus__Status_Category__c' AND NewValue = '[Your Status for Sent to Counterparty]'
             AND CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Contract Redlined/Revised': fclass="hl-string">"""
            SELECT Apttus__Agreement__c, CreatedDate
            FROM Apttus__Document_Version__c
            WHERE CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Internal Approvals Obtained': fclass="hl-string">"""
            SELECT TargetObjectId, CreatedDate
            FROM ProcessInstanceStep
            WHERE StepStatus = 'Approved' 
            AND IsDeleted = FALSE
            AND ProcessInstance.TargetObject.Type = 'Apttus__APTS_Agreement__c'
            AND CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Counterparty Approval Received': fclass="hl-string">"""
            SELECT ParentId, CreatedDate, NewValue
            FROM Apttus__APTS_Agreement__History
            WHERE Field = 'Apttus__Status_Category__c' AND NewValue = '[Your Status for Counterparty Approved]'
            AND CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Contract Executed/Signed': fclass="hl-string">"""
            SELECT ParentId, CreatedDate, NewValue
            FROM Apttus__APTS_Agreement__History
            WHERE Field = 'Apttus__Status_Category__c' AND NewValue = '[Your Status for Executed]'
            AND CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Contract Activated': fclass="hl-string">"""
            SELECT ParentId, CreatedDate, NewValue
            FROM Apttus__APTS_Agreement__History
            WHERE Field = 'Apttus__Status_Category__c' AND NewValue = '[Your Status for Active]'
            AND CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Compliance Review Performed': fclass="hl-string">"""
            SELECT ParentId, CreatedDate, NewValue
            FROM Apttus__APTS_Agreement__History
            WHERE Field = '[Your Field for Compliance Review]' AND NewValue = 'Completed'
            AND CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Amendment Requested': fclass="hl-string">"""
            SELECT Id, CreatedDate
            FROM Apttus__APTS_Agreement__c
            WHERE Apttus__Agreement_Category__c = 'Amendment'
            AND CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Contract Renewed': fclass="hl-string">"""
            SELECT ParentId, CreatedDate, NewValue
            FROM Apttus__APTS_Agreement__History
            WHERE Field = 'Apttus__Status_Category__c' AND NewValue = '[Your Status for Renewed]'
            AND CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """,
        class="hl-string">'Contract Terminated': fclass="hl-string">"""
            SELECT ParentId, CreatedDate, NewValue
            FROM Apttus__APTS_Agreement__History
            WHERE Field = 'Apttus__Status_Category__c' AND NewValue = '[Your Status for Terminated]'
            AND CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}
        """
    }
    return queries

class=class="hl-string">"hl-comment"># --- 4. DATA PROCESSING --- 
def fetch_and_process_data(sf, queries):
    class="hl-string">"""Executes queries and transforms data into an event log format."""
    all_events = []
    class=class="hl-string">"hl-comment"># Base query to get contract attributes
    print(class="hl-string">"Fetching base contract data...")
    base_data_query = fclass="hl-string">"""SELECT Id, Owner.Name, Apttus__Agreement_Type__c, Apttus__Total_Contract_Value__c, Apttus__Status_Category__c, Apttus__Contract_End_Date__c FROM Apttus__APTS_Agreement__c WHERE CreatedDate >= {START_DATE} AND CreatedDate <= {END_DATE}"""
    base_df = pd.DataFrame(sf.query_all(base_data_query)[class="hl-string">'records'])
    if not base_df.empty:
        base_df[class="hl-string">'Owner'] = base_df[class="hl-string">'Owner'].apply(lambda x: x[class="hl-string">'Name'] if x else None)
        base_df = base_df.drop(columns=[class="hl-string">'attributes'])
        base_df.set_index(class="hl-string">'Id', inplace=True)
    
    for activity_name, soql in queries.items():
        print(fclass="hl-string">"Fetching data for: {activity_name}...")
        try:
            results = sf.query_all(soql)[class="hl-string">'records']
            if not results:
                print(fclass="hl-string">" -> No records found for {activity_name}.")
                continue

            df = pd.DataFrame(results)
            df[class="hl-string">'ActivityName'] = activity_name

            class=class="hl-string">"hl-comment"># --- Column Renaming based on source object ---
            if class="hl-string">'ParentId' in df.columns: class=class="hl-string">"hl-comment"># From History object
                df.rename(columns={class="hl-string">'ParentId': class="hl-string">'ContractId', class="hl-string">'CreatedDate': class="hl-string">'EventTimestamp'}, inplace=True)
            elif class="hl-string">'Apttus__Agreement__c' in df.columns: class=class="hl-string">"hl-comment"># From Document Version
                df.rename(columns={class="hl-string">'Apttus__Agreement__c': class="hl-string">'ContractId', class="hl-string">'CreatedDate': class="hl-string">'EventTimestamp'}, inplace=True)
            elif class="hl-string">'TargetObjectId' in df.columns: class=class="hl-string">"hl-comment"># From ProcessInstanceStep
                df.rename(columns={class="hl-string">'TargetObjectId': class="hl-string">'ContractId', class="hl-string">'CreatedDate': class="hl-string">'EventTimestamp'}, inplace=True)
            else: class=class="hl-string">"hl-comment"># From Agreement object itself
                 df.rename(columns={class="hl-string">'Id': class="hl-string">'ContractId', class="hl-string">'CreatedDate': class="hl-string">'EventTimestamp'}, inplace=True)

            class=class="hl-string">"hl-comment"># Merge with base data to get attributes
            df = df.join(base_df, on=class="hl-string">'ContractId')
            
            class=class="hl-string">"hl-comment"># Select and reorder columns
            df = df[[class="hl-string">'ContractId', class="hl-string">'ActivityName', class="hl-string">'EventTimestamp'] + [col for col in base_df.columns if col in df.columns]]
            all_events.append(df)
        except Exception as e:
            print(fclass="hl-string">" -> Error fetching {activity_name}: {e}")

    if not all_events:
        print(class="hl-string">"No events were extracted.")
        return pd.DataFrame()

    event_log = pd.concat(all_events, ignore_index=True)

    class=class="hl-string">"hl-comment"># --- Add Derived class="hl-string">'Contract Expired' Event --- 
    print(class="hl-string">"Deriving 'Contract Expired' events...")
    expired_query = fclass="hl-string">"""
        SELECT Id, Apttus__Contract_End_Date__c
        FROM Apttus__APTS_Agreement__c 
        WHERE Apttus__Contract_End_Date__c < {datetime.utcnow().strftime('%Y-%m-%d')} 
        AND Apttus__Status_Category__c = '[Your Status for Active]'
    """
    expired_results = sf.query_all(expired_query)[class="hl-string">'records']
    if expired_results:
        expired_df = pd.DataFrame(expired_results)
        expired_df.rename(columns={class="hl-string">'Id': class="hl-string">'ContractId', class="hl-string">'Apttus__Contract_End_Date__c': class="hl-string">'EventTimestamp'}, inplace=True)
        expired_df[class="hl-string">'ActivityName'] = class="hl-string">'Contract Expired'
        expired_df = expired_df.join(base_df, on=class="hl-string">'ContractId')
        event_log = pd.concat([event_log, expired_df], ignore_index=True)

    class=class="hl-string">"hl-comment"># --- 5. FINAL TRANSFORMATION --- 
    print(class="hl-string">"Finalizing the event log...")
    class=class="hl-string">"hl-comment"># Rename columns to match the target schema
    event_log.rename(columns={
        class="hl-string">'Owner': class="hl-string">'ContractOwner',
        class="hl-string">'Apttus__Agreement_Type__c': class="hl-string">'ContractType',
        class="hl-string">'Apttus__Total_Contract_Value__c': class="hl-string">'ContractValue',
        class="hl-string">'Apttus__Status_Category__c': class="hl-string">'ContractStatus',
        class="hl-string">'Apttus__Contract_End_Date__c': class="hl-string">'ExpirationDate'
    }, inplace=True)
    
    class=class="hl-string">"hl-comment"># Add static and timestamp columns
    event_log[class="hl-string">'SourceSystemName'] = SOURCE_SYSTEM
    event_log[class="hl-string">'LastDataUpdateTimestamp'] = datetime.utcnow().isoformat()
    event_log[class="hl-string">'EventEndTime'] = pd.NaT class=class="hl-string">"hl-comment"># Or set based on next event if needed

    class=class="hl-string">"hl-comment"># Reorder for clarity
    final_columns = [
        class="hl-string">'ContractId', class="hl-string">'ActivityName', class="hl-string">'EventTimestamp', class="hl-string">'EventEndTime',
        class="hl-string">'ContractOwner', class="hl-string">'ContractType', class="hl-string">'ContractValue', class="hl-string">'ContractStatus',
        class="hl-string">'ExpirationDate', class="hl-string">'SourceSystemName', class="hl-string">'LastDataUpdateTimestamp'
    ]
    event_log = event_log[[col for col in final_columns if col in event_log.columns]]
    
    class=class="hl-string">"hl-comment"># Sort by case and timestamp
    event_log.sort_values(by=[class="hl-string">'ContractId', class="hl-string">'EventTimestamp'], inplace=True)
    
    return event_log

class=class="hl-string">"hl-comment"># --- 6. MAIN EXECUTION --- 
if __name__ == class="hl-string">"__main__":
    salesforce_session = connect_to_salesforce()
    if salesforce_session:
        soql_queries = get_soql_queries()
        final_event_log = fetch_and_process_data(salesforce_session, soql_queries)

        if not final_event_log.empty:
            output_filename = class="hl-string">'conga_clm_event_log.csv'
            final_event_log.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%fZ')
            print(fclass="hl-string">"\nExtraction complete. Event log saved to {output_filename}")
            print(fclass="hl-string">"Total events extracted: {len(final_event_log)}")