Datatemplate voor de verwerking van crediteurenfacturen

-- This is a conceptual SQL query representing the logic to be applied in an external tool (e.g., Power BI, Python, SQL DB)
-- to transform the exported D365 CSV files into a single event log.
-- Replace '[Your Company]' with the legal entity ID you are analyzing.

-- Step 1: Define CTEs (Common Table Expressions) to represent your loaded CSV files.

WITH VendInvoiceInfoTable AS (
    -- Represents the 'Vendor invoice headers' CSV file
    SELECT * FROM [dbo].[VendInvoiceInfoTable_Export]
),
VendInvoiceJour AS (
    -- Represents the 'Vendor posted invoices' CSV file
    SELECT * FROM [dbo].[VendInvoiceJour_Export]
),
VendTrans AS (
    -- Represents the 'Vendor transactions' CSV file
    SELECT * FROM [dbo].[VendTrans_Export]
),
VendSettlement AS (
    -- Represents the 'Vendor settlement transactions' CSV file
    SELECT * FROM [dbo].[VendSettlement_Export]
),
WorkflowTrackingStatusTable AS (
    -- Represents the 'Workflow tracking status' CSV file
    SELECT * FROM [dbo].[WorkflowTrackingStatusTable_Export]
),
LedgerJournalTrans AS (
    -- Represents the 'Payment journal lines' CSV file
    SELECT * FROM [dbo].[LedgerJournalTrans_Export]
),
BankAccountTrans AS (
    -- Represents the 'Bank transactions' CSV file
    SELECT * FROM [dbo].[BankAccountTrans_Export]
),
-- Step 2: Extract each activity as a separate SELECT statement and UNION them together.

FinalEventLog AS (
    -- 1. Invoice Registered
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Registered' AS "ActivityName",
        i.CREATEDDATETIME AS "EventTime",
        i.CREATEDBY AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceInfoTable i
    WHERE i.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 2. Invoice Data Validated (approximated by workflow start)
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Data Validated' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Submission' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 3 & 4. Purchase Order & Goods Receipt Matched (approximated by posting for PO invoices)
    SELECT
        vj.INVOICENUM AS "Invoice",
        'Purchase Order Matched' AS "ActivityName",
        vj.INVOICEDATE AS "EventTime",
        vj.MODIFIEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceJour vj
    WHERE vj.PURCHID IS NOT NULL AND vj.PURCHID <> '' AND vj.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 5. Discrepancy Identified (approximated by a rejection event)
    SELECT
        i.INVOICENUM AS "Invoice",
        'Discrepancy Identified' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Rejection' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 6. Invoice Submitted For Approval
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Submitted For Approval' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Submission' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 7. Invoice Approved
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Approved' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGSTATUS = 'Completed' AND w.TRACKINGTYPE = 'Approval' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 8. Invoice Rejected
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Rejected' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Rejection' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 9. Discrepancy Resolved (approximated by resubmission after rejection)
    SELECT
        i.INVOICENUM AS "Invoice",
        'Discrepancy Resolved' AS "ActivityName",
        w_submit.CREATEDDATETIME AS "EventTime",
        w_submit.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w_submit
    JOIN VendInvoiceInfoTable i ON w_submit.CONTEXTRECID = i.RECID
    WHERE w_submit.TRACKINGTYPE = 'Submission' AND w_submit.DATACOMPANY = '[Your Company]'
      AND EXISTS (
          SELECT 1 FROM WorkflowTrackingStatusTable w_reject
          WHERE w_reject.CONTEXTRECID = w_submit.CONTEXTRECID
            AND w_reject.TRACKINGTYPE = 'Rejection'
            AND w_reject.CREATEDDATETIME < w_submit.CREATEDDATETIME
      )

    UNION ALL

    -- 10. Invoice Posted
    SELECT
        vj.INVOICENUM AS "Invoice",
        'Invoice Posted' AS "ActivityName",
        vj.CREATEDDATETIME AS "EventTime",
        vj.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceJour vj
    WHERE vj.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 11. Payment Scheduled
    SELECT
        ljt.INVOICENUM AS "Invoice",
        'Payment Scheduled' AS "ActivityName",
        ljt.CREATEDDATETIME AS "EventTime",
        ljt.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM LedgerJournalTrans ljt
    JOIN VendInvoiceJour vj ON ljt.INVOICENUM = vj.INVOICENUM AND ljt.DATACOMPANY = vj.DATACOMPANY
    WHERE ljt.POSTED <> 'Yes' AND ljt.INVOICENUM <> '' AND ljt.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 12. Payment Executed
    SELECT 
        vt.INVOICE AS "Invoice",
        'Payment Executed' AS "ActivityName",
        vt.TRANSDATE AS "EventTime",
        vt.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendTrans vt
    JOIN VendSettlement vs ON vt.RECID = vs.OFFSETRECID
    JOIN VendTrans vt_inv ON vs.TRANSRECID = vt_inv.RECID
    JOIN VendInvoiceJour vj ON vt_inv.VOUCHER = vj.LEDGERVOUCHER AND vt_inv.DATACOMPANY = vj.DATACOMPANY
    WHERE vt.TRANSTYPE = 'Payment' AND vt.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 13. Payment Cleared
    SELECT 
        vt.INVOICE AS "Invoice",
        'Payment Cleared' AS "ActivityName",
        bt.CLEAREDDATE AS "EventTime",
        bt.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM BankAccountTrans bt
    JOIN VendTrans vt ON bt.VOUCHER = vt.VOUCHER AND bt.DATACOMPANY = vt.DATACOMPANY
    JOIN VendInvoiceJour vj ON vt.INVOICE = vj.INVOICENUM AND vt.DATACOMPANY = vj.DATACOMPANY
    WHERE bt.CLEAREDDATE IS NOT NULL AND vt.TRANSTYPE = 'Payment' AND bt.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 14. Invoice Cancelled
    SELECT
        vj.INVOICENUM AS "Invoice",
        'Invoice Cancelled' AS "ActivityName",
        vj.MODIFIEDDATETIME AS "EventTime",
        vj.MODIFIEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceJour vj
    WHERE vj.APPROVALSTATUS = 'Canceled' AND vj.DATACOMPANY = '[Your Company]'
)

