Datatemplate voor Order-to-Cash: facturatie en factuurverwerking

WITH base_invoices AS (
  SELECT
    rct.customer_trx_id,
    rct.trx_number,
    rct.trx_date,
    rct.creation_date,
    rct.created_by,
    rct.last_update_date,
    rct.last_updated_by,
    rct.bill_to_customer_id,
    rct.org_id,
    ps.amount_due_original,
    ps.due_date,
    ps.status AS payment_schedule_status,
    ps.class,
    ps.payment_schedule_id
  FROM
    ar.ra_customer_trx_all rct,
    ar.ar_payment_schedules_all ps
  WHERE
    rct.customer_trx_id = ps.customer_trx_id
    AND rct.trx_date BETWEEN TO_DATE(:p_start_date, 'YYYY-MM-DD') AND TO_DATE(:p_end_date, 'YYYY-MM-DD')
    AND rct.org_id = :p_org_id
    AND ps.class = 'INV'
    AND rct.complete_flag = 'Y'
)
-- 1. Invoice Generated
SELECT
  inv.trx_number AS "InvoiceNumber",
  'Invoice Generated' AS "ActivityName",
  inv.creation_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  fu.user_name AS "User",
  papf.full_name AS "Department", -- Placeholder for department logic
  'OPEN' AS "InvoiceStatus"
FROM
  base_invoices inv
  JOIN applsys.fnd_user fu ON inv.created_by = fu.user_id
  LEFT JOIN applsys.per_all_people_f papf ON fu.employee_id = papf.person_id AND inv.creation_date BETWEEN papf.effective_start_date AND papf.effective_end_date
UNION ALL
-- 2. Invoice Approved (using completion as proxy)
SELECT
  inv.trx_number AS "InvoiceNumber",
  'Invoice Approved' AS "ActivityName",
  rct.last_update_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  fu.user_name AS "User",
  papf.full_name AS "Department",
  'OPEN' AS "InvoiceStatus"
FROM
  base_invoices inv
  JOIN ar.ra_customer_trx_all rct ON inv.customer_trx_id = rct.customer_trx_id
  JOIN applsys.fnd_user fu ON rct.last_updated_by = fu.user_id
  LEFT JOIN applsys.per_all_people_f papf ON fu.employee_id = papf.person_id AND rct.last_update_date BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
  rct.complete_flag = 'Y' 
  AND rct.initial_customer_trx_id IS NULL -- Only for manually entered or imported invoices, not copies
UNION ALL
-- 3. Invoice Sent To Customer (using first print date)
SELECT
  inv.trx_number AS "InvoiceNumber",
  'Invoice Sent To Customer' AS "ActivityName",
  rct.printing_original_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  fu.user_name AS "User",
  papf.full_name AS "Department",
  'OPEN' AS "InvoiceStatus"
FROM
  base_invoices inv
  JOIN ar.ra_customer_trx_all rct ON inv.customer_trx_id = rct.customer_trx_id
  JOIN applsys.fnd_user fu ON rct.last_updated_by = fu.user_id
  LEFT JOIN applsys.per_all_people_f papf ON fu.employee_id = papf.person_id AND rct.printing_original_date BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
  rct.printing_original_date IS NOT NULL
UNION ALL
-- 4. Invoice Modified
SELECT
  inv.trx_number AS "InvoiceNumber",
  'Invoice Modified' AS "ActivityName",
  rct.last_update_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  fu.user_name AS "User",
  papf.full_name AS "Department",
  'INCOMPLETE' AS "InvoiceStatus"
FROM
  base_invoices inv
  JOIN ar.ra_customer_trx_all rct ON inv.customer_trx_id = rct.customer_trx_id
  JOIN applsys.fnd_user fu ON rct.last_updated_by = fu.user_id
  LEFT JOIN applsys.per_all_people_f papf ON fu.employee_id = papf.person_id AND rct.last_update_date BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
  rct.last_update_date > rct.creation_date
  AND rct.complete_flag = 'N' -- Capturing modifications while incomplete
