Modèle de Données : De la Commande au Paiement - Traitement des Commandes de Vente

WITH base_data AS (
  SELECT
    OOHA.HEADER_ID,
    OOHA.ORDER_NUMBER,
    OOHA.ORG_ID,
    HP.PARTY_NAME AS CUSTOMER_NAME,
    OOHA.CREATION_DATE AS ORDER_CREATION_DATE,
    OOHA.CREATED_BY AS ORDER_CREATED_BY,
    OOHA.BOOKED_DATE,
    OOHA.BOOKED_FLAG,
    OOHA.FLOW_STATUS_CODE AS HEADER_STATUS,
    OOHA.LAST_UPDATE_DATE AS HEADER_LAST_UPDATE_DATE,
    OOHA.LAST_UPDATED_BY AS HEADER_LAST_UPDATED_BY,
    OOHA.TRANSACTIONAL_CURR_CODE,
    RT.NAME AS PAYMENT_TERMS,
    (
      SELECT
        SUM(OOLA.ORDERED_QUANTITY * OOLA.UNIT_SELLING_PRICE)
      FROM
        OE_ORDER_LINES_ALL OOLA
      WHERE
        OOLA.HEADER_ID = OOHA.HEADER_ID
    ) AS TOTAL_ORDER_AMOUNT,
    OOHA.SOLD_TO_ORG_ID
  FROM
    OE_ORDER_HEADERS_ALL OOHA
    JOIN HZ_CUST_ACCOUNTS HCA ON OOHA.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
    JOIN HZ_PARTIES HP ON HCA.PARTY_ID = HP.PARTY_ID
    LEFT JOIN RA_TERMS_B RT ON OOHA.PAYMENT_TERM_ID = RT.TERM_ID
  WHERE
    OOHA.CREATION_DATE BETWEEN TO_DATE(:p_start_date, 'YYYY-MM-DD') AND TO_DATE(:p_end_date, 'YYYY-MM-DD HH24:MI:SS')
)
-- 1. Sales Order Created
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Sales Order Created' AS "ActivityName",
  BD.ORDER_CREATION_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  BD.HEADER_STATUS AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.ORDER_CREATED_BY = FU.USER_ID
UNION ALL
-- 2. Order Booked
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Booked' AS "ActivityName",
  BD.BOOKED_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'BOOKED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.BOOKED_DATE IS NOT NULL
UNION ALL
-- 3. Credit Check Performed (using Booked Date as a proxy)
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Credit Check Performed' AS "ActivityName",
  BD.BOOKED_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'BOOKED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.BOOKED_DATE IS NOT NULL
UNION ALL
-- 4. Inventory Allocated & 5. Pick Released (using WSH_DELIVERY_DETAILS creation)
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  CASE
    WHEN WDD.RELEASED_STATUS = 'Y' THEN 'Pick Released'
    ELSE 'Inventory Allocated'
  END AS "ActivityName",
  WDD.CREATION_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  OOLA.FLOW_STATUS_CODE AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  OOLA.PROMISE_DATE AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  JOIN OE_ORDER_LINES_ALL OOLA ON BD.HEADER_ID = OOLA.HEADER_ID
  JOIN WSH_DELIVERY_DETAILS WDD ON OOLA.LINE_ID = WDD.SOURCE_LINE_ID
  LEFT JOIN FND_USER FU ON WDD.CREATED_BY = FU.USER_ID
WHERE
  WDD.RELEASED_STATUS IN ('S', 'Y', 'C')
UNION ALL
-- 6. Goods Shipped
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Goods Shipped' AS "ActivityName",
  WTS.ACTUAL_DEPARTURE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  OOLA.FLOW_STATUS_CODE AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  OOLA.PROMISE_DATE AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  JOIN OE_ORDER_LINES_ALL OOLA ON BD.HEADER_ID = OOLA.HEADER_ID
  JOIN WSH_DELIVERY_DETAILS WDD ON OOLA.LINE_ID = WDD.SOURCE_LINE_ID
  JOIN WSH_DELIVERY_ASSIGNMENTS WDA ON WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
  JOIN WSH_NEW_DELIVERIES WND ON WDA.DELIVERY_ID = WND.DELIVERY_ID
  JOIN WSH_DELIVERY_LEGS WDL ON WND.DELIVERY_ID = WDL.DELIVERY_ID
  JOIN WSH_TRIP_STOPS WTS ON WDL.PICK_UP_STOP_ID = WTS.STOP_ID
  LEFT JOIN FND_USER FU ON WTS.LAST_UPDATED_BY = FU.USER_ID