SELECT * FROM FinalEventLog;

-- ==========================================================================================
-- T-SQL EXTRACTOR FOR DYNAMICS 365 F&O - ACCOUNTS PAYABLE INVOICE PROCESSING
-- ==========================================================================================

-- Parameters: Configure these values for your extraction
DECLARE @StartDate DATE = '2023-01-01'; -- The start date of the extraction window
DECLARE @EndDate DATE = '2023-12-31';   -- The end date of the extraction window
DECLARE @CompanyId NVARCHAR(4) = '[YourCompanyId]'; -- The legal entity/company ID (e.g., 'USMF')

-- ==========================================================================================
-- Main Query: Do not edit below this line
-- ==========================================================================================

WITH BaseInvoices AS (
    -- Selects the core set of invoices to be analyzed based on the date range and company
    SELECT
        viit.NUM AS Invoice,
        viit.PURCHID AS PurchaseOrderNumber,
        viit.INVOICEACCOUNT,
        viit.INVOICEDATE,
        viit.INVOICEAMOUNTMST AS InvoiceAmount,
        dp.NAME AS VendorName,
        pt.DESCRIPTION AS PaymentTerms,
        viit.DUEDATE AS InvoiceDueDate,
        viit.CREATEDBY AS CreatedUserId,
        viit.CREATEDDATETIME,
        viit.RECID AS VendInvoiceInfoTableRecId,
        viit.DATAREAID
    FROM VendInvoiceInfoTable viit
    LEFT JOIN VendTable vt ON viit.INVOICEACCOUNT = vt.ACCOUNTNUM AND viit.DATAREAID = vt.DATAREAID
    LEFT JOIN DirPartyTable dp ON vt.PARTY = dp.RECID
    LEFT JOIN PaymTerm pt ON vt.PAYMTERMID = pt.PAYMTERMID AND vt.DATAREAID = pt.DATAREAID
    WHERE viit.CREATEDDATETIME >= @StartDate 
      AND viit.CREATEDDATETIME < DATEADD(day, 1, @EndDate) 
      AND viit.DATAREAID = @CompanyId
)

-- 1. Invoice Registered
SELECT
    bi.Invoice,
    'Invoice Registered' AS ActivityName,
    bi.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
LEFT JOIN USERINFO u ON bi.CreatedUserId = u.ID

UNION ALL