UNION ALL
-- 5. Credit Memo Created
SELECT
  inv.trx_number AS "InvoiceNumber",
  'Credit Memo Created' AS "ActivityName",
  cm.creation_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  fu.user_name AS "User",
  papf.full_name AS "Department",
  inv.payment_schedule_status AS "InvoiceStatus"
FROM
  base_invoices inv
  JOIN ar.ra_customer_trx_all cm ON inv.customer_trx_id = cm.previous_customer_trx_id
  JOIN applsys.fnd_user fu ON cm.created_by = fu.user_id
  LEFT JOIN applsys.per_all_people_f papf ON fu.employee_id = papf.person_id AND cm.creation_date BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
  cm.trx_class = 'CM'
UNION ALL
-- 6. Dispute Raised
SELECT
  inv.trx_number AS "InvoiceNumber",
  'Dispute Raised' AS "ActivityName",
  adj.creation_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  fu.user_name AS "User",
  papf.full_name AS "Department",
  'DISPUTED' AS "InvoiceStatus"
FROM
  base_invoices inv
  JOIN ar.ar_adjustments_all adj ON inv.customer_trx_id = adj.customer_trx_id
  JOIN applsys.fnd_user fu ON adj.created_by = fu.user_id
  LEFT JOIN applsys.per_all_people_f papf ON fu.employee_id = papf.person_id AND adj.creation_date BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
  adj.type = 'INVOICE' AND adj.status = 'A' -- Approved adjustments related to disputes
UNION ALL
-- 7. Payment Due Date Reached
SELECT
  inv.trx_number AS "InvoiceNumber",
  'Payment Due Date Reached' AS "ActivityName",
  inv.due_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  'System' AS "User",
  NULL AS "Department",
  inv.payment_schedule_status AS "InvoiceStatus"
FROM
  base_invoices inv
WHERE
  inv.due_date < SYSDATE
UNION ALL
-- 8. Payment Reminder Issued
SELECT
  inv.trx_number AS "InvoiceNumber",
  'Payment Reminder Issued' AS "ActivityName",
  dunning.creation_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  fu.user_name AS "User",
  NULL AS "Department",
  inv.payment_schedule_status AS "InvoiceStatus"
FROM
  base_invoices inv
  JOIN iex.iex_dunnings dunning ON inv.payment_schedule_id = dunning.payment_schedule_id
  JOIN applsys.fnd_user fu ON dunning.created_by = fu.user_id
UNION ALL
-- 9. Customer Payment Received
SELECT DISTINCT
  inv.trx_number AS "InvoiceNumber",
  'Customer Payment Received' AS "ActivityName",
  cr.receipt_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  fu.user_name AS "User",
  papf.full_name AS "Department",
  inv.payment_schedule_status AS "InvoiceStatus"
FROM
  base_invoices inv
  JOIN ar.ar_receivable_applications_all app ON inv.customer_trx_id = app.applied_customer_trx_id
  JOIN ar.ar_cash_receipts_all cr ON app.cash_receipt_id = cr.cash_receipt_id
  JOIN applsys.fnd_user fu ON cr.created_by = fu.user_id
  LEFT JOIN applsys.per_all_people_f papf ON fu.employee_id = papf.person_id AND cr.receipt_date BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE app.status = 'APP'
UNION ALL
-- 10. Payment Reversed
SELECT DISTINCT
  inv.trx_number AS "InvoiceNumber",
  'Payment Reversed' AS "ActivityName",
  crh.creation_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  fu.user_name AS "User",
  papf.full_name AS "Department",
  'OPEN' AS "InvoiceStatus"
FROM
  base_invoices inv
  JOIN ar.ar_receivable_applications_all app ON inv.customer_trx_id = app.applied_customer_trx_id
  JOIN ar.ar_cash_receipt_history_all crh ON app.cash_receipt_id = crh.cash_receipt_id
  JOIN applsys.fnd_user fu ON crh.created_by = fu.user_id
  LEFT JOIN applsys.per_all_people_f papf ON fu.employee_id = papf.person_id AND crh.creation_date BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
  crh.status = 'REVERSED'
UNION ALL
-- 11. Cash Applied/Reconciled
SELECT
  inv.trx_number AS "InvoiceNumber",
  'Cash Applied/Reconciled' AS "ActivityName",
  app.apply_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  fu.user_name AS "User",
  papf.full_name AS "Department",
  inv.payment_schedule_status AS "InvoiceStatus"
