287316

DHL C-TMS

Hold Customer Invoice Consolidation Details

FUNCTIONAL SPECIFICATION - 10.6

18/05/2011 - 1.0
Reference: 287316 MW-8BNLNT

Functional Overview

Client Requirement

New db file to hold data element prior to xml extraction being sent to Tesco far east

New screen to amend data. Similar to existing spreadsheet format being used. Including relevant filter and check box status

XML EDI extraction

Send/resend functionality

colouring of mandatory fields

Solution

CIC Screen

A new screen called CIC will be created in CTMS. This screen will be accessible through the PO Detail tab on the Trip Screens. A button will be added to this tab (in both trip manipulation and trip planning and execution), that will call the CIC screen passing through the information from the tab E.G. trip id. The screen will also be accessible though the menus to allow users to search view and generate files for multiple records.

The data in the CIC screen will be stored in a new table INT_PO_CIC so as not to affect the data in the original tables. The CIC screen will contain the following data:-

Field	Source		Updateable
M-Ref	SCH_TRIP.BILL_OF_LADING		N
Supplier No	SCH_PURCHASE_ORDER.SOURCE		N
Supplier Name	GEO_LOCAITON.LOCATION_NAME for Source		N
ETD	SCH_TRIP_STOP.ACTUAL_DEPART		N
Vessel	Trailer or Vessel		Y
DR	Manual Entry		Y
ETA	SCH_TRIP_STOP.ARRIVE		N
PO ID	SCH_PURCHASE_ORDER.PO_ID		N
CBT PO	SCH_PURCHASE_ORDER.MATCH_CBTPO		N
TSHK PO	SCH_PURCHASE_ORDER.PO_NUMBER		N
Seaway BL NO / CMR NO	SCH_TRIP.SWB		Y
Container No / Trailer No	SCH_TRIP.CONTAINER_NO / SCH_TRIP.VEHICLE_ID		Y
Desc 1	E Book field (to be confirmed)		Y
Carton	SCH_PURCHASE_ORDER.SUB_TRANSIT_DU_QTY		Y
Qty	SCH_PURCHASE_ORDER.BOOKING_DU_QTY		Y
P INV	SCH_ORD_PO_DATA.INVOICE_NUMBER		Y
P INV Date	SCH_ORD_PO_DATA.DOCS_RECEIVED_DATE		Y
Export Licence No	Manual Entry		Y
Cert of Origin No	Manual Entry		Y
POL	GEO_LOCATION.COUNTRY_CODE for source_		N
Dest	GEO_LOCATION.COUNTRY_CODE for destination	N
Unit	E Book field (to be confirmed)	Y
Net Weight	E Book field (to be confirmed)	Y
Gross Weight	E Book field (to be confirmed)	Y
Order Ref No	Manual Entry	Y
Pack Code	Manual Entry	Y
So HTS Code	E Book field (to be confirmed)	Y
PO Unit Price	SCH_PURCHASE_ORDER.UNIT_COST	Y
FCR No	Manual Entry	Y
FCR Date	Manual Entry	Y
Chargeable Weight Air	Manual Entry	Y
Freight Cost Price Air	Manual Entry	Y
Remark	Manual Entry	Y
Export No	Generated	N
Export Send	Y' or 'N' as checkbox	Y
Export Sent	Y' or 'N' updated automatically as checkbox	N
Export Sent Date	Generated	N
Adjust No	??	N
Adjust Send	Y' or 'N' updated automatically as checkbox	N
Adjust Sent	Y' or 'N' updated automatically as checkbox	N
Locked	Y' or 'N' as checkbox	Y

Layout of the screen should be confirmed at functional spec stage.

These columns will all be included in the new table INT_PO_CIC. The table should also include the TRIP_ID for consistency with other tables and screens in the system. Each new record added to the table, when triggering from the trip screens, will be assigned a unique sequence. This will be used when linking the record to the audit information. The sequence will be stored in the column CIC_ID and will be the primary key on the table INT_PO_CIC.

The user that initially triggered the data, from the trip screens, will be stored in a created by field along with the date/time.

