İade ve Geri Ödeme İşleme Veri Şablonunuz

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