Data from a particular Scenario can be exported from APS in the ways listed below. These exports are primarily used for creating custom reports and updating external systems. The Exports can be invoked from the Publish menu in the user interface, on a periodic basis using the Scheduling Agent, or even programmatically from a custom program by submitting a Transmission to APS requesting a Publish to occur.
APS Publish Tables Overview
This portion provides an overview of all SQL Tables that can be published and updated by the APS system. these tables are maintained in the SQL APS Publish database, which is available in each Version package’s Integration folder. Each table contains one or more IDs which can be used to join with other tables to create SQL Views or Stored Procedures. Every table also contains a “PublishDate”, which can be used if more than one Published data set is maintained within the SQL database.
In this section, the tables are organized into three categories: Resource, Inventory, and Job. Please note that this is done for ease of reading; tables can be joined together as long as they contain the same IDs.
Resource Tables
Plants: Lists all Plants in the system
- Linkable ID: Plant ID
Departments: A master list of Departments
- Linkable IDs: Plant ID, Department ID
Resources: Lists all Resources in the system
- Linkable IDs: Plant ID, Department ID, Resource ID
Capabilities: A master list of all Capabilities in the system, corresponds to Capability Mappings
- Linkable ID: Capability ID
Resource Capabilities: Lists Capability Assignments to Resources
- Linkable IDs: Plant ID, Department ID, Resource ID, Capability ID
Capacity Intervals: A master list of non-recurring Capacity Intervals
- Linkable ID: Capacity Interval ID
Capacity Interval Resource Assignments: Lists the assignment of non-recurring Capacity Intervals to various Resources
- Linkable IDs: Capacity Interval ID, Resource ID
Recurring Capacity Intervals: Lists all Recurring Capacity Intervals
- Linkable ID: Recurring Capacity Interval ID
Recurring Capacity Interval Recurrence: List each Recurring Capacity Interval’s recurrence
- Linkable ID: Recurring Capacity Interval ID
Recurring Capacity Interval Resource Assignments: Lists the Resource Assignments of Recurring Capacity Intervals
- Linkable IDs: Recurring Capacity Interval ID, Resource ID
Schedules: Lists all Scenarios maintained in the system. This table is standalone.
Inventory Tables
Items: A master list of all Items in the system
- Linkable ID: Item ID
Warehouses: Lists all Warehouses in the system
- Linkable ID: Warehouse ID
Plant Warehouses: Lists all Plant-Warehouse relations
- Linkable IDs: Plant ID, Warehouse ID
Inventories: A list of the on-hand inventory records in the system, corresponds to Inventory Mappings
- Linkable IDs: Item ID, Warehouse ID, Inventory ID
Forecasts: A list of Forecast headers, corresponds to Forecast Mappings
- Linkable IDs: Inventory ID, Forecast ID
Forecast Shipments: A lists of Forecast Shipments, corresponds to Forecast Shipment Mappings
- Linkable IDs: Forecast ID, Forecast Shipment ID
Forecast Shipment Inventory Adjustments: Lists the Forecast adjustment in Inventory
- Linkable IDs: Inventory ID, Forecast Shipment ID
Sales Orders: Lists all Sales Order Headers
- Linkable ID: Sales Order ID
Sales Order Lines: Lists Sales Order Lines
- Linkable IDs: Sales Order ID, Sales Order Line ID
Sales Order Line Distribution: Lists Sales Order Line Distributions
- Linkable IDs: Sales Order Line ID, Sales Order Line Distribution ID
Sales Order Distribution Inventory Adjustments: Lists all Inventory Adjustments made by Sales Orders
- Linkable IDs: Inventory ID, Sales Order Distribution ID
Purchase To Stock: Lists all Purchase Orders in the system
- Linkable IDs: Purchase To Stock ID, Inventory ID
Purchase To Stock Deleted Demands: Lists all Purchase Orders whose demand has been deleted
- Linkable IDs: Purchase To Stock ID
Purchase To Stock Forecast Demands: Lists all Purchase Orders whose demand is derived from Forecast Orders
- Linkable IDs: Purchase To Stock ID, Forecast Shipment ID
Purchase To Stock Inventory Adjustments: Lists all Inventory Adjustments made by Purchase Orders
- Linkable IDs: Inventory ID, Purchase To Stock ID
Purchase To Stock Safety Stock Demands: Lists all Purchase Orders whose demand is derived from Safety Stock demands
- Linkable IDs: Purchase To Stock ID, Inventory ID
Purchase To Stock Sales Order Demands: Lists all Purchase Orders whose demand is derived from Sales Orders
- Linkable IDs: Purchase To Stock ID, Sales Order Distribution ID
Purchase To Stock Transfer Order Demands: Lists all Purchase Orders whose demand is derived from Transfer Orders
- Linkable IDs: Purchase To Stock ID, Transfer Order Distribution ID
Transfer Orders: Lists all Transfer Order Headers
- Linkable ID: Transfer Order ID
Transfer Order Distributions: Lists Transfer Order Distributions
- Linkable IDs: Transfer Order ID, Transfer Order Distribution ID, Item ID, From Warehouse ID, To Warehouse ID
Transfer Order Distribution Inventory Adjustments: Lists all Inventory Adjustments made by Transfer Orders
- Linkable IDs: Inventory ID, Transfer Order Distribution ID
Jobs Tables
Jobs: Lists all Jobs in the system
- Linkable ID: Job ID
Manufacturing Orders: Lists all Manufacturing Orders in the system
- Linkable IDs: Job ID, Manufacturing Order ID
Job Paths: Lists all Alternate Paths for each Job Manufacturing Order
- Linkable IDs: Job ID, Manufacturing Order, Path ID
Job Path Nodes: Lists all Alternate Path Nodes for each Job Manufacturing Order
- Linkable IDs: Job ID, Manufacturing Order ID, Path ID, Predecessor Operation ID, Successor Operation ID
Job Successor Manufacturing Orders: Lists Job Manufacturing Orders with successor Manufacturing Orders specified
- Linkable IDs: Job ID, Manufacturing Order ID, Successor Job ID, Successor Manufacturing Order ID, Successor Path ID, Successor Operation ID
Job Operations: List all Job Operations in the system
- Linkable IDs: Job ID, Manufacturing Order ID, Operation ID
Job Resources: Lists Resource Requirement at the Job Operation level
- Linkable IDs: Job ID, Manufacturing Order ID, Operation ID, Resource Requirement ID
Job Resource Capabilities: Lists Capability Requirement at Job Operation’s Resource Requirement level
- Linkable IDs: Job ID, Manufacturing Order ID, Operation ID, Resource Requirement ID, Capability ID
Job Resource Blocks: Lists scheduled Job Activities and their scheduled Resources
- Linkable IDs: Job ID, Manufacturing Order ID, Operation ID, Activity ID, Block ID, Plant ID, Department ID, Resource ID, Resource Requirement ID
Job Resource Block Intervals: Lists scheduled Job Activities and their scheduled Capacity Intervals
- Linkable IDs: Job ID, Manufacturing Order ID, Operation ID, Activity ID, Block ID, Interval Index
Job Operation Attributes: Lists each Job Operation’s Attributes
- Linkable IDs: Job ID, Manufacturing Order ID, Operation ID, Name (Attribute Name)
Job Products: Lists all Products at the Job Operation level
- Linkable IDs: Job ID, Manufacturing Order ID, Operation ID, Product ID, Item ID, Warehouse ID
Job Product Deleted Demands: Lists Job Operation Products whose original demand has been deleted
- Linkable IDs: Job Operation ID, Job Product ID
Job Product Forecast Demands: Lists Job Operation Products whose original demand is derived from Forecast Order(s)
- Linkable IDs: Job Operation ID, Job Product ID, Forecast Shipment ID
Job Product Safety Stock Demands: Lists Job Operation Products whose original demand is derived from Safety Stock demand(s).
- Linkable IDs: Job Operation ID, Job Product ID, Inventory ID
Job Product Sales Order Demands: Lists Job Operation Products whose original demand is derived from Sales Order(s)
- Linkable IDs: Job Operation ID, Job Product ID, Sales Order Distribution ID
Job Product Transfer Order Demands: Lists Job Operation Products whose original demand is derived from Transfer Order(s)
- Linkable IDs: Job Operation ID, Job Product ID, Transfer Order Distribution ID
Job Materials: Lists Material Requirement(s) at the Job Operation level
- Linkable IDs: Job ID, Manufacturing Order ID, Operation ID, Material Requirement ID
Job Material Supplying Activities: Lists Activities whose materials are supplied from other Activities
- Linkable IDs: Job ID, Manufacturing Order ID, Operation ID, Material Requirement ID, Activity ID, Supplying Job ID, Supplying Manufacturing Order ID, Supplying Operation ID, Supplying Activity ID
Job Activities: Lists all Job Activities
- Linkable IDs: Job ID, Manufacturing Order ID, Operation ID, Activity ID
Job Activity Inventory Adjustments: Lists inventory adjustments per Job Activity
- Linkable IDs: Inventory ID, Activity ID