返品・返金処理データテンプレート

This configuration represents the settings in the NetSuite Saved Search UI. The 'Results' tab should be configured with the following columns and formulas.

**Criteria Tab:**
*   `Type` = `Return Authorization`
*   `Main Line` = `true`
*   `Date Created` = `[Specify Desired Date Range]`

**Results Tab (Columns):**

| Custom Label | Field / Formula Type | Formula / Field ID |
|---|---|---|
| `ReturnCaseId` | Formula (Text) | `{tranid}` |
| `SourceSystem` | Formula (Text) | `'NetSuite'` |
| `LastDataUpdate` | Formula (Date/Time) | `{today}` |
| `ReturnAuthorizationStatus` | Field | `Status` |
| `ProcessingAgent` | Field | `Created By` |
| `Department` | Field | `Department` |
| `ReturnType` | Field | `{custbody_return_reason}` |
| `ActualRefundAmount` | Field | `Amount` |
| `CycleTime` | Formula (Numeric) | `CASE WHEN {status} = 'Closed' THEN {lastmodifieddate} - {datecreated} ELSE NULL END` |
| `Activity_ReturnAuthorizationCreated` | Field | `Date Created` |
| `Activity_ReturnAuthorizationApproved` | Formula (Date/Time) | `MIN(CASE WHEN {systemnotes.newvalue} = 'Pending Receipt' AND {systemnotes.field} = 'Status' THEN {systemnotes.date} ELSE NULL END)` |
| `Activity_ReturnAuthorizationRejected` | Formula (Date/Time) | `MIN(CASE WHEN {systemnotes.newvalue} = 'Rejected' AND {systemnotes.field} = 'Status' THEN {systemnotes.date} ELSE NULL END)` |
| `Activity_ItemReceived` | Formula (Date/Time) | `{applyingtransaction.trandate}` |
| `Activity_ItemInspected` | Formula (Date/Time) | `CASE WHEN {custbody_inspection_status} = 'Complete' THEN {custbody_inspection_date} ELSE NULL END` |
| `Activity_CreditMemoCreated` | Formula (Date/Time) | `{createdfrom.trandate}` |
| `Activity_CreditMemoApproved` | Formula (Date/Time) | `MIN(CASE WHEN {createdfrom.systemnotes.newvalue} = 'Open' AND {createdfrom.systemnotes.field} = 'Status' THEN {createdfrom.systemnotes.date} ELSE NULL END)` |
| `Activity_RefundProcessed` | Formula (Date/Time) | `{createdfrom.appliedtotransaction.trandate}` |
| `Activity_CreditMemoApplied` | Formula (Date/Time) | `CASE WHEN {createdfrom.status} = 'Fully Applied' AND {createdfrom.appliedtotransaction.type} = 'Invoice' THEN {createdfrom.appliedtotransaction.date} ELSE NULL END` |
| `Activity_ExchangeOrderCreated` | Formula (Date/Time) | `{custbody_exchange_order.trandate}` |
| `Activity_CustomerNotified` | Formula (Date/Time) | `MAX({messages.messagedate})` |
| `Activity_ReturnAuthorizationClosed` | Formula (Date/Time) | `MIN(CASE WHEN {systemnotes.newvalue} = 'Closed' AND {systemnotes.field} = 'Status' THEN {systemnotes.date} ELSE NULL END)` |

WITH ReturnAuthorizations AS (
    SELECT
        T.TRANSACTION_ID AS ReturnCaseId,
        T.TRANDATE
    FROM
        TRANSACTION T
    WHERE
        T.TYPE = 'ReturnAuthorization'
        AND T.TRANDATE BETWEEN TO_DATE('[YYYY-MM-DD]', 'YYYY-MM-DD') AND TO_DATE('[YYYY-MM-DD]', 'YYYY-MM-DD')
)
SELECT
    RA.ReturnCaseId AS "ReturnCaseId",
    'Return Authorization Created' AS "ActivityName",
    T.CREATED_DATE AS "EventTimestamp",
    'NetSuite' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate",
    T.STATUS AS "ReturnAuthorizationStatus",
    E.full_name AS "ProcessingAgent",
    D.full_name AS "Department",
    T.CUSTBODY_RETURN_TYPE AS "ReturnType",
    NULL AS "ActualRefundAmount",
    NULL AS "CycleTime"
