287315
DHL C-TMS
Change to PO Screen for Tesco Phase 2b
FUNCTIONAL SPECIFICATION - 10.6
- 1.0
Reference: FS 287315 MW-8BNLMJ
Client Requirement
When entering docs received date/invoice number cascade data to related trips if values against trips are null
2 new PO status "supplier confirmed" and "supplier rejected"
E-booking related - pop up screen at supplier confirmed status to manually confirm/reject supplier data entries from web portal
disallow GEN TI when no confirmed EDI message sent
Solution
The Docs received date and invoice number are entered from the Purchase Order screen (PURCH_ORD.fmx), via the Edit button. The data is stored on the table SCH_PURCHASE_ORD in columns DOCS_RECIEVED and INVOICE_NO. Fields to display the Docs Received and Invoice number on the trip screens are being added under RIO MW-8BNL82. As part of this RIO two new columns will be added to SCH_ORD_PO_DATA.
When an order is created from a PO a record is inserted into the table SCH_ORD_PO_DATA for that order and PO. This information is then displayed on the trip screens under the PO Detail tab. The population of this table will be changed to included the docs received and invoice number if they are present during TI generation.
If the fields are edited in the PO screen after TI generation, an update will be made on SCH_ORD_PO_DATA to reflect these changes.
Three new statuses will be added to SCH_PO_STATUS:-
•07 – Supplier BKG (Part of RIO MW-8BNLQH)
•07 – Supplier Confirmed
•08 – Supplier Rejected
These will be available in the drop down list on the Purchase Order form.
When a PO is actioned in the Supplier Booking screen (see RIO MW-8BNLQH for details) the PO status is changed to a new status of Supplier BKG. This indicates the PO has been altered by the external Supplier Screen. This status will be available in the search function to allow users to search for any PO’s at this status. A button will be added to the Edit Po section of the Purchase Order screen called ‘Suppler Bkg’, pressing this button will bring up a new pop up screen that will contain the information entered in the Supplier Booking screen.
The pop up screen will contain the following fields:-
Matched CBTPO
Currency
Unit cost
Cargo Receipt Date
Booking DU
Booking QTY
Transit DU,
Transit Qty
Transit Weight
Transit Volume
Sub Transit DU
Sub Transit QTY
Source Location
Destination Location
Net Weight
Item Description
Dimensions (Height, Width, Depth)
Comments
A new table SCH_PO_SUPPLIER_DATA (SPSD) will be created to store the information that comes back from the Supplier Booking Screen.
There will be two buttons available in the pop up screen, one to confirm the details and one to reject them. The free text comments box will allow the user to give a reason for rejecting the data. If the details are confirmed by the user then the information in SPSD will be used to update the PO record. All information will be updated apart from the locations (source and destination). These fields will change colour to indicate that different locations have been entered by the supplier but the data will not change as the Source and Destination from the PO are required by Tesco.
The Locations stored on SPSD will be used when Generating TI’s for PO’s where this information has been entered. For any PO’s that have not been updated in the supplier booking screen the source and destination from the PO will be used. This will require a change to the Gen Ti process.
The cargo receipt date on SPSD will be subject to the same validation as the other dates on the purchase order. The date must fall within the system parameters as implemented in RIO MW-87RGA6. If the dates are outside of this range and the user tries to confirm the data they will be prompted to reject the data so the supplier can update the date.
When the data is confirmed the PO status will be updated to ‘Supplier Confirmed’, or if the data is rejected the PO status will be updated to ‘Supplier Rejected’. PO’s that are at status Supplier Rejected cannot be progressed to Approved or Confirmed and the TI’s cannot be generated.
This status change will trigger an update of the Supplier booking screen (Portal) – Details of this will be covered in RIO MW-8BNL82.
Before allowing the Gen TI process on a purchase order a check will be made on the table INT_XML_OUTBOUND_FILE. If an EDI message is not found for the PO or the only EDI message for the PO failed to send, then the PO will not be included in the Gen TI process and a message displayed to the user.
If an EDI message for the PO is present in the table, and was successfully sent then the Gen TI process will proceed.
When retrieving PO’s in the purchase order screen a check will be made on the inbound version number associated with the PO Number. Each PO number can have several PO’s and they will all have a version number which should be the same. If a PO has a version number that is not the maximum number associated with the PO Number then this PO will be highlighted in red. The users will check this before progressing any of the PO’s.
Scope
This change will be applied to system version 10.6.
Set-up
Pre-requisites
Menu-Structure
Data
Functional Description
Docs Received & Invoice Number
The Docs Received Date and Invoice Number are entered on the Purchase Order screen.
As part of RIO MW-8BNL82 fields will be added to the Trip screens and also to the table SCH_ORD_PO_DATA.
At present when an order is created from a PO (POM.GEN_TI) a record is inserted into SCH_ORD_PO_DATA. This record contains information about the PO and about the order created. This process will be changed to include the population of the Docs Received Date and Invoice Number fields on this table.
If the fields are edited, in the Purchase Order screen, after TI generation SCH_ORD_PO_DATA will be updated to reflect these changes. This will include population of these fields if they were not populated during the TI generation process.
Supplier Booking Screen
Three new statuses will be added to the drop down list which is available in the Purchase Order screen search functionality.
The new statuses will be
- 07 – Supplier BKG (Handled in RIO MW-8BNLQH)
- 08 – Supplier Confirmed
- 09 – Supplier Refused
These new statuses will not be available in the PO status update drop down as they are not for manual updates.
The PO will move to status Supplier BKG when it is actioned in the new Supplier Booking screen – this functionality is covered in RIO MW-8BNLQH.This status indicates that the PO has been altered in the Supplier Booking screen and that the data entered should be checked by a user.
A new button will be added to the PO screen called ‘Supplier Bkg’.
Pressing this button will bring up a new pop up screen that contains the data entered in the Supplier Booking screen for this Purchase Order.
| Customer |
| Item No |
| Supplier Cargo Ready Date |
| CBTPO |
| HTS Code |
| Item Description |
| Unit Cost |
| Unit Currency |
| Freight Terms |
| Booked Type |
| Booked Qty |
| Transit DU |
| Transit Qty |
| Transit CBM |
| Weight Gross |
| Weight Net |
| Sub Transit DU |
| Sub Transt Qty |
| Dimensions Length |
| Dimensions Width |
| Dimensions Height |
| Calculated CBM |
| Source Location |
| Destination Location |
| Comments |
A new table will be created called SCH_PO_SUPPLIER_DATA (SPSD) – see Appendix A for table definition. The table will be populated from the Supplier Booking Screen.
The attached document indicates how the data in the pop up screen relates to the information in the Supplier Booking Screen.
The pop up screen will have 3 buttons.
- Approve – confirm changes to PO data
- Reject – Reject changes to PO Data
- Close – Close the pop up screen
If the user approves the data in this screen, then the purchase order will be updated with the information stored in SCH_PO_SUPPLIER_DATA. The below chart indicates what fields will be updated in SCH_PURCHASE_ORDER.
| PO Data | Action/Updated by |
| Customer | No Update |
| Item No | No Update |
| Supplier Cargo Ready Date | Cargo_Ready |
| CBTPO | Matched_CBTPO |
| HTS Code | Special_Instructions |
| Item Description | Item_Description |
| Unit Cost | Unit_Cost |
| Unit Currency | Unit_Currency |
| Freight Terms | Freight_Terms |
| Booked Type | Booking_DU_Type |
| Booked Qty | Booking_DU_Qty |
| Transit DU | Transit_DU_Type |
| Transit Qty | Transit_DU_Qty |
| Transit CBM | Transit_Volume |
| Weight Gross | Transit_Weight |
| Weight Net | Net_Weight - NEW FIELD |
| Sub Transit DU | Sub_Transit_DU_Type |
| Sub Transt Qty | Sub_Transit_DU_Qty |
| Dimensions Length | No Update |
| Dimensions Width | No Update |
| Dimensions Height | No Update |
| Calculated CBM | No Update |
| Source Location | No Update |
| Destination Location | No Update |
| Comments | No Update |
A new field called NET_WEIGHT will be added to SCH_PURCHASE_ORDER to store the Weight Net value from the Supplier Booking Screen. A field will also be added to the purchase order screen to display the Net Weight.
The dimensions fields will not update any data on the purchase order. The calculated CBM field is calculated as:-
(Length * Width * Height) * Transit Qty
The Source and Destination fields will not be used to update SCH_PURCHASE_ORDER as the original fields are required by Tesco in the PO message. However if the fields in the Supplier Booking screen differ from the existing Purchase Order source and destination the colour of the fields, in the purchase order screen, will be changed to indicate a difference.
Next to source and destination fields there will be a button labelled ‘View’. Pressing this button will bring up a further pop up screen that contains the address information associated with the location id.
The locations stored on SPSD will be used when creating orders in the Generate TI process. If a record exists in SPSD then the From Location and To Location of the order will be populated from this record. If a record doesn’t exist then the Purchase Order source and destination will be used. POM.GEN_TI will be changed to look for the locations on SCH_PO_SUPPLIER_DATA when creating the orders.
Once the data has been updated the Purchase Order status will be changed to ‘Suppler Confirmed’.
If there is a problem with the data, the user can reject the updates using the Reject button. The comments field on the pop up screen will be free text and will allow the user to give a reason for rejecting the updates.
The Supplier Cargo Receipt Date will be subject to the same validation as all other dates on a purchase order. There are two system parameters that determine how far back and how far forward dates are allowed to be. The supplier cargo receipt date should fall within these parameters. If it does not and the user attempts to confirm the data they will be prompted to reject the data so the supplier can fix the date.
If the user rejects the data, the purchase order status will be changed to ‘Supplier Refused’. This status change will trigger an update of the Supplier Booking Screen.
PO’s at a status of ‘Supplier Refused’ cannot be progressed to status ‘Approved’ or ‘Confirmed’ and the TI’s cannot be generated.
Gen TI Process
Before orders can be generated from a purchase order a check needs to be made to ensure the EDI message has been sent for this PO.
The generate TI process will be altered to perform this check before proceeding to create the orders. The table INT_PO_OUTBOUND_FILE will be used to determine if a message has been sent. Using the Po Number and Po reference the table will be checked, if a message is not found or the message is at status ‘SEND_FAIL’ then the PO will not be included in the Gen Ti process. A message will be displayed in the purchase order audit to indicate why the orders were not generated.
If a message is found and was successfully sent then the order will be generated from the purchase order data.
PO version Number
Each purchase order has a version number associated with it (SCH_PURCHASE_ORDER.PO_VERSION). One PO Number can have several PO reference and all of the version numbers should match. When a PO is retrieved in the purchase order screen the version number of all the PO’s associated with that PO Number will be checked. If the PO’s version number is lower than the maximum version number for the PO Number then this PO will be highlighted in red. This will then require an action from the user. E.G.
PO NumberPO Reference PO Version
12345ABC3
12345DEF2
12345GHI3
In this situation the PO Reference DEF would be highlighted as red in the purchase order screen, as the version number is lower than the others.
PO Export
A new button labelled ‘Export’ will be added to the purchase order screen.
Pressing this button will produce a CSV export which will contain the Supplier Booking data for the PO’s. The button will only be enabled if the user had searched for a particular PO and that PO has Supplier Data, or if the user has searched for PO’s at status Supplier BKG.
The extract will contain the following information from the table SCH_PO_SUPPLIER_DATA :-
| Customer |
| Item No |
| Supplier Cargo Ready Date |
| CBTPO |
| HTS Code |
| Item Description |
| Unit Cost |
| Unit Currency |
| Freight Terms |
| Booked Type |
| Booked Qty |
| Transit DU |
| Transit Qty |
| Transit CBM |
| Weight Gross |
| Weight Net |
| Sub Transit DU |
| Sub Transt Qty |
| Dimensions Length |
| Dimensions Width |
| Dimensions Height |
| Calculated CBM |
| Source Location |
| Source Location Name |
| Source Address Line 1 |
| Source Address Line 2 |
| Source Address Line 3 |
| Source Town |
| Source County |
| Source Country Code |
| Source Postcode |
| Destination Location |
| Destination Location Name |
| Destination Address Line 1 |
| Destination Address Line 2 |
| Destination Address Line 3 |
| Destination Town |
| Destination County |
| Destination Country Code |
| Destination Postcode |
| Comments |
If one PO has been selected then only that PO will be included in the export. If the status has been used to search then all POs at status ‘Supplier BKG’ will be included.
A new procedure will be written in DP_CSV2 that will produce the export. The procedure will accept a PO Number or PO status as a parameter.
Table Updates Required
New Table SCH_PO_SUPPLIER_DATA
| SUPPLIER_CARGO_READY | DATE |
| CBTPO | VARCHAR2(20) |
| HTS_CODE | VARCHAR2(4000) |
| ITEM_DESCRIPTION | VARCHAR2(100) |
| UNIT_COST | NUMBER(12,4) |
| UNIT_CURRENCY | VARCHAR2(3) |
| FREIGHT_TERMS | VARCHAR2(12) |
| BOOKING_DU_TYPE | VARCHAR2(20) |
| BOOKING_DU_QTY | NUMBER(24,4) |
| TRANSIT_DU_TYPE | VARCHAR2(20) |
| TRANSIT_DU_QTY | NUMBER(24,4) |
| TRANSIT_VOLUME | NUMBER(25,5) |
| GROSS_WEIGHT | NUMBER(24,4) |
| NET_WEIGHT | NUMBER(24,4) |
| SUB_TRANSIT_DU_TYPE | VARCHAR2(20) |
| SUB_TRANSIT_DY_QTY | NUMBER(24,4) |
| DIMENSION_LENGTH | NUMBER(12,4) |
| DIMENSION_WIDTH | NUMBER(12,4) |
| DIMENSION_HEIGHT | NUMBER(12,4) |
| CALCULATED_VOLUME | NUMBER(12,4) |
| SOURCE | VARCHAR2(12) |
| SOURCE_LOCATION_NAME | VARCHAR2(50) |
| SOURCE_ADDRESS_LINE1 | VARCHAR2(50) |
| SOURCE_ADDRESS_LINE2 | VARCHAR2(50) |
| SOURCE_ADDRESS_LINE3 | VARCHAR2(50) |
| SOURCE_TOWN | VARCHAR2(50) |
| SOURCE_COUNTY | VARCHAR2(50) |
| SOURCE_COUNTRY_CODE | VARCHAR2(3) |
| SOURCE_POSTCODE | VARCHAR2(9) |
| DESTINATIONVARCHAR2(12) | VARCHAR2(12) |
| DESTINATION _LOCATION_NAME | VARCHAR2(50) |
| DESTINATION _ADDRESS_LINE1 | VARCHAR2(50) |
| DESTINATION _ADDRESS_LINE2 | VARCHAR2(50) |
| DESTINATION _ADDRESS_LINE3 | VARCHAR2(50) |
| DESTINATION _TOWN | VARCHAR2(50) |
| DESTINATION _COUNTY | VARCHAR2(50) |
| DESTINATION _COUNTRY_CODE | VARCHAR2(3) |
| DESTINATION _POSTCODE | VARCHAR2(9) |
| COMMENTS | VARCHAR2(1000) |
Add column to SCH_PURCHASE_ORDER
NET_WEIGHT NUMBER(12,2)
Document History
| Initial version | ||||
| Reviewed and Issued | ||||
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
