주문부터 현금화까지: 청구 및 송장 발행 데이터 템플릿

/*
This section describes the configuration for multiple NetSuite Saved Searches needed to generate the complete event log. Each block represents a separate Saved Search that must be created in the UI, exported, and then combined externally.
*/

-- Search 1: Invoice Created
Activity Name: 'Invoice Created'
Saved Search Type: Transaction
Criteria:
  - Type is Invoice
  - Main Line is true
Results Columns (Mapped to Event Log Attributes):
  - InvoiceNumber: Document Number
  - ActivityName: Formula (Text) = 'Invoice Created'
  - EventTimestamp: Date Created
  - User: Created By
  - InvoiceDate: Date
  - DueDate: Due Date
  - InvoiceTotalAmount: Amount (Gross)
  - CustomerName: Name

-- Search 2: Invoice Submitted for Approval
Activity Name: 'Invoice Submitted for Approval'
Saved Search Type: Transaction
Criteria:
  - Type is Invoice
  - System Notes : Field is Approval Status
  - System Notes : New Value is [Your 'Pending Approval' status]
Results Columns:
  - InvoiceNumber: Document Number
  - ActivityName: Formula (Text) = 'Invoice Submitted for Approval'
  - EventTimestamp: System Notes : Date
  - User: System Notes : Set by
  - CustomerName: Name
  - InvoiceTotalAmount: Amount (Gross)

-- Search 3: Invoice Approved
Activity Name: 'Invoice Approved'
Saved Search Type: Transaction
Criteria:
  - Type is Invoice
  - System Notes : Field is Approval Status
  - System Notes : New Value is [Your 'Approved' status]
Results Columns:
  - InvoiceNumber: Document Number
  - ActivityName: Formula (Text) = 'Invoice Approved'
  - EventTimestamp: System Notes : Date
  - User: System Notes : Set by
  - CustomerName: Name
  - InvoiceTotalAmount: Amount (Gross)

-- Search 4: Invoice Sent to Customer
Activity Name: 'Invoice Sent to Customer'
Saved Search Type: Transaction
Criteria:
  - Type is Invoice
  - Messages : Date Sent is not empty
Results Columns:
  - InvoiceNumber: Document Number
  - ActivityName: Formula (Text) = 'Invoice Sent to Customer'
  - EventTimestamp: Messages : Date Sent (use Maximum summary type if multiple)
  - User: Messages : Author (use Maximum summary type)
  - CustomerName: Name
  - InvoiceTotalAmount: Amount (Gross)

-- Search 5: Payment Reminder Issued
Activity Name: 'Payment Reminder Issued'
Saved Search Type: Transaction
Criteria:
  - Type is Invoice
  - User Notes : Note contains [Your dunning/reminder keyword]
Results Columns:
  - InvoiceNumber: Document Number
  - ActivityName: Formula (Text) = 'Payment Reminder Issued'
  - EventTimestamp: User Notes : Date
  - User: User Notes : Author
  - CustomerName: Name
  - InvoiceTotalAmount: Amount (Gross)

-- Search 6: Credit Memo Created
Activity Name: 'Credit Memo Created'
Saved Search Type: Transaction
Criteria:
  - Type is Credit Memo
  - Main Line is true
Results Columns:
  - InvoiceNumber: Created From : Document Number
  - ActivityName: Formula (Text) = 'Credit Memo Created'
  - EventTimestamp: Date Created
  - User: Created By
  - CustomerName: Name
  - InvoiceTotalAmount: Amount (Gross)

-- Search 7: Customer Payment Received
Activity Name: 'Customer Payment Received'
Saved Search Type: Transaction
Criteria:
  - Type is Customer Payment
  - Main Line is true
Results Columns:
  - InvoiceNumber: Applied to Transaction : Document Number
  - ActivityName: Formula (Text) = 'Customer Payment Received'
  - EventTimestamp: Date Created
  - User: Created By
  - CustomerName: Name

-- Search 8: Payment Applied To Invoice
Activity Name: 'Payment Applied To Invoice'
Saved Search Type: Transaction
Criteria:
  - Type is Customer Payment
  - Main Line is false
Results Columns:
  - InvoiceNumber: Applied to Transaction : Document Number
  - ActivityName: Formula (Text) = 'Payment Applied To Invoice'
  - EventTimestamp: Date Created
  - User: Created By
  - CustomerName: Name

