Datatemplate voor Order-to-Cash: verwerking van verkooporders

-- This configuration describes the settings for four separate NetSuite Saved Searches.
-- The CSV results must be combined externally into a single file.

-- =========================================================================
-- Saved Search 1: ProcessMind_SalesOrder_Events
-- =========================================================================

-- Type: Transaction

-- Criteria:
--   Type = is "Sales Order"
--   Main Line = is true
--   System Notes : Field = is any of "Status", "[Your Custom Field for Credit Hold]"
--   Date Created = is within [Specify Date Range]

-- Results (Columns):
--   1. Formula (Text): {tranid} -> Header: SalesOrder
--   2. Formula (Text): CASE WHEN {systemnotes.type} = 'Create' THEN 'Sales Order Created' WHEN {systemnotes.field} = 'Status' AND {systemnotes.newvalue} like 'Pending Fulfillment%' THEN 'Sales Order Approved' WHEN {systemnotes.field} = 'Status' AND {systemnotes.newvalue} like '%Credit Hold%' THEN 'Credit Hold Applied' WHEN {systemnotes.field} = 'Status' AND {systemnotes.oldvalue} like '%Credit Hold%' THEN 'Credit Hold Released' WHEN {systemnotes.field} = 'Status' AND {systemnotes.newvalue} like 'Closed' THEN 'Sales Order Closed' WHEN {systemnotes.type} = 'Change' THEN 'Sales Order Changed' ELSE NULL END -> Header: ActivityName
--   3. Formula (DateTime): {systemnotes.date} -> Header: EventTime
--   4. System Notes : Set By -> Header: User
--   5. Name -> Header: CustomerName
--   6. Amount -> Header: TotalOrderAmount
--   7. Status -> Header: SalesOrderStatus
--   8. [Your Formula for Product Category] -> Header: ProductCategory
--   9. Ship Date -> Header: RequestedDeliveryDate
--   10. Terms -> Header: PaymentTerms
--   11. Created From : Document Number -> Header: InvoiceNumber

-- =========================================================================
-- Saved Search 2: ProcessMind_Fulfillment_Events
-- =========================================================================

-- Type: Transaction

-- Criteria:
--   Type = is "Item Fulfillment"
--   Main Line = is true
--   Created From : Date Created = is within [Specify Date Range]
--   System Notes : Field = is "Status"

-- Results (Columns):
--   1. Formula (Text): {createdfrom.tranid} -> Header: SalesOrder
--   2. Formula (Text): CASE WHEN {systemnotes.type} = 'Create' THEN 'Order Fulfillment Created' WHEN {systemnotes.newvalue} like 'Picked' THEN 'Goods Picked' WHEN {systemnotes.newvalue} like 'Packed' THEN 'Goods Packed' WHEN {systemnotes.newvalue} like 'Shipped' THEN 'Goods Shipped' ELSE NULL END -> Header: ActivityName
--   3. Formula (DateTime): {systemnotes.date} -> Header: EventTime
--   4. System Notes : Set By -> Header: User
--   5. Created From : Name -> Header: CustomerName
--   6. Created From : Amount -> Header: TotalOrderAmount
--   7. Created From : Status -> Header: SalesOrderStatus
--   8. [Your Formula for Product Category] -> Header: ProductCategory
--   9. Created From : Ship Date -> Header: RequestedDeliveryDate
--   10. Created From : Terms -> Header: PaymentTerms
--   11. NULL -> Header: InvoiceNumber

-- =========================================================================
-- Saved Search 3: ProcessMind_Billing_Events
-- =========================================================================

-- Type: Transaction

-- Criteria:
--   Type = is any of "Invoice", "Credit Memo"
--   Main Line = is true
--   Date Created = is within [Specify Date Range]

