退货与退款处理数据模板

/*
This is a conceptual guide representing multiple, distinct OData API calls. 
You will need a script (e.g., Python, PowerShell) to execute these calls sequentially, 
authenticate with a bearer token, handle pagination, and union the results into a single file.
Replace [YourD365URL], [StartDate], [EndDate], and [YourCompanyCode] with your specific values.
*/

// Base URL for all requests
const string BaseUrl = "https://[YourD365URL].dynamics.com/data";
const string CompanyFilter = "?cross-company=true&$filter=dataAreaId eq '[YourCompanyCode]' and ";
const string DateFilterCreated = "createdDateTime ge [StartDate]T00:00:00Z and createdDateTime le [EndDate]T23:59:59Z";
const string DateFilterModified = "modifiedDateTime ge [StartDate]T00:00:00Z and modifiedDateTime le [EndDate]T23:59:59Z";

// 1. Return Order Created
GET {BaseUrl}/ReturnOrderHeaders{CompanyFilter}{DateFilterCreated}&$select=ReturnOrderNumber,createdDateTime,createdby,ReturnReasonCodeId
// Mapping: ReturnOrderNumber -> ReturnCaseId, 'Return Order Created' -> ActivityName, createdDateTime -> EventTime, createdby -> ResponsibleUser, ReturnReasonCodeId -> ReturnReasonCode

// 2. Return Order Confirmed
// This often updates the header status. We look for a modification time on confirmed orders.
GET {BaseUrl}/ReturnOrderHeaders{CompanyFilter}ReturnOrderStatus eq 'Confirmed' and {DateFilterModified}&$select=ReturnOrderNumber,modifiedDateTime,modifiedby,ReturnReasonCodeId
// Mapping: ReturnOrderNumber -> ReturnCaseId, 'Return Order Confirmed' -> ActivityName, modifiedDateTime -> EventTime, modifiedby -> ResponsibleUser

// 3. Arrival Journal Created
GET {BaseUrl}/WarehouseArrivalJournalHeaders{CompanyFilter}{DateFilterCreated}&$expand=WarehouseArrivalJournalLines($select=InventTransactionId)&$select=JournalNumber,createdDateTime,createdby
// Note: This requires post-processing to link JournalNumber to a ReturnCaseId via InventTransactionId.
// Mapping: Link via InventTrans -> ReturnCaseId, 'Arrival Journal Created' -> ActivityName, createdDateTime -> EventTime, createdby -> ResponsibleUser

// 4. Item Received (Arrival Journal Posted)
GET {BaseUrl}/WarehouseArrivalJournalHeaders{CompanyFilter}JournalPosted eq 'Yes' and {DateFilterModified}&$expand=WarehouseArrivalJournalLines($select=InventTransactionId)&$select=JournalNumber,modifiedDateTime,modifiedby
// Mapping: Link via InventTrans -> ReturnCaseId, 'Item Received' -> ActivityName, modifiedDateTime -> EventTime, modifiedby -> ResponsibleUser

// 5. Quality Order Generated
GET {BaseUrl}/InventQualityOrders{CompanyFilter}{DateFilterCreated}&$select=QualityOrderId,InventTransId,createdDateTime,CreatedByUserId,ItemId
// Mapping: Link via InventTransId -> ReturnCaseId, 'Quality Order Generated' -> ActivityName, createdDateTime -> EventTime, CreatedByUserId -> ResponsibleUser, ItemId -> ProductId

// 6. Disposition Code Applied
// This is a status change on the return line.
GET {BaseUrl}/ReturnOrderLines{CompanyFilter}ReturnDispositionCodeId ne '' and {DateFilterModified}&$select=ReturnOrderNumber,modifiedDateTime,modifiedby,ReturnDispositionCodeId,ItemId
// Mapping: ReturnOrderNumber -> ReturnCaseId, 'Disposition Code Applied' -> ActivityName, modifiedDateTime -> EventTime, modifiedby -> ResponsibleUser, ReturnDispositionCodeId -> DispositionCode, ItemId -> ProductId

// 7. Credit Note Created
// Look for sales orders with type 'Returned Order' that are not yet invoiced.
GET {BaseUrl}/SalesOrderHeadersV2{CompanyFilter}SalesOrderProcessingStatus eq 'Open' and SalesOrderType eq 'ReturnedOrder' and {DateFilterCreated}&$select=SalesOrderNumber,createdDateTime,createdby
// Mapping: SalesOrderNumber -> ReturnCaseId, 'Credit Note Created' -> ActivityName, createdDateTime -> EventTime, createdby -> ResponsibleUser