FROM TRANSACTION T
INNER JOIN ReturnAuthorizations RA ON T.TRANSACTION_ID = RA.ReturnCaseId
LEFT JOIN EMPLOYEE E ON T.CREATED_BY_ID = E.EMPLOYEE_ID
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

UNION ALL

SELECT
    RA.ReturnCaseId,
    'Return Authorization Approved' AS "ActivityName",
    SN.NOTE_DATE AS "EventTimestamp",
    'NetSuite' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate",
    SN.NEW_VALUE AS "ReturnAuthorizationStatus",
    E.full_name AS "ProcessingAgent",
    D.full_name AS "Department",
    T.CUSTBODY_RETURN_TYPE AS "ReturnType",
    NULL AS "ActualRefundAmount",
    NULL AS "CycleTime"
FROM SYSTEM_NOTES SN
INNER JOIN TRANSACTION T ON SN.TRANSACTION_ID = T.TRANSACTION_ID
INNER JOIN ReturnAuthorizations RA ON T.TRANSACTION_ID = RA.ReturnCaseId
LEFT JOIN EMPLOYEE E ON SN.AUTHOR_ID = E.EMPLOYEE_ID
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE SN.FIELD = 'TRANSACTION.STATUS' AND SN.NEW_VALUE = 'Pending Receipt'

UNION ALL

SELECT
    RA.ReturnCaseId,
    'Return Authorization Rejected' AS "ActivityName",
    SN.NOTE_DATE AS "EventTimestamp",
    'NetSuite' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate",
    SN.NEW_VALUE AS "ReturnAuthorizationStatus",
    E.full_name AS "ProcessingAgent",
    D.full_name AS "Department",
    T.CUSTBODY_RETURN_TYPE AS "ReturnType",
    NULL AS "ActualRefundAmount",
    NULL AS "CycleTime"
FROM SYSTEM_NOTES SN
INNER JOIN TRANSACTION T ON SN.TRANSACTION_ID = T.TRANSACTION_ID
INNER JOIN ReturnAuthorizations RA ON T.TRANSACTION_ID = RA.ReturnCaseId
LEFT JOIN EMPLOYEE E ON SN.AUTHOR_ID = E.EMPLOYEE_ID
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE SN.FIELD = 'TRANSACTION.STATUS' AND SN.NEW_VALUE IN ('Rejected', 'Closed')

UNION ALL

SELECT
    RA.ReturnCaseId,
    'Item Received' AS "ActivityName",
    IR.CREATED_DATE AS "EventTimestamp",
    'NetSuite' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate",
    T_RA.STATUS AS "ReturnAuthorizationStatus",
    E.full_name AS "ProcessingAgent",
    D.full_name AS "Department",
    T_RA.CUSTBODY_RETURN_TYPE AS "ReturnType",
    NULL AS "ActualRefundAmount",
    NULL AS "CycleTime"
FROM TRANSACTION IR
INNER JOIN TRANSACTION T_RA ON IR.CREATED_FROM_ID = T_RA.TRANSACTION_ID
INNER JOIN ReturnAuthorizations RA ON T_RA.TRANSACTION_ID = RA.ReturnCaseId
LEFT JOIN EMPLOYEE E ON IR.CREATED_BY_ID = E.EMPLOYEE_ID
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE IR.TYPE = 'ItemReceipt'

UNION ALL

SELECT
    RA.ReturnCaseId,
    'Item Inspected' AS "ActivityName",
    SN.NOTE_DATE AS "EventTimestamp",
    'NetSuite' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate",
    T.STATUS AS "ReturnAuthorizationStatus",
    E.full_name AS "ProcessingAgent",
    D.full_name AS "Department",
    T.CUSTBODY_RETURN_TYPE AS "ReturnType",
    NULL AS "ActualRefundAmount",
    NULL AS "CycleTime"
