Purchase to Pay - Requisition adatsablon

This configuration defines the columns to be set up in the 'Results' tab of a NetSuite Transaction Saved Search. Each item represents a column in the output file, which is then transformed into an event log.

**Search Type**: Transaction

**Criteria**:
*   Type = 'Purchase Requisition'
*   Main Line = 'true'
*   Date Created = between [Specify Start Date] and [Specify End Date]

**Results Columns**:

---
-- **Required & Recommended Attributes**
-- These columns will be duplicated for each event after transformation.
---
*   **Custom Label**: PurchaseRequisitionId
    *   **Field**: Internal ID
*   **Custom Label**: Requester
    *   **Field**: Requested By
*   **Custom Label**: Department
    *   **Field**: Department
*   **Custom Label**: TotalAmount
    *   **Field**: Amount
*   **Custom Label**: RequisitionStatus
    *   **Field**: Status

---
-- **Event Timestamp Columns for Unpivoting**
-- These columns will be unpivoted to create the ActivityName and EventTime.
---
*   **Custom Label**: Time - Requisition Created
    *   **Formula (Date/Time)**: {datecreated}

*   **Custom Label**: Time - Requisition Submitted for Approval
    *   **Formula (Date/Time)**: MIN(CASE WHEN {systemnotes.newvalue} = 'Pending Approval' THEN {systemnotes.date} END)

*   **Custom Label**: Time - Requisition Amended
    *   **Formula (Date/Time)**: MIN(CASE WHEN {systemnotes.type} = 'Change' AND {systemnotes.field} NOT IN ('Approval Status', 'Status') THEN {systemnotes.date} END)

*   **Custom Label**: Time - Approval Step Started
    *   **Formula (Date/Time)**: NULL -- Limitation: This event is not reliably captured via standard System Notes.

*   **Custom Label**: Time - Approval Step Approved
    *   **Formula (Date/Time)**: MIN(CASE WHEN {systemnotes.context} = 'Workflow' AND {systemnotes.newvalue} LIKE '%Approved%' THEN {systemnotes.date} END)

*   **Custom Label**: Time - Approval Step Rejected
    *   **Formula (Date/Time)**: MIN(CASE WHEN {systemnotes.context} = 'Workflow' AND {systemnotes.newvalue} = 'Rejected' THEN {systemnotes.date} END)

*   **Custom Label**: Time - Requisition Fully Approved
    *   **Formula (Date/Time)**: MIN(CASE WHEN {systemnotes.field} = 'Approval Status' AND {systemnotes.newvalue} = 'Approved' THEN {systemnotes.date} END)

*   **Custom Label**: Time - Requisition Finally Rejected
    *   **Formula (Date/Time)**: MIN(CASE WHEN {systemnotes.field} = 'Approval Status' AND {systemnotes.newvalue} = 'Rejected' THEN {systemnotes.date} END)

*   **Custom Label**: Time - Requisition Withdrawn
    *   **Formula (Date/Time)**: MIN(CASE WHEN {systemnotes.field} = 'Status' AND {systemnotes.newvalue} = 'Cancelled' THEN {systemnotes.date} END) -- Note: Status may vary based on configuration.

*   **Custom Label**: Time - Purchase Order Created
    *   **Formula (Date/Time)**: {applyingtransaction.datecreated}

*   **Custom Label**: Time - Requisition Closed
    *   **Formula (Date/Time)**: MIN(CASE WHEN {systemnotes.field} = 'Status' AND {systemnotes.newvalue} = 'Closed' THEN {systemnotes.date} END)

