274129
274129 - MW-82GENA/ Tesco PO Form
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
Tesco require further development / information holding within MTS and sending via EDI to their own system.
New development of screens and functionality within the Purchase order screens
Solution
A new version of the xsd (MTSPurchaseOrder version 1.8) will be created (see appendix A).
All of the following tags have been added to the ‘PO_HEADER’ section :-
MATCHED_CBTPO,
HIERARCHY_1,
DOCS_RECEIVED,
INVOICE_NO,
SUBMITTED_DATE,
REVISED_DATE
All of the following tags have been added to the ORDER_TRACKING.
LOAD_TYPE,
BOOKING_DU_QTY,
TRANSIT_DU_QTY,
TRANSIT_WEIGHT,
TRANSIT_VOLUME
SUB_TRANSIT_DU_QTY
The tag TRANSPORT_MODE has been added to the TRIP_TRACKING section.
Relevant MTS tables will also be altered to store these new fields (see appendix B).
Inbound XML Changes:-
ESI will need to amend the current Tesco’s PO flow in order to extract the relevant part of Special Instructions containing the Hierarchy Level 1 which will then populate the new tag <HIERARCHY_1>.
OBS will change the procedure INT_XML.POPULATE_INT_PO_INBOUND to extract the new tag and populate the new field on the INT_PO_INBOUND_DETAIL table.
OBS will also change the procedure INT_XML.POPULATE_SCH_PURCHASE_ORDER to Populate the new HIEARCHY_1 field on SCH_PURCHASE_ORDER from the corresponding field on INT_PO_INBOUND_DETAIL.
Internal MTS Changes:-
The form PURCH_ORD (1.7) will be changed to add the new fields SCH_PURCHASE_ORDER.HEIRARCHY_1, MATCHED_CBTPO (labelled Additional Cust Ref), DOCS_RECEIVED and INVOICE_NO to the EDIT canvas only.
When the new field DOCS_RECEIVED is populated it will now need to create an outbound XML message. This will be done in the existing trigger T_SCH_PO_BU. This data will be used to generate the new milestone message MST-2. (See FS 274132 for more details about the milestone XML messages).
This same trigger also needs changing so that when the status is changed from provisional (’01) to either Approved (‘02’), Rejected (‘03’) or Revised (‘04’) then the new field SUBMITTED_DATE needs updating on the SCH_PURCHASE_ORDER table. If the status is changed from any other value (other than CONFIRMED ‘06’) then the REVISED_DATE will also be updated.
A new button on main canvas will also be provided to allow the re-send of a confirmation message (the same code that already exists when the status will be changed to CONFIRMED - ‘06’; see trigger T_SCH_PO_BU). This will only be allowed if the Purchase Order is currently at CONFIRMED status.
The new field MATCHED_CPTPO also needs adding to the search screen and if populated then only appropriate records will be selected when queried.
An additional record also needs to be written to SCH_PO_AUDIT table when a confirmation message is re-sent.
When orders are generated from purchase orders via the POM.GEN_TI procedure then this needs changing to populate the SCH_ORD.EXTERNAL_REF field from the new SCH_PURCHASE_ORDER.MATCHED_CPTPO field (if populated) otherwise the PO_NUMBER will be used as now.
Also a new table will be created called SCH_ORD_PO_DATA (see estimate for Order screen 274131 for more details) to hold the Load Type and other extra PO fields. As part of the GEN_TI process this table will be generated at OMS level, populating the Load Type and the other extra PO fields.
A further change will be to use the SUB_TRANSIT_DU_TYPE to create the order lines instead of the TRANSIT_DU_TYPE when the TRANSIT_DU_TYPE is set to ‘HBD’.
NB) Rather than hard coding ’HBD’ a new system registry will be available: PO_OVERIDE_DU_WITH_SUB which will be a comma separated string containing all DU Types to be matched against the Transit DU Type were the Sub Transit DU Type is to be used in preference.
Outbound XML Changes:-
The procedure INT_XML.PROCESS_PO_OUTBOUND will be changed to populate the new field MATCHED_CBTPO on the INT_PO_OUTBOUND_FILE table from the corresponding field on SCH_PURCHASE_ORDER.
HEIRACHY_1, DOCS_RECEIVED, INVOICE_NO, SUBMITTED_DATE and REVISED_DATE (if populated) will also be populated in the PO_TRACKING section.
The outbound message needs to populate all of the extra tags required in the ORDER_TRACKING section (see Order estimate 274131 for more details).
The Transport Mode also needs adding to the TRIP_TRACKING section (see Trip estimate 274132 for more details).
Export Changes:-
SUBMITTED_DATE, REVISED_DATE, DOCS_RECEIVED and INVOICE_NO all also need adding to the existing PO export.
Scope
This change will be applied to system version 10.5
Data
Systems Registries
A new system registry will be created called PO_OVERIDE_DU_WITH_SUB.
It will contain a comma separated list of DU Types.
If the ‘Transit DU Type’ field matches any of the values in the new system registry then the ‘Sub-Transit DU Type’ field will be used to generated the order lines from the purchase order instead of the transit field.
FUNCTIONAL DESCRIPTION
Additional fields are to be provided by Tesco via ESI using the updated XML inbound flow and stored in MTS. The other additional fields may be maintained within MTS specifically for the Tesco flow. All new fields will then be fed back to Tesco using the updated XML outbound flow.
This specification details the changes to Purchase Order form and how this affects the inbound and outbound XML flows.
There are 2 other specifications (Order - 274131 and Trip - 274132) which should be read in conjunction with this specification.
Inbound XML Changes
An updated version of the MTSPurchaseOrder xsd (version 1.8) can be found in appendix A.
The only new inbound tag that will affect the Purchase order form is in the ‘PO_HEADER’ section and is called ‘HIERARCHY_1’.
This field is already being provided by Tesco as part of the special instructions tag but to improve the visibility of this field it will now be extracted by ESI from this string and used to populate the new tag.
The inbound XML process (INT_XML.POPULATE_INT_PO_INBOUND) will be changed to extract this new tag and populate the corresponding new field HIERARCHY_1 on the table INT_PO_INBOUND_DETAIL (see appendix B for a full list of database changes).
Another inbound XML process (INT_XML.POPULATE_SCH_PURCHASE_ORDER) will be changed to populate the new field HIERARCHY_1 on the table SCH_PURCHASE_ORDER from the corresponding field already populated on the INT_PO_INBOUND_DETAIL table.
Internal MTS Changes
Several changes are to be made to the MTS system to maintain the fields passed in on the new XML flow. Changes will be made to maintain the additional fields required by Tesco. Some of these fields will be visible and maintained manually in MTS and others will be populated behind the scenes based upon certain activities performed in MTS.
Purchase Order Form
The Purchase Order form (PURCH_ORD – 1.8) currently looks like :-
The main canvas as shown above will remain mostly unchanged with the exception a new ‘Re-Send’ button next to the Search button.
The new button’s processing will firstly check to ensure that the user has at least one PO reference selected. It will also check that all selected lines are at status ‘CONFIRMED’ (‘06’), if not a suitable error will be displayed to the user. For each selected line the process will then write a record to the interface table as per the code that already exists in the T_SCH_PO_BU trigger so that the selected POs will be re-sent.
All new fields will be added to the Edit canvas displayed by pressing the ‘Edit’ button on the main canvas.
The canvas currently looks like :-
All of the new fields to be added will be from the SCH_PURCHASE_ORDER table.
The ‘Matched CBTPO’ field (MATCHED_CBTPO) will initially be blank as it will not have been received through the inbound XML flow. The user will be able to maintain the field here and enter the related CBTPO number (no validation/free text). This will then be used later to populate the Customer Reference (SCH_ORD.EXTERNAL_REF) on the generated orders (see Gen TI changes later).
Once the order has been generated (i.e. PO Status is CONFIRMED and TI flag is set) then the CBTPO field will not be allowed to be changed.
The ‘Hierarchy Level 1’ field (HIERARCHY_1) will have been populated by the inbound XML. It will be displayed and amendable in the above screen (no validation).
The ‘Docs Received’ field (DOCS_RECEIVED) will initially be blank but any valid passed/current date will be allowed to be entered. No future dates will be permitted.
At the same time as the user enters the ‘Docs Received’ field then it is presumed the user will also enter the ‘Invoice Number’ field (INVOICE_NO). This again will initially be blank but will be manually entered by the user to confirm the invoice number provided by the Suppliers. No validation will be made on entries and alpha-numeric sequence will be permitted.
T_SCH_PO_BU Trigger
This ’before-update’ trigger is currently used to generate a request to send an outbound XML message to Tesco depending on certain criteria regarding changes to existing records and also to add records to the audit file based on different criteria of what has been changed.
A new requirement will be to generate a request for an outbound XML message when the new ‘Docs Received’ field gets populated.
This new condition will be added to the existing conditions in the trigger and the same code to insert the XML outbound request will be run.
The outbound XML will be changed to add the new fields including this new ‘Docs Received’ field to this flow (see Outbound XML Changes section later for more details about this new flow - Milestone 2 ‘MST2’).
Changes to Gen TI Procedure
When orders (transport instructions) are generated from within the Purchase Order form using the Create TI’s button then this calls the procedure GEN_TI from the POM package.
This procedure needs to be modified to take into account the new fields.
The customer reference (SCH_ORD.EXTERNAL_REF) is currently being set from the purchase number (SCH_PURCHASE_ORDER.PO_NUMBER). If the new field ‘Matched CPTPO’ (SCH_PURCHASE_ORDER.MATCHED_CBTPO) is populated then this field is going to be used instead.
For every order generated a record will be created on the new SCH_ORD_PO_DATA (see specification for 274131 for more details).
For this specification the process simply needs to populate the PO_REFERENCE and LOAD_TYPE from the same values on the SCH_PURCHASE_ORDER table.
A further change is related to the creation of the order lines which is done by calling the standard procedure OMS.Insert_Line.
Two of the parameters that are passed to this procedure are the ‘DU Type’ and ‘DU Quantity’.
A new system registry called ‘PO_OVERIDE_DU_WITH_SUB’ will have been created which will contain a comma separated list of values for the Transit Du Type where the Sub-Transit values are to be used in preference.
If the value in the field SCH_PURCHASE_ORDER.TRANSIT_DU_TYPE exists within the string for the system registry ‘PO_OVERIDE_DU_WITH_SUB’ then when calling the insert order line code the process will pass the field SUB_TRANSIT_DU_TYPE for the ‘DU Type’ and the field SUB_TRANSIT_DY_QTY for the ‘DU Quantity’ otherwise the current fields will be used.
NB) The ‘Weight’, ‘Volume’ and ‘Qty in Cases’ fields will remain unaffected by this change.
Outbound EDI Changes
There are currently 2 outbound flows affected by this change.
There is an automatic XML flow generated from triggers and a manual csv export.
Changes to Outbound XML Flow
The procedure PROCESS_PO_OUTBOUND in the INT_XML package needs to be changed to use the new fields in the creation of the outbound XML file.
The flow firstly populates an intermediate table called INT_PO_OUTBOUND_FILE.
All of the new fields added to the SCH_PURCHASE_ORDER table will also have been added to the INT_PO_OUTBOUND_FILE table so will need populating accordingly.
These include :-
HEIRARCHY_1, DOCS_RECEIVED, INVOICE_NO, SUBMITTED_DATE and REVISED_DATE.
The outbound flow (see appendix A for full xsd) will then need to be changed to take this data into account in the population of the PO_HEADER section.
Also the LOAD_TYPE from the new table SCH_ORD_PO_DATA needs adding to the ORDER_TRACKING section.
A more significant change to this procedure is in the ORDER_TRACKING section (see order specification 274131 for more details) and the TRIP_TRACKING section (see trip specification 274132 for more details).
Changes to Export Flow
The existing export flow is called PURCHASE_ORDER_EXTRACT and is in the DP_CSV2 package.
This will need changing to add all of the required additional fields.
REFERENCES
Not Applicable
DOCUMENT HISTORY
| Initial version | ||||
| Reviewed and Issued |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
