เทมเพลตข้อมูลสำหรับกระบวนการชำระเงินเจ้าหนี้การค้า

Type: Transaction

CRITERIA TAB:
- Type: is any of Vendor Bill, Bill Payment, Bill Credit
- Main Line: is True
- System Notes : Date: is within [Your Date Range]

RESULTS TAB:

Column 1: Document Number
- Label: InvoiceNumber

Column 2: System Notes : Date
- Label: EventTime

Column 3: Formula (Text)
- Label: Activity
- Formula:
CASE 
   WHEN {systemnotes.type} = 'Create' AND {type} = 'Bill' THEN 'Invoice Created'
   WHEN {systemnotes.type} = 'Create' AND {type} = 'Bill' AND {createdfrom} IS NOT NULL THEN 'Invoice Matched to PO'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Approval Status' AND {systemnotes.newvalue} = 'Pending Approval' THEN 'Invoice Approval Requested'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Approval Status' AND {systemnotes.newvalue} = 'Open' THEN 'Invoice Approved'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Approval Status' AND {systemnotes.newvalue} = 'Rejected' THEN 'Invoice Rejected'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Amount' THEN 'Invoice Amount Modified'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Account' THEN 'Invoice Account Modified'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Payment Hold' AND {systemnotes.newvalue} = 'T' THEN 'Payment Hold Applied'
   WHEN {type} = 'Bill' AND {systemnotes.field} = 'Payment Hold' AND {systemnotes.newvalue} = 'F' THEN 'Payment Hold Released'
   WHEN {type} = 'Bill Credit' AND {systemnotes.type} = 'Create' THEN 'Vendor Credit Applied'
   WHEN {type} = 'Bill Payment' AND {systemnotes.type} = 'Create' AND {status} = 'Pending Approval' THEN 'Payment Scheduled'
   WHEN {type} = 'Bill Payment' AND {systemnotes.type} = 'Create' AND {status} = 'Paid In Full' THEN 'Payment Processed'
   WHEN {type} = 'Bill Payment' AND {systemnotes.field} = 'Void' THEN 'Payment Voided'
   WHEN {type} = 'Bill Payment' AND {systemnotes.field} = 'Cleared' AND {systemnotes.newvalue} = 'T' THEN 'Payment Cleared'
   ELSE 'General Update'
END

Column 4: Formula (Text)
- Label: SourceSystem
- Formula: 'NetSuite'

Column 5: Formula (Text)
- Label: LastDataUpdate
- Formula: TO_CHAR({today}, 'YYYY-MM-DD HH24:MI:SS')

Column 6: Vendor : Name
- Label: VendorName

Column 7: Subsidiary
- Label: Subsidiary

Column 8: Amount
- Label: InvoiceAmount

Column 9: Due Date
- Label: DueDate

Column 10: Created From
- Label: PurchaseOrderNumber

Column 11: System Notes : Set by
- Label: User

Column 12: Department
- Label: Department

Column 13: Status
- Label: ApprovalStatus

Column 14: Formula (Text)
- Label: IsNonPO
- Formula: CASE WHEN {createdfrom} IS NULL THEN 'True' ELSE 'False' END

WITH VendorBills AS (
    SELECT
        t.ID AS TransactionID,
        t.tranid AS InvoiceNumber,
        t.trandate AS InvoiceDate,
        t.createDate AS CreatedDate,
        t.lastModifiedDate AS LastModified,
        e.altName AS VendorName,
        s.name AS Subsidiary,
        t.foreignTotal AS InvoiceAmount,
        t.dueDate AS DueDate,
        t.createdFrom AS CreatedFromID,
        CASE WHEN t.createdFrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
        dep.name AS Department,
        'NetSuite' AS SourceSystem
    FROM
        Transaction t
        LEFT JOIN Entity e ON t.entity = e.id
        LEFT JOIN Subsidiary s ON t.subsidiary = s.id
        LEFT JOIN Department dep ON t.department = dep.id
    WHERE
        t.type = 'VendBill'
        AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD') -- REPLACE WITH YOUR START DATE
        AND t.trandate <= TO_DATE('2023-12-31', 'YYYY-MM-DD') -- REPLACE WITH YOUR END DATE
),
PO_Data AS (
    SELECT
        t.ID AS PO_ID,
        t.tranid AS PONumber
    FROM
        Transaction t
    WHERE
        t.type = 'PurchOrd'
)