FROM SYSTEM_NOTES SN
INNER JOIN TRANSACTION T ON SN.TRANSACTION_ID = T.TRANSACTION_ID
INNER JOIN ReturnAuthorizations RA ON T.TRANSACTION_ID = RA.ReturnCaseId
LEFT JOIN EMPLOYEE E ON SN.AUTHOR_ID = E.EMPLOYEE_ID
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE SN.FIELD = 'TRANSACTION.CUSTBODY_INSPECTION_STATUS' AND SN.NEW_VALUE = 'Completed'

UNION ALL

SELECT
    RA.ReturnCaseId,
    'Credit Memo Created' AS "ActivityName",
    CM.CREATED_DATE AS "EventTimestamp",
    'NetSuite' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate",
    T_RA.STATUS AS "ReturnAuthorizationStatus",
    E.full_name AS "ProcessingAgent",
    D.full_name AS "Department",
    T_RA.CUSTBODY_RETURN_TYPE AS "ReturnType",
    NULL AS "ActualRefundAmount",
    NULL AS "CycleTime"
FROM TRANSACTION CM
INNER JOIN TRANSACTION T_RA ON CM.CREATED_FROM_ID = T_RA.TRANSACTION_ID
INNER JOIN ReturnAuthorizations RA ON T_RA.TRANSACTION_ID = RA.ReturnCaseId
LEFT JOIN EMPLOYEE E ON CM.CREATED_BY_ID = E.EMPLOYEE_ID
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE CM.TYPE = 'CreditMemo'

UNION ALL

SELECT
    RA.ReturnCaseId,
    'Credit Memo Approved' AS "ActivityName",
    SN.NOTE_DATE AS "EventTimestamp",
    'NetSuite' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate",
    SN.NEW_VALUE AS "ReturnAuthorizationStatus",
    E.full_name AS "ProcessingAgent",
    D.full_name AS "Department",
    T_RA.CUSTBODY_RETURN_TYPE AS "ReturnType",
    NULL AS "ActualRefundAmount",
    NULL AS "CycleTime"
FROM SYSTEM_NOTES SN
INNER JOIN TRANSACTION CM ON SN.TRANSACTION_ID = CM.TRANSACTION_ID
INNER JOIN TRANSACTION T_RA ON CM.CREATED_FROM_ID = T_RA.TRANSACTION_ID
INNER JOIN ReturnAuthorizations RA ON T_RA.TRANSACTION_ID = RA.ReturnCaseId
LEFT JOIN EMPLOYEE E ON SN.AUTHOR_ID = E.EMPLOYEE_ID
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE CM.TYPE = 'CreditMemo' AND SN.FIELD = 'TRANSACTION.APPROVAL_STATUS' AND SN.NEW_VALUE = 'Approved'

UNION ALL

SELECT
    RA.ReturnCaseId,
    'Refund Processed' AS "ActivityName",
    REF.CREATED_DATE AS "EventTimestamp",
    'NetSuite' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate",
    T_RA.STATUS AS "ReturnAuthorizationStatus",
    E.full_name AS "ProcessingAgent",
    D.full_name AS "Department",
    T_RA.CUSTBODY_RETURN_TYPE AS "ReturnType",
    ABS(REF.TOTAL) AS "ActualRefundAmount",
    NULL AS "CycleTime"
FROM TRANSACTION REF
INNER JOIN TRANSACTION CM ON REF.APPLIED_TO_TRANSACTION_ID = CM.TRANSACTION_ID
INNER JOIN TRANSACTION T_RA ON CM.CREATED_FROM_ID = T_RA.TRANSACTION_ID
INNER JOIN ReturnAuthorizations RA ON T_RA.TRANSACTION_ID = RA.ReturnCaseId
LEFT JOIN EMPLOYEE E ON REF.CREATED_BY_ID = E.EMPLOYEE_ID
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE REF.TYPE = 'CustomerRefund'

UNION ALL

