Cloud API: Business Intelligence

Various

Pharmacies may enable third parties to access data stored within Fred NXT databases. The data that can be accessed by third parties is grouped into requirement focus points.

Within the Business Intelligence focus point, information is provided across the following categories:

  • Pharmacy Identifiers
  • Scripts
  • Sales
  • Orders
  • Stock
  • Barcodes
  • Products
  • Category Hierarchies

Field Types

  • String - Characters including digits
  • Long - A whole number, larger range than an integer
  • Flag - True or False value
  • Hash String - Characters including digits which have been transformed into a fixed size alphanumeric string for the purpose of de-identifying original characters.
  • Int (Integer) - A whole number
  • Datetime - A date and time value
  • Decimal - A numeric value including decimal precision

Scripts

Field Grouping Field Format
Identifier - Pharmacy Store Number (Internal Number) String
Identifier - Pharmacy Approval Number String
Identifier - Script Script Id (Internal Number) Long
Identifier - Script Script Id Long
Identifier - Script Script Number String
Identifier - Script Modified Date Time Datetime
Worker Operator Id (Internal Number) String
Worker Operator Initials String
Patient Patient Id (Internal Number) String
Patient Patient Category String
Patient Medicare Number Hash String
Patient Is Valid Medicare Number Flag
Patient Repat Number Hash String
Patient Concession Number Hash String
Patient Patient Name Hash String
Patient Patient Suburb String
Patient Patient Postcode String
Patient Patient Birth Month and Year String
Patient Patient Gender String
Prescriber Prescriber Id (Internal Number) Long
Prescriber Prescriber Name Hash String
Prescriber Prescriber Number Hash String
Prescriber Prescriber Suburb String
Prescriber Prescriber Postcode String
Prescriber Prescriber Type String
Medication Item Id (Internal Number) String
Medication Drug Schedule String
Medication Pack Size Int
Medication Is S8 Report Flag
Medication Drug Code String
Medication Manufacturer Code String
Medication Drug Description String
Medication Drug Description Alternative String
Medication Generic Ingredient String
Medication Item Barcode String
Medication PBS Code String

Scripts (cont.)

Field Grouping Field Format
Script Script Class String
Script Script Status String
Script Directions String
Script Quantity Int
Script Maximum Dispense Quantity Int
Script Repeats Int
Script Repeat Number Int
Script Repeats Left Int
Script Fast Repeat Code String
Script Reg 24 Dispensing Flag
Script Immediate Supply Flag
Script Authority Number String
Script Is Owing Flag
Script Deferred Flag
Script Is Previously Deferred Flag
Script Is Authority Script Flag
Script Is PBS Flag
Script Generic Substitution Used Flag
Script Dispense Claim Category String
Script Prescribed Date Datetime
Script Dispense Date Datetime
Script Script Type String
Script Script Type Hot Key String
Script Original Script Number String
Script Orignal Pharmacy Number String
Price Patient Price Decimal
Price Government Estimated Rebate Decimal
Price CTG Co Payment Relief Decimal
Price Escript Incentive Decimal
Price Premium Free Incentive Decimal
Price Cost Decimal
Price Co Payment Discount Decimal
Price PRF Safety Net Contribution Decimal
Price Private Tax Decimal
Other Classification (Normal | Question Mark Dispense) String
Field Grouping Field Format
Script Script Class String
Script Script Status String
Script Directions String
Script Quantity Int
Script Maximum Dispense Quantity Int
Script Repeats Int
Script Repeat Number Int
Script Repeats Left Int

Sales

Field Grouping Field Format
Identifier - Pharmacy Store Number (Internal Number) String
Identifier - Sale Sale Date & Time DateTime
Identifier - Sale Sale Code String
Identifier - Sale Receipt Id String
Worker Clerk Code String
Sale Sale Type Code String
Sale Customer Code String
Sale Is Abandoned Flag
Item Sale Line Code String
Item Item Code String
Item Item Description String
Item Barcode String
Item Recommended Retail Price Decimal
Item Gross Margin Amount Decimal
Item Cost of Goods Sold Amount Decimal
Item Gross Sales Amount Decimal
Item GST Amount Decimal
Item Sales Amount Ex. GST Decimal
Item Discount Amount Ex. GST Decimal
Item Sale Quantity Decimal
Item Net Sales Amount Decimal
Item Government Recovery Amount Ex. GST Decimal
Script Is Script Flag
Script Script Number String

Orders

