Reading Purchase Order Details
Learn how to retrieve complete purchase order information including line items, vendor details, approval status, and custom fields.
Prerequisites
- NetSuite connector configured with
suiteQLresource enabled - Purchase order ID or transaction ID
Getting Basic PO Information
Retrieve by Transaction ID
from abstra.connectors import run_connection_action
po_id = "123456" # Internal ID
result = run_connection_action(
"netsuite",
"query",
{
"query": f"""
SELECT *
FROM transaction
WHERE id = {po_id}
"""
}
)
po = result['data']['items'][0]
print(f"PO Number: {po['tranid']}")
print(f"Date: {po['trandate']}")
print(f"Status: {po['status']}")
print(f"Memo: {po['memo']}")
Retrieve by PO Number
po_number = "PO2024-001"
result = run_connection_action(
"netsuite",
"query",
{
"query": f"""
SELECT
t.id,
t.tranid,
t.trandate,
t.memo,
t.entity,
t.createdby,
t.createddate,
ts.name as status
FROM
transaction t
LEFT JOIN TransactionStatus ts ON t.status = ts.id
WHERE
t.tranid = '{po_number}'
AND t.type = 'PurchOrd'
"""
}
)
if result['data']['items']:
po = result['data']['items'][0]
print(f"Found PO: {po['tranid']}")
print(f"Internal ID: {po['id']}")
print(f"Status: {po['status']}")
else:
print(f"PO {po_number} not found")
Complete PO Details
Get All Fields
po_id = "123456"
result = run_connection_action(
"netsuite",
"query",
{
"query": f"""
SELECT *
FROM transaction
WHERE id = {po_id}
"""
}
)
po = result['data']['items'][0]
# Print all available fields
print("Complete Purchase Order Details:\n")
print(f"{'Field':<30} {'Value'}")
print("=" * 80)
for field, value in sorted(po.items()):
if value and str(value).strip():
# Format long values
value_str = str(value)
if len(value_str) > 50:
value_str = value_str[:47] + "..."
print(f"{field:<30} {value_str}")
Example Output:
Complete Purchase Order Details:
Field Value
================================================================================
approvalstatus 1
createdby 456
createddate 15/01/2025
currency 1
duedate 30/01/2025
email PROCUREMENT@EXAMPLECORP.COM
entity 98765
id 123456
memo Office Supplies Order - REQ-2025-001
status A
trandate 15/01/2025
tranid PO2024-001
type PurchOrd
Understanding Key Fields
Standard Transaction Fields
def parse_purchase_order(po_data):
"""
Parse and format purchase order data
"""
return {
# Identification
'internal_id': po_data['id'],
'po_number': po_data['tranid'],
'display_name': po_data.get('trandisplayname', ''),
# Dates
'created_date': po_data['createddate'],
'transaction_date': po_data['trandate'],
'due_date': po_data.get('duedate', ''),
'ship_date': po_data.get('shipdate', ''),
# Status
'status_code': po_data['status'],
'approval_status': po_data.get('approvalstatus', ''),
'billing_status': po_data.get('billingstatus', ''),
# Parties
'vendor_id': po_data['entity'],
'created_by': po_data['createdby'],
'employee': po_data.get('employee', ''),
# Financial
'currency': po_data.get('currency', ''),
'amount_unbilled': po_data.get('amountunbilled', ''),
'total_after_taxes': po_data.get('totalaftertaxes', ''),
# Other
'memo': po_data.get('memo', ''),
'email': po_data.get('email', ''),
'is_firmed': po_data.get('firmed', 'F') == 'T',
'to_be_emailed': po_data.get('tobeemailed', 'F') == 'T',
}
# Usage
po_id = "123456"
result = run_connection_action("netsuite", "query", {
"query": f"SELECT * FROM transaction WHERE id = {po_id}"
})
po_details = parse_purchase_order(result['data']['items'][0])
print(f"PO: {po_details['po_number']}")
print(f"Vendor ID: {po_details['vendor_id']}")
print(f"Total: {po_details['total_after_taxes']}")
print(f"Status: {po_details['approval_status']}")
Approval Status
Understanding Approval Status Codes
APPROVAL_STATUS = {
'1': 'Pending Approval',
'2': 'Approved',
'3': 'Rejected',
}
def get_approval_info(po_id):
"""
Get detailed approval status for a PO
"""
result = run_connection_action(
"netsuite",
"query",
{
"query": f"""
SELECT
id,
tranid,
approvalstatus,
nextapprover
FROM transaction
WHERE id = {po_id}
"""
}
)
po = result['data']['items'][0]
approval_code = po.get('approvalstatus', '')
approval_text = APPROVAL_STATUS.get(approval_code, 'Unknown')
return {
'po_number': po['tranid'],
'approval_status_code': approval_code,
'approval_status': approval_text,
'next_approver_id': po.get('nextapprover', ''),
'needs_approval': approval_code == '1',
}
# Usage
info = get_approval_info("123456")
print(f"PO {info['po_number']}: {info['approval_status']}")
if info['needs_approval']:
print(f" Next approver: {info['next_approver_id']}")
Custom Fields
Reading Custom Field Values
def get_custom_fields(po_id):
"""
Extract custom fields from a purchase order
"""
result = run_connection_action(
"netsuite",
"query",
{
"query": f"""
SELECT *
FROM transaction
WHERE id = {po_id}
"""
}
)
po = result['data']['items'][0]
# Extract all custom fields (start with 'custbody')
custom_fields = {
field: value
for field, value in po.items()
if field.startswith('custbody')
}
return custom_fields
# Usage
custom = get_custom_fields("123456")
print("Custom Fields:\n")
for field, value in custom.items():
if value and str(value).strip():
print(f" {field}: {value}")
Example Output:
Custom Fields:
custbody2: john.doe@example.com
custbody7: 3
custbody15: 789
custbody17: 1
custbody19: 100001
custbody_rey_ds_approvalstage: 2 Pending Technical Approval
custbodycustbody_rey_techapprove: 789
custbody_pd_observacao: REF-2025-001
custbody_pd_valor_aprovado: 25000
Common Custom Fields by Company
# Example: Company-specific custom fields
COMPANY_CUSTOM_FIELDS = {
'custbody2': 'Creator Email',
'custbody7': 'Department',
'custbody15': 'Approver ID',
'custbody17': 'Cost Center',
'custbody19': 'Project Code',
'custbody_rey_ds_approvalstage': 'Approval Stage Description',
'custbodycustbody_rey_techapprove': 'Technical Approver',
'custbody_pd_observacao': 'Observations',
'custbody_pd_valor_aprovado': 'Approved Amount',
}
def format_custom_fields(po_id):
"""
Format custom fields with friendly names
"""
custom = get_custom_fields(po_id)
print("Purchase Order Custom Information:\n")
for field, value in custom.items():
if value and str(value).strip():
field_name = COMPANY_CUSTOM_FIELDS.get(field, field)
print(f" {field_name}: {value}")
# Usage
format_custom_fields("123456")
Vendor Information
Get Vendor Details for PO
def get_po_with_vendor(po_id):
"""
Get PO information with vendor details
"""
result = run_connection_action(
"netsuite",
"query",
{
"query": f"""
SELECT
t.id,
t.tranid,
t.trandate,
t.entity as vendor_id,
v.companyname as vendor_name,
v.email as vendor_email,
v.phone as vendor_phone
FROM
transaction t
LEFT JOIN vendor v ON t.entity = v.id
WHERE
t.id = {po_id}
"""
}
)
return result['data']['items'][0] if result['data']['items'] else None
# Usage
po_info = get_po_with_vendor("123456")
if po_info:
print(f"PO: {po_info['tranid']}")
print(f"Vendor: {po_info['vendor_name']}")
print(f"Contact: {po_info['vendor_email']}")
Financial Details
Get Amount and Tax Information
def get_po_financial_details(po_id):
"""
Get financial breakdown of a purchase order
"""
result = run_connection_action(
"netsuite",
"query",
{
"query": f"""
SELECT
tranid,
amountunbilled,
foreigntotal,
totalaftertaxes,
basetotalaftertaxes,
currency,
exchangerate
FROM transaction
WHERE id = {po_id}
"""
}
)
po = result['data']['items'][0]
return {
'po_number': po['tranid'],
'amount_unbilled': float(po.get('amountunbilled', 0)),
'foreign_total': float(po.get('foreigntotal', 0)),
'total_after_taxes': float(po.get('totalaftertaxes', 0)),
'base_total': float(po.get('basetotalaftertaxes', 0)),
'currency': po.get('currency', ''),
'exchange_rate': float(po.get('exchangerate', 1)),
}
# Usage
financial = get_po_financial_details("123456")
print(f"PO {financial['po_number']} - Financial Summary:")
print(f" Total After Taxes: R$ {abs(financial['total_after_taxes']):,.2f}")
print(f" Amount Unbilled: R$ {abs(financial['amount_unbilled']):,.2f}")
print(f" Currency: {financial['currency']}")
Addresses
Get Billing and Shipping Addresses
def get_po_addresses(po_id):
"""
Get address information for a PO
"""
result = run_connection_action(
"netsuite",
"query",
{
"query": f"""
SELECT
tranid,
billingaddress,
shippingaddress
FROM transaction
WHERE id = {po_id}
"""
}
)
po = result['data']['items'][0]
return {
'po_number': po['tranid'],
'billing_address_id': po.get('billingaddress', ''),
'shipping_address_id': po.get('shippingaddress', ''),
}
# Usage
addresses = get_po_addresses("123456")
print(f"PO {addresses['po_number']}")
print(f" Billing Address ID: {addresses['billing_address_id']}")
print(f" Shipping Address ID: {addresses['shipping_address_id']}")
Complete Helper Function
All-in-One PO Details Retriever
def get_complete_po_details(po_number):
"""
Get comprehensive purchase order information
"""
# 1. Get PO with vendor and status
result = run_connection_action(
"netsuite",
"query",
{
"query": f"""
SELECT
t.*,
ts.name as status_name,
v.companyname as vendor_name,
v.email as vendor_email
FROM
transaction t
LEFT JOIN TransactionStatus ts ON t.status = ts.id
LEFT JOIN vendor v ON t.entity = v.id
WHERE
t.tranid = '{po_number}'
AND t.type = 'PurchOrd'
"""
}
)
if not result['data']['items']:
return None
po = result['data']['items'][0]
# 2. Parse and organize data
details = {
'header': {
'internal_id': po['id'],
'po_number': po['tranid'],
'display_name': po.get('trandisplayname', ''),
'created_date': po['createddate'],
'transaction_date': po['trandate'],
'due_date': po.get('duedate', ''),
},
'vendor': {
'id': po['entity'],
'name': po.get('vendor_name', ''),
'email': po.get('vendor_email', ''),
},
'status': {
'code': po['status'],
'name': po.get('status_name', ''),
'approval_status': po.get('approvalstatus', ''),
'billing_status': po.get('billingstatus', ''),
},
'financial': {
'total_after_taxes': po.get('totalaftertaxes', ''),
'amount_unbilled': po.get('amountunbilled', ''),
'currency': po.get('currency', ''),
},
'details': {
'memo': po.get('memo', ''),
'created_by': po.get('createdby', ''),
'employee': po.get('employee', ''),
},
'custom_fields': {
k: v for k, v in po.items()
if k.startswith('custbody') and v and str(v).strip()
}
}
return details
# Usage
po_details = get_complete_po_details("PO2024-001")
if po_details:
print(f"\n{'='*60}")
print(f"Purchase Order: {po_details['header']['po_number']}")
print(f"{'='*60}\n")
print("HEADER INFORMATION")
for key, value in po_details['header'].items():
print(f" {key.replace('_', ' ').title()}: {value}")
print("\nVENDOR INFORMATION")
for key, value in po_details['vendor'].items():
print(f" {key.replace('_', ' ').title()}: {value}")
print("\nSTATUS")
for key, value in po_details['status'].items():
print(f" {key.replace('_', ' ').title()}: {value}")
print("\nFINANCIAL")
for key, value in po_details['financial'].items():
print(f" {key.replace('_', ' ').title()}: {value}")
print("\nCUSTOM FIELDS")
for key, value in po_details['custom_fields'].items():
print(f" {key}: {value}")
else:
print("PO not found")
Export to Different Formats
Export to JSON
import json
po_details = get_complete_po_details("PO2024-001")
# Save to file
with open(f"po_{po_details['header']['po_number']}.json", 'w') as f:
json.dump(po_details, f, indent=2)
print(f"Exported to po_{po_details['header']['po_number']}.json")
Export to CSV
import csv
def export_pos_to_csv(po_numbers, filename='purchase_orders.csv'):
"""
Export multiple POs to CSV
"""
with open(filename, 'w', newline='') as f:
writer = csv.writer(f)
# Header
writer.writerow([
'PO Number', 'Date', 'Vendor', 'Status',
'Total', 'Created By', 'Memo'
])
# Data
for po_num in po_numbers:
details = get_complete_po_details(po_num)
if details:
writer.writerow([
details['header']['po_number'],
details['header']['transaction_date'],
details['vendor']['name'],
details['status']['name'],
details['financial']['total_after_taxes'],
details['details']['created_by'],
details['details']['memo'][:50]
])
print(f"Exported {len(po_numbers)} POs to {filename}")
# Usage
export_pos_to_csv(['PO2024-001', 'PO2024-002', 'PO2024-003'])
Best Practices
Cache Frequently Used Data
# ✅ Cache vendor information
vendor_cache = {}
def get_vendor_name(vendor_id):
if vendor_id not in vendor_cache:
result = run_connection_action("netsuite", "query", {
"query": f"SELECT companyname FROM vendor WHERE id = {vendor_id}"
})
vendor_cache[vendor_id] = result['data']['items'][0]['companyname']
return vendor_cache[vendor_id]
Handle Missing Fields Gracefully
# ✅ Use .get() with defaults
po.get('memo', 'No description')
po.get('duedate', 'Not specified')
# ✅ Check for None and empty strings
value = po.get('field', '')
if value and str(value).strip():
print(value)
Next Steps
- Searching Purchase Orders - Find POs with filters
- Filtering by Custom Fields - Query custom fields