Jouw datatemplate voor Purchase to Pay-factuurverwerking

Below is the configuration for the NetSuite Saved Search, detailing the criteria (filters) and the result columns needed to extract the data.

**Search Type**: Transaction

**Criteria (Filters)**:
| Field | Description | Value |
|---|---|---|
| Type | Transaction Type | is any of: Vendor Bill, Vendor Payment, Vendor Credit |
| Main Line | Main Line Filter | is true |
| Date Created | Date Filter | is within: [Specify Date Range, e.g., last 6 months] |
| Subsidiary | Subsidiary Filter | is: [Your Subsidiary Name] (if applicable) |

**Results (Columns)**:
| Column Label | Field ID | Description |
|---|---|---|
| InvoiceNumber | tranid | Case ID. The unique number for the bill, payment, or credit. |
| InternalID | internalid | Internal system ID for joining. |
| DateCreated | datecreated | Timestamp for 'Vendor Bill Created' activity. |
| VendorName | entity | Required attribute 'Vendor Name'. |
| InvoiceAmount | amount | Required attribute 'Invoice Amount'. |
| DueDate | duedate | Required attribute 'Payment Due Date'. |
| Approver | approver | Recommended attribute 'Approver'. May require drilling into related fields. |
| ApprovalStatus | approvalstatus | Field for inferring activities like 'Bill Submitted For Approval', 'Bill Approved', 'Bill Rejected'. |
| SystemNotes_ApprovalStatus_Date | formuladate | Formula: `NS_CONCAT('systemnotes.newvalue=''Approved''')` to get the approval date. Similar formulas needed for other statuses. |
| PaymentDueDate | duedate | Attribute for 'Payment Due Date'. |
| Terms | terms | Used with Discount Date to derive 'Early Payment Discount Missed'. |
| DiscountDate | discountdate | The date by which payment must be made to receive a discount. |
| Status | statusref | The current status of the transaction, e.g., 'Open', 'Paid in Full'. |
| DatePostedToGL | trandate | Timestamp for 'Bill Posted To GL', typically the transaction date. |
| AppliedToTransaction | appliedtotransaction | Links payments/credits to the original bill. |
| ApplyingTransaction | applyingtransaction | Links bills to their payments/credits. |
| PaymentDate | applyingtransaction.trandate | The date of the payment transaction. Used for 'Vendor Payment Created' and 'Bill Paid In Full' events. |
| CreditMemoDate | applyingtransaction.trandate | The date a credit memo was applied. |
| PaymentHold | custbody_payment_hold | Placeholder for a custom 'Payment Block Set' / 'Released' checkbox. Track changes via System Notes. |
| MatchingStatus | custbody_matching_status | Placeholder for a custom field to identify 'Matching Discrepancy Identified'. |
| IsAutoMatched | custbody_is_auto_matched | Placeholder for a custom field for the 'Is Auto Matched' attribute. |

WITH BaseInvoices AS (
    SELECT 
        T.transaction_id,
        T.tranid AS InvoiceNumber,
        E.companyname AS VendorName,
        T.foreignTotal AS InvoiceAmount,
        T.dueDate AS PaymentDueDate,
        T.createdDate,
        T.status AS InvoiceStatus,
        T.approvalstatus,
        T.tranDate,
        -- Placeholder for IsAutoMatched logic. Adjust based on your configuration.
        -- This might be a custom field on the bill or inferred from other data.
        'No' AS IsAutoMatched 
    FROM 
        Transaction T
    JOIN 
        Entity E ON T.entity_id = E.entity_id
    WHERE 
        T.type = 'VendBill'
        AND T.createdDate BETWEEN TO_DATE('[START_DATE]', 'YYYY-MM-DD') AND TO_DATE('[END_DATE]', 'YYYY-MM-DD')
        AND T.subsidiary_id = [Your_Company_Subsidiary_ID]
),
Payments AS (
    SELECT 
        ATL.prev_tran_id AS bill_id,
        MAX(P.trandate) AS final_payment_date
    FROM
        Transaction P
    JOIN 
        TransactionLine APL ON P.transaction_id = APL.transaction_id
    JOIN
        AppliedToTransactionLink ATL ON APL.transactionline_id = ATL.appliedtolink_id
    WHERE P.type = 'VendPymt'
    GROUP BY ATL.prev_tran_id
)
-- 1. Vendor Bill Created
SELECT 
    B.InvoiceNumber,
    'Vendor Bill Created' AS ActivityName,
    B.createdDate AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    NULL AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id

UNION ALL