WHERE
  WTS.ACTUAL_DEPARTURE_DATE IS NOT NULL
UNION ALL
-- 7. Goods Delivered - This is a placeholder as standard EBS does not track this event.
-- Data must be sourced from a carrier integration or customization.
/*
SELECT 
    '[Your Sales Order]' AS "SalesOrder",
    'Goods Delivered' AS "ActivityName",
    TO_DATE('[Your Delivery Date]', 'YYYY-MM-DD') AS "EventTime", 
    ... 
FROM [Your Custom Table]
*/
-- 8. Invoice Created
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Invoice Created' AS "ActivityName",
  RCTA.CREATION_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'INVOICED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  APSA.DUE_DATE AS "PaymentDueDate"
FROM
  base_data BD
  JOIN RA_CUSTOMER_TRX_LINES_ALL RCTLA ON BD.ORDER_NUMBER = RCTLA.INTERFACE_LINE_ATTRIBUTE1 AND TO_CHAR(BD.HEADER_ID) = RCTLA.INTERFACE_LINE_ATTRIBUTE6
  JOIN RA_CUSTOMER_TRX_ALL RCTA ON RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
  LEFT JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  LEFT JOIN FND_USER FU ON RCTA.CREATED_BY = FU.USER_ID
UNION ALL
-- 9. Invoice Sent to Customer (using Invoice Creation date as a proxy)
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Invoice Sent to Customer' AS "ActivityName",
  RCTA.TRX_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'INVOICED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  APSA.DUE_DATE AS "PaymentDueDate"
FROM
  base_data BD
  JOIN RA_CUSTOMER_TRX_LINES_ALL RCTLA ON BD.ORDER_NUMBER = RCTLA.INTERFACE_LINE_ATTRIBUTE1 AND TO_CHAR(BD.HEADER_ID) = RCTLA.INTERFACE_LINE_ATTRIBUTE6
  JOIN RA_CUSTOMER_TRX_ALL RCTA ON RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
  LEFT JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  LEFT JOIN FND_USER FU ON RCTA.CREATED_BY = FU.USER_ID
UNION ALL
-- 10. Payment Received
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Payment Received' AS "ActivityName",
  ARAA.APPLY_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'PAID' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  APSA.DUE_DATE AS "PaymentDueDate"
FROM
  base_data BD
  JOIN RA_CUSTOMER_TRX_ALL RCTA ON BD.SOLD_TO_ORG_ID = RCTA.SOLD_TO_CUSTOMER_ID
  JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  JOIN AR_RECEIVABLE_APPLICATIONS_ALL ARAA ON APSA.PAYMENT_SCHEDULE_ID = ARAA.APPLIED_PAYMENT_SCHEDULE_ID
  JOIN RA_CUSTOMER_TRX_LINES_ALL RCTLA ON RCTA.CUSTOMER_TRX_ID = RCTLA.CUSTOMER_TRX_ID AND BD.ORDER_NUMBER = RCTLA.INTERFACE_LINE_ATTRIBUTE1
  LEFT JOIN FND_USER FU ON ARAA.CREATED_BY = FU.USER_ID
WHERE
  ARAA.STATUS = 'APP'
UNION ALL
-- 11. Order Line Closed
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Line Closed' AS "ActivityName",
  OOLA.LAST_UPDATE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  OOLA.FLOW_STATUS_CODE AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  OOLA.PROMISE_DATE AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  JOIN OE_ORDER_LINES_ALL OOLA ON BD.HEADER_ID = OOLA.HEADER_ID
  LEFT JOIN FND_USER FU ON OOLA.LAST_UPDATED_BY = FU.USER_ID