// 8. Credit Note Posted
// Look for posted invoice journals linked to a return order.
GET {BaseUrl}/SalesInvoiceJournalHeaders{CompanyFilter}SalesOrderType eq 'ReturnedOrder' and {DateFilterCreated}&$select=SalesOrderNumber,InvoiceDate,createdby
// Mapping: SalesOrderNumber -> ReturnCaseId, 'Credit Note Posted' -> ActivityName, InvoiceDate -> EventTime, createdby -> ResponsibleUser

// 9. Replacement Order Created
// Disposition code on the return line triggers a replacement order.
GET {BaseUrl}/SalesOrderHeadersV2{CompanyFilter}SalesOrderOriginType eq 'ReturnOrder' and {DateFilterCreated}&$select=SalesOrderNumber,createdDateTime,createdby,ReturnOrderNumber
// Mapping: ReturnOrderNumber -> ReturnCaseId, 'Replacement Order Created' -> ActivityName, createdDateTime -> EventTime, createdby -> ResponsibleUser

// 10. Replacement Item Shipped
// Check for posted packing slips related to the replacement sales order.
GET {BaseUrl}/SalesPackingSlipJournals{CompanyFilter}{DateFilterCreated}&$select=SalesOrderNumber,DeliveryDate,createdby
// Note: This requires linking SalesOrderNumber back to the original ReturnOrderNumber for the ReturnCaseId.
// Mapping: Link SalesOrderNumber -> ReturnCaseId, 'Replacement Item Shipped' -> ActivityName, DeliveryDate -> EventTime, createdby -> ResponsibleUser

// 11. Return Order Closed
GET {BaseUrl}/ReturnOrderHeaders{CompanyFilter}ReturnOrderStatus eq 'Closed' and {DateFilterModified}&$select=ReturnOrderNumber,modifiedDateTime,modifiedby
// Mapping: ReturnOrderNumber -> ReturnCaseId, 'Return Order Closed' -> ActivityName, modifiedDateTime -> EventTime, modifiedby -> ResponsibleUser

// 12. Return Order Cancelled
GET {BaseUrl}/ReturnOrderHeaders{CompanyFilter}ReturnOrderStatus eq 'Canceled' and {DateFilterModified}&$select=ReturnOrderNumber,modifiedDateTime,modifiedby
// Mapping: ReturnOrderNumber -> ReturnCaseId, 'Return Order Cancelled' -> ActivityName, modifiedDateTime -> EventTime, modifiedby -> ResponsibleUser

SELECT
    st.RETURNITEMNUM AS ReturnCaseId,
    'Return Order Created' AS ActivityName,
    st.CREATEDDATETIME AS EventTime,
    st.CREATEDBY AS ResponsibleUser,
    sl.RETURNDISPOSITIONCODEID AS DispositionCode,
    sl.RETURNREASONCODEID AS ReturnReasonCode,
    st.SALESORIGINID AS ReturnChannel,
    sl.ITEMID AS ProductId
FROM SALESTABLE st
JOIN SALESLINE sl ON st.SALESID = sl.SALESID AND st.DATAAREAID = sl.DATAAREAID
WHERE st.SALESTYPE = 3 AND st.CREATEDDATETIME BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    st.RETURNITEMNUM AS ReturnCaseId,
    'Return Order Confirmed' AS ActivityName,
    st.MODIFIEDDATETIME AS EventTime,
    st.MODIFIEDBY AS ResponsibleUser,
    sl.RETURNDISPOSITIONCODEID AS DispositionCode,
    sl.RETURNREASONCODEID AS ReturnReasonCode,
    st.SALESORIGINID AS ReturnChannel,
    sl.ITEMID AS ProductId
FROM SALESTABLE st
JOIN SALESLINE sl ON st.SALESID = sl.SALESID AND st.DATAAREAID = sl.DATAAREAID
WHERE st.SALESTYPE = 3 AND st.DOCUMENTSTATUS = 1 AND st.CREATEDDATETIME BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    st.RETURNITEMNUM AS ReturnCaseId,
    'Arrival Journal Created' AS ActivityName,
    wjt.CREATEDDATETIME AS EventTime,
    wjt.CREATEDBY AS ResponsibleUser,
    sl.RETURNDISPOSITIONCODEID AS DispositionCode,
    sl.RETURNREASONCODEID AS ReturnReasonCode,
    st.SALESORIGINID AS ReturnChannel,
    sl.ITEMID AS ProductId
