Je datatemplate voor inkoopfactuurverwerking (AP)

-- OTBI Analysis Configuration for Accounts Payable Event Log
-- This is a logical representation of an OTBI UNION report.
-- Each block represents a separate analysis combined with a UNION operation.
-- The column order and data types must be identical in each block.

-- Base Columns (ensure these are selected in this order for every block):
-- 1. InvoiceId: "Payables Invoices - Transactions Real Time"."Invoice Details"."Invoice Number"
-- 2. ActivityName: 'Literal String Value'
-- 3. EventTime: [Specific Date/Time Column]
-- 4. VendorName: "Payables Invoices - Transactions Real Time"."Supplier"."Supplier Name"
-- 5. InvoiceAmount: "Payables Invoices - Transactions Real Time"."Invoice Lines"."Line Amount"
-- 6. User: [Specific User Column]
-- 7. PurchaseOrderNumber: "Payables Invoices - Transactions Real Time"."Matched Purchase Order"."PO Number"
-- 8. DueDate: "Payables Invoices - Transactions Real Time"."Invoice Installments"."Due Date"
-- 9. PaymentDate: "Payables Payments - Disbursements Real Time"."Payment Details"."Payment Date"
-- 10. InvoiceStatus: "Payables Invoices - Transactions Real Time"."Invoice Details"."Invoice Validation Status"
-- 11. EndTime: [Calculated as MAX(EventTime) over InvoiceId later]

-- Block 1: Invoice Created
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Created' AS ActivityName,
  "Invoice Details"."Invoice Creation Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Invoice Creation Date" IS NOT NULL

UNION ALL

-- Block 2: Invoice Validated
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Validated' AS ActivityName,
  "Invoice Details"."Validation Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Validation Date" IS NOT NULL

UNION ALL

-- Block 3: Matching Performed
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Matching Performed' AS ActivityName,
  "Invoice Details"."PO Matched Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."PO Matched Date" IS NOT NULL

UNION ALL

-- Block 4: Hold Placed
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Hold Placed' AS ActivityName,
  "Invoice Holds"."Hold Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Holds"."Hold Date" IS NOT NULL

UNION ALL

-- Block 5: Hold Released
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Hold Released' AS ActivityName,
  "Invoice Holds"."Release Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Holds"."Release Date" IS NOT NULL

UNION ALL

-- Block 6: Approval Initiated
-- Note: This is often the workflow start date, which might be the same as creation.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Approval Initiated' AS ActivityName,
  "Invoice Details"."Workflow Initiated Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Workflow Status" <> 'Not Required'
  AND "Invoice Details"."Workflow Initiated Date" IS NOT NULL

UNION ALL

-- Block 7: Invoice Approved
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Approved' AS ActivityName,
  "Invoice Details"."Workflow Approved Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Workflow Approved Date" IS NOT NULL

UNION ALL

-- Block 8: Invoice Rejected
-- Note: A specific rejection date field may not exist; use the last update date when status is 'Rejected'.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Rejected' AS ActivityName,
  "General Information"."Last Update Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Workflow Status" = 'Rejected'

UNION ALL

-- Block 9: Payment Scheduled
-- Uses the Payment subject area and joins back to Invoice.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Payment Scheduled' AS ActivityName,
  "Payment Process Request"."Request Creation Date" AS EventTime,
  ...
FROM "Payables Payments - Disbursements Real Time"
WHERE "Payment Process Request"."Request Creation Date" IS NOT NULL

UNION ALL

-- Block 10: Payment Executed
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Payment Executed' AS ActivityName,
  "Payment Details"."Payment Date" AS EventTime,
  ...
FROM "Payables Payments - Disbursements Real Time"
WHERE "Payment Details"."Payment Date" IS NOT NULL

UNION ALL

-- Block 11: Early Payment Taken
-- Note: Requires a formula column or filter condition.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Early Payment Taken' AS ActivityName,
  "Payment Details"."Payment Date" AS EventTime,
  ...