-- 2. Invoice Data Validated (Approximated by first review step in workflow)
SELECT
    bi.Invoice,
    'Invoice Data Validated' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable')
  AND wst.TRACKINGTYPE = 2 -- Task/Work Item
  AND wst.WORKFLOWSTEPID LIKE '%Review%'
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 3 & 4. Purchase Order / Goods Receipt Matched (Approximated by last modification when status is 'Passed')
SELECT
    bi.Invoice,
    'Purchase Order Matched' AS ActivityName, -- Assuming PO and GR matching is one event
    viit.MODIFIEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendInvoiceInfoTable viit ON bi.VendInvoiceInfoTableRecId = viit.RECID
LEFT JOIN USERINFO u ON viit.MODIFIEDBY = u.ID
WHERE viit.LASTMATCHINGSTAT = 2 -- Passed

UNION ALL

-- 5. Discrepancy Identified (Approximated by last modification when status is 'Failed')
SELECT
    bi.Invoice,
    'Discrepancy Identified' AS ActivityName,
    viit.MODIFIEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendInvoiceInfoTable viit ON bi.VendInvoiceInfoTableRecId = viit.RECID
LEFT JOIN USERINFO u ON viit.MODIFIEDBY = u.ID
WHERE viit.LASTMATCHINGSTAT = 3 -- Failed

UNION ALL

-- 6. Invoice Submitted For Approval
SELECT
    bi.Invoice,
    'Invoice Submitted For Approval' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable') 
  AND wst.TRACKINGTYPE = 1 -- Submission
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 7. Invoice Approved (Final approval of the workflow)
SELECT
    bi.Invoice,
    'Invoice Approved' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable') 
  AND wst.TRACKINGTYPE = 5 -- Completion
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 8. Invoice Rejected
SELECT
    bi.Invoice,
    'Invoice Rejected' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable') 
  AND wst.TRACKINGTYPE = 7 -- Rejection
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 9. Discrepancy Resolved (Approximated by workflow 'Request Change' step)
SELECT
    bi.Invoice,
    'Discrepancy Resolved' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable') 
  AND wst.TRACKINGTYPE = 3 -- RequestChange
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 10. Invoice Posted
SELECT
    bi.Invoice,
    'Invoice Posted' AS ActivityName,
    vij.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendInvoiceJour vij ON bi.Invoice = vij.INVOICEID AND bi.INVOICEDATE = vij.INVOICEDATE AND bi.INVOICEACCOUNT = vij.INVOICEACCOUNT AND bi.DATAREAID = vij.DATAREAID
LEFT JOIN USERINFO u ON vij.CREATEDBY = u.ID

UNION ALL

-- 11. Payment Scheduled (Creation of a payment journal line for the invoice)
SELECT DISTINCT
    bi.Invoice,
    'Payment Scheduled' AS ActivityName,
    ljt.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendTrans vt ON bi.Invoice = vt.INVOICENUM AND bi.DATAREAID = vt.DATAREAID
JOIN LedgerJournalTrans ljt ON vt.VOUCHER = ljt.VOUCHER AND vt.TRANSDATE = ljt.TRANSDATE AND vt.DATAREAID = ljt.DATAREAID
JOIN LedgerJournalTable ljh ON ljt.JOURNALNUM = ljh.JOURNALNUM AND ljt.DATAREAID = ljh.DATAREAID
LEFT JOIN USERINFO u ON ljt.CREATEDBY = u.ID
WHERE ljh.JOURNALTYPE = 2 -- Vendor Payment
  AND ljh.POSTED = 0

UNION ALL

-- 12. Payment Executed (Posting of the payment journal)
SELECT
    bi.Invoice,
    'Payment Executed' AS ActivityName,
    vs.TRANSDATE AS EventTime, -- Settlement Date
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendTrans vt_inv ON bi.Invoice = vt_inv.INVOICENUM AND bi.DATAREAID = vt_inv.DATAREAID
JOIN VendSettlement vs ON vt_inv.RECID = vs.TRANSRECID
JOIN VendTrans vt_pay ON vs.OFFSETRECID = vt_pay.RECID
LEFT JOIN USERINFO u ON vs.CREATEDBY = u.ID

UNION ALL

-- 13. Payment Cleared (Bank reconciliation date)
SELECT 
    bi.Invoice,
    'Payment Cleared' AS ActivityName,
    bt.CLEAREDDATE AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendTrans vt_inv ON bi.Invoice = vt_inv.INVOICENUM AND bi.DATAREAID = vt_inv.DATAREAID
