Your Returns & Refund Processing Data Template
Your Returns & Refund Processing Data Template
- Recommended attributes to collect
- Key activities to track
- Extraction guidance
Returns & Refund Processing Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Name ActivityName | The name of a specific business activity or event that occurred within the return and refund process. | ||
| Description This attribute describes a single step or milestone in the returns lifecycle. Activities represent the work being done, such as 'Return Order Approved' or 'Item Inspection Completed'. These are derived from status changes, document creation, or specific user actions recorded in SAP S/4HANA. Analyzing the sequence and frequency of these activities is the core of process mining. It helps visualize the process map, identify common and rare process paths, and pinpoint activities that are frequently repeated, indicating rework or inefficiencies. Why it matters Activities form the backbone of the process map, allowing for the visualization and analysis of the process flow, bottlenecks, and variations. Where to get Activity names are typically derived from a combination of data, such as document status changes in tables like VBUK/VBUP, creation events in header tables like VBAK (sales documents) and BKPF (accounting documents), and goods movement statuses in MSEG. Examples Return Request InitiatedGoods Received at WarehouseCredit Memo CreatedRefund Processed | |||
| Event Time EventTime | The precise timestamp indicating when a specific activity occurred. | ||
| Description Event Time captures the date and time that a business event was recorded in the system. This timestamp is crucial for ordering activities chronologically and for all time-based analysis. In process mining, this attribute is used to calculate cycle times between activities, identify the duration of each step, and analyze process performance over time. It is the basis for discovering bottlenecks, monitoring SLA adherence, and understanding the temporal dynamics of the returns process. Why it matters This timestamp is essential for ordering events, calculating all durations and cycle times, and identifying process delays. Where to get Typically sourced from date and time fields associated with document creation or status changes, such as ERDAT (Creation Date) and ERZET (Creation Time) in tables like VBAK, LIKP, and BKPF, or the posting date (BUDAT) in accounting documents. Examples 2023-10-26T10:05:00Z2023-10-27T14:30:15Z2023-10-28T09:00:00Z | |||
| Return Case ID ReturnCaseId | The unique identifier for a single customer return process, linking all related activities from initiation to closure. | ||
| Description The Return Case ID serves as the primary identifier that groups all events and activities belonging to a single return instance. Each customer return request is assigned a unique ID, which allows for end-to-end tracking of the entire process. In process mining, this attribute is fundamental for reconstructing the process flow. It enables the analysis of case durations, process variants, and bottlenecks by connecting disparate events like 'Return Request Initiated', 'Goods Received', and 'Refund Processed' into a coherent timeline for each specific return. Why it matters This is the essential key for tracking a return from start to finish, enabling all case-level analysis, including cycle time and process variant discovery. Where to get This is typically the sales document number (VBELN) from the returns order header table VBAK, where the document category (VBTYP) indicates a return. Examples 600001896000019060000191 | |||
| Last Data Update LastDataUpdateTimestamp | The timestamp indicating when the data for this event was last refreshed or extracted. | ||
| Description This attribute records the date and time of the last data extraction or update. It provides metadata about the freshness of the dataset being analyzed. It is important for understanding the timeliness of the process mining analysis. Users can see how current the data is, which is especially relevant for operational monitoring and dashboards that track ongoing cases. Why it matters Indicates the freshness of the data, which is critical for ensuring that analyses and dashboards are based on up-to-date information. Where to get This is typically generated and stamped on the dataset at the time of data extraction by the ETL or data pipeline tool. Examples 2023-11-01T02:00:00Z2023-11-02T02:00:00Z | |||
| Source System ID SourceSystemId | Identifier for the source system from which the data was extracted. | ||
| Description This attribute specifies the system of record where the event data originated. For this process, it would typically be the SAP S/4HANA instance ID. In environments with multiple systems, this field is critical for data lineage, troubleshooting, and ensuring data integrity. It helps differentiate data if returns are processed across different ERP instances or integrated with external systems like a warehouse management system. Why it matters Provides crucial context for data origin and lineage, especially in multi-system landscapes, ensuring data traceability and trust. Where to get This value is usually static and configured during data extraction. It can be retrieved from the SAP system's administrative information, such as the System ID (SID). Examples S4H_PROD_100S4Q_DEV_200 | |||
| Customer ID CustomerId | The unique identifier for the customer initiating the return. | ||
| Description This attribute identifies the customer who requested the return. It links the process instance to a specific party in the customer master data. Analyzing returns by customer helps identify patterns, such as customers with unusually high return rates, which might indicate fraudulent behavior or dissatisfaction. It also enables segmentation of the returns process based on customer type, value, or history, allowing for tailored service levels. Why it matters Connects returns to specific customers, allowing for analysis of customer behavior, segmentation, and identification of frequent returners. Where to get Found in the customer number field (KUNNR) in the return order header table (VBAK). Examples CUST-001234CUST-005678CUST-009012 | |||
| Event End Time EventEndTime | The timestamp marking the completion of an activity, used for calculating its duration. | ||
| Description While StartTime (EventTime) marks the beginning of an activity, EventEndTime marks its conclusion. For many system-generated events, the start and end times are identical, representing an instantaneous occurrence. However, for activities that have a measurable duration, like 'Item Inspection', this attribute is crucial. This attribute allows for the direct calculation of activity processing time. This is fundamental for performance analysis, helping to identify which specific steps, not just the gaps between them, are consuming the most time. Why it matters Enables the precise calculation of individual activity durations, which is key to pinpointing inefficiencies within specific process steps. Where to get This is often derived. For some activities, it might be a separate field. More commonly, it is the StartTime of the subsequent activity in the case. Examples 2023-10-26T11:25:30Z2023-10-27T15:00:00Z2023-10-28T09:10:45Z | |||
| Product ID ProductId | The unique identifier for the item being returned. | ||
| Description This attribute specifies the material or product that is the subject of the return. It links the return process to a specific item in the product catalog. Analyzing returns by product is fundamental to identifying items with high return rates, which may indicate quality defects, poor product descriptions, or manufacturing issues. This data helps businesses make informed decisions about product design, supplier management, and inventory strategy. Why it matters Links the return process to specific products, enabling analysis of item-level return rates and identification of quality or description issues. Where to get Found in the material number field (MATNR) in the return order item table (VBAP) or the return delivery item table (LIPS). Examples FG-10023HW-45981SW-LICENSE-PREM | |||
| Refund Amount RefundAmount | The final monetary value of the refund issued to the customer. | ||
| Description This attribute represents the actual amount credited or refunded to the customer upon completion of the return process. This value is recorded in financial documents like credit memos. This is a key financial metric used in various analyses. It is essential for the 'Refund Amount Discrepancy Analysis' dashboard to compare against the requested amount. It also allows for segmenting returns by value to identify if high-value returns follow a different process or take longer to resolve. Why it matters Tracks the financial impact of returns and is essential for analyzing refund accuracy, identifying high-value cases, and understanding overall costs. Where to get Sourced from the net value field (NETWR) of the credit memo document, found in tables like VBRK (Billing Document Header) or BSEG (Accounting Document Segment). Examples 125.50999.0049.99 | |||
| Return Reason ReturnReason | The reason provided by the customer for returning the item. | ||
| Description This attribute captures the customer's stated reason for the return, such as 'Defective Item', 'Wrong Size', or 'No Longer Needed'. This is typically selected from a predefined list of reason codes during the return initiation process. Analyzing return reasons is crucial for identifying product quality issues, improving product descriptions, or refining sales processes. It provides direct insight into customer dissatisfaction and helps prioritize areas for business improvement to reduce the overall return rate. Why it matters Provides critical insight into why returns are happening, enabling root cause analysis to address product quality, fulfillment errors, or customer expectation gaps. Where to get Typically stored in the return sales order item table (VBAP) in the field ABGRU (Reason for rejection of sales documents). Examples 001 - Poor Quality002 - Damaged in Transit005 - Incorrect Item Shipped | |||
| User Name UserName | The user ID of the employee who executed the activity. | ||
| Description This attribute identifies the specific user or system agent responsible for completing a task, such as approving a return or creating a credit memo. In SAP, this is often captured in fields that log the user who created or changed a document. Analyzing by user helps identify high-performing individuals or teams, training needs, and workload distribution. It is also essential for investigating deviations, as it links process actions to specific individuals, supporting compliance and auditing efforts. Why it matters Attributes process activities to specific users, enabling analysis of team performance, workload, and compliance. Where to get Commonly found in document header tables, such as ERNAM (Created by) in VBAK (Sales Orders), LIKP (Deliveries), and BKPF (Accounting Docs). User details can be enriched from user master table USR21. Examples CBROWNASMITHWF_BATCH | |||
| Case Cycle Time CycleTime | The total time elapsed from the initiation to the closure of a return case. | ||
| Description This calculated metric measures the end-to-end duration of the entire returns process for a single case. It is typically calculated as the time difference between the first and last events, such as from 'Return Request Initiated' to 'Return Case Closed'. Cycle Time is a primary KPI for process efficiency. It is used in the 'Overall Return Cycle Time' dashboard to monitor performance, set benchmarks, and identify trends. Analyzing the factors that correlate with longer cycle times, such as product type or return reason, can reveal systemic inefficiencies. Why it matters This is a fundamental KPI for measuring overall process efficiency and directly impacts customer satisfaction and operational costs. Where to get This is a calculated field. It is computed by taking the difference between the maximum and minimum EventTime for each unique ReturnCaseId. Examples 5d 4h 30m12d 2h 15m2d 8h 0m | |||
| Credit Memo Number CreditMemoNumber | The unique identifier for the credit memo document, which authorizes the refund. | ||
| Description A credit memo is the billing document that officially credits the customer's account for the returned items. This attribute is the unique number of that financial document. Tracking the Credit Memo Number is essential for analyzing the financial settlement part of the returns process. It marks a critical milestone, often triggering the actual refund payment, and is necessary for financial reconciliation and auditing. Why it matters Represents the official financial transaction for the refund, critical for tracking the final stages of the process and for financial auditing. Where to get This is the billing document number (VBELN) from the billing document header table (VBRK), where the document category indicates a credit memo. Examples 900003459000034690000347 | |||
| Is Automated IsAutomated | A flag indicating whether an activity was performed by a system or a human. | ||
| Description This boolean attribute distinguishes between activities executed automatically by a system, such as a workflow or background job, and those performed manually by a user. It is essential for calculating the 'Automated Refund Approval Rate' KPI and for identifying opportunities to increase automation. By filtering for manual tasks, businesses can focus their process improvement efforts on areas where automation could deliver the most significant benefits in terms of speed, cost, and accuracy. Why it matters Distinguishes between manual and automated tasks, which is crucial for identifying automation opportunities and measuring the impact of digital transformation. Where to get This is typically derived based on the User Name. For example, if the user is 'WF_BATCH' or another system ID, the activity is flagged as automated. Examples truefalse | |||
| Is Rework IsRework | A flag indicating if an activity in a case is a repetition of a previous activity. | ||
| Description This calculated boolean attribute identifies instances of rework, where an activity is performed more than once within the same case. For example, if an item inspection has to be repeated or a credit memo is created, cancelled, and then re-created. This attribute is essential for the 'Refund Processing Rework Analysis' dashboard and the 'Refund Rework Rate' KPI. It helps quantify process inefficiency by highlighting activities that are prone to errors or require multiple attempts, pointing to areas that need better controls or training. Why it matters Highlights process inefficiencies and errors by flagging repeated work, allowing for targeted improvements to reduce waste and delays. Where to get This flag is typically calculated by the process mining tool itself or can be pre-calculated in the data transformation. It checks if the same activity name has already appeared earlier in the same case. Examples truefalse | |||
| Item Inspection Outcome ItemInspectionOutcome | The result of the physical inspection of the returned item. | ||
| Description This attribute records the outcome of the inspection process performed after the goods are received at the warehouse. Common outcomes include 'Accepted', 'Rejected - Damaged', or 'Accepted - Resalable'. This data provides crucial context for subsequent process steps. It determines whether a full refund, a partial refund, or no refund is issued. Analyzing this outcome helps identify reasons for rejections and can provide feedback on product packaging or shipping partners if items are frequently damaged in transit. Why it matters Explains the decision-making process behind refund approvals or rejections, providing valuable data on item condition and reasons for refund adjustments. Where to get This information may be recorded in a quality management module (QM) inspection lot, or as a status or reason code on the return delivery line item (LIPS). It may also exist in a custom field. Examples Accepted - ResalableAccepted - To be refurbishedRejected - Customer induced damageRejected - Wrong item returned | |||
| Processing Agent ProcessingAgent | The specific agent or resource group responsible for handling a manual activity. | ||
| Description This attribute identifies the person or team that performed a given task. It can be more specific than the 'User Name' by referring to a role or a team, especially in a shared services environment. This is valuable for the 'Refund Approval Efficiency' dashboard to analyze performance across different agents or teams. It helps in understanding workload distribution, identifying training needs, and recognizing top performers or teams that may have best practices to share. Why it matters Allows for performance analysis at the agent or team level, helping to manage workload, identify training opportunities, and improve efficiency. Where to get This information might be available through SAP Business Partner functions if agents are assigned, or it could be derived from the user's department or role in the HR organizational structure. Examples Tier 1 SupportWarehouse Inspection TeamFinance Dept - AP | |||
| Refund SLA Adherence RefundSlaAdherence | A flag that indicates if the refund was processed within the Service Level Agreement (SLA) target. | ||
| Description This calculated attribute checks whether the 'Refund Processed' activity occurred on or before the 'Refund SLA Target Date'. It provides a simple true or false indicator of SLA compliance for each case. This is the core metric for the 'Refund SLA Adherence Monitoring' dashboard and the 'Refund SLA Adherence Rate' KPI. It helps measure performance against customer commitments and identifies cases that failed to meet expectations, allowing for root cause analysis of delays. Why it matters Directly measures performance against customer-facing commitments, making it a critical indicator of service quality and customer satisfaction. Where to get Calculated by comparing the EventTime of the 'Refund Processed' activity to the 'RefundSlaTargetDate' for each case. Examples truefalse | |||
| Refund SLA Target Date RefundSlaTargetDate | The target date by which the refund for the return case should be processed. | ||
| Description This attribute defines the Service Level Agreement (SLA) deadline for processing the refund. This date is usually calculated based on business rules, for example, a certain number of days after the return is approved or goods are received. This field is the basis for the 'Refund SLA Adherence Monitoring' dashboard and the associated KPI. It allows for the proactive tracking of cases at risk of breaching their SLA and for analyzing the root causes of delays, ultimately helping to improve customer satisfaction. Why it matters Provides the baseline for measuring SLA compliance, helping to monitor performance, prioritize aging cases, and improve customer satisfaction. Where to get This is almost always a derived field. The logic would be based on a key date (e.g., return request creation date) plus a duration defined by business rules, which might depend on factors like customer type or return reason. Examples 2023-11-10T23:59:59Z2023-11-15T23:59:59Z2023-11-20T23:59:59Z | |||
| Requested Refund Amount RequestedRefundAmount | The refund amount initially requested or expected at the start of the process. | ||
| Description This attribute captures the value of the goods being returned as per the initial return request. It serves as a baseline against which the final refunded amount can be compared. This field is specifically required for the 'Refund Amount Discrepancy Analysis' dashboard. Comparing the requested amount with the actual refund amount helps identify issues like partial refunds due to item damage, restocking fees, or other adjustments, ensuring financial accuracy and transparency. Why it matters Serves as a baseline to measure refund accuracy, helping to identify and analyze discrepancies between expected and actual refund values. Where to get Typically sourced from the net value of the items on the initial return sales order. This would be the net value (NETWR) from the corresponding line items in the VBAP table. Examples 125.501050.0049.99 | |||
| Return Delivery Number ReturnDeliveryNumber | The unique identifier for the return delivery document. | ||
| Description When a customer returns goods physically, a return delivery document is created in SAP to manage the inbound logistics. This attribute is the unique number of that document. This ID is important for tracking the physical movement of the returned goods. It connects the financial and logistical aspects of the return, allowing for detailed analysis of the goods receipt and inspection phases of the process. Why it matters Provides a key link between the return order and the physical receipt of goods, crucial for analyzing logistics and warehouse processing times. Where to get This is the delivery document number (VBELN) from the delivery header table (LIKP), where the document category indicates a return delivery. Examples 840000128400001384000014 | |||
| Return Order Status ReturnOrderStatus | The current overall status of the return case. | ||
| Description This attribute provides a high-level status of the return case at any given point in time, such as 'Open', 'In Process', or 'Closed'. This is often an aggregate status derived from the last completed major milestone. This is essential for the 'Current Return Case Status Dashboard', which provides an operational view of the current workload and case distribution. It helps managers understand how many cases are in each stage of the process, enabling better resource allocation and workload management. Why it matters Provides a snapshot of where each case is in the process, which is essential for operational dashboards that track current workload and status. Where to get Derived from the status fields on the relevant documents. For example, from the header status (GBSTK) or item status (LFSTK) in the related sales order (VBUK/VBUP) or delivery documents. Examples Awaiting Goods ReceiptPending InspectionPending RefundClosed | |||
| Return Policy Adherence ReturnPolicyAdherence | A flag indicating if the return case complies with the defined return policy. | ||
| Description This calculated boolean attribute indicates whether a return meets the criteria set out in the applicable return policy. The logic could check, for example, if the return was initiated within the allowed timeframe or if the return reason is valid for the product. This attribute directly supports the 'Return Policy Compliance Overview' dashboard. It quantifies compliance rates and allows for drilling down into non-compliant cases to understand the reasons for deviations, helping to enforce policies more effectively. Why it matters Quantifies compliance with business rules, helping to identify and reduce policy violations that may impact profitability or create process exceptions. Where to get Calculated based on business rules. For example, (Return Initiation Date - Original Purchase Date) <= [Allowed Return Days]. This requires the Original Purchase Date and the policy rules. Examples truefalse | |||
| Return Policy ID ReturnPolicyId | The identifier of the return policy that applies to this specific return case. | ||
| Description This attribute indicates which specific return policy or set of rules is applicable to the transaction. Policies can vary based on product type, customer segment, or time since purchase. This data is essential for the 'Return Policy Compliance Overview'. By associating each case with a policy, the system can automatically check for adherence to rules, such as return deadlines or item condition requirements, and flag deviations for analysis. Why it matters Enables automated compliance checking against business rules, helping to ensure that returns are processed consistently and according to policy. Where to get This is often not a standard SAP field and may need to be derived based on business logic using data like product type, customer, and sales date. It could be stored in a custom field if implemented. Examples STD-30DAYELEC-90DAY-WARRANTYFINAL-SALE-DEFECT | |||
| Sales Organization SalesOrganization | The organizational unit responsible for the original sale and the return. | ||
| Description The Sales Organization is a key organizational structure element in SAP that represents a unit responsible for the sale and distribution of products and services. It is assigned to the return transaction. This attribute allows for filtering and comparing the returns process across different business units, regions, or divisions. It helps to identify if certain sales organizations have higher return rates or less efficient return handling processes, providing a basis for organizational performance analysis. Why it matters Enables comparison of return process performance and rates across different business units, regions, or sales channels. Where to get Found in the sales organization field (VKORG) in the return order header table (VBAK). Examples 10002100US01 | |||
Returns & Refund Processing Activities
| Activity | Description | ||
|---|---|---|---|
| Credit Memo Created | This is the creation of the official billing document that credits the customer's account for the returned item. This is an explicit event captured when the credit memo is generated from the credit memo request. | ||
| Why it matters Creation of the credit memo is a critical financial milestone. It confirms the amount to be refunded and authorizes the payment process to begin. Where to get Captured from the creation of a billing document in table VBRK with a document category indicating a credit memo. This is linked to the credit memo request in the VBFA table. Capture Event is recorded upon saving a new credit memo billing document (e.g., using transaction VF01). Event type explicit | |||
| Credit Memo Request Created | Following a successful inspection, this activity marks the creation of a request to issue a credit to the customer. This is captured as a new sales document, a credit memo request, which references the original return order. | ||
| Why it matters This is the trigger for the financial settlement part of the returns process. Analyzing the time from inspection to this step reveals efficiency in the handoff from logistics to finance. Where to get Captured from the creation of a sales document in table VBAK with a document category for Credit Memo Request. The link to the return is maintained in the document flow table VBFA. Capture Event is recorded upon saving a new credit memo request document. Event type explicit | |||
| Goods Received at Warehouse | This event marks the physical receipt of the returned item at the warehouse or processing center. It is captured explicitly when a Post Goods Receipt (PGR) is executed against the return delivery, creating a material document. | ||
| Why it matters This is a critical milestone that begins the clock for inspection and disposition. Delays prior to this point are customer-driven, while delays after are internal. Where to get Captured from the material document tables MSEG and MKPF for the goods receipt movement type associated with returns. The posting date (MKPF-BUDAT) indicates the event time. Capture Event corresponds to the posting of a goods receipt for the return delivery. Event type explicit | |||
| Item Inspection Completed | Represents the completion of the quality and condition assessment of the returned goods. In Advanced Returns Management, this is often an explicit step that records the inspection outcome and determines the subsequent action, like refund or scrap. | ||
| Why it matters The duration and outcome of the inspection directly impact refund processing time and inventory management. This activity is vital for analyzing inspection efficiency and rework. Where to get In SAP Advanced Returns Management (ARM), this can be an explicit event from the inspection transaction. It may also be inferred from a status change on the return order item indicating the inspection result. Capture Captured from transaction logs or status changes related to the logistical follow-up activities in ARM. Event type explicit | |||
| Refund Processed | This activity marks the final step of the refund process, where the financial credit is cleared, signifying that payment has been sent to the customer. This is inferred by the creation of a clearing document in the finance module that settles the open credit on the customer's account. | ||
| Why it matters This is the moment the customer is actually paid. The time taken to reach this step from return initiation is a major driver of customer satisfaction and is key for measuring SLA adherence. Where to get Inferred from the clearing document information in the financial accounting line item table BSEG. The clearing date (BSEG-AUGDT) on the customer line item associated with the credit memo indicates when the refund was processed. Capture Inferred from the clearing date field being populated for the accounting document associated with the credit memo. Event type inferred | |||
| Return Case Closed | This is the final activity, indicating that the returns process is complete and no further actions are expected for the case. This is typically inferred when the return order document reaches a final, closed status in the system. | ||
| Why it matters This event defines the end of the process lifecycle, enabling the calculation of the total end-to-end cycle time. It confirms that the case has been fully resolved. Where to get Inferred from the overall status of the return order in table VBAK or its items in VBAP reaching a 'Complete' or 'Closed' state. This is determined by the system's status management configuration. Capture Inferred from the return order document status changing to 'Complete'. Event type inferred | |||
| Return Request Initiated | This is the starting point of the returns process, where a return order is formally created in the system. This event is captured explicitly when a new sales document of the return order type is saved in SAP S/4HANA. | ||
| Why it matters This activity marks the official start of the return case lifecycle. Analyzing the time from this event to closure is crucial for measuring the overall return cycle time and customer experience. Where to get This is an explicit event captured from the creation of a sales document in table VBAK where the document category (VBAK-VBTYP) indicates a return order. The creation timestamp is VBAK-ERDAT. Capture Event is recorded upon saving a new return sales order (e.g., using transaction VA01). Event type explicit | |||
| Accounting Document Created | This event occurs when the credit memo is successfully posted to the financial accounting module. It creates corresponding entries in the general ledger, making the credit official from an accounting perspective. | ||
| Why it matters This activity confirms that the credit has been integrated into the financial system. The time between credit memo creation and accounting posting can highlight issues in the billing-to-finance interface. Where to get Captured from the creation of a document header in the accounting table BKPF, which is linked to the credit memo in VBRK (VBRK-BELNR). Capture Event is recorded upon the successful posting of the billing document to Financial Accounting. Event type explicit | |||
| Exchange Order Created | This activity represents an alternative resolution where, instead of a refund, a new sales order is created to ship a replacement item to the customer. This is captured when a new sales order is created with a reference to the original return. | ||
| Why it matters This activity helps differentiate between returns for refund and returns for exchange, which have different process paths and customer outcomes. It is key for variant analysis. Where to get Captured from the creation of a new sales document in VBAK that is linked to the return order in the document flow table (VBFA). Capture Event is recorded upon saving a new sales order document that is designated as a replacement. Event type explicit | |||
| Return Delivery Created | This activity signifies the creation of an inbound delivery document, which is used to manage the physical receipt of the returned goods. The system captures this as an explicit creation event for a delivery document referencing the return order. | ||
| Why it matters This step is a key logistical milestone. The time between return approval and delivery creation highlights the efficiency of communicating return information to the warehouse or receiving department. Where to get Captured from the creation of a delivery header in table LIKP, linked to the preceding return order via the document flow table VBFA. Capture Event is recorded upon saving a new return delivery document (e.g., using transaction VL01N). Event type explicit | |||
| Return Order Approved | Represents the formal approval or release of the return order, allowing it to proceed to the next stage. This is typically inferred from a status change on the sales document header or item, indicating it has been released from any blocks. | ||
| Why it matters Approval steps can be a significant source of delay. Tracking this activity helps identify bottlenecks in the initial authorization phase of the returns process. Where to get Inferred from status management tables or status fields directly within the VBAK or VBAP tables. A change in a release status or the removal of a delivery block (VBAP-LIFSP) can signify approval. Capture Inferred from a change in the return order's header or item status fields indicating release or approval. Event type inferred | |||
| Return Rejected | Indicates that the returned item did not meet the return policy criteria and the request for a refund or credit has been denied. This is typically captured by a specific status or reason code being applied to the return order item after inspection. | ||
| Why it matters Tracking rejections helps in analyzing compliance with return policies and identifying common reasons for denial. It is a key exception path in the process. Where to get Inferred from a rejection reason (VBAP-ABGRU) being set on the return order item or a specific status being assigned during the inspection process in Advanced Returns Management. Capture Inferred from setting a rejection reason or a specific 'rejected' status on the return document item. Event type inferred | |||
Extraction Guides
Steps
- Prerequisites Verification: Ensure the user account executing the extraction has the necessary authorizations in SAP S/4HANA to access the required Core Data Services (CDS) Views. Key views include I_SalesDocument, I_SalesDocumentItem, I_SDDocumentFlow, I_DeliveryDocument, I_MaterialDocumentHeader, I_BillingDocument, I_JournalEntry, and I_ClearedItem.
- Access Query Tool: Log in to your preferred SQL client or data integration tool that has a connection established to the SAP S/4HANA database. This could be SAP's own tools like SAP Analytics Cloud or a third-party ETL platform.
- Set Query Parameters: Before executing, you must modify the provided SQL query. Locate the placeholder values and replace them with the correct parameters for your environment. This includes setting the
[Start Date],[End Date],[Your Source System ID],[Your Return Order Type], and other document type or company code filters. - Execute the Extraction Query: Copy the complete SQL query and execute it in your tool. The query is designed to collect all specified activities into a single dataset by unioning together results from multiple select statements.
- Understand the Query Logic: Each
SELECTblock in theUNION ALLstructure is responsible for extracting one specific activity. It joins multiple CDS views to gather the required attributes, assigns a fixed string as theActivityName, and selects the relevant timestamp for theEventTime. - Review the Raw Data: After the query completes, perform a brief review of the output. Check for a reasonable number of rows and ensure that key columns like
ReturnCaseId,ActivityName, andEventTimeare populated as expected. - Data Transformation: The query is structured to produce a flat event log format. No significant structural transformations are typically needed. However, you might need to adjust timestamp formats or data types depending on the requirements of your target system.
- Export the Event Log: Export the query result set as a CSV file. Ensure the file uses UTF-8 encoding to prevent character issues, especially with user names or product descriptions.
- Upload to Process Mining Tool: The resulting CSV file is now ready to be uploaded to your process mining platform, such as ProcessMind. Map the columns from the file to the corresponding fields in the tool, for example,
ReturnCaseIdto Case ID,ActivityNameto Activity, andEventTimeto Timestamp.
Configuration
- Prerequisites: The executing user needs display authorizations for objects related to sales documents (VBAK), deliveries (LIKP), billing (VBRK), and accounting (BSEG, BKPF). Access to the underlying CDS views is essential.
- Data Scope Filters: It is critical to filter the query for specific document types to isolate the returns process. Configure the placeholders for return order types (e.g., 'RE'), credit memo request types (e.g., 'G2'), and exchange order types (e.g., 'SO'). Filtering by
CompanyCodeorSalesOrganizationis also highly recommended to limit the data scope. - Date Range Filtering: To manage performance and data volume, always apply a date range filter. Start with a recent period of 3 to 6 months of data. The query uses the creation date of the initial return order (
I_SalesDocument.CreationDate) as the primary filter condition. - Performance Considerations: This is a comprehensive query that joins multiple large CDS views. Execution can be resource-intensive on the source S/4HANA system. Schedule the extraction during off-peak business hours to minimize impact. For very large datasets, consider incremental loading strategies.
a Sample Query sql
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'Steps
- Prerequisites Verification: Ensure the user account executing the extraction has the necessary authorizations in SAP S/4HANA to access the required Core Data Services (CDS) Views. Key views include I_SalesDocument, I_SalesDocumentItem, I_SDDocumentFlow, I_DeliveryDocument, I_MaterialDocumentHeader, I_BillingDocument, I_JournalEntry, and I_ClearedItem.
- Access Query Tool: Log in to your preferred SQL client or data integration tool that has a connection established to the SAP S/4HANA database. This could be SAP's own tools like SAP Analytics Cloud or a third-party ETL platform.
- Set Query Parameters: Before executing, you must modify the provided SQL query. Locate the placeholder values and replace them with the correct parameters for your environment. This includes setting the
[Start Date],[End Date],[Your Source System ID],[Your Return Order Type], and other document type or company code filters. - Execute the Extraction Query: Copy the complete SQL query and execute it in your tool. The query is designed to collect all specified activities into a single dataset by unioning together results from multiple select statements.
- Understand the Query Logic: Each
SELECTblock in theUNION ALLstructure is responsible for extracting one specific activity. It joins multiple CDS views to gather the required attributes, assigns a fixed string as theActivityName, and selects the relevant timestamp for theEventTime. - Review the Raw Data: After the query completes, perform a brief review of the output. Check for a reasonable number of rows and ensure that key columns like
ReturnCaseId,ActivityName, andEventTimeare populated as expected. - Data Transformation: The query is structured to produce a flat event log format. No significant structural transformations are typically needed. However, you might need to adjust timestamp formats or data types depending on the requirements of your target system.
- Export the Event Log: Export the query result set as a CSV file. Ensure the file uses UTF-8 encoding to prevent character issues, especially with user names or product descriptions.
- Upload to Process Mining Tool: The resulting CSV file is now ready to be uploaded to your process mining platform, such as ProcessMind. Map the columns from the file to the corresponding fields in the tool, for example,
ReturnCaseIdto Case ID,ActivityNameto Activity, andEventTimeto Timestamp.
Configuration
- Prerequisites: The executing user needs display authorizations for objects related to sales documents (VBAK), deliveries (LIKP), billing (VBRK), and accounting (BSEG, BKPF). Access to the underlying CDS views is essential.
- Data Scope Filters: It is critical to filter the query for specific document types to isolate the returns process. Configure the placeholders for return order types (e.g., 'RE'), credit memo request types (e.g., 'G2'), and exchange order types (e.g., 'SO'). Filtering by
CompanyCodeorSalesOrganizationis also highly recommended to limit the data scope. - Date Range Filtering: To manage performance and data volume, always apply a date range filter. Start with a recent period of 3 to 6 months of data. The query uses the creation date of the initial return order (
I_SalesDocument.CreationDate) as the primary filter condition. - Performance Considerations: This is a comprehensive query that joins multiple large CDS views. Execution can be resource-intensive on the source S/4HANA system. Schedule the extraction during off-peak business hours to minimize impact. For very large datasets, consider incremental loading strategies.
a Sample Query sql
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'