FROM "Payables Payments - Disbursements Real Time"
WHERE "Payment Details"."Payment Date" IS NOT NULL
  AND "Invoice Installments"."Discount Date 1" IS NOT NULL
  AND "Payment Details"."Payment Date" <= "Invoice Installments"."Discount Date 1"

UNION ALL

-- Block 12: Payment Cleared
-- Uses the Cash Management subject area.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Payment Cleared' AS ActivityName,
  "Bank Statement Line"."Cleared Date" AS EventTime,
  ...
FROM "Cash Management - Bank Statement Reconciliation Real Time"
WHERE "Bank Statement Line"."Cleared Date" IS NOT NULL

UNION ALL

-- Block 13: Invoice Cancelled
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Cancelled' AS ActivityName,
  "Invoice Details"."Cancellation Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Cancellation Date" IS NOT NULL

-- OTBI Analysis Configuration for Accounts Payable Event Log
-- This is a logical representation of an OTBI UNION report.
-- Each block represents a separate analysis combined with a UNION operation.
-- The column order and data types must be identical in each block.

-- Base Columns (ensure these are selected in this order for every block):
-- 1. InvoiceId: "Payables Invoices - Transactions Real Time"."Invoice Details"."Invoice Number"
-- 2. ActivityName: 'Literal String Value'
-- 3. EventTime: [Specific Date/Time Column]
-- 4. VendorName: "Payables Invoices - Transactions Real Time"."Supplier"."Supplier Name"
-- 5. InvoiceAmount: "Payables Invoices - Transactions Real Time"."Invoice Lines"."Line Amount"
-- 6. User: [Specific User Column]
-- 7. PurchaseOrderNumber: "Payables Invoices - Transactions Real Time"."Matched Purchase Order"."PO Number"
-- 8. DueDate: "Payables Invoices - Transactions Real Time"."Invoice Installments"."Due Date"
-- 9. PaymentDate: "Payables Payments - Disbursements Real Time"."Payment Details"."Payment Date"
-- 10. InvoiceStatus: "Payables Invoices - Transactions Real Time"."Invoice Details"."Invoice Validation Status"
-- 11. EndTime: [Calculated as MAX(EventTime) over InvoiceId later]

-- Block 1: Invoice Created
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Created' AS ActivityName,
  "Invoice Details"."Invoice Creation Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Invoice Creation Date" IS NOT NULL

UNION ALL

-- Block 2: Invoice Validated
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Validated' AS ActivityName,
  "Invoice Details"."Validation Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Validation Date" IS NOT NULL

UNION ALL

-- Block 3: Matching Performed
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Matching Performed' AS ActivityName,
  "Invoice Details"."PO Matched Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."PO Matched Date" IS NOT NULL

UNION ALL

-- Block 4: Hold Placed
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Hold Placed' AS ActivityName,
  "Invoice Holds"."Hold Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Holds"."Hold Date" IS NOT NULL

UNION ALL

-- Block 5: Hold Released
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Hold Released' AS ActivityName,
  "Invoice Holds"."Release Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Holds"."Release Date" IS NOT NULL

UNION ALL

-- Block 6: Approval Initiated
-- Note: This is often the workflow start date, which might be the same as creation.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Approval Initiated' AS ActivityName,
  "Invoice Details"."Workflow Initiated Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Workflow Status" <> 'Not Required'
  AND "Invoice Details"."Workflow Initiated Date" IS NOT NULL

UNION ALL

-- Block 7: Invoice Approved
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Approved' AS ActivityName,
  "Invoice Details"."Workflow Approved Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Workflow Approved Date" IS NOT NULL

UNION ALL

-- Block 8: Invoice Rejected
-- Note: A specific rejection date field may not exist; use the last update date when status is 'Rejected'.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Rejected' AS ActivityName,
  "General Information"."Last Update Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Workflow Status" = 'Rejected'

UNION ALL

