View source for 266451

=266451 - LH-7T6D8B / Enhance ETA Notification to Stores=

Copyright OBS Logistics © 2010

The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied


=FUNCTIONAL OVERVIEW=

==Client Requirement==
See attachment "MTSMail_Coding_LH - 7T6D8B" for details on change request; See attachment "MTSMailExample_V01_LH - 7T6D8B" for an example on how the finished article is expected to look / perform.; The example is for a store receiving 4 orders, one of which is of type collection (With appropriate sections in bold and paragraph spacing, etc
Amend current ETA E-mail functionality which sends a message for each order to send a message for each delivery instead, with some message detail enhancements.


==Solution==
Currently the procedure F_GET_MSG_DATA within the package MSG_PROCESSING has a cursor at order level (oms_ref) called c_del_loc. This cursor will be changed to be at delivery location level (sched name, trip id, stop no) with an accumulation of the cube value for all of the orders being delivered at this stop on this trip.
The procedure then calls another procedure called p_loc_ord_del within the package MSG_CONSTRUCTOR that produces the e-mail in the required format.  This process will be changed to format the e-mail in the new required layout. Please see document attached to RIO for specific changes. 
NB) OMS_REF will no longer be passed between the procedures or displayed in the e-mail but an additional value for departure date will be passed instead (needed to calculate unloading time).
Code will be added to retrieve any collection orders (down to line level in order to determine product type of ‘COLLECT’) at the same location on the trip (it may not be the same stop as we may have a DL and a PK) in order to determine the correct returns message to add to the e-mail.
A new table will be created to store the conversion factor for From location and product type. The table will contain "from loc"; "product type" "ConversionFactor". This will be used to calculate the Estimated number of pallets that is displayed on the report. 
To allow this information to be viewed or edited a new tab will be required on the Resources screen. This will contain the From Location, Product Type and conversion factor. Access to this tab can be restricted if required. 
A new import will be added to allow this information to be imported from a CSV. 

==Scope==

These changes will be applied to system version 10.6 on DSGTST and once approved DSGPRD. 

The following high level changes will be made:

*Create new table to store pallet conversion values at location level
*Create Maintenance screen for new table
*Create new import format for new table
*Modify Message generation to match requirements.

==Data==
The new functionality around the message body of event type LOC_ORD_DEL will be parameterised. This is to ensure it will not affect any clients who currently also use these messages. Therefore, the construction of the message body will be determined by the value held in parameter MSG_LOC_ORD_DEL_BY_DELIVERY. If this is set to ‘N’ then the message body will be constructed as normal. If this is set to ‘Y’ the message body will be constructed as set out in this RIO. This parameter will be set up in table ADM_SYSTEM_PARAM as below:

INSERT INTO ADM_SYSTEM_PARAM (param_name, value, data_type, max_length, displayed, user_modifiable, description, config_by, config_by_value)
VALUES ('MSG_LOC_ORD_DEL_BY_DELIVERY','Y','S',3,'Y','Y',
'Set to Y if message body for event LOC_ORD_DEL is to show delivery details',
'SYSTEM','NONE');

A new table will be created, as requested, to store the conversion factor of a product against a location. This will be created as detailed in ‘Appendix A Table Updates’. A record will be defined as unique by fields LOCATION_ID and PRODUCT_TYPE together. Data can be entered or edited on this table via a new tab on form RESOURCE, and also by a csv import which is detailed later. This new tab will only be available when promoted to a user group via the Access Control form. The data required to set up this tab info so that it appears for promoting is as follows:

INSERT INTO ADM_FORM_TAB (form_name, tab_name, description)
VALUES ('RESOURCE','RES_PROD_CONV','CONVERSION FACTOR OF A PRODUCT AGAINST A LOCATION');

Data is required for the new import functionality around importing Location Product Conversion Factors. The following data needs inserted into the relevant table to allow this new import type which we will call LOC_PROD_FACTOR:

INSERT INTO IMP_IMPORT_TYPE (import_type)
VALUES ('LOC_PROD_FACTOR');

These will be the available fields that can be configured for this import:
INSERT INTO IMP_FIELD (FIELD_NAME, IMPORT_TYPE)
VALUES ('LOCATION_ID', 'LOC_PROD_FACTOR');
INSERT INTO IMP_FIELD (FIELD_NAME, IMPORT_TYPE)
VALUES ('PRODUCT_TYPE_ID', 'LOC_PROD_FACTOR');
INSERT INTO IMP_FIELD (FIELD_NAME, IMPORT_TYPE)
VALUES ('CONVERSION_FACTOR', 'LOC_PROD_FACTOR');