FROM SALESTABLE st
JOIN SALESLINE sl ON st.SALESID = sl.SALESID AND st.DATAAREAID = sl.DATAAREAID
JOIN WMSJOURNALTABLE wjt ON st.SALESID = wjt.ORDERID AND st.DATAAREAID = wjt.DATAAREAID
WHERE st.SALESTYPE = 3 AND wjt.JOURNALTYPE = 4 AND st.CREATEDDATETIME BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    st.RETURNITEMNUM AS ReturnCaseId,
    'Item Received' AS ActivityName,
    wjt.POSTEDDATETIME AS EventTime,
    wjt.POSTEDUSERID AS ResponsibleUser,
    sl.RETURNDISPOSITIONCODEID AS DispositionCode,
    sl.RETURNREASONCODEID AS ReturnReasonCode,
    st.SALESORIGINID AS ReturnChannel,
    sl.ITEMID AS ProductId
FROM SALESTABLE st
JOIN SALESLINE sl ON st.SALESID = sl.SALESID AND st.DATAAREAID = sl.DATAAREAID
JOIN WMSJOURNALTABLE wjt ON st.SALESID = wjt.ORDERID AND st.DATAAREAID = wjt.DATAAREAID
WHERE st.SALESTYPE = 3 AND wjt.JOURNALTYPE = 4 AND wjt.POSTEDDATETIME IS NOT NULL AND st.CREATEDDATETIME BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    st.RETURNITEMNUM AS ReturnCaseId,
    'Quality Order Generated' AS ActivityName,
    iqot.CREATEDDATETIME AS EventTime,
    iqot.CREATEDBY AS ResponsibleUser,
    sl.RETURNDISPOSITIONCODEID AS DispositionCode,
    sl.RETURNREASONCODEID AS ReturnReasonCode,
    st.SALESORIGINID AS ReturnChannel,
    iqot.ITEMID AS ProductId
FROM SALESTABLE st
JOIN SALESLINE sl ON st.SALESID = sl.SALESID AND st.DATAAREAID = sl.DATAAREAID
JOIN INVENTQUALITYORDERTABLE iqot ON sl.INVENTTRANSID = iqot.INVENTTRANSID AND sl.DATAAREAID = iqot.DATAAREAID
WHERE st.SALESTYPE = 3 AND st.CREATEDDATETIME BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    st.RETURNITEMNUM AS ReturnCaseId,
    'Disposition Code Applied' AS ActivityName,
    iqot.VALIDATEDDATETIME AS EventTime,
    iqot.VALIDATEDBY AS ResponsibleUser,
    sl.RETURNDISPOSITIONCODEID AS DispositionCode,
    sl.RETURNREASONCODEID AS ReturnReasonCode,
    st.SALESORIGINID AS ReturnChannel,
    iqot.ITEMID AS ProductId
FROM SALESTABLE st
JOIN SALESLINE sl ON st.SALESID = sl.SALESID AND st.DATAAREAID = sl.DATAAREAID
JOIN INVENTQUALITYORDERTABLE iqot ON sl.INVENTTRANSID = iqot.INVENTTRANSID AND sl.DATAAREAID = iqot.DATAAREAID
WHERE st.SALESTYPE = 3 AND iqot.VALIDATEDDATETIME IS NOT NULL AND st.CREATEDDATETIME BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    st.RETURNITEMNUM AS ReturnCaseId,
    'Credit Note Created' AS ActivityName,
    st.MODIFIEDDATETIME AS EventTime,
    st.MODIFIEDBY AS ResponsibleUser,
    sl.RETURNDISPOSITIONCODEID AS DispositionCode,
    sl.RETURNREASONCODEID AS ReturnReasonCode,
    st.SALESORIGINID AS ReturnChannel,
    sl.ITEMID AS ProductId
FROM SALESTABLE st
JOIN SALESLINE sl ON st.SALESID = sl.SALESID AND st.DATAAREAID = sl.DATAAREAID
WHERE st.SALESTYPE = 3 AND st.SALESSTATUS = 3 AND st.CREATEDDATETIME BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    st.RETURNITEMNUM AS ReturnCaseId,
    'Credit Note Posted' AS ActivityName,
    cij.CREATEDDATETIME AS EventTime,
    cij.CREATEDBY AS ResponsibleUser,
    sl.RETURNDISPOSITIONCODEID AS DispositionCode,
    sl.RETURNREASONCODEID AS ReturnReasonCode,
    st.SALESORIGINID AS ReturnChannel,
    sl.ITEMID AS ProductId