WITH Requisitions AS (
  SELECT
    T.TRANSACTION_ID AS PurchaseRequisitionId,
    T.TRANID AS PurchaseRequisitionNumber,
    T.DATE_CREATED AS CreatedDate,
    E.FULL_NAME AS Requester,
    D.FULL_NAME AS Department,
    T.FOREIGN_TOTAL AS TotalAmount,
    BUILTIN.DF(T.TRANSACTION_STATUS) AS RequisitionStatus,
    T.SUBSIDIARY_ID
  FROM
    Transactions AS T
    LEFT JOIN Employees AS E ON T.EMPLOYEE_ID = E.EMPLOYEE_ID
    LEFT JOIN Departments AS D ON T.DEPARTMENT_ID = D.DEPARTMENT_ID
  WHERE
    T.TRANSACTION_TYPE = 'PurchReq'
    AND T.TRANDATE BETWEEN '{start_date}' AND '{end_date}'
    AND T.SUBSIDIARY_ID = [Your_Subsidiary_ID]
)
-- 1. Requisition Created
SELECT
  R.PurchaseRequisitionId,
  'Requisition Created' AS ActivityName,
  R.CreatedDate AS EventTime,
  R.Requester,
  R.Department,
  R.TotalAmount,
  R.RequisitionStatus
FROM
  Requisitions AS R
UNION ALL
-- 2. Requisition Submitted for Approval & 7. Requisition Fully Approved & 8. Requisition Finally Rejected & 9. Requisition Withdrawn & 11. Requisition Closed
SELECT
  R.PurchaseRequisitionId,
  CASE 
    WHEN SN.NEW_VALUE LIKE '%Pending Approval%' THEN 'Requisition Submitted for Approval'
    WHEN SN.NEW_VALUE LIKE '%Approved%' THEN 'Requisition Fully Approved'
    WHEN SN.NEW_VALUE LIKE '%Rejected%' THEN 'Requisition Finally Rejected'
    WHEN SN.NEW_VALUE LIKE '%Cancelled%' THEN 'Requisition Withdrawn'
    WHEN SN.NEW_VALUE LIKE '%Closed%' THEN 'Requisition Closed'
  END AS ActivityName,
  SN.DATE AS EventTime,
  R.Requester,
  R.Department,
  R.TotalAmount,
  R.RequisitionStatus
FROM
  Requisitions AS R
  INNER JOIN System_Notes AS SN ON R.PurchaseRequisitionId = SN.TRANSACTION_ID
WHERE
  SN.FIELD = 'TRANSACTION.STATUS' 
  AND SN.NEW_VALUE IN ('Pending Approval', 'Approved', 'Rejected', 'Cancelled', 'Closed')
UNION ALL
-- 3. Requisition Amended
SELECT DISTINCT
  R.PurchaseRequisitionId,
  'Requisition Amended' AS ActivityName,
  SN.DATE AS EventTime,
  R.Requester,
  R.Department,
  R.TotalAmount,
  R.RequisitionStatus
FROM
  Requisitions AS R
  INNER JOIN System_Notes AS SN ON R.PurchaseRequisitionId = SN.TRANSACTION_ID
WHERE 
  SN.DATE > R.CreatedDate
  AND SN.FIELD NOT IN ('TRANSACTION.STATUS', 'TRANSACTION.NEXT_APPROVER') -- Exclude status changes captured elsewhere
UNION ALL
-- 4. Approval Step Started
SELECT
  R.PurchaseRequisitionId,
  'Approval Step Started' AS ActivityName,
  SN.DATE AS EventTime,
  R.Requester,
  R.Department,
  R.TotalAmount,
  R.RequisitionStatus
FROM
  Requisitions AS R
  INNER JOIN System_Notes AS SN ON R.PurchaseRequisitionId = SN.TRANSACTION_ID
WHERE
  SN.FIELD = 'TRANSACTION.NEXT_APPROVER' AND SN.NEW_VALUE IS NOT NULL
UNION ALL
-- 5. Approval Step Approved & 6. Approval Step Rejected
SELECT
  R.PurchaseRequisitionId,
  CASE
    WHEN SN.OLD_VALUE LIKE '%Pending Approval%' AND SN.NEW_VALUE LIKE '%Pending Approval%' THEN 'Approval Step Approved' -- Indicates transition between approvers
    WHEN SN.OLD_VALUE LIKE '%Pending Approval%' AND SN.NEW_VALUE LIKE '%Rejected%' THEN 'Approval Step Rejected'
  END AS ActivityName,
  SN.DATE AS EventTime,
  R.Requester,
  R.Department,
  R.TotalAmount,
  R.RequisitionStatus
