Reading Purchase Order Line Items

Learn how to retrieve detailed line item information from NetSuite purchase orders, including item details, quantities, and custom line-level fields.

Prerequisites

NetSuite connector configured with rest resource enabled
Purchase orders with line items
Proper permissions for transaction and item access

Basic Line Item Retrieval

Get all line items for a specific purchase order:

from abstra.connectors import run_connection_action
import pandas as pd

def get_po_line_items(po_internal_id):
    """Get all line items for a purchase order."""
    
    result = run_connection_action(
        "netsuite",
        "suiteql",
        {
            "q": f"""
                SELECT 
                    pol.id as line_id,
                    pol.linenumber,
                    pol.item,
                    i.itemid as item_code,
                    i.displayname as item_name,
                    pol.quantity,
                    pol.rate,
                    pol.amount,
                    pol.quantityreceived,
                    pol.quantitybilled,
                    pol.isclosed,
                    pol.memo as line_memo
                FROM transactionline pol
                JOIN item i ON pol.item = i.id
                WHERE pol.transaction = {po_internal_id}
                AND pol.mainline = 'F'
                ORDER BY pol.linenumber
            """
        }
    )
    
    return pd.DataFrame(result['data']['items'])

# Get line items for PO
po_id = 12345
line_items = get_po_line_items(po_id)
print(f"Found {len(line_items)} line items for PO {po_id}")
print(line_items[['item_code', 'item_name', 'quantity', 'rate', 'amount']])

Detailed Line Item Analysis

Get comprehensive line item details with vendor and category information:

def get_detailed_line_items(po_internal_id):
    """Get detailed line item information including vendor and categories."""
    
    result = run_connection_action(
        "netsuite",
        "suiteql",
        {
            "q": f"""
                SELECT 
                    po.tranid as po_number,
                    ven.companyname as vendor_name,
                    pol.linenumber,
                    i.itemid as item_code,
                    i.displayname as item_name,
                    i.description as item_description,
                    ic.name as item_category,
                    iu.name as unit_of_measure,
                    pol.quantity,
                    pol.rate,
                    pol.amount,
                    pol.quantityreceived,
                    pol.quantitybilled,
                    (pol.quantity - COALESCE(pol.quantityreceived, 0)) as quantity_pending,
                    pol.isclosed,
                    pol.memo as line_memo,
                    -- Calculate percentages
                    ROUND((COALESCE(pol.quantityreceived, 0) / pol.quantity * 100), 2) as received_percentage,
                    ROUND((COALESCE(pol.quantitybilled, 0) / pol.quantity * 100), 2) as billed_percentage
                FROM transactionline pol
                JOIN transaction po ON pol.transaction = po.id
                JOIN vendor ven ON po.entity = ven.id
                JOIN item i ON pol.item = i.id
                LEFT JOIN itemcategory ic ON i.category = ic.id
                LEFT JOIN unitstype ut ON i.unitstype = ut.id
                LEFT JOIN unitsofmeasure iu ON ut.baseunit = iu.id
                WHERE pol.transaction = {po_internal_id}
                AND pol.mainline = 'F'
                ORDER BY pol.linenumber
            """
        }
    )
    
    return pd.DataFrame(result['data']['items'])

# Get detailed line items
detailed_items = get_detailed_line_items(po_id)
print("Detailed line item analysis:")
print(detailed_items[['item_code', 'quantity', 'received_percentage', 'billed_percentage']])

Line Items with Custom Fields

Retrieve line items with custom line-level fields:

def get_line_items_with_custom_fields(po_internal_id):
    """Get line items including custom fields."""
    
    result = run_connection_action(
        "netsuite",
        "suiteql",
        {
            "q": f"""
                SELECT 
                    pol.id as line_id,
                    pol.linenumber,
                    i.itemid as item_code,
                    i.displayname as item_name,
                    pol.quantity,
                    pol.rate,
                    pol.amount,
                    -- Custom line fields (adjust field IDs as needed)
                    cf1.value as project_code,
                    cf2.value as cost_center,
                    cf3.value as delivery_date,
                    cf4.value as special_instructions
                FROM transactionline pol
                JOIN item i ON pol.item = i.id
                LEFT JOIN transactionlinecustomfield cf1 ON pol.id = cf1.transactionline 
                    AND cf1.customfield = 'custcol_project'
                LEFT JOIN transactionlinecustomfield cf2 ON pol.id = cf2.transactionline 
                    AND cf2.customfield = 'custcol_costcenter'
                LEFT JOIN transactionlinecustomfield cf3 ON pol.id = cf3.transactionline 
                    AND cf3.customfield = 'custcol_delivery_date'
                LEFT JOIN transactionlinecustomfield cf4 ON pol.id = cf4.transactionline 
                    AND cf4.customfield = 'custcol_instructions'
                WHERE pol.transaction = {po_internal_id}
                AND pol.mainline = 'F'
                ORDER BY pol.linenumber
            """
        }
    )
    
    return pd.DataFrame(result['data']['items'])

