Your Returns & Refund Processing Data Template

<?xml version="1.0" encoding="UTF-8"?>
<export xmlns="http://www.demandware.com/xml/impex/order/2006-10-31">
    <orders>
        <order-query>
            <creation-date-from>2023-01-01T00:00:00.000Z</creation-date-from>
        </order-query>
    </orders>
    <data-transformation>
        <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
            <xsl:output method="text" encoding="UTF-8"/>
            <xsl:variable name="delimiter" select="','"/>
            <xsl:variable name="quote" select="'"'"/>
            <xsl:variable name="lastDataUpdate"><xsl:value-of select="current-dateTime()"/></xsl:variable>

            <xsl:template match="/">
                <!-- Header Row -->
                <xsl:text>ReturnCaseId,ActivityName,EventTime,SourceSystem,LastDataUpdate,ProcessingAgent,ReturnReason,ProductSku,RequestedRefundAmount,ActualRefundAmount,ReturnChannel</xsl:text>
                <xsl:text>&#10;</xsl:text>

                <!-- Process each return within each order -->
                <xsl:for-each select="orders/order/returns/return">
                    <xsl:variable name="return" select="."/>
                    <xsl:variable name="order" select="ancestor::order"/>

                    <!-- 1. Return Request Created -->
                    <xsl:call-template name="CreateRow">
                        <xsl:with-param name="returnNode" select="$return"/>
                        <xsl:with-param name="orderNode" select="$order"/>
                        <xsl:with-param name="activityName" select="'Return Request Created'"/>
                        <xsl:with-param name="eventTime" select="$return/creation-date"/>
                        <xsl:with-param name="sku" select="''"/>
                    </xsl:call-template>

                    <!-- 2. Return Request Approved -->
                    <xsl:if test="status='APPROVED'">
                        <xsl:call-template name="CreateRow">
                            <xsl:with-param name="returnNode" select="$return"/>
                            <xsl:with-param name="orderNode" select="$order"/>
                            <xsl:with-param name="activityName" select="'Return Request Approved'"/>
                            <xsl:with-param name="eventTime" select="$return/last-modified"/>
                            <xsl:with-param name="sku" select="''"/>
                        </xsl:call-template>
                    </xsl:if>

                    <!-- 3. Return Request Rejected -->
                    <xsl:if test="status='CANCELED' or status='REJECTED'">
                        <xsl:call-template name="CreateRow">
                            <xsl:with-param name="returnNode" select="$return"/>
                            <xsl:with-param name="orderNode" select="$order"/>
                            <xsl:with-param name="activityName" select="'Return Request Rejected'"/>
                            <xsl:with-param name="eventTime" select="$return/last-modified"/>
                            <xsl:with-param name="sku" select="''"/>
                        </xsl:call-template>
                    </xsl:if>

                    <!-- 4. Return Label Generated (Placeholder Logic) -->
                    <xsl:if test="custom-attributes/custom-attribute[@attribute-id='returnLabelGeneratedDate']">
                        <xsl:call-template name="CreateRow">
                            <xsl:with-param name="returnNode" select="$return"/>
                            <xsl:with-param name="orderNode" select="$order"/>
                            <xsl:with-param name="activityName" select="'Return Label Generated'"/>
                            <xsl:with-param name="eventTime" select="custom-attributes/custom-attribute[@attribute-id='returnLabelGeneratedDate']"/>
                            <xsl:with-param name="sku" select="''"/>
                        </xsl:call-template>
                    </xsl:if>

                    <!-- Item-level Events -->
                    <xsl:for-each select="items/return-item">
                        <!-- 5. Item Received At Warehouse -->
                        <xsl:if test="status='RECEIVED'">
                            <xsl:call-template name="CreateRow">
                                <xsl:with-param name="returnNode" select="$return"/>
                                <xsl:with-param name="orderNode" select="$order"/>
                                <xsl:with-param name="activityName" select="'Item Received At Warehouse'"/>
                                <xsl:with-param name="eventTime" select="last-modified"/>
                                <xsl:with-param name="sku" select="product-id"/>
                            </xsl:call-template>
                        </xsl:if>
                        <!-- 6. Item Inspection Started -->
                        <xsl:if test="status='INSPECTING'">
                             <xsl:call-template name="CreateRow">
                                <xsl:with-param name="returnNode" select="$return"/>
                                <xsl:with-param name="orderNode" select="$order"/>
                                <xsl:with-param name="activityName" select="'Item Inspection Started'"/>
                                <xsl:with-param name="eventTime" select="last-modified"/>
                                <xsl:with-param name="sku" select="product-id"/>
                            </xsl:call-template>
                        </xsl:if>
                        <!-- 7. Item Inspection Completed -->
                        <xsl:if test="status='INSPECTED' or status='COMPLETED_INSPECTION'">
                            <xsl:call-template name="CreateRow">
                                <xsl:with-param name="returnNode" select="$return"/>
                                <xsl:with-param name="orderNode" select="$order"/>
                                <xsl:with-param name="activityName" select="'Item Inspection Completed'"/>
                                <xsl:with-param name="eventTime" select="last-modified"/>
                                <xsl:with-param name="sku" select="product-id"/>
                            </xsl:call-template>
                        </xsl:if>
                    </xsl:for-each>

                    <!-- 8. Refund Amount Calculated (Placeholder Logic) -->
                    <xsl:if test="custom-attributes/custom-attribute[@attribute-id='refundCalculatedDate']">
                        <xsl:call-template name="CreateRow">
                            <xsl:with-param name="returnNode" select="$return"/>
                            <xsl:with-param name="orderNode" select="$order"/>
                            <xsl:with-param name="activityName" select="'Refund Amount Calculated'"/>
                            <xsl:with-param name="eventTime" select="custom-attributes/custom-attribute[@attribute-id='refundCalculatedDate']"/>
                            <xsl:with-param name="sku" select="''"/>
                        </xsl:call-template>
                    </xsl:if>

                    <!-- 9. Refund Approved (Placeholder Logic) -->
                    <xsl:if test="custom-attributes/custom-attribute[@attribute-id='refundApprovedDate']">
                        <xsl:call-template name="CreateRow">
                            <xsl:with-param name="returnNode" select="$return"/>
                            <xsl:with-param name="orderNode" select="$order"/>
                            <xsl:with-param name="activityName" select="'Refund Approved'"/>
                            <xsl:with-param name="eventTime" select="custom-attributes/custom-attribute[@attribute-id='refundApprovedDate']"/>
                            <xsl:with-param name="sku" select="''"/>
                        </xsl:call-template>
                    </xsl:if>

                    <!-- 10. Refund Initiated -->
                    <xsl:if test="return-payment-status='REFUND_INITIATED'">
                        <xsl:call-template name="CreateRow">
                            <xsl:with-param name="returnNode" select="$return"/>
                            <xsl:with-param name="orderNode" select="$order"/>
                            <xsl:with-param name="activityName" select="'Refund Initiated'"/>
                            <xsl:with-param name="eventTime" select="$return/last-modified"/>
                            <xsl:with-param name="sku" select="''"/>
                        </xsl:call-template>
                    </xsl:if>

                    <!-- 11. Refund Processed -->
                    <xsl:if test="return-payment-status='REFUNDED'">
                        <xsl:call-template name="CreateRow">
                            <xsl:with-param name="returnNode" select="$return"/>
                            <xsl:with-param name="orderNode" select="$order"/>
                            <xsl:with-param name="activityName" select="'Refund Processed'"/>
                            <xsl:with-param name="eventTime" select="$return/last-modified"/>
                            <xsl:with-param name="sku" select="''"/>
                        </xsl:call-template>
                    </xsl:if>

                    <!-- 12. Exchange Order Created -->
                    <xsl:if test="exchange-order-no">
                        <xsl:call-template name="CreateRow">
                            <xsl:with-param name="returnNode" select="$return"/>
                            <xsl:with-param name="orderNode" select="$order"/>
                            <xsl:with-param name="activityName" select="'Exchange Order Created'"/>
                            <xsl:with-param name="eventTime" select="$return/last-modified"/>
                            <xsl:with-param name="sku" select="''"/>
                        </xsl:call-template>
                    </xsl:if>

                    <!-- 13. Return Case Closed -->
                    <xsl:if test="status='COMPLETED'">
                        <xsl:call-template name="CreateRow">
                            <xsl:with-param name="returnNode" select="$return"/>
                            <xsl:with-param name="orderNode" select="$order"/>
                            <xsl:with-param name="activityName" select="'Return Case Closed'"/>
                            <xsl:with-param name="eventTime" select="$return/last-modified"/>
                            <xsl:with-param name="sku" select="''"/>
                        </xsl:call-template>
                    </xsl:if>

                </xsl:for-each>
            </xsl:template>

            <!-- Reusable template to generate a single CSV row -->
            <xsl:template name="CreateRow">
                <xsl:param name="returnNode"/>
                <xsl:param name="orderNode"/>
                <xsl:param name="activityName"/>
                <xsl:param name="eventTime"/>
                <xsl:param name="sku"/>

                <xsl:value-of select="concat($quote, $returnNode/return-number, $quote, $delimiter)"/>
                <xsl:value-of select="concat($quote, $activityName, $quote, $delimiter)"/>
                <xsl:value-of select="concat($quote, $eventTime, $quote, $delimiter)"/>
                <xsl:value-of select="concat($quote, 'Salesforce Commerce Cloud', $quote, $delimiter)"/>
                <xsl:value-of select="concat($quote, $lastDataUpdate, $quote, $delimiter)"/>
                <xsl:value-of select="concat($quote, $returnNode/created-by, $quote, $delimiter)"/>
                <xsl:value-of select="concat($quote, normalize-space($returnNode/items/return-item[1]/reason-text), $quote, $delimiter)"/>
                <xsl:value-of select="concat($quote, $sku, $quote, $delimiter)"/>
                <xsl:value-of select="concat($quote, $returnNode/totals/grand-total, $quote, $delimiter)"/>
                <xsl:value-of select="concat($quote, $returnNode/totals/refunded-total, $quote, $delimiter)"/>
                <xsl:value-of select="concat($quote, $orderNode/channel-type, $quote, $delimiter)"/>
                <xsl:text>&#10;</xsl:text>
            </xsl:template>

        </xsl:stylesheet>
    </data-transformation>