FROM
  Requisitions AS R
  INNER JOIN System_Notes AS SN ON R.PurchaseRequisitionId = SN.TRANSACTION_ID
WHERE 
  SN.FIELD = 'TRANSACTION.STATUS' AND (
    (SN.OLD_VALUE LIKE '%Pending Approval%' AND SN.NEW_VALUE LIKE '%Pending Approval%') OR
    (SN.OLD_VALUE LIKE '%Pending Approval%' AND SN.NEW_VALUE LIKE '%Rejected%')
  )
UNION ALL
-- 10. Purchase Order Created
SELECT
  R.PurchaseRequisitionId,
  'Purchase Order Created' AS ActivityName,
  PO.TRANDATE AS EventTime,
  R.Requester,
  R.Department,
  R.TotalAmount,
  R.RequisitionStatus
FROM
  Requisitions AS R
  INNER JOIN Transaction_Lines AS TL ON R.PurchaseRequisitionId = TL.APPLIED_TO_TRANSACTION_ID
  INNER JOIN Transactions AS PO ON TL.TRANSACTION_ID = PO.TRANSACTION_ID
WHERE
  PO.TRANSACTION_TYPE = 'PurchOrd'

import requests
from requests_oauthlib import OAuth1Session
import json
import pandas as pd
import os

class=class="hl-string">"hl-comment"># --- Configuration: Replace with your NetSuite credentials and account details ---
netsuite_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"># --- Date Range: Set the period for the data extraction ---
start_date = class="hl-string">"[YYYY-MM-DD]" class=class="hl-string">"hl-comment"># Example: class="hl-string">"2023-01-01"
end_date = class="hl-string">"[YYYY-MM-DD]"   class=class="hl-string">"hl-comment"># Example: class="hl-string">"2023-12-31"

