Datatemplate voor de verwerking van retouren en terugbetalingen

WITH ReturnOrders AS (
    SELECT 
        SalesDocument AS ReturnCaseId,
        CreationDate,
        CreationDateTime,
        CreatedByUser,
        OrderReason,
        SoldToParty
    FROM I_SalesDocument
    WHERE SalesDocumentType = '[Your Return Order Type]' -- e.g., 'RE'
    AND CreationDate BETWEEN '[Start Date]' AND '[End Date]'
    AND CompanyCode = '[Your Company Code]'
)
-- 1. Return Request Initiated
SELECT
    RO.ReturnCaseId AS "ReturnCaseId",
    'Return Request Initiated' AS "ActivityName",
    RO.CreationDateTime AS "EventTime",
    RO.CreationDateTime AS "EventEndTime",
    RO.CreatedByUser AS "UserName",
    RO.OrderReason AS "ReturnReason",
    CAST(NULL AS DECIMAL(17, 2)) AS "RefundAmount",
    I.Material AS "ProductId",
    RO.SoldToParty AS "CustomerId",
    '[Your Source System ID]' AS "SourceSystemId",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdateTimestamp"
FROM ReturnOrders RO
JOIN I_SalesDocumentItem I ON RO.ReturnCaseId = I.SalesDocument

UNION ALL

-- 2. Return Order Approved
SELECT
    SD.SalesDocument AS "ReturnCaseId",
    'Return Order Approved' AS "ActivityName",
    SD.LastChangeDateTime AS "EventTime", 
    SD.LastChangeDateTime AS "EventEndTime", 
    SD.LastChangedByUser AS "UserName",
    SD.OrderReason AS "ReturnReason",
    CAST(NULL AS DECIMAL(17, 2)) AS "RefundAmount",
    I.Material AS "ProductId",
    SD.SoldToParty AS "CustomerId",
    '[Your Source System ID]' AS "SourceSystemId",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdateTimestamp"
FROM I_SalesDocument AS SD
JOIN ReturnOrders RO ON SD.SalesDocument = RO.ReturnCaseId
JOIN I_SalesDocumentItem I ON SD.SalesDocument = I.SalesDocument
WHERE SD.OverallSDProcessStatus <> 'A' -- Not Open, implying it has been processed/approved
AND I.SDProcessStatus <> 'A'

UNION ALL

-- 3. Return Delivery Created
SELECT
    DF.PrecedingDocument AS "ReturnCaseId",
    'Return Delivery Created' AS "ActivityName",
    LH.CreationDateTime AS "EventTime",
    LH.CreationDateTime AS "EventEndTime",
    LH.CreatedByUser AS "UserName",
    RO.OrderReason AS "ReturnReason",
    CAST(NULL AS DECIMAL(17, 2)) AS "RefundAmount",
    LI.Material AS "ProductId",
    RO.SoldToParty AS "CustomerId",
    '[Your Source System ID]' AS "SourceSystemId",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdateTimestamp"
FROM I_SDDocumentFlow AS DF
JOIN ReturnOrders RO ON DF.PrecedingDocument = RO.ReturnCaseId
JOIN I_DeliveryDocument AS LH ON DF.SubsequentDocument = LH.DeliveryDocument
JOIN I_DeliveryDocumentItem AS LI ON LH.DeliveryDocument = LI.DeliveryDocument
WHERE DF.PrecedingDocumentCategory = 'C' AND DF.SubsequentDocumentCategory = 'J'

UNION ALL

-- 4. Goods Received at Warehouse
SELECT
    DF.PrecedingDocument AS "ReturnCaseId",
    'Goods Received at Warehouse' AS "ActivityName",
    MH.CreationDateTime AS "EventTime",
    MH.CreationDateTime AS "EventEndTime",
    MH.CreatedByUser AS "UserName",
    RO.OrderReason AS "ReturnReason",
    CAST(NULL AS DECIMAL(17, 2)) AS "RefundAmount",
    MI.Material AS "ProductId",
    RO.SoldToParty AS "CustomerId",
    '[Your Source System ID]' AS "SourceSystemId",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdateTimestamp"