The screen will contain a checkbox labelled ‘Locked’ which will indicate if the current record is available for update. Once a user unlocks a record, by unchecking his box, their username will be stored against the record, in a column called USERNAME and used later to identify which records are to be sent.

Once the user has finished making the required changes they will tick the Export Send flag on the line. This will indicate that this record is to be included in the file when generated. The user will be prompted to confirm that the row is to be included and at this point the record will be saved.

The screen will also be accessible through the menus. When accessed this way the user will be able to search the data using the following fields.

• M_REF
• Supplier No – Source
• CBTPO – Matched CBTPO
• TSHK PO – PO number
• Container No
• P_Inv – Invoice Number
• POL – Collection from country
• DEST – Deliver to country
• Export No
• Export Send (Yes or No)
• Export Sent (Yes or No)
• Export Sent Date
• Adjust No
• Adjust Send (Yes or No)
• Adjust Sent (Yes or No)
• Username – will default to current users name and should not be updatable.

Using the above search criteria the user will be able to retrieve the records they wish to send in the file. If the user retrieves records by choosing the Export Sent flag as ‘Y’ the records will not be available to be sent in the file but will be displayed only.

A button will be available that will be labelled ‘Generate’; this will be used to produce the XML file and the export. When pressing this button all records that meet the search criteria and where the Export Send flag is set to ‘Y’ will be included in the file.

A right click option will be available on each record in the screen which will contain an Audit option. Choosing this will display a pop up screen that contains an audit record for the current row. A new table INT_PO_CIC_AUDIT will be created to store this information. The audit table will contain the following columns.

• CIC_ID – CIC id link to INT_PO_CIC table
• CIC_AUDIT_ID – Unique ID for each audit record.
• Created Date – Date of action
• Created By – User who performed the action.
• Audit Message – text description of the action performed.

When a record is changed in the INT_PO_CIC table the field changed along with the old and new values will be stored in the audit message. The user name and time will also be stored. An audit message will also be written when the Export is sent.

Output Files

The XML file will be created at the time the button is pressed and will use the selections in the screen, the xml file will contain a section for each of the records in the interface table that meet the criteria. Once the file has been created the Export Sent flag will be set to ‘Y’ for each record.

At the same time the XML file is created, a CSV file containing the same data will be displayed on the screen. This can then be saved by the user.

An XSD is being created for the new XML message.

Both the XML and the CSV file will contain all the fields displayed on the screen (see above).

The xml file will be placed in the directory listed in the system parameter POM_OUTBOUND_PATH, and a copy kept in the directory listed in the parameter POM_OUTBOUND_ARCH.

Interface Errors

A new tab will be added to the Interface Errors screen. This tab will show

• Filename
• Date and time
• Status – sent or failed
• Trip Id
• PO Reference

There will be a ‘View’ button that will show a details screen for the message, including all the information that has been sent. This will be similar in structure to the existing PO Outbound message.

Scope

This change will be applied to system version 10.6.

Set-up

Data

New Sequence SEQ_INT_PO_CIC to generate unique reference on table INT_PO_CIC.

Functional Description

CIC Screen

A new screen will be created in CTMS called CIC. The screen will be accessible via the PO Detail tab on the trip screens and also via the menus.

Within the Trip Screens PO Tab, a check box labelled CIC will be added to each order line. When selected, this will trigger an update to the fields in the INT_PO_CIC table (see below for details).

A button will be added to the PO Detail tab on the trips screens, labelled ‘CIC’. Pressing this button will call the CIC screen passing through the current trip id. This will retrieve any details that have been added for the PO’s on this trip.

The data in the CIC screen will be stored in the new table INT_PO_CIC. Changes to the data in this screen will not be reflected in the original purchase order tables. The user’s name will be stored in the CREATED_BY field and the date/time in the CREATED_DATE field. For update actions username and current date/time will be added to the UPDATED_BY/UPDATED_DATE fields.

The screen will contain the following information.

