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 event or step that occurred within the returns process. | ||
| Description This attribute describes a single, distinct action or status change in the return lifecycle, such as 'Return Order Created', 'Goods Receipt Posted', or 'Credit Memo Created'. These activities form the building blocks of the process map. Analyzing the sequence and frequency of these activities helps identify the most common process paths, deviations, and rework loops. It is crucial for understanding what happens within a case and in what order, forming the basis for all process mining analysis. Why it matters Activities define the steps in the process. Analyzing their sequence, duration, and frequency is the core of process mining and reveals how work is actually done. Where to get Derived from status changes in tables like VBUK/VBUP, document creation events (e.g., in VBAK, LIKP, VBRK), or change logs in CDHDR/CDPOS tables. Examples Return Order CreatedGoods Receipt PostedCredit Memo CreatedReturn Order Item Completed | |||
| Event Time EventTime | The exact date and time when the activity occurred. | ||
| Description This timestamp marks the precise moment a business event happened. It is recorded for every activity in the process, providing the chronological sequence of events within a case. Event Time is critical for all time-based analysis, including calculating cycle times between activities, identifying bottlenecks where time is spent waiting, and measuring overall case duration. It enables performance analysis and compliance checks against service level agreements (SLAs). Why it matters This timestamp is essential for calculating all durations, analyzing process performance, identifying bottlenecks, and understanding the timeline of each return case. Where to get Sourced from various date and time fields across SAP tables. For creation events, it's often ERDAT and ERZET fields (e.g., in VBAK). For change events, it's UDATE and UTIME in the CDHDR table. Examples 2023-04-15T10:22:05Z2023-04-16T14:01:30Z2023-04-18T09:15:00Z | |||
| Return Case ID ReturnCaseId | The unique identifier for a customer's return request, linking all related activities and documents. | ||
| Description The Return Case ID serves as the primary key for tracking the entire lifecycle of a return process from initiation to closure. Each ID corresponds to a specific customer return, encompassing all associated events such as order creation, delivery, inspection, and credit memo processing. In process analysis, this attribute is fundamental for constructing the process map. It allows the system to group individual events into end-to-end case journeys, enabling the analysis of process variants, cycle times, and bottlenecks for each distinct return instance. Why it matters This is the essential case identifier that connects all steps of a return journey, making it possible to analyze the end-to-end process flow and performance. Where to get This is typically the Return Order number from the Sales and Distribution (SD) module. Found in table VBAK (Sales Document Header Data), field VBELN, where the document category (VBAK-VBTYP) is 'H' for Returns. Examples 600001236000045660000789 | |||
| Last Data Update LastDataUpdate | The timestamp indicating when the data for this process was last refreshed. | ||
| Description This attribute records the date and time of the most recent data extraction or update. It provides transparency into the freshness of the data being analyzed. In analysis, this is important for understanding the timeliness of the insights. Users can see how current the data is, which affects the relevance of any findings, especially for monitoring ongoing operations. Why it matters Indicates data freshness, ensuring users understand how current the process analysis is and when the next update can be expected. Where to get This is a metadata attribute populated with the execution timestamp of the data extraction job. Examples 2023-05-20T02:00:00Z2023-05-21T02:00:00Z | |||
| Source System SourceSystem | The system from which the data was extracted. | ||
| Description This attribute identifies the origin of the data, which is essential in environments with multiple systems. It provides context and helps ensure data lineage and integrity. For analysis, it is used to segment or filter data when combining processes from different source systems. It confirms that the events are coming from the expected application, in this case, SAP ECC. Why it matters Identifies the data's origin, which is crucial for data governance and for analyses spanning multiple enterprise systems. Where to get This is a static value defined during data extraction to identify the specific SAP ECC instance (e.g., 'ECC_PROD_100'). Examples SAP_ECC_PRODSAPECC_FINANCE_200 | |||
| Actual Refund Amount ActualRefundAmount | The final amount credited to the customer, as posted in the financial documents. | ||
| Description This attribute is the final, confirmed refund value that was processed and posted in the accounting system. This amount may differ from the requested amount due to restocking fees, adjustments based on item condition, or other policy applications. This is a critical attribute for the 'Refund Amount Discrepancy Tracking' dashboard. Comparing this to the requested amount helps identify systemic issues in the refund calculation and approval process, ensuring financial accuracy. Why it matters Represents the final financial outcome of the return. Comparing it to the requested amount helps ensure accuracy and identify financial leakage. Where to get Sourced from the financial document associated with the credit memo. Typically found in the BSEG (Accounting Document Segment) table, field WRBTR (Amount in document currency), for the relevant G/L account posting. Examples 150.0045.001200.75 | |||
| Material Number MaterialNumber | The unique identifier for the product being returned. | ||
| Description The Material Number, or SKU, specifies the exact item involved in the return. This allows for detailed analysis at the product level. By analyzing returns by Material Number, businesses can identify products with high return rates, which may indicate quality issues, inaccurate descriptions, or manufacturing defects. It is essential for dashboards like 'Inventory Impact of Returns' and for understanding how different products move through the returns process. Why it matters Identifies which products are being returned, highlighting potential quality control issues or inaccurate product descriptions. Where to get Found in the Sales Document Item table VBAP, field MATNR. Examples RM-1025FG-2050-BACC-5591 | |||
| Processing Agent ProcessingAgent | The user ID of the employee who performed the activity. | ||
| Description This attribute captures the username of the person responsible for executing a specific process step, such as creating the return order or posting the goods receipt. It is often referred to as the 'Changed By' or 'Created By' user. Analyzing by Processing Agent is key for performance management and resource analysis. It helps identify top performers, agents who may require additional training, and workload distribution across a team. It's used in dashboards like 'Agent Return Processing Performance' to compare cycle times and throughput. Why it matters Tracks user involvement, enabling analysis of team performance, workload distribution, and identification of training needs or best practices. Where to get Typically found in fields like ERNAM (Created by) or AENAM (Changed by) in header tables like VBAK, LIKP, VBRK. For change events, it is USERNAME in CDHDR. Examples CBURNSDSCRANTONJHALPERT | |||
| Requested Refund Amount RequestedRefundAmount | The expected value of the refund, typically based on the net value of the returned item(s). | ||
| Description This attribute represents the initial monetary value of the return, as captured in the return order. It serves as the baseline for financial analysis and reconciliation. This amount is compared against the 'Actual Refund Amount' to track discrepancies, a key metric for the 'Refund Amount Discrepancy Tracking' dashboard. Analyzing this value helps monitor the financial impact of returns and identify potential issues in pricing or credit calculation. Why it matters Establishes the initial financial value of the return, which is crucial for tracking financial discrepancies and understanding the total value of returned goods. Where to get This value is typically the net value from the return order item level. Found in table VBAP, field NETWR. Examples 150.0049.991250.75 | |||
| Return Channel ReturnChannel | The channel through which the return was initiated, such as online, in-store, or call center. | ||
| Description This attribute specifies the origin or intake method for the return request. It helps differentiate between returns initiated through a web portal, a physical store, or via a customer service representative. Segmenting the process by Return Channel is crucial for understanding operational differences and resource allocation. For example, in-store returns might have faster inspection times but different documentation steps compared to online returns. This is a key dimension for the 'Returns Process Throughput Trends' dashboard. Why it matters Distinguishes how returns are initiated, which often impacts the process flow, resource needs, and cycle times for different channels. Where to get This is often not a standard field in SAP ECC and may need to be sourced from a custom field (e.g., in VBAK) or inferred from other data like the Sales Organization (VKORG) or Distribution Channel (VTWEG). Consult SAP ECC documentation for specific implementation. Examples Online PortalIn-StoreCall Center | |||
| Return Reason ReturnReason | The reason code provided by the customer for returning the item. | ||
| Description This attribute indicates why a product was returned, using predefined reason codes in SAP. Examples include 'Damaged in transit', 'Wrong item shipped', or 'Does not fit'. This is a critical dimension for root cause analysis. By filtering the process map or KPIs by Return Reason, analysts can identify if certain reasons are associated with longer processing times, higher inspection failure rates, or specific process deviations. This insight can drive improvements in product quality, logistics, or sales processes. Why it matters Explains why returns occur, enabling root cause analysis to reduce return rates by addressing product quality, shipping, or description issues. Where to get Found in the Sales Document Item table VBAP, field ABGRU (Reason for rejection of sales documents). Examples 001 - Damaged product002 - Wrong product005 - Arrived too late | |||
| Company Code CompanyCode | The legal entity or company responsible for the transaction. | ||
| Description The Company Code represents a self-contained accounting unit within SAP. All financial transactions, including returns and refunds, are posted to a specific company code. This attribute is essential for financial reporting and for segmenting the process in multinational or multi-entity organizations. Analyzing by Company Code allows for comparison of return process performance across different legal entities within the corporation. Why it matters Allows for filtering and comparing return processes across different legal entities within an organization, which is critical for financial analysis. Where to get Found in the Sales Document Header table VBAK, field BUKRS_VF (Company code for billing). Examples 10002000US01 | |||
| Credit Memo Number CreditMemoNumber | The unique identifier for the credit memo document issued for the refund. | ||
| Description The Credit Memo is the official billing document in SAP that formalizes the refund to be paid to the customer. This number uniquely identifies that financial document. This attribute is essential for financial reconciliation and for tracking the process from the operational return to the financial settlement. It serves as a key milestone in the process and is used to find related accounting documents for attributes like 'Actual Refund Amount'. Why it matters Provides a direct link to the financial document that authorizes the customer refund, which is essential for financial auditing and reconciliation. Where to get The credit memo is a billing document. Its number is found in the VBRK table (Billing Document: Header Data), field VBELN. The link is found via the document flow of the return order. Examples 900011229000334490005566 | |||
| Customer ID CustomerId | The unique identifier for the customer initiating the return. | ||
| Description This attribute identifies the specific customer (the 'Sold-to Party' in SAP terms) who is returning the product. It links the return transaction to the customer master data. Analyzing by Customer ID helps identify customers with frequent returns, which could signal dissatisfaction or potential misuse of return policies. It can also be used with customer segmentation data to understand if certain customer groups have different return behaviors or process experiences. Why it matters Links returns to specific customers, allowing for analysis of customer behavior, identification of repeat returners, and impact on customer relationships. Where to get Found in the Sales Document Header table VBAK, field KUNNR (Sold-to party). Examples CUST-100432CUST-203991CUST-831102 | |||
| End-to-End Cycle Time EndToEndCycleTime | The total time elapsed from the first to the last activity for a return case. | ||
| Description This is a case-level metric calculated as the duration between the very first event (e.g., 'Return Order Created') and the final event (e.g., 'Return Order Completed' or 'Credit Memo Cleared'). It represents the total time a return spends in the process. This is a primary key performance indicator for overall process efficiency. It is used in trend analysis and benchmarking to track improvements over time. Analyzing the distribution of this metric helps identify the factors, such as product type or return reason, that correlate with longer or shorter resolution times. Why it matters Measures the total duration of the return process, providing a key indicator of overall process efficiency and customer experience. Where to get This is a calculated metric. It is the timestamp of the last activity in a case minus the timestamp of the first activity. Examples 5 days 4 hours12 days 8 hours3 days 2 hours | |||
| Is SLA Compliant IsSLACompliant | A flag indicating whether the refund was processed within the agreed service level agreement (SLA). | ||
| Description This is a calculated boolean attribute that compares the actual date of the 'Refund Processed' activity with the 'Refund SLA Target Date'. It resolves to true if the refund was completed on time and false otherwise. This attribute simplifies analysis and reporting by providing a clear, binary outcome for each case's SLA performance. It is used to calculate the overall 'Refund Processing SLA Compliance' rate and allows for easy filtering to isolate and analyze non-compliant cases. Why it matters Provides a clear, binary indicator of SLA performance for each case, simplifying compliance monitoring and reporting. Where to get This is a calculated attribute. The logic is: 'Refund Processed' EventTime <= RefundSLATargetDate. Examples truefalse | |||
| Original Sales Document OriginalSalesDocument | The number of the original sales order against which the return is being made. | ||
| Description This attribute provides a direct link back to the initial customer purchase. It is a reference document that connects the return to the original transaction details. Having this link is extremely valuable for deeper analysis. It allows analysts to investigate why sales from certain orders are returned, to check original pricing and terms, and to understand the complete customer order-to-return lifecycle. It can help answer questions about whether products sold via specific campaigns or channels have higher return rates. Why it matters Links the return to the original sale, enabling a full-circle view of the customer transaction and deeper root cause analysis. Where to get This reference is stored at the item level of the return order. It can be found in table VBAP, field VGBEL (Document number of the reference document). Examples 100034561000987110012345 | |||
| Plant Plant | The physical location or facility where the returned item is received and processed. | ||
| Description The Plant in SAP represents a physical location, such as a warehouse or distribution center, where goods are handled. For returns, this is typically the location where the item is received and inspected. Analyzing the process by Plant helps identify performance variations between different facilities. It can highlight which warehouses are more efficient at item inspection or have higher throughput, supporting the 'Item Inspection Throughput & Efficiency' dashboard. Why it matters Identifies the physical location processing the return, enabling performance comparison across different warehouses or distribution centers. Where to get Found at the item level in the return order, table VBAP, field WERKS. Examples PL01WH02DC05 | |||
| Refund Amount Discrepancy RefundAmountDiscrepancy | The calculated difference between the actual and requested refund amounts. | ||
| Description This calculated metric quantifies the monetary difference between what was initially requested in the return order and what was finally issued in the credit memo. A positive value might indicate a partial refund, while a negative value is unusual but could indicate an overpayment. This attribute is the key measure for the 'Refund Amount Discrepancy Tracking' dashboard. It helps to quickly identify and analyze cases with financial deviations, which could be due to item condition assessments, restocking fees, or errors. Monitoring this helps ensure financial control and policy adherence. Why it matters Directly measures financial deviations in the refund process, helping to identify policy non-compliance, processing errors, or financial leakage. Where to get Calculated field: ActualRefundAmount - RequestedRefundAmount. Examples 0.00-4.99-50.00 | |||
| Refund SLA Target Date RefundSLATargetDate | The date by which the refund is expected to be processed according to service level agreements. | ||
| Description This attribute defines the deadline for completing the refund process for a given return case. It is typically calculated based on business rules, such as '5 business days from goods receipt'. This target date is the benchmark against which actual performance is measured. It is the core component for the 'Refund Processing SLA Compliance' dashboard, allowing the business to monitor adherence to customer commitments and identify cases that are at risk of breaching their SLA. Why it matters Sets the performance target for processing refunds, allowing the business to measure and report on SLA compliance and prioritize overdue cases. Where to get This is typically not a standard SAP field and would need to be derived based on business rules. It could be calculated from a key date field (e.g., goods receipt date from MKPF) plus a configured duration. Consult SAP ECC documentation or business requirements. Examples 2023-04-25T23:59:59Z2023-04-28T23:59:59Z2023-05-02T23:59:59Z | |||
| Return Order Status ReturnOrderStatus | The overall processing status of the return order case. | ||
| Description This attribute provides a snapshot of the current state of the return case, such as 'Open', 'In Process', or 'Completed'. It is derived from the combination of statuses at the header or item level of the sales document. In analysis, this attribute is useful for filtering cases to focus on active or completed returns. It can help in monitoring the overall workload and progress of open cases and provides a high-level outcome for each return journey. Why it matters Provides a high-level view of where a case is in its lifecycle, allowing for filtering and analysis of open, in-progress, or closed returns. Where to get Derived from the status fields in tables VBUK (Header Status) and VBUP (Item Status). For example, VBUK-GBSTK is the overall processing status of the document. Examples OpenIn ProcessCompleted | |||
| Return Policy Adherence ReturnPolicyAdherence | A flag indicating if the return complies with all defined business rules and policies. | ||
| Description This calculated boolean attribute signals whether a return case followed the standard, prescribed company policies. The logic could involve checking multiple conditions, such as if the return was initiated within the allowed timeframe, if the reason code is valid for the product, or if manager approval was obtained for an exception. This attribute powers the 'Return Approval Policy Compliance' dashboard. It allows for direct measurement of the compliance rate and helps identify which policies are most frequently bypassed, enabling targeted process improvement or training. Why it matters Measures compliance against business rules, helping to enforce policies consistently and identify cases that require special review or approval. Where to get This is a derived attribute based on a set of business rules. For example: (Return Initiation Date - Original Purchase Date) <= 30 days AND ReturnReason IS NOT NULL. Examples truefalse | |||
Returns & Refund Processing Activities
| Activity | Description | ||
|---|---|---|---|
| Credit Memo Created | A billing document (credit memo) is generated to authorize the financial credit to the customer. This is the official financial document that formalizes the refund amount. | ||
| Why it matters This is a major financial milestone in the process. Analyzing the time to create the credit memo helps identify delays in financial document processing after goods receipt and inspection. Where to get This is an explicit event recorded in the VBRK table (Billing Document Header). The creation date is VBRK-ERDAT. The credit memo is linked to the return order or credit memo request in the document flow. Capture Use creation timestamp (ERDAT) from the VBRK table for the relevant billing document. Event type explicit | |||
| Credit Memo Posted To FI | The credit memo is released to financial accounting, making it an official accounts receivable posting. This step triggers the actual refund payment process to the customer. | ||
| Why it matters This activity marks the point where the refund is financially recognized by the company. Delays between credit memo creation and posting can slow down the actual refund to the customer. Where to get This is an inferred event, identified when the posting status in the billing document header (VBRK-RFBSK) is updated to 'C' (Posting document has been created). The actual accounting document creation date is in the BKPF table. Capture Identify the timestamp when VBRK-RFBSK is set to 'C', or use the creation date (CPUDT) from the linked BKPF accounting document. Event type inferred | |||
| Goods Receipt Posted | This activity occurs when the returned physical item is received at the warehouse or processing center. It is captured by the posting of a goods movement document against the return delivery. | ||
| Why it matters A key milestone indicating the company has taken possession of the returned item. It is the starting point for physical inspection and affects inventory accuracy. Where to get This event can be inferred from the goods movement status of the return delivery item in the VBUP table (e.g., WBSTA = 'C'). The exact timestamp is in the material document header (MKPF-BUDAT) for the corresponding goods receipt. Capture Find the posting date (BUDAT) from the MKPF table for the material document linked to the return delivery. Event type inferred | |||
| Return Order Completed | This activity marks the end of the returns process from a sales and distribution perspective. It occurs when all line items on the return order are fully processed and closed. | ||
| Why it matters This is the primary end event for the process. Measuring the time from 'Return Order Created' to this activity gives the end-to-end cycle time for the return case. Where to get This is an inferred event from the status tables. It is captured when the overall status of the sales document header (VBUK-GBSTK) is updated to 'C' (Completely processed). Capture Identify the timestamp from change documents (CDHDR/CDPOS) when the header status field VBUK-GBSTK changes to 'C'. Event type inferred | |||
| Return Order Created | This activity marks the initiation of the return process when a customer requests to return a product. It is captured when a new sales document of type 'Return' (e.g., RE) is created in SAP ECC. | ||
| Why it matters This is the primary start event for the returns process. Analyzing the time from this activity to others helps measure the total cycle time and identify initial processing delays. Where to get This is an explicit event recorded in the VBAK table (Sales Document Header). The creation timestamp is stored in VBAK-ERDAT and VBAK-ERZET for the corresponding sales document number (VBAK-VBELN) with order type VBAK-AUART = 'RE'. Capture Use creation timestamp (ERDAT, ERZET) from the VBAK table for sales document type 'RE'. Event type explicit | |||
| Usage Decision Made | After inspection, a quality engineer or inspector makes a formal decision on the condition of the returned item. This decision determines the subsequent process, such as returning to stock, scrapping, or repair. | ||
| Why it matters This activity is crucial for understanding inspection efficiency and its outcomes. The decision directly impacts the refund amount and inventory management. Where to get If SAP QM is used, this is an explicit event captured in the QAVE table (Inspection processing usage decision). The decision time is stored in QAVE-VDATUM. The link to the delivery is in the QALS table. Capture Use the usage decision date (VDATUM) from the QAVE table, linked via the inspection lot (QALS-PRUEFLOS). Event type explicit | |||
| Credit Memo Cleared | The open credit item in accounts receivable is cleared, typically by an outgoing payment to the customer. This event marks the final financial closure of the refund. | ||
| Why it matters This activity confirms the cash-out event and completes the financial side of the process. It is the true end of the refund journey from a customer perspective. Where to get This is an explicit event from the FI module. The clearing date for the accounting document is stored in the BSEG table (BSEG-AUGDT) for the corresponding customer line item. Capture Use the clearing date (AUGDT) from the BSEG or BSAD table for the accounting document linked to the credit memo. Event type explicit | |||
| Credit Memo Request Created | A credit memo request is created to formally document the need for a refund. In many configurations, the return order itself serves as the credit memo request. | ||
| Why it matters This is the formal start of the financial settlement part of the returns process. It provides a trigger for subsequent financial approvals and document creation. Where to get This can be an explicit event in VBAK for a sales document of type 'Credit Memo Request' (e.g., CR), or it can be the same event as 'Return Order Created' if the order type is configured to be order-related billing. Capture Use creation timestamp (ERDAT) from VBAK for document type 'CR', or reuse the return order creation event. Event type explicit | |||
| Return Delivery Created | A delivery document is generated to manage the physical receipt of the returned goods. This event signifies that the logistics process for the return has been initiated. | ||
| Why it matters Tracks the transition from administrative processing to physical logistics. Delays here can impact warehouse planning and overall return cycle time. Where to get This is an explicit event recorded in the LIKP table (Delivery Header). The creation timestamp is in LIKP-ERDAT. The link to the source return order is found in the LIPS table (LIPS-VGBEL). Capture Use creation timestamp (ERDAT) from the LIKP table for the delivery associated with the return order. Event type explicit | |||
| Return Order Block Removed | Represents the approval of the return request, allowing it to proceed to the next stage. This is typically captured by a change in the document status or the removal of a delivery or billing block. | ||
| Why it matters This activity is a critical approval milestone. Measuring the time taken to reach this step helps identify bottlenecks in the return authorization and approval process. Where to get This is an inferred event, derived from change logs for the sales document. Check tables CDHDR and CDPOS for changes to block fields in VBAK or VBAP, or status fields in VBUK/VBUP. Capture Identify the timestamp from change document tables (CDHDR/CDPOS) when a relevant block status is removed. Event type inferred | |||
| Return Order Item Completed | An individual line item within the return order is marked as fully processed. This typically occurs after all logistics and financial follow-on documents for that item have been completed. | ||
| Why it matters Tracking at the item level helps identify which products or return reasons cause the longest delays. It provides a more granular view of process completion. Where to get This is an inferred event from the status tables. It is captured when the overall status of the sales document item (VBUP-GBSTK) is updated to 'C' (Completely processed). Capture Identify the timestamp from change documents (CDHDR/CDPOS) when the item status field VBUP-GBSTK changes to 'C'. Event type inferred | |||
| Return Order Item Rejected | A specific item on the return order is rejected, either during initial review or after inspection. This means no further processing, such as a refund, will occur for this item. | ||
| Why it matters Analyzing rejections helps identify invalid return requests and can inform customer policy communication. It's a key path in the process that does not lead to a refund. Where to get This is an inferred event. It is typically captured by a unique 'Reason for Rejection' code being set for the item in the VBAP table (VBAP-ABGRU). The timestamp must be sourced from change logs. Capture Identify the timestamp from change documents (CDHDR/CDPOS) when the VBAP-ABGRU field is populated for a return item. Event type inferred | |||
Extraction Guides
Steps
- Create ABAP Program: Use transaction code
SE38to create a new executable ABAP program, for example,Z_PM_RETURN_EXTRACTION. Provide a descriptive title and save it as a local object or within a development package. - Define Selection Screen: In the program, define a selection screen to allow users to filter the data. Key filters include the creation date range for return orders (
S_ERDAT), the sales document type for returns (S_AUART, e.g., 'RE'), and the company code (P_BUKRS). - Define Data Structures: Create a local structure type that matches the required event log format. This structure should include fields for
ReturnCaseId,ActivityName,EventTime,SourceSystem,LastDataUpdate,ProcessingAgent,ReturnReason, and other required attributes. - Implement Data Selection Logic: Write the core ABAP logic to select data for each of the 12 required activities. This involves querying various SAP tables such as VBAK, VBAP, LIKP, VBRK, BKPF, CDHDR, and CDPOS. Use the document flow table (VBFA) to link related documents like deliveries and credit memos back to the original return order.
- Extract 'Return Order Created': Select records from the
VBAKandVBAPtables based on the selection screen criteria. The sales document number (VBAK-VBELN) will serve as theReturnCaseId. - Extract Status Changes and Key Events: For activities like 'Return Order Block Removed' or 'Return Order Item Rejected', query the change document tables (
CDHDR,CDPOS) or status fields in tables likeVBAP(ABGRU). For logistical and financial follow-on activities, select from tables likeLIKP,MKPF,VBRK, andBKPF, usingVBFAto ensure they are linked to the initial set of return orders. - Populate the Event Log Table: For each activity retrieved, create a new record in an internal table defined with your event log structure. Map the data from the source SAP tables to the corresponding fields, for example, map
VBAK-ERNAMtoProcessingAgentandVBAK-ERDATandVBAK-ERZETtoEventTime. - Combine Activity Data: Ensure that each SELECT statement for an activity appends its results to the single, main internal table. This creates the final flat file structure where each row represents one event.
- Handle Timestamps and System Info: Use the system field
SY-DATUMandSY-UZEITto populate theLastDataUpdatetimestamp for every row. UseSY-SYSIDto populate theSourceSystem. - Implement Output Functionality: Add functionality to display the results in an ALV grid (SAP List Viewer) for online analysis using function modules like
REUSE_ALV_GRID_DISPLAY. Provide an option to download the final internal table as a CSV or flat text file to the user's local machine. - Execute and Export: Run the program via
SE38. Enter the desired selection criteria and execute. Once the data is displayed or processed, use the program's export feature to save the event log file. Ensure the file is saved in a format compatible with ProcessMind, typically CSV with a header row.
Configuration
- Date Range: On the selection screen, specify a date range for the return order creation date. A range of 3 to 6 months is recommended for initial analysis to balance data volume and process insight.
- Return Document Type: Filter by the specific sales document type used for returns in your system, for example, 'RE' for Returns, 'CR' for Credit Memo Request. This is a critical filter to isolate the correct process. Enter these values in the
S_AUARTselection option. - Company Code: Specify the relevant company code (
P_BUKRS) to limit the data to a specific legal entity. This is essential in multi-company environments. - Prerequisites: The user executing the program requires authorizations for
SE38(program execution) and access to the underlying SD, MM, and FI tables (e.g., VBAK, LIKP, VBRK, BKPF, CDHDR). Development requires an ABAP developer role. - Performance: For very large datasets spanning multiple years, the program may experience long runtimes. Consider running it as a background job (
SM36) and writing the output to the application server file system instead of displaying it online.
a Sample Query abap
REPORT Z_PM_RETURN_EXTRACTION.
" =======================================================================
" DATA DECLARATIONS
" =======================================================================
TYPES: BEGIN OF ty_event_log,
returncaseid TYPE vbeln_va,
activityname TYPE string,
eventtime TYPE timestamp,
sourcesystem TYPE sysysid,
lastdataupdate TYPE timestamp,
processingagent TYPE ernam,
returnreason TYPE augru,
materialnumber TYPE matnr,
returnchannel TYPE string, " Placeholder, needs custom logic
requestedrefundamount TYPE netwr,
actualrefundamount TYPE netwr,
END OF ty_event_log.
DATA: lt_event_log TYPE TABLE OF ty_event_log,
ls_event_log TYPE ty_event_log.
DATA: lv_timestamp TYPE timestamp,
lv_last_update TYPE timestamp.
" =======================================================================
" SELECTION SCREEN
" =======================================================================
SELECT-OPTIONS: s_erdat FOR sy-datum OBLIGATORY,
s_auart FOR vbak-auart.
PARAMETERS: p_bukrs TYPE bukrs OBLIGATORY.
" =======================================================================
" MAIN LOGIC
" =======================================================================
START-OF-SELECTION.
" Set static values for the extraction run
GET TIME STAMP FIELD lv_last_update.
" 1. Return Order Created & 6. Credit Memo Request Created
" Note: In many SAP standard processes, the Return Order itself acts as the Credit Memo Request.
SELECT h~vbeln, h~erdat, h~erzet, h~ernam, h~augru, i~matnr, i~netwr
INTO TABLE @DATA(lt_return_orders)
FROM vbak AS h
INNER JOIN vbap AS i ON h~vbeln = i~vbeln
WHERE h~erdat IN @s_erdat
AND h~auart IN @s_auart
AND h~bukrs_vf = @p_bukrs.
LOOP AT lt_return_orders INTO DATA(ls_return_order).
" Activity: Return Order Created
ls_event_log-returncaseid = ls_return_order-vbeln.
ls_event_log-activityname = 'Return Order Created'.
CONVERT DATE ls_return_order-erdat TIME ls_return_order-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-sourcesystem = sy-sysid.
ls_event_log-lastdataupdate = lv_last_update.
ls_event_log-processingagent = ls_return_order-ernam.
ls_event_log-returnreason = ls_return_order-augru.
ls_event_log-materialnumber = ls_return_order-matnr.
ls_event_log-requestedrefundamount = ls_return_order-netwr.
APPEND ls_event_log TO lt_event_log.
" Activity: Credit Memo Request Created
ls_event_log-activityname = 'Credit Memo Request Created'.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
IF lt_return_orders IS NOT INITIAL.
" 2. Return Order Block Removed (Example for Delivery Block)
SELECT objectid, udate, utime, username
FROM cdhdr
INTO TABLE @DATA(lt_block_changes)
FOR ALL ENTRIES IN @lt_return_orders
WHERE objectclas = 'VERKBELEG'
AND objectid = @lt_return_orders-vbeln
AND tcode = 'VA02'.
IF sy-subrc = 0.
SELECT objectid, fname, value_new
INTO TABLE @DATA(lt_cdpos_block)
FROM cdpos
FOR ALL ENTRIES IN @lt_block_changes
WHERE objectclas = @lt_block_changes-objectclas
AND objectid = @lt_block_changes-objectid
AND changenr = @lt_block_changes-changenr
AND tabname = 'VBAK'
AND fname = 'LIFSK' " Delivery Block field
AND value_new = ''.
LOOP AT lt_cdpos_block INTO DATA(ls_cdpos_block).
READ TABLE lt_block_changes INTO DATA(ls_block_change) WITH KEY objectid = ls_cdpos_block-objectid.
IF sy-subrc = 0.
ls_event_log-returncaseid = ls_cdpos_block-objectid.
ls_event_log-activityname = 'Return Order Block Removed'.
CONVERT DATE ls_block_change-udate TIME ls_block_change-utime INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-sourcesystem = sy-sysid.
ls_event_log-lastdataupdate = lv_last_update.
ls_event_log-processingagent = ls_block_change-username.
CLEAR: ls_event_log-returnreason, ls_event_log-materialnumber, ls_event_log-requestedrefundamount, ls_event_log-actualrefundamount.
APPEND ls_event_log TO lt_event_log.
ENDIF.
ENDLOOP.
ENDIF.
" Get subsequent documents from flow
SELECT vbelv, vbtyp_n, vbeln, rfbsk
INTO TABLE @DATA(lt_doc_flow)
FROM vbfa
FOR ALL ENTRIES IN @lt_return_orders
WHERE vbelv = @lt_return_orders-vbeln
AND vbtyp_n IN ('J', 'R', 'M'). " J=Delivery, R=Goods Mvmt, M=Invoice
" 3. Return Delivery Created
SELECT vbeln, erdat, erzet, ernam FROM likp
INTO TABLE @DATA(lt_deliveries)
FOR ALL ENTRIES IN @lt_doc_flow
WHERE vbeln = @lt_doc_flow-vbeln AND @lt_doc_flow-vbtyp_n = 'J'.
LOOP AT lt_deliveries INTO DATA(ls_delivery).
READ TABLE lt_doc_flow INTO DATA(ls_doc_flow_del) WITH KEY vbeln = ls_delivery-vbeln.
ls_event_log-returncaseid = ls_doc_flow_del-vbelv.
ls_event_log-activityname = 'Return Delivery Created'.
CONVERT DATE ls_delivery-erdat TIME ls_delivery-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-sourcesystem = sy-sysid.
ls_event_log-lastdataupdate = lv_last_update.
ls_event_log-processingagent = ls_delivery-ernam.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" 4. Goods Receipt Posted
SELECT mblnr, mjahr, budat, cputm, usnam FROM mkpf
INTO TABLE @DATA(lt_goods_receipt)
FOR ALL ENTRIES IN @lt_doc_flow
WHERE xblnr = @lt_doc_flow-vbeln AND @lt_doc_flow-vbtyp_n = 'R'.
LOOP AT lt_goods_receipt INTO DATA(ls_goods_receipt).
READ TABLE lt_doc_flow INTO DATA(ls_doc_flow_gr) WITH KEY vbeln = ls_goods_receipt-mblnr.
ls_event_log-returncaseid = ls_doc_flow_gr-vbelv.
ls_event_log-activityname = 'Goods Receipt Posted'.
CONVERT DATE ls_goods_receipt-budat TIME ls_goods_receipt-cputm INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-sourcesystem = sy-sysid.
ls_event_log-lastdataupdate = lv_last_update.
ls_event_log-processingagent = ls_goods_receipt-usnam.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" 5. Usage Decision Made (Requires QM Module implementation)
SELECT q~prueflos, q~vdatu, q~vzeit, q~vname
FROM qals AS q
INNER JOIN qave AS v ON q~prueflos = v~prueflos
INTO TABLE @DATA(lt_usage_decisions)
FOR ALL ENTRIES IN @lt_deliveries
WHERE q~kdauf = @lt_deliveries-vbeln.
LOOP AT lt_usage_decisions INTO DATA(ls_ud).
SELECT SINGLE vbelv FROM vbfa INTO @DATA(lv_vbelv_ud) WHERE vbeln = @ls_ud-prueflos.
ls_event_log-returncaseid = lv_vbelv_ud.
ls_event_log-activityname = 'Usage Decision Made'.
CONVERT DATE ls_ud-vdatu TIME ls_ud-vzeit INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-sourcesystem = sy-sysid.
ls_event_log-lastdataupdate = lv_last_update.
ls_event_log-processingagent = ls_ud-vname.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" 7. Credit Memo Created
SELECT h~vbeln, h~erdat, h~erzet, h~ernam, i~netwr, i~vgbel
INTO TABLE @DATA(lt_cred_memos)
FROM vbrk AS h
INNER JOIN vbrp AS i ON h~vbeln = i~vbeln
FOR ALL ENTRIES IN @lt_doc_flow
WHERE h~vbeln = @lt_doc_flow-vbeln AND @lt_doc_flow-vbtyp_n = 'M'.
LOOP AT lt_cred_memos INTO DATA(ls_cred_memo).
ls_event_log-returncaseid = ls_cred_memo-vgbel.
ls_event_log-activityname = 'Credit Memo Created'.
CONVERT DATE ls_cred_memo-erdat TIME ls_cred_memo-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-sourcesystem = sy-sysid.
ls_event_log-lastdataupdate = lv_last_update.
ls_event_log-processingagent = ls_cred_memo-ernam.
ls_event_log-actualrefundamount = ls_cred_memo-netwr.
APPEND ls_event_log TO lt_event_log.
" 8. Credit Memo Posted To FI
SELECT SINGLE budat, cputime, usnam FROM bkpf
INTO @DATA(ls_bkpf_post)
WHERE awkey = @ls_cred_memo-vbeln AND awtyp = 'VBRK'.
IF sy-subrc = 0.
ls_event_log-activityname = 'Credit Memo Posted To FI'.
CONVERT DATE ls_bkpf_post-budat TIME ls_bkpf_post-cputime INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-processingagent = ls_bkpf_post-usnam.
APPEND ls_event_log TO lt_event_log.
ENDIF.
" 12. Credit Memo Cleared
SELECT SINGLE augdt FROM bseg
INTO @DATA(lv_augdt)
WHERE bukrs = @p_bukrs
AND belnr = @ls_cred_memo-vbeln
AND augdt IS NOT NULL.
IF sy-subrc = 0 AND lv_augdt IS NOT INITIAL.
ls_event_log-activityname = 'Credit Memo Cleared'.
CONVERT DATE lv_augdt INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
CLEAR: ls_event_log-processingagent.
APPEND ls_event_log TO lt_event_log.
ENDIF.
ENDLOOP.
" 9. Return Order Item Completed
SELECT vbeln, erdat, erzet, ernam, abgru FROM vbap
INTO TABLE @DATA(lt_items_completed)
FOR ALL ENTRIES IN @lt_return_orders
WHERE vbeln = @lt_return_orders-vbeln
AND gbsta = 'C'.
LOOP AT lt_items_completed INTO DATA(ls_item_completed).
ls_event_log-returncaseid = ls_item_completed-vbeln.
ls_event_log-activityname = 'Return Order Item Completed'.
CONVERT DATE ls_item_completed-erdat TIME ls_item_completed-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-sourcesystem = sy-sysid.
ls_event_log-lastdataupdate = lv_last_update.
ls_event_log-processingagent = ls_item_completed-ernam.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" 10. Return Order Completed
SELECT vbeln, erdat, erzet, ernam FROM vbak
INTO TABLE @DATA(lt_orders_completed)
FOR ALL ENTRIES IN @lt_return_orders
WHERE vbeln = @lt_return_orders-vbeln
AND gbsta = 'C'.
LOOP AT lt_orders_completed INTO DATA(ls_order_completed).
ls_event_log-returncaseid = ls_order_completed-vbeln.
ls_event_log-activityname = 'Return Order Completed'.
CONVERT DATE ls_order_completed-erdat TIME ls_order_completed-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-sourcesystem = sy-sysid.
ls_event_log-lastdataupdate = lv_last_update.
ls_event_log-processingagent = ls_order_completed-ernam.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" 11. Return Order Item Rejected
SELECT vbeln, erdat, erzet, ernam FROM vbap
INTO TABLE @DATA(lt_items_rejected)
FOR ALL ENTRIES IN @lt_return_orders
WHERE vbeln = @lt_return_orders-vbeln
AND abgru IS NOT NULL AND abgru <> ''.
LOOP AT lt_items_rejected INTO DATA(ls_item_rejected).
ls_event_log-returncaseid = ls_item_rejected-vbeln.
ls_event_log-activityname = 'Return Order Item Rejected'.
CONVERT DATE ls_item_rejected-erdat TIME ls_item_rejected-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-sourcesystem = sy-sysid.
ls_event_log-lastdataupdate = lv_last_update.
ls_event_log-processingagent = ls_item_rejected-ernam.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
ENDIF.
" =======================================================================
" OUTPUT
" =======================================================================
cl_salv_table=>factory(
IMPORTING
r_salv_table = DATA(lo_alv)
CHANGING
t_table = lt_event_log ).
lo_alv->display( ).Steps
- Prerequisites: Ensure you have a database user with read access to the required SAP ECC tables. This includes tables from the Sales and Distribution (SD), Materials Management (MM), and Financial Accounting (FI) modules. You will need a suitable SQL client tool like DBeaver, SAP HANA Studio, or Oracle SQL Developer to connect to the database.
- Identify System Specifics: Before running the query, you must identify the specific document types used for returns in your organization. The query uses a placeholder
[YOUR_RETURN_ORDER_TYPES], which you should replace with actual values, such as 'RE' for standard returns. - Prepare the Query: Copy the complete SQL query provided in the 'query' section into your SQL client.
- Configure Parameters: Locate the Common Table Expression (CTE) named
BaseReturnOrdersat the beginning of the query. Modify theWHEREclause to specify the desired date range by replacing[START_DATE]and[END_DATE]. Update the list of return order types and, if necessary, filter by a specific company code (BUKRS_VF). - Execute the Query: Run the modified query against the SAP ECC database. The execution time may vary depending on the selected date range and the size of the database.
- Review the Raw Data: Once the query finishes, perform a quick review of the output. Check that the columns
ReturnCaseId,ActivityName, andEventTimeare populated and that data appears for multiple activities. - Export to CSV: Export the entire result set from your SQL client to a CSV file. Use UTF-8 encoding to ensure all characters are preserved correctly.
- Prepare for Upload: Open the CSV file and verify that the column headers match the required attributes exactly:
ReturnCaseId,ActivityName,EventTime,SourceSystem, etc. Adjust headers if your SQL client altered them during export. - Final Upload: Upload the clean CSV file to the ProcessMind platform for analysis.
Configuration
- Return Document Types: The query must be configured with the specific sales document types that represent return orders in your SAP system. This is done by replacing the
[YOUR_RETURN_ORDER_TYPES]placeholder. Common examples include 'RE' or custom 'Z' types. - Date Range: It is critical to define a suitable date range for the extraction. A range of 3 to 6 months of data is typically recommended for an initial analysis. Set this using the
[START_DATE]and[END_DATE]placeholders in the format 'YYYYMMDD'. - Company Code: For organizations with multiple company codes, filtering the data is essential for a focused analysis. You can add a filter like
VBAK.BUKRS_VF = '[YOUR_COMPANY_CODE]'in the initial CTE to limit the scope. - Database Dialect: The provided query uses common SQL functions. However, date and time conversions, such as
TO_TIMESTAMPand string concatenation (CONCAT), may require slight syntax adjustments depending on the underlying database system (e.g., Oracle, HANA, DB2). - Required Authorizations: The database user needs read access to the following key tables: VBAK, VBAP, VBFA, LIKP, LIPS, VBRK, VBRP, MKPF, MSEG, BKPF, BSAD, QALS, QAVE, CDHDR, and CDPOS.
a Sample Query sql
WITH BaseReturnOrders AS (
SELECT
VBAK.VBELN AS ReturnOrderNumber,
VBAP.POSNR AS ReturnOrderItemNumber
FROM VBAK
JOIN VBAP ON VBAK.VBELN = VBAP.VBELN
WHERE VBAK.AUART IN ('[YOUR_RETURN_ORDER_TYPES]') -- e.g., 'RE'
AND VBAK.ERDAT BETWEEN '[START_DATE]' AND '[END_DATE]' -- e.g., '20230101' and '20231231'
)
-- 1. Return Order Created
SELECT
CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
'Return Order Created' AS "ActivityName",
TO_TIMESTAMP(CONCAT(VBAK.ERDAT, VBAK.ERZET), 'YYYYMMDDHH24MISS') AS "EventTime",
'SAP ECC' AS "SourceSystem",
CURRENT_TIMESTAMP AS "LastDataUpdate",
VBAK.ERNAM AS "ProcessingAgent",
VBAK.AUGRU AS "ReturnReason",
VBAP.MATNR AS "MaterialNumber",
VBAK.VTWEG AS "ReturnChannel",
VBAP.NETWR AS "RequestedRefundAmount",
NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
UNION ALL
-- 2. Return Order Block Removed
SELECT
CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
'Return Order Block Removed' AS "ActivityName",
TO_TIMESTAMP(CONCAT(CDHDR.UDATE, CDHDR.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
'SAP ECC' AS "SourceSystem",
CURRENT_TIMESTAMP AS "LastDataUpdate",
CDHDR.USERNAME AS "ProcessingAgent",
VBAK.AUGRU AS "ReturnReason",
VBAP.MATNR AS "MaterialNumber",
VBAK.VTWEG AS "ReturnChannel",
VBAP.NETWR AS "RequestedRefundAmount",
NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN CDHDR ON B.ReturnOrderNumber = CDHDR.OBJECTID
JOIN CDPOS ON CDHDR.OBJECTCLAS = CDPOS.OBJECTCLAS AND CDHDR.OBJECTID = CDPOS.OBJECTID AND CDHDR.CHANGENR = CDPOS.CHANGENR
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
WHERE CDHDR.OBJECTCLAS = 'VERKBELEG'
AND CDPOS.TABNAME = 'VBAK'
AND CDPOS.FNAME IN ('LIFSK', 'FAKSK')
AND CDPOS.VALUE_NEW = ''
AND CDPOS.VALUE_OLD <> ''
UNION ALL
-- 3. Return Delivery Created
SELECT
CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
'Return Delivery Created' AS "ActivityName",
TO_TIMESTAMP(CONCAT(LIKP.ERDAT, LIKP.ERZET), 'YYYYMMDDHH24MISS') AS "EventTime",
'SAP ECC' AS "SourceSystem",
CURRENT_TIMESTAMP AS "LastDataUpdate",
LIKP.ERNAM AS "ProcessingAgent",
VBAK.AUGRU AS "ReturnReason",
VBAP.MATNR AS "MaterialNumber",
VBAK.VTWEG AS "ReturnChannel",
VBAP.NETWR AS "RequestedRefundAmount",
NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'J'
JOIN LIKP ON VBFA.VBELN = LIKP.VBELN
UNION ALL
-- 4. Goods Receipt Posted
SELECT
CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
'Goods Receipt Posted' AS "ActivityName",
TO_TIMESTAMP(CONCAT(MKPF.CPUDT, MKPF.CPUTM), 'YYYYMMDDHH24MISS') AS "EventTime",
'SAP ECC' AS "SourceSystem",
CURRENT_TIMESTAMP AS "LastDataUpdate",
MKPF.USNAM AS "ProcessingAgent",
VBAK.AUGRU AS "ReturnReason",
VBAP.MATNR AS "MaterialNumber",
VBAK.VTWEG AS "ReturnChannel",
VBAP.NETWR AS "RequestedRefundAmount",
NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'J'
JOIN LIPS ON VBFA.VBELN = LIPS.VBELN AND VBFA.POSNN = LIPS.POSNR
JOIN MSEG ON LIPS.VBELN = MSEG.LFBNR AND LIPS.POSNR = MSEG.LFPOS
JOIN MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.BWART IN ('651', '653', '655') -- Standard returns movement types
UNION ALL
-- 5. Usage Decision Made
SELECT
CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
'Usage Decision Made' AS "ActivityName",
TO_TIMESTAMP(CONCAT(QAVE.ADAT, QAVE.ATIM), 'YYYYMMDDHH24MISS') AS "EventTime",
'SAP ECC' AS "SourceSystem",
CURRENT_TIMESTAMP AS "LastDataUpdate",
QAVE.AENAME AS "ProcessingAgent",
VBAK.AUGRU AS "ReturnReason",
VBAP.MATNR AS "MaterialNumber",
VBAK.VTWEG AS "ReturnChannel",
VBAP.NETWR AS "RequestedRefundAmount",
NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'J'
JOIN QALS ON VBFA.VBELN = QALS.VBELN AND VBFA.POSNN = QALS.POSNR -- Linking inspection lot to delivery item
JOIN QAVE ON QALS.PRUEFLOS = QAVE.PRUEFLOS
UNION ALL
-- 6. Credit Memo Request Created (assumes a separate doc, or can be the return order itself)
SELECT
CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
'Credit Memo Request Created' AS "ActivityName",
TO_TIMESTAMP(CONCAT(CMR_VBAK.ERDAT, CMR_VBAK.ERZET), 'YYYYMMDDHH24MISS') AS "EventTime",
'SAP ECC' AS "SourceSystem",
CURRENT_TIMESTAMP AS "LastDataUpdate",
CMR_VBAK.ERNAM AS "ProcessingAgent",
VBAK.AUGRU AS "ReturnReason",
VBAP.MATNR AS "MaterialNumber",
VBAK.VTWEG AS "ReturnChannel",
VBAP.NETWR AS "RequestedRefundAmount",
NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'K' -- 'K' is Credit Memo Request
JOIN VBAK AS CMR_VBAK ON VBFA.VBELN = CMR_VBAK.VBELN
UNION ALL
-- 7. Credit Memo Created
SELECT
CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
'Credit Memo Created' AS "ActivityName",
TO_TIMESTAMP(CONCAT(VBRK.ERDAT, VBRK.ERZET), 'YYYYMMDDHH24MISS') AS "EventTime",
'SAP ECC' AS "SourceSystem",
CURRENT_TIMESTAMP AS "LastDataUpdate",
VBRK.ERNAM AS "ProcessingAgent",
VBAK.AUGRU AS "ReturnReason",
VBAP.MATNR AS "MaterialNumber",
VBAK.VTWEG AS "ReturnChannel",
VBAP.NETWR AS "RequestedRefundAmount",
VBRP.NETWR AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'M'
JOIN VBRK ON VBFA.VBELN = VBRK.VBELN
JOIN VBRP ON VBRK.VBELN = VBRP.VBELN AND VBFA.POSNN = VBRP.POSNR
WHERE VBRK.FKART IN ('[YOUR_CREDIT_MEMO_TYPES]') -- e.g., 'G2'
UNION ALL
-- 8. Credit Memo Posted To FI
SELECT
CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
'Credit Memo Posted To FI' AS "ActivityName",
TO_TIMESTAMP(CONCAT(BKPF.CPUDT, BKPF.CPUTM), 'YYYYMMDDHH24MISS') AS "EventTime",
'SAP ECC' AS "SourceSystem",
CURRENT_TIMESTAMP AS "LastDataUpdate",
BKPF.USNAM AS "ProcessingAgent",
VBAK.AUGRU AS "ReturnReason",
VBAP.MATNR AS "MaterialNumber",
VBAK.VTWEG AS "ReturnChannel",
VBAP.NETWR AS "RequestedRefundAmount",
VBRP.NETWR AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'M'
JOIN VBRK ON VBFA.VBELN = VBRK.VBELN
JOIN VBRP ON VBRK.VBELN = VBRP.VBELN AND VBFA.POSNN = VBRP.POSNR
JOIN BKPF ON VBRK.VBELN = BKPF.AWKEY AND BKPF.AWTYP = 'VBRK'
UNION ALL
-- 9. Return Order Item Completed
SELECT
CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
'Return Order Item Completed' AS "ActivityName",
TO_TIMESTAMP(CONCAT(CDHDR.UDATE, CDHDR.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
'SAP ECC' AS "SourceSystem",
CURRENT_TIMESTAMP AS "LastDataUpdate",
CDHDR.USERNAME AS "ProcessingAgent",
VBAK.AUGRU AS "ReturnReason",
VBAP.MATNR AS "MaterialNumber",
VBAK.VTWEG AS "ReturnChannel",
VBAP.NETWR AS "RequestedRefundAmount",
NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN CDHDR ON CONCAT(B.ReturnOrderNumber, LPAD(B.ReturnOrderItemNumber, 6, '0')) = CDHDR.OBJECTID
JOIN CDPOS ON CDHDR.OBJECTCLAS = CDPOS.OBJECTCLAS AND CDHDR.OBJECTID = CDPOS.OBJECTID AND CDHDR.CHANGENR = CDPOS.CHANGENR
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
WHERE CDHDR.OBJECTCLAS = 'VERKBELEG'
AND CDPOS.TABNAME = 'VBUP'
AND CDPOS.FNAME = 'GBSTA'
AND CDPOS.VALUE_NEW = 'C'
UNION ALL
-- 10. Return Order Completed
SELECT
CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
'Return Order Completed' AS "ActivityName",
TO_TIMESTAMP(CONCAT(CDHDR.UDATE, CDHDR.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
'SAP ECC' AS "SourceSystem",
CURRENT_TIMESTAMP AS "LastDataUpdate",
CDHDR.USERNAME AS "ProcessingAgent",
VBAK.AUGRU AS "ReturnReason",
VBAP.MATNR AS "MaterialNumber",
VBAK.VTWEG AS "ReturnChannel",
VBAP.NETWR AS "RequestedRefundAmount",
NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN CDHDR ON B.ReturnOrderNumber = CDHDR.OBJECTID
JOIN CDPOS ON CDHDR.OBJECTCLAS = CDPOS.OBJECTCLAS AND CDHDR.OBJECTID = CDPOS.OBJECTID AND CDHDR.CHANGENR = CDPOS.CHANGENR
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
WHERE CDHDR.OBJECTCLAS = 'VERKBELEG'
AND CDPOS.TABNAME = 'VBUK'
AND CDPOS.FNAME = 'GBSTK'
AND CDPOS.VALUE_NEW = 'C'
UNION ALL
-- 11. Return Order Item Rejected
SELECT
CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
'Return Order Item Rejected' AS "ActivityName",
TO_TIMESTAMP(CONCAT(CDHDR.UDATE, CDHDR.UTIME), 'YYYYMMDDHH24MISS') AS "EventTime",
'SAP ECC' AS "SourceSystem",
CURRENT_TIMESTAMP AS "LastDataUpdate",
CDHDR.USERNAME AS "ProcessingAgent",
CDPOS.VALUE_NEW AS "ReturnReason", -- Using rejection reason for this specific event
VBAP.MATNR AS "MaterialNumber",
VBAK.VTWEG AS "ReturnChannel",
VBAP.NETWR AS "RequestedRefundAmount",
NULL AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN CDHDR ON CONCAT(B.ReturnOrderNumber, LPAD(B.ReturnOrderItemNumber, 6, '0')) = CDHDR.OBJECTID
JOIN CDPOS ON CDHDR.OBJECTCLAS = CDPOS.OBJECTCLAS AND CDHDR.OBJECTID = CDPOS.OBJECTID AND CDHDR.CHANGENR = CDPOS.CHANGENR
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
WHERE CDHDR.OBJECTCLAS = 'VERKBELEG'
AND CDPOS.TABNAME = 'VBAP'
AND CDPOS.FNAME = 'ABGRU'
AND CDPOS.VALUE_NEW <> ''
UNION ALL
-- 12. Credit Memo Cleared
SELECT
CONCAT(B.ReturnOrderNumber, '-', B.ReturnOrderItemNumber) AS "ReturnCaseId",
'Credit Memo Cleared' AS "ActivityName",
TO_TIMESTAMP(BSAD.AUGDT, 'YYYYMMDD') AS "EventTime",
'SAP ECC' AS "SourceSystem",
CURRENT_TIMESTAMP AS "LastDataUpdate",
CLEAR_DOC.USNAM AS "ProcessingAgent",
VBAK.AUGRU AS "ReturnReason",
VBAP.MATNR AS "MaterialNumber",
VBAK.VTWEG AS "ReturnChannel",
VBAP.NETWR AS "RequestedRefundAmount",
VBRP.NETWR AS "ActualRefundAmount"
FROM BaseReturnOrders B
JOIN VBAK ON B.ReturnOrderNumber = VBAK.VBELN
JOIN VBAP ON B.ReturnOrderNumber = VBAP.VBELN AND B.ReturnOrderItemNumber = VBAP.POSNR
JOIN VBFA ON B.ReturnOrderNumber = VBFA.VBELV AND B.ReturnOrderItemNumber = VBFA.POSNV AND VBFA.VBTYP_N = 'M'
JOIN VBRK ON VBFA.VBELN = VBRK.VBELN
JOIN VBRP ON VBRK.VBELN = VBRP.VBELN AND VBFA.POSNN = VBRP.POSNR
JOIN BSAD ON VBRK.VBELN = BSAD.VBELN
JOIN BKPF AS CLEAR_DOC ON BSAD.AUGBL = CLEAR_DOC.BELNR AND BSAD.BUKRS = CLEAR_DOC.BUKRS AND BSAD.GJAHR_AUGL = CLEAR_DOC.GJAHR;