-- Results (Columns):
--   1. Formula (Text): {createdfrom.tranid} -> Header: SalesOrder
--   2. Formula (Text): CASE WHEN {type} = 'Invoice' THEN 'Invoice Created' WHEN {type} = 'Credit Memo' THEN 'Credit Memo Created' ELSE NULL END -> Header: ActivityName
--   3. Formula (DateTime): {datecreated} -> Header: EventTime
--   4. Created By -> Header: User
--   5. Name -> Header: CustomerName
--   6. Created From : Amount -> Header: TotalOrderAmount
--   7. Created From : Status -> Header: SalesOrderStatus
--   8. [Your Formula for Product Category] -> Header: ProductCategory
--   9. Created From : Ship Date -> Header: RequestedDeliveryDate
--   10. Terms -> Header: PaymentTerms
--   11. Document Number -> Header: InvoiceNumber

-- =========================================================================
-- Saved Search 4: ProcessMind_Payment_Events
-- =========================================================================

-- Type: Transaction

-- Criteria:
--   Type = is "Customer Payment"
--   Main Line = is true
--   Date Created = is within [Specify Date Range]

-- Results (Columns):
--   1. Formula (Text): {appliedtotransaction.createdfrom} -> Header: SalesOrder
--   2. Formula (Text): 'Payment Received' -> Header: ActivityName
--   3. Formula (DateTime): {datecreated} -> Header: EventTime
--   4. Created By -> Header: User
--   5. Customer : Company Name -> Header: CustomerName
--   6. Applied To Transaction : Created From : Amount -> Header: TotalOrderAmount
--   7. Applied To Transaction : Created From : Status -> Header: SalesOrderStatus
--   8. [Your Formula for Product Category] -> Header: ProductCategory
--   9. Applied To Transaction : Created From : Ship Date -> Header: RequestedDeliveryDate
--   10. Applied To Transaction : Terms -> Header: PaymentTerms
--   11. Applied To Transaction : Document Number -> Header: InvoiceNumber

WITH SalesOrders AS (
    SELECT
        T.Transaction_ID,
        T.TranID AS SalesOrder_Number,
        T.Foreign_Total AS Total_Amount,
        T.Status AS Current_Status,
        T.Ship_Date AS Requested_Delivery_Date,
        CUST.Company_Name AS Customer_Name,
        TERMS.Term_Name AS Payment_Terms,
        (SELECT LIST_AGG(IT.Display_Name, ', ') FROM Transaction_Lines TL JOIN Item IT ON TL.Item_ID = IT.Item_ID WHERE TL.Transaction_ID = T.Transaction_ID) AS Product_Category_List -- Simplified product category
    FROM
        "Transaction" T
    JOIN
        Entity CUST ON T.Entity_ID = CUST.Entity_ID
    LEFT JOIN
        Terms ON T.Terms_ID = TERMS.Terms_ID
    WHERE
        T.Type = 'SalesOrd'
        AND T.Date_Created >= TO_TIMESTAMP_NTZ('[START_DATE_YYYY-MM-DD]', 'YYYY-MM-DD')
)
-- 1. Sales Order Created
SELECT
    SO.SalesOrder_Number AS "SalesOrder",
    'Sales Order Created' AS "ActivityName",
    T.Date_Created AS "EventTime",
    E.First_Name || ' ' || E.Last_Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    T.Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM
    "Transaction" T
JOIN
    SalesOrders SO ON T.Transaction_ID = SO.Transaction_ID
LEFT JOIN
    Employee E ON T.Created_By_ID = E.Employee_ID

UNION ALL

-- 2, 3, 4, 14. Status Changes (Credit Hold, Released, Approved, Closed)
SELECT
    SO.SalesOrder_Number AS "SalesOrder",
    CASE
        WHEN SN.New_Value LIKE '%Pending Approv%' OR SN.New_Value LIKE '%Credit Hold%' THEN 'Credit Hold Applied'
        WHEN SN.Old_Value LIKE '%Pending Approv%' AND SN.New_Value LIKE '%Pending Fulfill%' THEN 'Credit Hold Released'
        WHEN SN.New_Value LIKE '%Pending Fulfill%' THEN 'Sales Order Approved'
        WHEN SN.New_Value LIKE '%Closed%' OR SN.New_Value LIKE '%Billed%' THEN 'Sales Order Closed'
    END AS "ActivityName",
    SN.Date AS "EventTime",
    SN.Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SN.New_Value AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM
    System_Notes SN