FROM
  base_invoices inv
  JOIN ar.ar_receivable_applications_all app ON inv.customer_trx_id = app.applied_customer_trx_id
  JOIN applsys.fnd_user fu ON app.created_by = fu.user_id
  LEFT JOIN applsys.per_all_people_f papf ON fu.employee_id = papf.person_id AND app.apply_date BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
  app.status = 'APP'
  AND app.display = 'Y'
UNION ALL
-- 12. Write-Off Created
SELECT
  inv.trx_number AS "InvoiceNumber",
  'Write-Off Created' AS "ActivityName",
  adj.creation_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  fu.user_name AS "User",
  papf.full_name AS "Department",
  inv.payment_schedule_status AS "InvoiceStatus"
FROM
  base_invoices inv
  JOIN ar.ar_adjustments_all adj ON inv.customer_trx_id = adj.customer_trx_id
  JOIN ar.ar_receivables_trx_all rt ON adj.receivables_trx_id = rt.receivables_trx_id
  JOIN applsys.fnd_user fu ON adj.created_by = fu.user_id
  LEFT JOIN applsys.per_all_people_f papf ON fu.employee_id = papf.person_id AND adj.creation_date BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
  rt.name LIKE '%Write-Off%'
UNION ALL
-- 13. Invoice Closed
SELECT
  inv.trx_number AS "InvoiceNumber",
  'Invoice Closed' AS "ActivityName",
  ps_closed.last_update_date AS "EventTime",
  inv.amount_due_original AS "TotalInvoiceAmount",
  inv.due_date AS "DueDate",
  inv.bill_to_customer_id AS "CustomerId",
  fu.user_name AS "User",
  papf.full_name AS "Department",
  'CLOSED' AS "InvoiceStatus"
FROM
  base_invoices inv
  JOIN ar.ar_payment_schedules_all ps_closed ON inv.payment_schedule_id = ps_closed.payment_schedule_id
  JOIN applsys.fnd_user fu ON ps_closed.last_updated_by = fu.user_id
  LEFT JOIN applsys.per_all_people_f papf ON fu.employee_id = papf.person_id AND ps_closed.last_update_date BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
  ps_closed.status = 'CL'