WHERE
  OOLA.FLOW_STATUS_CODE = 'CLOSED'
UNION ALL
-- 12. Order Closed
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Closed' AS "ActivityName",
  BD.HEADER_LAST_UPDATE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  BD.HEADER_STATUS AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.HEADER_STATUS = 'CLOSED'
UNION ALL
-- 13. Order Cancelled
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Cancelled' AS "ActivityName",
  BD.HEADER_LAST_UPDATE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  BD.HEADER_STATUS AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.HEADER_STATUS = 'CANCELLED'

WITH base_data AS (
  SELECT
    OOHA.HEADER_ID,
    OOHA.ORDER_NUMBER,
    OOHA.ORG_ID,
    HP.PARTY_NAME AS CUSTOMER_NAME,
    OOHA.CREATION_DATE AS ORDER_CREATION_DATE,
    OOHA.CREATED_BY AS ORDER_CREATED_BY,
    OOHA.BOOKED_DATE,
    OOHA.BOOKED_FLAG,
    OOHA.FLOW_STATUS_CODE AS HEADER_STATUS,
    OOHA.LAST_UPDATE_DATE AS HEADER_LAST_UPDATE_DATE,
    OOHA.LAST_UPDATED_BY AS HEADER_LAST_UPDATED_BY,
    OOHA.TRANSACTIONAL_CURR_CODE,
    RT.NAME AS PAYMENT_TERMS,
    (
      SELECT
        SUM(OOLA.ORDERED_QUANTITY * OOLA.UNIT_SELLING_PRICE)
      FROM
        OE_ORDER_LINES_ALL OOLA
      WHERE
        OOLA.HEADER_ID = OOHA.HEADER_ID
    ) AS TOTAL_ORDER_AMOUNT,
    OOHA.SOLD_TO_ORG_ID
  FROM
    OE_ORDER_HEADERS_ALL OOHA
    JOIN HZ_CUST_ACCOUNTS HCA ON OOHA.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
    JOIN HZ_PARTIES HP ON HCA.PARTY_ID = HP.PARTY_ID
    LEFT JOIN RA_TERMS_B RT ON OOHA.PAYMENT_TERM_ID = RT.TERM_ID
  WHERE
    OOHA.CREATION_DATE BETWEEN TO_DATE(:p_start_date, 'YYYY-MM-DD') AND TO_DATE(:p_end_date, 'YYYY-MM-DD HH24:MI:SS')
)
-- 1. Sales Order Created
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Sales Order Created' AS "ActivityName",
  BD.ORDER_CREATION_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  BD.HEADER_STATUS AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.ORDER_CREATED_BY = FU.USER_ID
UNION ALL
-- 2. Order Booked
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Booked' AS "ActivityName",
  BD.BOOKED_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'BOOKED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.BOOKED_DATE IS NOT NULL
UNION ALL
-- 3. Credit Check Performed (using Booked Date as a proxy)
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Credit Check Performed' AS "ActivityName",
  BD.BOOKED_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'BOOKED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.BOOKED_DATE IS NOT NULL
UNION ALL
-- 4. Inventory Allocated & 5. Pick Released (using WSH_DELIVERY_DETAILS creation)
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  CASE
    WHEN WDD.RELEASED_STATUS = 'Y' THEN 'Pick Released'
    ELSE 'Inventory Allocated'
  END AS "ActivityName",
  WDD.CREATION_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  OOLA.FLOW_STATUS_CODE AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  OOLA.PROMISE_DATE AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  JOIN OE_ORDER_LINES_ALL OOLA ON BD.HEADER_ID = OOLA.HEADER_ID
  JOIN WSH_DELIVERY_DETAILS WDD ON OOLA.LINE_ID = WDD.SOURCE_LINE_ID
  LEFT JOIN FND_USER FU ON WDD.CREATED_BY = FU.USER_ID
WHERE
  WDD.RELEASED_STATUS IN ('S', 'Y', 'C')
