288465
DHL C-TMS
Enable Report Subscription
FUNCTIONAL SPECIFICATION - 10.6
16/06/2011 - 3.0
Reference: FS 288465 DK-8GHCRU
Functional Overview
Client Requirement
Change Request Summary:
To enable report subscription to take place.
Change Request Details:
There is a requirement for the operation to be able to set up certain reports to be sent automatically from the system on defined days / times.
Benefits identified as a result of the change:
Enable operation to continue working as they currently do.
Solution
The ‘EDI’ page of the ‘Imports’ screen will be changed to allow new flow types of ‘REPORT’, ‘EXPORT’ and ‘ORS’ to be introduced to produce reports at the required interval.
The specific parameters for the report may be stored with values (e.g. ‘SYSDATE – 7’ for the previous week) via the sub screen called by the ‘Params’ button.
Multiple reports may be setup as required, users will be able to define the reports they require.
The report may then be produced by a database job generated for the EDI parameters setup and the PDF or CSV file sent to the output device specified in the report parameters (e.g. ‘FTP’, ‘EMAIL’ or ‘ZETAFAX’).
Comments added 14/06/2011
BS & DK – The report format will need to be selectable as the DOOD report needs to be produced in .XLS format
The selection should be .XLS (version 7) .PDF .CSV .XML
OBS comment – CSV output’s will be converted, in most browsers, to Microsoft XLS compatible data automatically, allowing users to save the document as desired. DK has confirmed that XML output is not required in this change.
BS & DK – the operation will need to be able to use this functionality not only for reports but also for EXPORTS and EXPORT SUITE documents also. Basically for all the reports / extracts / and document requests that have been made by the operation for this project so far. Inclusive of
- DOOD Report
- Failed Shipment Report
- Order Tracking Report
- Carrier Confirmation Report
- Driver Manifest
BS & DK – Please ensure that reports can be set with a start date / time and an end date / time i.e. run 3 separate reports for 3 separate months worth of data, and also that the report subscription can be Removed / deleted.
BS & DK – The subscription should also be able to be set up to run forward i.e. SYSDATE +7
OBS comment – the parameter used to control the number of days accessed by the report will also allow forward days to be configured, the physical output from the system will depend on the data within CTMS, e.g. if there is no data for 5 days in advance of the current date then nothing can be reported.
Scope
This change will be applied to system version 10.6.0 on INDTST and once approved INDPRD.
The format and content of any reports scheduled using this functionality will not be changed.
Set-up
Pre-requisites
None
Menu Structure
‘Unchanged’
Data
- The new flow types of ‘REPORT’, EXPORT’ and ‘ORS’ exist (see Appendix A for a script to run).
- The new EDI records exist for the new ‘REPORT’, ‘EXPORT’ and ‘ORS’ flow types.
- The new ‘EDI_REPORT_VALUES’ and ‘EDI_REPORT_DEVICES’ tables exist.
- The system directory for system parameter ‘REPORT_DIRECTORY’ must exist.
Functional Description
EDI Setup
The ‘EDI’ page of the ‘Imports’ screen will be changed to allow a new flow type of ‘REPORT’, ‘EXPORT’ or ‘ORS’, to be introduced to produce reports, or extracts, at the required interval.
‘REPORT’
‘REPORT’ will cover the reports that may be produced in a ‘PDF’ file format.
The specific parameters for the report may be stored with values (e.g. ‘SYSDATE – 7’ for the previous week) via the sub screen called by the ‘Params’ button. If ‘SYSDATE’ is specified then the actual date will be obtained and used as the parameter.
Multiple reports may be setup as required with separate process names and the users will be able to define the reports they require. Therefore, the same report may be setup to run with different parameters and at different intervals.
For example, the ‘Openfield Drivers Manifest’ may be produced as a separate report with up to seven parameters specified:
The same report may be setup in the ‘EDI tab page with the new ‘REPORT’ flow type:
The report parameters will be set in a new subscreen with the name ‘Process Report Values’ called via the ‘Params’ button; the new subscreen will be similar to the ‘Process Trigger Types’ subscreen shown below:
The ‘Process Report Values’ subscreen will display the following data from a new table called ‘EDI_REPORT_VALUES’ for the process name:
| Parameter | Name | Value |
| PARAM_TITLE | PARAM_NAME | PARAM_VALUE |
The new table will include the following columns:
| Name | Type | Nullable | Default | Comments |
| PROCESS_NAME | VARCHAR2(35) | |||
| PARAM_TITLE | VARCHAR2(50) | |||
| PARAM_NAME | VARCHAR2(50) | |||
| PARAM_VALUE | VARCHAR2(100) |
For example,
| Parameter | Name | Value |
| Carrier Name | CARRIER_NAME | 1 |
| Customer | CUSTOMER | 3 |
| From Schedule | SCHED_FROM | 5 |
| Group Name | GROUP_NAME | 2 |
| Owning Depot | OWNING_DEPOT | 7 |
| To Schedule | SCHED_TO | 4 |
| Trip ID | TRIP_ID | 6 |
| Report | P_REPORT | OPENFIELD_DRVR_MAN.rep |
The ‘Report’ value will be the name of the program to be run for the process name and the ‘.rep’ suffix will identify the compiled program type for a report.
A ‘.csv’ suffix will identify the package and procedure for an extract.
A unique index will be added to this table so that the same parameters may not be entered more than once (i.e. PROCESS_NAME, PARAM_TITLE, PARAM_NAME).
N.B. The ‘Name’ will be used to pass to the report as a parameter so that the value may be recognised correctly for the queries in the report to select the data. The ‘Name’ should, therefore, match the types setup for the report to be run from the ‘Reports’ screen itself.
A button called ‘Devices’ will also be available in the ‘Process Report Values’ subscreen for the medium and recipient addresses to be specified.
A new subscreen called ‘Process Report Devices’ will display the following data from a new table called ‘EDI_REPORT_DEVICES’ for the process name:
| Output Device | Recipient Address |
| PARAM_MEDIUM | PARAM_ADDRESS_VALUE |
The new table will include the following columns:
| Name | Type | Nullable | Default | Comments |
| PROCESS_NAME | VARCHAR2(35) | |||
| PARAM_MEDIUM | VARCHAR2(12) | |||
| PARAM_ADDRESS_VALUE | VARCHAR2(100) |
For example,
| Output Device | Recipient Address |
| FTP | 10.43.0.71 |
| FTP | 10.43.0.77 |
| [email protected] | |
| [email protected] | |
| [email protected] | |
| ZETAFAX | 0151 333 4444 |
This setup would enable the ‘OPENFIELD_DRVR_MAN’ report to be produced and sent to each of the addresses setup for the output devices (i.e. ‘Medium’).
The ‘FTP’ details may be set in the subscreen called via the ‘FTP’ button:
The direction, in the ‘EDI tab page, will be ‘Outbound’ and a specific time may be set to run the report if required.
The following data must be setup before the report may be run:
- Process Name
- Filename Format
- Direction
- Flow Type
- Frequency Type
- Delivery Folder
- Failures Folder
- Archive Folder
- Report
- Output Device
‘EXPORT’
‘EXPORT’ will cover the extracts that may be in a ‘CSV’ file format.
‘EXPORT’ will enable an extract to be produced that would be produced from the ‘Exports’ screen in a CSV file format using the same functionality as the ‘REPORT’ flow type.
However, the ‘Process Report Values’ subscreen will be called ‘Process Export Values’.
‘ORS’
‘ORS’ will cover the extracts that may be in a ‘CSV’ file format.
‘ORS’ will enable an extract to be produced that would be produced from the ‘Oracle Reporting Suite’ screen in a CSV file format using the same functionality as the ‘REPORT’ flow type.
However, the ‘Process Report Values’ subscreen will be called ‘Process Export Values’.
‘ORS’ will enable an extract to be produced that would be produced from the ‘Oracle Reporting Suite’ in a CSV file format.
The ‘ORS’ flow type will only need the header type and the header level to produce the extract since the selection criteria should be setup already in the ‘Oracle Reporting Suite’ screen.
The saved report name will need to be specified and will be taken from the ‘Process Name’.
‘Imports’ Screen
The ‘IMPORTS_EXEC’ form for the ‘Imports’ screen will be changed to process the ‘REPORT’, ‘EXPORT’ and ‘ORS’ flow types differently to the other flow types so that the actual report specified will be run and produced for the specified output device.
When the ‘Start’ button is pressed the data setup will be validated and then the report run.
The list of data in section 3.1 should exist but a check will be included, if the flow type is ‘REPORT’, ‘EXPORT’ or ‘ORS’, to ensure that the ‘Report’ and ‘Output Device’ parameters have been populated, if the relevant data has not been entered an error will be displayed prompting the user to complete the setup data before continuing.
The procedure ‘START_PROCESS’ in the package ‘EDI’ will be called and the type of process to run will be determined and a database job created.
‘EDI’ Package
If the flow type is ‘REPORT’ then the database job will be created using the ‘COMMAND_SQL’ column on table ‘EDI_FLOW_TYPES’ and the process name as the parameter for the specific report.
The database job will then be created for the time interval set, for example:
DECLARE
g_process_name edi_process_header.process_name%TYPE;
BEGIN
g_process_name := 'OPENFIELD_DRIVER_MANIFEST';
MSG_REPORTS_EXPORTS.P_RUN_REPORT(g_process_name);
COMMIT;
END;
If the flow type is ‘EXPORT’ then the database job will be created using the ‘COMMAND_SQL’ column on table ‘EDI_FLOW_TYPES’ and the process name as the parameter for the specific report.
The database job will then be created for the time interval set, for example:
DECLARE
g_process_name edi_process_header.process_name%TYPE;
BEGIN
g_process_name := FAILED_SHIPMENT’;
MSG_REPORTS_EXPORTS.P_RUN_EXPORT(g_process_name);
COMMIT;
END;
If the flow type is ‘ORS’ then the database job will be created using the ‘COMMAND_SQL’ column on table ‘EDI_FLOW_TYPES’ and the process name as the parameter for the specific report.
The database job will then be created for the time interval set, for example:
DECLARE
g_process_name edi_process_header.process_name%TYPE;
BEGIN
g_process_name := 'DOOD_REPORT';
MSG_REPORTS_EXPORTS.P_RUN_ORS(g_process_name);
COMMIT;
END;
No changes will be required in the ‘EDI’ package.
‘MSG_REPORTS_EXPORTS’ Package
A new package called ‘MSG_REPORTS_EXPORTS’ will be created to produce the reports and extracts.
‘REPORT’
The ‘Report’ parameter setup for the process name will be used to define which report to run.
A new procedure called ‘P_RUN_REPORT’ will be created based on the existing procedures in the ‘MSG_CARRIER_REPORTS’ package called ‘CARRIER_WORK_REPORTS’ and ‘P_PROCESS_EACH_CARRIER’ except that it will receive the parameter from the database job (e.g. ‘OPENFIELD_DRIVER_MANIFEST’).
Procedure ‘P_RUN_REPORT’ will need to obtain and pass the parameters setup for the process name to the actual report and then direct the output to the output devices required.
If an output device has not been setup then the default will be obtained from the system parameters:
- REPORT_DIRECTORY
- REPORT_CONNECT_STRING
- REPORT_FLOC
- REPORT_PORT
- REPORT_IP_ADDRESS
- REPORT_SERVER
- SMTP_PRIMARY
- SMTP_SECONDARY
- SMTP_SENDER (the default should the email address not be known)
N.B. All of these parameters must have a value for the report to be produced.
An audit message will be written for each run of the report using procedure ‘WRITE_MSG’ in package ‘ADM’.
Any spaces in the report parameters set will be replaced with ‘%20’ as the ‘HTTP’ call does not accept spaces.
The call to the ‘CARRIER_WORK’ report may be seen below as an example:
t_request := 'http://' || t_servhst || ':' || t_portnum || '/reports/rwservlet?server=' || t_repserv || '&' || 'report=' || t_servflc || '/' || TRIM(t_report_name) || '&' || 'userid=' || t_connstr || '&' || 'destype=FILE' || '&' || 'desname=' || t_filename || '&' || 'desformat=PDF' || '&' || 'p_sched_from=' || p_sched || '&' || 'p_sched_to=' || p_sched || '&' || 'p_carrier=' || t_param_carrier || '&' || 'recursive_load=no';
The ‘t_request’ variable for the ‘OPENFIELD_DRVR_MAN’ report would replace the ‘p_sched_from’, ‘p_sched_to’ and ‘p_carrier’ parameters with ‘carrier_name’, ‘customer’, ‘sched_from’, ‘group_name’, ‘owning_depot’, ‘sched_to’ and ‘trip_id’ with ‘t_report_name’ taken from ’p_report’.
If the report has been created successfully then the output device will be assessed.
‘EXPORT’
The ‘Export’ parameter setup for the process name will be used to define which report to run.
A new procedure called ‘P_RUN_EXPORT’ will be created based on the existing procedures in the ‘MSG_CARRIER_REPORTS’ package called ‘CARRIER_WORK_REPORTS’ and ‘P_PROCESS_EACH_CARRIER’ except that it will receive the parameter from the database job (e.g. ‘OPENFIELD_DRIVER_MANIFEST’).
Procedure ‘P_RUN_EXPORT’ will need to obtain and pass the parameters setup for the process name to the actual extract and then direct the output to the output devices required.
If an output device has not been setup then the default will be obtained from the system parameters:
- REPORT_DIRECTORY
- REPORT_CONNECT_STRING
- REPORT_FLOC
- REPORT_PORT
- REPORT_IP_ADDRESS
- REPORT_SERVER
- SMTP_PRIMARY
- SMTP_SECONDARY
- SMTP_SENDER (the default should the email address not be known)
N.B. All of these parameters must have a value for the export to be produced.
An audit message will be written for each run of the report using procedure ‘WRITE_MSG’ in package ‘ADM’.
Any spaces in the report parameters set will be replaced with ‘%20’ as the ‘HTTP’ call does not accept spaces.
The export may be run using the same method employed in the ‘EXPORT.PROCESS’ program unit in the ‘EXPORTS’ form.
The default path and ‘FTP’ parameters may be obtained from the system parameters should they not be setup in the ‘Process Report Values’ and ‘FTP details’ subscreens:
- MTS_EXPORT_PATH
- MTS_CSV_EXPORT_PATH
- MTS_CSV_IP_ADDR
- MTS_CSV_FTP_PATH
- MTS_CSV_USER
- MTS_CSV_PASSWORD
The file naming conventions will be retained, for example:
‘{FILENAME}_{SYSDATE}.csv’.
If the report has been created successfully then the output device will be assessed.
‘ORS’
A procedure called ‘P_RUN_ORS’ will be created to produce the extract using the ‘ORS’ selection criteria setup in the ‘Oracle Reporting Suite’ screen for the ‘Report’ name received from the database job.
The code in the ‘PRODUCE_XLS_WMS’ procedure in the ‘ORS’ form may be used as a base to build an ‘SQL’ statement to pass to the ‘PRODUCE_XLS’ procedure in the ‘ORS’ package for the ‘ORS flow type.
‘EMAIL’
Appropriate details will be obtained from the system parameter ‘TA_EMAIL_TEXT’: this parameter will be ‘Please find Documentation Attached’, for example.
The message body and header would be created normally in the package ‘MSG_CONSTRUCTOR’ to insert records into the ‘MSG_BODY’ and ‘MSG_MESSAGE’ tables based on the message events using procedures ‘P_GEN_TA_MAN_MSG’ and ‘P_MESSAGE_HEADER’.
However, the records will instead be inserted into these tables using the data in the ‘EDI_REPORT_DEVICES’ table; the header record will have status ‘NEW’.
An existing database job would normally then process these records using procedure ‘P_SEND_MSG’ in package ‘MSG_JOB’ but view ‘V_MSG_DETAILS’ would not select records without an electronic address setup for the message.
The records will instead be processed directly using function ‘F_MAIL_STMP’ in package ‘MSG_PROCESSING’ passing the message body which will contain the generated report.
‘ZETAFAX’
Appropriate details will be obtained from the system parameters:
- TA_FTP_IP_ADDRESS
- TA_FTP_PORT
- TA_FTP_DIRECTORY
- TA_FTP_USER
- TA_FTP_PASS
- TA_PDF_TO_TIFF1
- TA_PDF_TO_TIFF2
- TA_SUB_FROM
- TA_SUB_USER
- TA_SUB_PRIORITY
- TA_SUB_ORGANISATION
A system directory will need to be setup for the system parameter ‘REPORT_DIRECTORY’ to obtain the name of the directory in which to place the report.
The method will be based on the procedure ‘P_PROCESS_EACH_CARRIER’.
N.B. It is expected that only reports with a ‘PDF’ format will be sent via ‘Zetafax’ as occurs at present for individual processing.
‘FTP’
Appropriate details will be obtained from the system parameters:
- FTP_WRITE_LOC (the default should the FTP address not be known)
- OF_SOURCE_SYSTEM_CODE
The method will be based on the function ‘F_CREATE_FILE’ in package ‘MSG_PROCESSING’.
The address values will be obtained from the ‘FTP’ details entered for the report.
Table Updates Required
The new flow types of ‘REPORT’ and ‘ORS’ may be created using the following script:
INSERT INTO edi_flow_types
(FLOW_TYPE,COMMAND_SQL)
VALUES
('REPORT','MSG_REPORTS_EXPORTS.P_RUN_REPORT');
INSERT INTO edi_flow_types (FLOW_TYPE,COMMAND_SQL) VALUES ('EXPORT','MSG_REPORTS_EXPORTS.P_RUN_EXPORT');
INSERT INTO edi_flow_types (FLOW_TYPE,COMMAND_SQL) VALUES ('ORS','MSG_REPORTS_EXPORTS.P_RUN_ORS'); /
The new ‘EDI_REPORT_VALUES’ and ‘EDI_REPORT_DEVICES’ tables may be created using the following scripts:
-- Create table
create table EDI_REPORT_VALUES
(
PROCESS_NAME VARCHAR2(35), PARAM_TITLE VARCHAR2(50), PARAM_NAME VARCHAR2(50), PARAM_VALUE VARCHAR2(100)
);
-- Grant/Revoke object privileges grant select, insert, update, delete, alter on EDI_REPORT_VALUES to MTS_USER; grant select on EDI_REPORT_VALUES to MTS_USER_READ_ONLY;
create public synonym EDI_REPORT_VALUES for EDI_REPORT_VALUES;
-- Add a normal index EDI_REPORT_VALUES_IDX1 for all columns and owner MTS_OWNER; create index EDI_REPORT_VALUES_IDX1 on EDI_REPORT_VALUES (PROCESS_NAME,PARAM_TITLE,PARAM_NAME,PARAM_VALUE)
-- Create table create table EDI_REPORT_DEVICES (
PROCESS_NAME VARCHAR2(35), PARAM_MEDIUM VARCHAR2(12), PARAM_ADDRESS_VALUE VARCHAR2(100)
);
-- Grant/Revoke object privileges grant select, insert, update, delete, alter on EDI_REPORT_DEVICES to MTS_USER; grant select on EDI_REPORT_DEVICES to MTS_USER_READ_ONLY;
create public synonym EDI_REPORT_DEVICES for EDI_REPORT_DEVICES;
-- Add a normal index EDI_REPORT_DEVICES_IDX1 for all columns and owner MTS_OWNER; create index EDI_REPORT_DEVICES_IDX1 on EDI_REPORT_DEVICES (PROCESS_NAME,PARAM_MEDIUM,PARAM_ADDRESS_VALUE)
References
| EST-288465 DK-8GHCRU Enable Report Subscription v1.0.doc | |||
| FS-288465 DK-8GHCRU Enable Report Subscription v1.1.doc |
Glossary
| C-TMS | Calidus TMS |
| CSV | Comma Separated Values |
| FTP | File Transfer Protocol |
| ORS | Oracle Reporting Suite |
Document History
| Initial version | ||||
| Reviewed and Issued | ||||
| Referred with new requirements regarding the inclusion of extracts | ||||
| Updated after comments received from the client in v1.1 to include production of extracts | ||||
| Review of the updated draft document | ||||
| V2.0 Issued to client | ||||
| Update after comments from client added and clarified |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
