Votre modèle de données de gestion des stocks

The following represents the configuration for 15 separate NetSuite Saved Searches. The results of these searches must be exported to CSV and combined into a single file.

**Search 1: Goods Receipt Recorded**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Item Receipt'; Main Line is false; Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Goods Receipt Recorded' (Custom Label: ActivityName), Date (Custom Label: TransactionDate), Created From.Date (Custom Label: EndTime), Created By (Custom Label: User), Location (Custom Label: Location), Memo (Custom Label: MovementReasonCode), Amount (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

**Search 2: Quality Inspection Performed**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Item Receipt'; Main Line is false; [Your Custom Inspection Status Field] is '[Completed Status]'; Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Quality Inspection Performed' (Custom Label: ActivityName), Last Modified Date (Custom Label: TransactionDate), null (Custom Label: EndTime), Last Modified By (Custom Label: User), Location (Custom Label: Location), null (Custom Label: MovementReasonCode), Amount (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

**Search 3: Put-away Initiated**
*   **Search Type**: Transaction
*   **Criteria**: Type is '[Your Bin Putaway Worksheet Type]'; Main Line is false; Status is '[Initiated Status]'; Date Created is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Put-away Initiated' (Custom Label: ActivityName), Date Created (Custom Label: TransactionDate), null (Custom Label: EndTime), Created By (Custom Label: User), Location (Custom Label: Location), null (Custom Label: MovementReasonCode), null (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

**Search 4: Put-away Completed**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Bin Transfer' or '[Your Bin Putaway Worksheet Type]'; Main Line is false; Status is '[Completed Status]'; Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Put-away Completed' (Custom Label: ActivityName), Date (Custom Label: TransactionDate), Date (Custom Label: EndTime), Last Modified By (Custom Label: User), Location (Custom Label: Location), Memo (Custom Label: MovementReasonCode), null (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

**Search 5: Stock Moved Internally**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Inventory Transfer' or 'Bin Transfer'; Main Line is false; Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Stock Moved Internally' (Custom Label: ActivityName), Date (Custom Label: TransactionDate), Date (Custom Label: EndTime), Created By (Custom Label: User), Location (Custom Label: Location), Memo (Custom Label: MovementReasonCode), null (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

**Search 6: Inventory Count Initiated**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Inventory Count'; Main Line is false; Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Inventory Count Initiated' (Custom Label: ActivityName), Date (Custom Label: TransactionDate), null (Custom Label: EndTime), Created By (Custom Label: User), Location (Custom Label: Location), Memo (Custom Label: MovementReasonCode), null (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

**Search 7: Inventory Count Approved**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Inventory Count'; Main Line is false; Status is 'Approved'; Last Modified Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Inventory Count Approved' (Custom Label: ActivityName), Last Modified Date (Custom Label: TransactionDate), Last Modified Date (Custom Label: EndTime), Last Modified By (Custom Label: User), Location (Custom Label: Location), Memo (Custom Label: MovementReasonCode), null (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

**Search 8: Inventory Discrepancy Adjusted**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Inventory Adjustment'; Main Line is false; Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Inventory Discrepancy Adjusted' (Custom Label: ActivityName), Date (Custom Label: TransactionDate), Date (Custom Label: EndTime), Created By (Custom Label: User), Location (Custom Label: Location), Memo (Custom Label: MovementReasonCode), Est. Extended Cost (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

**Search 9: Picking Wave Created**
*   **Search Type**: Transaction
*   **Criteria**: Type is '[Your Wave Transaction Type]'; Main Line is false; Date Created is within [Start Date] and [End Date]
*   **Results**: Formula (Text): 'Extract Lot Number from Memo or Custom Field' (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Picking Wave Created' (Custom Label: ActivityName), Date Created (Custom Label: TransactionDate), null (Custom Label: EndTime), Created By (Custom Label: User), Location (Custom Label: Location), null (Custom Label: MovementReasonCode), null (Custom Label: StockValue), Type (Custom Label: TransactionType), null (Custom Label: Item)

**Search 10: Picking Completed**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Item Fulfillment'; Main Line is false; Status is 'Picked' or 'Packed'; Last Modified Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Picking Completed' (Custom Label: ActivityName), Last Modified Date (Custom Label: TransactionDate), Last Modified Date (Custom Label: EndTime), Last Modified By (Custom Label: User), Location (Custom Label: Location), null (Custom Label: MovementReasonCode), Amount (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

**Search 11: Packing Completed**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Item Fulfillment'; Main Line is false; Status is 'Packed' or 'Shipped'; Last Modified Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Packing Completed' (Custom Label: ActivityName), Last Modified Date (Custom Label: TransactionDate), Last Modified Date (Custom Label: EndTime), Last Modified By (Custom Label: User), Location (Custom Label: Location), null (Custom Label: MovementReasonCode), Amount (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

**Search 12: Goods Issue Recorded**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Item Fulfillment'; Main Line is false; Status is 'Shipped'; Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Goods Issue Recorded' (Custom Label: ActivityName), Actual Ship/Receipt Date (Custom Label: TransactionDate), Actual Ship/Receipt Date (Custom Label: EndTime), Last Modified By (Custom Label: User), Location (Custom Label: Location), Memo (Custom Label: MovementReasonCode), Amount (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)
	
**Search 13: Return Authorization Created**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Return Authorization'; Main Line is false; Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Return Authorization Created' (Custom Label: ActivityName), Date (Custom Label: TransactionDate), null (Custom Label: EndTime), Created By (Custom Label: User), Location (Custom Label: Location), Memo (Custom Label: MovementReasonCode), Amount (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

**Search 14: Return Goods Received**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Item Receipt'; Main Line is false; Created From.Type is 'Return Authorization'; Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Return Goods Received' (Custom Label: ActivityName), Date (Custom Label: TransactionDate), Date (Custom Label: EndTime), Created By (Custom Label: User), Location (Custom Label: Location), Memo (Custom Label: MovementReasonCode), Amount (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

**Search 15: Stock Scrapped/Disposed**
*   **Search Type**: Transaction
*   **Criteria**: Type is 'Inventory Adjustment'; Main Line is false; Quantity is negative; [Your Reason Code Field] is '[Scrap Reason]'; Date is within [Start Date] and [End Date]; Inventory Detail.Lot/Serial Number is not empty
*   **Results**: Inventory Detail.Lot/Serial Number (Custom Label: InventoryBatchLotNumber), Formula (Text): 'Stock Scrapped/Disposed' (Custom Label: ActivityName), Date (Custom Label: TransactionDate), Date (Custom Label: EndTime), Created By (Custom Label: User), Location (Custom Label: Location), Memo (Custom Label: MovementReasonCode), Est. Extended Cost (Custom Label: StockValue), Type (Custom Label: TransactionType), Item (Custom Label: Item)

WITH EventData AS (
  SELECT
    T.tranid AS TransactionId,
    T.trandate AS TransactionDate,
    T.id AS TransactionInternalId,
    T.lastmodifieddate AS LastModifiedDate,
    BUILTIN.DF(T.type) AS TransactionType,
    BUILTIN.DF(T.status) AS TransactionStatus,
    T.memo AS TransactionMemo,
    TL.id AS TransactionLineId,
    IA.inventorynumber AS InventoryNumberId,
    INUMB.inventorynumber AS InventoryBatchLotNumber,
    I.itemid AS Sku,
    E.entityid AS UserName,
    L.fullname AS LocationName,
    TL.quantity AS Quantity,
    TL.rate AS Rate,
    T.createdfrom AS CreatedFromId,
    INUMB.custitemnumber_quality_status AS QualityStatus -- Placeholder for a custom quality status field
  FROM
    Transaction AS T
    JOIN TransactionLine AS TL ON T.id = TL.transaction
    JOIN InventoryAssignment AS IA ON TL.id = IA.transactionline
    JOIN InventoryNumber AS INUMB ON IA.inventorynumber = INUMB.id
    LEFT JOIN Item AS I ON TL.item = I.id
    LEFT JOIN Employee AS E ON T.lastmodifiedby = E.id
    LEFT JOIN Location AS L ON TL.location = L.id
  WHERE
    T.trandate >= '2023-01-01' AND T.trandate < '2024-01-01' -- Define your date range here
    AND T.type IN ('ItemRcpt', 'InvTrnfr', 'BinTrnfr', 'InvCount', 'InvAdjst', 'ItemShip', 'RtnAuth')
    AND IA.inventorynumber IS NOT NULL
)
SELECT
  InventoryBatchLotNumber,
  'Goods Receipt Recorded' AS ActivityName,
  TransactionDate,
  TransactionDate as EndTime,
  UserName AS User,
  LocationName AS Location,
  TransactionMemo AS MovementReasonCode,
  (Quantity * Rate) AS StockValue,
  TransactionType,
  Sku AS Item
FROM EventData
WHERE TransactionType = 'Item Receipt'
UNION ALL
SELECT
  InventoryBatchLotNumber,
  'Quality Inspection Performed' AS ActivityName,
  LastModifiedDate AS TransactionDate, -- Using last modified as a proxy for this custom event
  LastModifiedDate as EndTime,
  UserName AS User,
  LocationName AS Location,
  'Quality Status set to ' || QualityStatus AS MovementReasonCode,
  (Quantity * Rate) AS StockValue,
  TransactionType,
  Sku AS Item
FROM EventData
WHERE QualityStatus = 'Approved' -- This condition requires customization based on your quality process
UNION ALL
-- NetSuite WMS activities often use separate tables. The following are placeholders if not using EventData.
-- If not using WMS, these activities might be inferred from status changes on other transactions.
SELECT
  NULL AS InventoryBatchLotNumber, -- This information needs to be joined from your WMS implementation
  'Put-away Initiated' AS ActivityName,
  trandate AS TransactionDate,
  trandate as EndTime,
  BUILTIN.DF(lastmodifiedby) AS User,
  BUILTIN.DF(location) AS Location,
  memo AS MovementReasonCode,
  NULL AS StockValue,
  'Bin Putaway Worksheet' AS TransactionType,
  NULL AS Item
FROM Transaction
WHERE type = 'BinWksht' AND status = 'A' -- Status for 'Pending'
UNION ALL
SELECT
  NULL AS InventoryBatchLotNumber, -- This information needs to be joined from your WMS implementation
  'Put-away Completed' AS ActivityName,
  lastmodifieddate AS TransactionDate,
  lastmodifieddate as EndTime,
  BUILTIN.DF(lastmodifiedby) AS User,
  BUILTIN.DF(location) AS Location,
  memo AS MovementReasonCode,
  NULL AS StockValue,
  'Bin Transfer' AS TransactionType,
  NULL AS Item
FROM Transaction
WHERE type = 'BinTrnfr' AND BUILTIN.DF(status) = 'Completed'
UNION ALL
SELECT
  InventoryBatchLotNumber,
  'Stock Moved Internally' AS ActivityName,
  TransactionDate,
  TransactionDate as EndTime,
  UserName AS User,
  LocationName AS Location,
  TransactionMemo AS MovementReasonCode,
  (Quantity * Rate) AS StockValue,
  TransactionType,
  Sku AS Item
FROM EventData
WHERE TransactionType IN ('Inventory Transfer', 'Bin Transfer')
UNION ALL
SELECT
  InventoryBatchLotNumber,
  'Inventory Count Initiated' AS ActivityName,
  TransactionDate,
  TransactionDate as EndTime,
  UserName AS User,
  LocationName AS Location,
  TransactionMemo AS MovementReasonCode,
  NULL AS StockValue,
  TransactionType,
  Sku AS Item
FROM EventData
WHERE TransactionType = 'Inventory Count'
UNION ALL
SELECT
  InventoryBatchLotNumber,
  'Inventory Count Approved' AS ActivityName,
  LastModifiedDate AS TransactionDate,
  LastModifiedDate as EndTime,
  UserName AS User,
  LocationName AS Location,
  TransactionMemo AS MovementReasonCode,
  NULL AS StockValue,
  TransactionType,
  Sku AS Item
FROM EventData
WHERE TransactionType = 'Inventory Count' AND TransactionStatus LIKE '%Approved%'
UNION ALL
SELECT
  InventoryBatchLotNumber,
  'Inventory Discrepancy Adjusted' AS ActivityName,
  TransactionDate,
  TransactionDate as EndTime,
  UserName AS User,
  LocationName AS Location,
  TransactionMemo AS MovementReasonCode,
  (Quantity * Rate) AS StockValue,
  TransactionType,
  Sku AS Item
FROM EventData
WHERE TransactionType = 'Inventory Adjustment'
UNION ALL
-- Picking Wave is a WMS-specific record. This query assumes a 'Wave' table exists.
SELECT
  NULL AS InventoryBatchLotNumber, -- Requires joining WaveTransaction and other tables to get lot numbers
  'Picking Wave Created' AS ActivityName,
  W.trandate AS TransactionDate,
  W.trandate AS EndTime,
  BUILTIN.DF(W.lastmodifiedby) AS User,
  BUILTIN.DF(WT.location) AS Location,
  W.memo AS MovementReasonCode,
  NULL AS StockValue,
  'Wave' AS TransactionType,
  NULL AS Item
FROM Wave AS W
JOIN WaveTransaction AS WT ON W.id = WT.wave
WHERE W.trandate >= '2023-01-01' AND W.trandate < '2024-01-01' -- Define your date range here
UNION ALL
SELECT
  InventoryBatchLotNumber,
  'Picking Completed' AS ActivityName,
  LastModifiedDate AS TransactionDate,
  LastModifiedDate as EndTime,
  UserName AS User,
  LocationName AS Location,
  TransactionMemo AS MovementReasonCode,
  (Quantity * Rate) AS StockValue,
  TransactionType,
  Sku AS Item
FROM EventData
WHERE TransactionType = 'Item Fulfillment' AND TransactionStatus LIKE '%Picked%'
UNION ALL
SELECT
  InventoryBatchLotNumber,
  'Packing Completed' AS ActivityName,
  LastModifiedDate AS TransactionDate,
  LastModifiedDate as EndTime,
  UserName AS User,
  LocationName AS Location,
  TransactionMemo AS MovementReasonCode,
  (Quantity * Rate) AS StockValue,
  TransactionType,
  Sku AS Item
FROM EventData
WHERE TransactionType = 'Item Fulfillment' AND TransactionStatus LIKE '%Packed%'
UNION ALL
SELECT
  InventoryBatchLotNumber,
  'Goods Issue Recorded' AS ActivityName,
  TransactionDate,
  TransactionDate as EndTime,
  UserName AS User,
  LocationName AS Location,
  TransactionMemo AS MovementReasonCode,
  (Quantity * Rate) AS StockValue,
  TransactionType,
  Sku AS Item
FROM EventData
WHERE TransactionType = 'Item Fulfillment' AND TransactionStatus LIKE '%Shipped%'
UNION ALL
SELECT
  InventoryBatchLotNumber,
  'Return Authorization Created' AS ActivityName,
  TransactionDate,
  TransactionDate as EndTime,
  UserName AS User,
  LocationName AS Location,
  TransactionMemo AS MovementReasonCode,
  (Quantity * Rate) AS StockValue,
  TransactionType,
  Sku AS Item
FROM EventData
WHERE TransactionType = 'Return Authorization'
UNION ALL
SELECT
  ED.InventoryBatchLotNumber,
  'Return Goods Received' AS ActivityName,
  ED.TransactionDate,
  ED.TransactionDate as EndTime,
  ED.UserName AS User,
  ED.LocationName AS Location,
  ED.TransactionMemo AS MovementReasonCode,
  (ED.Quantity * ED.Rate) AS StockValue,
  ED.TransactionType,
  ED.Sku AS Item
FROM EventData ED
JOIN Transaction T_RMA ON ED.CreatedFromId = T_RMA.id
WHERE ED.TransactionType = 'Item Receipt' AND T_RMA.type = 'RtnAuth'
UNION ALL
SELECT
  InventoryBatchLotNumber,
  'Stock Scrapped/Disposed' AS ActivityName,
  TransactionDate,
  TransactionDate as EndTime,
  UserName AS User,
  LocationName AS Location,
  TransactionMemo AS MovementReasonCode,
  (Quantity * Rate) AS StockValue,
  TransactionType,
  Sku AS Item
FROM EventData
WHERE TransactionType = 'Inventory Adjustment' AND Quantity < 0 AND (LOWER(TransactionMemo) LIKE '%scrap%' OR LOWER(TransactionMemo) LIKE '%dispose%');

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

class=class="hl-string">"hl-comment"># --- Configuration: Replace with your NetSuite Credentials and Parameters ---
ns_account_id = class="hl-string">"[Your NetSuite Account ID]"  class=class="hl-string">"hl-comment"># e.g., class="hl-string">"1234567_SB1"
ns_consumer_key = class="hl-string">"[Your Consumer Key]"
ns_consumer_secret = class="hl-string">"[Your Consumer Secret]"
ns_token_id = class="hl-string">"[Your Token ID]"
ns_token_secret = class="hl-string">"[Your Token Secret]"

class=class="hl-string">"hl-comment"># --- Date Range for Extraction ---
start_date = class="hl-string">"2023-01-01"
end_date = class="hl-string">"2023-06-30"

class=class="hl-string">"hl-comment"># --- SuiteQL Query ---
class=class="hl-string">"hl-comment"># This query unions data from multiple transaction types to build a comprehensive event log.
class=class="hl-string">"hl-comment"># Each SELECT block corresponds to a specific inventory activity.
suiteql_query = fclass="hl-string">"""
SELECT
    InventoryNumber.inventoryNumber AS InventoryBatchLotNumber,
    'Goods Receipt Recorded' AS ActivityName,
    Transaction.trandate AS TransactionDate,
    Transaction.lastModifiedDate AS EndTime,
    BUILTIN.DF(Transaction.lastModifiedBy) AS User,
    BUILTIN.DF(TransactionLine.location) AS Location,
    NULL AS MovementReasonCode,
    (TransactionLine.quantity * TransactionLine.rate) AS StockValue,
    BUILTIN.DF(Transaction.type) AS TransactionType,
    BUILTIN.DF(TransactionLine.item) AS Item
FROM
    Transaction
    INNER JOIN TransactionLine ON Transaction.id = TransactionLine.transaction
    INNER JOIN InventoryAssignment ON Transaction.id = InventoryAssignment.transaction AND TransactionLine.id = InventoryAssignment.transactionline
    INNER JOIN InventoryNumber ON InventoryAssignment.inventorynumber = InventoryNumber.id
WHERE
    Transaction.type = 'ItemRcpt' AND Transaction.trandate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

-- Note: Quality Inspection is often a custom process. This is a placeholder.
-- You may need to join to a custom record or check a custom field on the InventoryNumber record.
SELECT
    InventoryNumber.inventoryNumber AS InventoryBatchLotNumber,
    'Quality Inspection Performed' AS ActivityName,
    InventoryNumber.lastModifiedDate AS TransactionDate, -- Using last modified date as event time
    InventoryNumber.lastModifiedDate AS EndTime,
    (SELECT employee.entityId FROM employee WHERE employee.id = InventoryNumber.lastModifiedBy) AS User,
    BUILTIN.DF(InventoryNumber.location) AS Location,
    'QA Completed' AS MovementReasonCode,
    NULL AS StockValue,
    'Custom QA Process' AS TransactionType,
    BUILTIN.DF(InventoryNumber.item) AS Item
FROM
    InventoryNumber
WHERE
    InventoryNumber.custitemnumber_qa_status = 'Approved' -- Placeholder: [Configure based on your system]
    AND InventoryNumber.lastModifiedDate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    InventoryNumber.inventoryNumber AS InventoryBatchLotNumber,
    'Put-away Completed' AS ActivityName,
    Transaction.trandate AS TransactionDate,
    Transaction.lastModifiedDate AS EndTime,
    BUILTIN.DF(Transaction.lastModifiedBy) AS User,
    BUILTIN.DF(TransactionLine.location) AS Location,
    'Put-away' AS MovementReasonCode,
    NULL AS StockValue,
    BUILTIN.DF(Transaction.type) AS TransactionType,
    BUILTIN.DF(TransactionLine.item) AS Item
FROM
    Transaction
    INNER JOIN TransactionLine ON Transaction.id = TransactionLine.transaction
    INNER JOIN InventoryAssignment ON Transaction.id = InventoryAssignment.transaction AND TransactionLine.id = InventoryAssignment.transactionline
    INNER JOIN InventoryNumber ON InventoryAssignment.inventorynumber = InventoryNumber.id
WHERE
    Transaction.type = 'BinTrnfr' AND TransactionLine.quantity > 0 AND Transaction.trandate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    InventoryNumber.inventoryNumber AS InventoryBatchLotNumber,
    'Stock Moved Internally' AS ActivityName,
    Transaction.trandate AS TransactionDate,
    Transaction.lastModifiedDate AS EndTime,
    BUILTIN.DF(Transaction.lastModifiedBy) AS User,
    BUILTIN.DF(TransactionLine.location) AS Location,
    Transaction.memo AS MovementReasonCode,
    NULL AS StockValue,
    BUILTIN.DF(Transaction.type) AS TransactionType,
    BUILTIN.DF(TransactionLine.item) AS Item
FROM
    Transaction
    INNER JOIN TransactionLine ON Transaction.id = TransactionLine.transaction
    INNER JOIN InventoryAssignment ON Transaction.id = InventoryAssignment.transaction AND TransactionLine.id = InventoryAssignment.transactionline
    INNER JOIN InventoryNumber ON InventoryAssignment.inventorynumber = InventoryNumber.id
WHERE
    Transaction.type IN ('InvTrnfr', 'BinTrnfr') AND Transaction.trandate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

-- Note: Capturing multiple events from InventoryCount record (creation, approval)
SELECT
    InventoryDetail.inventoryNumber AS InventoryBatchLotNumber,
    'Inventory Count Initiated' AS ActivityName,
    InventoryCount.tranDate AS TransactionDate,
    NULL AS EndTime,
    BUILTIN.DF(InventoryCount.createdBy) as User,
    BUILTIN.DF(InventoryCount.location) as Location,
    'Count Creation' as MovementReasonCode,
    NULL as StockValue,
    'InvCount' as TransactionType,
    BUILTIN.DF(InventoryDetail.item) as Item
FROM
    InventoryCount
    INNER JOIN TransactionLine InventoryDetail ON InventoryCount.id = InventoryDetail.transaction
WHERE
    InventoryCount.tranDate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    InventoryDetail.inventoryNumber AS InventoryBatchLotNumber,
    'Inventory Count Approved' AS ActivityName,
    NoteSystem.noteDate AS TransactionDate, -- Using system note date for accuracy
    NoteSystem.noteDate AS EndTime,
    NoteSystem.author AS User,
    BUILTIN.DF(InventoryCount.location) as Location,
    'Count Approval' as MovementReasonCode,
    NULL as StockValue,
    'InvCount' as TransactionType,
    BUILTIN.DF(InventoryDetail.item) as Item
FROM
    InventoryCount
    INNER JOIN TransactionLine InventoryDetail ON InventoryCount.id = InventoryDetail.transaction
    INNER JOIN SystemNote NoteSystem on InventoryCount.id = NoteSystem.recordId
WHERE
    NoteSystem.field = 'TRANSTATUS' AND NoteSystem.newvalue = 'Approved'
    AND NoteSystem.noteDate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    InventoryNumber.inventoryNumber AS InventoryBatchLotNumber,
    'Inventory Discrepancy Adjusted' AS ActivityName,
    Transaction.trandate AS TransactionDate,
    Transaction.lastModifiedDate AS EndTime,
    BUILTIN.DF(Transaction.lastModifiedBy) AS User,
    BUILTIN.DF(TransactionLine.location) AS Location,
    Transaction.memo AS MovementReasonCode,
    (TransactionLine.quantity * TransactionLine.rate) AS StockValue,
    BUILTIN.DF(Transaction.type) AS TransactionType,
    BUILTIN.DF(TransactionLine.item) AS Item
FROM
    Transaction
    INNER JOIN TransactionLine ON Transaction.id = TransactionLine.transaction
    INNER JOIN InventoryAssignment ON Transaction.id = InventoryAssignment.transaction AND TransactionLine.id = InventoryAssignment.transactionline
    INNER JOIN InventoryNumber ON InventoryAssignment.inventorynumber = InventoryNumber.id
WHERE
    Transaction.type = 'InvAdjst' AND Transaction.trandate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    InventoryNumber.inventoryNumber AS InventoryBatchLotNumber,
    'Goods Issue Recorded' AS ActivityName,
    Transaction.trandate AS TransactionDate,
    Transaction.actualShipDate AS EndTime,
    BUILTIN.DF(Transaction.lastModifiedBy) AS User,
    BUILTIN.DF(TransactionLine.location) AS Location,
    'Shipment' AS MovementReasonCode,
    (TransactionLine.quantity * TransactionLine.rate) AS StockValue,
    BUILTIN.DF(Transaction.type) AS TransactionType,
    BUILTIN.DF(TransactionLine.item) AS Item
FROM
    Transaction
    INNER JOIN TransactionLine ON Transaction.id = TransactionLine.transaction
    INNER JOIN InventoryAssignment ON Transaction.id = InventoryAssignment.transaction AND TransactionLine.id = InventoryAssignment.transactionline
    INNER JOIN InventoryNumber ON InventoryAssignment.inventorynumber = InventoryNumber.id
WHERE
    Transaction.type = 'ItemShip' AND Transaction.status = 'ItemShip:G' -- G = Shipped
    AND Transaction.trandate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    NULL AS InventoryBatchLotNumber, -- Batch not known at time of authorization
    'Return Authorization Created' AS ActivityName,
    Transaction.trandate AS TransactionDate,
    Transaction.lastModifiedDate AS EndTime,
    BUILTIN.DF(Transaction.createdby) AS User,
    BUILTIN.DF(TransactionLine.location) AS Location,
    Transaction.memo AS MovementReasonCode,
    (TransactionLine.quantity * TransactionLine.rate) AS StockValue,
    BUILTIN.DF(Transaction.type) AS TransactionType,
    BUILTIN.DF(TransactionLine.item) AS Item
FROM
    Transaction
    INNER JOIN TransactionLine ON Transaction.id = TransactionLine.transaction
WHERE
    Transaction.type = 'RtnAuth' AND Transaction.trandate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    InventoryNumber.inventoryNumber AS InventoryBatchLotNumber,
    'Return Goods Received' AS ActivityName,
    Transaction.trandate AS TransactionDate,
    Transaction.lastModifiedDate AS EndTime,
    BUILTIN.DF(Transaction.lastModifiedBy) AS User,
    BUILTIN.DF(TransactionLine.location) AS Location,
    'Return' AS MovementReasonCode,
    (TransactionLine.quantity * TransactionLine.rate) AS StockValue,
    BUILTIN.DF(Transaction.type) AS TransactionType,
    BUILTIN.DF(TransactionLine.item) AS Item
FROM
    Transaction
    INNER JOIN TransactionLine ON Transaction.id = TransactionLine.transaction
    INNER JOIN InventoryAssignment ON Transaction.id = InventoryAssignment.transaction AND TransactionLine.id = InventoryAssignment.transactionline
    INNER JOIN InventoryNumber ON InventoryAssignment.inventorynumber = InventoryNumber.id
WHERE
    Transaction.type = 'ItemRcpt' AND Transaction.createdFrom IS NOT NULL AND BUILTIN.DF(Transaction.createdFrom.type) = 'Return Authorization'
    AND Transaction.trandate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    InventoryNumber.inventoryNumber AS InventoryBatchLotNumber,
    'Stock Scrapped/Disposed' AS ActivityName,
    Transaction.trandate AS TransactionDate,
    Transaction.lastModifiedDate AS EndTime,
    BUILTIN.DF(Transaction.lastModifiedBy) AS User,
    BUILTIN.DF(TransactionLine.location) AS Location,
    'Scrap/Disposal' AS MovementReasonCode,
    (TransactionLine.quantity * TransactionLine.rate) AS StockValue,
    BUILTIN.DF(Transaction.type) AS TransactionType,
    BUILTIN.DF(TransactionLine.item) AS Item
FROM
    Transaction
    INNER JOIN TransactionLine ON Transaction.id = TransactionLine.transaction
    INNER JOIN InventoryAssignment ON Transaction.id = InventoryAssignment.transaction AND TransactionLine.id = InventoryAssignment.transactionline
    INNER JOIN InventoryNumber ON InventoryAssignment.inventorynumber = InventoryNumber.id
WHERE
    Transaction.type = 'InvAdjst' AND TransactionLine.quantity < 0 AND Transaction.account = [Your Scrap Account ID] -- Placeholder: [Configure based on your system]
    AND Transaction.trandate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

-- Note: Activities like Picking/Packing/Wave Creation often require the WMS module.
-- The following are common representations but may need adjustment for your specific configuration.

UNION ALL

SELECT
    NULL AS InventoryBatchLotNumber, -- Batch is assigned during picking, not wave creation
    'Picking Wave Created' as ActivityName,
    Wave.creationDate as TransactionDate,
    Wave.lastModifiedDate as EndTime,
    BUILTIN.DF(Wave.createdBy) as User,
    BUILTIN.DF(Wave.location) as Location,
    NULL as MovementReasonCode,
    NULL as StockValue,
    'Wave' as TransactionType,
    NULL as Item
FROM Wave
WHERE Wave.creationDate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

-- Picking/Packing are often status changes on the Item Fulfillment
SELECT
    InventoryNumber.inventoryNumber AS InventoryBatchLotNumber,
    'Picking Completed' as ActivityName,
    NoteSystem.noteDate as TransactionDate, -- Use system note date for status change time
    NoteSystem.noteDate as EndTime,
    NoteSystem.author AS User,
    BUILTIN.DF(TransactionLine.location) AS Location,
    'Picking for Shipment' as MovementReasonCode,
    NULL AS StockValue,
    BUILTIN.DF(Transaction.type) AS TransactionType,
    BUILTIN.DF(TransactionLine.item) AS Item
FROM
    Transaction
    INNER JOIN TransactionLine ON Transaction.id = TransactionLine.transaction
    INNER JOIN InventoryAssignment ON Transaction.id = InventoryAssignment.transaction AND TransactionLine.id = InventoryAssignment.transactionline
    INNER JOIN InventoryNumber ON InventoryAssignment.inventorynumber = InventoryNumber.id
    INNER JOIN SystemNote NoteSystem on Transaction.id = NoteSystem.recordId
WHERE
    Transaction.type = 'ItemShip' AND NoteSystem.field = 'TRANSTATUS' AND NoteSystem.newvalue = 'Packed'
    AND NoteSystem.noteDate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

UNION ALL

SELECT
    InventoryNumber.inventoryNumber AS InventoryBatchLotNumber,
    'Packing Completed' as ActivityName,
    NoteSystem.noteDate as TransactionDate, -- Use system note date for status change time
    NoteSystem.noteDate as EndTime,
    NoteSystem.author AS User,
    BUILTIN.DF(TransactionLine.location) AS Location,
    'Packing for Shipment' as MovementReasonCode,
    NULL AS StockValue,
    BUILTIN.DF(Transaction.type) AS TransactionType,
    BUILTIN.DF(TransactionLine.item) AS Item
FROM
    Transaction
    INNER JOIN TransactionLine ON Transaction.id = TransactionLine.transaction
    INNER JOIN InventoryAssignment ON Transaction.id = InventoryAssignment.transaction AND TransactionLine.id = InventoryAssignment.transactionline
    INNER JOIN InventoryNumber ON InventoryAssignment.inventorynumber = InventoryNumber.id
    INNER JOIN SystemNote NoteSystem on Transaction.id = NoteSystem.recordId
WHERE
    Transaction.type = 'ItemShip' AND NoteSystem.field = 'TRANSTATUS' AND NoteSystem.newvalue = 'Packed'
    AND NoteSystem.noteDate BETWEEN TO_DATE('{start_date}', 'YYYY-MM-DD') AND TO_DATE('{end_date}', 'YYYY-MM-DD')

-- Put-away Initiated is not included as Bin Putaway Worksheet is not available in standard SuiteQL at this time.

"""

class=class="hl-string">"hl-comment"># --- Main Script Logic ---
def main():
    class=class="hl-string">"hl-comment"># Construct the URL for the SuiteQL endpoint
    base_url = fclass="hl-string">"https://{ns_account_id.lower().replace('_', '-')}.suitetalk.api.netsuite.com"
    suiteql_url = fclass="hl-string">"{base_url}/services/rest/query/v1/suiteql"

    class=class="hl-string">"hl-comment"># Set up OAuth1 authentication
    auth = OAuth1(
        client_key=ns_consumer_key,
        client_secret=ns_consumer_secret,
        resource_owner_key=ns_token_id,
        resource_owner_secret=ns_token_secret,
        realm=ns_account_id
    )

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

    offset = 0
    limit = 1000  class=class="hl-string">"hl-comment"># NetSuite's max limit per page
    all_data = []

    print(class="hl-string">"Starting NetSuite data extraction...")

    while True:
        payload = json.dumps({
            class="hl-string">'q': suiteql_query,
            class="hl-string">'offset': offset,
            class="hl-string">'limit': limit
        })

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

            result = response.json()
            
            if class="hl-string">'items' in result and result[class="hl-string">'items']:
                all_data.extend(result[class="hl-string">'items'])
            
            has_more = result.get(class="hl-string">'hasMore', False)
            if not has_more:
                print(class="hl-string">"All pages have been fetched.")
                break
            
            offset += limit

        except requests.exceptions.HTTPError as e:
            print(fclass="hl-string">"HTTP Error: {e}")
            print(fclass="hl-string">"Response body: {response.text}")
            sys.exit(1)
        except Exception as e:
            print(fclass="hl-string">"An unexpected error occurred: {e}")
            sys.exit(1)

    if not all_data:
        print(class="hl-string">"No data found for the specified criteria.")
        return

    class=class="hl-string">"hl-comment"># Convert the list of dictionaries to a pandas DataFrame
    df = pd.DataFrame(all_data)

    class=class="hl-string">"hl-comment"># Save the DataFrame to a CSV file
    output_file = class="hl-string">'netsuite_inventory_event_log.csv'
    df.to_csv(output_file, index=False)
    print(fclass="hl-string">"Extraction complete. Data saved to {output_file}")

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