</export>

import requests
import json
import pandas as pd
from datetime import datetime, timezone

class=class="hl-string">"hl-comment">## CONFIGURATION ##
CLIENT_ID = class="hl-string">'[Your_Salesforce_Client_ID]'
CLIENT_SECRET = class="hl-string">'[Your_Salesforce_Client_Secret]'
BASE_URL = class="hl-string">'https://[your-instance].demandware.net'
API_VERSION = class="hl-string">'v23_1' class=class="hl-string">"hl-comment"># Example version, update as needed
SITE_ID = class="hl-string">'[Your_Site_ID]'
START_DATE = class="hl-string">'2023-01-01' class=class="hl-string">"hl-comment"># Inclusive start date YYYY-MM-DD
END_DATE = class="hl-string">'2023-06-30' class=class="hl-string">"hl-comment"># Inclusive end date YYYY-MM-DD
COUNT = 200 class=class="hl-string">"hl-comment"># Number of records per API call, max is 200

def get_auth_token():
    class="hl-string">"""Authenticates with Salesforce OCAPI and returns a bearer token."""
    url = fclass="hl-string">'{BASE_URL}/dw/oauth2/access_token'
    payload = {
        class="hl-string">'grant_type': class="hl-string">'client_credentials'
    }
    try:
        response = requests.post(url, auth=(CLIENT_ID, CLIENT_SECRET), data=payload)
        response.raise_for_status()
        return response.json().get(class="hl-string">'access_token')
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Error getting auth token: {e}")
        return None