Field Grouping Field Format Comments
Store Store Number (Internal Number) String  
Order Order Id Long  
Order Order Date Datetime The date when the order is sent to the supplier
Order Supplier Id Long The internal ID for the supplier
Order Supplier Name String The name of the supplier
Order Order Status Code String  
Order Order Status String  
Order Order Type Code String  
Order Order Type String  
Invoice Invoice Number Long  
Invoice Invoice Date Datetime Date the invoice was raised
Invoice Receipt Date Datetime The date when the goods arrived
Item Order Line Id Long  
Item Order Number Code String  
Item Item Code String  
Item Item Description String  
Item Universal Code String  
Item Department Code Long Natural key for product department
Item Department Name String  
Item Category Code Long Natural key for product category
Item Category Name String  
Item PDE Code String  
Item Pack Size Int  
Item Outer Size Int  
Item Minimum Order Quantity Decimal Minimum order Qty for Suppler Item
Item GST Flag Flag Flag indicating if GST is payable for the item
Item SOH Quantity Decimal  
Item Pricing Cost Price Decimal  
Item Pricing Retail Price Decimal  
Item Order Order Quantity Decimal Number of units ordered
Item Order Order Price Decimal Unit price amount ex. GST from Order
Item Order Order Net Amount Decimal  
Item Invoice Invoice Quantity Decimal Number of units delivered
Item Invoice Invoice Price Decimal Unit price amount ex. GST from Invoice
Item Invoice Invoice Net Amount Decimal Cost amount charged by supplier
Item Invoice Invoice GST Amount Decimal GST on QTY Received
Item Invoice Invoice Gross Amount Decimal Net Invoice into Store Inc GST

Stock

Field Grouping Field Format Comments
Store Store Number (Internal Number) String Store unique identifier
Item Item Code String Stock code
Item Product Lookup Code String Product lookup code, alternate code for Stock
Item Unique Product Identifier Code String Unique product identifier across all stores
Item Barcode String Default Barcode
Item Description String Stock description
Item Department Code Long Natural key for product department
Item Department Name String  
Item Category Code Long Natural key for product category
Item Category Name String  
Item Stock On Hand Quantity Decimal At a point in time (daily). This value will reflect the last value extracted within a month.
Item Value Cost Price Decimal Cost Price per unit inc GST
Item Value Average Cost Price Decimal  
Item Value Retail Price Decimal Recommended Retail Price per unit. Includes GST.
Item Value Retail GST Amount Decimal GST payable on unit price inc GST and discounts
Item Value Markup Percentage Decimal  
Item Supplier Supplier Code String Default supplier code
Item Supplier Supplier Name String Default supplier name

Barcodes

Field Grouping Field Format
Identifier - Pharmacy Store Number (Internal Number) String
Identifier - Item Item Id String

Products

Field Grouping Field Format Comments
Identifier - Pharmacy Store Number (Internal Number) String Number or text to identify pharmacy
Identifier - Item Item Id String Item identifier for product
Item Brand    
Item Item Description    
Supplier Supplier Code    
Supplier Supplier Name    
Item Product Id    
Item Status    
Item Type    
Item Product Sub Type    
Item Purchase Unit    

Hierarchy

 Retail Product Hierarchy Parent    
Hierarchy Retail Product Hierarchy Path    
Item UOM    
Item Pack Size    
Item Product Modified Date Time    
Medication Drug Id    
Medication AMT Code    
Medication Manufacturer Code    
Medication Alternative Description    
Medication PBS Code    
Medication Fred Drug Code    
Medication Drug Schedule    
Medication AppCat Item Id    
Medication Active Ingredient    
Medication Generic Ingredient    
Medication Format    
Medication Format Short Code    
Medication Strength    
Medication Drug Modified Date Time    
Hierarchy Category Levels    
Item SOH Quantity    
Item SOH Modified Date Time    
Barcodes Active    
Barcodes Modified Date Time    
Barcodes Item Id    
Barcodes Barcode    
Barcodes Barcode Setup Id    
Item Length    
Item Height    
Item Width    
Item Weight    
Item Planogram Code    

Category Hierarchies

Field Grouping Field Format Comments
Identifier - Pharmacy Store Number (Internal Number) String Number or text to identify pharmacy
Identifier - Item Item Id String Item identifier for product
Category Type Id   Identifier for the type of hierarchy
Category Type Name   Name for the type of hierarchy
Category Name   Hierarchy name
Category Code    
Category Active    
Category Parent   Identifier of the parent hierarchy. Used to establish hierarchy structure.
Category Path Text   Hierarchy path using names
Category Path Id   Hierarchy path using identifiers
Category Level    
Category Created Date Time    
Category Modified Date Time    

Repeats for levels within levels.