-- 1. Invoice Created
SELECT
    vb.InvoiceNumber,
    'Invoice Created' AS Activity,
    vb.CreatedDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Created' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID

UNION ALL

-- 2. Invoice Matched to PO
SELECT
    vb.InvoiceNumber,
    'Invoice Matched to PO' AS Activity,
    vb.CreatedDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Created' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID

UNION ALL

-- 3. Invoice Approval Requested (Derived from System Notes)
SELECT
    vb.InvoiceNumber,
    'Invoice Approval Requested' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Pending Approval' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'approvalstatus'
    AND sn.newValue = 'Pending Approval'

UNION ALL

-- 4. Invoice Approved
SELECT
    vb.InvoiceNumber,
    'Invoice Approved' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Open' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'approvalstatus'
    AND (sn.newValue = 'Approved' OR sn.newValue = 'Open')

UNION ALL

-- 5. Invoice Rejected
SELECT
    vb.InvoiceNumber,
    'Invoice Rejected' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Rejected' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'approvalstatus'
    AND sn.newValue = 'Rejected'

UNION ALL

-- 6. Invoice Amount Modified
SELECT
    vb.InvoiceNumber,
    'Invoice Amount Modified' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Modified' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    (sn.standardfield = 'foreigntotal' OR sn.standardfield = 'total')

UNION ALL

-- 7. Invoice Account Modified
SELECT
    vb.InvoiceNumber,
    'Invoice Account Modified' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Modified' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'account'

UNION ALL

-- 8. Payment Hold Applied
SELECT
    vb.InvoiceNumber,
    'Payment Hold Applied' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Hold' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'paymenthold'
    AND sn.newValue = 'T'

UNION ALL

-- 9. Payment Hold Released
SELECT
    vb.InvoiceNumber,
    'Payment Hold Released' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Released' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN SystemNote sn ON vb.TransactionID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    sn.standardfield = 'paymenthold'
    AND sn.newValue = 'F'

UNION ALL

-- 10. Vendor Credit Applied
SELECT
    vb.InvoiceNumber,
    'Vendor Credit Applied' AS Activity,
    vc.createDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Credit Applied' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vc ON tl.nextDoc = vc.ID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
WHERE
    vc.type = 'VendCred'

UNION ALL

-- 11. Payment Scheduled (Bill Payment created but pending)
SELECT
    vb.InvoiceNumber,
    'Payment Scheduled' AS Activity,
    vp.createDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Scheduled' AS ApprovalStatus,
    vb.IsNonPO,
    vp.trandate AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vp ON tl.nextDoc = vp.ID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
WHERE
    vp.type = 'VendPymt'
    AND vp.status = 'Pending Approval'

UNION ALL

-- 12. Payment Processed (Payment posted)
SELECT
    vb.InvoiceNumber,
    'Payment Processed' AS Activity,
    vp.trandate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Paid' AS ApprovalStatus,
    vb.IsNonPO,
    vp.trandate AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vp ON tl.nextDoc = vp.ID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
WHERE
    vp.type = 'VendPymt'
    AND (vp.status = 'PaidInFull' OR vp.status = 'Deposited' OR vp.status = 'NotDeposited')

UNION ALL