-- Block 9: Payment Scheduled
-- Uses the Payment subject area and joins back to Invoice.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Payment Scheduled' AS ActivityName,
  "Payment Process Request"."Request Creation Date" AS EventTime,
  ...
FROM "Payables Payments - Disbursements Real Time"
WHERE "Payment Process Request"."Request Creation Date" IS NOT NULL

UNION ALL

-- Block 10: Payment Executed
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Payment Executed' AS ActivityName,
  "Payment Details"."Payment Date" AS EventTime,
  ...
FROM "Payables Payments - Disbursements Real Time"
WHERE "Payment Details"."Payment Date" IS NOT NULL

UNION ALL

-- Block 11: Early Payment Taken
-- Note: Requires a formula column or filter condition.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Early Payment Taken' AS ActivityName,
  "Payment Details"."Payment Date" AS EventTime,
  ...
FROM "Payables Payments - Disbursements Real Time"
WHERE "Payment Details"."Payment Date" IS NOT NULL
  AND "Invoice Installments"."Discount Date 1" IS NOT NULL
  AND "Payment Details"."Payment Date" <= "Invoice Installments"."Discount Date 1"

UNION ALL

-- Block 12: Payment Cleared
-- Uses the Cash Management subject area.
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Payment Cleared' AS ActivityName,
  "Bank Statement Line"."Cleared Date" AS EventTime,
  ...
FROM "Cash Management - Bank Statement Reconciliation Real Time"
WHERE "Bank Statement Line"."Cleared Date" IS NOT NULL

UNION ALL

-- Block 13: Invoice Cancelled
SELECT
  "Invoice Details"."Invoice Number" AS InvoiceId,
  'Invoice Cancelled' AS ActivityName,
  "Invoice Details"."Cancellation Date" AS EventTime,
  ...
FROM "Payables Invoices - Transactions Real Time"
WHERE "Invoice Details"."Cancellation Date" IS NOT NULL

import requests
import json
import csv
import os
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration Settings --- 
class=class="hl-string">"hl-comment"># Replace with your Oracle Fusion instance details
ORACLE_HOST = class="hl-string">"https://[your-fusion-host].fa.em2.oraclecloud.com"
USERNAME = class="hl-string">"[your_api_user]"
PASSWORD = class="hl-string">"[your_api_password]"

class=class="hl-string">"hl-comment"># --- Extraction Parameters ---
class=class="hl-string">"hl-comment"># Date range for invoice creation date
START_DATE = class="hl-string">"2023-01-01"
END_DATE = class="hl-string">"2023-12-31"

class=class="hl-string">"hl-comment"># API request parameters
LIMIT = 500
API_ENDPOINT = fclass="hl-string">"{ORACLE_HOST}/fscmRestApi/resources/11.13.18.05/invoices"
OUTPUT_FILE = class="hl-string">"oracle_fusion_ap_event_log.csv"