-- 2. Bill Submitted For Approval / 5. Bill Approved / 6. Bill Rejected
SELECT 
    B.InvoiceNumber,
    CASE 
        WHEN SN.new_value_id = '2' THEN 'Bill Approved' -- '2' is typically the internal ID for 'Approved'
        WHEN SN.new_value_id = '3' THEN 'Bill Rejected' -- '3' is typically the internal ID for 'Rejected'
        WHEN SN.new_value_id = '1' AND SN.old_value_id = '2' THEN 'Bill Sent For Rework' -- Example: Approved back to Pending
        WHEN SN.new_value_id = '1' THEN 'Bill Submitted For Approval' -- '1' is 'Pending Approval'
    END AS ActivityName,
    SN.date AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    U.entityid AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    SystemNotes SN ON B.transaction_id = SN.transaction_id
JOIN
    User U ON SN.author_id = U.id
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE 
    SN.field = 'TRAN_APPROVALSTATUS' 
    AND SN.new_value_id IN ('1', '2', '3')

UNION ALL

-- 3. Matching Discrepancy Identified / 4. Bill Sent For Rework (via custom status)
-- This section is highly dependent on custom fields and workflow. Adjust field name as needed.
SELECT 
    B.InvoiceNumber,
    CASE 
        WHEN SN.new_value = 'Matching Hold' THEN 'Matching Discrepancy Identified'
        WHEN SN.new_value = 'Pending Submission' AND SN.old_value = 'Pending Approval' THEN 'Bill Sent For Rework'
    END AS ActivityName,
    SN.date AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    U.entityid AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    SystemNotes SN ON B.transaction_id = SN.transaction_id
JOIN
    User U ON SN.author_id = U.id
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE 
    SN.field = '[Your_Custom_Status_Field_ID]' -- E.g., 'CUSTBODY_INVOICE_STATUS'
    AND (SN.new_value = 'Matching Hold' OR (SN.new_value = 'Pending Submission' AND SN.old_value = 'Pending Approval'))

UNION ALL

-- 7. Payment Block Set / 8. Payment Block Released
-- Assumes a custom checkbox field 'custbody_payment_hold'. 'T' for True, 'F' for False.
SELECT 
    B.InvoiceNumber,
    CASE WHEN SN.new_value = 'T' THEN 'Payment Block Set' ELSE 'Payment Block Released' END AS ActivityName,
    SN.date AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    U.entityid AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    SystemNotes SN ON B.transaction_id = SN.transaction_id
JOIN
    User U ON SN.author_id = U.id
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE 
    SN.field = '[Your_Payment_Hold_Field_ID]' -- E.g., 'CUSTBODY_PAYMENT_HOLD'
    AND SN.new_value IN ('T', 'F')

UNION ALL

-- 9. Bill Posted To GL
-- This typically happens upon approval in standard configurations.
SELECT 
    B.InvoiceNumber,
    'Bill Posted To GL' AS ActivityName,
    SN.date AS EventTime, -- Using approval date as posting date
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    NULL AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    SystemNotes SN ON B.transaction_id = SN.transaction_id
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE 
    SN.field = 'TRAN_APPROVALSTATUS' 
    AND SN.new_value_id = '2' -- 'Approved'

UNION ALL

-- 10. Payment Scheduled In Batch / 12. Vendor Payment Created
-- Using Vendor Payment creation as the event for both.
SELECT 
    B.InvoiceNumber,
    'Vendor Payment Created' AS ActivityName, 
    P.createdDate AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    NULL AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN PayDates.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    AppliedToTransactionLink ATL ON B.transaction_id = ATL.prev_tran_id
JOIN 
    TransactionLine APL ON ATL.appliedtolink_id = APL.transactionline_id
JOIN
    Transaction P ON APL.transaction_id = P.transaction_id
LEFT JOIN
    Payments PayDates ON B.transaction_id = PayDates.bill_id
WHERE P.type = 'VendPymt'

UNION ALL

-- 11. Early Payment Discount Missed
-- This event is created if a payment exists and was made after the discount date.
SELECT
    B.InvoiceNumber,
    'Early Payment Discount Missed' AS ActivityName,
    T.discountDate + 1 AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    NULL AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    'No' AS IsPaidOnTime
FROM
    BaseInvoices B
JOIN 
    Transaction T ON B.transaction_id = T.transaction_id
JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE 
    T.discountDate IS NOT NULL
    AND P.final_payment_date > T.discountDate

UNION ALL

-- 13. Bill Paid In Full
SELECT 
    B.InvoiceNumber,
    'Bill Paid In Full' AS ActivityName,
    SN.date AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    NULL AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    SystemNotes SN ON B.transaction_id = SN.transaction_id
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE 
    SN.field = 'TRAN_STATUS' AND SN.new_value_id = 'B' -- 'B' is 'Paid In Full' for Vendor Bills