FROM SALESTABLE st
JOIN SALESLINE sl ON st.SALESID = sl.SALESID AND st.DATAAREAID = sl.DATAAREAID
JOIN CUSTINVOICEJOUR cij ON st.SALESID = cij.SALESID AND st.DATAAREAID = cij.DATAAREAID
WHERE st.SALESTYPE = 3 AND st.CREATEDDATETIME BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    ro.RETURNITEMNUM AS ReturnCaseId,
    'Replacement Order Created' AS ActivityName,
    replacement_so.CREATEDDATETIME AS EventTime,
    replacement_so.CREATEDBY AS ResponsibleUser,
    NULL AS DispositionCode,
    NULL AS ReturnReasonCode,
    replacement_so.SALESORIGINID AS ReturnChannel,
    replacement_sl.ITEMID AS ProductId
FROM SALESTABLE ro
JOIN SALESLINE rol ON ro.SALESID = rol.SALESID AND ro.DATAAREAID = rol.DATAAREAID
JOIN SALESTABLE replacement_so ON ro.CUSTACCOUNT = replacement_so.CUSTACCOUNT AND ro.DATAAREAID = replacement_so.DATAAREAID
JOIN SALESLINE replacement_sl ON replacement_so.SALESID = replacement_sl.SALESID AND replacement_so.DATAAREAID = replacement_sl.DATAAREAID
WHERE ro.SALESTYPE = 3
AND rol.RETURNDISPOSITIONCODEID IN ('[YourReplaceCode1]', '[YourReplaceCode2]')
AND replacement_so.SALESTYPE = 1
AND replacement_so.CREATEDDATETIME > ro.CREATEDDATETIME
-- The join above is a basic example and must be replaced with your system's specific logic for linking returns to replacements.
AND ro.CREATEDDATETIME BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    ro.RETURNITEMNUM AS ReturnCaseId,
    'Replacement Item Shipped' AS ActivityName,
    cpsj.CREATEDDATETIME AS EventTime,
    cpsj.CREATEDBY AS ResponsibleUser,
    NULL AS DispositionCode,
    NULL AS ReturnReasonCode,
    replacement_so.SALESORIGINID AS ReturnChannel,
    cpsl.ITEMID AS ProductId
FROM SALESTABLE ro
JOIN SALESLINE rol ON ro.SALESID = rol.SALESID AND ro.DATAAREAID = rol.DATAAREAID
JOIN SALESTABLE replacement_so ON ro.CUSTACCOUNT = replacement_so.CUSTACCOUNT AND ro.DATAAREAID = replacement_so.DATAAREAID
JOIN CUSTPACKINGSLIPJOUR cpsj ON replacement_so.SALESID = cpsj.SALESID AND replacement_so.DATAAREAID = cpsj.DATAAREAID
JOIN CUSTPACKINGSLIPTRANS cpsl ON cpsj.PACKINGSLIPID = cpsl.PACKINGSLIPID AND cpsj.SALESID = cpsl.SALESID AND cpsj.DATAAREAID = cpsl.DATAAREAID
WHERE ro.SALESTYPE = 3
AND rol.RETURNDISPOSITIONCODEID IN ('[YourReplaceCode1]', '[YourReplaceCode2]')
AND replacement_so.SALESTYPE = 1
AND replacement_so.CREATEDDATETIME > ro.CREATEDDATETIME
-- The join above is a basic example and must be replaced with your system's specific logic for linking returns to replacements.
AND ro.CREATEDDATETIME BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    st.RETURNITEMNUM AS ReturnCaseId,
    'Return Order Closed' AS ActivityName,
    st.MODIFIEDDATETIME AS EventTime,
    st.MODIFIEDBY AS ResponsibleUser,
    sl.RETURNDISPOSITIONCODEID AS DispositionCode,
    sl.RETURNREASONCODEID AS ReturnReasonCode,
    st.SALESORIGINID AS ReturnChannel,
    sl.ITEMID AS ProductId
FROM SALESTABLE st
JOIN SALESLINE sl ON st.SALESID = sl.SALESID AND st.DATAAREAID = sl.DATAAREAID
WHERE st.SALESTYPE = 3 AND st.SALESSTATUS = 3 AND st.CREATEDDATETIME BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

SELECT
    st.RETURNITEMNUM AS ReturnCaseId,
    'Return Order Cancelled' AS ActivityName,
    st.MODIFIEDDATETIME AS EventTime,
    st.MODIFIEDBY AS ResponsibleUser,
    sl.RETURNDISPOSITIONCODEID AS DispositionCode,
    sl.RETURNREASONCODEID AS ReturnReasonCode,
    st.SALESORIGINID AS ReturnChannel,
    sl.ITEMID AS ProductId
FROM SALESTABLE st
JOIN SALESLINE sl ON st.SALESID = sl.SALESID AND st.DATAAREAID = sl.DATAAREAID
WHERE st.SALESTYPE = 3 AND st.SALESSTATUS = 4 AND st.CREATEDDATETIME BETWEEN '{StartDate}' AND '{EndDate}';