JOIN
    SalesOrders SO ON SN.Transaction_ID = SO.Transaction_ID
WHERE
    SN.Field = 'TRANSACTION.STATUS' AND "ActivityName" IS NOT NULL

UNION ALL

-- 5. Sales Order Changed
SELECT
    SO.SalesOrder_Number AS "SalesOrder",
    'Sales Order Changed' AS "ActivityName",
    SN.Date AS "EventTime",
    SN.Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM
    System_Notes SN
JOIN
    SalesOrders SO ON SN.Transaction_ID = SO.Transaction_ID
WHERE 
    SN.Record_Type_ID = -30 -- Sales Order Transaction Type ID
    AND SN.Field NOT IN ('TRANSACTION.STATUS', 'TRANSACTION.MEMO') -- Exclude simple status or memo changes
    AND SN.Date > (SELECT T.Date_Created FROM "Transaction" T WHERE T.Transaction_ID = SO.Transaction_ID)

UNION ALL

-- 6. Inventory Committed
SELECT DISTINCT
    SO.SalesOrder_Number AS "SalesOrder",
    'Inventory Committed' AS "ActivityName",
    SN.Date AS "EventTime",
    SN.Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM
    System_Notes SN
JOIN
    Transaction_Lines TL ON SN.Transaction_Line_ID = TL.Transaction_Line_ID AND SN.Transaction_ID = TL.Transaction_ID
JOIN
    SalesOrders SO ON TL.Transaction_ID = SO.Transaction_ID
WHERE
    SN.Field = 'TRANSACTION_LINES.QUANTITY_COMMITTED'
    AND SN.New_Value > 0 AND (SN.Old_Value = 0 OR SN.Old_Value IS NULL)

UNION ALL

-- 7. Order Fulfillment Created
SELECT
    SO.SalesOrder_Number AS "SalesOrder",
    'Order Fulfillment Created' AS "ActivityName",
    IFF.Date_Created AS "EventTime",
    E.First_Name || ' ' || E.Last_Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM
    "Transaction" IFF
JOIN
    Transaction_Links TL ON IFF.Transaction_ID = TL.Transaction_ID
JOIN
    SalesOrders SO ON TL.Previous_Transaction_ID = SO.Transaction_ID
LEFT JOIN
    Employee E ON IFF.Created_By_ID = E.Employee_ID
WHERE
    IFF.Type = 'ItemShip'

UNION ALL

-- 8, 9, 10. Goods Picked, Packed, Shipped
SELECT 
    SO.SalesOrder_Number AS "SalesOrder",
    CASE 
        WHEN SN.New_Value = 'Picked' THEN 'Goods Picked'
        WHEN SN.New_Value = 'Packed' THEN 'Goods Packed'
        WHEN SN.New_Value = 'Shipped' THEN 'Goods Shipped'
    END AS "ActivityName",
    SN.Date AS "EventTime",
    SN.Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM 
    System_Notes SN
JOIN 
    "Transaction" IFF ON SN.Transaction_ID = IFF.Transaction_ID
JOIN 
    Transaction_Links TL ON IFF.Transaction_ID = TL.Transaction_ID
JOIN 
    SalesOrders SO ON TL.Previous_Transaction_ID = SO.Transaction_ID
WHERE 
    IFF.Type = 'ItemShip'
    AND SN.Field = 'TRANSACTION.STATUS'
    AND "ActivityName" IS NOT NULL

UNION ALL

-- 11. Invoice Created
SELECT
    SO.SalesOrder_Number AS "SalesOrder",
    'Invoice Created' AS "ActivityName",
    INV.Date_Created AS "EventTime",
    E.First_Name || ' ' || E.Last_Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    INV.TranID AS "InvoiceNumber"
FROM
    "Transaction" INV
JOIN
    Transaction_Links TL ON INV.Transaction_ID = TL.Transaction_ID
JOIN
    SalesOrders SO ON TL.Previous_Transaction_ID = SO.Transaction_ID
LEFT JOIN
    Employee E ON INV.Created_By_ID = E.Employee_ID
WHERE
    INV.Type = 'CustInvc'

UNION ALL