-- Search 9: Invoice Paid In Full
Activity Name: 'Invoice Paid In Full'
Saved Search Type: Transaction
Criteria:
  - Type is Invoice
  - System Notes : Field is Status
  - System Notes : New Value is Paid In Full
Results Columns:
  - InvoiceNumber: Document Number
  - ActivityName: Formula (Text) = 'Invoice Paid In Full'
  - EventTimestamp: System Notes : Date
  - User: System Notes : Set by
  - CustomerName: Name
  - InvoiceTotalAmount: Amount (Gross)

-- Search 10: Invoice Voided
Activity Name: 'Invoice Voided'
Saved Search Type: Transaction
Criteria:
  - Type is Invoice
  - Status is Invoice : Voided
  - System Notes : Field is Status
  - System Notes : New Value is Voided
Results Columns:
  - InvoiceNumber: Document Number
  - ActivityName: Formula (Text) = 'Invoice Voided'
  - EventTimestamp: System Notes : Date
  - User: System Notes : Set by
  - CustomerName: Name

-- Search 11: Invoice Closed
Activity Name: 'Invoice Closed'
Saved Search Type: Transaction
Criteria:
  - Type is Invoice
  - System Notes : Field is Status
  - System Notes : New Value contains Closed
Results Columns:
  - InvoiceNumber: Document Number
  - ActivityName: Formula (Text) = 'Invoice Closed'
  - EventTimestamp: System Notes : Date
  - User: System Notes : Set by
  - CustomerName: Name
  - InvoiceTotalAmount: Amount (Gross)

-- Event to be Derived Post-Extraction: Payment Due Date Reached
/*
This event is not extracted directly. After combining all other CSVs, create a new row for each unique invoice.
Set ActivityName to 'Payment Due Date Reached'.
Set EventTimestamp to the DueDate value from the 'Invoice Created' event for that invoice.
Copy other attributes like InvoiceNumber, CustomerName, etc.
*/

WITH Invoices AS (
    SELECT
        T.TRANSACTION_ID,
        T.TRANID AS InvoiceNumber,
        T.TRANDATE AS InvoiceDate,
        T.DUEDATE AS DueDate,
        T.FOREIGN_TOTAL AS InvoiceTotalAmount,
        C.COMPANYNAME AS CustomerName,
        BUILTIN.DF(C.LOCATION) AS Region,
        T.CREATED_DATE AS InvoiceCreationDate,
        CREATOR.FIRST_NAME || ' ' || CREATOR.LAST_NAME AS CreatorUser
    FROM
        TRANSACTIONS T
    JOIN
        CUSTOMERS C ON T.ENTITY_ID = C.CUSTOMER_ID
    LEFT JOIN
        EMPLOYEES CREATOR ON T.CREATED_BY_ID = CREATOR.EMPLOYEE_ID
    WHERE
        T.TRANSACTION_TYPE = 'Invoice'
        AND T.TRANDATE BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD')
        -- AND T.SUBSIDIARY_ID = [Your Subsidiary ID] -- Add your subsidiary filter if needed
)
-- 1. Invoice Created
SELECT
    I.InvoiceNumber AS "InvoiceNumber",
    'Invoice Created' AS "ActivityName",
    I.InvoiceCreationDate AS "EventTimestamp",
    I.InvoiceCreationDate AS "EventEndTime",
    I.CreatorUser AS "User",
    'NetSuite' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    I.InvoiceDate AS "InvoiceDate",
    I.DueDate AS "DueDate",
    I.InvoiceTotalAmount AS "InvoiceTotalAmount",
    I.CustomerName AS "CustomerName",
    I.Region AS "Region",
    CASE WHEN I.CreatorUser LIKE '%System%' THEN 1 ELSE 0 END AS "IsAutomated"
FROM
    Invoices I

UNION ALL

-- 2. Invoice Submitted for Approval & 3. Invoice Approved
SELECT
    I.InvoiceNumber AS "InvoiceNumber",
    CASE WHEN SN.NEW_VALUE = 'Pending Approval' THEN 'Invoice Submitted for Approval' ELSE 'Invoice Approved' END AS "ActivityName",
    SN.DATE_CREATED AS "EventTimestamp",
    SN.DATE_CREATED AS "EventEndTime",
    SN_AUTHOR.FIRST_NAME || ' ' || SN_AUTHOR.LAST_NAME AS "User",
    'NetSuite' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    I.InvoiceDate AS "InvoiceDate",
    I.DueDate AS "DueDate",
    I.InvoiceTotalAmount AS "InvoiceTotalAmount",
    I.CustomerName AS "CustomerName",
    I.Region AS "Region",
    CASE WHEN SN.AUTHOR_ID < 0 THEN 1 ELSE 0 END AS "IsAutomated"