UNION ALL

-- 14. Credit Memo Applied
SELECT 
    B.InvoiceNumber,
    'Credit Memo Applied' AS ActivityName, 
    C.createdDate AS EventTime,
    'NetSuite' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.VendorName,
    B.InvoiceAmount,
    B.PaymentDueDate,
    NULL AS Approver,
    B.InvoiceStatus,
    B.IsAutoMatched,
    CASE WHEN P.final_payment_date <= B.PaymentDueDate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
FROM 
    BaseInvoices B
JOIN 
    AppliedToTransactionLink ATL ON B.transaction_id = ATL.prev_tran_id
JOIN 
    TransactionLine APL ON ATL.appliedtolink_id = APL.transactionline_id
JOIN
    Transaction C ON APL.transaction_id = C.transaction_id
LEFT JOIN
    Payments P ON B.transaction_id = P.bill_id
WHERE C.type = 'VendCred'

import requests
from requests_oauthlib import OAuth1
import os
import json
import pandas as pd
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration: Replace with your NetSuite Credentials ---
ns_account_id = os.environ.get(class="hl-string">'NS_ACCOUNT_ID', class="hl-string">'YOUR_ACCOUNT_ID')
consumer_key = os.environ.get(class="hl-string">'NS_CONSUMER_KEY', class="hl-string">'YOUR_CONSUMER_KEY')
consumer_secret = os.environ.get(class="hl-string">'NS_CONSUMER_SECRET', class="hl-string">'YOUR_CONSUMER_SECRET')
token_id = os.environ.get(class="hl-string">'NS_TOKEN_ID', class="hl-string">'YOUR_TOKEN_ID')
token_secret = os.environ.get(class="hl-string">'NS_TOKEN_SECRET', class="hl-string">'YOUR_TOKEN_SECRET')

class=class="hl-string">"hl-comment"># Construct the NetSuite REST API URL
class=class="hl-string">"hl-comment"># Use .suitetalk.api for production, .connect.api for sandbox
url_base = fclass="hl-string">"https://{ns_account_id.lower().replace('_', '-')}.suitetalk.api.netsuite.com"
query_url = fclass="hl-string">"{url_base}/services/rest/query/v1/suiteql"