-- 13. Payment Voided
SELECT
    vb.InvoiceNumber,
    'Payment Voided' AS Activity,
    sn.date AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    emp.entityid AS User,
    vb.Department,
    'Voided' AS ApprovalStatus,
    vb.IsNonPO,
    NULL AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vp ON tl.nextDoc = vp.ID
    INNER JOIN SystemNote sn ON vp.ID = sn.recordID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
    LEFT JOIN Entity emp ON sn.name = emp.id
WHERE
    vp.type = 'VendPymt'
    AND sn.standardfield = 'status'
    AND sn.newValue LIKE '%Void%'

UNION ALL

-- 14. Payment Cleared
SELECT
    vb.InvoiceNumber,
    'Payment Cleared' AS Activity,
    vp.clearedDate AS EventTime,
    vb.SourceSystem,
    vb.LastModified AS LastDataUpdate,
    vb.VendorName,
    vb.Subsidiary,
    vb.InvoiceAmount,
    vb.DueDate,
    po.PONumber AS PurchaseOrderNumber,
    NULL AS User,
    vb.Department,
    'Cleared' AS ApprovalStatus,
    vb.IsNonPO,
    vp.trandate AS PaymentDate
FROM
    VendorBills vb
    INNER JOIN TransactionLink tl ON vb.TransactionID = tl.previousDoc
    INNER JOIN Transaction vp ON tl.nextDoc = vp.ID
    LEFT JOIN PO_Data po ON vb.CreatedFromID = po.PO_ID
WHERE
    vp.type = 'VendPymt'
    AND vp.clearedDate IS NOT NULL

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

class=class="hl-string">"hl-comment"># === CONFIGURATION ===
ACCOUNT_ID = class="hl-string">'1234567_SB1'  class=class="hl-string">"hl-comment"># Your NetSuite Account ID (Realm). Use hyphens for sandbox (e.g., 1234567-sb1)
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'
START_DATE = class="hl-string">'2024-01-01'  class=class="hl-string">"hl-comment"># Filter for data extraction

class=class="hl-string">"hl-comment"># Normalize Account ID for URL (NetSuite requires lowercase and hyphens)
url_account_id = ACCOUNT_ID.lower().replace(class="hl-string">'_', class="hl-string">'-')
BASE_URL = fclass="hl-string">'https://{url_account_id}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql'

