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

Leave a Reply