=FUNCTIONAL DESCRIPTION=

==Resource Form==

Below is a sample of how the new tab ‘Loc Prod Conv’ on the RESOURCE screen could look like. In here users will be able to insert or edit new records and specify a conversion factor to 5 decimal places.

[[Image:266451_1.png]]

When inserting new records a check will be done to ensure the Location Id and Product Type entered are valid. This will be achieved via a valid list of acceptable values on the field. Duplicates records on Location Id and Product Type will not be allowed. 

==IMPORT_MAINT Form==

It has been requested that Location Product Conversions Factors are to be imported into the new table via a new import type, or updated if they already exist. For this to happen, a new Import Type will have to bet up. This will be called LOC_PROD_FACTOR and will have configurable fields called LOCATION_ID, PRODUCT_TYPE_ID and CONVERSION_FACTOR. These fields will available for adding into the layout section of the IMPORT_MAINT screen. Users can specify in which position they want the fields to appear. The csv that they import via the IMPORT_EXEC screen must match this layout. Below is a sample of the fields that will be available for the new import type.

[[Image:266451_2.png]]

==Sample Message==

Below is the sample message taken from the ‘MTSMail_Coding_LH - 7T6D8B.doc ‘ document attached to the RIO request. The layout will be as close to the sample provided:

[[Image:266451_3.png]]

==Package MSG_CONTRSUCTOR==

Package MSG_CONSTRUCTOR needs function p_loc_ord_del updated to build the body depending on whether the new functionality is enabled or not. For the new functionality, the following details required in the body of the message will have to be calculated within this section:

*Depart time for the stop_id passed in so that the allocated unloading time can be calculated when compared to arrive time
*Relevant return message required depending on whether a collection is planned
*Drill down to order level for the delivery stop so that Pallet Estimates can be calculated. This includes;
**Decoding the From Location of the order
**Retrieving the product type of the order
**Retrieving the cube value of the order
**Retrieving the conversion factor for the order by querying the new RES_LOC_PRD_CONVERSION table for the from location and product type of the order
**Calculating the Pallet Estimate using the cube value and the conversion factor
**Calculating totals for Cube and Pallet Estimate

==PACKAGE IMP==

===Import_Server_File===

This function will have to be updated to handle files imported via the IMPORT_EXEC screen of type LOC_PROD_FACTOR. This function imports the file onto the server and will call a new function to process the contents of the file.

===Process_Loc_Prod_Factor===
The following code will be used to process the data held in the imported file. It will check that a value is held for the 3 fields in the field, and will error if any are black. It will also check that the values passed in exist and are valid in MTS, and will error if they do not. Finally, if the data passed in already exist in table RES_LOC_PROD_FACTOR, the Conversion Factor will be updated with the passed in value. If it doesn’t exist, a new record will be inserted. 

==Triggers==

A trigger will be required on new table RES_LOC_PRD_CONVERSION to insert and update the created and updated date and user. This will be as follows:
=REFERENCES=

{| Border="1"	
| <center>'''Ref No'''</center>
| <center>'''Document Title & ID'''</center>
| <center>'''Version'''</center>
| <center>'''Date'''</center>

|-
| <center>1</center>
| MTSMailExample_V01_LH - 7T6D8B.doc
| <center>N/A</center>
| <center>N/A</center>

|-
| <center>2</center>
| MTSMail_Coding_LH - 7T6D8B.doc
| <center>N/A</center>
| <center>N/A</center>

|-
| <center>3</center>
| EST-266451 LH-7T6D8B Enhance ETA Notification to Stores v1.0.doc
| <center>1.0</center>
| <center>30/06/09</center>
|}

=DOCUMENT HISTORY=

{| Border="1"	
| <center>'''Version'''</center>
| <center>'''Date'''</center>
| <center>'''Status'''</center>
| <center>'''Reason'''</center>
| <center>'''Initials'''</center>

|-
| <center>1a</center>
| <center>12/01/09</center>
| <center>Draft</center>
| Initial version
| <center>PDR</center>

|-
| <center>1</center>
| <center>12/01/09</center>
| <center>Issue</center>
| Reviewed and Issued
| <center>MJC</center>
|}


=AUTHORISED BY=



{| Border="1"	
| '''''Matt Crisford'''''
| Development Manager
| 

|-
| '''''Peter Greer'''''
| TMSCC MTS Product Manager 
| 

|}

Back to [[CONPRD Release - 24/02/2010]]