Ihr Datentemplate für Purchase-to-Pay: Einkaufsbestellungen

import pandas as pd
import numpy as np
from datetime import datetime

# --- Configuration ---
# This script assumes you have unzipped the export package into a folder named 'd365_export'
# Update file paths as needed.

SOURCE_SYSTEM = 'Microsoft Dynamics 365'
LAST_DATA_UPDATE = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

# File paths to the CSV files from the D365 export
file_paths = {
    'req_lines': './d365_export/Purchase requisition lines V2.csv',
    'po_headers': './d365_export/Purchase order headers V2.csv',
    'po_confirmations': './d365_export/Purchase order confirmations V2.csv',
    'po_versions': './d365_export/Purchase order header versions.csv',
    'receipts': './d365_export/Product receipt headers.csv',
    'quality': './d365_export/Quality orders.csv',
    'returns': './d365_export/Return order headers.csv',
    'invoices': './d365_export/Vendor invoice journal headers.csv'
}

def load_data(path):
    try:
        return pd.read_csv(path, low_memory=False)
    except FileNotFoundError:
        print(f'Warning: File not found at {path}. Skipping...')
        return None

# --- Main Transformation Logic ---
event_log_frames = []

# Load all data sources
dataframes = {name: load_data(path) for name, path in file_paths.items()}

# Activity: Purchase Requisition Created & Approved
if dataframes['req_lines'] is not None:
    req = dataframes['req_lines'].copy()
    req = req[req['PurchaseOrderNumber'].notna() & (req['PurchaseOrderNumber'] != '')]
    
    # Created event
    created = req[['RequisitionNumber', 'CreatedDateTime', 'RequestedBy', 'PurchaseOrderNumber']].copy()
    created.rename(columns={'RequisitionNumber': 'CaseID_temp', 'CreatedDateTime': 'EventTime', 'RequestedBy': 'UserName'}, inplace=True)
    created['ActivityName'] = 'Purchase Requisition Created'
    event_log_frames.append(created)

    # Approved event (using status change)
    approved = req[req['RequisitionStatus'].isin(['Approved', 'Closed'])].copy()
    approved = approved[['RequisitionNumber', 'ModifiedDateTime', 'RequestedBy', 'PurchaseOrderNumber']]
    approved.rename(columns={'RequisitionNumber': 'CaseID_temp', 'ModifiedDateTime': 'EventTime', 'RequestedBy': 'UserName'}, inplace=True)
    approved['ActivityName'] = 'Purchase Requisition Approved'
    event_log_frames.append(approved)