Field	Source	Updateable
Trip ID	SCH_TRIP.TRIP_ID	N
M-Ref	SCH_TRIP.BILL_OF_LADING	N
OMS Ref	SCH_ORD.OMS_REF	N
Supplier No	SCH_PURCHASE_ORDER.SOURCE	N
Supplier Name	GEO_LOCAITON.LOCATION_NAME for Source	N
ETD	SCH_TRIP_STOP.ACTUAL_DEPART	N
Vessel	Trail or Vessel	Y
DR	Manual Entry	Y
ETA	SCH_TRIP_STOP.ARRIVE	N
PO ID	SCH_PURCHASE_ORDER.PO_ID	N
CBT PO	SCH_PURCHASE_ORDER.MATCH_CBTPO	N
TSHK PO	SCH_PURCHASE_ORDER.PO_NUMBER	N
Seaway BL NO / CMR NO	SCH_TRIP.SWB	Y
Container No / Trailer No	SCH_TRIP.CONTAINER_NO / SCH_TRIP.VEHICLE_ID	Y
Item No	SCH_PURCHASE_ORDER.ITEM_NUMBER	N
Desc 1	SCH_PO_SUPPLIER_DATA.ITEM_DESCRIPTION	Y
Carton	SCH_PURCHASE_ORDER.SUB_TRANSIT_DU_QTY	Y
Qty	SCH_PURCHASE_ORDER.BOOKING_DU_QTY	Y
P INV	SCH_ORD_PO_DATA.INVOICE_NUMBER	Y
P INV Date	SCH_ORD_PO_DATA.DOCS_RECEIVED_DATE	Y
Export Licence No	Manual Entry	Y
Cert of Origin No	Manual Entry	Y
POL	GEO_LOCATION.COUNTRY_CODE for source_	N
Dest	GEO_LOCATION.COUNTRY_CODE for destination	N
Unit	SCH_PO_SUPPLIER_DATA.BOOKING_DU_TYPE	Y
Net Weight	SCH_PO_SUPPLIER_DATA.NET_WEIGHT	Y
Gross Weight	SCH_PO_SUPPLIER_DATA.GROSS_WEIGHT	Y
Order Ref No	Manual Entry	Y
Pack Code	Manual Entry	Y
So HTS Code	SCH_PO_SUPPLIER_DATA.SPECIAL_INSTRUCTIONS	Y
PO Unit Price	SCH_PURCHASE_ORDER.UNIT_COST	Y
FCR No	Manual Entry	Y
FCR Date	Manual Entry	Y
Chargeable Weight Air	Manual Entry	Y
Freight Cost Price Air	Manual Entry	Y
Remark	Manual Entry	Y
Created By	Populated Automatically	N
Created Date	Populated Automatically	N
Username	Populated Automatically	N
Export	Generated	N
Export Sent Date	Generated	N
Adjust	Generated	N
Locked	Y’ or ‘N’ as checkbox	Y

The screen will contain a line for each purchase order that has been ticked in the trip screens, and each line will be assigned a unique sequence. This sequence will be used to link this table with the audit information and will form the primary key of the table.

When accessing the screen from the trip screens the table SCH_ORD_PO_DATA can be used to retrieve the purchase order details associated with the Trip.

SELECT DISTINCT SOPD.OMS_REF,

SOPD.PO_REFERENCE,

STS.TRIP_ID

FROM SCH_ORD_PO_DATA SOPD,

SCH_TRIP_STOP STS,

SCH_HAULAGE_ACTIVITY SHA

WHERE SOPD.OMS_REF = SHA.OMS_REF

AND SHA.STOP_ID = STS.STOP_ID

AND SHA.ACTIVITY_NAME = ‘Load’

This will give a list of the PO’s associated with the trip and from this the purchase order details can be found in SCH_PURCHASE_ORDER and SCH_ORD_PO_DATA.

The items listed as updatable (in the above table) will be available for the user to make changes. The fields that are not marked as updatable will be display only.

The first field on each row of the screen will be a check box labelled ‘Locked’. When a new record is added to the table the check box will default to ‘Locked’ and no changes will be allowed to the record until it is ‘Unlocked’.