def process_invoice(invoice):
    class="hl-string">"""Processes a single invoice JSON object and returns a list of event dictionaries."""
    events = []
    
    class=class="hl-string">"hl-comment"># Common attributes for all events related to this invoice
    common_attrs = {
        class="hl-string">'InvoiceId': invoice.get(class="hl-string">'InvoiceId'),
        class="hl-string">'VendorName': invoice.get(class="hl-string">'Supplier'),
        class="hl-string">'InvoiceAmount': invoice.get(class="hl-string">'InvoiceAmount'),
        class="hl-string">'PurchaseOrderNumber': invoice.get(class="hl-string">'PoNumber'),
        class="hl-string">'DueDate': invoice.get(class="hl-string">'DueDate'),
        class="hl-string">'InvoiceStatus': invoice.get(class="hl-string">'InvoiceStatus'),
        class="hl-string">'User': None, class=class="hl-string">"hl-comment"># Often not available per event in summary APIs
        class="hl-string">'PaymentDate': None,
        class="hl-string">'EndTime': None
    }

    class=class="hl-string">"hl-comment"># Activity: Invoice Created
    if invoice.get(class="hl-string">'CreationDate'):
        events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Invoice Created',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'CreationDate'),
            class="hl-string">'User': invoice.get(class="hl-string">'CreatedBy')
        })

    class=class="hl-string">"hl-comment"># Activity: Invoice Validated
    if invoice.get(class="hl-string">'ValidatedDate'):
        events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Invoice Validated',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'ValidatedDate')
        })

    class=class="hl-string">"hl-comment"># Activity: Matching Performed (using ValidationDate as a proxy if no specific match date is available)
    if invoice.get(class="hl-string">'MatchStatus') in [class="hl-string">'Success', class="hl-string">'Partial'] and invoice.get(class="hl-string">'ValidatedDate'):
        events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Matching Performed',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'ValidatedDate') class=class="hl-string">"hl-comment"># Proxy timestamp
        })

    class=class="hl-string">"hl-comment"># Activities from Holds
    if invoice.get(class="hl-string">'holds'):
        for hold in invoice[class="hl-string">'holds']:
            if hold.get(class="hl-string">'HoldDate'):
                events.append({
                    **common_attrs,
                    class="hl-string">'ActivityName': class="hl-string">'Hold Placed',
                    class="hl-string">'EventTime': hold.get(class="hl-string">'HoldDate'),
                    class="hl-string">'User': hold.get(class="hl-string">'HeldBy')
                })
            if hold.get(class="hl-string">'ReleaseDate'):
                events.append({
                    **common_attrs,
                    class="hl-string">'ActivityName': class="hl-string">'Hold Released',
                    class="hl-string">'EventTime': hold.get(class="hl-string">'ReleaseDate'),
                    class="hl-string">'User': hold.get(class="hl-string">'ReleasedBy')
                })

    class=class="hl-string">"hl-comment"># Activities from Approval Status
    if invoice.get(class="hl-string">'ApprovalStatus') == class="hl-string">'Initiated' and invoice.get(class="hl-string">'LastUpdateDate'):
         events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Approval Initiated',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'LastUpdateDate') class=class="hl-string">"hl-comment"># Proxy timestamp
        })
    if invoice.get(class="hl-string">'ApprovalStatus') == class="hl-string">'Approved' and invoice.get(class="hl-string">'ApprovedDate'):
         events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Invoice Approved',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'ApprovedDate')
        })
    if invoice.get(class="hl-string">'ApprovalStatus') == class="hl-string">'Rejected' and invoice.get(class="hl-string">'LastUpdateDate'):
         events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Invoice Rejected',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'LastUpdateDate') class=class="hl-string">"hl-comment"># Proxy timestamp
        })

    class=class="hl-string">"hl-comment"># Activities from Payment Schedules and Payments
    final_payment_date = None
    if invoice.get(class="hl-string">'paymentSchedules'):
        for schedule in invoice[class="hl-string">'paymentSchedules']:
            class=class="hl-string">"hl-comment"># Activity: Payment Scheduled
            events.append({
                **common_attrs,
                class="hl-string">'ActivityName': class="hl-string">'Payment Scheduled',
                class="hl-string">'EventTime': schedule.get(class="hl-string">'CreationDate') or invoice.get(class="hl-string">'ApprovedDate') class=class="hl-string">"hl-comment"># Use schedule creation or invoice approval date
            })

            if schedule.get(class="hl-string">'payments'):
                for payment in schedule[class="hl-string">'payments']:
                    if payment.get(class="hl-string">'PaymentDate'):
                        final_payment_date = payment.get(class="hl-string">'PaymentDate')
                        class=class="hl-string">"hl-comment"># Activity: Payment Executed
                        events.append({
                            **common_attrs,
                            class="hl-string">'ActivityName': class="hl-string">'Payment Executed',
                            class="hl-string">'EventTime': payment.get(class="hl-string">'PaymentDate'),
                            class="hl-string">'PaymentDate': payment.get(class="hl-string">'PaymentDate')
                        })

                        class=class="hl-string">"hl-comment"># Activity: Early Payment Taken (Calculated)
                        discount_date = schedule.get(class="hl-string">'firstDiscountDate')
                        if discount_date and payment.get(class="hl-string">'PaymentDate') <= discount_date:
                            events.append({
                                **common_attrs,
                                class="hl-string">'ActivityName': class="hl-string">'Early Payment Taken',
                                class="hl-string">'EventTime': payment.get(class="hl-string">'PaymentDate')
                            })

                    class=class="hl-string">"hl-comment"># Activity: Payment Cleared
                    if payment.get(class="hl-string">'clearedDate'):
                        events.append({
                            **common_attrs,
                            class="hl-string">'ActivityName': class="hl-string">'Payment Cleared',
                            class="hl-string">'EventTime': payment.get(class="hl-string">'clearedDate')
                        })

    class=class="hl-string">"hl-comment"># Activity: Invoice Cancelled
    if invoice.get(class="hl-string">'CancelledDate'):
        events.append({
            **common_attrs,
            class="hl-string">'ActivityName': class="hl-string">'Invoice Cancelled',
            class="hl-string">'EventTime': invoice.get(class="hl-string">'CancelledDate'),
            class="hl-string">'User': invoice.get(class="hl-string">'CancelledBy')
        })

    class=class="hl-string">"hl-comment"># Calculate EndTime as the timestamp of the last event
    if events:
        end_time = max(e[class="hl-string">'EventTime'] for e in events if e.get(class="hl-string">'EventTime'))
        for e in events:
            e[class="hl-string">'EndTime'] = end_time
            if not e.get(class="hl-string">'PaymentDate'): class=class="hl-string">"hl-comment"># Backfill payment date if available
                e[class="hl-string">'PaymentDate'] = final_payment_date

    return events