UNION ALL
-- 6. Goods Shipped
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Goods Shipped' AS "ActivityName",
  WTS.ACTUAL_DEPARTURE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  OOLA.FLOW_STATUS_CODE AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  OOLA.PROMISE_DATE AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  JOIN OE_ORDER_LINES_ALL OOLA ON BD.HEADER_ID = OOLA.HEADER_ID
  JOIN WSH_DELIVERY_DETAILS WDD ON OOLA.LINE_ID = WDD.SOURCE_LINE_ID
  JOIN WSH_DELIVERY_ASSIGNMENTS WDA ON WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
  JOIN WSH_NEW_DELIVERIES WND ON WDA.DELIVERY_ID = WND.DELIVERY_ID
  JOIN WSH_DELIVERY_LEGS WDL ON WND.DELIVERY_ID = WDL.DELIVERY_ID
  JOIN WSH_TRIP_STOPS WTS ON WDL.PICK_UP_STOP_ID = WTS.STOP_ID
  LEFT JOIN FND_USER FU ON WTS.LAST_UPDATED_BY = FU.USER_ID
WHERE
  WTS.ACTUAL_DEPARTURE_DATE IS NOT NULL
UNION ALL
-- 7. Goods Delivered - This is a placeholder as standard EBS does not track this event.
-- Data must be sourced from a carrier integration or customization.
/*
SELECT 
    '[Your Sales Order]' AS "SalesOrder",
    'Goods Delivered' AS "ActivityName",
    TO_DATE('[Your Delivery Date]', 'YYYY-MM-DD') AS "EventTime", 
    ... 
FROM [Your Custom Table]
*/
-- 8. Invoice Created
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Invoice Created' AS "ActivityName",
  RCTA.CREATION_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'INVOICED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  APSA.DUE_DATE AS "PaymentDueDate"
FROM
  base_data BD
  JOIN RA_CUSTOMER_TRX_LINES_ALL RCTLA ON BD.ORDER_NUMBER = RCTLA.INTERFACE_LINE_ATTRIBUTE1 AND TO_CHAR(BD.HEADER_ID) = RCTLA.INTERFACE_LINE_ATTRIBUTE6
  JOIN RA_CUSTOMER_TRX_ALL RCTA ON RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
  LEFT JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  LEFT JOIN FND_USER FU ON RCTA.CREATED_BY = FU.USER_ID
UNION ALL
-- 9. Invoice Sent to Customer (using Invoice Creation date as a proxy)
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Invoice Sent to Customer' AS "ActivityName",
  RCTA.TRX_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'INVOICED' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  APSA.DUE_DATE AS "PaymentDueDate"
FROM
  base_data BD
  JOIN RA_CUSTOMER_TRX_LINES_ALL RCTLA ON BD.ORDER_NUMBER = RCTLA.INTERFACE_LINE_ATTRIBUTE1 AND TO_CHAR(BD.HEADER_ID) = RCTLA.INTERFACE_LINE_ATTRIBUTE6
  JOIN RA_CUSTOMER_TRX_ALL RCTA ON RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
  LEFT JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  LEFT JOIN FND_USER FU ON RCTA.CREATED_BY = FU.USER_ID
UNION ALL
-- 10. Payment Received
SELECT DISTINCT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Payment Received' AS "ActivityName",
  ARAA.APPLY_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  'PAID' AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  APSA.DUE_DATE AS "PaymentDueDate"
FROM
  base_data BD
  JOIN RA_CUSTOMER_TRX_ALL RCTA ON BD.SOLD_TO_ORG_ID = RCTA.SOLD_TO_CUSTOMER_ID
  JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  JOIN AR_RECEIVABLE_APPLICATIONS_ALL ARAA ON APSA.PAYMENT_SCHEDULE_ID = ARAA.APPLIED_PAYMENT_SCHEDULE_ID
  JOIN RA_CUSTOMER_TRX_LINES_ALL RCTLA ON RCTA.CUSTOMER_TRX_ID = RCTLA.CUSTOMER_TRX_ID AND BD.ORDER_NUMBER = RCTLA.INTERFACE_LINE_ATTRIBUTE1
  LEFT JOIN FND_USER FU ON ARAA.CREATED_BY = FU.USER_ID
WHERE
  ARAA.STATUS = 'APP'