If a user changes this check box to ‘N’ (I.E. un-ticks it) their username will be stored against the record in the column USERNAME. This username will be used it identify which records a user has changed and therefore which records they can send in the outbound file.

The Export and Amend items will be radio groups with 3 values ‘No’, ‘Send’, and ‘Sent’. When the record is initially created the Export and Amend items will default to be ‘No’.

Once this user has finished making any changes to the record they will tick the Export Send item. At this point the user will be prompted to save the changes and this record will be included in the next outbound file to be sent.

The screen will also be available through the Menus. This will require a change to ORION.mmb to add the new menu item. When accessed through the menus the user will need to search for the required data. A header section in the screen will contain the fields that the user can search on. This section of the screen will only be enabled when the screen is accessed from the menus, and will be disabled when coming from the trip screens.

The following fields will be available as search criteria

M_REF	Free Text
Supplier No – Source	Free Text validated against LOV
CBTPO – Matched CBTPO	Free Text
TSHK PO – PO number	Free Text
Container No	Free Text
P_Inv – Invoice Number	Free Text
POL – Collection from country	Free Text validated against LOV
DEST – Deliver to country	Free Text validated against LOV
Export	Radio group – No, Send or Sent
Export Sent Date	Free text validated against date format
Adjust	Radio Group – No, Send or Sent
Username	Defaults to current user – cannot be changed

Using the search criteria the user will be able to select which records will be sent in the XML file and displayed in the Export.

A button labeled ‘Generate’ will be available on the screen. The button will only be available when the screen is accessed from the menu. This button will be used to produce an XML file and CSV export. When pressing this button any records that are currently available on the screen and have the Export or Amend item set to ‘SEND’ will be included in the XML file and the export.

Once a record has been sent in the XML file the Export item will be changed to SENT, and the Export Date will be updated with the current date. At this point the Export item will be disabled.

If the user makes changes to the record once it has been sent in a file, they will be prompted to change the Amend item to ‘SEND’. This will include this item in the next file that is run (providing it meets the search criteria). Once the amended details are sent, the Amend item will be changed to SENT and the Amend Date updated to the current date.

Each row will have a right click option to retrieve the audit information for the CIC record. A new table INT_PO_CIC_AUDIT will be created to store this information. Choosing the CIC Audit option in the right click menu will show a pop up screen that will display the Audit information. This will be similar in structure to the PO Audit tab available in the purchase order screen.

The following columns will be included in the audit table.

• CIC_ID – CIC id link to INT_PO_CIC table
• CIC_AUDIT_ID – Unique ID for each audit record.
• Created Date – Date of action
• Created By – User who performed the action.
• Audit Message – text description of the action performed.

The audit table will contain details of the field changes and the user who performed the change. A trigger will be added to the INT_PO_CIC table that will write a message when a record is changed. When the Export or Amend Item is changed to SEND or SENT a message will also be written by the trigger.

For each audit record the username and date/time of the action will be stored.

As there is a large amount of data to be displayed it is suggested that the CIC screen have a scrolling canvas that will allow a large number of columns to be displayed.

N.B. this is an example layout.

Output Files

An XML file will be created when the ‘Generate’ button is pressed. The file will contain all the records that match the search criteria entered in the screen were the Export or Amend item is ‘SEND’.

The XML file will be put in the same directory as the outbound PO flow, and a copy kept in the archive directory. The destination for these files is controlled by the system parameters :-

POM_OUTBOUND_PATH

POM_OUTBOUND_ARCH

At the same time the XML file is created, a CSV export will also be created and displayed on the screen. A new procedure will be added to the package POM to create the CSV file.

The XML and CSV files will contain the same information. The columns to be included are :-

Trip ID

M-Ref

OMS Ref

Supplier No

Supplier Name

ETD

Vessel

DR

ETA

PO ID

CBT PO

TSHK PO

Seaway BL NO / CMR NO

Container No / Trailer No

Desc 1

Carton

Qty

P INV

P INV Date