# Get line items with custom fields
custom_items = get_line_items_with_custom_fields(po_id)

Bulk Line Item Analysis

Analyze line items across multiple purchase orders:

def analyze_bulk_line_items(date_from, date_to, vendor_ids=None):
    """Analyze line items across multiple POs."""
    
    vendor_filter = ""
    if vendor_ids:
        vendor_list = ','.join(map(str, vendor_ids))
        vendor_filter = f"AND po.entity IN ({vendor_list})"
    
    result = run_connection_action(
        "netsuite",
        "suiteql",
        {
            "q": f"""
                SELECT 
                    po.tranid as po_number,
                    po.trandate as po_date,
                    ven.companyname as vendor_name,
                    i.itemid as item_code,
                    i.displayname as item_name,
                    ic.name as item_category,
                    COUNT(pol.id) as line_count,
                    SUM(pol.quantity) as total_quantity,
                    AVG(pol.rate) as avg_rate,
                    SUM(pol.amount) as total_amount,
                    SUM(COALESCE(pol.quantityreceived, 0)) as total_received,
                    SUM(COALESCE(pol.quantitybilled, 0)) as total_billed
                FROM transactionline pol
                JOIN transaction po ON pol.transaction = po.id
                JOIN vendor ven ON po.entity = ven.id
                JOIN item i ON pol.item = i.id
                LEFT JOIN itemcategory ic ON i.category = ic.id
                WHERE po.type = 'PurchOrd'
                AND po.trandate BETWEEN '{date_from}' AND '{date_to}'
                AND pol.mainline = 'F'
                {vendor_filter}
                GROUP BY po.tranid, po.trandate, ven.companyname, i.itemid, i.displayname, ic.name
                ORDER BY po.trandate DESC, po.tranid, pol.linenumber
            """
        }
    )
    
    df = pd.DataFrame(result['data']['items'])
    
    # Calculate fulfillment rates
    df['received_rate'] = (df['total_received'] / df['total_quantity'] * 100).round(2)
    df['billed_rate'] = (df['total_billed'] / df['total_quantity'] * 100).round(2)
    
    return df

# Analyze last 3 months
bulk_analysis = analyze_bulk_line_items('2024-01-01', '2024-03-31')
print("Bulk line item analysis summary:")
print(bulk_analysis.groupby('item_category')['total_amount'].sum().sort_values(ascending=False))

Item Performance Analysis

Analyze item performance across purchase orders:

def analyze_item_performance():
    """Analyze item performance metrics."""
    
    result = run_connection_action(
        "netsuite",
        "suiteql",
        {
            "q": """
                SELECT 
                    i.itemid as item_code,
                    i.displayname as item_name,
                    ic.name as item_category,
                    COUNT(DISTINCT pol.transaction) as po_count,
                    COUNT(pol.id) as line_count,
                    SUM(pol.quantity) as total_quantity,
                    SUM(pol.amount) as total_amount,
                    AVG(pol.rate) as avg_rate,
                    MIN(pol.rate) as min_rate,
                    MAX(pol.rate) as max_rate,
                    COUNT(DISTINCT po.entity) as vendor_count,
                    -- Delivery performance
                    SUM(CASE WHEN pol.quantityreceived >= pol.quantity THEN 1 ELSE 0 END) as fully_received_lines,
                    SUM(CASE WHEN pol.isclosed = 'T' THEN 1 ELSE 0 END) as closed_lines
                FROM transactionline pol
                JOIN transaction po ON pol.transaction = po.id
                JOIN item i ON pol.item = i.id
                LEFT JOIN itemcategory ic ON i.category = ic.id
                WHERE po.type = 'PurchOrd'
                AND po.trandate >= ADD_MONTHS(CURRENT_DATE, -12)
                AND pol.mainline = 'F'
                GROUP BY i.itemid, i.displayname, ic.name
                HAVING COUNT(pol.id) >= 5  -- Items with at least 5 line entries
                ORDER BY total_amount DESC
            """
        }
    )
    
    df = pd.DataFrame(result['data']['items'])
    
    # Calculate performance metrics
    df['rate_variance'] = ((df['max_rate'] - df['min_rate']) / df['avg_rate'] * 100).round(2)
    df['fulfillment_rate'] = (df['fully_received_lines'] / df['line_count'] * 100).round(2)
    df['avg_po_amount'] = (df['total_amount'] / df['po_count']).round(2)
    
    return df