-- 12. Credit Memo Created
SELECT
    SO.SalesOrder_Number AS "SalesOrder",
    'Credit Memo Created' AS "ActivityName",
    CM.Date_Created AS "EventTime",
    E.First_Name || ' ' || E.Last_Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM
    "Transaction" CM
JOIN
    Transaction_Links TL ON CM.Transaction_ID = TL.Transaction_ID
JOIN
    SalesOrders SO ON TL.Previous_Transaction_ID = SO.Transaction_ID
LEFT JOIN
    Employee E ON CM.Created_By_ID = E.Employee_ID
WHERE
    CM.Type = 'CustCred'

UNION ALL

-- 13. Payment Received
SELECT DISTINCT
    SO.SalesOrder_Number AS "SalesOrder",
    'Payment Received' AS "ActivityName",
    PAY.Date_Created AS "EventTime",
    E.First_Name || ' ' || E.Last_Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    INV.TranID AS "InvoiceNumber"
FROM 
    "Transaction" PAY
JOIN 
    Transaction_Links PAY_LINK ON PAY.Transaction_ID = PAY_LINK.Transaction_ID -- Payment to Invoice link
JOIN 
    "Transaction" INV ON PAY_LINK.Previous_Transaction_ID = INV.Transaction_ID AND INV.Type = 'CustInvc'
JOIN 
    Transaction_Links INV_LINK ON INV.Transaction_ID = INV_LINK.Transaction_ID -- Invoice to SO link
JOIN 
    SalesOrders SO ON INV_LINK.Previous_Transaction_ID = SO.Transaction_ID
LEFT JOIN
    Employee E ON PAY.Created_By_ID = E.Employee_ID
WHERE 
    PAY.Type = 'CustPymt'
ORDER BY "SalesOrder", "EventTime";

import requests
from requests_oauthlib import OAuth1
import json
import pandas as pd
import time

class=class="hl-string">"hl-comment"># --- Configuration Section --- #
class=class="hl-string">"hl-comment"># Replace with your NetSuite account-specific details
ACCOUNT_ID = class="hl-string">'[Your NetSuite Account ID]'
CONSUMER_KEY = class="hl-string">'[Your Consumer Key]'
CONSUMER_SECRET = class="hl-string">'[Your Consumer Secret]'
TOKEN_ID = class="hl-string">'[Your Token ID]'
TOKEN_SECRET = class="hl-string">'[Your Token Secret]'

class=class="hl-string">"hl-comment"># The base URL for the REST API, typically requires replacing hyphens with underscores in the account ID for the domain
class=class="hl-string">"hl-comment"># e.g., if your account ID is 1234567-sb1, the domain part would be 1234567_sb1.suitetalk.api.netsuite.com
class=class="hl-string">"hl-comment"># For production accounts, it's just the account ID, e.g., 1234567.suitetalk.api.netsuite.com
BASE_URL = fclass="hl-string">'https://{ACCOUNT_ID.replace("-", "_")}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql'

class=class="hl-string">"hl-comment"># --- Extraction Parameters --- #
START_DATE = class="hl-string">'2023-01-01' class=class="hl-string">"hl-comment"># Inclusive
END_DATE = class="hl-string">'2023-12-31'   class=class="hl-string">"hl-comment"># Inclusive
OUTPUT_FILE = class="hl-string">'netsuite_otc_event_log.csv'