FROM
    Invoices I
JOIN
    SYSTEM_NOTES SN ON I.TRANSACTION_ID = SN.TRANSACTION_ID
LEFT JOIN
    EMPLOYEES SN_AUTHOR ON SN.AUTHOR_ID = SN_AUTHOR.EMPLOYEE_ID
WHERE
    SN.FIELD = 'TRANSACTION.APPROVALSTATUS' AND SN.NEW_VALUE IN ('Pending Approval', 'Approved')

UNION ALL

-- 4. Invoice Sent to Customer
SELECT
    I.InvoiceNumber AS "InvoiceNumber",
    'Invoice Sent to Customer' AS "ActivityName",
    M.MESSAGE_DATE AS "EventTimestamp",
    M.MESSAGE_DATE AS "EventEndTime",
    MSG_AUTHOR.FIRST_NAME || ' ' || MSG_AUTHOR.LAST_NAME AS "User",
    'NetSuite' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    I.InvoiceDate AS "InvoiceDate",
    I.DueDate AS "DueDate",
    I.InvoiceTotalAmount AS "InvoiceTotalAmount",
    I.CustomerName AS "CustomerName",
    I.Region AS "Region",
    0 AS "IsAutomated"
FROM
    Invoices I
JOIN
    MESSAGES M ON I.TRANSACTION_ID = M.TRANSACTION_ID
LEFT JOIN
    EMPLOYEES MSG_AUTHOR ON M.AUTHOR_ID = MSG_AUTHOR.EMPLOYEE_ID
WHERE
    M.INCOMING = 'F' -- Indicates an outgoing message

UNION ALL

-- 5. Payment Due Date Reached
SELECT
    I.InvoiceNumber AS "InvoiceNumber",
    'Payment Due Date Reached' AS "ActivityName",
    I.DueDate AS "EventTimestamp",
    I.DueDate AS "EventEndTime",
    'System' AS "User",
    'NetSuite' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    I.InvoiceDate AS "InvoiceDate",
    I.DueDate AS "DueDate",
    I.InvoiceTotalAmount AS "InvoiceTotalAmount",
    I.CustomerName AS "CustomerName",
    I.Region AS "Region",
    1 AS "IsAutomated"
FROM
    Invoices I
WHERE
    I.DueDate IS NOT NULL AND I.DueDate < SYSDATE

UNION ALL

-- 6. Payment Reminder Issued (based on Messages table, requires a specific subject line convention)
SELECT
    I.InvoiceNumber AS "InvoiceNumber",
    'Payment Reminder Issued' AS "ActivityName",
    M.MESSAGE_DATE AS "EventTimestamp",
    M.MESSAGE_DATE AS "EventEndTime",
    MSG_AUTHOR.FIRST_NAME || ' ' || MSG_AUTHOR.LAST_NAME AS "User",
    'NetSuite' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    I.InvoiceDate AS "InvoiceDate",
    I.DueDate AS "DueDate",
    I.InvoiceTotalAmount AS "InvoiceTotalAmount",
    I.CustomerName AS "CustomerName",
    I.Region AS "Region",
    CASE WHEN M.AUTHOR_ID < 0 THEN 1 ELSE 0 END AS "IsAutomated"
FROM
    Invoices I
JOIN
    MESSAGES M ON I.TRANSACTION_ID = M.TRANSACTION_ID
LEFT JOIN
    EMPLOYEES MSG_AUTHOR ON M.AUTHOR_ID = MSG_AUTHOR.EMPLOYEE_ID
WHERE
    M.INCOMING = 'F' AND UPPER(M.SUBJECT) LIKE '%PAYMENT REMINDER%'

UNION ALL

-- 7. Credit Memo Created & Applied
SELECT
    I.InvoiceNumber AS "InvoiceNumber",
    'Credit Memo Created' AS "ActivityName",
    CM.CREATED_DATE AS "EventTimestamp",
    CM.CREATED_DATE AS "EventEndTime",
    CM_CREATOR.FIRST_NAME || ' ' || CM_CREATOR.LAST_NAME AS "User",
    'NetSuite' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    I.InvoiceDate AS "InvoiceDate",
    I.DueDate AS "DueDate",
    I.InvoiceTotalAmount AS "InvoiceTotalAmount",
    I.CustomerName AS "CustomerName",
    I.Region AS "Region",
    0 AS "IsAutomated"