def process_return_data(return_case, last_update_ts):
    class="hl-string">"""Processes a single return case JSON and generates event log rows."""
    events = []
    case_id = return_case.get(class="hl-string">'return_order_no')
    creation_time = return_case.get(class="hl-string">'creation_date')
    last_modified_time = return_case.get(class="hl-string">'last_modified')
    status = return_case.get(class="hl-string">'status')
    return_reason = return_case.get(class="hl-string">'return_reason') class=class="hl-string">"hl-comment"># Placeholder, adjust to actual field
    channel = return_case.get(class="hl-string">'channel_type') class=class="hl-string">"hl-comment"># Example field for channel

    def create_event(activity, event_time, item=None):
        class=class="hl-string">"hl-comment"># Helper to create a single event dictionary
        if not event_time:
            return None
        
        class=class="hl-string">"hl-comment"># Base attributes
        base_event = {
            class="hl-string">'ReturnCaseId': case_id,
            class="hl-string">'ActivityName': activity,
            class="hl-string">'EventTime': event_time,
            class="hl-string">'SourceSystem': class="hl-string">'Salesforce Commerce Cloud',
            class="hl-string">'LastDataUpdate': last_update_ts,
            class="hl-string">'ReturnReason': return_reason,
            class="hl-string">'ReturnChannel': channel,
            class="hl-string">'ProcessingAgent': return_case.get(class="hl-string">'created_by'),
            class="hl-string">'RequestedRefundAmount': return_case.get(class="hl-string">'price_total'), class=class="hl-string">"hl-comment"># Total value of return
            class="hl-string">'ActualRefundAmount': None, class=class="hl-string">"hl-comment"># Populated later from payment info
            class="hl-string">'ProductSku': None,
            class="hl-string">'ReturnCycleTime': None class=class="hl-string">"hl-comment"># This would typically be calculated post-extraction
        }

        if item:
            base_event[class="hl-string">'ProductSku'] = item.get(class="hl-string">'product_id')
            base_event[class="hl-string">'RequestedRefundAmount'] = item.get(class="hl-string">'price')

        class=class="hl-string">"hl-comment"># Populate refund amount from payment info if available
        if return_case.get(class="hl-string">'payments') and class="hl-string">'amount' in return_case[class="hl-string">'payments'][0]:
            base_event[class="hl-string">'ActualRefundAmount'] = return_case[class="hl-string">'payments'][0][class="hl-string">'amount']

        return base_event

    class=class="hl-string">"hl-comment"># 1. Return Request Created
    events.append(create_event(class="hl-string">'Return Request Created', creation_time))

    class=class="hl-string">"hl-comment"># Status-based events use last_modified as a proxy for the event time
    class=class="hl-string">"hl-comment"># 2. Return Request Approved
    if status == class="hl-string">'APPROVED':
        events.append(create_event(class="hl-string">'Return Request Approved', last_modified_time))
    
    class=class="hl-string">"hl-comment"># 3. Return Request Rejected
    if status in [class="hl-string">'CANCELED', class="hl-string">'REJECTED']:
        events.append(create_event(class="hl-string">'Return Request Rejected', last_modified_time))

    class=class="hl-string">"hl-comment"># 13. Return Case Closed
    if status == class="hl-string">'CLOSED':
        events.append(create_event(class="hl-string">'Return Case Closed', last_modified_time))

    class=class="hl-string">"hl-comment"># Item-level events
    if return_case.get(class="hl-string">'items'):
        for item in return_case[class="hl-string">'items']:
            item_status = item.get(class="hl-string">'status')
            item_last_modified = item.get(class="hl-string">'last_modified') class=class="hl-string">"hl-comment"># Assume item has its own modified time

            class=class="hl-string">"hl-comment"># 5. Item Received At Warehouse
            if item_status == class="hl-string">'RECEIVED':
                events.append(create_event(class="hl-string">'Item Received At Warehouse', item_last_modified, item=item))
            class=class="hl-string">"hl-comment"># 6. Item Inspection Started
            if item.get(class="hl-string">'c_inspectionStartedDate'): class=class="hl-string">"hl-comment"># Example custom field
                events.append(create_event(class="hl-string">'Item Inspection Started', item.get(class="hl-string">'c_inspectionStartedDate'), item=item))
            class=class="hl-string">"hl-comment"># 7. Item Inspection Completed
            if item.get(class="hl-string">'c_inspectionCompletedDate'): class=class="hl-string">"hl-comment"># Example custom field
                events.append(create_event(class="hl-string">'Item Inspection Completed', item.get(class="hl-string">'c_inspectionCompletedDate'), item=item))

    class=class="hl-string">"hl-comment"># Payment/Refund-level events
    if return_case.get(class="hl-string">'payments'):
        for payment in return_case[class="hl-string">'payments']:
            payment_status = payment.get(class="hl-string">'status') class=class="hl-string">"hl-comment"># Hypothetical status field
            payment_last_modified = payment.get(class="hl-string">'last_modified') class=class="hl-string">"hl-comment"># Hypothetical timestamp

            class=class="hl-string">"hl-comment"># 8. Refund Amount Calculated
            class=class="hl-string">"hl-comment"># This is often implicit. We can use the payment record creation date as a proxy.
            events.append(create_event(class="hl-string">'Refund Amount Calculated', payment.get(class="hl-string">'creation_date')))
            
            class=class="hl-string">"hl-comment"># 9. Refund Approved
            if payment_status == class="hl-string">'APPROVED_FOR_REFUND':
                events.append(create_event(class="hl-string">'Refund Approved', payment_last_modified))
            class=class="hl-string">"hl-comment"># 10. Refund Initiated
            if payment_status == class="hl-string">'REFUND_INITIATED':
                events.append(create_event(class="hl-string">'Refund Initiated', payment_last_modified))
            class=class="hl-string">"hl-comment"># 11. Refund Processed
            if payment_status == class="hl-string">'PROCESSED':
                events.append(create_event(class="hl-string">'Refund Processed', payment_last_modified))

    class=class="hl-string">"hl-comment"># Integration-dependent events (using placeholder custom attributes)
    class=class="hl-string">"hl-comment"># 4. Return Label Generated
    if return_case.get(class="hl-string">'c_labelGeneratedDate'):
        events.append(create_event(class="hl-string">'Return Label Generated', return_case.get(class="hl-string">'c_labelGeneratedDate')))

    class=class="hl-string">"hl-comment"># 12. Exchange Order Created
    if return_case.get(class="hl-string">'exchange_order_no') and return_case.get(class="hl-string">'c_exchangeOrderCreationDate'):
        events.append(create_event(class="hl-string">'Exchange Order Created', return_case.get(class="hl-string">'c_exchangeOrderCreationDate')))
    
    class=class="hl-string">"hl-comment"># Filter out any null events that might have been created
    return [e for e in events if e is not None]

