เทมเพลตข้อมูลรายการสมุดรายวันสำหรับ Record to Report

/*
This is not a single executable query. Instead, it defines the configuration of a Data Management Framework export project. The following entities and fields must be selected and then combined using an external script to build the final event log.
*/

-- Configuration for Entity 1: Ledger journal headers
-- Purpose: Extracts core journal header data for Created, Posted, and Reversal events.
-- Source Entity: Ledger journal headers
-- Key Fields to Map:
--   JournalBatchNumber -> JournalEntryId
--   CREATEDDATETIME -> EventTime (for 'Journal Entry Created')
--   CREATEDBY -> UserName (for 'Journal Entry Created')
--   LedgerJournalWFApprovalStatus -> JournalStatus (for workflow context)
--   JournalType -> JournalType
--   company -> LegalEntity
--   PostedDateTime -> EventTime (for 'Journal Entry Posted')
--   PostedBy -> UserName (for 'Journal Entry Posted')
--   ReverseDate -> EventTime (for 'Journal Entry Reversal Initiated')
--   MODIFIEDDATETIME -> Used to infer 'Journal Entry Corrected'
--   MODIFIEDBY -> UserName

-- Configuration for Entity 2: Ledger journal lines
-- Purpose: Extracts individual transaction lines to create 'Journal Line Added' events.
-- Source Entity: Ledger journal lines
-- Key Fields to Map:
--   JournalBatchNumber -> JournalEntryId
--   CREATEDDATETIME -> EventTime (for 'Journal Line Added')
--   CREATEDBY -> UserName (for 'Journal Line Added')
--   AmountInTransactionCurrency -> JournalTotalAmount (Note: Must be aggregated per JournalEntryId)
--   company -> LegalEntity

-- Configuration for Entity 3: Workflow tracking status table
-- Purpose: Extracts detailed workflow history for approval and rejection events.
-- Source Entity: Workflow tracking status table
-- Key Fields to Map:
--   ContextRecId -> (This needs to be joined back to the RecId of the LedgerJournalTable to get the JournalBatchNumber)
--   ContextTableId -> (Used to filter for Journal related workflows)
--   CREATEDDATETIME -> EventTime
--   User -> UserName
--   TrackingStatus -> Used to derive ActivityName ('Submitted For Approval', 'Journal Approved', 'Journal Rejected')
--   Comment -> RejectionReason

-- **Post-Processing Transformation Logic (to be implemented in an external script)**

-- 1. For each row in 'Ledger journal headers' export:
--    - Create one row with ActivityName = 'Journal Entry Created', EventTime = CREATEDDATETIME.
--    - If PostedDateTime is not null, create one row with ActivityName = 'Journal Entry Posted', EventTime = PostedDateTime.
--    - If ReverseDate is not null, create one row with ActivityName = 'Journal Entry Reversal Initiated', EventTime = ReverseDate. (A separate 'Journal Entry Reversed' event may need to be derived from the creation of the reversing journal).

-- 2. For each row in 'Ledger journal lines' export:
--    - Create one row with ActivityName = 'Journal Line Added', EventTime = CREATEDDATETIME.

-- 3. For each row in 'Workflow tracking status table' export (after joining to get JournalBatchNumber):
--    - IF TrackingStatus indicates submission, create ActivityName = 'Journal Submitted For Approval'.
--    - IF TrackingStatus indicates approval, create ActivityName = 'Journal Approved'.
--    - IF TrackingStatus indicates rejection, create ActivityName = 'Journal Rejected'.
--    - Other activities like 'Journal Review Started', 'Journal Entry Corrected', 'Journal Resubmitted For Approval', and 'Journal Posting Attempted' are often not explicitly logged. They must be inferred from status changes, user actions within a timeframe, or modification timestamps on the journal header after a rejection event.

DECLARE @StartDate DATETIME = '2023-01-01';
DECLARE @EndDate DATETIME = '2023-12-31';

WITH JournalHeaders AS (
    SELECT 
        JOURNALBATCHNUMBER AS JournalEntryId,
        JOURNALTYPE AS JournalType,
        DOCUMENTSTATUS AS JournalStatus, -- Captures Posted, etc.
        APPROVALSTATUS AS ApprovalStatus, -- Captures Draft, InReview, Approved, Rejected
        LOG AS RejectionReason, -- Often contains rejection comments
        POSTEDDATETIME, 
        CREATEDDATETIME,
        CREATEDBY,
        MODIFIEDDATETIME,
        MODIFIEDBY,
        DATA indignationId
    FROM 
        [YourD365DB].[dbo].LedgerJournalTable
    WHERE 
        CREATEDDATETIME BETWEEN @StartDate AND @EndDate
        AND DATA indignationId = '[YourLegalEntity]'
),
WorkflowEvents AS (
    SELECT 
        wtst.CONTEXTRECID,
        wtst.CONTEXTTABLEID,
        wtst.USERID,
        wtst.CREATEDDATETIME,
        wtst.TRACKINGSTATUS, -- Submitted, Approved, Rejected
        wtsi.INSTANCENUMBER
    FROM 
        [YourD365DB].[dbo].WorkflowTrackingStatusTable wtst
    JOIN 
        [YourD365DB].[dbo].WorkflowTrackingTable wtsi ON wtst.WORKFLOWTRACKINGTABLEINSTANCENUMBER = wtsi.RECID
    WHERE 
        wtst.CONTEXTTABLEID = (SELECT TABLEID FROM [YourD365DB].[dbo].SysTableIdTable WHERE NAME = 'LedgerJournalTable')
)