class=class="hl-string">"hl-comment"># === SUITEQL QUERY ===
class=class="hl-string">"hl-comment"># This query extracts 14 activities using UNION ALL to combine Transaction data and System Notes.
suiteql_query = fclass="hl-string">"""
/* 1. Invoice Created */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Created' AS Activity,
    TO_CHAR(t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM Transaction t
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee e ON t.createdBy = e.id
LEFT JOIN Department d ON t.department = d.id
WHERE t.type = 'VendBill'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 2. Invoice Matched to PO */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Matched to PO' AS Activity,
    TO_CHAR(t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    'False' AS IsNonPO,
    NULL AS PaymentDate
FROM Transaction t
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee e ON t.createdBy = e.id
LEFT JOIN Department d ON t.department = d.id
WHERE t.type = 'VendBill'
AND t.createdfrom IS NOT NULL
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 3. Invoice Approval Requested (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Approval Requested' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30 /* Internal ID for Vendor Bill */
AND sn.field = 'status'
AND sn.value = 'Pending Approval'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 4. Invoice Approved (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Approved' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'status'
AND sn.value = 'Open'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 5. Invoice Rejected (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Rejected' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'status'
AND sn.value = 'Rejected'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 6. Invoice Amount Modified (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Amount Modified' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'total'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 7. Invoice Account Modified (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Invoice Account Modified' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'account'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 8. Payment Hold Applied (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Payment Hold Applied' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'paymenthold'
AND sn.value = 'T'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 9. Payment Hold Released (System Note) */
SELECT
    t.tranid AS InvoiceNumber,
    'Payment Hold Released' AS Activity,
    TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t.foreignTotal AS InvoiceAmount,
    t.duedate AS DueDate,
    t.otherrefnum AS PurchaseOrderNumber,
    rau.entityid AS User,
    d.name AS Department,
    t.status AS ApprovalStatus,
    CASE WHEN t.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    NULL AS PaymentDate
FROM SystemNote sn
JOIN Transaction t ON sn.recordid = t.id
LEFT JOIN Entity bu ON t.entity = bu.id
LEFT JOIN Subsidiary s ON t.subsidiary = s.id
LEFT JOIN Employee rau ON sn.name = rau.id
LEFT JOIN Department d ON t.department = d.id
WHERE sn.recordtypeid = -30
AND sn.field = 'paymenthold'
AND sn.value = 'F'
AND t.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 10. Vendor Credit Applied */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Vendor Credit Applied' AS Activity,
    TO_CHAR(t_cred.trandate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_cred.trandate AS PaymentDate
FROM Transaction t_cred
JOIN TransactionLine tl ON t_cred.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_cred.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_cred.type = 'VendCred'
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 11. Payment Scheduled */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Payment Scheduled' AS Activity,
    TO_CHAR(t_pay.createddate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_pay.trandate AS PaymentDate
FROM Transaction t_pay
JOIN TransactionLine tl ON t_pay.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_pay.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_pay.type = 'BillPymt'
AND t_pay.status = 'Pending Approval'
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 12. Payment Processed */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Payment Processed' AS Activity,
    TO_CHAR(t_pay.trandate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_pay.trandate AS PaymentDate
FROM Transaction t_pay
JOIN TransactionLine tl ON t_pay.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_pay.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_pay.type = 'BillPymt'
AND t_pay.status <> 'Voided'
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 13. Payment Voided */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Payment Voided' AS Activity,
    TO_CHAR(t_pay.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_pay.trandate AS PaymentDate
FROM Transaction t_pay
JOIN TransactionLine tl ON t_pay.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_pay.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_pay.type = 'BillPymt'
AND t_pay.status = 'Voided'
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')

UNION ALL

/* 14. Payment Cleared (Logic depends on bank rec features, using Check Cleared date or modification) */
SELECT
    t_bill.tranid AS InvoiceNumber,
    'Payment Cleared' AS Activity,
    /* Fallback to trandate if clearedDate is unavailable in your config */
    TO_CHAR(COALESCE(t_pay.createddate, t_pay.trandate), 'YYYY-MM-DD HH24:MI:SS') AS EventTime,
    'NetSuite' AS SourceSystem,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
    bu.name AS VendorName,
    s.name AS Subsidiary,
    t_bill.foreignTotal AS InvoiceAmount,
    t_bill.duedate AS DueDate,
    t_bill.otherrefnum AS PurchaseOrderNumber,
    e.entityid AS User,
    d.name AS Department,
    t_bill.status AS ApprovalStatus,
    CASE WHEN t_bill.createdfrom IS NULL THEN 'True' ELSE 'False' END AS IsNonPO,
    t_pay.trandate AS PaymentDate
FROM Transaction t_pay
JOIN TransactionLine tl ON t_pay.id = tl.transaction
JOIN Transaction t_bill ON tl.appliedtotransaction = t_bill.id
LEFT JOIN Entity bu ON t_bill.entity = bu.id
LEFT JOIN Subsidiary s ON t_bill.subsidiary = s.id
LEFT JOIN Employee e ON t_pay.createdBy = e.id
LEFT JOIN Department d ON t_bill.department = d.id
WHERE t_pay.type = 'BillPymt'
AND t_pay.status = 'Paid In Full' /* Indicates cleared/posted in some contexts */
AND t_bill.trandate >= TO_DATE('{START_DATE}', 'YYYY-MM-DD')
"""

class=class="hl-string">"hl-comment"># === AUTH AND EXECUTION ===

