Mẫu dữ liệu xử lý đơn bán hàng từ đơn hàng đến thu tiền

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
params AS (
    SELECT
        TO_DATE('[START_DATE]', 'YYYY-MM-DD') AS start_date,
        TO_DATE('[END_DATE]', 'YYYY-MM-DD') + 1 AS end_date
    FROM dual
),
base_orders AS (
    SELECT
        h.header_id,
        h.order_number,
        h.creation_date,
        h.created_by,
        h.flow_status_code,
        h.ordered_date,
        h.request_date,
        h.payment_term_id,
        h.sold_to_org_id,
        h.org_id,
        h.transactional_curr_code,
        h.order_category_code,
        h.booked_date,
        h.cancelled_flag,
        h.cancelled_date,
        h.attribute1 AS confirmed_delivery_date_source,
        p.party_name AS customer_name,
        ca.account_number AS customer_account,
        h.order_number AS sales_order,
        (SELECT SUM(NVL(l.ordered_quantity, 0) * NVL(l.unit_selling_price, 0))
           FROM oe_order_lines_all l
          WHERE l.header_id = h.header_id) AS total_order_amount,
        (SELECT name
           FROM ra_terms t
          WHERE t.term_id = h.payment_term_id
            AND ROWNUM = 1) AS payment_terms
    FROM oe_order_headers_all h
    LEFT JOIN hz_cust_accounts ca
      ON ca.cust_account_id = h.sold_to_org_id
    LEFT JOIN hz_parties p
      ON p.party_id = ca.party_id
    CROSS JOIN params x
    WHERE h.creation_date < x.end_date
      AND (h.cancelled_date IS NULL OR h.cancelled_date >= x.start_date)
      AND h.org_id IN ([YOUR_ORG_ID])
),
order_lines AS (
    SELECT
        l.line_id,
        l.header_id,
        l.line_number,
        l.flow_status_code AS line_status,
        l.ordered_quantity,
        l.unit_selling_price,
        l.request_date,
        l.schedule_ship_date,
        l.schedule_arrival_date,
        l.shipment_priority_code,
        l.creation_date AS line_creation_date
    FROM oe_order_lines_all l
    JOIN base_orders b
      ON b.header_id = l.header_id
),
order_status_events AS (
    SELECT
        h.header_id,
        h.order_number,
        h.creation_date AS event_time,
        TO_CHAR(h.created_by) AS user_name,
        h.flow_status_code AS order_status,
        'Sales Order Created' AS activity_name
    FROM base_orders h
    UNION ALL
    SELECT
        h.header_id,
        h.order_number,
        h.booked_date AS event_time,
        CAST(NULL AS VARCHAR2(255)) AS user_name,
        'BOOKED' AS order_status,
        'Order Booked' AS activity_name
    FROM base_orders h
    WHERE h.booked_date IS NOT NULL
    UNION ALL
    SELECT
        h.header_id,
        h.order_number,
        h.cancelled_date AS event_time,
        CAST(NULL AS VARCHAR2(255)) AS user_name,
        'CANCELLED' AS order_status,
        'Order Cancelled' AS activity_name
    FROM base_orders h
    WHERE h.cancelled_date IS NOT NULL
),
credit_events AS (
    SELECT
        b.header_id,
        b.order_number,
        oh.release_date AS event_time,
        TO_CHAR(oh.released_by) AS user_name,
        'CREDIT_CHECK_RELEASED' AS order_status,
        'Credit Check Performed' AS activity_name
    FROM base_orders b
    JOIN oe_order_holds_all oh
      ON oh.header_id = b.header_id
    WHERE UPPER(NVL(oh.hold_source_code, '')) LIKE '%CREDIT%'
      AND oh.release_date IS NOT NULL
),
allocation_events AS (
    SELECT
        b.header_id,
        b.order_number,
        NVL(r.creation_date, l.line_creation_date) AS event_time,
        CAST(NULL AS VARCHAR2(255)) AS user_name,
        l.line_status AS order_status,
        'Inventory Allocated' AS activity_name
    FROM base_orders b
    JOIN order_lines l
      ON l.header_id = b.header_id
    LEFT JOIN mtl_reservations r
      ON r.demand_source_line_id = l.line_id
    WHERE r.reservation_id IS NOT NULL
       OR UPPER(NVL(l.line_status, '')) IN ('RESERVED', 'AWAITING_SHIPPING', 'PICK_RELEASED', 'PICKED')
),
pick_events AS (
    SELECT
        b.header_id,
        b.order_number,
        d.creation_date AS event_time,
        CAST(NULL AS VARCHAR2(255)) AS user_name,
        d.released_status AS order_status,
        'Pick Released' AS activity_name
    FROM base_orders b
    JOIN wsh_delivery_details d
      ON d.source_header_id = b.header_id
    WHERE UPPER(NVL(d.released_status, '')) IN ('S', 'Y', 'RELEASED')
),
ship_events AS (
    SELECT
        b.header_id,
        b.order_number,
        d.confirm_date AS event_time,
        CAST(NULL AS VARCHAR2(255)) AS user_name,
        d.status_code AS order_status,
        'Goods Shipped' AS activity_name
    FROM base_orders b
    JOIN wsh_delivery_details dd
      ON dd.source_header_id = b.header_id
    JOIN wsh_new_deliveries d
      ON d.delivery_id = dd.delivery_id
    WHERE d.confirm_date IS NOT NULL
),
delivery_events AS (
    SELECT
        b.header_id,
        b.order_number,
        CAST(e.event_time AS DATE) AS event_time,
        e.user_name,
        e.order_status,
        'Goods Delivered' AS activity_name
    FROM base_orders b
    JOIN [YOUR_DELIVERY_EVENT_TABLE] e
      ON e.sales_order = b.order_number
    WHERE UPPER(e.activity_name) = 'GOODS DELIVERED'
),
invoice_events AS (
    SELECT
        b.header_id,
        b.order_number,
        t.trx_date AS event_time,
        CAST(NULL AS VARCHAR2(255)) AS user_name,
        t.status_trx AS order_status,
        'Invoice Created' AS activity_name
    FROM base_orders b
    JOIN ra_customer_trx_lines_all tl
      ON tl.interface_line_attribute1 = TO_CHAR(b.order_number)
    JOIN ra_customer_trx_all t
      ON t.customer_trx_id = tl.customer_trx_id
    WHERE t.trx_date IS NOT NULL
),
invoice_sent_events AS (
    SELECT
        b.header_id,
        b.order_number,
        CAST(e.event_time AS DATE) AS event_time,
        e.user_name,
        e.order_status,
        'Invoice Sent to Customer' AS activity_name
    FROM base_orders b
    JOIN [YOUR_INVOICE_TRANSMISSION_EVENT_TABLE] e
      ON e.sales_order = b.order_number
    WHERE UPPER(e.activity_name) = 'INVOICE SENT TO CUSTOMER'
),
payment_events AS (
    SELECT
        b.header_id,
        b.order_number,
        ara.apply_date AS event_time,
        CAST(NULL AS VARCHAR2(255)) AS user_name,
        'APPLIED' AS order_status,
        'Payment Received' AS activity_name
    FROM base_orders b
    JOIN ra_customer_trx_lines_all tl
      ON tl.interface_line_attribute1 = TO_CHAR(b.order_number)
    JOIN ar_receivable_applications_all ara
      ON ara.applied_customer_trx_id = tl.customer_trx_id
    JOIN ar_cash_receipts_all cr
      ON cr.cash_receipt_id = ara.cash_receipt_id
    WHERE ara.status = 'APP'
      AND ara.apply_date IS NOT NULL
),
line_close_events AS (
    SELECT
        b.header_id,
        b.order_number,
        NVL(l.actual_shipment_date, l.last_update_date) AS event_time,
        CAST(NULL AS VARCHAR2(255)) AS user_name,
        l.flow_status_code AS order_status,
        'Order Line Closed' AS activity_name
    FROM base_orders b
    JOIN oe_order_lines_all l
      ON l.header_id = b.header_id
    WHERE UPPER(NVL(l.flow_status_code, '')) = 'CLOSED'
),
order_close_events AS (
    SELECT
        b.header_id,
        b.order_number,
        b.last_update_date AS event_time,
        CAST(NULL AS VARCHAR2(255)) AS user_name,
        b.flow_status_code AS order_status,
        'Order Closed' AS activity_name
    FROM oe_order_headers_all b
    JOIN base_orders x
      ON x.header_id = b.header_id
    WHERE UPPER(NVL(b.flow_status_code, '')) = 'CLOSED'
),
all_events AS (
    SELECT header_id, order_number, event_time, user_name, order_status, activity_name FROM order_status_events
    UNION ALL
    SELECT header_id, order_number, event_time, user_name, order_status, activity_name FROM credit_events
    UNION ALL
    SELECT header_id, order_number, event_time, user_name, order_status, activity_name FROM allocation_events
    UNION ALL
    SELECT header_id, order_number, event_time, user_name, order_status, activity_name FROM pick_events
    UNION ALL
    SELECT header_id, order_number, event_time, user_name, order_status, activity_name FROM ship_events
    UNION ALL
    SELECT header_id, order_number, event_time, user_name, order_status, activity_name FROM delivery_events
    UNION ALL
    SELECT header_id, order_number, event_time, user_name, order_status, activity_name FROM invoice_events
    UNION ALL
    SELECT header_id, order_number, event_time, user_name, order_status, activity_name FROM invoice_sent_events
    UNION ALL
    SELECT header_id, order_number, event_time, user_name, order_status, activity_name FROM payment_events
    UNION ALL
    SELECT header_id, order_number, event_time, user_name, order_status, activity_name FROM line_close_events
    UNION ALL
    SELECT header_id, order_number, event_time, user_name, order_status, activity_name FROM order_close_events
)
SELECT
    e.order_number AS SalesOrder,
    e.activity_name AS ActivityName,
    e.event_time AS EventTime,
    e.user_name AS UserName,
    e.order_status AS OrderStatus,
    b.customer_name AS CustomerName,
    b.total_order_amount AS TotalOrderAmount,
    CASE
        WHEN REGEXP_LIKE(b.confirmed_delivery_date_source, '^\d{4}-\d{2}-\d{2}')
        THEN TO_DATE(SUBSTR(b.confirmed_delivery_date_source, 1, 10), 'YYYY-MM-DD')
        ELSE NULL
    END AS ConfirmedDeliveryDate,
    b.payment_terms AS PaymentTerms,
    ps.due_date AS PaymentDueDate
FROM all_events e
JOIN base_orders b
  ON b.header_id = e.header_id
LEFT JOIN ra_customer_trx_lines_all tl
  ON tl.interface_line_attribute1 = TO_CHAR(e.order_number)
LEFT JOIN ra_customer_trx_all t
  ON t.customer_trx_id = tl.customer_trx_id
LEFT JOIN ar_payment_schedules_all ps
  ON ps.customer_trx_id = t.customer_trx_id
CROSS JOIN params p
WHERE e.event_time >= p.start_date
  AND e.event_time < p.end_date
ORDER BY e.order_number, e.event_time, e.activity_name

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)