WITH
invoice_base AS (
    SELECT
        i.[invoice_number_column] AS invoice_number,
        i.[invoice_id_column] AS invoice_id,
        i.[invoice_date_column] AS invoice_date,
        i.[creation_timestamp_column] AS creation_timestamp,
        i.[last_update_timestamp_column] AS last_update_timestamp,
        i.[total_amount_column] AS total_invoice_amount,
        i.[due_date_column] AS due_date,
        i.[customer_id_column] AS customer_id,
        i.[invoice_status_column] AS invoice_status,
        i.[company_scope_column] AS company_scope
    FROM [Your Receivables invoice table] i
    WHERE i.[creation_timestamp_column] >= :start_date
      AND i.[creation_timestamp_column] < :end_date
      AND i.[company_scope_column] = :company_filter
),
event_log AS (
    SELECT
        b.invoice_number AS InvoiceNumber,
        'Invoice Generated' AS ActivityName,
        b.creation_timestamp AS EventTime,
        b.total_invoice_amount AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        CAST(NULL AS VARCHAR2(255)) AS User,
        CAST(NULL AS VARCHAR2(255)) AS Department,
        b.invoice_status AS InvoiceStatus
    FROM invoice_base b

    UNION ALL

    SELECT
        b.invoice_number AS InvoiceNumber,
        'Invoice Approved' AS ActivityName,
        a.[approval_event_timestamp_column] AS EventTime,
        b.total_invoice_amount AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        a.[approval_user_column] AS User,
        a.[approval_department_column] AS Department,
        b.invoice_status AS InvoiceStatus
    FROM invoice_base b
    INNER JOIN [Your invoice approval or workflow history table] a
        ON a.[invoice_id_column] = b.invoice_id
    WHERE a.[approval_status_column] = '[Your approved status value]'
      AND a.[approval_event_timestamp_column] >= :start_date
      AND a.[approval_event_timestamp_column] < :end_date

    UNION ALL

    SELECT
        b.invoice_number AS InvoiceNumber,
        'Invoice Sent To Customer' AS ActivityName,
        s.[transmission_timestamp_column] AS EventTime,
        b.total_invoice_amount AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        s.[transmission_user_column] AS User,
        s.[transmission_department_column] AS Department,
        b.invoice_status AS InvoiceStatus
    FROM invoice_base b
    INNER JOIN [Your invoice print or electronic delivery history table] s
        ON s.[invoice_id_column] = b.invoice_id
    WHERE s.[transmission_status_column] = '[Your successful transmission status value]'
      AND s.[transmission_timestamp_column] >= :start_date
      AND s.[transmission_timestamp_column] < :end_date

    UNION ALL

    SELECT
        b.invoice_number AS InvoiceNumber,
        'Invoice Modified' AS ActivityName,
        m.[modification_timestamp_column] AS EventTime,
        b.total_invoice_amount AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        m.[modification_user_column] AS User,
        m.[modification_department_column] AS Department,
        b.invoice_status AS InvoiceStatus
    FROM invoice_base b
    INNER JOIN [Your invoice audit or modification history table] m
        ON m.[invoice_id_column] = b.invoice_id
    WHERE m.[modification_timestamp_column] >= :start_date
      AND m.[modification_timestamp_column] < :end_date

    UNION ALL

    SELECT
        b.invoice_number AS InvoiceNumber,
        'Credit Memo Created' AS ActivityName,
        c.[credit_memo_creation_timestamp_column] AS EventTime,
        c.[credit_memo_amount_column] AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        c.[credit_memo_user_column] AS User,
        c.[credit_memo_department_column] AS Department,
        c.[credit_memo_status_column] AS InvoiceStatus
    FROM invoice_base b
    INNER JOIN [Your credit memo table] c
        ON c.[related_invoice_id_column] = b.invoice_id
    WHERE c.[credit_memo_creation_timestamp_column] >= :start_date
      AND c.[credit_memo_creation_timestamp_column] < :end_date

    UNION ALL

    SELECT
        b.invoice_number AS InvoiceNumber,
        'Dispute Raised' AS ActivityName,
        d.[dispute_timestamp_column] AS EventTime,
        b.total_invoice_amount AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        d.[dispute_user_column] AS User,
        d.[dispute_department_column] AS Department,
        b.invoice_status AS InvoiceStatus
    FROM invoice_base b
    INNER JOIN [Your dispute or collections dispute table] d
        ON d.[invoice_id_column] = b.invoice_id
    WHERE d.[dispute_status_column] = '[Your raised dispute status value]'
      AND d.[dispute_timestamp_column] >= :start_date
      AND d.[dispute_timestamp_column] < :end_date

    UNION ALL

    SELECT
        b.invoice_number AS InvoiceNumber,
        'Payment Due Date Reached' AS ActivityName,
        CAST(b.due_date AS TIMESTAMP) AS EventTime,
        b.total_invoice_amount AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        CAST(NULL AS VARCHAR2(255)) AS User,
        CAST(NULL AS VARCHAR2(255)) AS Department,
        b.invoice_status AS InvoiceStatus
    FROM invoice_base b
    WHERE b.due_date >= CAST(:start_date AS DATE)
      AND b.due_date < CAST(:end_date AS DATE)

    UNION ALL

    SELECT
        b.invoice_number AS InvoiceNumber,
        'Payment Reminder Issued' AS ActivityName,
        r.[reminder_timestamp_column] AS EventTime,
        b.total_invoice_amount AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        r.[reminder_user_column] AS User,
        r.[reminder_department_column] AS Department,
        b.invoice_status AS InvoiceStatus
    FROM invoice_base b
    INNER JOIN [Your dunning or payment reminder history table] r
        ON r.[invoice_id_column] = b.invoice_id
    WHERE r.[reminder_status_column] = '[Your issued reminder status value]'
      AND r.[reminder_timestamp_column] >= :start_date
      AND r.[reminder_timestamp_column] < :end_date

    UNION ALL

    SELECT
        b.invoice_number AS InvoiceNumber,
        'Customer Payment Received' AS ActivityName,
        p.[receipt_timestamp_column] AS EventTime,
        b.total_invoice_amount AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        p.[receipt_user_column] AS User,
        p.[receipt_department_column] AS Department,
        b.invoice_status AS InvoiceStatus
    FROM invoice_base b
    INNER JOIN [Your customer receipt table] p
        ON p.[invoice_id_column] = b.invoice_id
    WHERE p.[receipt_timestamp_column] >= :start_date
      AND p.[receipt_timestamp_column] < :end_date

    UNION ALL

    SELECT
        b.invoice_number AS InvoiceNumber,
        'Payment Reversed' AS ActivityName,
        v.[reversal_timestamp_column] AS EventTime,
        b.total_invoice_amount AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        v.[reversal_user_column] AS User,
        v.[reversal_department_column] AS Department,
        b.invoice_status AS InvoiceStatus
    FROM invoice_base b
    INNER JOIN [Your receipt reversal history table] v
        ON v.[invoice_id_column] = b.invoice_id
    WHERE v.[reversal_timestamp_column] >= :start_date
      AND v.[reversal_timestamp_column] < :end_date

    UNION ALL

    SELECT
        b.invoice_number AS InvoiceNumber,
        'Cash Applied/Reconciled' AS ActivityName,
        x.[application_timestamp_column] AS EventTime,
        b.total_invoice_amount AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        x.[application_user_column] AS User,
        x.[application_department_column] AS Department,
        b.invoice_status AS InvoiceStatus
    FROM invoice_base b
    INNER JOIN [Your receipt application table] x
        ON x.[invoice_id_column] = b.invoice_id
    WHERE x.[application_status_column] = '[Your applied status value]'
      AND x.[application_timestamp_column] >= :start_date
      AND x.[application_timestamp_column] < :end_date

    UNION ALL

    SELECT
        b.invoice_number AS InvoiceNumber,
        'Write-Off Created' AS ActivityName,
        w.[write_off_timestamp_column] AS EventTime,
        b.total_invoice_amount AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        w.[write_off_user_column] AS User,
        w.[write_off_department_column] AS Department,
        b.invoice_status AS InvoiceStatus
    FROM invoice_base b
    INNER JOIN [Your write-off or adjustment history table] w
        ON w.[invoice_id_column] = b.invoice_id
    WHERE w.[write_off_type_column] = '[Your write-off type value]'
      AND w.[write_off_timestamp_column] >= :start_date
      AND w.[write_off_timestamp_column] < :end_date

    UNION ALL

    SELECT
        b.invoice_number AS InvoiceNumber,
        'Invoice Closed' AS ActivityName,
        z.[closure_timestamp_column] AS EventTime,
        b.total_invoice_amount AS TotalInvoiceAmount,
        b.due_date AS DueDate,
        b.customer_id AS CustomerId,
        z.[closure_user_column] AS User,
        z.[closure_department_column] AS Department,
        z.[closure_status_column] AS InvoiceStatus
    FROM invoice_base b
    INNER JOIN [Your invoice status or closure history table] z
        ON z.[invoice_id_column] = b.invoice_id
    WHERE z.[closure_status_column] = '[Your closed status value]'
      AND z.[closure_timestamp_column] >= :start_date
      AND z.[closure_timestamp_column] < :end_date
)
SELECT
    InvoiceNumber,
    ActivityName,
    EventTime,
    TotalInvoiceAmount,
    DueDate,
    CustomerId,
    User,
    Department,
    InvoiceStatus