class=class="hl-string">"hl-comment"># --- SuiteQL Query --- #
class=class="hl-string">"hl-comment"># This query unions data from multiple sources to create a comprehensive event log.
SUITEQL_QUERY = fclass="hl-string">"""
-- 1. Sales Order Created
SELECT
    t.tranId AS SalesOrder,
    'Sales Order Created' AS ActivityName,
    t.createdDate AS EventTime,
    BUILTIN.DF(t.entity) AS CustomerName,
    BUILTIN.DF(t.salesRep) AS User,
    t.total AS TotalOrderAmount,
    BUILTIN.DF(t.status) AS SalesOrderStatus,
    BUILTIN.DF(tl.item) AS ProductCategory, -- Simplified, assumes first line item category is representative
    t.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(t.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM
    Transaction AS t
    JOIN TransactionLine AS tl ON t.id = tl.transaction
WHERE
    t.type = 'SalesOrd'
    AND tl.mainLine = 'F'
    AND tl.item IS NOT NULL
    AND t.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND t.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 2. Credit Hold Applied / 3. Credit Hold Released / 4. Sales Order Approved / 14. Sales Order Closed
SELECT
    t.tranId AS SalesOrder,
    CASE 
        WHEN sn.newValue LIKE '%Pending Approv%' THEN 'Credit Hold Applied'
        WHEN sn.oldValue LIKE '%Pending Approv%' AND sn.newValue LIKE '%Pending Fulfill%' THEN 'Credit Hold Released'
        WHEN sn.newValue LIKE '%Pending Fulfill%' THEN 'Sales Order Approved'
        WHEN sn.newValue LIKE '%Closed%' THEN 'Sales Order Closed'
    END AS ActivityName,
    sn.date AS EventTime,
    BUILTIN.DF(t.entity) AS CustomerName,
    BUILTIN.DF(sn.author) AS User,
    t.total AS TotalOrderAmount,
    sn.newValue AS SalesOrderStatus,
    NULL AS ProductCategory,
    t.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(t.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM
    Transaction AS t
    JOIN SystemNote AS sn ON sn.recordId = t.id
WHERE
    t.type = 'SalesOrd'
    AND sn.field = 'TRANSACTION.STATUS'
    AND t.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    AND CASE 
        WHEN sn.newValue LIKE '%Pending Approv%' THEN 1
        WHEN sn.oldValue LIKE '%Pending Approv%' AND sn.newValue LIKE '%Pending Fulfill%' THEN 1
        WHEN sn.newValue LIKE '%Pending Fulfill%' THEN 1
        WHEN sn.newValue LIKE '%Closed%' THEN 1
        ELSE 0
    END = 1
    /* AND t.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 5. Sales Order Changed
SELECT
    t.tranId AS SalesOrder,
    'Sales Order Changed' AS ActivityName,
    sn.date AS EventTime,
    BUILTIN.DF(t.entity) AS CustomerName,
    BUILTIN.DF(sn.author) AS User,
    t.total AS TotalOrderAmount,
    BUILTIN.DF(t.status) AS SalesOrderStatus,
    NULL AS ProductCategory,
    t.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(t.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM
    Transaction AS t
    JOIN SystemNote AS sn ON sn.recordId = t.id
WHERE
    t.type = 'SalesOrd'
    AND sn.field IN ('TRANSACTION.TRANDATE', 'TRANSACTION.SHIPDATE', 'TRANSACTIONLINE.QUANTITY', 'TRANSACTIONLINE.RATE')
    AND t.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND t.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 6. Inventory Committed (approximated by 'Sales Order Approved' event)
SELECT
    t.tranId AS SalesOrder,
    'Inventory Committed' AS ActivityName,
    sn.date AS EventTime,
    BUILTIN.DF(t.entity) AS CustomerName,
    BUILTIN.DF(sn.author) AS User,
    t.total AS TotalOrderAmount,
    sn.newValue AS SalesOrderStatus,
    NULL AS ProductCategory,
    t.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(t.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM
    Transaction AS t
    JOIN SystemNote AS sn ON sn.recordId = t.id
WHERE
    t.type = 'SalesOrd'
    AND sn.field = 'TRANSACTION.STATUS'
    AND sn.newValue LIKE '%Pending Fulfill%'
    AND t.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND t.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 7. Order Fulfillment Created
SELECT
    so.tranId AS SalesOrder,
    'Order Fulfillment Created' AS ActivityName,
    ful.createdDate AS EventTime,
    BUILTIN.DF(so.entity) AS CustomerName,
    BUILTIN.DF(ful.createdby) AS User,
    so.total AS TotalOrderAmount,
    BUILTIN.DF(so.status) AS SalesOrderStatus,
    NULL AS ProductCategory,
    so.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(so.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM
    Transaction AS ful
    JOIN TransactionLink AS tl ON ful.id = tl.id
    JOIN Transaction AS so ON so.id = tl.previousdoc
WHERE
    ful.type = 'ItemShip'
    AND so.type = 'SalesOrd'
    AND so.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND so.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 8. Goods Picked / 9. Goods Packed / 10. Goods Shipped
SELECT
    so.tranId AS SalesOrder,
    CASE 
        WHEN sn.newValue LIKE '%Picked%' THEN 'Goods Picked'
        WHEN sn.newValue LIKE '%Packed%' THEN 'Goods Packed'
        WHEN sn.newValue LIKE '%Shipped%' THEN 'Goods Shipped'
    END AS ActivityName,
    sn.date AS EventTime,
    BUILTIN.DF(so.entity) AS CustomerName,
    BUILTIN.DF(sn.author) AS User,
    so.total AS TotalOrderAmount,
    BUILTIN.DF(so.status) AS SalesOrderStatus,
    NULL AS ProductCategory,
    so.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(so.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM
    Transaction AS ful
    JOIN SystemNote AS sn ON sn.recordId = ful.id
    JOIN TransactionLink AS tl ON ful.id = tl.id
    JOIN Transaction AS so ON so.id = tl.previousdoc
WHERE
    ful.type = 'ItemShip'
    AND so.type = 'SalesOrd'
    AND sn.field = 'TRANSACTION.STATUS'
    AND so.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND so.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 11. Invoice Created
SELECT
    so.tranId AS SalesOrder,
    'Invoice Created' AS ActivityName,
    inv.createdDate AS EventTime,
    BUILTIN.DF(so.entity) AS CustomerName,
    BUILTIN.DF(inv.createdby) AS User,
    so.total AS TotalOrderAmount,
    BUILTIN.DF(so.status) AS SalesOrderStatus,
    NULL AS ProductCategory,
    so.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(so.terms) AS PaymentTerms,
    inv.tranId AS InvoiceNumber
FROM
    Transaction AS inv
    JOIN TransactionLink AS tl ON inv.id = tl.id
    JOIN Transaction AS so ON so.id = tl.previousdoc
WHERE
    inv.type = 'CustInvc'
    AND so.type = 'SalesOrd'
    AND so.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND so.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 12. Credit Memo Created
SELECT 
    so.tranId AS SalesOrder,
    'Credit Memo Created' AS ActivityName,
    cm.createdDate AS EventTime,
    BUILTIN.DF(so.entity) AS CustomerName,
    BUILTIN.DF(cm.createdby) AS User,
    so.total AS TotalOrderAmount,
    BUILTIN.DF(so.status) AS SalesOrderStatus,
    NULL AS ProductCategory,
    so.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(so.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM 
    Transaction cm
    JOIN TransactionLink tl ON cm.id = tl.id
    JOIN Transaction so ON tl.previousdoc = so.id
WHERE 
    cm.type = 'CustCred'
    AND so.type = 'SalesOrd'
    AND so.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND so.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 13. Payment Received
SELECT
    so.tranId AS SalesOrder,
    'Payment Received' AS ActivityName,
    payment.createdDate AS EventTime,
    BUILTIN.DF(so.entity) AS CustomerName,
    BUILTIN.DF(payment.createdby) AS User,
    so.total AS TotalOrderAmount,
    BUILTIN.DF(so.status) AS SalesOrderStatus,
    NULL AS ProductCategory,
    so.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(so.terms) AS PaymentTerms,
    inv.tranId AS InvoiceNumber
FROM
    Transaction AS payment
    JOIN TransactionLink AS paymentLink ON payment.id = paymentLink.id
    JOIN Transaction AS inv ON paymentLink.previousdoc = inv.id
    JOIN TransactionLink AS invLink ON inv.id = invLink.id
    JOIN Transaction AS so ON invLink.previousdoc = so.id
WHERE
    payment.type = 'CustPymt'
    AND inv.type = 'CustInvc'
    AND so.type = 'SalesOrd'
    AND so.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND so.subsidiary = '[Your Subsidiary ID]' */
"""