def get_suiteql_results(query):
    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'
    )
    
    all_rows = []
    offset = 0
    has_more = True
    
    print(class="hl-string">"Starting extraction...")
    
    while has_more:
        class=class="hl-string">"hl-comment"># SuiteQL REST API payload
        headers = {
            class="hl-string">'Prefer': class="hl-string">'transient',
            class="hl-string">'Content-Type': class="hl-string">'application/json'
        }
        
        class=class="hl-string">"hl-comment"># Add LIMIT/OFFSET to query if not using built-in cursors (SuiteQL doesn't support OFFSET well natively in string, 
        class=class="hl-string">"hl-comment"># but REST API handles pagination via links. However, for raw query, we usually rely on class="hl-string">'items' return)
        class=class="hl-string">"hl-comment"># Note: Pure SuiteQL via REST returns a set number of records. We must iterate via the class="hl-string">'links' provided in response
        class=class="hl-string">"hl-comment"># or typically, SuiteQL REST creates a job for large queries. 
        class=class="hl-string">"hl-comment"># For simplicity in this script, we assume a direct synchronous call pattern.
        
        class=class="hl-string">"hl-comment"># Actually, for large datasets, we must use the class="hl-string">'q' parameter. 
        class=class="hl-string">"hl-comment"># But SuiteQL POST body is standard. We will implement simple looping if class="hl-string">'hasMore' is present.
        
        payload = {
            class="hl-string">"q": query
        }
        
        response = requests.post(BASE_URL, headers=headers, auth=oauth, json=payload)
        
        if response.status_code != 200:
            print(fclass="hl-string">"Error: {response.text}")
            break
            
        data = response.json()
        items = data.get(class="hl-string">'items', [])
        all_rows.extend(items)
        
        class=class="hl-string">"hl-comment"># Check if there are more results (SuiteQL REST paging)
        class=class="hl-string">"hl-comment"># Standard REST response has class="hl-string">'hasMore' and class="hl-string">'links'. 
        class=class="hl-string">"hl-comment"># However, Query API V1 often requires traversing the class="hl-string">'next' link.
        has_more = data.get(class="hl-string">'hasMore', False)
        
        if has_more:
            class=class="hl-string">"hl-comment"># For subsequent pages, we follow the class="hl-string">'next' link logic or offset logic.
            class=class="hl-string">"hl-comment"># Note: The Query API creates a paged result set. We strictly need to follow the class="hl-string">'links' > class="hl-string">'next'.
            links = data.get(class="hl-string">'links', [])
            next_link = next((l[class="hl-string">'href'] for l in links if l[class="hl-string">'rel'] == class="hl-string">'next'), None)
            if next_link:
                BASE_URL = next_link class=class="hl-string">"hl-comment"># Update URL for next loop
                class=class="hl-string">"hl-comment"># Payload is not needed for next link GET usually, but Query API might differ.
                class=class="hl-string">"hl-comment"># If using links, we switch to GET for the next page.
                class=class="hl-string">"hl-comment"># (Simplified logic: In production, robustly handle the href)
            else:
                has_more = False
        
        print(fclass="hl-string">"Fetched {len(items)} rows. Total: {len(all_rows)}")
        time.sleep(1) class=class="hl-string">"hl-comment"># Rate limit protection

    return pd.DataFrame(all_rows)

class=class="hl-string">"hl-comment"># === RUN ===
if __name__ == class="hl-string">'__main__':
    df = get_suiteql_results(suiteql_query)
    
    class=class="hl-string">"hl-comment"># Post-processing
    if not df.empty:
        class=class="hl-string">"hl-comment"># Ensure numeric formats
        df[class="hl-string">'invoiceamount'] = pd.to_numeric(df[class="hl-string">'invoiceamount'], errors=class="hl-string">'coerce').fillna(0)
        
        class=class="hl-string">"hl-comment"># Save
        filename = class="hl-string">'netsuite_ap_event_log.csv'
        df.to_csv(filename, index=False)
        print(fclass="hl-string">"Success! Saved {len(df)} rows to {filename}")
    else:
        print(class="hl-string">"No data found.")