FROM event_log
WHERE InvoiceNumber IS NOT NULL
  AND ActivityName IS NOT NULL
  AND EventTime IS NOT NULL
ORDER BY InvoiceNumber, EventTime, ActivityName

import pandas as pd
import oracledb
import sys

class=class="hl-string">"hl-comment"># --- Configuration: Replace with your database details ---
class=class="hl-string">"hl-comment"># For security, consider using environment variables or a secrets management tool.
db_user = class="hl-string">"your_user"
db_password = class="hl-string">"your_password"
class=class="hl-string">"hl-comment"># DSN (Data Source Name) e.g., class="hl-string">'your_db_host:1521/your_service_name'
db_dsn = class="hl-string">"your_dsn"

class=class="hl-string">"hl-comment"># --- Parameters for the extraction ---
class=class="hl-string">"hl-comment"># Use class="hl-string">'YYYY-MM-DD' format for dates
params = {
    class="hl-string">'start_date': class="hl-string">'2023-01-01',
    class="hl-string">'end_date': class="hl-string">'2023-12-31',
    class="hl-string">'org_id': 204 class=class="hl-string">"hl-comment"># Example Organization ID
}

sql_query = class="hl-string">"""
WITH base_invoices AS (
    -- Pre-filter invoices to the relevant period to improve performance
    SELECT
        rct.customer_trx_id,
        rct.trx_number,
        rct.org_id
    FROM
        RA_CUSTOMER_TRX_ALL rct
    WHERE
        rct.creation_date BETWEEN TO_DATE(:start_date, 'YYYY-MM-DD') AND TO_DATE(:end_date, 'YYYY-MM-DD')
        AND rct.org_id = :org_id
        AND rct.cust_trx_type_id IN (SELECT cust_trx_type_id FROM ra_cust_trx_types_all WHERE type IN ('INV', 'CM', 'DM'))
)
-- 1. Invoice Generated
SELECT
    rct.trx_number AS InvoiceNumber,
    'Invoice Generated' AS ActivityName,
    rct.creation_date AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    fu.user_name AS User,
    '[Department Placeholder]' AS Department,
    aps.status AS InvoiceStatus
FROM
    RA_CUSTOMER_TRX_ALL rct
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON rct.customer_trx_id = aps.customer_trx_id
    LEFT JOIN FND_USER fu ON rct.created_by = fu.user_id
WHERE rct.customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)

UNION ALL

-- 2. Invoice Approved
-- This activity is highly dependent on custom workflows. 
-- This query assumes approval is marked by the invoice status becoming 'OP' (Open) from an 'IP' (Incomplete) state.
-- The event time is the last update date when this condition is met.
SELECT
    rct.trx_number AS InvoiceNumber,
    'Invoice Approved' AS ActivityName,
    rct.last_update_date AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    fu.user_name AS User,
    '[Department Placeholder]' AS Department,
    aps.status AS InvoiceStatus
FROM
    RA_CUSTOMER_TRX_ALL rct
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON rct.customer_trx_id = aps.customer_trx_id
    LEFT JOIN FND_USER fu ON rct.last_updated_by = fu.user_id
WHERE rct.customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)
  AND rct.complete_flag = 'Y'
  AND aps.status = 'OP'

UNION ALL

-- 3. Invoice Sent To Customer
-- This event is often not explicitly stored. We use the invoice date (TRX_DATE) of a completed invoice as a proxy.
SELECT
    rct.trx_number AS InvoiceNumber,
    'Invoice Sent To Customer' AS ActivityName,
    rct.trx_date AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    fu.user_name AS User,
    '[Department Placeholder]' AS Department,
    aps.status AS InvoiceStatus
FROM
    RA_CUSTOMER_TRX_ALL rct
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON rct.customer_trx_id = aps.customer_trx_id
    LEFT JOIN FND_USER fu ON rct.last_updated_by = fu.user_id
WHERE rct.customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)
  AND rct.complete_flag = 'Y'

UNION ALL

-- 4. Invoice Modified
-- Captures updates to an invoice before it is completed.
SELECT
    rct.trx_number AS InvoiceNumber,
    'Invoice Modified' AS ActivityName,
    rct.last_update_date AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    fu.user_name AS User,
    '[Department Placeholder]' AS Department,
    aps.status AS InvoiceStatus
FROM
    RA_CUSTOMER_TRX_ALL rct
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON rct.customer_trx_id = aps.customer_trx_id
    LEFT JOIN FND_USER fu ON rct.last_updated_by = fu.user_id
WHERE rct.customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)
  AND rct.last_update_date > rct.creation_date + INTERVAL '1' MINUTE
  AND rct.complete_flag = 'N'

UNION ALL

-- 5. Credit Memo Created
SELECT
    rct.trx_number AS InvoiceNumber,
    'Credit Memo Created' AS ActivityName,
    rct.creation_date AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    fu.user_name AS User,
    '[Department Placeholder]' AS Department,
    aps.status AS InvoiceStatus
FROM
    RA_CUSTOMER_TRX_ALL rct
    JOIN RA_CUST_TRX_TYPES_ALL rctt ON rct.cust_trx_type_id = rctt.cust_trx_type_id
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON rct.customer_trx_id = aps.customer_trx_id
    LEFT JOIN FND_USER fu ON rct.created_by = fu.user_id
WHERE rct.previous_customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)
  AND rctt.type = 'CM'

UNION ALL

-- 6. Dispute Raised
SELECT
    rct.trx_number AS InvoiceNumber,
    'Dispute Raised' AS ActivityName,
    aps.dispute_date AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    fu.user_name AS User,
    '[Department Placeholder]' AS Department,
    aps.status AS InvoiceStatus
FROM
    RA_CUSTOMER_TRX_ALL rct
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON rct.customer_trx_id = aps.customer_trx_id
    LEFT JOIN FND_USER fu ON aps.last_updated_by = fu.user_id
WHERE rct.customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)
  AND aps.dispute_date IS NOT NULL

UNION ALL

-- 7. Payment Due Date Reached
SELECT
    rct.trx_number AS InvoiceNumber,
    'Payment Due Date Reached' AS ActivityName,
    aps.due_date AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    'SYSTEM' AS User,
    'System' AS Department,
    aps.status AS InvoiceStatus
FROM
    RA_CUSTOMER_TRX_ALL rct
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON rct.customer_trx_id = aps.customer_trx_id
WHERE rct.customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)

UNION ALL

-- 8. Payment Reminder Issued
-- Assumes usage of Oracle Advanced Collections dunning letters
SELECT
    rct.trx_number AS InvoiceNumber,
    'Payment Reminder Issued' AS ActivityName,
    adl.dunning_letter_date AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    fu.user_name AS User,
    '[Department Placeholder]' AS Department,
    aps.status AS InvoiceStatus
FROM
    AR_DUNNING_LETTERS adl
    JOIN AR_DUNNING_LETTER_SETS adls ON adl.dunning_letter_set_id = adls.dunning_letter_set_id
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON adl.payment_schedule_id = aps.payment_schedule_id
    JOIN RA_CUSTOMER_TRX_ALL rct ON aps.customer_trx_id = rct.customer_trx_id
    LEFT JOIN FND_USER fu ON adl.last_updated_by = fu.user_id
WHERE rct.customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)

UNION ALL

-- 9. Customer Payment Received
SELECT 
    rct.trx_number AS InvoiceNumber,
    'Customer Payment Received' AS ActivityName,
    acr.receipt_date AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    fu.user_name AS User,
    '[Department Placeholder]' AS Department,
    aps.status AS InvoiceStatus
FROM 
    AR_RECEIVABLE_APPLICATIONS_ALL ara
    JOIN AR_CASH_RECEIPTS_ALL acr ON ara.cash_receipt_id = acr.cash_receipt_id
    JOIN RA_CUSTOMER_TRX_ALL rct ON ara.applied_customer_trx_id = rct.customer_trx_id
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON ara.applied_payment_schedule_id = aps.payment_schedule_id
    LEFT JOIN FND_USER fu ON acr.created_by = fu.user_id
WHERE rct.customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)
  AND ara.status = 'APP'

UNION ALL

-- 10. Payment Reversed
SELECT 
    rct.trx_number AS InvoiceNumber,
    'Payment Reversed' AS ActivityName,
    ara.reversal_date AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    fu.user_name AS User,
    '[Department Placeholder]' AS Department,
    aps.status AS InvoiceStatus
FROM 
    AR_RECEIVABLE_APPLICATIONS_ALL ara
    JOIN RA_CUSTOMER_TRX_ALL rct ON ara.applied_customer_trx_id = rct.customer_trx_id
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON ara.applied_payment_schedule_id = aps.payment_schedule_id
    LEFT JOIN FND_USER fu ON ara.last_updated_by = fu.user_id
WHERE rct.customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)
  AND ara.reversal_date IS NOT NULL

UNION ALL

-- 11. Cash Applied/Reconciled
SELECT
    rct.trx_number AS InvoiceNumber,
    'Cash Applied/Reconciled' AS ActivityName,
    ara.apply_date AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    fu.user_name AS User,
    '[Department Placeholder]' AS Department,
    aps.status AS InvoiceStatus
FROM
    AR_RECEIVABLE_APPLICATIONS_ALL ara
    JOIN RA_CUSTOMER_TRX_ALL rct ON ara.applied_customer_trx_id = rct.customer_trx_id
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON ara.applied_payment_schedule_id = aps.payment_schedule_id
    LEFT JOIN FND_USER fu ON ara.created_by = fu.user_id
WHERE rct.customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)
  AND ara.status = 'APP'

UNION ALL

-- 12. Write-Off Created
SELECT
    rct.trx_number AS InvoiceNumber,
    'Write-Off Created' AS ActivityName,
    adj.apply_date AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    fu.user_name AS User,
    '[Department Placeholder]' AS Department,
    aps.status AS InvoiceStatus
FROM
    AR_ADJUSTMENTS_ALL adj
    JOIN RA_CUSTOMER_TRX_ALL rct ON adj.customer_trx_id = rct.customer_trx_id
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON rct.customer_trx_id = aps.customer_trx_id
    LEFT JOIN FND_USER fu ON adj.created_by = fu.user_id
WHERE rct.customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)
  AND adj.type = 'INVOICE'
  AND adj.receivables_trx_id IN (SELECT receivables_trx_id FROM AR_RECEIVABLES_TRX_ALL WHERE name = 'Write-Off')

UNION ALL

-- 13. Invoice Closed
SELECT
    rct.trx_number AS InvoiceNumber,
    'Invoice Closed' AS ActivityName,
    aps.actual_date_closed AS EventTime,
    aps.amount_due_original AS TotalInvoiceAmount,
    aps.due_date AS DueDate,
    rct.bill_to_customer_id AS CustomerId,
    fu.user_name AS User,
    '[Department Placeholder]' AS Department,
    aps.status AS InvoiceStatus
FROM
    RA_CUSTOMER_TRX_ALL rct
    JOIN AR_PAYMENT_SCHEDULES_ALL aps ON rct.customer_trx_id = aps.customer_trx_id
    LEFT JOIN FND_USER fu ON aps.last_updated_by = fu.user_id
WHERE rct.customer_trx_id IN (SELECT customer_trx_id FROM base_invoices)
  AND aps.status = 'CL'
"""