-- 1. Journal Entry Created
SELECT 
    jh.JournalEntryId AS JournalEntryId,
    'Journal Entry Created' AS ActivityName,
    jh.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    CAST(jh.ApprovalStatus AS VARCHAR) AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    (SELECT SUM(ABS(jlt.AMOUNTCREDIT)) FROM [YourD365DB].[dbo].LedgerJournalTrans jlt WHERE jlt.JOURNALBATCHNUMBER = jh.JournalEntryId) AS JournalTotalAmount
FROM 
    JournalHeaders jh
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON jh.CREATEDBY = u.ID

UNION ALL

-- 2. Journal Line Added
SELECT 
    jlt.JOURNALBATCHNUMBER AS JournalEntryId,
    'Journal Line Added' AS ActivityName,
    jlt.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    CAST(jh.ApprovalStatus AS VARCHAR) AS JournalStatus,
    NULL AS RejectionReason,
    jlt.DATA indignationId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    [YourD365DB].[dbo].LedgerJournalTrans jlt
JOIN 
    JournalHeaders jh ON jlt.JOURNALBATCHNUMBER = jh.JournalEntryId AND jlt.DATA indignationId = jh.DataAreaId
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON jlt.CREATEDBY = u.ID

UNION ALL

-- 3. Journal Submitted For Approval
SELECT 
    jh.JournalEntryId,
    'Journal Submitted For Approval' AS ActivityName,
    we.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'InReview' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
JOIN 
    WorkflowEvents we ON jh.RecId = we.CONTEXTRECID
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON we.USERID = u.ID
WHERE 
    we.TRACKINGSTATUS = 1 -- Submitted

UNION ALL

-- 4. Journal Review Started (Approximation)
SELECT 
    jh.JournalEntryId,
    'Journal Review Started' AS ActivityName,
    we.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'InReview' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
JOIN 
    WorkflowEvents we ON jh.RecId = we.CONTEXTRECID
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON we.USERID = u.ID
WHERE 
    we.TRACKINGTYPE = 5 -- Work item creation

UNION ALL

-- 5. Journal Approved
SELECT 
    jh.JournalEntryId,
    'Journal Approved' AS ActivityName,
    we.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Approved' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
JOIN 
    WorkflowEvents we ON jh.RecId = we.CONTEXTRECID
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON we.USERID = u.ID
WHERE 
    we.TRACKINGSTATUS = 5 -- Completion

UNION ALL

-- 6. Journal Rejected
SELECT 
    jh.JournalEntryId,
    'Journal Rejected' AS ActivityName,
    we.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Rejected' AS JournalStatus,
    jh.RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
JOIN 
    WorkflowEvents we ON jh.RecId = we.CONTEXTRECID
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON we.USERID = u.ID
WHERE 
    we.TRACKINGSTATUS = 3 -- Rejection

UNION ALL

-- 7. Journal Entry Corrected (Approximation)
SELECT 
    jh.JournalEntryId,
    'Journal Entry Corrected' AS ActivityName,
    jh.MODIFIEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Corrected' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON jh.MODIFIEDBY = u.ID
WHERE 
    jh.MODIFIEDDATETIME > jh.CREATEDDATETIME AND
    EXISTS (
        SELECT 1 FROM WorkflowEvents we 
        WHERE we.CONTEXTRECID = jh.RecId AND we.TRACKINGSTATUS = 3 -- Rejection
        AND jh.MODIFIEDDATETIME > we.CREATEDDATETIME
    )

UNION ALL

-- 8. Journal Resubmitted For Approval
SELECT 
    jh.JournalEntryId,
    'Journal Resubmitted For Approval' AS ActivityName,
    we.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'InReview' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
JOIN 
    WorkflowEvents we ON jh.RecId = we.CONTEXTRECID
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON we.USERID = u.ID
WHERE 
    we.TRACKINGSTATUS = 1 -- Submitted
    AND EXISTS (
        SELECT 1 FROM WorkflowEvents rej_we 
        WHERE rej_we.CONTEXTRECID = jh.RecId AND rej_we.TRACKINGSTATUS = 3 -- Rejection
        AND we.CREATEDDATETIME > rej_we.CREATEDDATETIME
    )

UNION ALL