FROM I_SDDocumentFlow AS DF
JOIN ReturnOrders RO ON DF.PrecedingDocument = RO.ReturnCaseId
JOIN I_DeliveryDocumentItem AS LI ON DF.SubsequentDocument = LI.DeliveryDocument AND DF.SubsequentDocumentItem = LI.DeliveryDocumentItem
JOIN I_MaterialDocumentItem AS MI ON LI.DeliveryDocument = MI.DeliveryDocument AND LI.DeliveryDocumentItem = MI.DeliveryDocumentItem
JOIN I_MaterialDocumentHeader AS MH ON MI.MaterialDocument = MH.MaterialDocument AND MI.MaterialDocumentYear = MH.MaterialDocumentYear
WHERE DF.SubsequentDocumentCategory = 'J' AND MH.GoodsMovementType = '[Your Return Goods Receipt MVT]' -- e.g., '651', '653'

UNION ALL

-- 5. Item Inspection Completed
SELECT 
    SDI.SalesDocument AS "ReturnCaseId",
    'Item Inspection Completed' AS "ActivityName",
    SDI.LastChangeDateTime AS "EventTime",
    SDI.LastChangeDateTime AS "EventEndTime",
    SDI.LastChangedByUser AS "UserName",
    RO.OrderReason AS "ReturnReason",
    CAST(NULL AS DECIMAL(17, 2)) AS "RefundAmount",
    SDI.Material AS "ProductId",
    RO.SoldToParty AS "CustomerId",
    '[Your Source System ID]' AS "SourceSystemId",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdateTimestamp"
FROM I_SalesDocumentItem AS SDI
JOIN ReturnOrders RO ON SDI.SalesDocument = RO.ReturnCaseId
WHERE SDI.ReturnsInspectionStatus = '4' -- 'Inspection Completed', adjust value based on your config

UNION ALL

-- 6. Return Rejected
SELECT
    SDI.SalesDocument AS "ReturnCaseId",
    'Return Rejected' AS "ActivityName",
    SDI.LastChangeDateTime AS "EventTime",
    SDI.LastChangeDateTime AS "EventEndTime",
    SDI.LastChangedByUser AS "UserName",
    RO.OrderReason AS "ReturnReason",
    CAST(NULL AS DECIMAL(17, 2)) AS "RefundAmount",
    SDI.Material AS "ProductId",
    RO.SoldToParty AS "CustomerId",
    '[Your Source System ID]' AS "SourceSystemId",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdateTimestamp"
FROM I_SalesDocumentItem AS SDI
JOIN ReturnOrders RO ON SDI.SalesDocument = RO.ReturnCaseId
WHERE SDI.SalesDocumentItemRejectionReason <> ''

UNION ALL

-- 7. Credit Memo Request Created
SELECT
    DF.PrecedingDocument AS "ReturnCaseId",
    'Credit Memo Request Created' AS "ActivityName",
    CM_REQ.CreationDateTime AS "EventTime",
    CM_REQ.CreationDateTime AS "EventEndTime",
    CM_REQ.CreatedByUser AS "UserName",
    RO.OrderReason AS "ReturnReason",
    CAST(NULL AS DECIMAL(17, 2)) AS "RefundAmount",
    I.Material AS "ProductId",
    RO.SoldToParty AS "CustomerId",
    '[Your Source System ID]' AS "SourceSystemId",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdateTimestamp"
FROM I_SDDocumentFlow AS DF
JOIN ReturnOrders RO ON DF.PrecedingDocument = RO.ReturnCaseId
JOIN I_SalesDocument AS CM_REQ ON DF.SubsequentDocument = CM_REQ.SalesDocument
JOIN I_SalesDocumentItem I ON CM_REQ.SalesDocument = I.SalesDocument
WHERE CM_REQ.SalesDocumentType = '[Your Credit Memo Request Type]' -- e.g., 'CR'

UNION ALL