class=class="hl-string">"hl-comment"># --- SuiteQL Query for Purchase Requisition Event Log ---
class=class="hl-string">"hl-comment"># This query unions data from multiple sources to create a complete event log.
suiteql_query = fclass="hl-string">"""
SELECT
    pr.tranid AS PurchaseRequisitionId,
    'Requisition Created' AS ActivityName,
    pr.trandate AS EventTime,
    creator.entityid AS Requester,
    dept.name AS Department,
    pr.total AS TotalAmount,
    pr.status AS RequisitionStatus
FROM
    Transaction AS pr
JOIN
    Employee AS creator ON pr.entity = creator.id
LEFT JOIN
    Department AS dept ON pr.department = dept.id
WHERE
    pr.type = 'PurchReq'
    AND pr.lastmodifieddate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    t.tranid AS PurchaseRequisitionId,
    'Requisition Submitted for Approval' AS ActivityName,
    sn.date AS EventTime,
    e.entityid AS Requester,
    d.name AS Department,
    t.total AS TotalAmount,
    t.status AS RequisitionStatus
FROM
    SystemNote sn
JOIN
    Transaction t ON sn.recordid = t.id
JOIN
    Employee e ON t.entity = e.id
LEFT JOIN
    Department d ON t.department = d.id
WHERE
    sn.recordtypeid = -251 -- Purchase Requisition Record Type ID
    AND sn.field = 'APPROVALSTATUS'
    AND sn.newvalue = 'Pending Approval'
    AND t.lastmodifieddate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    t.tranid AS PurchaseRequisitionId,
    'Requisition Amended' AS ActivityName,
    sn.date AS EventTime,
    e.entityid AS Requester,
    d.name AS Department,
    t.total AS TotalAmount,
    t.status AS RequisitionStatus
FROM
    SystemNote sn
JOIN
    Transaction t ON sn.recordid = t.id
JOIN
    Employee e ON t.entity = e.id
LEFT JOIN
    Department d ON t.department = d.id
WHERE
    sn.recordtypeid = -251
    AND sn.field NOT IN ('STATUS', 'APPROVALSTATUS')
    AND t.lastmodifieddate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT 
    t.tranid AS PurchaseRequisitionId, 
    'Approval Step Approved' AS ActivityName, 
    sn.date AS EventTime, 
    e.entityid AS Requester, 
    d.name AS Department, 
    t.total AS TotalAmount, 
    t.status AS RequisitionStatus 
FROM 
    SystemNote sn
JOIN
    Transaction t ON sn.recordid = t.id
JOIN
    Employee e ON t.entity = e.id
LEFT JOIN
    Department d ON t.department = d.id
WHERE 
    sn.recordtypeid = -251 
    AND sn.field = 'APPROVALSTATUS' 
    AND sn.newvalue <> 'Approved' -- Excludes the final approval
    AND sn.oldvalue <> sn.newvalue
    AND t.lastmodifieddate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT 
    t.tranid AS PurchaseRequisitionId, 
    'Approval Step Rejected' AS ActivityName, 
    sn.date AS EventTime, 
    e.entityid AS Requester, 
    d.name AS Department, 
    t.total AS TotalAmount, 
    t.status AS RequisitionStatus 
FROM 
    SystemNote sn
JOIN
    Transaction t ON sn.recordid = t.id
JOIN
    Employee e ON t.entity = e.id
LEFT JOIN
    Department d ON t.department = d.id
WHERE 
    sn.recordtypeid = -251 
    AND sn.field = 'APPROVALSTATUS' 
    AND sn.newvalue = 'Rejected' -- Capturing a step rejection
    AND t.lastmodifieddate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    t.tranid AS PurchaseRequisitionId,
    'Requisition Fully Approved' AS ActivityName,
    sn.date AS EventTime,
    e.entityid AS Requester,
    d.name AS Department,
    t.total AS TotalAmount,
    t.status AS RequisitionStatus
FROM
    SystemNote sn
JOIN
    Transaction t ON sn.recordid = t.id
JOIN
    Employee e ON t.entity = e.id
LEFT JOIN
    Department d ON t.department = d.id
WHERE
    sn.recordtypeid = -251
    AND sn.field = 'APPROVALSTATUS'
    AND sn.newvalue = 'Approved'
    AND t.lastmodifieddate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    t.tranid AS PurchaseRequisitionId,
    'Requisition Finally Rejected' AS ActivityName,
    sn.date AS EventTime,
    e.entityid AS Requester,
    d.name AS Department,
    t.total AS TotalAmount,
    t.status AS RequisitionStatus
FROM
    SystemNote sn
JOIN
    Transaction t ON sn.recordid = t.id
JOIN
    Employee e ON t.entity = e.id
LEFT JOIN
    Department d ON t.department = d.id
WHERE
    sn.recordtypeid = -251
    AND sn.field = 'APPROVALSTATUS'
    AND sn.newvalue = 'Rejected'
    AND t.lastmodifieddate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    t.tranid AS PurchaseRequisitionId,
    'Requisition Withdrawn' AS ActivityName,
    sn.date AS EventTime,
    e.entityid AS Requester,
    d.name AS Department,
    t.total AS TotalAmount,
    t.status AS RequisitionStatus
FROM
    SystemNote sn
JOIN
    Transaction t ON sn.recordid = t.id
JOIN
    Employee e ON t.entity = e.id
LEFT JOIN
    Department d ON t.department = d.id
WHERE
    sn.recordtypeid = -251
    AND sn.field = 'STATUS'
    AND BUILTIN.CF(sn.newvalue) LIKE '%Cancelled%'
    AND t.lastmodifieddate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    appliedToTransaction.tranid AS PurchaseRequisitionId,
    'Purchase Order Created' AS ActivityName,
    po.trandate AS EventTime,
    creator.entityid AS Requester,
    dept.name AS Department,
    appliedToTransaction.total AS TotalAmount,
    appliedToTransaction.status AS RequisitionStatus
FROM
    Transaction po
JOIN
    TransactionLine poline ON po.id = poline.transaction
JOIN
    Transaction appliedToTransaction ON poline.appliedToTransaction = appliedToTransaction.id
JOIN
    Employee creator ON appliedToTransaction.entity = creator.id
LEFT JOIN
    Department dept ON appliedToTransaction.department = dept.id
WHERE
    po.type = 'PurchOrd'
    AND appliedToTransaction.type = 'PurchReq'
    AND po.lastmodifieddate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    t.tranid AS PurchaseRequisitionId,
    'Requisition Closed' AS ActivityName,
    sn.date AS EventTime,
    e.entityid AS Requester,
    d.name AS Department,
    t.total AS TotalAmount,
    t.status AS RequisitionStatus
FROM
    SystemNote sn
JOIN
    Transaction t ON sn.recordid = t.id
JOIN
    Employee e ON t.entity = e.id
LEFT JOIN
    Department d ON t.department = d.id
WHERE
    sn.recordtypeid = -251
    AND sn.field = 'STATUS'
    AND BUILTIN.CF(sn.newvalue) LIKE '%Closed%'
    AND t.lastmodifieddate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

-- Note: Approval Step Started is difficult to capture reliably from System Notes alone.
-- This event may require querying custom approval log records if available.

"""