Export Licence No

Cert of Origin No

POL

Dest

Unit

Net Weight

Gross Weight

Order Ref No

Pack Code

So HTS Code

PO Unit Price

FCR No

FCR Date

Chargeable Weight Air

Freight Cost Price Air

Remark

Created By

Created Date

Username

All fields will be populated from the INT_PO_CIC table.

Both the XML and CSV files will contain a record / row for each of the INT_PO_CIC records that match the criteria selected in the CIC screen.

A new XSD will be created for the XML flow.

Interface Errors

A new tab called PO CIC will be added to the interface errors screen. The format for this tab will be similar in structure to the current PO_OUTBOUND tab.

All the information on this tab will be retrieved from INT_PO_CIC.

The tab will show :-

• Filename – INT_PO_CIC.FILENAME
• Date / Time – INT_PO_CIC.EXPORT_DATE
• Status – INT_PO_CIC.EXPORT_STATUS
• Trip_id – INT_PO_CIC.TRIP_ID
• PO_Number – INT_PO_CIC.PO_NUBMER
• PO_ID – INT_PO_CIC.PO_ID

There will be a ‘View’ button which will bring up a pop up screen containing all the information that has been sent for the chosen record in the main tab. The information in the detail tab will include all the columns that were sent in the file (see above for details).

Screenshot of current PO Outbound Tab and detail screen.

Table Updates Required

New table INT_PO_CIC

CIC_ID NUMBER (Primary Key)

LOCKEDVARCHAR2(1)

TRIP_IDVARCHAR2(12)

M_REFVARCHAR2(12)

OMS_REFVARCHAR2(12)

SUPPLIERVARCHAR2(12)

SUPPLIER_NAMEVARCHAR2(50)

ETDDATE

VESSELVARCHAR2(12)

DRVARCHAR2(12)

ETADATE

PO_IDVARCHAR2(12)

CBT_POVARCHAR2(20)

TSHK_POVARCHAR2(12)

SEAWAY_BLVARCHAR2(50)

CONTAINTER_NO VARCHAR2(50)

ITEM_NUMBER VARCHAR2(12)

DESCVARCHAR2(100)

CARTONNUMBER(24,4)

QUANTITYNUMBER(24,2)

P_INVVARCHAR2(20)

P_INV_DATEDATE

EXPORT_LICENCE_NO VARCHAR2(50)

CERT_OF_ORIGIN VARCHAR2(50)

POLVARCHAR2(3)

DESTVARCHAR2(3)

UNITVARCHAR2(20)

NET_WEIGHT NUMBER(24,4)

GROSS_WEIGHT NUMBER(24,4)

ORDER_REF_NOVARCHAR2(50)

PACK_CODEVARCHAR2(50)

SO_HTS_CODEVARCHAR2(4000)

PO_UNIT_PRICE NUMBER(12,4)

FCR_NO VARCHAR2(50)

FCR_DATE DATE

CHARGEABLE_WEIGHT_AIR NUMBER(24,4)

FREIGHT_COST_PRICE_AIR NUMBER(24,4)

REMARK VARCHAR2(100)

EXPORT VARCHAR2(12)

EXPORT_DATE DATE

ADJUST VARCHAR2(12)

ADJUST_DATE DATE

CREATED_BY VARCHAR2(40)

CREATED_DATE DATE

UPDATED_BY VARCHAR2(40)

UPDATED_DATE DATE

USERNAME VARCAHR2(40)

FILENAMEVARCHAR2(100)

New Table INT_PO_CIC_AUDIT

CIC_ID NUMBER

CIC_AUDIT_ID NUMBER

CREATED_DATE DATE

CREATED_BY VARCHAR2(40)

AUDIT_MESSAGE VARCAHR2(4000)

Document History

Version	Date	Status	Reason	Initials
0.1	03/05/2011	Draft	Initial version	DNG
1.0	18/05/2011	Issued	Reviewed and Issued	MJC

AUTHORISED BY

Matt Crisford	Development Manager
Peter Greer	TMSCC MTS Product Manager