-- 8. Exchange Order Created
SELECT
    DF.PrecedingDocument AS "ReturnCaseId",
    'Exchange Order Created' AS "ActivityName",
    EX_ORD.CreationDateTime AS "EventTime",
    EX_ORD.CreationDateTime AS "EventEndTime",
    EX_ORD.CreatedByUser AS "UserName",
    RO.OrderReason AS "ReturnReason",
    CAST(NULL AS DECIMAL(17, 2)) AS "RefundAmount",
    I.Material AS "ProductId",
    RO.SoldToParty AS "CustomerId",
    '[Your Source System ID]' AS "SourceSystemId",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdateTimestamp"
FROM I_SDDocumentFlow AS DF
JOIN ReturnOrders RO ON DF.PrecedingDocument = RO.ReturnCaseId
JOIN I_SalesDocument AS EX_ORD ON DF.SubsequentDocument = EX_ORD.SalesDocument
JOIN I_SalesDocumentItem I ON EX_ORD.SalesDocument = I.SalesDocument
WHERE EX_ORD.SalesDocumentType = '[Your Exchange Order Type]' -- e.g., 'OR'

UNION ALL

-- 9. Credit Memo Created
SELECT
    DF_CM.PrecedingDocument AS "ReturnCaseId",
    'Credit Memo Created' AS "ActivityName",
    BD.CreationDateTime AS "EventTime",
    BD.CreationDateTime AS "EventEndTime",
    BD.CreatedByUser AS "UserName",
    RO.OrderReason AS "ReturnReason",
    BD.TotalNetAmount AS "RefundAmount",
    BDI.Material AS "ProductId",
    RO.SoldToParty AS "CustomerId",
    '[Your Source System ID]' AS "SourceSystemId",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdateTimestamp"
FROM I_SDDocumentFlow AS DF
JOIN I_SalesDocument AS CM_REQ ON DF.SubsequentDocument = CM_REQ.SalesDocument AND CM_REQ.SalesDocumentType = '[Your Credit Memo Request Type]'
JOIN I_SDDocumentFlow AS DF_CM ON CM_REQ.SalesDocument = DF_CM.PrecedingDocument
JOIN I_BillingDocument AS BD ON DF_CM.SubsequentDocument = BD.BillingDocument
JOIN I_BillingDocumentItem AS BDI ON BD.BillingDocument = BDI.BillingDocument
JOIN ReturnOrders RO ON DF.PrecedingDocument = RO.ReturnCaseId
WHERE DF.PrecedingDocumentCategory = 'C'

UNION ALL

-- 10. Accounting Document Created
SELECT
    RO.ReturnCaseId AS "ReturnCaseId",
    'Accounting Document Created' AS "ActivityName",
    JE.CreationDateTime AS "EventTime",
    JE.CreationDateTime AS "EventEndTime",
    JE.CreatedByUser AS "UserName",
    RO.OrderReason AS "ReturnReason",
    JE.AmountInCompanyCodeCurrency AS "RefundAmount",
    JRI.ProductName AS "ProductId",
    RO.SoldToParty AS "CustomerId",
    '[Your Source System ID]' AS "SourceSystemId",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdateTimestamp"
FROM I_JournalEntry AS JE
JOIN I_JournalEntryItem JRI ON JE.AccountingDocument = JRI.AccountingDocument
JOIN I_BillingDocument BD ON JE.ReferenceDocument = BD.BillingDocument
JOIN I_SDDocumentFlow DF_CM ON BD.BillingDocument = DF_CM.SubsequentDocument
JOIN I_SalesDocument CM_REQ ON DF_CM.PrecedingDocument = CM_REQ.SalesDocument AND CM_REQ.SalesDocumentType = '[Your Credit Memo Request Type]'
JOIN I_SDDocumentFlow DF ON CM_REQ.SalesDocument = DF.SubsequentDocument
JOIN ReturnOrders RO ON DF.PrecedingDocument = RO.ReturnCaseId
WHERE JE.OriginalReferenceDocumentType = 'VBRK'

UNION ALL

-- 11. Refund Processed
SELECT
    RO.ReturnCaseId AS "ReturnCaseId",
    'Refund Processed' AS "ActivityName",
    CI.ClearingDate AS "EventTime",
    CI.ClearingDate AS "EventEndTime",
    CI.LastChangedByUser AS "UserName",
    RO.OrderReason AS "ReturnReason",
    CI.AmountInCompanyCodeCurrency AS "RefundAmount",
    JRI.ProductName AS "ProductId",
    RO.SoldToParty AS "CustomerId",
    '[Your Source System ID]' AS "SourceSystemId",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdateTimestamp"