def get_netsuite_data():
    class="hl-string">"""Fetches and processes data from NetSuite SuiteQL endpoint."""
    class=class="hl-string">"hl-comment"># Sanitize account ID for URL
    url_account_id = netsuite_account_id.replace(class="hl-string">'_', class="hl-string">'-').lower()
    url = fclass="hl-string">"https://{url_account_id}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql"
    
    all_items = []
    page_num = 1
    
    class=class="hl-string">"hl-comment"># Use OAuth1Session for authentication
    oauth = OAuth1Session(
        client_key=consumer_key,
        client_secret=consumer_secret,
        resource_owner_key=token_id,
        resource_owner_secret=token_secret,
        realm=netsuite_account_id,
        signature_method=class="hl-string">'HMAC-SHA256'
    )

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

    body = {
        class="hl-string">'q': suiteql_query
    }

    try:
        while url:
            print(fclass="hl-string">"Fetching page {page_num}...")
            response = oauth.post(url, headers=headers, data=json.dumps(body))
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raise an exception for bad status codes (4xx or 5xx)
            
            data = response.json()
            all_items.extend(data.get(class="hl-string">'items', []))

            class=class="hl-string">"hl-comment"># Check for next page link
            next_link = next((link for link in data.get(class="hl-string">'links', []) if link.get(class="hl-string">'rel') == class="hl-string">'next'), None)
            if next_link:
                url = next_link[class="hl-string">'href']
                body = None class=class="hl-string">"hl-comment"># Body is not needed for subsequent page requests
                page_num += 1
            else:
                url = None class=class="hl-string">"hl-comment"># No more pages

        print(fclass="hl-string">"Successfully fetched {len(all_items)} total records.")
        return all_items

    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Error fetching data from NetSuite API: {e}")
        if e.response:
            print(fclass="hl-string">"Response Status: {e.response.status_code}")
            print(fclass="hl-string">"Response Text: {e.response.text}")
        return None