class=class="hl-string">"hl-comment"># --- SuiteQL Query ---
class=class="hl-string">"hl-comment"># This query unions multiple subqueries to create a complete event log.
class=class="hl-string">"hl-comment"># You MUST customize placeholders like class="hl-string">'[Matching Discrepancy Status]' and class="hl-string">'custbody_payment_hold'.
suiteql_query = class="hl-string">"""
SELECT
    InvoiceNumber,
    ActivityName,
    EventTime,
    'NetSuite' AS SourceSystem,
    BUILTIN.TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS') AS LastDataUpdate,
    VendorName,
    InvoiceAmount,
    PaymentDueDate,
    Approver,
    InvoiceStatus,
    IsAutoMatched,
    IsPaidOnTime
FROM (
    -- 1. Vendor Bill Created
    SELECT
        transaction.tranid AS InvoiceNumber,
        'Vendor Bill Created' AS ActivityName,
        transaction.trandate AS EventTime,
        vendor.companyname AS VendorName,
        transaction.foreignTotal AS InvoiceAmount,
        transaction.duedate AS PaymentDueDate,
        transaction.nextapprover AS Approver,
        transaction.status AS InvoiceStatus,
        NULL AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction
    JOIN
        Entity AS vendor ON transaction.entity = vendor.id
    WHERE
        transaction.type = 'VendBill' AND transaction.mainline = 'T'
        AND transaction.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD') -- Set your date range

    UNION ALL

    -- 2. Bill Status Changes (Submitted, Approved, Rejected, Rework) from System Notes
    SELECT
        transaction.tranid AS InvoiceNumber,
        CASE
            WHEN systemnote.newvalue = 'Pending Approval' THEN 'Bill Submitted For Approval'
            WHEN systemnote.newvalue = 'Approved' THEN 'Bill Approved'
            WHEN systemnote.newvalue = 'Rejected' THEN 'Bill Rejected'
            -- Configure the 'old value' based on your rework process
            WHEN systemnote.newvalue = 'Open' AND systemnote.oldvalue = 'Pending Approval' THEN 'Bill Sent For Rework'
            ELSE NULL
        END AS ActivityName,
        systemnote.date AS EventTime,
        vendor.companyname AS VendorName,
        transaction.foreignTotal AS InvoiceAmount,
        transaction.duedate AS PaymentDueDate,
        systemnote.name AS Approver,
        systemnote.newvalue AS InvoiceStatus,
        NULL AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction
    JOIN
        SystemNote ON systemnote.transactionid = transaction.id
    JOIN
        Entity AS vendor ON transaction.entity = vendor.id
    WHERE
        transaction.type = 'VendBill' AND transaction.mainline = 'T'
        AND systemnote.field = 'TRAN_APPROVALSTATUS' -- This is a common field, but may vary
        AND ActivityName IS NOT NULL
        AND transaction.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    UNION ALL

    -- 3. Matching Discrepancy Identified
    SELECT
        transaction.tranid AS InvoiceNumber,
        'Matching Discrepancy Identified' AS ActivityName,
        systemnote.date AS EventTime,
        vendor.companyname AS VendorName,
        transaction.foreignTotal AS InvoiceAmount,
        transaction.duedate AS PaymentDueDate,
        NULL AS Approver,
        systemnote.newvalue AS InvoiceStatus,
        'No' AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction
    JOIN
        SystemNote ON systemnote.transactionid = transaction.id
    JOIN
        Entity AS vendor ON transaction.entity = vendor.id
    WHERE
        transaction.type = 'VendBill' AND transaction.mainline = 'T'
        AND systemnote.field = 'TRAN_STATUS' -- May also be approvalstatus
        AND systemnote.newvalue = '[Matching Discrepancy Status]' -- *** CONFIGURE THIS VALUE ***
        AND transaction.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    UNION ALL

    -- 7 & 8. Payment Block Set/Released (using a custom field)
    SELECT
        transaction.tranid AS InvoiceNumber,
        CASE
            WHEN systemnote.newvalue = 'T' THEN 'Payment Block Set'
            WHEN systemnote.newvalue = 'F' THEN 'Payment Block Released'
        END AS ActivityName,
        systemnote.date AS EventTime,
        vendor.companyname AS VendorName,
        transaction.foreignTotal AS InvoiceAmount,
        transaction.duedate AS PaymentDueDate,
        systemnote.name AS Approver,
        transaction.status AS InvoiceStatus,
        NULL AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction
    JOIN
        SystemNote ON systemnote.transactionid = transaction.id
    JOIN
        Entity AS vendor ON transaction.entity = vendor.id
    WHERE
        transaction.type = 'VendBill' AND transaction.mainline = 'T'
        AND systemnote.field = 'CUSTBODY_PAYMENT_HOLD' -- *** CONFIGURE YOUR CUSTOM FIELD ID ***
        AND transaction.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    UNION ALL

    -- 9. Bill Posted to GL (assumed to occur at approval time)
    SELECT
        transaction.tranid AS InvoiceNumber,
        'Bill Posted To GL' AS ActivityName,
        systemnote.date AS EventTime,
        vendor.companyname AS VendorName,
        transaction.foreignTotal AS InvoiceAmount,
        transaction.duedate AS PaymentDueDate,
        systemnote.name AS Approver,
        'Posted' AS InvoiceStatus,
        NULL AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction
    JOIN
        SystemNote ON systemnote.transactionid = transaction.id
    JOIN
        Entity AS vendor ON transaction.entity = vendor.id
    WHERE
        transaction.type = 'VendBill' AND transaction.mainline = 'T'
        AND systemnote.field = 'TRAN_APPROVALSTATUS' AND systemnote.newvalue = 'Approved'
        AND transaction.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    UNION ALL

    -- 12. Vendor Payment Created & 10. Payment Scheduled In Batch (Approximated)
    SELECT
        bill.tranid AS InvoiceNumber,
        'Vendor Payment Created' AS ActivityName,
        payment.trandate AS EventTime,
        vendor.companyname AS VendorName,
        bill.foreignTotal AS InvoiceAmount,
        bill.duedate AS PaymentDueDate,
        payment.createdby AS Approver,
        payment.status AS InvoiceStatus,
        NULL AS IsAutoMatched,
        CASE WHEN payment.trandate <= bill.duedate THEN 'Yes' ELSE 'No' END AS IsPaidOnTime
    FROM
        Transaction AS payment
    JOIN
        TransactionLine AS paymentLine ON payment.id = paymentLine.transaction
    JOIN
        Transaction AS bill ON paymentLine.appliedToTransaction = bill.id
    JOIN
        Entity AS vendor ON bill.entity = vendor.id
    WHERE
        payment.type = 'VendPymt' AND payment.mainline = 'F' AND bill.type = 'VendBill'
        AND payment.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    UNION ALL

    -- 13. Bill Paid In Full
    SELECT
        transaction.tranid AS InvoiceNumber,
        'Bill Paid In Full' AS ActivityName,
        systemnote.date AS EventTime,
        vendor.companyname AS VendorName,
        transaction.foreignTotal AS InvoiceAmount,
        transaction.duedate AS PaymentDueDate,
        NULL AS Approver,
        systemnote.newvalue AS InvoiceStatus,
        NULL AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction
    JOIN
        SystemNote ON systemnote.transactionid = transaction.id
    JOIN
        Entity AS vendor ON transaction.entity = vendor.id
    WHERE
        transaction.type = 'VendBill' AND transaction.mainline = 'T'
        AND systemnote.field = 'TRAN_STATUS' AND systemnote.newvalue = 'Paid In Full'
        AND transaction.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    UNION ALL

    -- 14. Credit Memo Applied
    SELECT
        bill.tranid AS InvoiceNumber,
        'Credit Memo Applied' AS ActivityName,
        credit.trandate AS EventTime,
        vendor.companyname AS VendorName,
        bill.foreignTotal AS InvoiceAmount,
        bill.duedate AS PaymentDueDate,
        credit.createdby AS Approver,
        credit.status AS InvoiceStatus,
        NULL AS IsAutoMatched,
        NULL AS IsPaidOnTime
    FROM
        Transaction AS credit
    JOIN
        TransactionLine AS creditLine ON credit.id = creditLine.transaction
    JOIN
        Transaction AS bill ON creditLine.appliedToTransaction = bill.id
    JOIN
        Entity AS vendor ON bill.entity = vendor.id
    WHERE
        credit.type = 'VendCred' AND credit.mainline = 'F' AND bill.type = 'VendBill'
        AND credit.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

    -- Note: 'Early Payment Discount Missed' is best derived in post-processing. A query for it is complex and unreliable.
)
ORDER BY
    InvoiceNumber, EventTime
"""