FROM I_ClearedItem AS CI
JOIN I_JournalEntryItem JRI ON CI.AccountingDocument = JRI.AccountingDocument AND CI.FiscalYear = JRI.FiscalYear AND CI.LedgerGLLineItem = JRI.LedgerGLLineItem
JOIN I_JournalEntry JE ON JRI.AccountingDocument = JE.AccountingDocument
JOIN I_BillingDocument BD ON JE.ReferenceDocument = BD.BillingDocument
JOIN I_SDDocumentFlow DF_CM ON BD.BillingDocument = DF_CM.SubsequentDocument
JOIN I_SalesDocument CM_REQ ON DF_CM.PrecedingDocument = CM_REQ.SalesDocument AND CM_REQ.SalesDocumentType = '[Your Credit Memo Request Type]'
JOIN I_SDDocumentFlow DF ON CM_REQ.SalesDocument = DF.SubsequentDocument
JOIN ReturnOrders RO ON DF.PrecedingDocument = RO.ReturnCaseId
WHERE JE.OriginalReferenceDocumentType = 'VBRK' AND CI.ClearingDate IS NOT NULL

UNION ALL

-- 12. Return Case Closed
SELECT
    SD.SalesDocument AS "ReturnCaseId",
    'Return Case Closed' AS "ActivityName",
    SD.LastChangeDateTime AS "EventTime",
    SD.LastChangeDateTime AS "EventEndTime",
    SD.LastChangedByUser AS "UserName",
    SD.OrderReason AS "ReturnReason",
    CAST(NULL AS DECIMAL(17, 2)) AS "RefundAmount",
    I.Material AS "ProductId",
    SD.SoldToParty AS "CustomerId",
    '[Your Source System ID]' AS "SourceSystemId",
    CURRENT_UTCTIMESTAMP AS "LastDataUpdateTimestamp"
FROM I_SalesDocument AS SD
JOIN ReturnOrders RO ON SD.SalesDocument = RO.ReturnCaseId
JOIN I_SalesDocumentItem I ON SD.SalesDocument = I.SalesDocument
WHERE SD.OverallSDProcessStatus = 'C' -- 'Completed'