UNION ALL
-- 11. Order Line Closed
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Line Closed' AS "ActivityName",
  OOLA.LAST_UPDATE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  OOLA.FLOW_STATUS_CODE AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  OOLA.PROMISE_DATE AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  JOIN OE_ORDER_LINES_ALL OOLA ON BD.HEADER_ID = OOLA.HEADER_ID
  LEFT JOIN FND_USER FU ON OOLA.LAST_UPDATED_BY = FU.USER_ID
WHERE
  OOLA.FLOW_STATUS_CODE = 'CLOSED'
UNION ALL
-- 12. Order Closed
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Closed' AS "ActivityName",
  BD.HEADER_LAST_UPDATE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  BD.HEADER_STATUS AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.HEADER_STATUS = 'CLOSED'
UNION ALL
-- 13. Order Cancelled
SELECT
  BD.ORDER_NUMBER AS "SalesOrder",
  'Order Cancelled' AS "ActivityName",
  BD.HEADER_LAST_UPDATE_DATE AS "EventTime",
  FU.USER_NAME AS "UserName",
  BD.HEADER_STATUS AS "OrderStatus",
  BD.CUSTOMER_NAME AS "CustomerName",
  BD.TOTAL_ORDER_AMOUNT AS "TotalOrderAmount",
  NULL AS "ConfirmedDeliveryDate",
  BD.PAYMENT_TERMS AS "PaymentTerms",
  NULL AS "PaymentDueDate"
FROM
  base_data BD
  LEFT JOIN FND_USER FU ON BD.HEADER_LAST_UPDATED_BY = FU.USER_ID
WHERE
  BD.HEADER_STATUS = 'CANCELLED'

import oracledb
import pandas as pd
import sys

class=class="hl-string">"hl-comment">## CONFIGURATION ##
class=class="hl-string">"hl-comment"># Replace with your actual database credentials and connection details
ORA_USER = class="hl-string">'[your_username]'
ORA_PASS = class="hl-string">'[your_password]'
class=class="hl-string">"hl-comment"># Example DSN: class="hl-string">'your_host:1521/your_service_name'
ORA_DSN = class="hl-string">'[your_db_host:port/service_name]'

class=class="hl-string">"hl-comment"># Set the date range for the extraction
start_date = class="hl-string">'2023-01-01'
end_date = class="hl-string">'2023-06-30'

class=class="hl-string">"hl-comment"># Output file name
output_csv_file = class="hl-string">'o2c_event_log.csv'