-- 9. Journal Posting Attempted (Approximation)
SELECT 
    jh.JournalEntryId,
    'Journal Posting Attempted' AS ActivityName,
    DATEADD(second, -5, jh.POSTEDDATETIME) AS EventTime, -- Approximated as just before posting
    jh.POSTEDDATETIME AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Posting' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON jh.MODIFIEDBY = u.ID -- Often posted by the last modifier or a system account
WHERE 
    jh.POSTEDDATETIME IS NOT NULL

UNION ALL

-- 10. Journal Entry Posted
SELECT 
    jh.JournalEntryId,
    'Journal Entry Posted' AS ActivityName,
    jh.POSTEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Posted' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON jh.MODIFIEDBY = u.ID
WHERE 
    jh.POSTEDDATETIME IS NOT NULL

UNION ALL

-- 11. Journal Entry Reversal Initiated
SELECT 
    gje.JOURNALNUMBER AS JournalEntryId,
    'Journal Entry Reversal Initiated' AS ActivityName,
    gje_rev.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Reversing' AS JournalStatus,
    NULL AS RejectionReason,
    gje.LEDGER AS LegalEntity,
    NULL AS JournalTotalAmount
FROM
    [YourD365DB].[dbo].GeneralJournalEntry gje
JOIN
    [YourD365DB].[dbo].GeneralJournalEntry gje_rev ON gje.JOURNALNUMBER = gje_rev.OriginalJournalNumber
JOIN
    JournalHeaders jh ON gje.JOURNALNUMBER = jh.JournalEntryId
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON gje_rev.CREATEDBY = u.ID
WHERE
    gje.REVERSED = 'Yes'

UNION ALL

-- 12. Journal Entry Reversed
SELECT 
    gje.JOURNALNUMBER AS JournalEntryId,
    'Journal Entry Reversed' AS ActivityName,
    gje.REVERSEDDATE AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Reversed' AS JournalStatus,
    NULL AS RejectionReason,
    gje.LEDGER AS LegalEntity,
    NULL AS JournalTotalAmount
FROM
    [YourD365DB].[dbo].GeneralJournalEntry gje
JOIN
    JournalHeaders jh ON gje.JOURNALNUMBER = jh.JournalEntryId
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON gje.MODIFIEDBY = u.ID
WHERE
    gje.REVERSED = 'Yes' AND gje.REVERSEDDATE IS NOT NULL;

import adal
import requests
import pandas as pd
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration --- 
class=class="hl-string">"hl-comment"># Azure AD Application Details
TENANT_ID = class="hl-string">'[Your Tenant ID]'
CLIENT_ID = class="hl-string">'[Your Application Client ID]'
CLIENT_SECRET = class="hl-string">'[Your Client Secret]'

class=class="hl-string">"hl-comment"># Dynamics 365 F&O Environment Details
D365_URL = class="hl-string">'https://[your-d365-url].operations.dynamics.com'
RESOURCE = D365_URL
AUTHORITY_URL = fclass="hl-string">'https://login.microsoftonline.com/{TENANT_ID}'

class=class="hl-string">"hl-comment"># Extraction Parameters
START_DATE = class="hl-string">'2023-01-01T00:00:00Z'
END_DATE = class="hl-string">'2023-12-31T23:59:59Z'
LEGAL_ENTITIES = [class="hl-string">'[YourLegalEntity1]', class="hl-string">'[YourLegalEntity2]'] class=class="hl-string">"hl-comment"># e.g., [class="hl-string">'USMF']

class=class="hl-string">"hl-comment"># --- OData Helper Functions ---

def get_access_token():
    class="hl-string">"""Authenticates with Azure AD to get an access token."""
    context = adal.AuthenticationContext(AUTHORITY_URL)
    token = context.acquire_token_with_client_credentials(RESOURCE, CLIENT_ID, CLIENT_SECRET)
    if class="hl-string">'accessToken' not in token:
        raise Exception(fclass="hl-string">"Failed to get access token: {token.get('error_description')}")
    return token[class="hl-string">'accessToken']

def execute_odata_query(entity_url, headers):
    class="hl-string">"""Executes a paginated OData query and returns a list of records."""
    all_records = []
    next_link = entity_url
    page_num = 1
    while next_link:
        print(fclass="hl-string">"Fetching page {page_num} for entity: {entity_url.split('/')[-1].split('?')[0]}...")
        response = requests.get(next_link, headers=headers)
        if response.status_code != 200:
            raise Exception(fclass="hl-string">"Query failed with status {response.status_code}: {response.text}")
        data = response.json()
        all_records.extend(data[class="hl-string">'value'])
        next_link = data.get(class="hl-string">'@odata.nextLink')
        page_num += 1
    return all_records

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---