try:
    class=class="hl-string">"hl-comment"># Establish the connection to the Oracle database
    print(class="hl-string">"Connecting to Oracle database...")
    connection = oracledb.connect(user=db_user, password=db_password, dsn=db_dsn)

    print(class="hl-string">"Executing query...")
    class=class="hl-string">"hl-comment"># Execute the query and load data into a pandas DataFrame
    class=class="hl-string">"hl-comment"># Using a with statement ensures the connection is closed automatically
    with connection:
        df = pd.read_sql_query(sql_query, connection, params=params)

    print(fclass="hl-string">"Successfully extracted {len(df)} events.")

    class=class="hl-string">"hl-comment"># Data processing and cleanup
    if not df.empty:
        df[class="hl-string">'EVENTTIME'] = pd.to_datetime(df[class="hl-string">'EVENTTIME'])
        df.sort_values(by=[class="hl-string">'INVOICENUMBER', class="hl-string">'EVENTTIME'], inplace=True)

    class=class="hl-string">"hl-comment"># Save the DataFrame to a CSV file
    output_filename = class="hl-string">'oracle_ebs_billing_event_log.csv'
    df.to_csv(output_filename, index=False, encoding=class="hl-string">'utf-8')
    print(fclass="hl-string">"Event log saved to {output_filename}")

except oracledb.Error as e:
    print(fclass="hl-string">"Database error occurred: {e}")
    sys.exit(1)
except Exception as e:
    print(fclass="hl-string">"An error occurred: {e}")
    sys.exit(1)