def main():
    class="hl-string">"""Main function to fetch data and write to CSV."""
    all_events = []
    offset = 0
    has_more = True

    class=class="hl-string">"hl-comment"># Prepare headers for the API request
    headers = {
        class="hl-string">'Content-Type': class="hl-string">'application/vnd.oracle.adf.resourceitem+json'
    }
    auth = (USERNAME, PASSWORD)

    class=class="hl-string">"hl-comment"># Define fields to retrieve for efficiency
    fields_to_get = [
        class="hl-string">'InvoiceId', class="hl-string">'InvoiceNumber', class="hl-string">'InvoiceAmount', class="hl-string">'CreationDate', class="hl-string">'CreatedBy',
        class="hl-string">'Supplier', class="hl-string">'PoNumber', class="hl-string">'DueDate', class="hl-string">'InvoiceStatus', class="hl-string">'ValidatedDate',
        class="hl-string">'MatchStatus', class="hl-string">'ApprovalStatus', class="hl-string">'ApprovedDate', class="hl-string">'LastUpdateDate',
        class="hl-string">'CancelledDate', class="hl-string">'CancelledBy',
        class="hl-string">'holds(HoldDate,HeldBy,ReleaseDate,ReleasedBy)',
        class="hl-string">'paymentSchedules(CreationDate,firstDiscountDate,payments(PaymentDate,clearedDate))'
    ]

    print(class="hl-string">"Starting Oracle Fusion AP Invoice data extraction...")

    while has_more:
        params = {
            class="hl-string">'q': fclass="hl-string">"CreationDate between '{START_DATE}' and '{END_DATE}'",
            class="hl-string">'onlyData': class="hl-string">'true',
            class="hl-string">'limit': LIMIT,
            class="hl-string">'offset': offset,
            class="hl-string">'fields': class="hl-string">','.join(fields_to_get),
            class="hl-string">'expand': class="hl-string">'holds,paymentSchedules.payments'
        }

        try:
            print(fclass="hl-string">"Fetching records from offset {offset}...")
            response = requests.get(API_ENDPOINT, auth=auth, headers=headers, params=params, timeout=120)
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raise an exception for bad status codes

            data = response.json()
            invoices = data.get(class="hl-string">'items', [])

            if not invoices:
                print(class="hl-string">"No more invoices found.")
                break

            for invoice in invoices:
                all_events.extend(process_invoice(invoice))
            
            has_more = data.get(class="hl-string">'hasMore', False)
            offset += len(invoices)

        except requests.exceptions.HTTPError as http_err:
            print(fclass="hl-string">"HTTP error occurred: {http_err} - {response.text}")
            break
        except requests.exceptions.RequestException as req_err:
            print(fclass="hl-string">"Request error occurred: {req_err}")
            break
        except json.JSONDecodeError:
            print(fclass="hl-string">"Failed to decode JSON from response: {response.text}")
            break

    if not all_events:
        print(class="hl-string">"No events were generated. The output file will not be created.")
        return

    class=class="hl-string">"hl-comment"># Write to CSV
    print(fclass="hl-string">"\nWriting {len(all_events)} events to {OUTPUT_FILE}...")
    fieldnames = [
        class="hl-string">'InvoiceId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'VendorName', class="hl-string">'InvoiceAmount',
        class="hl-string">'User', class="hl-string">'PurchaseOrderNumber', class="hl-string">'DueDate', class="hl-string">'PaymentDate', class="hl-string">'InvoiceStatus', class="hl-string">'EndTime'
    ]
    
    try:
        with open(OUTPUT_FILE, class="hl-string">'w', newline=class="hl-string">'', encoding=class="hl-string">'utf-8') as csvfile:
            writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
            writer.writeheader()
            writer.writerows(all_events)
        print(class="hl-string">"Extraction complete.")
    except IOError as io_err:
        print(fclass="hl-string">"Error writing to file: {io_err}")

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