if __name__ == class="hl-string">"__main__":
    print(class="hl-string">"Starting Journal Entry data extraction...")
    
    class=class="hl-string">"hl-comment"># 1. Get Access Token
    access_token = get_access_token()
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json',
        class="hl-string">'OData-MaxVersion': class="hl-string">'4.0',
        class="hl-string">'OData-Version': class="hl-string">'4.0',
        class="hl-string">'Accept': class="hl-string">'application/json'
    }

    class=class="hl-string">"hl-comment"># 2. Build and Execute Queries
    legal_entity_filter = class="hl-string">" or ".join([fclass="hl-string">"dataAreaId eq '{le}'" for le in LEGAL_ENTITIES])
    date_filter = fclass="hl-string">"CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}"

    class=class="hl-string">"hl-comment"># Query LedgerJournalHeaders
    headers_fields = [
        class="hl-string">'JournalBatchNumber', class="hl-string">'dataAreaId', class="hl-string">'LedgerJournalType', class="hl-string">'WorkflowApprovalStatus',
        class="hl-string">'CreatedBy', class="hl-string">'CreatedDateTime', class="hl-string">'ModifiedBy', class="hl-string">'ModifiedDateTime', class="hl-string">'PostedDateTime',
        class="hl-string">'JournalTotalDebit', class="hl-string">'Reversed', class="hl-string">'ReversedDate', class="hl-string">'RejectionReasonComment' class=class="hl-string">"hl-comment"># Assuming a field for rejection reason
    ]
    headers_select = fclass="hl-string">"$select={','.join(headers_fields)}"
    headers_filter = fclass="hl-string">"$filter=({legal_entity_filter}) and ({date_filter})"
    headers_url = fclass="hl-string">"{D365_URL}/data/LedgerJournalHeaders?{headers_select}&{headers_filter}&cross-company=true"
    
    journal_headers_raw = execute_odata_query(headers_url, headers)
    if not journal_headers_raw:
        print(class="hl-string">"No journal headers found for the specified criteria. Exiting.")
        exit()

    df_headers = pd.DataFrame(journal_headers_raw)
    df_headers.to_csv(class="hl-string">'raw_journal_headers.csv', index=False)
    print(fclass="hl-string">"Fetched and saved {len(df_headers)} journal headers.")

    class=class="hl-string">"hl-comment"># Query LedgerJournalLines for class="hl-string">'Line Added' activity
    journal_batch_numbers = df_headers[class="hl-string">'JournalBatchNumber'].unique().tolist()
    journal_batch_filter = class="hl-string">" or ".join([fclass="hl-string">"JournalBatchNumber eq '{jbn}'" for jbn in journal_batch_numbers])
    
    lines_fields = [class="hl-string">'JournalBatchNumber', class="hl-string">'CreatedDateTime', class="hl-string">'CreatedBy']
    lines_select = fclass="hl-string">"$select={','.join(lines_fields)}"
    lines_filter = fclass="hl-string">"$filter=({legal_entity_filter}) and ({journal_batch_filter})"
    lines_url = fclass="hl-string">"{D365_URL}/data/LedgerJournalLines?{lines_select}&{lines_filter}&cross-company=true"

    journal_lines_raw = execute_odata_query(lines_url, headers)
    df_lines = pd.DataFrame(journal_lines_raw)
    df_lines.to_csv(class="hl-string">'raw_journal_lines.csv', index=False)
    print(fclass="hl-string">"Fetched and saved {len(df_lines)} journal lines.")

    class=class="hl-string">"hl-comment"># 3. Transform Data into Event Log Format
    print(class="hl-string">"Transforming raw data into event log...")
    event_log_frames = []

    class=class="hl-string">"hl-comment"># Rename columns for consistency
    df_headers.rename(columns={
        class="hl-string">'JournalBatchNumber': class="hl-string">'JournalEntryId',
        class="hl-string">'dataAreaId': class="hl-string">'LegalEntity',
        class="hl-string">'LedgerJournalType': class="hl-string">'JournalType',
        class="hl-string">'JournalTotalDebit': class="hl-string">'JournalTotalAmount',
        class="hl-string">'WorkflowApprovalStatus': class="hl-string">'JournalStatus',
        class="hl-string">'RejectionReasonComment': class="hl-string">'RejectionReason'
    }, inplace=True)
    df_lines.rename(columns={class="hl-string">'JournalBatchNumber': class="hl-string">'JournalEntryId'}, inplace=True)

    class=class="hl-string">"hl-comment"># Activity: Journal Entry Created
    df_created = df_headers[[class="hl-string">'JournalEntryId', class="hl-string">'CreatedDateTime', class="hl-string">'CreatedBy']].copy()
    df_created[class="hl-string">'ActivityName'] = class="hl-string">'Journal Entry Created'
    df_created.rename(columns={class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'CreatedBy': class="hl-string">'UserName'}, inplace=True)
    event_log_frames.append(df_created)

    class=class="hl-string">"hl-comment"># Activity: Journal Line Added
    df_line_added = df_lines[[class="hl-string">'JournalEntryId', class="hl-string">'CreatedDateTime', class="hl-string">'CreatedBy']].copy()
    df_line_added[class="hl-string">'ActivityName'] = class="hl-string">'Journal Line Added'
    df_line_added.rename(columns={class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'CreatedBy': class="hl-string">'UserName'}, inplace=True)
    event_log_frames.append(df_line_added)

    class=class="hl-string">"hl-comment"># Activities inferred from status changes and modifications
    class=class="hl-string">"hl-comment"># This part requires a more stateful analysis if audit trails are not directly available
    class=class="hl-string">"hl-comment"># We use ModifiedDateTime as a proxy for the event time of status changes.
    
    class=class="hl-string">"hl-comment"># NOTE: The logic below is a simplified inference. A real implementation might require parsing audit trail logs for precise timestamps.
    status_events_map = {
        class="hl-string">'Submitted': class="hl-string">'Journal Submitted For Approval',
        class="hl-string">'InProgress': class="hl-string">'Journal Review Started', class=class="hl-string">"hl-comment"># Approximation
        class="hl-string">'Approved': class="hl-string">'Journal Approved',
        class="hl-string">'Rejected': class="hl-string">'Journal Rejected',
        class="hl-string">'ChangeRequested': class="hl-string">'Journal Rejected', class=class="hl-string">"hl-comment"># Map another status to Rejected
        class="hl-string">'Canceled': class="hl-string">'Journal Rejected' class=class="hl-string">"hl-comment"># Map another status to Rejected
    }

    status_change_df = df_headers[df_headers[class="hl-string">'JournalStatus'].isin(status_events_map.keys())].copy()
    status_change_df[class="hl-string">'ActivityName'] = status_change_df[class="hl-string">'JournalStatus'].map(status_events_map)
    status_change_df.rename(columns={class="hl-string">'ModifiedDateTime': class="hl-string">'EventTime', class="hl-string">'ModifiedBy': class="hl-string">'UserName'}, inplace=True)
    event_log_frames.append(status_change_df[[class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName', class="hl-string">'RejectionReason']])

    class=class="hl-string">"hl-comment"># Activity: Journal Resubmitted For Approval & Journal Entry Corrected
    class=class="hl-string">"hl-comment"># Infer this by finding a class="hl-string">'Submitted' status that occurred after a class="hl-string">'Rejected' status for the same journal.
    class=class="hl-string">"hl-comment"># For simplicity, we assume any modification after rejection is a correction.
    df_rejected = status_change_df[status_change_df[class="hl-string">'ActivityName'] == class="hl-string">'Journal Rejected']
    for index, row in df_rejected.iterrows():
        class=class="hl-string">"hl-comment"># Check for modifications on the header after rejection
        header_row = df_headers[df_headers[class="hl-string">'JournalEntryId'] == row[class="hl-string">'JournalEntryId']].iloc[0]
        if pd.to_datetime(header_row[class="hl-string">'ModifiedDateTime']) > pd.to_datetime(row[class="hl-string">'EventTime']):
            class=class="hl-string">"hl-comment"># Add Corrected Event
            corrected_event = row.copy()
            corrected_event[class="hl-string">'ActivityName'] = class="hl-string">'Journal Entry Corrected'
            corrected_event[class="hl-string">'EventTime'] = header_row[class="hl-string">'ModifiedDateTime']
            corrected_event[class="hl-string">'UserName'] = header_row[class="hl-string">'ModifiedBy']
            event_log_frames.append(pd.DataFrame([corrected_event]))
            
            class=class="hl-string">"hl-comment"># If status went back to submitted, it's a resubmission
            if header_row[class="hl-string">'JournalStatus'] == class="hl-string">'Submitted':
                resubmitted_event = row.copy()
                resubmitted_event[class="hl-string">'ActivityName'] = class="hl-string">'Journal Resubmitted For Approval'
                resubmitted_event[class="hl-string">'EventTime'] = header_row[class="hl-string">'ModifiedDateTime']
                resubmitted_event[class="hl-string">'UserName'] = header_row[class="hl-string">'ModifiedBy']
                event_log_frames.append(pd.DataFrame([resubmitted_event]))

    class=class="hl-string">"hl-comment"># Activity: Journal Posting Attempted (Approximation)
    class=class="hl-string">"hl-comment"># This is difficult to capture. We'll use the approval time as a proxy.
    df_approved_for_posting = status_change_df[status_change_df[class="hl-string">'ActivityName'] == class="hl-string">'Journal Approved'].copy()
    df_approved_for_posting[class="hl-string">'ActivityName'] = class="hl-string">'Journal Posting Attempted'
    event_log_frames.append(df_approved_for_posting[[class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']])

    class=class="hl-string">"hl-comment"># Activity: Journal Entry Posted
    df_posted = df_headers[df_headers[class="hl-string">'PostedDateTime'].notna()].copy()
    if not df_posted.empty:
        df_posted[class="hl-string">'ActivityName'] = class="hl-string">'Journal Entry Posted'
        class=class="hl-string">"hl-comment"># Use PostedDateTime if available, otherwise fallback to ModifiedDateTime
        df_posted[class="hl-string">'EventTime'] = df_posted[class="hl-string">'PostedDateTime'].fillna(df_posted[class="hl-string">'ModifiedDateTime'])
        df_posted.rename(columns={class="hl-string">'ModifiedBy': class="hl-string">'UserName'}, inplace=True)
        event_log_frames.append(df_posted[[class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']])

    class=class="hl-string">"hl-comment"># Activity: Journal Entry Reversal Initiated & Reversed
    df_reversed = df_headers[df_headers[class="hl-string">'Reversed'] == True].copy()
    if not df_reversed.empty:
        class=class="hl-string">"hl-comment"># Reversal Initiated (approximated by modification date when Reversed flag is set)
        df_reversal_initiated = df_reversed.copy()
        df_reversal_initiated[class="hl-string">'ActivityName'] = class="hl-string">'Journal Entry Reversal Initiated'
        df_reversal_initiated.rename(columns={class="hl-string">'ModifiedDateTime': class="hl-string">'EventTime', class="hl-string">'ModifiedBy': class="hl-string">'UserName'}, inplace=True)
        event_log_frames.append(df_reversal_initiated[[class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']])
        class=class="hl-string">"hl-comment"># Reversed (using the specific ReversedDate)
        df_reversed_final = df_reversed[df_reversed[class="hl-string">'ReversedDate'].notna()].copy()
        df_reversed_final[class="hl-string">'ActivityName'] = class="hl-string">'Journal Entry Reversed'
        df_reversed_final.rename(columns={class="hl-string">'ReversedDate': class="hl-string">'EventTime', class="hl-string">'ModifiedBy': class="hl-string">'UserName'}, inplace=True)
        event_log_frames.append(df_reversed_final[[class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']])

    class=class="hl-string">"hl-comment"># 4. Assemble Final Event Log
    print(class="hl-string">"Assembling the final event log...")
    final_event_log = pd.concat(event_log_frames, ignore_index=True)

    class=class="hl-string">"hl-comment"># Merge attributes from the header file
    attributes_to_merge = df_headers[[class="hl-string">'JournalEntryId', class="hl-string">'LegalEntity', class="hl-string">'JournalType', class="hl-string">'JournalStatus', class="hl-string">'JournalTotalAmount', class="hl-string">'RejectionReason']].drop_duplicates()
    final_event_log = pd.merge(final_event_log, attributes_to_merge, on=class="hl-string">'JournalEntryId', how=class="hl-string">'left')

    class=class="hl-string">"hl-comment"># Clean up and order columns
    final_event_log[class="hl-string">'EventTime'] = pd.to_datetime(final_event_log[class="hl-string">'EventTime'])
    final_event_log.sort_values(by=[class="hl-string">'JournalEntryId', class="hl-string">'EventTime'], inplace=True)
    final_event_log.drop_duplicates(subset=[class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime'], inplace=True)
    
    class=class="hl-string">"hl-comment"># Define column order
    final_columns = [
        class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName', class="hl-string">'LegalEntity',
        class="hl-string">'JournalType', class="hl-string">'JournalStatus', class="hl-string">'JournalTotalAmount', class="hl-string">'RejectionReason'
    ]
    class=class="hl-string">"hl-comment"># Add missing columns with None
    for col in final_columns:
        if col not in final_event_log.columns:
            final_event_log[col] = None
    final_event_log = final_event_log[final_columns]
    
    class=class="hl-string">"hl-comment"># Add EventEndTime (optional, can be populated if duration is known)
    final_event_log[class="hl-string">'EventEndTime'] = None
    
    class=class="hl-string">"hl-comment"># 5. Export to CSV
    final_event_log.to_csv(class="hl-string">'journal_entry_event_log.csv', index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%SZ')
    print(fclass="hl-string">"Extraction complete. Event log with {len(final_event_log)} events saved to journal_entry_event_log.csv")

WITH
JournalHeader AS (
    SELECT
        CAST([JournalEntryId] AS varchar(255)) AS JournalEntryId,
        [CreatedDateTime] AS CreatedDateTime,
        [ModifiedDateTime] AS ModifiedDateTime,
        [CreatedBy] AS CreatedBy,
        [ModifiedBy] AS ModifiedBy,
        [JournalType] AS JournalType,
        [JournalStatus] AS JournalStatus,
        [RejectionReason] AS RejectionReason,
        [LegalEntity] AS LegalEntity,
        [JournalTotalAmount] AS JournalTotalAmount
    FROM [Your journal header entity]
    WHERE [CreatedDateTime] >= '[Start date parameter]'
      AND [CreatedDateTime] < '[End date parameter]'
      AND ([LegalEntity] IN ([Your company filter]) OR [Your company filter] IS NULL)
),
JournalLines AS (
    SELECT
        CAST([JournalEntryId] AS varchar(255)) AS JournalEntryId,
        [CreatedDateTime] AS EventTime,
        [CreatedBy] AS UserName,
        [JournalType] AS JournalType,
        [JournalStatus] AS JournalStatus,
        [RejectionReason] AS RejectionReason,
        [LegalEntity] AS LegalEntity,
        [JournalTotalAmount] AS JournalTotalAmount
    FROM [Your journal line entity]
    WHERE [CreatedDateTime] >= '[Start date parameter]'
      AND [CreatedDateTime] < '[End date parameter]'
),
StatusHistory AS (
    SELECT
        CAST([JournalEntryId] AS varchar(255)) AS JournalEntryId,
        [StatusChangeDateTime] AS EventTime,
        [StatusChangeEndDateTime] AS EventEndTime,
        [ChangedBy] AS UserName,
        [PreviousStatus] AS PreviousStatus,
        [NewStatus] AS NewStatus,
        [JournalType] AS JournalType,
        [RejectionReason] AS RejectionReason,
        [LegalEntity] AS LegalEntity,
        [JournalTotalAmount] AS JournalTotalAmount
    FROM [Your journal status history entity]
    WHERE [StatusChangeDateTime] >= '[Start date parameter]'
      AND [StatusChangeDateTime] < '[End date parameter]'
),
ReviewHistory AS (
    SELECT
        CAST([JournalEntryId] AS varchar(255)) AS JournalEntryId,
        [ReviewStartDateTime] AS EventTime,
        [ReviewEndDateTime] AS EventEndTime,
        [Reviewer] AS UserName,
        [JournalType] AS JournalType,
        [JournalStatus] AS JournalStatus,
        [RejectionReason] AS RejectionReason,
        [LegalEntity] AS LegalEntity,
        [JournalTotalAmount] AS JournalTotalAmount
    FROM [Your journal review history entity]
    WHERE [ReviewStartDateTime] >= '[Start date parameter]'
      AND [ReviewStartDateTime] < '[End date parameter]'
),
PostingHistory AS (
    SELECT
        CAST([JournalEntryId] AS varchar(255)) AS JournalEntryId,
        [PostingAttemptDateTime] AS PostingAttemptDateTime,
        [PostingDateTime] AS PostingDateTime,
        [PostingEndDateTime] AS PostingEndTime,
        [PostedBy] AS PostedBy,
        [JournalType] AS JournalType,
        [JournalStatus] AS JournalStatus,
        [LegalEntity] AS LegalEntity,
        [JournalTotalAmount] AS JournalTotalAmount
    FROM [Your journal posting history entity]
    WHERE [PostingAttemptDateTime] >= '[Start date parameter]'
      AND [PostingAttemptDateTime] < '[End date parameter]'
),
ReversalHistory AS (
    SELECT
        CAST([JournalEntryId] AS varchar(255)) AS JournalEntryId,
        [ReversalInitiatedDateTime] AS ReversalInitiatedDateTime,
        [ReversalCompletedDateTime] AS ReversalCompletedDateTime,
        [ReversalCompletedBy] AS ReversalCompletedBy,
        [JournalType] AS JournalType,
        [JournalStatus] AS JournalStatus,
        [LegalEntity] AS LegalEntity,
        [JournalTotalAmount] AS JournalTotalAmount
    FROM [Your journal reversal history entity]
    WHERE [ReversalInitiatedDateTime] >= '[Start date parameter]'
      AND [ReversalInitiatedDateTime] < '[End date parameter]'
),
CorrectionHistory AS (
    SELECT
        CAST([JournalEntryId] AS varchar(255)) AS JournalEntryId,
        [ModificationDateTime] AS EventTime,
        [ModificationEndDateTime] AS EventEndTime,
        [ModifiedBy] AS UserName,
        [JournalType] AS JournalType,
        [JournalStatus] AS JournalStatus,
        [RejectionReason] AS RejectionReason,
        [LegalEntity] AS LegalEntity,
        [JournalTotalAmount] AS JournalTotalAmount
    FROM [Your journal audit or modification history entity]
    WHERE [ModificationDateTime] >= '[Start date parameter]'
      AND [ModificationDateTime] < '[End date parameter]'
),
EventLog AS (
    SELECT JournalEntryId, 'Journal Entry Created' AS ActivityName, CreatedDateTime AS EventTime, CreatedDateTime AS EventEndTime, CreatedBy AS UserName, JournalType, JournalStatus, RejectionReason, LegalEntity, JournalTotalAmount FROM JournalHeader WHERE JournalEntryId IS NOT NULL AND CreatedDateTime IS NOT NULL
    UNION ALL
    SELECT JournalEntryId, 'Journal Line Added' AS ActivityName, EventTime, EventTime AS EventEndTime, UserName, JournalType, JournalStatus, RejectionReason, LegalEntity, JournalTotalAmount FROM JournalLines WHERE JournalEntryId IS NOT NULL AND EventTime IS NOT NULL
    UNION ALL
    SELECT JournalEntryId, 'Journal Submitted For Approval' AS ActivityName, EventTime, EventEndTime, UserName, JournalType, NewStatus AS JournalStatus, RejectionReason, LegalEntity, JournalTotalAmount FROM StatusHistory WHERE JournalEntryId IS NOT NULL AND EventTime IS NOT NULL AND (NewStatus IN ('In Review', 'Submitted') OR (PreviousStatus = 'Draft' AND NewStatus IN ('In Review', 'Submitted')))
    UNION ALL
    SELECT JournalEntryId, 'Journal Review Started' AS ActivityName, EventTime, EventEndTime, UserName, JournalType, JournalStatus, RejectionReason, LegalEntity, JournalTotalAmount FROM ReviewHistory WHERE JournalEntryId IS NOT NULL AND EventTime IS NOT NULL
    UNION ALL
    SELECT JournalEntryId, 'Journal Approved' AS ActivityName, EventTime, EventEndTime, UserName, JournalType, NewStatus AS JournalStatus, RejectionReason, LegalEntity, JournalTotalAmount FROM StatusHistory WHERE JournalEntryId IS NOT NULL AND EventTime IS NOT NULL AND NewStatus = 'Approved'
    UNION ALL
    SELECT JournalEntryId, 'Journal Rejected' AS ActivityName, EventTime, EventEndTime, UserName, JournalType, NewStatus AS JournalStatus, RejectionReason, LegalEntity, JournalTotalAmount FROM StatusHistory WHERE JournalEntryId IS NOT NULL AND EventTime IS NOT NULL AND NewStatus IN ('Rejected', 'Needs Correction')
    UNION ALL
    SELECT c.JournalEntryId, 'Journal Entry Corrected' AS ActivityName, c.EventTime, c.EventEndTime, c.UserName, c.JournalType, c.JournalStatus, c.RejectionReason, c.LegalEntity, c.JournalTotalAmount FROM CorrectionHistory c WHERE c.JournalEntryId IS NOT NULL AND c.EventTime IS NOT NULL AND EXISTS (SELECT 1 FROM StatusHistory s WHERE s.JournalEntryId = c.JournalEntryId AND s.NewStatus IN ('Rejected', 'Needs Correction') AND s.EventTime < c.EventTime)
    UNION ALL
    SELECT JournalEntryId, 'Journal Resubmitted For Approval' AS ActivityName, EventTime, EventEndTime, UserName, JournalType, NewStatus AS JournalStatus, RejectionReason, LegalEntity, JournalTotalAmount FROM StatusHistory WHERE JournalEntryId IS NOT NULL AND EventTime IS NOT NULL AND PreviousStatus IN ('Rejected', 'Needs Correction', 'Draft') AND NewStatus IN ('In Review', 'Submitted')
    UNION ALL
    SELECT JournalEntryId, 'Journal Posting Attempted' AS ActivityName, PostingAttemptDateTime AS EventTime, PostingAttemptDateTime AS EventEndTime, PostedBy AS UserName, JournalType, JournalStatus, CAST(NULL AS varchar(255)) AS RejectionReason, LegalEntity, JournalTotalAmount FROM PostingHistory WHERE JournalEntryId IS NOT NULL AND PostingAttemptDateTime IS NOT NULL
    UNION ALL
    SELECT JournalEntryId, 'Journal Entry Posted' AS ActivityName, PostingDateTime AS EventTime, PostingEndTime AS EventEndTime, PostedBy AS UserName, JournalType, 'Posted' AS JournalStatus, CAST(NULL AS varchar(255)) AS RejectionReason, LegalEntity, JournalTotalAmount FROM PostingHistory WHERE JournalEntryId IS NOT NULL AND PostingDateTime IS NOT NULL
    UNION ALL
    SELECT JournalEntryId, 'Journal Entry Reversal Initiated' AS ActivityName, ReversalInitiatedDateTime AS EventTime, ReversalInitiatedDateTime AS EventEndTime, CAST(NULL AS varchar(255)) AS UserName, JournalType, JournalStatus, CAST(NULL AS varchar(255)) AS RejectionReason, LegalEntity, JournalTotalAmount FROM ReversalHistory WHERE JournalEntryId IS NOT NULL AND ReversalInitiatedDateTime IS NOT NULL
    UNION ALL
    SELECT JournalEntryId, 'Journal Entry Reversed' AS ActivityName, ReversalCompletedDateTime AS EventTime, ReversalCompletedDateTime AS EventEndTime, ReversalCompletedBy AS UserName, JournalType, JournalStatus, CAST(NULL AS varchar(255)) AS RejectionReason, LegalEntity, JournalTotalAmount FROM ReversalHistory WHERE JournalEntryId IS NOT NULL AND ReversalCompletedDateTime IS NOT NULL
)
SELECT
    JournalEntryId,
    ActivityName,
    EventTime,
    EventEndTime,
    UserName,
    JournalType,
    JournalStatus,
    RejectionReason,
    LegalEntity,
    JournalTotalAmount
FROM EventLog
WHERE JournalEntryId IS NOT NULL
  AND EventTime IS NOT NULL
ORDER BY JournalEntryId, EventTime, ActivityName;