SELECT
    RA.ReturnCaseId,
    'Credit Memo Applied' AS "ActivityName",
    SN.NOTE_DATE AS "EventTimestamp",
    'NetSuite' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate",
    SN.NEW_VALUE AS "ReturnAuthorizationStatus",
    E.full_name AS "ProcessingAgent",
    D.full_name AS "Department",
    T_RA.CUSTBODY_RETURN_TYPE AS "ReturnType",
    NULL AS "ActualRefundAmount",
    NULL AS "CycleTime"
FROM SYSTEM_NOTES SN
INNER JOIN TRANSACTION CM ON SN.TRANSACTION_ID = CM.TRANSACTION_ID
INNER JOIN TRANSACTION T_RA ON CM.CREATED_FROM_ID = T_RA.TRANSACTION_ID
INNER JOIN ReturnAuthorizations RA ON T_RA.TRANSACTION_ID = RA.ReturnCaseId
LEFT JOIN EMPLOYEE E ON SN.AUTHOR_ID = E.EMPLOYEE_ID
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE CM.TYPE = 'CreditMemo' AND SN.FIELD = 'TRANSACTION.STATUS' AND SN.NEW_VALUE = 'Fully Applied'

UNION ALL

SELECT
    RA.ReturnCaseId,
    'Exchange Order Created' AS "ActivityName",
    SO.CREATED_DATE AS "EventTimestamp",
    'NetSuite' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate",
    T_RA.STATUS AS "ReturnAuthorizationStatus",
    E.full_name AS "ProcessingAgent",
    D.full_name AS "Department",
    T_RA.CUSTBODY_RETURN_TYPE AS "ReturnType",
    NULL AS "ActualRefundAmount",
    NULL AS "CycleTime"
FROM TRANSACTION SO
INNER JOIN TRANSACTION T_RA ON SO.CREATED_FROM_ID = T_RA.TRANSACTION_ID
INNER JOIN ReturnAuthorizations RA ON T_RA.TRANSACTION_ID = RA.ReturnCaseId
LEFT JOIN EMPLOYEE E ON SO.CREATED_BY_ID = E.EMPLOYEE_ID
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE SO.TYPE = 'SalesOrd'

UNION ALL

SELECT
    RA.ReturnCaseId,
    'Customer Notified' AS "ActivityName",
    MSG.MESSAGE_DATE AS "EventTimestamp",
    'NetSuite' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate",
    T.STATUS AS "ReturnAuthorizationStatus",
    E.full_name AS "ProcessingAgent",
    D.full_name AS "Department",
    T.CUSTBODY_RETURN_TYPE AS "ReturnType",
    NULL AS "ActualRefundAmount",
    NULL AS "CycleTime"
FROM MESSAGES MSG
INNER JOIN TRANSACTION T ON MSG.TRANSACTION_ID = T.TRANSACTION_ID
INNER JOIN ReturnAuthorizations RA ON T.TRANSACTION_ID = RA.ReturnCaseId
LEFT JOIN EMPLOYEE E ON MSG.AUTHOR_ID = E.EMPLOYEE_ID
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE MSG.INCOMING = 'F'

UNION ALL

SELECT
    RA.ReturnCaseId,
    'Return Authorization Closed' AS "ActivityName",
    SN.NOTE_DATE AS "EventTimestamp",
    'NetSuite' AS "SourceSystem",
    CURRENT_TIMESTAMP AS "LastDataUpdate",
    SN.NEW_VALUE AS "ReturnAuthorizationStatus",
    E.full_name AS "ProcessingAgent",
    D.full_name AS "Department",
    T.CUSTBODY_RETURN_TYPE AS "ReturnType",
    NULL AS "ActualRefundAmount",
    NULL AS "CycleTime"
FROM SYSTEM_NOTES SN
INNER JOIN TRANSACTION T ON SN.TRANSACTION_ID = T.TRANSACTION_ID
INNER JOIN ReturnAuthorizations RA ON T.TRANSACTION_ID = RA.ReturnCaseId
LEFT JOIN EMPLOYEE E ON SN.AUTHOR_ID = E.EMPLOYEE_ID
LEFT JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE T.TYPE = 'ReturnAuthorization' AND SN.FIELD = 'TRANSACTION.STATUS' AND SN.NEW_VALUE LIKE '%Closed%';