265413
265413 - NW-7S7FHR / Add Slot Flag
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
Add an additional flag to Slots and create a new DB job
Slots need to be recognised as either being Core or Additional for M&S, therefore two checkboxes should be added to the Slots form denoting either Core or Additional. Only one of these should be able to be checked at any one time and one should always be populated i.e. the Slot must be either Core or Additional. As part of this RIO this flag should also be added to the Slot Import format.
A new database job is also required to run overnight to set all Additional Slots that are Active to Inactive. The time that this job is run should be able to be changed via a P5 EARS call if required.
Project requirement for MTS as part of the re-deployment to M&S.
N.B. it has been requested to not include the database job or functionality around setting additional slots to inactive.
Solution
LOCATIONS Form Updates
2 new check boxes will be added to tab ‘Slots’ on the LOCATIONS form as this is where they are maintained. They will be displayed to the right of field ‘Carrier’ currently on this tab. Other items on the tab will need to be moved to make room for the 2 new check boxes. First checkbox will have header ‘Add.’ (for Additional as no room for entire word) and second checkbox will have header ‘Core’.
When ‘New’ button is clicked for Slots, a pop-up is displayed for the user to create a new slot. This pop-up will also have 2 checkboxes to denote whether the slot is ‘Core’ or ‘Additional’. The default value will be ‘Core’ therefore when the ‘New’ button is clicked ‘Core’ will initially be set as checked.
These check boxes will be coded in such a way that only 1 of them can be checked at any time for a single slot. E.g. a trigger will be attached to both check boxes that will uncheck ‘Add.’ if ‘Core’ is checked and vice versa.
These checkboxes will reference 1 new field that will be created on table GEO_SLOT called ADD_CORE. If a ‘Save’ is actioned in the ‘Slots’ tab and ‘Add.’ is checked at the time of save then GEO_SLOT.ADD_CORE will be set to ADDITIONAL for the corresponding Slot record. If ‘Core’ is checked at the time a ‘Save’ is actioned in the ‘Slots’ tab then GEO_SLOT.ADD_CORE will be set to CORE for the corresponding Slot record.
SLOT_USAGE Form Updates
The Slot Usage Form will be updated to display the data held in the new field GEO_SLOT.ADD_CORE. This will be displayed under ‘Unused Slots’ next to the ‘Slot Status’ field as 2 check boxes similar to those that will be displayed in the LOCATIONS form, whereby only 1 box can be checked at any time. Currently it looks like these fields under ‘Unused Slots’ can be edited/updated, so the checkboxes will also be editable.
When this functionality is released to MSPF and then MSLV, as there are already slots set up, all current slots will have field ADD_CORE set to ‘CORE’ initially by an update script on table GEO_SLOT.
Slot Import Functionality Update
In the IMPORT_MAINT screen (MSPF), Import type of SLOT currently details 24 fields. This config will be updated to import new field ADD_CORE in position 25. This will require users to append this new field to the end of the current Slot file format they use for importing.
Package IMP.sql will have function Process_Slot updated to pick up this new field during Slot Importing. A check will be done on this field to ensure it is either set to ADDITIONAL or CORE. These are the only 2 acceptable values. Users involved in importing Slots will have to be informed that new field ADD_CORE will be added into the Slot Import and can only be set to ADDITIONAL or CORE. If this field is left blank during the Import or is not a recognisable value, the default value will be CORE.
During the processing of lines in the Slot Import, field values are temporarily held in a Record of Type Slot_Type in Package SLOT.sql. Therefore, to temporarily hold the new field value of ADD_CORE, this record type of Slot_Type will have to be updated in this package.
Ability to set Additional Slots to Inactive
A new procedure called Inactivate_Additional_Slots in package SLOT.sql will be created. The purpose of this new procedure will be to select ALL records from table GEO_SLOT that have new field ADD_CORE set to ADDITIONAL and have a SLOT_STATUS of ACTIVE. All retrieved records will be updated to have a SLOT_STATUS of INACTIVE. This procedure will be run daily via a database job at 23:00. Note: It is currently assumed that this db job will run at 23:00 and may change during Development for testing purposes, however after release to the PRD system, any changes to the time the db jobs runs at will require a chargeable EARS call to be raised.
N.B. it has been requested to not include the database job or functionality around setting additional slots to inactive.
Ensuring new functionality remains Generic
This functionality has been requested by M&S, therefore we will have to make it generic so that it does not affect other clients using Slots. To do this the checkboxes on the ‘Slots’ tab of LOCATIONS and on the ‘New Slot’ pop-up will be system parameter based. If new parameter SLOT_INC_ADD_CORE_TYPES is set to ‘Y’ then the checkboxes will be shown, but if this parameter is set to ‘N’ or does not exist in a database then the checkboxes will not be shown, although there will be a space where they should appear on the ‘Slots’ tab.
Amendments to packages SLOT.sql and IMP.sql to pick up the new field ADD_CORE in the Imported file should not have any affect on the other clients if released, as they will not have their IMPORT_MAINT form configured to pick up this new field in import type SLOT.
Scope
These changes will be applied to system version 10.6 on MASTST and once approved MASPRD.
The solution requirement has been revised from the above to NOT include the database job or functionality around setting additional slots to inactive.
Data
The following sql script will be required to add the new field ADD_CORE to table GEO_SLOT, that will hold value ‘CORE’ or ‘ADDITIONAL’. This table is outlined in detail in ‘APPENDIX A TABLE UPDATES REQUIRED’
ALTER TABLE GEO_SLOT ADD ADD_CORE VARCHAR2(15);
Any slots already in M&S will need updated to have this new field populated because part of the request was that all slots must be set to ‘CORE’ or ‘ADDITIONAL’. The following script will update all slots current in M&S and set them to ‘CORE’ initially.
UPDATE GEO_SLOT SET ADD_CORE = 'CORE';
For the new field to be displayed on imported file format of SLOT on the IMPORT_MAINT it will first have to be added to the IMP_FIELD table. The script for this is below. When this is done it can de added to the SLOT file format in the last position of 25.
INSERT INTO IMP_FIELD (FIELD_NAME, IMPORT_TYPE) VALUES ('ADD_CORE', 'SLOT');
M&S are not the only client to use Slots so this functionality will be made as generic as possible so as not to affect other clients using the same forms and packages. The forms will be made generic by only displaying the new check boxes if system parameter SLOT_INC_ADD_CORE_TYPES is set to ‘Y’. Therefore, if a client does not use this functionality these check boxes will be set to invisible. The script to set up this parameter is:
INSERT INTO ADM_SYSTEM_PARAM (param_name, value, data_type, max_length, displayed, user_modifiable, description, config_by, config_by_value) VALUES ('SLOT_INC_ADD_CORE_TYPES ','Y','S',3,'Y','Y', 'Set to Y if Slot functionality is to make use of types CORE or ADDITIONAL', 'SYSTEM','NONE');
FUNCTIONAL DESCRIPTION
Locations Form
Below is how the ‘Slots’ tab on the LOCATIONS form will look including the new checkboxes for ‘Add’ (ADDITIONAL) and ‘Core’. (Note the final solution will show Core first then Add rather than as displayed below). The code behind these checkboxes will only allow one to be checked for each slot at any time. This code will be detailed in section 4. Code Amendments.
Below is how the ‘New Slots’ pop-up will look. It will be edited to include check boxes for Additional and Core. The default selection is Core and again there will be code behind these boxes that only allow one check box to be checked at any time.
Slot Usage Form
Below is what the amendments to the SLOT_UAGE form will look like to display the data held in the new field GEO_SLOT.ADD_CORE. Again, these checkboxes will display according to the value held in system parameter SLOT_INC_ADD_CORE_TYPES. (Again, Core will show first before Add)
Slot Import Functionality
Below is the SLOT file import format in the IMPORT_MAINT form. This will be updated to allow the importing of new field ADD_CORE into the GEO_SLOT table. This new field will be imported at the end of the current file format, in position 25. Below is a screenshot (manipulated to show all fields on the SLOT import on one screenshot) of the fields that should appear on the SLOT import:
CODE AMENDMENTS
Check Box Triggers
WHEN-CHECKBOX-CHANGED
The following code is a sample of how the code for the new checkboxes in the ‘Slots’ tab and ‘New Slots’ pop-up of the LOCATION form, and the SLOT_USAGE form will behave when they are check/unchecked. This will be the code used on the ‘Core’ checkbox whereby if a user selects ‘Core’, the ‘Add’ checkbox will become unchecked and vice versa, meaning only one can be checked at any time.
WHEN-VALIDATE-ITEM
The following code will be used to validate the selection each time a user navigates into another item on the form. This is the sample code that will be used on the ‘Core’ checkbox for validation:
Package IMP.sql update for SLOT Import
M&S currently run the most up-to-date version of IMP.sql package which is v5.3. This package will be updated to process the new column on the SLOT import in M&S. This code change should have no affect on other clients using SLOT import functionality as they will not have their import file format amended to pick up this field. The code will check to ensure the passed in value is either CORE or ADDITIONAL. If the field is blank or spelt incorrectly the slot default will be CORE. An extract of the amended code design to process field ADD_CORE is below:
v_slot.add_core := IMP.Extract_Field_Num( i_format,
'ADD_CORE',
0,
i_line,
v_format.delimiter,
v_errmsg );
IF v_slot.add_core IS NULL OR v_slot.add_core NOT IN ('ADDITIONAL','CORE') THEN
--default to 'CORE' if value passed in is null or mis-spelt
v_slot.add_core := 'CORE';
END IF;
Package SLOT.sql updates
The SLOT.sql package has been amalgamated into version v5.1 so this will be the version that will be updated and released to M&S with the required changes. There should be no dependencies on previous versions as all databases use the same version of SLOT.sql and all versions were identical.
RECORD of Slot_Type
A RECORD of type Slot_Type is defined in the SLOT.sql package. This will have to be updated to include the new field ADD_CORE as below:
TYPE Slot_Type IS RECORD (
slot_id geo_slot.slot_id%type, principal_loc geo_slot.principal_loc%type, secondary_loc geo_slot.secondary_loc%type, slot_type geo_slot.slot_type%type, day geo_slot.day%type, CWS geo_slot.collect_window_start%type, CWE geo_slot.collect_window_end%type, DWS geo_slot.deliver_window_start%type, DWE geo_slot.deliver_window_end%type, orig_DWS geo_slot.orig_deliver_window_start%type, orig_DWE geo_slot.orig_deliver_window_end%type, CWS_offset geo_slot.cws_day_offset%type, CWE_offset geo_slot.cwe_day_offset%type, DWS_offset geo_slot.dws_day_offset%type, DWE_offset geo_slot.dwe_day_offset%type, target_time geo_slot.target_time%type, bay geo_slot.bay%type, priority geo_slot.priority%type, mandatory geo_slot.mandatory%type, slot_status geo_slot.slot_status%TYPE, trlr_type geo_slot_Trailer_type.trailer_type%TYPE, add_core geo_slot.add_core%TYPE
REFERENCES
| EST-265413 NW-7S7FHR Add Slot Flag & DB Job v1.0.doc | |||
| EST-265413 NW-7S7FHR Add Slot Flag & DB Job v2.0.doc |
DOCUMENT HISTORY
| Initial version | ||||
| Reviewed and Issued |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Suk Sandhu | TMSCC MTS Product Manager |