def execute_suiteql_query(query):
    class="hl-string">"""Executes a SuiteQL query and handles pagination."""
    all_results = []
    offset = 0
    limit = 1000

    oauth = OAuth1(
        client_key=CONSUMER_KEY,
        client_secret=CONSUMER_SECRET,
        resource_owner_key=TOKEN_ID,
        resource_owner_secret=TOKEN_SECRET,
        realm=ACCOUNT_ID,
        signature_method=class="hl-string">'HMAC-SHA256'
    )

    while True:
        paginated_query = {
            class="hl-string">'q': query
        }

        headers = {
            class="hl-string">'Prefer': fclass="hl-string">'transient, odata.maxpagesize={limit}',
            class="hl-string">'Content-Type': class="hl-string">'application/json'
        }

        rest_url = fclass="hl-string">"{BASE_URL}?limit={limit}&offset={offset}"

        try:
            print(fclass="hl-string">"Fetching data... Page offset: {offset}")
            response = requests.post(rest_url, headers=headers, json=paginated_query, auth=oauth)
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raises an HTTPError for bad responses (4xx or 5xx)

            data = response.json()
            results = data.get(class="hl-string">'items', [])
            all_results.extend(results)

            if not data.get(class="hl-string">'hasMore', False):
                print(class="hl-string">"No more pages to fetch.")
                break

            offset += limit
            class=class="hl-string">"hl-comment"># Respect API concurrency limits
            time.sleep(1)

        except requests.exceptions.HTTPError as e:
            print(fclass="hl-string">"HTTP Error: {e}")
            print(fclass="hl-string">"Response Body: {e.response.text}")
            break
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Request Error: {e}")
            break
        except json.JSONDecodeError:
            print(fclass="hl-string">"JSON Decode Error. Response was: {response.text}")
            break

    return all_results