JOIN VendSettlement vs ON vt_inv.RECID = vs.TRANSRECID
JOIN VendTrans vt_pay ON vs.OFFSETRECID = vt_pay.RECID
JOIN BankAccountTrans bat ON vt_pay.VOUCHER = bat.VOUCHER AND vt_pay.TRANSDATE = bat.TRANSDATE AND vt_pay.DATAREAID = bat.DATAREAID
JOIN BankTrans bt ON bat.RECID = bt.BANKACCOUNTTRANSRECID
LEFT JOIN USERINFO u ON bt.CREATEDBY = u.ID
WHERE bt.CLEAREDDATE > '1900-01-01'

UNION ALL

-- 14. Invoice Cancelled
SELECT
    bi.Invoice,
    'Invoice Cancelled' AS ActivityName,
    vij.MODIFIEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendInvoiceJour vij ON bi.Invoice = vij.INVOICEID AND bi.INVOICEDATE = vij.INVOICEDATE AND bi.INVOICEACCOUNT = vij.INVOICEACCOUNT AND bi.DATAREAID = vij.DATAREAID
LEFT JOIN USERINFO u ON vij.MODIFIEDBY = u.ID
WHERE vij.INVOICECANCELLED_RU = 1 -- Note: This field can be localization-specific. Adjust if needed.

ORDER BY Invoice, EventTime;

import requests
import msal
import pandas as pd
import time
import urllib.parse

class=class="hl-string">"hl-comment"># --- CONFIGURATION --- 
class=class="hl-string">"hl-comment"># Replace with your Dynamics 365 Finance & Operations details
D365_URL = class="hl-string">"https://[your_d365_instance].operations.dynamics.com"
TENANT_ID = class="hl-string">"[your_azure_tenant_id]"
CLIENT_ID = class="hl-string">"[your_azure_app_client_id]"
CLIENT_SECRET = class="hl-string">"[your_azure_app_client_secret]"

class=class="hl-string">"hl-comment"># Replace with your specific data filters
DATA_AREA_ID = class="hl-string">"[your_company_id]" class=class="hl-string">"hl-comment"># e.g., class="hl-string">'USMF'
START_DATE = class="hl-string">"2023-01-01T00:00:00Z"
END_DATE = class="hl-string">"2023-12-31T23:59:59Z"

class=class="hl-string">"hl-comment"># --- AUTHENTICATION --- 
def get_access_token():
    class="hl-string">"""Authenticates with Azure AD and returns an access token."""
    authority = fclass="hl-string">"https://login.microsoftonline.com/{TENANT_ID}"
    scope = [fclass="hl-string">"{D365_URL}/.default"]
    app = msal.ConfidentialClientApplication(
        CLIENT_ID, authority=authority, client_credential=CLIENT_SECRET
    )
    result = app.acquire_token_for_client(scopes=scope)
    if class="hl-string">"access_token" in result:
        return result[class="hl-string">'access_token']
    else:
        raise Exception(fclass="hl-string">"Error acquiring token: {result.get('error_description')}")

class=class="hl-string">"hl-comment"># --- DATA FETCHING --- 
def fetch_odata_data(access_token, entity_url):
    class="hl-string">"""Fetches all pages of data from a given OData entity URL."""
    all_records = []
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json',
        class="hl-string">'Prefer': class="hl-string">'odata.maxpagesize=1000' class=class="hl-string">"hl-comment"># Request pagination
    }
    while entity_url:
        try:
            response = requests.get(entity_url, headers=headers, timeout=60)
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            data = response.json()
            all_records.extend(data[class="hl-string">'value'])
            entity_url = data.get(class="hl-string">'@odata.nextLink') class=class="hl-string">"hl-comment"># Get URL for the next page
            print(fclass="hl-string">"Fetched {len(all_records)} records so far...")
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"An error occurred: {e}")
            time.sleep(5) class=class="hl-string">"hl-comment"># Simple retry logic
            class=class="hl-string">"hl-comment"># More robust error handling might be needed for production
            continue
    return all_records

