Il template dei dati per l’elaborazione delle fatture fornitori

-- 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

WITH
invoice_created AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Invoice Created' AS ActivityName,
        CAST([Invoice creation timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Creation user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST(NULL AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Invoice header PVO]
    WHERE CAST([Invoice creation timestamp attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Invoice creation timestamp attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
      AND [Company Code attribute] IN ([Company-specific filter])
),
invoice_validated AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Invoice Validated' AS ActivityName,
        CAST([Validation status timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Validation user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST(NULL AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Invoice validation history PVO]
    WHERE [Validation status attribute] = '[Validated status value]'
      AND CAST([Validation status timestamp attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Validation status timestamp attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
),
matching_performed AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Matching Performed' AS ActivityName,
        CAST([Matching status timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Matching user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST(NULL AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Invoice matching history PVO]
    WHERE [Matching status attribute] IN ('[Matched status value]', '[Partially matched status value]')
      AND CAST([Matching status timestamp attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Matching status timestamp attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
),
hold_placed AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Hold Placed' AS ActivityName,
        CAST([Hold creation timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Hold creation user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST(NULL AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Invoice hold PVO]
    WHERE [Hold creation timestamp attribute] IS NOT NULL
      AND CAST([Hold creation timestamp attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Hold creation timestamp attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
),
hold_released AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Hold Released' AS ActivityName,
        CAST([Hold release timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Hold release user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST(NULL AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Invoice hold PVO]
    WHERE [Hold release timestamp attribute] IS NOT NULL
      AND CAST([Hold release timestamp attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Hold release timestamp attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
),
approval_initiated AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Approval Initiated' AS ActivityName,
        CAST([Workflow status timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Workflow user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST(NULL AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Invoice workflow history PVO]
    WHERE [Workflow status attribute] = '[Approval initiated status value]'
      AND CAST([Workflow status timestamp attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Workflow status timestamp attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
),
invoice_approved AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Invoice Approved' AS ActivityName,
        CAST([Workflow status timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Workflow user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST(NULL AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Invoice workflow history PVO]
    WHERE [Workflow status attribute] = '[Approved status value]'
      AND CAST([Workflow status timestamp attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Workflow status timestamp attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
),
invoice_rejected AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Invoice Rejected' AS ActivityName,
        CAST([Workflow status timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Workflow user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST(NULL AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Invoice workflow history PVO]
    WHERE [Workflow status attribute] = '[Rejected status value]'
      AND CAST([Workflow status timestamp attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Workflow status timestamp attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
),
payment_scheduled AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Payment Scheduled' AS ActivityName,
        CAST([Payment schedule finalized timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Payment scheduling user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST([Payment date attribute] AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Payment schedule PVO]
    WHERE [Payment schedule finalized timestamp attribute] IS NOT NULL
      AND CAST([Payment schedule finalized timestamp attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Payment schedule finalized timestamp attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
),
payment_executed AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Payment Executed' AS ActivityName,
        CAST([Payment creation or issuance timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Payment user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST([Payment date attribute] AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Payment PVO]
    WHERE [Payment creation or issuance timestamp attribute] IS NOT NULL
      AND CAST([Payment creation or issuance timestamp attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Payment creation or issuance timestamp attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
),
early_payment_taken AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Early Payment Taken' AS ActivityName,
        CAST([Payment date attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Payment user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST([Payment date attribute] AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Payment PVO]
    WHERE [Payment date attribute] IS NOT NULL
      AND [Discount taken indicator attribute] = '[Discount taken value]'
      AND CAST([Payment date attribute] AS TIMESTAMP) < CAST([Discount deadline attribute] AS TIMESTAMP)
      AND CAST([Payment date attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Payment date attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
),
payment_cleared AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Payment Cleared' AS ActivityName,
        CAST([Bank clearing or reconciliation timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Reconciliation user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST([Payment date attribute] AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Payment clearing or reconciliation PVO]
    WHERE [Bank clearing or reconciliation timestamp attribute] IS NOT NULL
      AND CAST([Bank clearing or reconciliation timestamp attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Bank clearing or reconciliation timestamp attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
),
invoice_cancelled AS (
    SELECT
        CAST([Invoice identifier attribute] AS VARCHAR(200)) AS InvoiceId,
        'Invoice Cancelled' AS ActivityName,
        CAST([Cancellation timestamp attribute] AS TIMESTAMP) AS EventTime,
        CAST([Vendor name attribute] AS VARCHAR(500)) AS VendorName,
        CAST([Invoice amount attribute] AS DECIMAL(38, 10)) AS InvoiceAmount,
        CAST([Cancellation user attribute] AS VARCHAR(500)) AS "User",
        CAST([Purchase order number attribute] AS VARCHAR(200)) AS PurchaseOrderNumber,
        CAST([Due date attribute] AS DATE) AS DueDate,
        CAST(NULL AS TIMESTAMP) AS PaymentDate,
        CAST([Invoice status attribute] AS VARCHAR(200)) AS InvoiceStatus,
        CAST(NULL AS TIMESTAMP) AS EndTime
    FROM [Invoice header PVO]
    WHERE [Cancellation timestamp attribute] IS NOT NULL
      AND CAST([Cancellation timestamp attribute] AS TIMESTAMP) >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND CAST([Cancellation timestamp attribute] AS TIMESTAMP) < CAST('[End date parameter]' AS TIMESTAMP)
),
all_events AS (
    SELECT * FROM invoice_created
    UNION ALL SELECT * FROM invoice_validated
    UNION ALL SELECT * FROM matching_performed
    UNION ALL SELECT * FROM hold_placed
    UNION ALL SELECT * FROM hold_released
    UNION ALL SELECT * FROM approval_initiated
    UNION ALL SELECT * FROM invoice_approved
    UNION ALL SELECT * FROM invoice_rejected
    UNION ALL SELECT * FROM payment_scheduled
    UNION ALL SELECT * FROM payment_executed
    UNION ALL SELECT * FROM early_payment_taken
    UNION ALL SELECT * FROM payment_cleared
    UNION ALL SELECT * FROM invoice_cancelled
),
final_events AS (
    SELECT
        InvoiceId,
        ActivityName,
        EventTime,
        VendorName,
        InvoiceAmount,
        "User",
        PurchaseOrderNumber,
        DueDate,
        PaymentDate,
        InvoiceStatus,
        MAX(EventTime) OVER (PARTITION BY InvoiceId) AS EndTime,
        ROW_NUMBER() OVER (
            PARTITION BY InvoiceId, ActivityName, EventTime
            ORDER BY EventTime
        ) AS duplicate_rank
    FROM all_events
    WHERE InvoiceId IS NOT NULL
      AND EventTime IS NOT NULL
)
SELECT
    InvoiceId,
    ActivityName,
    EventTime,
    VendorName,
    InvoiceAmount,
    "User",
    PurchaseOrderNumber,
    DueDate,
    PaymentDate,
    InvoiceStatus,
    EndTime
FROM final_events
WHERE duplicate_rank = 1
ORDER BY InvoiceId, EventTime, ActivityName;

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";