if __name__ == class="hl-string">"__main__":
    print(class="hl-string">"Starting NetSuite Purchase Requisition data extraction...")
    raw_data = get_netsuite_data()

    if raw_data:
        class=class="hl-string">"hl-comment"># Convert to pandas DataFrame for processing
        df = pd.DataFrame(raw_data)

        class=class="hl-string">"hl-comment"># Data type conversions
        if not df.empty:
            df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'], errors=class="hl-string">'coerce')
            df[class="hl-string">'TotalAmount'] = pd.to_numeric(df[class="hl-string">'TotalAmount'], errors=class="hl-string">'coerce')

            class=class="hl-string">"hl-comment"># Sort data to ensure chronological order per case
            df = df.sort_values(by=[class="hl-string">'PurchaseRequisitionId', class="hl-string">'EventTime'])

            class=class="hl-string">"hl-comment"># Save to CSV
            output_filename = class="hl-string">'netsuite_pr_event_log.csv'
            df.to_csv(output_filename, index=False, encoding=class="hl-string">'utf-8')
            print(fclass="hl-string">"Event log successfully saved to {output_filename}")
        else:
            print(class="hl-string">"No data returned from the query. The output file was not created.")
    else:
        print(class="hl-string">"Extraction failed. Please check credentials, permissions, and query.")

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 * @NModuleScope SameAccount
 */

/*
 * This script extracts purchase requisition lifecycle events for process mining.
 * It queries data using multiple searches and compiles a single CSV event log file.
 */

