291359
DHL C-TMS
Master Data Maintenance
FUNCTIONAL SPECIFICATION - 10.7
19/10/11 - 2.0
Reference: FS 291359-MS8KNBG7
Client Requirement
Master Data Paul McGoran/Manchester/UK/NFC
Solution
For Heavy Orders (and Ad-hoc orders.Etc.) C-TMS will keep a product master list. This product list will be maintained using a C-TMS maintenance screen and a CSV import function will be provided to allow the complete master list of products to be uploaded. Note that order received from DISC via EDI will not be validated against this master list. A new flag will be introduced against the customer record to allow customers to be marked as ‘validate products from master list’ or not. Each product code is categorised into a commodity. The existing fields in C-TMS ITEM_IDENTIFIER and PRODUCT_TYPE will be used to store the NR product code and commodity respectively. Note that the ITEM_IDENTIFER is a 20 character field so it is assumed that product codes no greater than 20 characters will be setup in C-TMS. PRODUCT_TYPE is a 12 character fields so it is assumed that a scheme of codes to describe commodities will be devised within this 12 character field.
It is understood that approximately 30 to 40 commodities exist covering many hundreds of product codes.
The product master list is required to allow Heavy orders to be validated at import from CSV format files.
Product Type (commodity) and Product Code:
Orders will be stored using the order header, order line and order items.
Note that the order line shows the product type as commodity. This is the 12 character field so a coding convention to cover all the commodity names will be devised at implementation. For example AGGBAG is a code that relates to Aggregate Bagged. The DU type will be used to define the Delivery Unit and can be for example BAGS or LOOSE or LENGTHS or PALLETS or whatever else is required.
The DU type can be setup to default from the commodity in Product Type through the master data setup, i.e. DU BAGS defaults from using product type AGGBAG.
The Identifier field will then refer to a maintained master product data list and be supported by lookup, and the input through EDI or CSV be validated against this list depending on the customer (this means non- heavy can be excluded and from validation against master product codes).
ote that the Identifier is the 20 character ITEM_IDENTIFER field which will cover the ‘structured’ records in the product list provided by Simon Allen.
The Product Item screen will be used to store the product master list and will provide the relationship between item (e.g. 0057/007569/0120) and product type (e.g. SHORTRAIL) and item description (e.g. RAIL SHORT, 60E2, ALPHATEK COATED, 18M, UNDRILLED).
A new cost centre based system parameter NWR_ORDER_VALIDATION will be created. Setting the value of this parameter to Y will control the order entry process. The orders form will be amended to check the value of the above parameter when adding order items. If this value has been set to Y the order of input of order items will be changed to enter the product type as the first value. The product will then be used to construct a list of values based on entries in the product master table for the product type in question. This will ensure that the correct values are assigned to the item identifier. Order items are linked to order lines by product type and du type combination, validation should therefore be performed when entering or amending order items that appropriate product type and du type exist at order line level.
The Blameworthy column requirement for reason codes will be accommodated from a recent development for BG Waste RIO AR-8KNVZ (OBS Ref 291172) so there is no incremental cost in this RIO for this feature.
The missing Latitude / Longitude from Locations extract will be accommodated from a recent development for BG Waste RIO AR-8KCC3R (OBS Ref 290898) so there is no incremental cost in this RIO for this feature.
This functional specification is provided assuming that the product codes will be maximum 20 characters. If this is insufficient the functional specification will need to be referred and reworked accordingly.
Scope
This change will be applied to system version 10.7
Set-up
Pre-requisites
None
Menu Structure
Unchanged
Data
A new column VALIDATE_PROD will be added to the ORG_CUSTOMER table if set this indicates that the products should be validated against the master product list.
A new cost centre based system parameter will be created ALTERNATE_ORDER_ENTRY when set this will determine the sequence in which order item information is captured.
The ITEM_IDENTIFIER column on the PRD_PRODUCT_ITEM and PRD_LOC_PRODUCT_ITEM tables will be increased from 12 to 20 characters and the
ITEM_DESCRIPTION will be increased from 35 to 122 characters.
Records will be inserted into the IMP_RECORD_FORMAT, IMP_IMPORT_TYPE and IMP_FIELD tables to accommodate the CSV import of Product Items.
Implementation Advice
The import Maintenance screen should be used to set up the import parameters for the CSV import an example import is shown below
A system super user will be required to grant access to the import functions an example is shown below.
A system super user will be required to ensure the system parameters have been set up correctly an example is shown below
Functional Description
Product item Maintenance
The PRD_PRODUCT_ITEM and PRD_LOC_PRODUCT_ITEM tables will have the ITEM_IDENTIFIER column increased from 12 to 20 characters. The ITEM_DESCRIPTION will also be increased from 35 to 122 characters on both tables. Product items can be displayed and captured using the PRODUCT form using the Product Item and Location Product Items tabs as shown below
Both tab pages will be amended to allow the display and capture of the larger fields any processing or validation associated with these fields should be amended accordingly to allow the larger field length.
There are five database packages which reference the amended tables and these packages will be changed to accommodate the new larger size field a list of package names is provided in Appendix B.
There are two oracle reports which reference the product item data and these will also be changed to accommodate the new larger field size details of the reports are provided in Appendix B.
Customer Data
A new column VALIDATE_PROD will be added to the ORG_CUSTOMER table this will then be displayed as a check box titled “Validate Products from Master List” on the main canvas of the CUST_COST form as shown below.
The default value for this field will be null. If the box is checked for a given customer the VALIDATE_PROD column will be set to “Y” this will indicate that order items for this customer should have the product and item identifier validated from the master list. If the box is unchecked this value should be set to “N”.
CSV Import
The IMP package will be amended to add a new function PROCESS_PRODUCT_ITEM. The import type and field values will be created to allow the import format to be defined. The format of the csv import file is as follows
| Import Field | Database field |
| PRODUCT | PRD_PRODUCT_ITEM.ITEM_ID |
| COMMODITY | PRD_PRODUCT_ITEM.PRODUCT_TYPE |
| WEIGHT | PRD_PRODUCT_ITEM.KG_WEIGHT |
| VOLUME | PRD_PRODUCT_ITEM.VOLUME_CUBIC_METRES |
| DESCRIPTION | PRD_PRODUCT_ITEM.ITEM_DESCRIPTION |
The data contained within the csv file must be validated to ensure that all commodities already exist in C-TMS the commodity must exist as a PROD_TYPE_ID in the PRD_PRODUCT_TYPE table if an invalid commodity is found the record will be rejected.
The item identifier is the primary key of the table and duplicate entries are not allowed, if any duplicates are found the record should be rejected.
The weight and volume fields should be numeric and the record should be rejected if non numeric data is found.
The item description should be no longer than 122 character’s any records exceeding this length should be rejected.
The PRD_PRODUCT_ITEMS table also contains CREATED_BY and CREATED_DATE columns these will be populated by a pre-existing trigger.
All rejected records will be displayed with an appropriate error message to allow the user to correct errors and re-import the data.
Manual Order Entry
A new cost centre based parameter will be created ALTERNATE_ORDER_ENTRY this parameter will control the sequence of entry for order items. The ORDERS form will be amended to check the value of the system parameter when the Create or Edit/Delete button is selected from the order items tab see below
[[Image:]]
If the parameter value is not set or set to “N” no change to the entry sequence or additional validation will be required and records should be processed in the existing manner. If the value of the parameter is set to “Y” then the entry sequence for order items will be amended to enter the product and du type information before the item identifier. A suggested layout is shown below.
[[Image:]]
Once the product/commodity has been selected from the drop down list the DU type should automatically be populated. The product type should then be used to construct a list of values for the item identifier field from the PRD_PRODUCT_ITEM table and a valid record must be selected from this list. Order items are connected to the order lines a combination of Product and du type and validation should be performed to ensure that no order items can be entered for a product and du type combination which does not already exist for this order. If invalid entries are found then an appropriate error message should be displayed. The item identifier must be unique within the OMS ref and therefore no duplicate identifiers are allowed within an order. If duplicates are entered an appropriate error will be displayed. All input errors should be corrected before the order items are committed.
Developer Note – This will be achieved by setting the x and y co-ordinates of the items and attaching an LOV to the item_identifier field based on the value of the system parameter at runtime. Using the system parameter as opposed to the customer flag will allow all types of orders (i.e. heavy non-heavy and Ad Hoc) to be validated in this way.
Order Import
Orders can be imported via a CSV or XML file and both processes will be amended to allow validation of order items.
For XML type records if the customer of the order has the VALIDATE_PROD column set to “Y” then item validation will be amended. The current validation checks the ITEM_IDENTIFIER against the RES_DESPATCH_UNIT_TYPE table, for records with the VALIDATE_PROD column set, this will be replaced with validation on the PRD_PRODUCT_ITEM table to ensure that the item already exists. If any errors are found the order should not be processed and an appropriate error message should be given. If the VALIDATE_PROD is not set or set to “N” the record should be processed in the normal manner.
For CSV type records if the customer of the order has the VALIDATE_PROD flag set to “Y” and additional check should be added to ensure that the item identifier already exists in the PRD_PRODUCT_ITEM table if not the record should not be loaded and an appropriate error message provided. If the VALIDATE_PROD is not set or set to “N” no additional checks are required and the record should be processed in the normal manner.
Developer Note – this will require a change to both the IMP (process_ti_order) and INT_XML_IN (process_ord_xml_in) packages
Reason codes
Reason codes to be flagged as blameworthy or not blameworthy for reporting OTIF. This functionality will be accommodated by a recent development for BG Waste RIO 291172 AR8KNVZ.
Latitude Longitude Report
The missing Latitude/Longitude from locations extract will be accommodated by a recent development for BG Waste RIO 290898 AR8KCC3R.
Table Updates Required
[[Image:]] [[Image:]]
[[Image:]] [[Image:]] [[Image:]]
Modules to be changed
| Module Name | Module Type | Notes |
| PRODUCT.fmb | Form | Changes to field sizes |
| CUST_COST.fmb | Form | Addition of validation flag |
| BKG.sql | Package | Changes to field sizes |
| HKP.sql | Package | Changes to field sizes |
| CSCS.sql | Package | Changes to field sizes |
| PRD.sql | Package | Changes to field sizes |
| VAST.sql | Package | Changes to field sizes |
| IMP.sql | Package | New product items import and changes to order items validation |
| ORDERS.fmb | Package | Changes to order item input |
| INT_XML_IN.sql | Package | Changes to order items validation |
References
| EST-291359 MS-8KNBG7 Master Data Maintenance | |||
Glossary
| C-TMS | Calidus TMS |
Document History
| Initial version | ||||
| Reviewed and Issued | ||||
| Revised after review by S Allen | ||||
| Reviewed and Issued | ||||
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
