250104

250104 SW-7EJC8K Revenue Accounting Module (RAM)

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

File extract of all POC and POD records which have had a successful acknowledged from SAP. One file is to contain all the POC records and the other will contain the POD records. This is extract is to run once a day at 12 noon and will include the previous days acknowledgments and relevant POC/POD data. A new TAB page, on the INT_ERR screen, is also required to display the file header details and the detail breakdown of records sent.

Solution

•The trip Actual data will be written when the outbound message is sent and held on the Table INT_ SAP_POC_OUTBOUND.

•The individual acknowledgment records will be assigned a unique id to allow comparison to the outbound records.

•A new procedure will be written to extract the data comparing the acknowledgment received in table INT_ POC_POD_ACK and matching the individual records sent from INT_ SAP_POC_OUTBOUND.

•Records will be written to a file, each day after 12 noon, for collection by ESI.

•The file records will be viewed via the INT_ERR screen which will display totals sent in the file and also the details for each booking reference.

Scope

This change will be applied to system version 10.4.7

FUNCTIONAL DESCRIPTION

EDI Interface – ‘RAM’ Message

A new outbound EDI flow will be developed for the new ‘RAM’ message (i.e. ‘Revenue Accounting Module’) that will be used to insert records of successful POC/POD acknowledgements and collection/delivery information relevant to the booking_ref received.

This will require new tables and forms to be developed as follows:

1) Table – ‘INT_RAM_HEADER’
   This table will contain the data to process the header line of the ‘RAM’ files - details regarding the table setup, may be seen in Appendix A.
2) Table – ‘INT_RAM_DETAIL’
   This table will contain the data to process the detail lines of the ‘RAM’ files - details regarding the table setup, may be seen in Appendix A.
3) Form – ‘INT_ERR’ (Interface Errors)
   A new tab page called ‘RAM’ will be added to the existing ‘Interface Errors’ form shown below:
   A database run job will need to be added for the user to upload the ‘RAM’ file at a set interval.

Output File specifics

The output file is expected to have the file name format;

HHTTRIPTRK_SYSDATE in the format DDMMYYYYHHMISS which represents a sequence number for the file created.

The file will contain header information and detail line information (see example on Appendix C).

Change to the ‘PROC_POC_POD package

Changes will be required within the existing functionality within the database package PROC_POC_POD, to store the details of POC/POD. This will involve changing the existing table INT_SAP_POC_OUTBOUND, see details Appendix B.

Change to the ‘INT_MSG’ package

Changes will also be required within INT_MSG, to store the sequence of the individual records received in the acknowledgment file, this will involve changing the existing table INT_POC_POD_ACK, see details on Appendix B.

APPENDIX A: NEW TABLES REQUIRED

Table – ‘INT_ RAM_HEADER

Name	Type	Nullable	Default	Storage	Comments
INT_RECORD_ID	VARCHAR2(100)	N
MTS_OPERATION	VARCHAR2(10)	Y
MSG_TYPE	VARCHAR2(12)	Y	RAM
NUM_POC	NUMBER	Y
NUM_POD	NUMBER	Y
CREATED_DATE	DATE	Y	SYSDATE
CREATED_BY	VARCHAR2(40)	Y	USER
SEQ_NUMBER	NUMBER	N

Table – ‘INT_ RAM_DETAIL’

Name	Type	Nullable	Default	Storage	Comments
INT_RECORD_ID	VARCHAR2(2000)	Y
SEQ_NUMBER	NUMBER	Y
IPPA_ID	NUMBER	Y
ISPO_ID	NUMBER	Y

APPENDIX B: CHANGES TO EXISTING TABLES REQUIRED

Table – INT_ SAP_POC_OUTBOUND (ISPO)

Add the following columns:

Name	Type	Nullable	Default	Storage	Comments
TRIP_ID	VARCHAR2(2000)	Y
STOP_ID	NUMBER
STOP_TYPE	VARCHAR2(2)
ACTUAL_ARRIVE	DATE
ACTUAL_DEPART	DATE
DU_TYPE	VARCHAR2(12)
DU_QUANTITY	NUMBER(24,4)
REASON_CODE	VARCHAR2(12)
CARRIER_ID	VARCHAR2(12)
SO_POD_NAME	VARCHAR2(50)
SCHED_NAME	VARCHAR2(12)

Table – INT_ POC_POD_ACK (IPPA)

Add the following columns:

Name	Type	Nullable	Default	Storage	Comments
ID	NUMBER	Y

APPENDIX C: EXPECTED OUTPUT FILE LAYOUT

1. File name format will be as follows HHTTRIPTRK_SYSDATE in the format DDMMYYYYHHMISS which represents a sequence number for the file created. The file will contain a header section in the format

<RECHEADSTART>SARLV,RAM,2<RECHEADENDC>

<RECHEADSTART>	=	Start of header Line
SALRV 			=	Database Name
RAM			=	Identifier
2				=	Number of records on file
<RECHEADENDC>	=	End of header Line

2.The body of the file should be as follows;

<HHTSTART>MAN-00297931,1251303,PK,R,02/06/2008,10:45,4502408266000010001,10,PIPE,,14:00,8758455<HHTEND>
<HHTSTART>MAN-00297912,1251224,PK,R,02/06/2008,08:55,4502375106000040001,12,BUNDLE,,12:50,<HHTEND>

<HHTSTART>			=	Start of detail Line
MAN-00297931		=	Trip_id
1251303			=	Stop_id	this will be the stop_no
PK					=	Stop_type
R					=	User_type
02/06/2008			=	Actual_arrival_date
10:45				=	Actual_arrival_time
4502408266000010001	=	Booking_ref
10					=	Du_quantity
PIPE				=	Du_type
Empty field          =	Reason_code (Will this ever be populated ??)
14:00				=	Actual_depart_time
8758455			=	Badge_number
<HHTEND>			=	End of detail Line

References

Ref No	Document Title & ID	Version	Date
	HHTTRIPTRK File Format – 140508.xls

Document History

Version	Date	Status	Reason	Initials
1a	23/04/08	Draft	Initial version	CBW
1b	09/06/08	Draft	Change to re-processing	JAT

Authorised By

Dave Meir	Development Manager
Suk Sandhu	TMSCC MTS Product Manager