270871
270871 - PA-7X9EDF/ CN Trip and Order Detail Change
Copyright OBS Logistics © 2009
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
Changes to CTMS Report CN Trip and Order DetailAllan Butterworth/St Albans/UK/Exel Change Request Details:
- Change the Report parameters so that the report only includes 'UNLOAD' activity name.
- Include a column for POD Recieved Date and populate from Database
Solution
Amend the existing procedure CSV.WRITE_CN_TRIP_ORDER_DETAIL. The existing cursor C_TRIP_ORDER will be changed to include the following selection criteria
SCH_HAULAGE.ACTIVITY_NAME = ‘Unload’
To include POD_Received_Date , the cursor will also be updated to select ACTION_DATE from the order audit table SCH_ORD_AUDIT, where the ORD_CHANGE = 'POD changed from N to Y.' Using the audit table rather than the interface table means that POD_Received_Date will be populated when the POD has been manually applied and the extract will run quicker as OMS_REF is a primary key of the audit table.
The procedure will be updated to display the new field in the extract, at the end of the existing information.
Scope
This change will be applied to system version 10.5.0 on CONTST and once approved CONPRD.
SET-UP
Data
The data selected by the cursor C_TRIP_ORDER will be amended.
A condition will be added based on SCH_HAULAGE_ACTIVITY, where activity_name = ‘UNLOAD’.
A field will be added to the select statement, SCH_ORD_AUDIT.ACTION_DATE where SCH_ORD_AUDIT.ORD_CHANGE =’POD Changed from N to Y’. To ensure that records which do not have a POD date are excluded, the table will be added to the cursor with an outer join and the ORD_CHANGE condition will include an outer join. The outer join will ensure that records are not excluded where the POD has not bee changed. In this instance , the action date field will be returned as a null value.
FUNCTIONAL DESCRIPTION
After making the amendments to the cursor which extracts the data, we must also add the new field POD Received Date to the extract. The field will be displayed at the end of the existing data.
If an audit record exists for the oms_ref with the required ORD_CHANGE of ‘POD changed from N to Y’, the new field will be populated with the ACTION_DATE value, otherwise the new field will be null.
REFERENCES
| EST 270871 PA-7X9EDF CN Trip and Order Detail Changes v1.0 |
DOCUMENT HISTORY
| Initial version | ||||
| Reviewed and Issued |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
