Middletong at 09:12, 14 September 2010

2010-09-14T09:12:15Z

← Older revision		Revision as of 11:12, 14 September 2010
Line 65:		Line 65:
	== Table – ‘INT_ RAM_HEADER ==		== Table – ‘INT_ RAM_HEADER ==

	{\|~~Border="1"~~		{\|
	\|Name		\|Name
	\|Type		\|Type
Line 132:		Line 132:
	== Table – ‘INT_ RAM_DETAIL’ ==		== Table – ‘INT_ RAM_DETAIL’ ==

	{\|~~Border="1"~~		{\|
	\|Name		\|Name
	\|Type		\|Type
Line 175:		Line 175:
	Add the following columns:		Add the following columns:

	{\|~~Border="1"~~		{\|
	\|Name		\|Name
	\|Type		\|Type
Line 261:		Line 261:
	\|}		\|}

	b) Table – INT_ POC_POD_ACK (IPPA)		== Table – INT_ POC_POD_ACK (IPPA) ==

	Add the following columns:		Add the following columns:

	{\|~~Border="1"~~		{\|
	\|Name		\|Name
	\|Type		\|Type

Middletong: New page: = 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 f...

2010-09-14T09:10:21Z

New page

= 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 ==

{|Border="1"
|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’ ==

{|Border="1"
|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:

{|Border="1"
|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)
|
|
|
|
|}

b) Table – INT_ POC_POD_ACK (IPPA)

Add the following columns:

{|Border="1"
|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 =

{| Border="1"
| <center>'''Ref No'''</center>
| <center>'''Document Title & ID'''</center>
| <center>'''Version'''</center>
| <center>'''Date'''</center>
|-
|
|HHTTRIPTRK File Format – 140508.xls
|
|
|}

= Document History =

{| Border="1"
| <center>'''Version'''</center>
| <center>'''Date'''</center>
| <center>'''Status'''</center>
| <center>'''Reason'''</center>
| <center>'''Initials'''</center>

|-
| <center>1a</center>
| <center>23/04/08</center>
| <center>Draft</center>
| Initial version
| <center>CBW</center>

|-
| <center>1b</center>
| <center>09/06/08</center>
| <center>Draft</center>
| Change to re-processing
| <center>JAT</center>

|}

= Authorised By =

{| Border="1"
| '''''Dave Meir'''''
| Development Manager
|

|-
| '''''Suk Sandhu'''''
| TMSCC MTS Product Manager
|

|}

250104 - Revision history

Middletong at 09:12, 14 September 2010

Middletong: New page: = 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 f...