define(['N/search', 'N/runtime', 'N/file', 'N/format'],

    (search, runtime, file, format) => {

        const execute = (scriptContext) => {

            const script = runtime.getCurrentScript();
            const startDate = script.getParameter({ name: 'custscript_pm_start_date' });
            const endDate = script.getParameter({ name: 'custscript_pm_end_date' });
            const targetFolderId = script.getParameter({ name: 'custscript_pm_folder_id' });

            if (!startDate || !endDate || !targetFolderId) {
                log.error('Missing Parameter', 'Start Date, End Date, or Target Folder ID is not set.');
                return;
            }

            let allEvents = [];

            // Search 1: Base requisition data and 'Created' event
            const reqSearch = search.create({
                type: 'purchaserequisition',
                filters: [
                    ['mainline', 'is', 'T'],
                    'AND',
                    ['datecreated', 'within', startDate, endDate]
                ],
                columns: [
                    'tranid', 'internalid', 'datecreated', 'entity', 'department', 'total', 'status', 'approvalstatus', 'closedate'
                ]
            });

            let reqDataMap = {};
            reqSearch.run().each(result => {
                const reqId = result.getValue('internalid');
                reqDataMap[reqId] = {
                    PurchaseRequisitionId: result.getValue('tranid') || reqId,
                    Requester: result.getText('entity'),
                    Department: result.getText('department'),
                    TotalAmount: result.getValue('total'),
                    RequisitionStatus: result.getText('status')
                };
                allEvents.push({
                    ...reqDataMap[reqId],
                    ActivityName: 'Requisition Created',
                    EventTime: result.getValue('datecreated')
                });

                // 'Requisition Closed' event
                const closeDate = result.getValue('closedate');
                if (closeDate) {
                    allEvents.push({
                        ...reqDataMap[reqId],
                        ActivityName: 'Requisition Closed',
                        EventTime: closeDate + ' 00:00:00'
                    });
                }
                return true;
            });
            
            const reqInternalIds = Object.keys(reqDataMap);
            if (reqInternalIds.length === 0) {
                log.audit('No Requisitions Found', 'No requisitions found for the specified date range.');
                return;
            }

            // Search 2: System notes for status changes and amendments
            const noteSearch = search.create({
                type: 'systemnote',
                filters: [
                    ['recordtype', 'is', 'PURCHASEREQUISITION'],
                    'AND',
                    ['recordid', 'anyof', reqInternalIds],
                    'AND',
                    ['field', 'isnotempty', '']
                ],
                columns: ['recordid', 'date', 'field', 'newvalue', 'oldvalue']
            });

            noteSearch.run().each(result => {
                const reqId = result.getValue('recordid');
                const baseData = reqDataMap[reqId];
                if (!baseData) return true;

                let activity = null;
                const field = result.getText('field');
                const newValue = result.getValue('newvalue');

                if (field === 'Approval Status') {
                    if (newValue === 'Pending Approval') activity = 'Requisition Submitted for Approval';
                    else if (newValue === 'Approved') activity = 'Requisition Fully Approved';
                    else if (newValue === 'Rejected') activity = 'Requisition Finally Rejected';
                } else if (field === 'Status' && (newValue.includes('Cancelled') || newValue.includes('Withdrawn'))) {
                    activity = 'Requisition Withdrawn';
                } else {
                    activity = 'Requisition Amended';
                }
                
                allEvents.push({
                    ...baseData,
                    ActivityName: activity,
                    EventTime: result.getValue('date')
                });
                return true;
            });

            // Search 3: Approval workflow steps (assumes SuiteApprovals custom record)
            // Placeholder: Replace 'customrecord_wr_approval_history' with your actual approval log record ID.
            try {
                const approvalSearch = search.create({
                    type: '[customrecord_wr_approval_history]', // IMPORTANT: Configure this to your system's approval log record
                    filters: [['custrecord_wr_approval_tran', 'anyof', reqInternalIds]],
                    columns: [
                        'custrecord_wr_approval_tran',
                        'created', // For 'Approval Step Started'
                        'lastmodified', // For 'Approval Step Approved' or 'Rejected'
                        'custrecord_wr_approval_status'
                    ]
                });
                approvalSearch.run().each(result => {
                    const reqId = result.getValue('custrecord_wr_approval_tran');
                    const baseData = reqDataMap[reqId];
                    if (!baseData) return true;

                    const status = result.getText('custrecord_wr_approval_status');
                    let activity = null;
                    let eventTime = null;

                    allEvents.push({
                       ...baseData,
                       ActivityName: 'Approval Step Started',
                       EventTime: result.getValue('created')
                    });

                    if (status === 'Approved') {
                        activity = 'Approval Step Approved';
                        eventTime = result.getValue('lastmodified');
                    } else if (status === 'Rejected') {
                        activity = 'Approval Step Rejected';
                        eventTime = result.getValue('lastmodified');
                    }

                    if (activity) {
                        allEvents.push({ ...baseData, ActivityName: activity, EventTime: eventTime });
                    }
                    return true;
                });
            } catch (e) {
                log.error('Approval Search Failed', 'Could not find or query the approval history custom record. This step will be skipped. Error: ' + e.message);
            }
            
            // Search 4: Purchase Order creation from Requisition
            const poSearch = search.create({
                type: 'purchaseorder',
                filters: [
                    ['createdfrom', 'anyof', reqInternalIds],
                    'AND',
                    ['mainline', 'is', 'T']
                ],
                columns: ['createdfrom', 'datecreated']
            });

            poSearch.run().each(result => {
                const reqId = result.getValue('createdfrom');
                const baseData = reqDataMap[reqId];
                if (!baseData) return true;

                allEvents.push({
                    ...baseData,
                    ActivityName: 'Purchase Order Created',
                    EventTime: result.getValue('datecreated')
                });
                return true;
            });

            // Generate CSV file
            if (allEvents.length > 0) {
                let csvContent = 'PurchaseRequisitionId,ActivityName,EventTime,Requester,Department,TotalAmount,RequisitionStatus\n';
                allEvents.forEach(event => {
                    const escape = (val) => val ? `"${String(val).replace(/"/g, '""')}"` : '';
                    csvContent += `${escape(event.PurchaseRequisitionId)},${escape(event.ActivityName)},${escape(event.EventTime)},${escape(event.Requester)},${escape(event.Department)},${event.TotalAmount || ''},${escape(event.RequisitionStatus)}\n`;
                });

                const formattedDate = format.format({ value: new Date(), type: format.Type.DATETIME }).replace(/[:\s]/g, '_');
                const csvFile = file.create({
                    name: `requisition_event_log_${formattedDate}.csv`,
                    fileType: file.Type.CSV,
                    contents: csvContent,
                    folder: targetFolderId
                });
                const fileId = csvFile.save();
                log.audit('CSV File Created', `Successfully created file ID ${fileId} in folder ID ${targetFolderId}. Events processed: ${allEvents.length}`);
            } else {
                log.audit('No Events Generated', 'The script ran successfully but did not generate any events to record.');
            }
        }

        return { execute };

    });