# Activities from Purchase Order Headers
if dataframes['po_headers'] is not None:
    po = dataframes['po_headers'].copy()
    po_base = po[['PurchaseOrderNumber', 'VendorAccount', 'VendorName', 'TotalAmount', 'PurchaseOrderStatus', 'RequestedDeliveryDate']].copy()
    po_base.rename(columns={'TotalAmount': 'PurchaseOrderTotalAmount'}, inplace=True)

    # Created Event
    created = po[['PurchaseOrderNumber', 'CreatedDateTime', 'CreatedBy']].copy()
    created.rename(columns={'CreatedDateTime': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    created['ActivityName'] = 'Purchase Order Created'
    event_log_frames.append(created)

    # Submitted & Approved Events (approximated by status changes)
    approved = po[po['DocumentApprovalStatus'].isin(['Approved', 'Confirmed', 'Finalized'])].copy()
    approved = approved[['PurchaseOrderNumber', 'ModifiedDateTime', 'ModifiedBy']]
    approved.rename(columns={'ModifiedDateTime': 'EventTime', 'ModifiedBy': 'UserName'}, inplace=True)
    # Logic: Submitted is often the first change from Draft, Approved is the final state. We use ModifiedDateTime for both as a proxy.
    submitted = approved.copy()
    submitted['ActivityName'] = 'Purchase Order Submitted for Approval'
    approved['ActivityName'] = 'Purchase Order Approved'
    event_log_frames.append(submitted)
    event_log_frames.append(approved)

    # Completed & Canceled Events
    completed = po[po['PurchaseOrderStatus'].isin(['Invoiced', 'Received'])].copy()
    completed = completed[['PurchaseOrderNumber', 'ModifiedDateTime', 'ModifiedBy']]
    completed.rename(columns={'ModifiedDateTime': 'EventTime', 'ModifiedBy': 'UserName'}, inplace=True)
    completed['ActivityName'] = 'Purchase Order Completed'
    event_log_frames.append(completed)

    canceled = po[po['PurchaseOrderStatus'] == 'Canceled'].copy()
    canceled = canceled[['PurchaseOrderNumber', 'ModifiedDateTime', 'ModifiedBy']]
    canceled.rename(columns={'ModifiedDateTime': 'EventTime', 'ModifiedBy': 'UserName'}, inplace=True)
    canceled['ActivityName'] = 'Purchase Order Canceled'
    event_log_frames.append(canceled)

# Activity: Purchase Order Sent to Vendor (Confirmation)
if dataframes['po_confirmations'] is not None:
    confirm = dataframes['po_confirmations'].copy()
    confirm = confirm[['PurchaseOrderNumber', 'ConfirmationDate', 'CreatedBy']].copy()
    confirm.rename(columns={'ConfirmationDate': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    confirm['ActivityName'] = 'Purchase Order Sent to Vendor'
    event_log_frames.append(confirm)

# Activity: Purchase Order Changed
if dataframes['po_versions'] is not None:
    versions = dataframes['po_versions'].copy()
    versions = versions[['PurchaseOrderNumber', 'CreatedDateTime', 'CreatedBy']].copy()
    # We only care about changes after the first version
    versions = versions.sort_values('CreatedDateTime').groupby('PurchaseOrderNumber').tail(-1)
    versions.rename(columns={'CreatedDateTime': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    versions['ActivityName'] = 'Purchase Order Changed'
    event_log_frames.append(versions)

# Activity: Goods Receipt Posted
if dataframes['receipts'] is not None:
    receipts = dataframes['receipts'].copy()
    receipts = receipts[['PurchaseOrderNumber', 'ReceiptDate', 'CreatedBy']].copy()
    receipts.rename(columns={'ReceiptDate': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    receipts['ActivityName'] = 'Goods Receipt Posted'
    event_log_frames.append(receipts)

# Activity: Quality Inspection Performed
if dataframes['quality'] is not None:
    quality = dataframes['quality'][dataframes['quality']['ReferenceType'] == 'Purchase'].copy()
    quality = quality[['InventoryLotID', 'AcceptedDateTime', 'CreatedBy']].copy()
    # Note: Linking Quality Order to PO may require InventoryLotID or custom logic
    quality.rename(columns={'InventoryLotID': 'PurchaseOrderNumber', 'AcceptedDateTime': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    quality['ActivityName'] = 'Quality Inspection Performed'
    event_log_frames.append(quality)

# Activity: Goods Returned to Vendor
if dataframes['returns'] is not None:
    returns = dataframes['returns'].copy()
    returns = returns[['PurchaseOrderNumber', 'CreatedDateTime', 'CreatedBy']].copy()
    returns.rename(columns={'CreatedDateTime': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    returns['ActivityName'] = 'Goods Returned to Vendor'
    event_log_frames.append(returns)

# Activity: Purchase Order Invoiced
if dataframes['invoices'] is not None:
    invoices = dataframes['invoices'].copy()
    invoices = invoices[['PurchaseOrderNumber', 'InvoiceDate', 'CreatedBy']].copy()
    invoices.rename(columns={'InvoiceDate': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    invoices['ActivityName'] = 'Purchase Order Invoiced'
    event_log_frames.append(invoices)

# --- Final Assembly ---
if event_log_frames:
    event_log = pd.concat(event_log_frames, ignore_index=True)

    # Clean up and merge attributes
    event_log = event_log[event_log['PurchaseOrderNumber'].notna()]
    if dataframes['po_headers'] is not None:
      po_base = dataframes['po_headers'][['PurchaseOrderNumber', 'VendorName', 'PurchaseOrderTotalAmount', 'PurchaseOrderStatus', 'RequestedDeliveryDate']].copy()
      event_log = pd.merge(event_log, po_base, on='PurchaseOrderNumber', how='left')

    # Add constant columns
    event_log['SourceSystem'] = SOURCE_SYSTEM
    event_log['LastDataUpdate'] = LAST_DATA_UPDATE
    
    # Ensure data types are correct
    event_log['EventTime'] = pd.to_datetime(event_log['EventTime'], errors='coerce')
    event_log.dropna(subset=['EventTime'], inplace=True)
    
    # Final column selection and ordering
    final_columns = [
        'PurchaseOrderNumber', 'ActivityName', 'EventTime', 'SourceSystem', 'LastDataUpdate',
        'UserName', 'VendorName', 'PurchaseOrderTotalAmount', 'PurchaseOrderStatus', 'RequestedDeliveryDate'
    ]
    # Select only columns that exist in the dataframe to avoid errors
    event_log = event_log[[col for col in final_columns if col in event_log.columns]]
    
    event_log.sort_values(by=['PurchaseOrderNumber', 'EventTime'], inplace=True)

    # Save to CSV
    event_log.to_csv('p2p_event_log.csv', index=False)
    print('Event log saved to p2p_event_log.csv')
else:
    print('No data processed. Event log not created.')

DECLARE @StartDate AS DATETIME = '2023-01-01';
DECLARE @EndDate AS DATETIME = '2024-01-01';
DECLARE @DATAAREAID AS NVARCHAR(4) = '[YourCompanyID]'; -- Example: 'USMF'

WITH PO_Base AS (
    SELECT
        PT.PURCHID AS PurchaseOrderNumber,
        PT.CREATEDDATETIME,
        PT.MODIFIEDDATETIME,
        PT.DOCUMENTSTATE,
        PT.PURCHSTATUS,
        PT.DELIVERYDATE AS RequestedDeliveryDate,
        PT.INVOICEACCOUNT,
        VT.NAME AS VendorName,
        PT.createdByUser AS UserName, -- Using createdByUser as a general user, can be changed per event
        PT.DATAAREAID,
        PT.RECID
    FROM PURCHTABLE PT
    LEFT JOIN VENDTABLE VT ON PT.INVOICEACCOUNT = VT.ACCOUNTNUM AND PT.DATAAREAID = VT.DATAAREAID
    WHERE PT.DATAAREAID = @DATAAREAID AND PT.CREATEDDATETIME BETWEEN @StartDate AND @EndDate
)
-- 1. Purchase Requisition Created
SELECT
    PL.PURCHID AS PurchaseOrderNumber,
    'Purchase Requisition Created' AS ActivityName,
    PRT.CREATEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    PRT.CREATEDBY AS UserName,
    PB.VendorName,
    NULL AS PurchaseOrderTotalAmount, -- Amount not yet known on PO
    NULL AS PurchaseOrderStatus,
    PB.RequestedDeliveryDate
FROM PURCHREQTABLE PRT
JOIN PURCHREQLINE PRL ON PRT.RECID = PRL.PURCHREQTABLE
JOIN PURCHLINE PL ON PRL.RECID = PL.PURCHREQLINEREFID
JOIN PO_Base PB ON PL.PURCHID = PB.PurchaseOrderNumber AND PL.DATAAREAID = PB.DATAAREAID
WHERE PRT.CREATEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 2. Purchase Requisition Approved
-- Note: This uses the requisition's status change to approved. A more precise method involves workflow tables.
SELECT
    PL.PURCHID AS PurchaseOrderNumber,
    'Purchase Requisition Approved' AS ActivityName,
    PRT.MODIFIEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    PRT.MODIFIEDBY AS UserName,
    PB.VendorName,
    NULL AS PurchaseOrderTotalAmount,
    NULL AS PurchaseOrderStatus,
    PB.RequestedDeliveryDate
FROM PURCHREQTABLE PRT
JOIN PURCHREQLINE PRL ON PRT.RECID = PRL.PURCHREQTABLE
JOIN PURCHLINE PL ON PRL.RECID = PL.PURCHREQLINEREFID
JOIN PO_Base PB ON PL.PURCHID = PB.PurchaseOrderNumber AND PL.DATAAREAID = PB.DATAAREAID
WHERE PRT.STATUS = 2 -- Status for 'Approved'

UNION ALL

-- 3. Purchase Order Created
SELECT
    PurchaseOrderNumber,
    'Purchase Order Created' AS ActivityName,
    CREATEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base

UNION ALL

-- 4. Purchase Order Submitted for Approval
-- Note: This is approximated by creation if workflow is immediate. For detailed tracking, join with WORKFLOWTRACKINGSTATUSTABLE.
SELECT
    PurchaseOrderNumber,
    'Purchase Order Submitted for Approval' AS ActivityName,
    CREATEDDATETIME AS EventTime, -- Approximated as creation time
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base
WHERE DOCUMENTSTATE > 1 -- Any state after Draft

UNION ALL

-- 5. Purchase Order Approved
SELECT
    PurchaseOrderNumber,
    'Purchase Order Approved' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base
WHERE DOCUMENTSTATE = 2 -- Approved

UNION ALL

-- 6. Purchase Order Sent to Vendor (Confirmed)
SELECT
    PCJ.PURCHID AS PurchaseOrderNumber,
    'Purchase Order Sent to Vendor' AS ActivityName,
    PCJ.CONFIRMATIONDATE AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    PCJ.CREATEDBY AS UserName,
    PB.VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PCJ.PURCHID) AS PurchaseOrderTotalAmount,
    CAST(PB.DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    PB.RequestedDeliveryDate
FROM PURCHCONFIRMATIONJOUR PCJ
JOIN PO_Base PB ON PCJ.PURCHID = PB.PurchaseOrderNumber AND PCJ.DATAAREAID = PB.DATAAREAID

UNION ALL

-- 7. Purchase Order Confirmed by Vendor
-- Note: This is often not a standard event. The logic here is a placeholder.
-- You may need to adapt this based on custom fields or statuses indicating vendor confirmation.
SELECT
    NULL AS PurchaseOrderNumber,
    'Purchase Order Confirmed by Vendor' AS ActivityName,
    NULL AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    NULL AS UserName,
    NULL AS VendorName,
    NULL AS PurchaseOrderTotalAmount,
    NULL AS PurchaseOrderStatus,
    NULL AS RequestedDeliveryDate
WHERE 1=0 -- Disable this by default

UNION ALL

-- 8. Purchase Order Changed
SELECT
    PurchaseOrderNumber,
    'Purchase Order Changed' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base
WHERE MODIFIEDDATETIME <> CREATEDDATETIME AND DOCUMENTSTATE > 1 -- Change after initial creation/draft

UNION ALL

-- 9. Goods Receipt Posted
SELECT
    VPJ.PURCHID AS PurchaseOrderNumber,
    'Goods Receipt Posted' AS ActivityName,
    VPJ.DELIVERYDATE AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    VPJ.CREATEDBY AS UserName,
    PB.VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = VPJ.PURCHID) AS PurchaseOrderTotalAmount,
    CAST(PB.DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    PB.RequestedDeliveryDate
FROM VENDPACKINGSLIPJOUR VPJ
JOIN PO_Base PB ON VPJ.PURCHID = PB.PurchaseOrderNumber AND VPJ.DATAAREAID = PB.DATAAREAID

UNION ALL

-- 10. Quality Inspection Performed
SELECT
    IT.REFID AS PurchaseOrderNumber,
    'Quality Inspection Performed' AS ActivityName,
    IQT.CREATEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    IQT.CREATEDBY AS UserName,
    PB.VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = IT.REFID) AS PurchaseOrderTotalAmount,
    CAST(PB.DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    PB.RequestedDeliveryDate
FROM INVENTQUALITYORDERTABLE IQT
JOIN INVENTTRANS IT ON IQT.INVENTTRANSORIGIN = IT.INVENTTRANSORIGIN
JOIN PO_Base PB ON IT.REFID = PB.PurchaseOrderNumber AND IT.DATAAREAID = PB.DATAAREAID
WHERE IQT.STATUS = 2 -- Status for 'Passed' or 'Closed', adjust as needed

UNION ALL

-- 11. Goods Returned to Vendor
SELECT
    PurchaseOrderNumber,
    'Goods Returned to Vendor' AS ActivityName,
    CREATEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base
WHERE EXISTS (SELECT 1 FROM PURCHLINE PL WHERE PL.PURCHID = PO_Base.PurchaseOrderNumber AND PL.PURCHQTY < 0)

UNION ALL

-- 12. Purchase Order Invoiced
SELECT
    VIJ.PURCHID AS PurchaseOrderNumber,
    'Purchase Order Invoiced' AS ActivityName,
    VIJ.INVOICEDATE AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    VIJ.CREATEDBY AS UserName,
    PB.VendorName,
    VIJ.INVOICEAMOUNT AS PurchaseOrderTotalAmount,
    CAST(PB.DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    PB.RequestedDeliveryDate
FROM VENDINVOICEJOUR VIJ
JOIN PO_Base PB ON VIJ.PURCHID = PB.PurchaseOrderNumber AND VIJ.DATAAREAID = PB.DATAAREAID

UNION ALL

-- 13. Purchase Order Completed
SELECT
    PurchaseOrderNumber,
    'Purchase Order Completed' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(PURCHSTATUS AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base
WHERE PURCHSTATUS = 4 -- Status for 'Invoiced'

UNION ALL

-- 14. Purchase Order Canceled
SELECT
    PurchaseOrderNumber,
    'Purchase Order Canceled' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(PURCHSTATUS AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base
WHERE PURCHSTATUS = 5; -- Status for 'Canceled'

import requests
import pandas as pd
import datetime

class=class="hl-string">"hl-comment"># --- PLACEHOLDER CONFIGURATION ---
D365_URL = class="hl-string">"https://[your-environment].operations.dynamics.com"
CLIENT_ID = class="hl-string">"[your-client-id]"
CLIENT_SECRET = class="hl-string">"[your-client-secret]"
TENANT_ID = class="hl-string">"[your-tenant-id]"

class=class="hl-string">"hl-comment"># --- EXTRACTION PARAMETERS ---
data_area_id = class="hl-string">"[YourCompanyCode]"  class=class="hl-string">"hl-comment"># e.g., class="hl-string">'USMF'
start_date_filter = class="hl-string">"2023-01-01T00:00:00Z"


def get_access_token():
    class="hl-string">"""Authenticates with Azure AD to get an access token."""
    auth_url = fclass="hl-string">"https://login.microsoftonline.com/{TENANT_ID}/oauth2/token"
    payload = {
        class="hl-string">'grant_type': class="hl-string">'client_credentials',
        class="hl-string">'client_id': CLIENT_ID,
        class="hl-string">'client_secret': CLIENT_SECRET,
        class="hl-string">'resource': D365_URL
    }
    response = requests.post(auth_url, data=payload)
    response.raise_for_status()
    return response.json()[class="hl-string">'access_token']

def fetch_all_pages(url, headers):
    class="hl-string">"""Fetches all pages of data from a D365 OData endpoint."""
    all_results = []
    while url:
        response = requests.get(url, headers=headers)
        response.raise_for_status()
        data = response.json()
        all_results.extend(data[class="hl-string">'value'])
        url = data.get(class="hl-string">'@odata.nextLink')
        print(fclass="hl-string">"Fetched {len(all_results)} records...")
    return all_results

def extract_p2p_data():
    class="hl-string">"""Main function to extract P2P event log data."""
    try:
        access_token = get_access_token()
    except requests.exceptions.HTTPError as e:
        print(fclass="hl-string">"Authentication failed: {e.response.text}")
        return

    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json'
    }

    all_events = []
    last_data_update = datetime.datetime.now(datetime.timezone.utc).isoformat()

    class=class="hl-string">"hl-comment"># Define queries for each activity
    activity_queries = {
        class="hl-string">"Purchase Requisition Created": fclass="hl-string">"{D365_URL}/data/PurchaseRequisitionLinesV2?$filter=dataAreaId eq '{data_area_id}' and CreatedDateTime ge {start_date_filter}&$select=PurchaseRequisitionNumber,PurchaseOrderNumber,CreatedDateTime,CreatedBy,RequisitionStatus,RequestedDeliveryDate",
        class="hl-string">"Purchase Requisition Approved": fclass="hl-string">"{D365_URL}/data/PurchaseRequisitionLinesV2?$filter=dataAreaId eq '{data_area_id}' and RequisitionStatus eq 'Approved' and ModifiedDateTime ge {start_date_filter}&$select=PurchaseRequisitionNumber,PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,RequisitionStatus,RequestedDeliveryDate",
        class="hl-string">"Purchase Order Created": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and CreatedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,CreatedDateTime,CreatedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
        class="hl-string">"Purchase Order Submitted for Approval": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and ApprovalStatus eq 'InReview' and ModifiedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
        class="hl-string">"Purchase Order Approved": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and ApprovalStatus eq 'Approved' and ModifiedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
        class="hl-string">"Purchase Order Sent to Vendor": fclass="hl-string">"{D365_URL}/data/PurchaseOrderConfirmationsV2?$filter=dataAreaId eq '{data_area_id}' and ConfirmationDate ge {start_date_filter}&$select=PurchaseOrderNumber,ConfirmationDate,CreatedBy",
        class="hl-string">"Purchase Order Confirmed by Vendor": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and PurchaseOrderStatus eq 'Confirmed' and ModifiedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
        class="hl-string">"Purchase Order Changed": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and ModifiedDateTime gt CreatedDateTime and ModifiedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
        class="hl-string">"Goods Receipt Posted": fclass="hl-string">"{D365_URL}/data/PurchaseOrderReceiptsV2?$filter=dataAreaId eq '{data_area_id}' and ReceiptDate ge {start_date_filter}&$select=PurchaseOrderNumber,ReceiptDate,CreatedBy,ProductReceiptNumber",
        class="hl-string">"Quality Inspection Performed": fclass="hl-string">"{D365_URL}/data/QualityOrders?$filter=dataAreaId eq '{data_area_id}' and ReferenceType eq 'Purchase' and CreatedDateTime ge {start_date_filter}&$select=ReferenceNumber,CreatedDateTime,CreatedBy",
        class="hl-string">"Goods Returned to Vendor": fclass="hl-string">"{D365_URL}/data/[YourVendorReturnEntity]?$filter=dataAreaId eq '{data_area_id}' and CreatedDateTime ge {start_date_filter}&$select=OriginalPurchaseOrderNumber,ReturnDate,CreatedBy",
        class="hl-string">"Purchase Order Invoiced": fclass="hl-string">"{D365_URL}/data/PurchaseOrderInvoiceLinesV2?$filter=dataAreaId eq '{data_area_id}' and InvoiceDate ge {start_date_filter}&$select=PurchaseOrderNumber,InvoiceDate,CreatedBy",
        class="hl-string">"Purchase Order Completed": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and PurchaseOrderStatus eq 'Invoiced' and ModifiedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
        class="hl-string">"Purchase Order Canceled": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and PurchaseOrderStatus eq 'Canceled' and ModifiedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
    }

    for activity, url in activity_queries.items():
        print(fclass="hl-string">'\n--- Fetching data for activity: {activity} ---')
        try:
            results = fetch_all_pages(url, headers)
            for item in results:
                po_number = item.get(class="hl-string">'PurchaseOrderNumber') or item.get(class="hl-string">'ReferenceNumber') or item.get(class="hl-string">'OriginalPurchaseOrderNumber')
                if not po_number:
                    continue

                event = {
                    class="hl-string">'PurchaseOrderNumber': po_number,
                    class="hl-string">'ActivityName': activity,
                    class="hl-string">'EventTime': item.get(class="hl-string">'ModifiedDateTime') or item.get(class="hl-string">'CreatedDateTime') or item.get(class="hl-string">'ConfirmationDate') or item.get(class="hl-string">'ReceiptDate') or item.get(class="hl-string">'InvoiceDate') or item.get(class="hl-string">'ReturnDate'),
                    class="hl-string">'SourceSystem': class="hl-string">'Microsoft Dynamics 365',
                    class="hl-string">'LastDataUpdate': last_data_update,
                    class="hl-string">'UserName': item.get(class="hl-string">'ModifiedBy') or item.get(class="hl-string">'CreatedBy'),
                    class="hl-string">'VendorName': item.get(class="hl-string">'VendorV2', {}).get(class="hl-string">'VendorName') if item.get(class="hl-string">'VendorV2') else None,
                    class="hl-string">'PurchaseOrderTotalAmount': item.get(class="hl-string">'PurchaseOrderTotalAmount'),
                    class="hl-string">'PurchaseOrderStatus': item.get(class="hl-string">'PurchaseOrderStatus'),
                    class="hl-string">'RequestedDeliveryDate': item.get(class="hl-string">'RequestedDeliveryDate'),
                }
                all_events.append(event)
        except requests.exceptions.HTTPError as e:
            print(fclass="hl-string">"Could not fetch data for '{activity}'. URL: {url}. Error: {e.response.text}")
        except Exception as e:
            print(fclass="hl-string">"An unexpected error occurred for '{activity}': {e}")

    if not all_events:
        print(class="hl-string">"No events were extracted. Please check your configuration and D365 data.")
        return

    class=class="hl-string">"hl-comment"># Create DataFrame and save to CSV
    df = pd.DataFrame(all_events)
    df = df.dropna(subset=[class="hl-string">'PurchaseOrderNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime'])
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'], errors=class="hl-string">'coerce')
    df = df.sort_values(by=[class="hl-string">'PurchaseOrderNumber', class="hl-string">'EventTime'])

    class=class="hl-string">"hl-comment"># Reorder columns for clarity
    column_order = [
        class="hl-string">'PurchaseOrderNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'UserName', class="hl-string">'VendorName', class="hl-string">'PurchaseOrderTotalAmount', class="hl-string">'PurchaseOrderStatus', class="hl-string">'RequestedDeliveryDate'
    ]
    df = df[column_order]

    df.to_csv(class="hl-string">'d365_p2p_event_log.csv', index=False)
    print(class="hl-string">'\nExtraction complete. Event log saved to d365_p2p_event_log.csv')

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

import pandas as pd
import numpy as np
from datetime import datetime

# --- Configuration ---
# This script assumes you have unzipped the export package into a folder named 'd365_export'
# Update file paths as needed.

SOURCE_SYSTEM = 'Microsoft Dynamics 365'
LAST_DATA_UPDATE = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

# File paths to the CSV files from the D365 export
file_paths = {
    'req_lines': './d365_export/Purchase requisition lines V2.csv',
    'po_headers': './d365_export/Purchase order headers V2.csv',
    'po_confirmations': './d365_export/Purchase order confirmations V2.csv',
    'po_versions': './d365_export/Purchase order header versions.csv',
    'receipts': './d365_export/Product receipt headers.csv',
    'quality': './d365_export/Quality orders.csv',
    'returns': './d365_export/Return order headers.csv',
    'invoices': './d365_export/Vendor invoice journal headers.csv'
}

def load_data(path):
    try:
        return pd.read_csv(path, low_memory=False)
    except FileNotFoundError:
        print(f'Warning: File not found at {path}. Skipping...')
        return None

# --- Main Transformation Logic ---
event_log_frames = []

# Load all data sources
dataframes = {name: load_data(path) for name, path in file_paths.items()}

# Activity: Purchase Requisition Created & Approved
if dataframes['req_lines'] is not None:
    req = dataframes['req_lines'].copy()
    req = req[req['PurchaseOrderNumber'].notna() & (req['PurchaseOrderNumber'] != '')]
    
    # Created event
    created = req[['RequisitionNumber', 'CreatedDateTime', 'RequestedBy', 'PurchaseOrderNumber']].copy()
    created.rename(columns={'RequisitionNumber': 'CaseID_temp', 'CreatedDateTime': 'EventTime', 'RequestedBy': 'UserName'}, inplace=True)
    created['ActivityName'] = 'Purchase Requisition Created'
    event_log_frames.append(created)

    # Approved event (using status change)
    approved = req[req['RequisitionStatus'].isin(['Approved', 'Closed'])].copy()
    approved = approved[['RequisitionNumber', 'ModifiedDateTime', 'RequestedBy', 'PurchaseOrderNumber']]
    approved.rename(columns={'RequisitionNumber': 'CaseID_temp', 'ModifiedDateTime': 'EventTime', 'RequestedBy': 'UserName'}, inplace=True)
    approved['ActivityName'] = 'Purchase Requisition Approved'
    event_log_frames.append(approved)

# Activities from Purchase Order Headers
if dataframes['po_headers'] is not None:
    po = dataframes['po_headers'].copy()
    po_base = po[['PurchaseOrderNumber', 'VendorAccount', 'VendorName', 'TotalAmount', 'PurchaseOrderStatus', 'RequestedDeliveryDate']].copy()
    po_base.rename(columns={'TotalAmount': 'PurchaseOrderTotalAmount'}, inplace=True)

    # Created Event
    created = po[['PurchaseOrderNumber', 'CreatedDateTime', 'CreatedBy']].copy()
    created.rename(columns={'CreatedDateTime': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    created['ActivityName'] = 'Purchase Order Created'
    event_log_frames.append(created)

    # Submitted & Approved Events (approximated by status changes)
    approved = po[po['DocumentApprovalStatus'].isin(['Approved', 'Confirmed', 'Finalized'])].copy()
    approved = approved[['PurchaseOrderNumber', 'ModifiedDateTime', 'ModifiedBy']]
    approved.rename(columns={'ModifiedDateTime': 'EventTime', 'ModifiedBy': 'UserName'}, inplace=True)
    # Logic: Submitted is often the first change from Draft, Approved is the final state. We use ModifiedDateTime for both as a proxy.
    submitted = approved.copy()
    submitted['ActivityName'] = 'Purchase Order Submitted for Approval'
    approved['ActivityName'] = 'Purchase Order Approved'
    event_log_frames.append(submitted)
    event_log_frames.append(approved)

    # Completed & Canceled Events
    completed = po[po['PurchaseOrderStatus'].isin(['Invoiced', 'Received'])].copy()
    completed = completed[['PurchaseOrderNumber', 'ModifiedDateTime', 'ModifiedBy']]
    completed.rename(columns={'ModifiedDateTime': 'EventTime', 'ModifiedBy': 'UserName'}, inplace=True)
    completed['ActivityName'] = 'Purchase Order Completed'
    event_log_frames.append(completed)

    canceled = po[po['PurchaseOrderStatus'] == 'Canceled'].copy()
    canceled = canceled[['PurchaseOrderNumber', 'ModifiedDateTime', 'ModifiedBy']]
    canceled.rename(columns={'ModifiedDateTime': 'EventTime', 'ModifiedBy': 'UserName'}, inplace=True)
    canceled['ActivityName'] = 'Purchase Order Canceled'
    event_log_frames.append(canceled)

# Activity: Purchase Order Sent to Vendor (Confirmation)
if dataframes['po_confirmations'] is not None:
    confirm = dataframes['po_confirmations'].copy()
    confirm = confirm[['PurchaseOrderNumber', 'ConfirmationDate', 'CreatedBy']].copy()
    confirm.rename(columns={'ConfirmationDate': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    confirm['ActivityName'] = 'Purchase Order Sent to Vendor'
    event_log_frames.append(confirm)

# Activity: Purchase Order Changed
if dataframes['po_versions'] is not None:
    versions = dataframes['po_versions'].copy()
    versions = versions[['PurchaseOrderNumber', 'CreatedDateTime', 'CreatedBy']].copy()
    # We only care about changes after the first version
    versions = versions.sort_values('CreatedDateTime').groupby('PurchaseOrderNumber').tail(-1)
    versions.rename(columns={'CreatedDateTime': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    versions['ActivityName'] = 'Purchase Order Changed'
    event_log_frames.append(versions)

# Activity: Goods Receipt Posted
if dataframes['receipts'] is not None:
    receipts = dataframes['receipts'].copy()
    receipts = receipts[['PurchaseOrderNumber', 'ReceiptDate', 'CreatedBy']].copy()
    receipts.rename(columns={'ReceiptDate': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    receipts['ActivityName'] = 'Goods Receipt Posted'
    event_log_frames.append(receipts)

# Activity: Quality Inspection Performed
if dataframes['quality'] is not None:
    quality = dataframes['quality'][dataframes['quality']['ReferenceType'] == 'Purchase'].copy()
    quality = quality[['InventoryLotID', 'AcceptedDateTime', 'CreatedBy']].copy()
    # Note: Linking Quality Order to PO may require InventoryLotID or custom logic
    quality.rename(columns={'InventoryLotID': 'PurchaseOrderNumber', 'AcceptedDateTime': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    quality['ActivityName'] = 'Quality Inspection Performed'
    event_log_frames.append(quality)

# Activity: Goods Returned to Vendor
if dataframes['returns'] is not None:
    returns = dataframes['returns'].copy()
    returns = returns[['PurchaseOrderNumber', 'CreatedDateTime', 'CreatedBy']].copy()
    returns.rename(columns={'CreatedDateTime': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    returns['ActivityName'] = 'Goods Returned to Vendor'
    event_log_frames.append(returns)

# Activity: Purchase Order Invoiced
if dataframes['invoices'] is not None:
    invoices = dataframes['invoices'].copy()
    invoices = invoices[['PurchaseOrderNumber', 'InvoiceDate', 'CreatedBy']].copy()
    invoices.rename(columns={'InvoiceDate': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    invoices['ActivityName'] = 'Purchase Order Invoiced'
    event_log_frames.append(invoices)

# --- Final Assembly ---
if event_log_frames:
    event_log = pd.concat(event_log_frames, ignore_index=True)

    # Clean up and merge attributes
    event_log = event_log[event_log['PurchaseOrderNumber'].notna()]
    if dataframes['po_headers'] is not None:
      po_base = dataframes['po_headers'][['PurchaseOrderNumber', 'VendorName', 'PurchaseOrderTotalAmount', 'PurchaseOrderStatus', 'RequestedDeliveryDate']].copy()
      event_log = pd.merge(event_log, po_base, on='PurchaseOrderNumber', how='left')

    # Add constant columns
    event_log['SourceSystem'] = SOURCE_SYSTEM
    event_log['LastDataUpdate'] = LAST_DATA_UPDATE
    
    # Ensure data types are correct
    event_log['EventTime'] = pd.to_datetime(event_log['EventTime'], errors='coerce')
    event_log.dropna(subset=['EventTime'], inplace=True)
    
    # Final column selection and ordering
    final_columns = [
        'PurchaseOrderNumber', 'ActivityName', 'EventTime', 'SourceSystem', 'LastDataUpdate',
        'UserName', 'VendorName', 'PurchaseOrderTotalAmount', 'PurchaseOrderStatus', 'RequestedDeliveryDate'
    ]
    # Select only columns that exist in the dataframe to avoid errors
    event_log = event_log[[col for col in final_columns if col in event_log.columns]]
    
    event_log.sort_values(by=['PurchaseOrderNumber', 'EventTime'], inplace=True)

    # Save to CSV
    event_log.to_csv('p2p_event_log.csv', index=False)
    print('Event log saved to p2p_event_log.csv')
else:
    print('No data processed. Event log not created.')