if __name__ == class="hl-string">"__main__":
    print(class="hl-string">"Starting NetSuite Order-to-Cash extraction...")
    
    results = execute_suiteql_query(SUITEQL_QUERY)

    if results:
        df = pd.json_normalize(results)
        class=class="hl-string">"hl-comment"># Rename columns to match ProcessMind requirements
        df.columns = [c.replace(class="hl-string">'.', class="hl-string">'_') for c in df.columns] class=class="hl-string">"hl-comment"># Flatten nested names
        df.rename(columns={
            class="hl-string">'salesorder': class="hl-string">'SalesOrder',
            class="hl-string">'activityname': class="hl-string">'ActivityName',
            class="hl-string">'eventtime': class="hl-string">'EventTime',
            class="hl-string">'customername': class="hl-string">'CustomerName',
            class="hl-string">'user': class="hl-string">'User',
            class="hl-string">'totalorderamount': class="hl-string">'TotalOrderAmount',
            class="hl-string">'salesorderstatus': class="hl-string">'SalesOrderStatus',
            class="hl-string">'productcategory': class="hl-string">'ProductCategory',
            class="hl-string">'requesteddeliverydate': class="hl-string">'RequestedDeliveryDate',
            class="hl-string">'paymentterms': class="hl-string">'PaymentTerms',
            class="hl-string">'invoicenumber': class="hl-string">'InvoiceNumber'
        }, inplace=True)
        
        class=class="hl-string">"hl-comment"># Ensure all required columns exist
        required_cols = [class="hl-string">'SalesOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'CustomerName', class="hl-string">'User', class="hl-string">'TotalOrderAmount', class="hl-string">'SalesOrderStatus', class="hl-string">'ProductCategory', class="hl-string">'RequestedDeliveryDate', class="hl-string">'PaymentTerms', class="hl-string">'InvoiceNumber']
        for col in required_cols:
            if col not in df.columns:
                df[col] = None

        class=class="hl-string">"hl-comment"># Convert EventTime to datetime and format it
        df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'])
        df = df.sort_values(by=[class="hl-string">'SalesOrder', class="hl-string">'EventTime'])

        df.to_csv(OUTPUT_FILE, index=False)
        print(fclass="hl-string">"Successfully extracted {len(df)} events to {OUTPUT_FILE}")
    else:
        print(class="hl-string">"No data returned from the query.")