# Analyze item performance
item_performance = analyze_item_performance()
print("Top performing items by amount:")
print(item_performance[['item_code', 'item_name', 'total_amount', 'fulfillment_rate']].head(10))

Line Item Discrepancy Detection

Identify discrepancies in line items:

def detect_line_item_discrepancies():
    """Detect potential issues with line items."""
    
    result = run_connection_action(
        "netsuite",
        "suiteql",
        {
            "q": """
                SELECT 
                    po.tranid as po_number,
                    po.trandate as po_date,
                    ven.companyname as vendor_name,
                    pol.linenumber,
                    i.itemid as item_code,
                    pol.quantity,
                    pol.rate,
                    pol.amount,
                    pol.quantityreceived,
                    pol.quantitybilled,
                    -- Calculate expected amount
                    (pol.quantity * pol.rate) as expected_amount,
                    -- Flag discrepancies
                    CASE 
                        WHEN pol.amount != (pol.quantity * pol.rate) THEN 'Amount Mismatch'
                        WHEN pol.quantityreceived > pol.quantity THEN 'Over Received'
                        WHEN pol.quantitybilled > pol.quantity THEN 'Over Billed'
                        WHEN pol.quantitybilled > pol.quantityreceived THEN 'Billed Before Received'
                        ELSE 'OK'
                    END as discrepancy_type
                FROM transactionline pol
                JOIN transaction po ON pol.transaction = po.id
                JOIN vendor ven ON po.entity = ven.id
                JOIN item i ON pol.item = i.id
                WHERE po.type = 'PurchOrd'
                AND po.trandate >= ADD_MONTHS(CURRENT_DATE, -6)
                AND pol.mainline = 'F'
                AND (
                    pol.amount != (pol.quantity * pol.rate) OR
                    pol.quantityreceived > pol.quantity OR
                    pol.quantitybilled > pol.quantity OR
                    pol.quantitybilled > pol.quantityreceived
                )
                ORDER BY po.trandate DESC
            """
        }
    )
    
    df = pd.DataFrame(result['data']['items'])
    
    # Summary of discrepancies
    discrepancy_summary = df['discrepancy_type'].value_counts()
    
    return df, discrepancy_summary

# Detect discrepancies
discrepancies, summary = detect_line_item_discrepancies()
print("Discrepancy Summary:")
print(summary)

if len(discrepancies) > 0:
    print("\nSample discrepancies:")
    print(discrepancies[['po_number', 'item_code', 'discrepancy_type']].head())

Line Item Receipt Tracking

Track receipt status of line items:

def track_receipt_status(days_back=30):
    """Track receipt status of recent line items."""
    
    result = run_connection_action(
        "netsuite",
        "suiteql",
        {
            "q": f"""
                SELECT 
                    po.tranid as po_number,
                    po.trandate as po_date,
                    po.duedate as due_date,
                    ven.companyname as vendor_name,
                    pol.linenumber,
                    i.itemid as item_code,
                    i.displayname as item_name,
                    pol.quantity as ordered_qty,
                    COALESCE(pol.quantityreceived, 0) as received_qty,
                    (pol.quantity - COALESCE(pol.quantityreceived, 0)) as pending_qty,
                    pol.isclosed,
                    -- Days since PO date
                    (CURRENT_DATE - po.trandate) as days_since_po,
                    -- Days past due
                    CASE 
                        WHEN po.duedate < CURRENT_DATE AND pol.quantityreceived < pol.quantity 
                        THEN (CURRENT_DATE - po.duedate)
                        ELSE 0 
                    END as days_overdue,
                    -- Status classification
                    CASE 
                        WHEN pol.isclosed = 'T' THEN 'Closed'
                        WHEN pol.quantityreceived >= pol.quantity THEN 'Fully Received'
                        WHEN pol.quantityreceived > 0 THEN 'Partially Received'
                        WHEN po.duedate < CURRENT_DATE THEN 'Overdue'
                        ELSE 'Pending'
                    END as receipt_status
                FROM transactionline pol
                JOIN transaction po ON pol.transaction = po.id
                JOIN vendor ven ON po.entity = ven.id
                JOIN item i ON pol.item = i.id
                WHERE po.type = 'PurchOrd'
                AND po.trandate >= CURRENT_DATE - {days_back}
                AND pol.mainline = 'F'
                ORDER BY days_overdue DESC, po.trandate DESC
            """
        }
    )
    
    df = pd.DataFrame(result['data']['items'])
    
    # Receipt status summary
    status_summary = df['receipt_status'].value_counts()
    
    return df, status_summary