def execute_suiteql(query, oauth_session):
    headers = {
        class="hl-string">'Prefer': class="hl-string">'transient',
        class="hl-string">'Content-Type': class="hl-string">'application/json'
    }
    payload = json.dumps({class="hl-string">'q': query})
    all_results = []
    offset = 0
    limit = 1000  class=class="hl-string">"hl-comment"># NetSuite's max page size

    while True:
        paginated_url = fclass="hl-string">"{query_url}?limit={limit}&offset={offset}"
        try:
            response = oauth_session.post(paginated_url, headers=headers, data=payload)
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            data = response.json()

            if not data or class="hl-string">'items' not in data or not data[class="hl-string">'items']:
                print(class="hl-string">"No more items found.")
                break
            
            results = data.get(class="hl-string">'items', [])
            all_results.extend(results)
            print(fclass="hl-string">"Fetched {len(results)} records, total so far: {len(all_results)}")

            has_more = data.get(class="hl-string">'hasMore', False)
            if not has_more:
                break

            offset += limit

        except requests.exceptions.HTTPError as e:
            print(fclass="hl-string">"HTTP Error: {e.response.status_code} {e.response.text}")
            break
        except Exception as e:
            print(fclass="hl-string">"An error occurred: {e}")
            break

    return all_results

def main():
    print(class="hl-string">"Starting NetSuite data extraction...")
    oauth = OAuth1(
        client_key=consumer_key,
        client_secret=consumer_secret,
        resource_owner_key=token_id,
        resource_owner_secret=token_secret,
        realm=ns_account_id
    )

    results = execute_suiteql(suiteql_query, requests.Session()) class=class="hl-string">"hl-comment"># Use a basic session, OAuth1 handles auth
    class=class="hl-string">"hl-comment"># The above line needs correction, the oauth object should be passed to the session
    class=class="hl-string">"hl-comment"># Correct implementation:
    session = requests.Session()
    session.auth = oauth
    
    results = execute_suiteql(suiteql_query, session)

    if results:
        df = pd.DataFrame(results)
        class=class="hl-string">"hl-comment"># Re-map columns from the response which are often dictionaries
        for col in df.columns:
            df[col] = df[col].apply(lambda x: x[class="hl-string">'value'] if isinstance(x, dict) and class="hl-string">'value' in x else x)
        
        output_filename = class="hl-string">'netsuite_invoice_event_log.csv'
        df.to_csv(output_filename, index=False, encoding=class="hl-string">'utf-8')
        print(fclass="hl-string">"Successfully extracted {len(df)} events to {output_filename}")
    else:
        print(class="hl-string">"No data was extracted.")

if __name__ == class="hl-string">"__main__":
    main()