FROM
    Invoices I
JOIN
    TRANSACTION_LINES T_LINES ON I.TRANSACTION_ID = T_LINES.APPLIED_TO_TRANSACTION_ID
JOIN
    TRANSACTIONS CM ON T_LINES.TRANSACTION_ID = CM.TRANSACTION_ID
LEFT JOIN
    EMPLOYEES CM_CREATOR ON CM.CREATED_BY_ID = CM_CREATOR.EMPLOYEE_ID
WHERE
    CM.TRANSACTION_TYPE = 'Credit Memo'

UNION ALL

-- 8. Customer Payment Received & 9. Payment Applied to Invoice
SELECT
    I.InvoiceNumber AS "InvoiceNumber",
    'Customer Payment Received' AS "ActivityName", -- Can also create a separate 'Payment Applied' event if needed
    PAYMENT.CREATED_DATE AS "EventTimestamp",
    PAYMENT.CREATED_DATE AS "EventEndTime",
    PMT_CREATOR.FIRST_NAME || ' ' || PMT_CREATOR.LAST_NAME AS "User",
    'NetSuite' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    I.InvoiceDate AS "InvoiceDate",
    I.DueDate AS "DueDate",
    I.InvoiceTotalAmount AS "InvoiceTotalAmount",
    I.CustomerName AS "CustomerName",
    I.Region AS "Region",
    0 AS "IsAutomated"
FROM
    Invoices I
JOIN
    TRANSACTION_LINES T_LINES ON I.TRANSACTION_ID = T_LINES.APPLIED_TO_TRANSACTION_ID
JOIN
    TRANSACTIONS PAYMENT ON T_LINES.TRANSACTION_ID = PAYMENT.TRANSACTION_ID
LEFT JOIN
    EMPLOYEES PMT_CREATOR ON PAYMENT.CREATED_BY_ID = PMT_CREATOR.EMPLOYEE_ID
WHERE
    PAYMENT.TRANSACTION_TYPE = 'Customer Payment'

UNION ALL

-- 10. Invoice Paid In Full, 11. Invoice Voided, 12. Invoice Closed
SELECT
    I.InvoiceNumber AS "InvoiceNumber",
    CASE
        WHEN SN.NEW_VALUE_ID = 'Invoice:B' THEN 'Invoice Paid In Full' -- 'B' is status 'Paid In Full'
        WHEN SN.FIELD = 'TRANSACTION.VOIDED' AND SN.NEW_VALUE = 'T' THEN 'Invoice Voided'
        WHEN SN.FIELD = 'TRANSACTION.CLOSED' AND SN.NEW_VALUE = 'T' THEN 'Invoice Closed'
    END AS "ActivityName",
    SN.DATE_CREATED AS "EventTimestamp",
    SN.DATE_CREATED AS "EventEndTime",
    SN_AUTHOR.FIRST_NAME || ' ' || SN_AUTHOR.LAST_NAME AS "User",
    'NetSuite' AS "SourceSystem",
    SYSDATE AS "LastDataUpdate",
    I.InvoiceDate AS "InvoiceDate",
    I.DueDate AS "DueDate",
    I.InvoiceTotalAmount AS "InvoiceTotalAmount",
    I.CustomerName AS "CustomerName",
    I.Region AS "Region",
    CASE WHEN SN.AUTHOR_ID < 0 THEN 1 ELSE 0 END AS "IsAutomated"
FROM
    Invoices I
JOIN
    SYSTEM_NOTES SN ON I.TRANSACTION_ID = SN.TRANSACTION_ID
LEFT JOIN
    EMPLOYEES SN_AUTHOR ON SN.AUTHOR_ID = SN_AUTHOR.EMPLOYEE_ID
WHERE
    (SN.FIELD = 'TRANSACTION.STATUS' AND SN.NEW_VALUE_ID = 'Invoice:B')
    OR (SN.FIELD = 'TRANSACTION.VOIDED' AND SN.NEW_VALUE = 'T')
    OR (SN.FIELD = 'TRANSACTION.CLOSED' AND SN.NEW_VALUE = 'T');