sql_query = fclass="hl-string">"""
WITH OrderAmounts AS (
    SELECT 
        header_id,
        SUM(ordered_quantity * unit_selling_price) AS calculated_order_amount
    FROM oe_order_lines_all
    GROUP BY header_id
)
SELECT
    ooha.order_number AS "SalesOrder",
    'Sales Order Created' AS "ActivityName",
    ooha.creation_date AS "EventTime",
    fu_created.user_name AS "UserName",
    ooha.flow_status_code AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    oe_order_headers_all ooha
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu_created ON ooha.created_by = fu_created.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Order Booked' AS "ActivityName",
    ooha.booked_date AS "EventTime",
    fu_updated.user_name AS "UserName",
    'BOOKED' AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    oe_order_headers_all ooha
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu_updated ON ooha.last_updated_by = fu_updated.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND ooha.booked_date IS NOT NULL

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Credit Check Performed' AS "ActivityName",
    ooh.release_date AS "EventTime",
    fu.user_name AS "UserName",
    ooha.flow_status_code AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    oe_order_holds_all ooh
    JOIN oe_order_headers_all ooha ON ooh.header_id = ooha.header_id
    JOIN oe_hold_sources_all ohs ON ooh.hold_source_id = ohs.hold_source_id
    JOIN oe_hold_definitions ohd ON ohs.hold_id = ohd.hold_id
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON ooh.last_updated_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND ohd.name LIKE '%Credit%'
    AND ooh.release_date IS NOT NULL

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Inventory Allocated' AS "ActivityName",
    wdd.creation_date AS "EventTime",
    fu.user_name AS "UserName",
    wdd.released_status AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    oola.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM 
    wsh_delivery_details wdd
    JOIN oe_order_lines_all oola ON wdd.source_line_id = oola.line_id
    JOIN oe_order_headers_all ooha ON oola.header_id = ooha.header_id
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON wdd.created_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE 
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND wdd.source_code = 'OE'

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Pick Released' AS "ActivityName",
    wdd.last_update_date AS "EventTime",
    fu.user_name AS "UserName",
    wdd.released_status AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    oola.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM 
    wsh_delivery_details wdd
    JOIN oe_order_lines_all oola ON wdd.source_line_id = oola.line_id
    JOIN oe_order_headers_all ooha ON oola.header_id = ooha.header_id
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON wdd.last_updated_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE 
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND wdd.released_status IN ('S', 'Y')

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Goods Shipped' AS "ActivityName",
    wts.actual_departure_date AS "EventTime",
    fu.user_name AS "UserName",
    wts.status_code AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    oola.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    wsh_trip_stops wts
    JOIN wsh_delivery_legs wdl ON wts.stop_id = wdl.pick_up_stop_id
    JOIN wsh_delivery_assignments wda ON wdl.delivery_leg_id = wda.delivery_leg_id
    JOIN wsh_delivery_details wdd ON wda.delivery_detail_id = wdd.delivery_detail_id
    JOIN oe_order_lines_all oola ON wdd.source_line_id = oola.line_id
    JOIN oe_order_headers_all ooha ON oola.header_id = ooha.header_id
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON wts.last_updated_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND wts.status_code = 'CL' -- CL = Closed
    AND wts.actual_departure_date IS NOT NULL

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Goods Delivered' AS "ActivityName",
    -- Standard EBS does not track delivery. This needs data from an external carrier system or a custom table.
    -- Using actual_departure_date + a placeholder interval as an example.
    wts.actual_departure_date + INTERVAL '2' DAY AS "EventTime",
    'SYSTEM' AS "UserName",
    'DELIVERED' AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    oola.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    wsh_trip_stops wts
    JOIN wsh_delivery_legs wdl ON wts.stop_id = wdl.pick_up_stop_id
    JOIN wsh_delivery_assignments wda ON wdl.delivery_leg_id = wda.delivery_leg_id
    JOIN wsh_delivery_details wdd ON wda.delivery_detail_id = wdd.delivery_detail_id
    JOIN oe_order_lines_all oola ON wdd.source_line_id = oola.line_id
    JOIN oe_order_headers_all ooha ON oola.header_id = ooha.header_id
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND wts.status_code = 'CL' AND wts.actual_departure_date IS NOT NULL

UNION ALL

SELECT
    rcta.interface_header_attribute1 AS "SalesOrder",
    'Invoice Created' AS "ActivityName",
    rcta.creation_date AS "EventTime",
    fu.user_name AS "UserName",
    'INVOICED' AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    apsa.due_date AS "PaymentDueDate"
FROM
    ra_customer_trx_all rcta
    JOIN oe_order_headers_all ooha ON rcta.interface_header_attribute1 = ooha.order_number
    JOIN hz_cust_accounts hca ON rcta.bill_to_customer_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON rcta.created_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON rcta.term_id = rtb.term_id
    LEFT JOIN ar_payment_schedules_all apsa ON rcta.customer_trx_id = apsa.customer_trx_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999

UNION ALL

SELECT
    rcta.interface_header_attribute1 AS "SalesOrder",
    'Invoice Sent to Customer' AS "ActivityName",
    rcta.trx_date + 1/1440 AS "EventTime", -- Approximating: 1 minute after creation, as this is not explicitly tracked
    'SYSTEM' AS "UserName",
    'INVOICED' AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    apsa.due_date AS "PaymentDueDate"
FROM
    ra_customer_trx_all rcta
    JOIN oe_order_headers_all ooha ON rcta.interface_header_attribute1 = ooha.order_number
    JOIN hz_cust_accounts hca ON rcta.bill_to_customer_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    LEFT JOIN ra_terms_b rtb ON rcta.term_id = rtb.term_id
    LEFT JOIN ar_payment_schedules_all apsa ON rcta.customer_trx_id = apsa.customer_trx_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Payment Received' AS "ActivityName",
    araa.apply_date AS "EventTime",
    fu.user_name AS "UserName",
    'PAID' AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    apsa.due_date AS "PaymentDueDate"
FROM
    ar_receivable_applications_all araa
    JOIN ra_customer_trx_all rcta ON araa.applied_customer_trx_id = rcta.customer_trx_id
    JOIN oe_order_headers_all ooha ON rcta.interface_header_attribute1 = ooha.order_number
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON araa.created_by = fu.user_id
    LEFT JOIN ar_payment_schedules_all apsa ON rcta.customer_trx_id = apsa.customer_trx_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND araa.status = 'APP'

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Order Line Closed' AS "ActivityName",
    oola.last_update_date AS "EventTime",
    fu.user_name AS "UserName",
    oola.flow_status_code AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    oola.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    oe_order_lines_all oola
    JOIN oe_order_headers_all ooha ON oola.header_id = ooha.header_id
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON oola.last_updated_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND oola.flow_status_code = 'CLOSED'

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Order Closed' AS "ActivityName",
    ooha.last_update_date AS "EventTime",
    fu.user_name AS "UserName",
    ooha.flow_status_code AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    oe_order_headers_all ooha
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON ooha.last_updated_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND ooha.flow_status_code = 'CLOSED'

UNION ALL

SELECT
    ooha.order_number AS "SalesOrder",
    'Order Cancelled' AS "ActivityName",
    ooha.last_update_date AS "EventTime",
    fu.user_name AS "UserName",
    ooha.flow_status_code AS "OrderStatus",
    hp.party_name AS "CustomerName",
    oa.calculated_order_amount AS "TotalOrderAmount",
    ooha.promise_date AS "ConfirmedDeliveryDate",
    rtb.name AS "PaymentTerms",
    NULL AS "PaymentDueDate"
FROM
    oe_order_headers_all ooha
    JOIN hz_cust_accounts hca ON ooha.sold_to_org_id = hca.cust_account_id
    JOIN hz_parties hp ON hca.party_id = hp.party_id
    JOIN fnd_user fu ON ooha.last_updated_by = fu.user_id
    LEFT JOIN ra_terms_b rtb ON ooha.payment_term_id = rtb.term_id
    LEFT JOIN OrderAmounts oa ON ooha.header_id = oa.header_id
WHERE
    ooha.creation_date BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD') + 0.99999
    AND ooha.flow_status_code = 'CANCELLED'

ORDER BY "SalesOrder", "EventTime"
"""

