ERP Scheduler: CFS Interface Support
Basic Steps for doing CFS Interface Support
Pre-Requisites
Make sure you have login privileges to the SQL server and SQL. (Refer to the Client connection page for this information)
Make sure you know where ERP Scheduler is installed and configured (Refer to ERP Scheduler: Setup and Configuration)
Make sure you have access to QT ODBC tool (Refer to ERP Scheduler: CFS Interface Setup)
Step-by-Step Guide
Connect to Client Database Server and locate ERP and External databases
In ERP database locate CFS staging tables
In ERP database locate CFS stored procedures
In External database locate configuration tables
Find Job where Client is having an issue
Find Job Steps for Client Issue
Refine Job Steps to Source and Destination Commands
Review ERP stored procedure CommandOrder = 1 Check Pending ERP Orders SourceCommand = spCFSGetORderHeaders
Review ODBC Command CommandOrder = 2 Get Batch # for Orders SourceCommand = Select PUB.BatchNumber.NEXTVAL as 'BatchNumber' from SysProgress.SysCalcTable
ERP CFS Staging tables notes
Not all CFS Staging tables are utilized
Each Client will have their own CFS Staging tables based on their business needs
For this client location only two CFS Staging tables are used
CFSOrderSend logs each order that has been sent to CFS Queue
CFSShippingOrder is every order with its items that were pulled from CFS to update ERP Customer Orders
CFSCatchWeights is the individual weights associated with items for orders that have catch weights that were pulled from CFS to update ERP Subitem table
CFS Interfaces tables notes
To view CFS Interfaces database tables use QT ODBC tool (Refer to ERP Scheduler: CFS Interface Setup)
Table Names of interfaces databases are fairly consistent for all clients however the data contained in the tables can vary.
Commands from Job Steps can be copy and pasted into QT ODBC tool to check results
Our Clients use the following tables
Batch this is used when creating new batches when sending data to CFS and finding batches for data coming from CFS
Customer is used when we send Customers to CFS. Since we assign a new batch each time a customer is sent to CFS we can query this table and see how many times a customer has been transmitted to CFS. Furthermore by joining the Customer to the Batch table we can see the date and time the Customer was sent to CFS.
Product is used when we send Items to CFS. Since we assign a new batch each time a product is sent to CFS we can query this table and see how many times a product has been transmitted to CFS. Furthermore by joining the Product to the Batch table we can see the date and time the Product was sent to CFS.
OrderHeader is used when we send OrderMaster data to CFS. Since we assign a new batch each time an Order is sent to CFS we can query this table and see how many times an Order has been transmitted to CFS. Furthermore by joining the OrderHeader to the Batch table we can see the date and time the Order was sent to CFS
OrderDeatil is used when we send OrderLine data to CFS. Since we assign a new batch each time an Order is sent to CFS we can query this table and see Products and Quantities for each time an Order was transmitted to CFS. Furthermore by joining the OrderDetail to the Batch table we can see the date and time the Order was sent to CFS
OrderStatus is used when CFS sets an order to PICKING. Picking orders can be locked in ERP. Not all clients lock orders for picking. A locked order in ERP has the OrderMaster WarehouseInterfaceStatusID set to locked (3).
ShippingOrderHeader is used when CFS sets an order to CONFIRMED. Confirmed Orders are ready to update ERP. We query Batch table for Confirmed Orders then copy this information from CFS to ERP staging table CFSShippingOrder. We can query this table for an Order and see each time an Order was confirmed in CFS.
ShippingOrderDetail holds the picked (BoxesShipped) quantities for CONFIRMED orders, this information is combined with ShippingOrderHeader and stored in the ERP CFSShippingOrder table.
ShippingOrderDetailWeght holds the individual catch weights for a product code in the ShippingOrderDetail for Confirmed Orders. This information is stored in the ERP CFSCatchWeights table.
Notes