# Track receipt status
receipt_tracking, status_summary = track_receipt_status(30)
print("Receipt Status Summary:")
print(status_summary)

# Show overdue items
overdue_items = receipt_tracking[receipt_tracking['days_overdue'] > 0]
if len(overdue_items) > 0:
    print(f"\n{len(overdue_items)} overdue line items found")
    print(overdue_items[['po_number', 'item_code', 'pending_qty', 'days_overdue']].head())

Export Line Item Reports

Export line item data for external analysis:

def export_line_item_reports(po_internal_id=None):
    """Export comprehensive line item reports."""
    
    from datetime import datetime
    
    # Get all relevant line item data
    if po_internal_id:
        detailed_items = get_detailed_line_items(po_internal_id)
        custom_items = get_line_items_with_custom_fields(po_internal_id)
        filename_suffix = f"po_{po_internal_id}"
    else:
        # Get data for recent POs
        date_from = (datetime.now() - timedelta(days=90)).strftime('%Y-%m-%d')
        date_to = datetime.now().strftime('%Y-%m-%d')
        detailed_items = analyze_bulk_line_items(date_from, date_to)
        discrepancies, _ = detect_line_item_discrepancies()
        receipt_tracking, _ = track_receipt_status(90)
        filename_suffix = "bulk_analysis"
    
    # Export to Excel
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    filename = f'line_item_analysis_{filename_suffix}_{timestamp}.xlsx'
    
    with pd.ExcelWriter(filename) as writer:
        if po_internal_id:
            detailed_items.to_excel(writer, sheet_name='Line_Items', index=False)
            custom_items.to_excel(writer, sheet_name='Custom_Fields', index=False)
        else:
            detailed_items.to_excel(writer, sheet_name='Bulk_Analysis', index=False)
            discrepancies.to_excel(writer, sheet_name='Discrepancies', index=False)
            receipt_tracking.to_excel(writer, sheet_name='Receipt_Tracking', index=False)
    
    print(f"Line item reports exported to: {filename}")

# Export reports
export_line_item_reports()  # Bulk analysis
# export_line_item_reports(12345)  # Specific PO

Best Practices

Performance Tips

Use specific filters: Always filter by date range or specific POs
Limit result sets: Use LIMIT clauses for large datasets
Index on custom fields: Ensure custom fields are properly indexed
Join optimization: Only join necessary tables

Error Handling

def safe_line_item_query(query_function, *args, **kwargs):
    """Execute line item queries with error handling."""
    try:
        return query_function(*args, **kwargs)
    except Exception as e:
        print(f"Error in line item query: {e}")
        if "timeout" in str(e).lower():
            print("Query timed out. Try reducing date range or adding more filters.")
        return pd.DataFrame()

Troubleshooting

Common Issues

No line items returned:
- Check that mainline = 'F' filter is applied
- Verify PO has line items (not just header)
- Check permissions for transaction line access
Custom fields not appearing:
- Verify custom field internal IDs
- Check field permissions and availability
- Ensure fields are applied to transaction lines
Performance issues:
- Add date filters to queries
- Use pagination for large result sets
- Consider using REST API for simple lookups

Next Steps

Creating Custom Reports - Build comprehensive PO reports

Prerequisites​

Basic Line Item Retrieval​

Detailed Line Item Analysis​

Line Items with Custom Fields​

Bulk Line Item Analysis​

Item Performance Analysis​

Line Item Discrepancy Detection​

Line Item Receipt Tracking​

Export Line Item Reports​

Best Practices​

Performance Tips​

Error Handling​

Troubleshooting​

Common Issues​

Next Steps​

Resources​