try:
    print(class="hl-string">"Initializing database connection...")
    class=class="hl-string">"hl-comment"># Using thick mode requires Oracle Instant Client. 
    class=class="hl-string">"hl-comment"># For thin mode, remove the lib_dir and config_dir parameters.
    oracledb.init_oracle_client()

    print(fclass="hl-string">"Connecting to {ORA_DSN}...")
    with oracledb.connect(user=ORA_USER, password=ORA_PASS, dsn=ORA_DSN) as connection:
        print(class="hl-string">"Connection successful. Executing query...")
        print(fclass="hl-string">"Extracting data from {start_date} to {end_date}.")
        
        class=class="hl-string">"hl-comment"># Use pandas to execute the query and fetch data into a DataFrame
        df = pd.read_sql_query(sql_query, connection)
        
        print(fclass="hl-string">"Query executed. Found {len(df)} events.")

        if not df.empty:
            class=class="hl-string">"hl-comment"># Data type conversions and formatting
            df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'])
            df[class="hl-string">'TotalOrderAmount'] = pd.to_numeric(df[class="hl-string">'TotalOrderAmount'])
            
            print(fclass="hl-string">"Saving data to {output_csv_file}...")
            df.to_csv(output_csv_file, index=False, date_format=class="hl-string">'%Y-%m-%d %H:%M:%S')
            print(class="hl-string">"Extraction complete.")
        else:
            print(class="hl-string">"No data found for the specified date range.")

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)