255508
255508 SC-7K2E3M EDI Validation to include Schedule Date
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
Extra validation to be added to include schedule date. Stephen Coles/Daventry/UK/Exel
Added by SS - Currently the EDI customer reference is validated on whether the ref is unique by cross referencing customer name. By adding schedule date to be included to this validation the issue of orders being rejected as a duplicate should be solved
To remove risk of orders not being validated in MTS, thus preventing major operational issues at customer DC due to non production of delivery schedule.
Solution
The Staples Order upload into MTS will be modified to include the schedule date as an additional field while validating for duplicate entries.
The Orders interface runs from data provided in csv files with filenames EXE_ORD…, for example EXE_ORD_20080926_000399.txt_1
Currently the combination of customer ‘Staples’ and external reference (customer) ref for example 770544428270 is checked for each new order against existing orders on MTS. If the interface validation finds a match, the new order is rejected as a duplicate. As a consequence of Staples reusing external references, the validation rules will be modified to also include schedule date for this duplication check.
This means the same external reference can be used on different schedules.
Scope
This change will be applied to system version 10.5 on CMPF (UKSTST) and once approved CMLV (UKSPRD).
Because this estimate was requested as an urgent system enhancement, no impact analysis has been done to identify whether any ‘down stream’ system issues might arise as a consequence of allowing the same external (customer) ref into the system more than once. This isn’t thought to be a significant risk but if issues arise requiring further system enhancements, these would be considered out of scope of this estimate and related MTS system development.
Analysis has established that the EXE_ORDER inbound message flow has the potential to accept modification requests for existing orders. These are identified using the EXTERNAL_REF. Allowing duplication of EXTERNAL_REF could cause conflict for order modification updates, should duplicates exist. At present the client has confirmed that there is no intention to use modification files and as such no change to this functionality is required.
SET-UP
Pre-requisites
Setup to receive Inbound EDI orders of type EXE_ORDER - This is already in place as changes are to existing MTS Inbound EXE order message flow. No additional setup required.
Menu Structure
Unchanged
Data
No Data Changes required.
FUNCTIONAL DESCRIPTION
Existing Functionality
The following fields from the existing message format are used in the current validation: • ACTION received in data position 5 <HEADERSOR> section – after 4th comma • CUSTOMER received in data position 8 (HEADERSOR> section – after 7th comma • EXTERNAL_REF received in data position 16 <HEADERSOR> section - after 15th comma
Example of current file:
"EXE_ORD_20081007_000410.txt" <RECHEADSTART>CMLV,EXE_ORDER<RECHEADEND> <HEADERSOR>,,,EXE,A,09-10-08,DHL,Staples,WHS00577,STR44002,08102008230000,11102008230000,09102008080000,09102008120000,N,770576345,,,Standard,3002,,WATLING,100720080557<HEADEREOR><DETAILSOR>0576345,STATIONERY,STATIONERY,14,,473600,2600000<DETAILEOR> <HEADERSOR>,,,EXE,A,09-10-08,DHL,Staples,WHS00577,STR44007,08102008230000,11102008230000,0910200870000,09102008073000,N,770576385,,,Standard,3007,,WATLING,100720080557<HEADEREOR><DETAILSOR>0576385,STATIONERY,STATIONERY,11,,343100,2000000<DETAILEOR> .. .. <EOF>
The EXE_ORDER message type upload runs a generic section of the MTS order upload process. Currently, only order files of type EXE_ORDER and ‘A’ddition are received in CMLV (UKSPRD).
A Unix Script runs Procedure INT_MSG.read_int_msg_header, which in turn calls: - INT_MSG.process_unison_order_file - OMS_INT.process_unison_record
Currently, validation for EXTERNAL_REF is carried out by procedure OMS_INT.duplicate_order. This is passed CUSTOMER and EXTERNAL_REF and returns OMS_REF and an error message.
The checks for duplicates are performed in two places in the upload process. There are calls from within the OMS_INT package from the following procedure/functions; process_unison_record and post_order.
Each check for duplicates will set a variable v_dup_ret / v_ret to TRUE if at least one instance of the given EXTERNAL_REF and CUSTOMER exists on the orders table (SCH_ORD) otherwise this is FALSE.
Checks are made for both ACTION field ‘M’odify and ‘A’ddition.
v_dup_ret := duplicate_order(v_order.customer,v_order.external_Ref,v_oms_ref,v_dup_errmsg);
v_order.ACTION != 'M' AND v_dup_ret THEN v_duplicate := 'Y'; v_errmsg := 'An order (OMS_REF ' || v_oms_ref || ') already exists with a Customer of ' || v_order.customer || ' and Customer Ref of ' || v_order.external_ref ||', this Order is a duplicate and will not be loaded.'; v_order_created := FALSE;
New Functionality
New validation is required to allow duplicate external references for differing schedules.
The following fields from the current message format will be used in the new validation: • MSG_TYPE received in data position 2 <RECHEADSTART> - after 1st comma • ACTION received in data position 5 <HEADERSOR> section – after 4th comma • SCHED_NAME received in data position 6 <HEADERSOR> section - after 5th comma in file • CUSTOMER received in data position 8 <HEADERSOR> section – after 7th comma • EXTERNAL_REF received in data position 16 <HEADERSOR> section - after 15th comma
A new function F_DUPLICATE_ORDER_SCHEDULE will be created and will receive SCHED_NAME, CUSTOMER and EXTERNAL_REF from the file. This will perform the validation based on the new logic to include SCHED_NAME.
The format of the schedule as received in the EXE_ORDER files is ‘DD-MM-YY’ and this is stored as such on the interface table (INT_ORDER_HEADER). This is not the standard MTS schedule format and will need to be converted to the format YYMMDD before being passed to the new function for duplicate validation.
This function will return OMS_REF and an ERROR_MESSAGE and indicate TRUE for an attempted file upload if an order record exists on MTS (SCH_ORD) on the same schedule with the same customer and external ref.
This new function (F_DUPLICATE_ORDER_SCHEDULE) will replace the existing validation calls for duplicate orders (DUPLICATE_ORDER) ONLY where the ACTION field is ‘A’ddition or NOT ‘M’odfify. The existing validation calls for modification files will remain unchanged.
As the EXE_ORDER files use a generic upload process, in order to preserve this generic functionality the new validation check will be conditional and only apply if message type is EXE_ORDER. If the message type is not EXE_ORDER the functionality should remain as current.
The new error message text will read: v_errmsg := 'An order (OMS_REF <OMS_REF>) already exists on schedule <SCHEDULE> with a Customer of <CUSTOMER> and Customer Ref of <CUSTOMER_REF>, this Order is a duplicate and will not be loaded.';
Any errors can be viewed in the Interface Errors screen (INT_ERR) in the normal manner, this will remain unchanged.
The current message format will remained unchanged. No new fields or layout changes are to be applied. The naming convention will also remain unchanged.
To clarify, if an order record exists with the same customer and external ref, but, on a differing schedule to that of the attempted order upload, the upload process will to continue to process the order with the duplicate external reference. This will upload successfully pending the further order upload standard validation checks, which will remain unchanged.
Changes will be required to the OMS_INT package v1.15 for the UK Secondary (Staples) database.
References
| EST 255508 SC-7K2E3M EDI Validation to include Schedule Date v1.doc | 1 | 02/10/2008 |
Document History
| Initial version | ||||
| Reviewed and Issued |
Authorised By
| Dave Meir | Development Manager | |
| Suk Sandhu | TMSCC MTS Product Manager |