def main():
    class="hl-string">"""Main function to orchestrate the data extraction."""
    token = get_auth_token()
    if not token:
        return

    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json'
    }

    all_events = []
    start_index = 0
    total_records = -1
    last_update_ts = datetime.now(timezone.utc).isoformat()

    print(class="hl-string">'Starting extraction...')

    while total_records == -1 or start_index < total_records:
        class=class="hl-string">"hl-comment"># Construct query to filter by creation date
        query = {
            class="hl-string">"query": {
                class="hl-string">"filtered_query": {
                    class="hl-string">"query": {class="hl-string">"match_all_query": {}},
                    class="hl-string">"filter": {
                        class="hl-string">"range_filter": {
                            class="hl-string">"field": class="hl-string">"creation_date",
                            class="hl-string">"from": fclass="hl-string">"{START_DATE}T00:00:00.000Z",
                            class="hl-string">"to": fclass="hl-string">"{END_DATE}T23:59:59.999Z"
                        }
                    }
                }
            },
            class="hl-string">"select": class="hl-string">"(**)",
            class="hl-string">"expand": [class="hl-string">"items", class="hl-string">"payments"]
        }

        url = fclass="hl-string">'{BASE_URL}/s/{SITE_ID}/dw/data/{API_VERSION}/returns?start={start_index}&count={COUNT}'
        
        try:
            response = requests.post(fclass="hl-string">'{BASE_URL}/s/{SITE_ID}/dw/data/{API_VERSION}/returns/search', headers=headers, json=query)
            response.raise_for_status()
            data = response.json()

            if total_records == -1:
                total_records = data.get(class="hl-string">'total', 0)
                print(fclass="hl-string">'Found {total_records} return records to process.')

            return_hits = data.get(class="hl-string">'hits', [])
            if not return_hits:
                break

            for hit in return_hits:
                events = process_return_data(hit, last_update_ts)
                all_events.extend(events)

            start_index += len(return_hits)
            print(fclass="hl-string">'Processed {start_index} of {total_records} records...')

        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"An API error occurred: {e}")
            print(fclass="hl-string">"Response body: {response.text}")
            break

    if all_events:
        df = pd.DataFrame(all_events)
        class=class="hl-string">"hl-comment"># Ensure required columns exist even if empty
        for col in [class="hl-string">'ReturnCaseId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate']:
            if col not in df.columns:
                df[col] = None
        df.to_csv(class="hl-string">'returns_event_log.csv', index=False)
        print(fclass="hl-string">'Successfully extracted {len(df)} events to returns_event_log.csv')
    else:
        print(class="hl-string">'No events were extracted.')

if __name__ == class="hl-string">'__main__':
    main()