class=class="hl-string">"hl-comment"># --- MAIN SCRIPT --- 
def main():
    print(class="hl-string">"Acquiring access token...")
    token = get_access_token()
    all_activity_dfs = []

    base_api_url = fclass="hl-string">"{D365_URL}/data"

    class=class="hl-string">"hl-comment"># Common attributes to select
    common_attrs = class="hl-string">'VendorAccount,InvoiceNumber,InvoiceAmount,InvoiceDate,DueDate,PaymentTermsName,PurchaseOrderNumber'

    class=class="hl-string">"hl-comment"># 1. Invoice Registered
    print(class="hl-string">"\nExtracting 'Invoice Registered'...")
    entity = class="hl-string">'VendorInvoiceHeaders'
    filters = fclass="hl-string">"dataAreaId eq '{DATA_AREA_ID}' and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}"
    url = fclass="hl-string">"{base_api_url}/{entity}?$select={common_attrs},CreatedDateTime,CreatedBy&$filter={filters}"
    records = fetch_odata_data(token, url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Registered'
        df.rename(columns={class="hl-string">'InvoiceNumber': class="hl-string">'Invoice', class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'CreatedBy': class="hl-string">'UserName', class="hl-string">'VendorAccount': class="hl-string">'VendorName'}, inplace=True)
        df[class="hl-string">'PaymentTerms'] = df[class="hl-string">'PaymentTermsName']
        df[class="hl-string">'InvoiceDueDate'] = df[class="hl-string">'DueDate']
        all_activity_dfs.append(df)

    class=class="hl-string">"hl-comment"># Get a list of invoice numbers for subsequent queries
    if class="hl-string">'Invoice' in df.columns:
        invoice_list = df[class="hl-string">'Invoice'].unique().tolist()
        if not invoice_list:
            print(class="hl-string">"No invoices found in the specified date range. Exiting.")
            return
        class=class="hl-string">"hl-comment"># Prepare for class="hl-string">'in' clause in OData filter
        invoice_filter_part = class="hl-string">" or ".join([fclass="hl-string">"RefRecIdTable/InvoiceNumber eq '{inv}'" for inv in invoice_list])
        vendtrans_filter_part = class="hl-string">" or ".join([fclass="hl-string">"Invoice eq '{inv}'" for inv in invoice_list])
    else:
        print(class="hl-string">"No invoices registered in the period. Exiting.")
        return

    class=class="hl-string">"hl-comment"># Activities from Workflow History (Submitted, Approved, Rejected, Discrepancy related)
    print(class="hl-string">"\nExtracting workflow activities...")
    entity = class="hl-string">'WorkflowTrackingStatusTables'
    class=class="hl-string">"hl-comment"># NOTE: The class="hl-string">'Comment' field filter is highly dependent on your specific workflow configuration.
    class=class="hl-string">"hl-comment"># Adjust the activity names like class="hl-string">'Submit', class="hl-string">'Approve', class="hl-string">'Reject', class="hl-string">'Resolve' as needed.
    activity_filters = class="hl-string">"(contains(Comment, 'Submit') or contains(Comment, 'Approv') or contains(Comment, 'Reject') or contains(Comment, 'Discrepancy') or contains(Comment, 'Validat'))"
    filters = fclass="hl-string">"({invoice_filter_part}) and {activity_filters}"
    class=class="hl-string">"hl-comment"># Expand to get invoice number and other details
    url = fclass="hl-string">"{base_api_url}/{entity}?$select=Comment,CreatedDateTime,UserId&$expand=RefRecIdTable($select=InvoiceNumber,VendorAccount,InvoiceAmount,DueDate,PaymentTermsName,PurchaseOrderNumber)&$filter={filters}"
    records = fetch_odata_data(token, url)
    df_wf = pd.DataFrame(records)
    if not df_wf.empty:
        df_wf[class="hl-string">'Invoice'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'InvoiceNumber') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'VendorName'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'VendorAccount') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'InvoiceAmount'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'InvoiceAmount') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'InvoiceDueDate'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'DueDate') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'PaymentTerms'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'PaymentTermsName') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'PurchaseOrderNumber'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'PurchaseOrderNumber') if isinstance(x, dict) else None)
        
        def map_activity(comment):
            comment = str(comment).lower()
            if class="hl-string">'submit' in comment: return class="hl-string">'Invoice Submitted For Approval'
            if class="hl-string">'approv' in comment: return class="hl-string">'Invoice Approved'
            if class="hl-string">'reject' in comment: return class="hl-string">'Invoice Rejected'
            if class="hl-string">'discrepancy identified' in comment: return class="hl-string">'Discrepancy Identified' class=class="hl-string">"hl-comment"># Assumes specific comment
            if class="hl-string">'discrepancy resolved' in comment: return class="hl-string">'Discrepancy Resolved' class=class="hl-string">"hl-comment"># Assumes specific comment
            if class="hl-string">'match' in comment: return class="hl-string">'Purchase Order Matched' class=class="hl-string">"hl-comment"># Assumes step name
            if class="hl-string">'receipt matched' in comment: return class="hl-string">'Goods Receipt Matched' class=class="hl-string">"hl-comment"># Assumes step name
            if class="hl-string">'validat' in comment: return class="hl-string">'Invoice Data Validated' class=class="hl-string">"hl-comment"># Assumes step name
            return None

        df_wf[class="hl-string">'ActivityName'] = df_wf[class="hl-string">'Comment'].apply(map_activity)
        df_wf.rename(columns={class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'UserId': class="hl-string">'UserName'}, inplace=True)
        df_wf.dropna(subset=[class="hl-string">'ActivityName', class="hl-string">'Invoice'], inplace=True)
        all_activity_dfs.append(df_wf)

    class=class="hl-string">"hl-comment"># Activities from Posted Transactions (Posted, Paid, Cancelled)
    print(class="hl-string">"\nExtracting posted transactions (Invoice, Payment, Cancellation)...")
    entity = class="hl-string">'VendTrans'
    filters = fclass="hl-string">"dataAreaId eq '{DATA_AREA_ID}' and ({vendtrans_filter_part})"
    url = fclass="hl-string">"{base_api_url}/{entity}?$select=Invoice,TransDate,CreatedDateTime,TransactionType,LastSettleVoucher,VendorAccount&$filter={filters}"
    records = fetch_odata_data(token, url)
    df_vt = pd.DataFrame(records)
    if not df_vt.empty:
        class=class="hl-string">"hl-comment"># Invoice Posted
        df_posted = df_vt[df_vt[class="hl-string">'TransactionType'] == class="hl-string">'Invoice'].copy()
        df_posted[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Posted'
        class=class="hl-string">"hl-comment"># Payment Executed
        df_paid = df_vt[df_vt[class="hl-string">'TransactionType'] == class="hl-string">'Payment'].copy()
        df_paid[class="hl-string">'ActivityName'] = class="hl-string">'Payment Executed'
        class=class="hl-string">"hl-comment"># Invoice Cancelled (often a reversal of the invoice transaction)
        class=class="hl-string">"hl-comment"># This logic may need adjustment based on how cancellations are recorded
        df_cancelled = df_vt[(df_vt[class="hl-string">'TransactionType'] == class="hl-string">'Invoice') & (df_vt[class="hl-string">'Invoice'].str.contains(class="hl-string">'CANCEL', na=False))].copy()
        df_cancelled[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Cancelled'

        df_trans = pd.concat([df_posted, df_paid, df_cancelled])
        df_trans.rename(columns={class="hl-string">'TransDate': class="hl-string">'EventTime', class="hl-string">'VendorAccount': class="hl-string">'VendorName'}, inplace=True)
        df_trans[class="hl-string">'UserName'] = class="hl-string">'System' class=class="hl-string">"hl-comment"># Usually a system-driven event
        all_activity_dfs.append(df_trans)

    class=class="hl-string">"hl-comment"># 11. Payment Scheduled
    print(class="hl-string">"\nExtracting 'Payment Scheduled'...")
    entity = class="hl-string">'VendorPaymentJournalLines'
    filters = fclass="hl-string">"dataAreaId eq '{DATA_AREA_ID}' and ({vendtrans_filter_part})"
    url = fclass="hl-string">"{base_api_url}/{entity}?$select=JournalBatchNumber,CreatedDateTime,AccountDisplayValue,Invoice,AmountInTransactionCurrency&$filter={filters}"
    records = fetch_odata_data(token, url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Payment Scheduled'
        df.rename(columns={class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'AccountDisplayValue': class="hl-string">'VendorName'}, inplace=True)
        df[class="hl-string">'UserName'] = class="hl-string">'System' 
        df[class="hl-string">'InvoiceAmount'] = df[class="hl-string">'AmountInTransactionCurrency']
        all_activity_dfs.append(df)

    class=class="hl-string">"hl-comment"># 13. Payment Cleared
    print(class="hl-string">"\nExtracting 'Payment Cleared'...")
    class=class="hl-string">"hl-comment"># This requires linking payments to bank data, which can be complex.
    class=class="hl-string">"hl-comment"># This example assumes we can find cleared checks and relate them back via voucher.
    settle_vouchers = df_vt[df_vt[class="hl-string">'LastSettleVoucher'].notna()][class="hl-string">'LastSettleVoucher'].unique().tolist()
    if settle_vouchers:
        voucher_filter = class="hl-string">" or ".join([fclass="hl-string">"CheckNumber eq '{v}'" for v in settle_vouchers])
        entity = class="hl-string">'BankCanceledChecks' class=class="hl-string">"hl-comment"># This entity name might vary
        filters = fclass="hl-string">"dataAreaId eq '{DATA_AREA_ID}' and ({voucher_filter})"
        url = fclass="hl-string">"{base_api_url}/{entity}?$select=CheckNumber,ClearedDate,AccountID&$filter={filters}"
        records = fetch_odata_data(token, url)
        df_cleared = pd.DataFrame(records)
        if not df_cleared.empty:
            class=class="hl-string">"hl-comment"># Map back to invoice
            map_voucher_invoice = df_vt.set_index(class="hl-string">'LastSettleVoucher')[class="hl-string">'Invoice'].to_dict()
            df_cleared[class="hl-string">'Invoice'] = df_cleared[class="hl-string">'CheckNumber'].map(map_voucher_invoice)
            df_cleared.dropna(subset=[class="hl-string">'Invoice'], inplace=True)
            df_cleared[class="hl-string">'ActivityName'] = class="hl-string">'Payment Cleared'
            df_cleared.rename(columns={class="hl-string">'ClearedDate': class="hl-string">'EventTime'}, inplace=True)
            df_cleared[class="hl-string">'UserName'] = class="hl-string">'System'
            all_activity_dfs.append(df_cleared)

    class=class="hl-string">"hl-comment"># --- FINAL ASSEMBLY --- 
    print(class="hl-string">"\nAssembling final event log...")
    if not all_activity_dfs:
        print(class="hl-string">"No activities were extracted. Exiting.")
        return

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

    class=class="hl-string">"hl-comment"># Select and reorder columns
    final_cols = [
        class="hl-string">'Invoice', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName',
        class="hl-string">'VendorName', class="hl-string">'InvoiceAmount', class="hl-string">'InvoiceDueDate', 
        class="hl-string">'PaymentTerms', class="hl-string">'PurchaseOrderNumber'
    ]
    class=class="hl-string">"hl-comment"># Add missing columns with None
    for col in final_cols:
        if col not in event_log.columns:
            event_log[col] = None
    
    event_log = event_log[final_cols]
    
    class=class="hl-string">"hl-comment"># Clean up and sort
    event_log.dropna(subset=[class="hl-string">'Invoice', class="hl-string">'ActivityName', class="hl-string">'EventTime'], inplace=True)
    event_log.sort_values(by=[class="hl-string">'Invoice', class="hl-string">'EventTime'], inplace=True)

    class=class="hl-string">"hl-comment"># Export to CSV
    output_filename = class="hl-string">'d365_ap_event_log.csv'
    event_log.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%SZ')
    print(fclass="hl-string">"\nExtraction complete. Event log saved to {output_filename}")

if __name__ == class="hl-string">"__main__":
    main()

WITH
InvoiceRegistered AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Registered' AS varchar(100)) AS ActivityName,
        CAST([Your invoice creation timestamp field] AS datetime2) AS EventTime,
        CAST([Your invoice creation user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice entity]
    WHERE [Your invoice creation timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your invoice creation timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your company filter field] = '[Your company filter]'
      AND [Your document type field] = '[Your document type filter]'
),
InvoiceDataValidated AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Data Validated' AS varchar(100)) AS ActivityName,
        CAST([Your validation timestamp field] AS datetime2) AS EventTime,
        CAST([Your validation user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice validation entity]
    WHERE [Your validation timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your validation timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your validation result field] = '[Your successful validation value]'
),
PurchaseOrderMatched AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Purchase Order Matched' AS varchar(100)) AS ActivityName,
        CAST([Your purchase order match timestamp field] AS datetime2) AS EventTime,
        CAST([Your match user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your purchase order matching entity]
    WHERE [Your purchase order match timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your purchase order match timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your match result field] = '[Your successful match value]'
),
GoodsReceiptMatched AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Goods Receipt Matched' AS varchar(100)) AS ActivityName,
        CAST([Your goods receipt match timestamp field] AS datetime2) AS EventTime,
        CAST([Your match user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your goods receipt matching entity]
    WHERE [Your goods receipt match timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your goods receipt match timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your match result field] = '[Your successful match value]'
),
DiscrepancyIdentified AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Discrepancy Identified' AS varchar(100)) AS ActivityName,
        CAST([Your discrepancy creation timestamp field] AS datetime2) AS EventTime,
        CAST([Your discrepancy user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice discrepancy entity]
    WHERE [Your discrepancy creation timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your discrepancy creation timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your discrepancy status field] = '[Your open discrepancy value]'
),
InvoiceSubmittedForApproval AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Submitted For Approval' AS varchar(100)) AS ActivityName,
        CAST([Your submission timestamp field] AS datetime2) AS EventTime,
        CAST([Your submission user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice workflow entity]
    WHERE [Your submission timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your submission timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your workflow action field] = '[Your submission action value]'
),
InvoiceApproved AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Approved' AS varchar(100)) AS ActivityName,
        CAST([Your approval timestamp field] AS datetime2) AS EventTime,
        CAST([Your approval user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice workflow entity]
    WHERE [Your approval timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your approval timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your workflow action field] = '[Your approval action value]'
),
InvoiceRejected AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Rejected' AS varchar(100)) AS ActivityName,
        CAST([Your rejection timestamp field] AS datetime2) AS EventTime,
        CAST([Your rejection user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice workflow entity]
    WHERE [Your rejection timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your rejection timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your workflow action field] = '[Your rejection action value]'
),
DiscrepancyResolved AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Discrepancy Resolved' AS varchar(100)) AS ActivityName,
        CAST([Your discrepancy resolution timestamp field] AS datetime2) AS EventTime,
        CAST([Your discrepancy resolution user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice discrepancy entity]
    WHERE [Your discrepancy resolution timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your discrepancy resolution timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your discrepancy status field] = '[Your resolved discrepancy value]'
),
InvoicePosted AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Posted' AS varchar(100)) AS ActivityName,
        CAST([Your posting timestamp field] AS datetime2) AS EventTime,
        CAST([Your posting user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice posting entity]
    WHERE [Your posting timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your posting timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your posting status field] = '[Your posted value]'
),
PaymentScheduled AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Payment Scheduled' AS varchar(100)) AS ActivityName,
        CAST([Your payment scheduling timestamp field] AS datetime2) AS EventTime,
        CAST([Your payment scheduling user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your payment proposal or journal entity]
    WHERE [Your payment scheduling timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your payment scheduling timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your payment scheduling status field] = '[Your scheduled value]'
),
PaymentExecuted AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Payment Executed' AS varchar(100)) AS ActivityName,
        CAST([Your payment execution timestamp field] AS datetime2) AS EventTime,
        CAST([Your payment execution user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your payment journal entity]
    WHERE [Your payment execution timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your payment execution timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your payment execution status field] = '[Your executed value]'
),
PaymentCleared AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Payment Cleared' AS varchar(100)) AS ActivityName,
        CAST([Your bank clearing timestamp field] AS datetime2) AS EventTime,
        CAST([Your bank reconciliation user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your bank reconciliation entity]
    WHERE [Your bank clearing timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your bank clearing timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your bank clearing status field] = '[Your cleared value]'
),
InvoiceCancelled AS (
    SELECT
        CAST([Your invoice identifier field] AS varchar(255)) AS Invoice,
        CAST('Invoice Cancelled' AS varchar(100)) AS ActivityName,
        CAST([Your cancellation timestamp field] AS datetime2) AS EventTime,
        CAST([Your cancellation user field] AS varchar(255)) AS UserName,
        CAST([Your vendor name field] AS varchar(255)) AS VendorName,
        CAST([Your invoice amount field] AS decimal(38, 6)) AS InvoiceAmount,
        CAST([Your invoice due date field] AS date) AS InvoiceDueDate,
        CAST([Your payment terms field] AS varchar(255)) AS PaymentTerms,
        CAST([Your purchase order number field] AS varchar(255)) AS PurchaseOrderNumber
    FROM [Your invoice cancellation entity]
    WHERE [Your cancellation timestamp field] >= CAST('[Your start date]' AS datetime2)
      AND [Your cancellation timestamp field] < CAST('[Your end date]' AS datetime2)
      AND [Your cancellation status field] = '[Your cancelled value]'
)
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoiceRegistered
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoiceDataValidated
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM PurchaseOrderMatched
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM GoodsReceiptMatched
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM DiscrepancyIdentified
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoiceSubmittedForApproval
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoiceApproved
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoiceRejected
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM DiscrepancyResolved
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoicePosted
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM PaymentScheduled
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM PaymentExecuted
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM PaymentCleared
UNION ALL
SELECT Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber FROM InvoiceCancelled;