WITH BaseInvoices AS (
    -- Select base invoice data and apply primary filters here
    SELECT 
        AIA.INVOICE_ID,
        AIA.INVOICE_NUM,
        AIA.INVOICE_AMOUNT,
        AIA.CREATION_DATE AS INVOICE_CREATION_DATE,
        AIA.CANCELLED_DATE,
        AIA.WFAPPROVAL_STATUS AS INVOICE_STATUS,
        AIA.LAST_UPDATE_DATE AS INVOICE_LAST_UPDATE_DATE,
        AIA.LAST_UPDATED_BY AS INVOICE_LAST_UPDATED_BY,
        AIA.CREATED_BY AS INVOICE_CREATED_BY,
        PSV.VENDOR_NAME,
        (SELECT LISTAGG(PHA.SEGMENT1, ', ') WITHIN GROUP (ORDER BY PHA.PO_HEADER_ID) 
         FROM AP_INVOICE_LINES_ALL AILA 
         JOIN PO_HEADERS_ALL PHA ON AILA.PO_HEADER_ID = PHA.PO_HEADER_ID
         WHERE AILA.INVOICE_ID = AIA.INVOICE_ID) AS PurchaseOrderNumber
    FROM 
        AP_INVOICES_ALL AIA
    JOIN 
        POZ_SUPPLIERS_V PSV ON AIA.VENDOR_ID = PSV.VENDOR_ID
    JOIN
        FUN_ALL_BUSINESS_UNITS_V BU ON AIA.ORG_ID = BU.BU_ID
    WHERE 
        AIA.CREATION_DATE BETWEEN TO_TIMESTAMP(:P_START_DATE, 'YYYY-MM-DD') AND TO_TIMESTAMP(:P_END_DATE, 'YYYY-MM-DD HH24:MI:SS')
        -- Add additional filters here if needed, e.g.:
        -- AND BU.BU_NAME = '[Your Business Unit Name]'
),
EventLog_Pre AS (
    -- 1. Invoice Created
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Invoice Created' AS "ActivityName",
        BI.INVOICE_CREATION_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN PER_USERS PU ON BI.INVOICE_CREATED_BY = PU.USER_GUID

    UNION ALL

    -- 2. Invoice Validated
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Invoice Validated' AS "ActivityName",
        APH.CREATION_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_APPROVAL_HISTORY_ALL APH ON BI.INVOICE_ID = APH.INVOICE_ID
    JOIN PER_USERS PU ON APH.CREATED_BY = PU.USERNAME
    WHERE APH.RESPONSE = 'VALIDATED'

    UNION ALL

    -- 3. Matching Performed
    -- This uses the last update date when match status becomes 'A' (Tested)
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Matching Performed' AS "ActivityName",
        AID.LAST_UPDATE_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_INVOICE_DISTRIBUTIONS_ALL AID ON BI.INVOICE_ID = AID.INVOICE_ID
    JOIN PER_USERS PU ON AID.LAST_UPDATED_BY = PU.USER_GUID
    WHERE AID.MATCH_STATUS_FLAG = 'A'
    AND ROWNUM = 1 -- To get one event per invoice if multiple lines are matched at different times

    UNION ALL

    -- 4. Hold Placed
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Hold Placed' AS "ActivityName",
        APH.HOLD_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_HOLDS_ALL APH ON BI.INVOICE_ID = APH.INVOICE_ID
    JOIN PER_USERS PU ON APH.CREATED_BY = PU.USERNAME

    UNION ALL

    -- 5. Hold Released
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Hold Released' AS "ActivityName",
        APH.LAST_UPDATE_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_HOLDS_ALL APH ON BI.INVOICE_ID = APH.INVOICE_ID
    JOIN PER_USERS PU ON APH.LAST_UPDATED_BY = PU.USER_GUID
    WHERE APH.RELEASE_LOOKUP_CODE IS NOT NULL

    UNION ALL

    -- 6. Approval Initiated
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Approval Initiated' AS "ActivityName",
        APH.CREATION_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_APPROVAL_HISTORY_ALL APH ON BI.INVOICE_ID = APH.INVOICE_ID
    JOIN PER_USERS PU ON APH.CREATED_BY = PU.USERNAME
    WHERE APH.RESPONSE = 'SUBMITTED'

    UNION ALL

    -- 7. Invoice Approved
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Invoice Approved' AS "ActivityName",
        APH.CREATION_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_APPROVAL_HISTORY_ALL APH ON BI.INVOICE_ID = APH.INVOICE_ID
    JOIN PER_USERS PU ON APH.CREATED_BY = PU.USERNAME
    WHERE APH.RESPONSE = 'APPROVED'

    UNION ALL

    -- 8. Invoice Rejected
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Invoice Rejected' AS "ActivityName",
        APH.CREATION_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_APPROVAL_HISTORY_ALL APH ON BI.INVOICE_ID = APH.INVOICE_ID
    JOIN PER_USERS PU ON APH.CREATED_BY = PU.USERNAME
    WHERE APH.RESPONSE = 'REJECTED'

    UNION ALL

    -- 9. Payment Scheduled
    SELECT
        BI.INVOICE_ID AS "InvoiceId",
        'Payment Scheduled' AS "ActivityName",
        APS.LAST_UPDATE_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_PAYMENT_SCHEDULES_ALL APS ON BI.INVOICE_ID = APS.INVOICE_ID
    JOIN PER_USERS PU ON APS.LAST_UPDATED_BY = PU.USER_GUID
    WHERE APS.CHECKRUN_ID IS NOT NULL

    UNION ALL

    -- 10. Payment Executed
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Payment Executed' AS "ActivityName",
        AC.CHECK_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_INVOICE_PAYMENTS_ALL AIP ON BI.INVOICE_ID = AIP.INVOICE_ID
    JOIN AP_CHECKS_ALL AC ON AIP.CHECK_ID = AC.CHECK_ID
    JOIN PER_USERS PU ON AC.LAST_UPDATED_BY = PU.USER_GUID

    UNION ALL

    -- 11. Early Payment Taken
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Early Payment Taken' AS "ActivityName",
        AC.CHECK_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_INVOICE_PAYMENTS_ALL AIP ON BI.INVOICE_ID = AIP.INVOICE_ID
    JOIN AP_CHECKS_ALL AC ON AIP.CHECK_ID = AC.CHECK_ID
    JOIN AP_PAYMENT_SCHEDULES_ALL APS ON AIP.INVOICE_ID = APS.INVOICE_ID AND AIP.PAYMENT_NUM = APS.PAYMENT_NUM
    JOIN PER_USERS PU ON AC.LAST_UPDATED_BY = PU.USER_GUID
    WHERE AC.CHECK_DATE <= APS.DISCOUNT_DATE

    UNION ALL

    -- 12. Payment Cleared
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Payment Cleared' AS "ActivityName",
        AC.CLEARED_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN AP_INVOICE_PAYMENTS_ALL AIP ON BI.INVOICE_ID = AIP.INVOICE_ID
    JOIN AP_CHECKS_ALL AC ON AIP.CHECK_ID = AC.CHECK_ID
    JOIN PER_USERS PU ON AC.LAST_UPDATED_BY = PU.USER_GUID
    WHERE AC.CLEARED_DATE IS NOT NULL

    UNION ALL

    -- 13. Invoice Cancelled
    SELECT 
        BI.INVOICE_ID AS "InvoiceId",
        'Invoice Cancelled' AS "ActivityName",
        BI.CANCELLED_DATE AS "EventTime",
        BI.VENDOR_NAME AS "VendorName",
        BI.INVOICE_AMOUNT AS "InvoiceAmount",
        PU.USERNAME AS "User",
        BI.PurchaseOrderNumber AS "PurchaseOrderNumber"
    FROM BaseInvoices BI
    JOIN PER_USERS PU ON BI.INVOICE_LAST_UPDATED_BY = PU.USER_GUID
    WHERE BI.CANCELLED_DATE IS NOT NULL
),
FinalLog AS (
    SELECT 
        E."InvoiceId",
        E."ActivityName",
        E."EventTime",
        E."VendorName",
        E."InvoiceAmount",
        E."User",
        E."PurchaseOrderNumber",
        APS.DUE_DATE AS "DueDate",
        AC.CHECK_DATE AS "PaymentDate",
        AIA.WFAPPROVAL_STATUS AS "InvoiceStatus"
    FROM EventLog_Pre E
    LEFT JOIN AP_INVOICES_ALL AIA ON E."InvoiceId" = AIA.INVOICE_ID
    LEFT JOIN AP_PAYMENT_SCHEDULES_ALL APS ON E."InvoiceId" = APS.INVOICE_ID AND APS.PAYMENT_NUM = 1 -- Assuming first payment schedule for due date
    LEFT JOIN AP_INVOICE_PAYMENTS_ALL AIP ON E."InvoiceId" = AIP.INVOICE_ID AND ROWNUM = 1 -- To get one payment record
    LEFT JOIN AP_CHECKS_ALL AC ON AIP.CHECK_ID = AC.CHECK_ID
)
SELECT 
    F."InvoiceId",
    F."ActivityName",
    F."EventTime",
    F."VendorName",
    F."InvoiceAmount",
    F."User",
    F."PurchaseOrderNumber",
    F."DueDate",
    F."PaymentDate",
    F."InvoiceStatus",
    MAX(F."EventTime") OVER (PARTITION BY F."InvoiceId") AS "EndTime"
FROM FinalLog F
WHERE F."EventTime" IS NOT NULL
ORDER BY F."InvoiceId", F."EventTime";