WITH
parameters AS (
    SELECT
        CAST('[Start date]' AS TIMESTAMP) AS start_ts,
        CAST('[End date]' AS TIMESTAMP) AS end_ts,
        CAST('[Extraction timestamp]' AS TIMESTAMP) AS extraction_ts,
        CAST('[Source system identifier]' AS NVARCHAR(100)) AS source_system_id
    FROM DUMMY
),
return_orders AS (
    SELECT
        h.[Return order number] AS return_case_id,
        i.[Return order item number] AS return_item_id,
        h.[Return order creation timestamp] AS return_created_ts,
        h.[Return order creator] AS return_creator,
        h.[Customer number] AS customer_id,
        i.[Product number] AS product_id,
        i.[Return reason] AS return_reason,
        i.[Return order quantity] AS return_quantity,
        h.[Company code] AS company_code,
        h.[Return order document type] AS return_document_type
    FROM [Your sales document header table] h
    INNER JOIN [Your sales document item table] i
        ON h.[Sales document number] = i.[Sales document number]
    CROSS JOIN parameters p
    WHERE h.[Return order creation timestamp] >= p.start_ts
      AND h.[Return order creation timestamp] < p.end_ts
      AND h.[Return order document type] IN ([Your return order document type filter])
      AND h.[Company code] IN ([Company Code filter])
),
document_flow AS (
    SELECT
        ro.return_case_id,
        ro.return_item_id,
        f.[Preceding document number] AS preceding_document_id,
        f.[Preceding item number] AS preceding_item_id,
        f.[Subsequent document number] AS subsequent_document_id,
        f.[Subsequent item number] AS subsequent_item_id,
        f.[Subsequent document category] AS subsequent_document_category,
        f.[Document flow creation timestamp] AS flow_created_ts
    FROM return_orders ro
    LEFT JOIN [Your document flow table or view] f
        ON f.[Preceding document number] = ro.return_case_id
       AND f.[Preceding item number] = ro.return_item_id
),
return_delivery AS (
    SELECT
        df.return_case_id,
        df.return_item_id,
        df.subsequent_document_id AS delivery_id,
        df.subsequent_item_id AS delivery_item_id,
        d.[Delivery creation timestamp] AS delivery_created_ts,
        d.[Delivery creator] AS delivery_creator,
        d.[Delivery completion timestamp] AS delivery_completed_ts
    FROM document_flow df
    INNER JOIN [Your delivery header table] d
        ON d.[Delivery number] = df.subsequent_document_id
    WHERE df.subsequent_document_category = '[Inbound delivery document category]'
),
credit_memo_requests AS (
    SELECT
        df.return_case_id,
        df.return_item_id,
        df.subsequent_document_id AS credit_memo_request_id,
        df.subsequent_item_id AS credit_memo_request_item_id,
        c.[Credit memo request creation timestamp] AS request_created_ts,
        c.[Credit memo request creator] AS request_creator
    FROM document_flow df
    INNER JOIN [Your credit memo request header table] c
        ON c.[Credit memo request number] = df.subsequent_document_id
    WHERE df.subsequent_document_category = '[Credit memo request document category]'
),
exchange_orders AS (
    SELECT
        df.return_case_id,
        df.return_item_id,
        df.subsequent_document_id AS exchange_order_id,
        df.subsequent_item_id AS exchange_order_item_id,
        e.[Exchange order creation timestamp] AS exchange_created_ts,
        e.[Exchange order creator] AS exchange_creator
    FROM document_flow df
    INNER JOIN [Your exchange order header table] e
        ON e.[Exchange order number] = df.subsequent_document_id
    WHERE df.subsequent_document_category = '[Exchange order document category]'
),
credit_memos AS (
    SELECT
        df.return_case_id,
        df.return_item_id,
        df.subsequent_document_id AS credit_memo_id,
        df.subsequent_item_id AS credit_memo_item_id,
        b.[Credit memo creation timestamp] AS credit_memo_created_ts,
        b.[Credit memo creator] AS credit_memo_creator,
        b.[Credit memo amount] AS refund_amount,
        b.[Accounting document number] AS accounting_document_id,
        b.[Company code] AS billing_company_code
    FROM document_flow df
    INNER JOIN [Your billing document header table] b
        ON b.[Billing document number] = df.subsequent_document_id
    WHERE df.subsequent_document_category = '[Credit memo document category]'
),
approval_events AS (
    SELECT
        ro.return_case_id,
        ro.return_item_id,
        s.[Approval timestamp] AS event_ts,
        s.[Approval user] AS user_name
    FROM return_orders ro
    INNER JOIN [Your return status table or view] s
        ON s.[Return order number] = ro.return_case_id
       AND s.[Return order item number] = ro.return_item_id
    WHERE s.[Status code] IN ([Your approved or released status values])
),
inspection_events AS (
    SELECT
        ro.return_case_id,
        ro.return_item_id,
        q.[Inspection completion timestamp] AS event_ts,
        q.[Inspection user] AS user_name
    FROM return_orders ro
    INNER JOIN [Your return inspection table or view] q
        ON q.[Return order number] = ro.return_case_id
       AND q.[Return order item number] = ro.return_item_id
    WHERE q.[Inspection status] IN ([Your completed inspection status values])
),
rejection_events AS (
    SELECT
        ro.return_case_id,
        ro.return_item_id,
        q.[Rejection timestamp] AS event_ts,
        q.[Rejection user] AS user_name
    FROM return_orders ro
    INNER JOIN [Your return inspection table or view] q
        ON q.[Return order number] = ro.return_case_id
       AND q.[Return order item number] = ro.return_item_id
    WHERE q.[Inspection outcome or rejection code] IN ([Your rejection reason values])
),
goods_receipt_events AS (
    SELECT
        rd.return_case_id,
        rd.return_item_id,
        m.[Goods receipt posting timestamp] AS event_ts,
        m.[Goods receipt posting user] AS user_name
    FROM return_delivery rd
    INNER JOIN [Your material document header table] m
        ON m.[Reference delivery number] = rd.delivery_id
    INNER JOIN [Your material document item table] mi
        ON mi.[Material document number] = m.[Material document number]
       AND mi.[Material document item number] = [Your material document item linkage]
    WHERE m.[Goods movement type] IN ([Your goods receipt movement type values])
),
accounting_events AS (
    SELECT
        cm.return_case_id,
        cm.return_item_id,
        a.[Accounting posting timestamp] AS event_ts,
        a.[Accounting user] AS user_name
    FROM credit_memos cm
    INNER JOIN [Your accounting document header table] a
        ON a.[Accounting document number] = cm.accounting_document_id
       AND a.[Company code] = cm.billing_company_code
    WHERE a.[Accounting document status] IN ([Your posted accounting status values])
),
refund_events AS (
    SELECT
        cm.return_case_id,
        cm.return_item_id,
        cl.[Clearing timestamp] AS event_ts,
        cl.[Clearing user] AS user_name
    FROM credit_memos cm
    INNER JOIN [Your customer clearing table or view] cl
        ON cl.[Cleared accounting document number] = cm.accounting_document_id
    WHERE cl.[Clearing status] IN ([Your completed clearing status values])
),
closure_events AS (
    SELECT
        ro.return_case_id,
        ro.return_item_id,
        s.[Closure timestamp] AS event_ts,
        s.[Closure user] AS user_name
    FROM return_orders ro
    INNER JOIN [Your return status table or view] s
        ON s.[Return order number] = ro.return_case_id
       AND s.[Return order item number] = ro.return_item_id
    WHERE s.[Status code] IN ([Your final closed status values])
),
events AS (
    SELECT ro.return_case_id, ro.return_item_id, 'Return Request Initiated' AS activity_name, ro.return_created_ts AS event_time, CAST(NULL AS TIMESTAMP) AS event_end_time, ro.return_creator AS user_name, ro.return_reason, CAST(NULL AS DECIMAL(19,2)) AS refund_amount, ro.product_id, ro.customer_id FROM return_orders ro
    UNION ALL
    SELECT ae.return_case_id, ae.return_item_id, 'Return Order Approved', ae.event_ts, CAST(NULL AS TIMESTAMP), ae.user_name, ro.return_reason, CAST(NULL AS DECIMAL(19,2)), ro.product_id, ro.customer_id FROM approval_events ae INNER JOIN return_orders ro ON ro.return_case_id = ae.return_case_id AND ro.return_item_id = ae.return_item_id
    UNION ALL
    SELECT rd.return_case_id, rd.return_item_id, 'Return Delivery Created', rd.delivery_created_ts, rd.delivery_completed_ts, rd.delivery_creator, ro.return_reason, CAST(NULL AS DECIMAL(19,2)), ro.product_id, ro.customer_id FROM return_delivery rd INNER JOIN return_orders ro ON ro.return_case_id = rd.return_case_id AND ro.return_item_id = rd.return_item_id
    UNION ALL
    SELECT gr.return_case_id, gr.return_item_id, 'Goods Received at Warehouse', gr.event_ts, CAST(NULL AS TIMESTAMP), gr.user_name, ro.return_reason, CAST(NULL AS DECIMAL(19,2)), ro.product_id, ro.customer_id FROM goods_receipt_events gr INNER JOIN return_orders ro ON ro.return_case_id = gr.return_case_id AND ro.return_item_id = gr.return_item_id
    UNION ALL
    SELECT ie.return_case_id, ie.return_item_id, 'Item Inspection Completed', ie.event_ts, CAST(NULL AS TIMESTAMP), ie.user_name, ro.return_reason, CAST(NULL AS DECIMAL(19,2)), ro.product_id, ro.customer_id FROM inspection_events ie INNER JOIN return_orders ro ON ro.return_case_id = ie.return_case_id AND ro.return_item_id = ie.return_item_id
    UNION ALL
    SELECT re.return_case_id, re.return_item_id, 'Return Rejected', re.event_ts, CAST(NULL AS TIMESTAMP), re.user_name, ro.return_reason, CAST(NULL AS DECIMAL(19,2)), ro.product_id, ro.customer_id FROM rejection_events re INNER JOIN return_orders ro ON ro.return_case_id = re.return_case_id AND ro.return_item_id = re.return_item_id
    UNION ALL
    SELECT cr.return_case_id, cr.return_item_id, 'Credit Memo Request Created', cr.request_created_ts, CAST(NULL AS TIMESTAMP), cr.request_creator, ro.return_reason, CAST(NULL AS DECIMAL(19,2)), ro.product_id, ro.customer_id FROM credit_memo_requests cr INNER JOIN return_orders ro ON ro.return_case_id = cr.return_case_id AND ro.return_item_id = cr.return_item_id
    UNION ALL
    SELECT eo.return_case_id, eo.return_item_id, 'Exchange Order Created', eo.exchange_created_ts, CAST(NULL AS TIMESTAMP), eo.exchange_creator, ro.return_reason, CAST(NULL AS DECIMAL(19,2)), ro.product_id, ro.customer_id FROM exchange_orders eo INNER JOIN return_orders ro ON ro.return_case_id = eo.return_case_id AND ro.return_item_id = eo.return_item_id
    UNION ALL
    SELECT cm.return_case_id, cm.return_item_id, 'Credit Memo Created', cm.credit_memo_created_ts, CAST(NULL AS TIMESTAMP), cm.credit_memo_creator, ro.return_reason, cm.refund_amount, ro.product_id, ro.customer_id FROM credit_memos cm INNER JOIN return_orders ro ON ro.return_case_id = cm.return_case_id AND ro.return_item_id = cm.return_item_id
    UNION ALL
    SELECT ac.return_case_id, ac.return_item_id, 'Accounting Document Created', ac.event_ts, CAST(NULL AS TIMESTAMP), ac.user_name, ro.return_reason, cm.refund_amount, ro.product_id, ro.customer_id FROM accounting_events ac INNER JOIN return_orders ro ON ro.return_case_id = ac.return_case_id AND ro.return_item_id = ac.return_item_id LEFT JOIN credit_memos cm ON cm.return_case_id = ac.return_case_id AND cm.return_item_id = ac.return_item_id
    UNION ALL
    SELECT rf.return_case_id, rf.return_item_id, 'Refund Processed', rf.event_ts, CAST(NULL AS TIMESTAMP), rf.user_name, ro.return_reason, cm.refund_amount, ro.product_id, ro.customer_id FROM refund_events rf INNER JOIN return_orders ro ON ro.return_case_id = rf.return_case_id AND ro.return_item_id = rf.return_item_id LEFT JOIN credit_memos cm ON cm.return_case_id = rf.return_case_id AND cm.return_item_id = rf.return_item_id
    UNION ALL
    SELECT ce.return_case_id, ce.return_item_id, 'Return Case Closed', ce.event_ts, CAST(NULL AS TIMESTAMP), ce.user_name, ro.return_reason, cm.refund_amount, ro.product_id, ro.customer_id FROM closure_events ce INNER JOIN return_orders ro ON ro.return_case_id = ce.return_case_id AND ro.return_item_id = ce.return_item_id LEFT JOIN credit_memos cm ON cm.return_case_id = ce.return_case_id AND cm.return_item_id = ce.return_item_id
)
SELECT
    e.return_case_id AS "ReturnCaseId",
    e.activity_name AS "ActivityName",
    e.event_time AS "EventTime",
    e.event_end_time AS "EventEndTime",
    e.user_name AS "UserName",
    e.return_reason AS "ReturnReason",
    e.refund_amount AS "RefundAmount",
    e.product_id AS "ProductId",
    e.customer_id AS "CustomerId",
    p.source_system_id AS "SourceSystemId",
    p.extraction_ts AS "LastDataUpdateTimestamp"
FROM events e
CROSS JOIN parameters p
WHERE e.event_time IS NOT NULL
ORDER BY e.return_case_id, e.event_time, e.activity_name;