FUSITSU
FUIITSUSERVICES.
Document Title:
Document Type:
Release:
Abstr
Document Status:
Originator & Dept:
Contributors:
Internal Distribution:
External Distribution:
Approval Authorities:
TPS HR SAP Summarisation & Transaction
Corrections HLD
COMMERCIAL IN CONFIDENCE
Ref: EA/HLD/009
Version: 2.02.0
Date: 15/1024/11/04
TPS HR SAP Summarisation & Transaction Corrections HLD
High Level Design
S80
This document describes changes to TPS to generate feeds for
HR SAP, POL MIS and CTS. It also describes changes to handle
the passing of Transaction Corrections from POL FS back to the
counters.
DRAFFAPPROVED
Peter Ashdown
Peter Jobson, APDU
Sudhanshu Agrawal, APDU
Name Position Signature Date
Dave Johns Design Manager, APDU
Gareth Jenkins Impact Design Authority
RASD
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE
Page: 1 of 120
FUJ00090327
FUJ00090327
FUSITSU
FUIITSUSERVICES.
TPS HR SAP Summarisation & Transaction
Corrections HLD
COMMERCIAL IN CONFIDENCE
0. Document Control
0.1 Document History
Ref: EA/HLD/009
Version: 2.02.0
Date: 15/1024/11/04
Version No. I Date Reason for Issue Associated.
CP/PinICL
ou 25/05/04 Initial issue.
02 07/07/04 Changes in response to review comments.
Addition of missing sections.
10 28/07/04 Further feedback from second review
Document submitted for approval
1 20/09/04 ‘Changes following feedback from developers and.
further review comments
12 15/10/04 Minor changes P3843,
20 24/11/04 Minor corrections
Document submitted for approval
0.2 Review Details
Review Comments by
July 19" 2004
Review Comments to : I Originator
Mandatory Review Authority Name
RASD Gareth Jenkins (*)
Development/Design Andy Seott
Dave Johns.
Duncan Macdonald
Mark Ascot
Peter Jobson (*)
Rex Dixon
Roger Bames
Sudhanshu Agrawal (*)
Trevor Leahy
ITU Janusz Hollender
Neil Gormley
APDU Test Harjinder Hothi
Optional Review / Issued for Information
Development/Design
Nick Lawman
Roger Donato (*)
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE
Page: 2 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAHLDI009
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
(*)= Reviewers that retuned comments
0.3
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 3 of 120
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Associated Documents
Reference Doe Vers [Date I Title Ste
TPS_AGENT_HLD I EA/HLD/010 TPS Agent HLD
TPS_AGENT_DES _ I AD/DES/047 TPS Tables and Mappings for
CSR+
GEN_AGENT SPE I AD/SPE/006 ‘Agents Generic Database Table
Interface Specification
AIS_ DWH DW/FS/021 MIS BI3 TPS Application Interface
Specification
DP EA/DPR/004 IMPACT Release Design
Proposal
TPS_POLFS HLD I BA/HLD/007 TPS POLFS Summarisation HLD
MIGRATION EA/HLD/008 IMPACT Release 3. Migration
HLD
AIS_POLFS BAMIFS/002 POL FS to _‘TMS/Horizon
Transaction Corrections Interface
Spee
Als_CTS EAMIFS/005 Horizon to Client
‘Transmission Summaries AIS
AIS_POLMIS BA/IFS/006 Horizon to POL Data Warehouse
AIS
‘AIS_HRSAP EAMIFS/015 Horizon to HR SAP System AIS
AIS_FRTS NBMFS/012 Bureau de Change Transaction
Feed for FRTS
LLD_TPSC201 PI/LLD/023 ‘TPSC201 — Produce TIP Files
REF_DATA RDIDES/056 Reference Data E2E HLD for $80
‘AIS_RDDS RD/IFS/018 RDMC/RDDS to TPS AIS
FIMS TD/ON/005 FTMS Configuration for Pathway
‘TPS to POCL TIP Links
‘TPS_HLD TUDES/002 TPS High Level Design
‘TPS_OPS TUMAN/002 TPS Operations —ManualHlost
iupport Guide
Unless a specific version is referred to above, reference should be made to the current approved
versions of the documents.
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE
Page: 4 of 120
FUJ00090327
FUJ00090327
FUSITSU
FUIITSUSERVICES.
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
Corrections HLD Version: 2.02.0
‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
0.4 Abbreviations/Definitions
0.4.1 Abbreviations
Abbreviation I Definition
AIS Application Interface Specification
BDC Bureau de Change
BCT ‘Bureau Control Totals (ype of file which POL forwards on to FRTS)
BLE Branch Ledger Fairy
BID ‘Bureau Transaction Data (ype of file which POL forwards on to FRTS)
CAP Cash Account Period
CBDB Counters Business Database (Wo be replaced by POL FS at S80)
HPO Company Franchised Post Office eg, Tessos
These must be differentiated in feed to HR SAP as remuneration of CFPOs and SPSOs is different.
cs Client Transmission Summaries
Cir ‘Counter Transaction Timings
DWH Data Warehouse. In the context of this document, this refers to the warehouse which is operated by Fujisu
Services, partly for the monitoring of Service Level Agreements
EDG Fleetronie Data Gateway (POL)
EFT Electronic Financial Transaction, Now referred to a8 Debit Card Transaction. EFT is sll used to identify ono oF
the transaction types within TPS.
BY Fnvironment Variable
‘This i a UNIX variable which is local to a user and is typically used to identify directory paths The EV name is
nomnally prefixed by aS” e.g, STIP_OUTPUT
FIMS File Transfer Management Service —A Fujitsu Services service that supports reliable fle transfers to and from
POL
FRIS First Rate Travel Serves; handle Bureau de Change transactions for Post Ofice Lid
HR SAP ‘The SAP System used by Royal Mail Group's Human Resources to pay sub-postmasters
MST ‘Management Support Uni
RDS New Reference Data System ~ POL syatem which provides ref data or RDMC
NWB Network Banking
OFnP ‘Operational TIP Gee TIP)
orx Facompasses all Post Office counter transactions with a customer, hat is, EPOSS, APS, OBCS, NWB, EFT &
BDC wansactons
PIVOT Post Office Volumes of Transactions
‘This is a POL database holding volumes and values of Monthly Sunnmarised Produet data, Current source of HR
SAP retmuneration data and i being replaced by data generated by TPS at S80
POLTS Post Office L's Financial System
POL MIS I Post Office [id's Management Information System. This receives daly feeds of wansastion data
DDS Reference Data Disibution Service, Host database application which Feeds "sol data to Counter Appliations
and to TPS. Also used to identify the database itself.
© 2004 Fujitsu Services COMMERCIAL IN CONFIDENCE Page: 5 of 120
FUJ00090327
FUJ00090327
FUSITSU TPS HR SAP Summarisation & Transaction Ref: EA/HLD/009
Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
RDMC Reference Data Management Centre, Host database application hat works as a Font end to RDS. Also used to
idem the database itself.
BMG Royal Mail Group
3P50 Seale Payment Sub-Pow Office, There are 150007 ossurances
TP Transaction Taformation Processing —@ POL system which & being removed at S80, This system i nonmally
referred to as OPT.
TMS “Transaction Management System, Originally this was the part of Ruposte that handled the messaging between the
Post Offices and the Correspondence Servers. However, within Impact it is used to refer tothe component that
interfaces to POL,
TPS “Transaction Processing System, Prior to S80, ie role of Wis application was To colle wanssction formation
‘and send it to MIS and TIP. At $80, its role is considerably broader.
0.4.2 Definitions
Tom Definition
‘Agent “A component of the Horizon architecture, which links lost Systems o Riposte, There are two basic sorts —
Harvester Agents and Bulk Loader Agents.
Note that itis used in other documents to refer to a person working in a Branch. In this document it is solely
used to refer to the Horizon System component.
Branch ‘The term used for any Post Office whether operated direelly by POL or on their behalf by a sub-postmaster
In the past the term Outlet was used
Statement
Branch — Trading I A report providing a summary of what has happened within a Branch during a Trading Period
Bulk Loader
‘A software Agent that transfers data from a Hlost System to Riposte
Business Day
Ta the context ofthis document, this is the Maestro Business Day, which is defined as fam on Day 1 to Sam
‘on Day 2.
Clerk “Aperson working ina Branch who uses Horizon
Counter ‘The terminal used by a clerk when interacting with Horizon, Note that there are also “back office” counters
in some larger branches which are purely for administrative functions.
Daia Centre ‘The Central Systems run by Fujitsu Services in their Data Centres of Bootle and Wigan,
Deferment Th the context of IR SAP, this is the delay between the receipt ofthe transaction and inclusion ofthe data in
a feed file to HR SAP.
Desktop “The software that provides the interface to Horizon for a Clerk
Tad of Day Td of Day i defined as aking place 30 mins aller the scheduled closing time (in Reference Data) for @
Branch or 19:00 whichever is earlier.
Horizon has a mechanism whereby background processes can be triggered to operate in the Branch at the
End of Day time, for example to trigger the harvesting of that day's transactions.
Tror Notice ‘A manal mechanism by which the Central Post Office Lid accounting fanetions can Fequest correstions are
rade to branch accounts following various errors. This is to be replaced by Transaction Corrections at S80.
FAD Code Unique identifier for a Branch (FAD = “Financial Accounting Division")
Harvester ‘A software Agent that transfers data from Riposte to a Host System
Torizon ‘That part of Post Office Lid's System developed and operated by Fujitsu Services
Tost “A component of the Horizon architecture that supports databases and database processing,
Huihwaite The location of Post Office Lid's Data Ceatre
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 6 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
FUJITSU TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Tem Definition
Maestro The Tob Scheduling software used within Horizon
Messagesiore I The storage mechanism used by Riposte
Mode Tentifies the business finetons under which a counter wansaction is being eared ut
Guile ‘No Tonger used. This tenn has now boon replaced by Branch
Pivot Subsystem wihin CBDB, which operated by Prism, on behalfof POD, Is vole isto generate HR SAP ood
files and itis being replaced by new functionality in TPS at S80.
Pesta Th person responsible fora Branch,
Prism Alliance I The organisation that & responsible for the development, option and support of Royal Mail Group's
central systems including those of Pst Office Li.
Product Someting thats tansncted at a counter, Products may be Stock products or Service products
Reference Data I A mechanism by which parameter values are held outside code in sich a way that they can be easily updated
through defined processes
Sub-File “Tat part of @ fle of data that holds the data associated with a sagle Branch’s Wading on a angle Trading
Day,
Suimmarisaion I This the process of taking the information fom a set oF tamsactions and producing a summary total ofthe
overall net effet of such transactions
Trading Day I The accounting day within a Branch, Tis defined asthe period between two successive Endo Daye
Auy transactions that take place after dhe end of the Trading Day are considered to be pat of the following
‘Trading Day.
Transaction “Tiis represents an EPOSS Transaction written to the Raposte mewage sore
The sum ofthe sale values of all Transactions within a Customer Session wil always be zero thus enabling
normal double entry book-keeping to be carried out
Transaction “Ka automated mechanism by which the Central Post Office Led accounting functions can request comestions
Correction are made to branch accounts following various error This replaces Error Notices at S80.
0.5 Changes in this Version
Version Changes
420 ‘The main changes in this version are:
1. HR SAP Summarisation
in FPSC207, wh ference: dete from RODS, if peebl ie
if current file sequence is 9999, set number back to 0001_—then-just-ousput
2. Transaction Corrections
~raise alert if no file received for current Business Day (CP3843 — CR276)
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 7 of 120
FUJITSU
FUIITSUSERVICES.
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
Corrections HLD Version: 2.02.0
‘COMMERCIAL IN CONFIDENCE Date: 1/4024/11/04
= change validation performed for Error Code 042
a Gonvitions
add exiza-validation-io-checkethat:
oe for mode-ids 91-0nd.92
——_b) enor qiy- is zero for-other mode ids
0} value is zere-for mode ids 91and 92
4 cecal Clee eer ft
33, POL MIS Data Feed
E e-new = =
for yy get the-¢ Scone purchased-qi field
fort field-wh flee
e £ EPOS: by-filtering. listel
produces in-anewereference table
add 2 news coltnns io FMS_RX_BPOSS_EVENTS tables io-captire
Jongth of, d-field bert
——=change interface to POLMIS.
a) CP3843 — CR272
b) to remove cash_account_period, cash_account_day and balance_period -
these were omitted when AIS was created, but change was not picked up
4. Migration
~ identify harvester table whose constraints are being added at Point 104-CFS
——?eL
SR £C —
pH Reconeil Point}
< a
iii dote-te-the Me edule (at Point +
FPS-FACT_DATA)
jaiosst lean! saanamerincunt by rosder haat
Pallgwiile subaes feel “FPS -FILE_REGISTER
defi ‘ Point 10-and Point 40
defines. soc rrr es
F-General
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 8 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref:
FUJITSU Corrections HLD Version:
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date:
0.6 Changes Expected
EA/HLD/009
2.02.0
45/1024/11/04
1) signs in HR-SAP summarisation
Both isques-are-aweiting-«reeponse-from POL
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 9 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EA/HLD/009
FUJITSU Corrections HLD Version: 2.02.0
idinseenne’ COMMERCIAL IN CONFIDENCE Date: 15/1024/14/04
0.7__ Table of Contents
0. DOCUMENT CONTROL... 2
0.1 Document History ©
0.2 REVIEW DETAILS... 22
0.3 ASSOCIATED DOCUMENTS .... 3,
0.4 ABBREVIATIONS/DEFINITIONS. 4
04.1 Abbreviations... mer
04.2 Definitions nn aa
0.5 CHANGES IN THIS VERSION 6
0.6 CHANGES EXPECTED. Ls
0.7___ TABLE OF CONTENTS eT
1__INTRODUCTION. 10
2.__SCOPE...
3.__ ARCHITECTURE
4. DESIGN SPECIFICATION
4.1__ FILE TRANS
4.11 From TPS.
411] FIMS Services
4112 NES Mounts
41.2 TOTPS. ncn
4121 NFS Mounts
4.2 HR SAP TRANSACTION SUMMARISATION AND DATA FEED.
42.1 Overview.
42.2 Code,
4201 New Module nseesnnsnnnnn
4221 TPSC280 Summarise HR SAP Data
422.12 —TPSC2&L Create HR SAP File
4 Thanged Modules sossssssssssssssssssss
4222.1 — TPSC211 Star of Day.
4222.2 TPSC210 Harvest Receipt Info
42.223 TPSC206 Create Delivery File.
4222.4 TPSC207 Harvest Branch Info fiom RDS.
4223 Common Routines
42231 SutxportFile
4223.2 — SuniExportIRSAPFile,
4.2.3 Performance Considerations
4231 Partitions and Indexes
4.23.2 Tablespaces
42.4 Volumetrics... ni
424] Main Assumptions
4242 Tables size
4243 File sizes
42.5 Housekeeping.
425] Duabase
4252 Filestoe =
4.3. TRANSACTION CORRECTIONS.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 10 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EA/HLD/009
FUJITSU Corrections HLD 207
idinseenne’ COMMERCIAL IN CONFIDENCE Date: 15/1024/14/04
43.1 Overview...
43.2 _ Error Handling
31
4321 Overview,
43. Errors Cod
43.3 Code...
4331 New Modules...
432.11 TPSC28 TC Pre-Process
433.12 —TPSX283h TC Initial Load
4331.3 TPSC284-TC Load TMS Table
433.14 IPSC285 TC Create Enor Fil
43315 TPSC286 TC Find Job
43.4 Volumetrics. 7
43.41 Tables $1208 s.:ssssssnsssssnansn
4342 File ses.
43.5 Housekeeping.
4351 Database
435.2 Filestore
4.4 POLMIS Data FEED.
4a] _ Overview...
4411 Changing format of files sent to POLMIS
44.1.2 Suppression of settlement transactions
44.13 Preventing new events being sent to TIP
44.1.4 Harvesting additional event information,
44.15 Migration
442 Code.
442.1 New Modules e =
4.42.1,] _TPSC287 Produce POLMIS Files.
442.2 Changed Modules
4422.1 TPSC201 Produce TIP Files.
4422.2 TPSC229 Strip out errored Sub-files & return rest of file to TIP...
4422.3 TPSC240 Load comected ‘errored Sub-files’ into TPS.
442.24 TPSC232_ Create TIP OTX Resend File ’
4.5 FRTS DaTa FEED..
452.1 Changed Modules.
452.11 — TPSC271 Generate Bureau Feed...
4.6 CLIENT’ TRANSMISSION SUMMARIES DATA FEED
4.62.1 Changed Modules.
‘TPSC223 Create CTS File
472.11 TPSC225 Create TIP CAC/STY files
42212 — TPSC205 Drop lemarnr la end sem And Bonn of Tes
4.7.2.1.3 — Cash Account Processing Modules.
473 Schema. Pz
‘473.1 Tables tobe identified as redundant
5.__ MIGRATION
5.1 OVERVIEW .. "
5.2___ MIGRATION PREPARATION -
5.3 Horizon DATA CENTRE. MIGRATION...
53.1 _ Migrate RDMC/RDDS
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 11 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
FUITSU SERVICES ‘COMMERCIAL IN CONFIDENCE Date: 1/4024/11/04
$3.3 Install TPS Host Software. 68
53.4 Enable new Maestro Schedules. 69
53.5 Create Tablespace 70
53.6 Schema Migration 70
536] Create now ables 70
53.62 Create new sequences 1
53.463 Change definition of existing tables. I
53.64 Populate new TPS reference tables n
$3.65 Truncate redundant Cash Acooust tables 2
$3.66 Update me
5.3.6.7 Change Role Definition...
53.6.8 Create User
53.6.9 Check Constraints
5.3.7 Change environment of TPS Users
S371 Create Host Directories
53.7.2___ Environment Variables.
53.8__Enable the new FTMS services
5.4 START POLFS SUMMARISATION
5.5 FINAL CBDB Casi ACCOUNT... 7 "
5.6 SWITCH OFF FEED TO TIP AND START REPLACEMENT FEEDS at sanoernmcare 0)
5.7___ SwiTcH OVER HR SAP EXTRACTION FROM CBDB To TPS.....
6.__APPENDIX 1: DATABASE SCHEMA...
6.1 FACT TABLES...
‘6:11 HR SAP Tables... "
6.1.2 Transaction Correction Tables.
6.1.3 Other Tables.
6.2 __ REFERENCE TABLES..
62.1 HR SAP Tables.
6.2.2 Transaction Correction Tables
62.3 Other Tables.
6.3 CONTROL TABLES... °
‘63.1 Transaction Correction Tables
63.2 General Tables...
6.4 CONTROL DATA..
1. APPENDIX 2: MAESTRO SCHEDULER...
8.__APPENDIX 3: DOCUMENTS AFFECTED
8.1 CHANGED DOCUMENTS.
8.2 WITHDRAWN DOCUMENTS
0._DOCUMENT- CONTROL =
Q1- _ Doce sent Hh
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 12 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EA/HLD/009
FUJITSU Corrections HLD Version: 2.02.0
idinseenne’ COMMERCIAL IN CONFIDENCE Date: 15/1024/14/04
4,_INFRODUCTION. Pri
2, SCOPE 4
ARCHITECTURE
4, DESIGN SPECIFICATION 13
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 13 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
FUITSU SERVICES ‘COMMERCIAL IN CONFIDENCE Date: 1/4024/11/04
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 14 of 120
TPS HR SAP Summarisation & Transaction Ref: EA/HLD/009
FUJITSU Corrections HLD Version: 2.02.0
idinseenne’ COMMERCIAL IN CONFIDENCE Date: 15/1024/14/04
~ Get: —HRSAP Tables
6.1.2 —Transaction Correction Tables. . . ee rere BB
6.1.3 —Other Tables eee 88
2B 5, 99
© 624 —HR-SAP Tablet 49
(Sled — Transaction Goereotiow-Tablet renner eenreeeenerreemrntrrmerrer SH
+,
63:1 Transaction. Correction Tables...
8, APPENDIX. 3: DOCUMENTS AFFECTED.
1
8.2 WITHDRAWN DOCUMENTS
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 15 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
1. Introduction -
Prior to $80, TIP provided the interface between TPS and other processes within POL. In some cases it
merely passed the TPS generated files onto a 3 party e.g. bureau transaction files are passed onto
ERTS. In other cases, TIP generated files for onward transmission using the transaction data provided
by TPS e.g. Cash Account data for CBDB, which in turn generated remuneration data for HR SAP.
One of the major changes in $80 is the removal of TIP and CBDB from the POL arena. Therefore
processing previously performed by TIP, which is still relevant, is being absorbed into TPS or moved
elsewhere. Also new functionality is being introduced which must also be handled by TPS, such as the
processing of Transaction Corrections being passed from POL FS back to the counters,
1.2. Scope
This document describes changes to TPS to:
* generate a new feed file for HR SAP
© process Transaction Corrections provided by POL FS, passing the corrections down to the
counters,
© pass the transaction files directly to POL MIS and perform several minor changes to the files
eg. inclusion of extra transaction data
© pass the bureau files directly to POL, for onward transmission to FTRS, and cater for the
removal of Cash Account Period
© pass the Client Transaction Summaries directly to POL
© remove all processes connected with Cash Account reconciliation and handling of Balance
Periods
* copy related Reference Data from RDDS.
The document does NOT cover:
© details of the Bulk Loader Agent, which loads Transaction Correction messages into the
message store, The details of new agents for this development are in TPS Agent HLD
[TPS_AGENT_HLD] and TPS Tables and Mappings [TPS_AGENT_DES]
© the generation of Branch Ledger Entry statements for passing to POL FS. This is described in
‘TPS POLFS Summarisation HLD (TPS_POLFS_HLD)
© details of the file transfer process. Details of all the new FTMS Services will be covered in
FITMS Configuration for Pathway TPS to POCL TIP Links [FTMS]
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 16 of 120
FUJ00090327
FUJ00090327
Formatted: Ble and Nanberng }
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
2.3. Architecture
Prior to S80
3rd Party
Rot, CBDB I——___»I HR SAP. POL MIs
Gash Accounts eg
Bureau Files
TN Files ~
Fujitsu r= $nip_outPuT APS Summary
Pours I, #0 Bureau Fes
Fs. (Cash Acounts
“OUTPUT NES)
“I TPs NSOUTPUTINFS) DWH
1
possum
Counters
‘rd Party FRTS
ry
POL POL Client HR SAP POL MIS EDG
Settiement
¥ f Al ‘
APS Summary HR SAP Files TAN Files Bureau Files
SHRSAP_OUTPUT SFRTS_OUTPUT
Fujitsu
‘SMIS_OUTPUTINFS) DWH
2
Counters
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 17 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
3.4. Design Specification
344.1 File Transfers
3444.1. From TPS
S-beb4.1.1.1_ FIMS Services
Prior to S80
EV [TPS Host Directory Target I Ichar I FIMS I FIMS IFTMSFail
System I abbrev I Pickup I Success I NAK File
File I ACK File I Extension
Extension I Extension
STIP_OUTPUT Tovaw0l/tps/trans/tip TIP T I *IP_pzI *1PR I *1PX
S80
With the removal of TIP, TPS becomes responsible for transferring files to more destinations, as
summarised below:
EV TPS Host Directory Target] char I FIMS I FIMS IFIMSFail
System I abbrev I Pickup I Success I NAK File
File I ACK File I Extension
Extension I Extension
STIP_OUTPUT Tovaw0l/ips/transipolmis POL MIS] T I *1P_pzI *1PR I *TPX
S$HRSAP_OUTPUT /bvnw01/tps/trans/hrsap I HR SAP Ss * hrp.pz *HRR * HRX
$CTS_OUTPUT 7ovaw0l/ips/trans/ets POLCTsI *TP_pz I *CTR *CTX
SERTS_OUTPUT _I/ovaw0l/ips/irans/frts FRIS I F I*BIDpz[ *FRR I “FRX
*.BCT.pz
The new FTMS services required will be described in FTMS. Note that the existing “TIP files” are
transferred to a new directory (.../polmis), but the EV remains the same (STIP_OUTPUT). Also we
will never receive NAK files from HR SAP, or CTS (and probably not for FRTS).
3.4:4.24.1.1.2_ NFS Mounts
Note that this section does not describe NFS mounts, which are created as part of the implementation
of an FTMS service or are used by the Audi/SSC Archive Servers. It just describes mounts, which are
used in isolation to make files available on remote systems. There are no changes between pre-S80 and
$80 — the following is included just for completeness.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 18 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
alien ‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
S80
EV TPS Host Directory Target System
SPOLFS_SHARE Tovaw0/ips/trans/polfs_share POL FS
SMIS_OUTPUT Tovaw0W/ipsiirans/mis DWH
34.24.1.2 To TPS
3-he244.1.2.1_ NFS Mounts
Prior to S80
‘There are currently no NFS Mounts used for transferring files from POL to TPS
S80
EV ‘TPS Host Directory Source
System
SPOLFS_INPUT SHARE I /bvnw0l/tps/trans/polfs_input_share I POL FS
3.24.2 HR SAP transaction summarisation and data feed
3.24.
HR SAP is a system run by RMG to pay Postmasters. It is fed by summaries of value and volume of a
subset of transactions, which are the basis of the Postmaster’s pay. Prior to S80, the HR SAP feed was
generated by CBDB, using data supplied by TIP. At S80, TIP is removed and TPS must therefore
assume responsibility for the generation of the HR SAP feed.
Overview
‘There are two main criteria in construction of the HR SAP feed:
© the delivery schedule
¢ the deferment between receipt of the transaction and delivery of the data
The delivery schedule is determined bythe branch, using firstly table
TPS OUTLETS to define the mapping from branch to HR SAP Group and secondly table
‘TPS_HR_SAP_SCHEDULES to define the mapping from HR SAP Group to period and delivery date.
‘The different classes of branch are identified by their HR SAP Group. There are 3 classes of branch:
a) SPSO - their data is delivered monthly according to one schedule (HR SAP Group-Pivot)
b) CFPO- their data is delivered monthly according to another schedule (HR SAP Group-Pivot2)
c) Directly Managed Branches - their data is NOT sent to HR SAP (HR SAP Group=null)
‘The deferment for an HR SAP Group is determined by the product/transaction mode pair. Note that
the deferment is the gap between receipt of the transaction and inclusion of the data in a feed file to
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 19 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
HR SAP. Every product'mode pair maps to a CTT number using table TPS_HR_SAP_MAPPINGS
(actually, there is NOT a mapping for every product mode pair....some product mode pairs may not
have a CTT mapping) which in tum identifies the level of deferment using table
‘TPS_HR_SAP_CTT NUMBERS. Finally, the HR SAP Group, HR SAP Period and deferment level
identify the range of dates to be included, using table TPS. HR_SAP_DEF PERIODS
‘There are 2 levels of deferment:
a)2 — - this is the majority of transactions
b) 1 - this is basically Lottery transactions
Note however that both deferment levels could map onto the same date range for a given period for
either or both of the HR SAP Groups.
‘The current position required by POL is summarised in the following diagram. Two files are generated
and passed to HR SAP every month:
© the PIVOT? (i.e CFPO) extract includes transactions within a single date range ie. the level 1
and level 2 deferment types have the same date range
«the PIVOT (i.e. SPSO) extract includes transactions from 2 date ranges i.e. level 1 and level 2
deferment types have the different date ranges
pvizennnn>.hrp _ pivtsnnnn>.hrp
403 / 34314 29/4
ry *
‘Two main processes are involved in the generation of the HR SAP feed files. Both processes run every
day. It is assumed that the harvester interface tables (e.g. TMS_RX_APS TRANSACTIONS) have
already been summarised into an initial summary table (TPS_PROD_MODE_SUMMARIES) by an
earlier daily process (see TPS_POLFS_HLD)
The first process performs the HR SAP summarisation from the initial summary table,
TPS_PROD MODE SUMMARIES into TPS_HR SAP SUMMARIES. Basically, the
summarisation is going up the HR SAP calendar hierarchy from trading date to period id, If the target
table does not already contain a row for the period/branch/CTT then a new row is inserted. If it does
contain the required row, then that row is updated by adding the totals derived from the source table to
the existing totals in the target table.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 20 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Not all rows from the initial summary table are summarised into the HR SAP table. Rows, which are
ignored, are:
a) those for transactions which are not required by HR SAP - the required set is identified in reference
data as produet/mode pairs
b) those for transactions performed at Directly Managed Branches — these branches are identified in
reference data
Therefore all rows, which are written to the summary table, will be used during the subsequent
generation of the HR SAP file. The table will contain data for at least 4 periods (the current one plus
the previous 3 periods) to allow the required time lag between the transaction taking place and delivery
of the data to HR SAP. For each of these periods, there will be a row for every branch/CTT that has
had some transactions.
The second process starts off by checking whether a delivery to HR SAP is due. Currently the
following 2 files are delivered every month, using a schedule defined in reference data,
© $HRSAP_OUTPUT/pivt<nnnn>hrp
Contains transaction data from branches, which are in the “PIVOT” HR SAP Group. These are
the SPSO branches.
© $HRSAP_OUTPUT/pvt2<nnnn>.hrp
Contains transaction data from branches, which are in the “PIVOT2” HR SAP Group. These
are the CFPO branches.
<nnnn> is an incrementing sequence number.
‘ach file contains data for a single period (the target period), which in tu comprises the set of
eligible transactions whose trading date is between the start and end dates of the period (as defined in
reference data)
‘The delivery schedule identifies the required delivery date, but TPS will start attempting to extract the
file <d> days before, to allow for delays in extraction/delivery. <d> is configurable and is defined via a
new parameter in TPS SYSTEM_PARAMI PS will continue trying to create the file every
night until it is successfully created
Note that if the problem is non-delivery of the file, then TPS will not merely try to re-create the file.
Such extractions would be empty since the summary table will have been updated to mark the data as
having been delivered. The operator is made aware of the existence of a problem in the file delivery,
which should cause an investigation to be instigated e.g. into the state of the FTMS gateway. When the
file is eventually delivered successfully, everything will be tidied up automatically.
‘The expected schedule will have at least 2 weeks between end date of transactions to be included and
the date when the corresponding summary data is required by HR SAP. Therefore there will be very
few transactions, which are received too late for inclusion in their target file. However, allowance must
be made for inclusion of late transactions in subsequent files, since this data forms the basis of
Postmasters pay. Therefore the scan of the summary table must extract
a) all data for the current period
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 21 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref EAMLDiocs
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
») all data for previous periods which has not already been delivered to HR SAP. All such data is
marked as having been late
Note that the inclusion of late data in an extraction will not cause an exception to be raised since no
action is required by MSU in response to it having occurred.
The files are delivered to HR SAP via a new FTMS service, which will acknowledge successful
delivery of the file by returning a *HRR file to the gateway. Receipt of this will be detected and
included in the standard Outward Delivery File, which is delivered to the DWH the following day.
3:224.2.2Code
3.2.244.2.2.1 New Modules
32-2444.2.2.1.1 __TPSC280 Summarise HR SAP Data
Function
TPSC280 extracts the transaction data for the current day from the initial summary table,
TPS_PROD_MODE_SUMMARIES and summarises it into table, TPS_ HR_SAP_SUMMARIES.
‘The target period is determined from the trading date using reference data. The majority of the rows
will be for the same target period, since the source data will be primarily from a single trading date.
However, it can also include data from previous trading dates (due to late receipt of data from a
branch), which may be within the same period or an earlier one.
If a row already exists for the period/branch/CTT which hasn’t already been delivered to HR SAP,
then that row is updated. Otherwise a new row is added to the target table. ‘Therefore there will only
be a single non-delivered row for every period/branch/CTT.
Not all the data in the source table is required by HR SAP. The following categories of data are NOT
required and are therefore not transferred to the target table:
© transactions of products which are not required — these do not have a product/mode to CTT
number mapping defined in reference data (TPS_HR_SAP_MAPPINGS)
© transactions performed at branches which are not required ie. Directly Managed Branches ~
these have a null value for attribute HR_SAP_GROUP in reference data (TPS_OUTLETS)
Each row added/updated has the last_updated_system_date field set to the current Business Day, so
that /ate data can be identified subsequently.
Interface
‘TPSC280 does not expect any parameters.
Rerunability
If the module fails, then it can be rerun, since a commit of database changes is only performed on
successful completion of the process.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 22 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EA/HLD/009
FUJITSU Corrections HLD Version: 2.02.0
FUTSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
It guards against double insertion of transaction data in the summary table, by an initial check
performed within the module, Common routine, StartControl, checks whether the process has already
been called on the current day, using table TPS PROCESS CONTROL. If it has, then a second
insertion is not done ~ instead the module will exit tidily.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 23 of 120
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Design
‘TPS Common routines such as OraConnect() and Startup() are used so that, as far as possible, tasks
common to several programs are performed in a consistent fashion and that the program is able to
restart after a failure
Logon to Oracle using OraConneet ().
Perform Initialisation using Startup ()
Perform process control (for restartability) using StartControl ()
if 0 returned (i. already run successfully), then exit
‘Truncate intermediate table, TPS_HR_SAP_DAILY_SUMMARII
Create a union view of TPS PROD MODE SUMMARIES (which is a view of partitions 1 to 64)
with the 65" partition (TPS_PROD_MODE_SUMMARIES 65)
Summarise from the above view into TPS_HR_SAP_DAILY_SUMMARIES
© only include transactions for which cash_account_period is null or > final cash account period
defined in TPS_SYSTEM_PARAMETERS,
* join to TPS OUTLETS on group _id to determine hr_sap_group — if the hr_sap_group is null
(ic. it is Directly Managed Branch) then ignore data
* join to PRODUCT TRANS MODE HISTORIES on prod id & trans mode id-0 to
determine the default accounting_sense ~ this will have values ‘~* or “+”. If value = ‘-’, then
change the sign of total transaction quantity and total_transaction_amount by multiplying by
-l
© join to TPS HR_SAP MAPPINGS on prod id & trans mode id, where trading date is
between start_date and end date, to determine ett_number — if there is no mapping (i.e.
transaction data is not required by HR SAP) then ignore data
© jointo TPS HR_SAP_CTT NUMBERS on ctt_number to determine deferment_type
© join to TPS_HR_SAP_DEF PERIODS on hr sap group, deferment type & (trading date
between period start date and period_end_date) to determine target_period_id
© join to TPS HR_SAP_SUMMARIES on target_period_id & group_id & ctt_number.
If'a rows exists AND delivered_period_id is NULL (i.e. data not delivered), then set
update_row = “Y”
Otherwise, set update_row = “N”
During the summarisation:
© group by fields: target_period_id, group_id & ctt_number
© sum fields: total_transaction_quantity
total_transaction_amount
total_transaction_count
Merge TPS_HR_SAP_DAILY SUMMARIES into TPS_HR_SAP_SUMMARIES
© if update_row =
quantity
"Y”, perform a bulk update summing fields
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 24 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
amount
txn_count
Set last_updated_system_date = Business Day
© if update_row =“N”, perform a bulk insert
Set last_updated_system_date = Business Day
late_flag = “N
Identify the process as having been completed successfully using StopControl()
Commit all transactions
‘TPSC281 Create HR SAP File
Function
‘TPSC281 creates an HR SAP file for passing to POL, according to the schedule defined in reference
data, If a file is due, it is output in zipped format to the standard interface directory identified via EV
SHRSAP_OUTPUT. From there it is picked up by FTMS and transferred to POL
See AIS_HRSAP for the format of the content of the file,
‘The source data is obtained from table, TPS HR_SAP_SUMMARIES and the name of the output file
is constructed using a pair of parameters in TPS_ SYSTEM_PARAMETERS. The parameter names are
based on the HR SAP Group whose file is being created. For example, if the HR SAP Group is
“PIVOT”, then values of parameters PIVOT FILE PREFIX and PIVOT FILE SEQ are used to
construct a filename of the form
SHRSAP_OUTPUT/pivt1004.hrp.pz
Any one call creates at most a single HR SAP file. If two files are due on the same day, only 1 will be
created on the first day — the other file will be produced the following day.
Inter
TPS
e
281 does not expect any parameters.
Rerunability
If the module has not finished then it can be simply rerun provided it has not performed the final
commit. If it has, then before re-running it, it will be nec the tables
‘TPS_FILE_REGISTER, TPS_HR_SAP_SUMMARIES and TPS_SY:
the committed changes, as described below.
If the module has finished, then re-running it will have no effect, since it will decide that the required
file has already been created and therefore no action is required. However, if the file needs to be re-
created (e.g, if it has been lost or is corrupt), then before re-running this process:
© delete the row from TPS_FILE_REGI:
will re-create the file)
TER for the file which was created (so that the process
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 25 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref EAMLDiocs
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Design
update TPS_HR_SAP_SUMMARIES to set delivered_period_id back to NULL for the current
target_period id & hr sap_group i.e. not delivered (so that the correct source data is picked up)
update TPS_ SYSTEM PARAMETERS to decrement value of either of parameters “PIVOT
FILE SEQ” or “PIVOT? FI *, depending on which HR SAP Group is being created (so
that the correct filename is constructed)
TPS Common routines such as OraConnect() and Startup() are used so that, as far as possible, tasks
common to several programs are performed in a consistent fashion and that the program is able to
restart after a failure.
Logon to Oracle using OraConnect ()
Perform Initialisation using Startup ()
Perform process control (for restartability) using StartControl ()
if 0 returned (i.e. already run successfully), then exit
This process is run every day and so the first action is to check whether a file is due to be created on
the current day.
To allow for problems with extraction/delivery, the HR SAP file should be attempted to be
extracted/ransferred several days BEFORE the required delivery date, as defined in
TPS_SYSTEM_ PARAMETERS (parameter_name = “HR SAP CREATION OFFSET”).
‘The successful creation/transfer of all files is recorded in TPS_FILE_REGISTER. In the case of
HR SAP files, the file for a given hr sap_group and hr_sap_period id has been successfully
created if'a row for that group and period exists.
Therefore, a file is due to be created on the current day if there is a row in
TPS_HR_SAP SCHEDULES for which TODAY + creation offset >= delivery date and there
is not a corresponding row in TPS_FILE_REGISTER signifying successful creation of that file
ie. for the hr sap group and hr sap period id defined in the row in
‘TPS_HR_SAP_SCHEDULES
If a file is due, then carry on to create a file for the required hr sap group and
hr_sap_period_id. If both files are due on the same day, just produce one ~ the other file will be
produced the following day.
If no file is due, the exit returning 0
StartExportHRSAPFile is called passing in
group =value of hr_sap_group for required file (to identify the system
parameters to be used in constructing the filename)
‘This will open the required file for write access, returning the name of the file. A row is added
to TPS FILE REGISTER for the file. Note that hr sap period id is unset at this point
signifying that the file has not been successfully created yet
Truncate intermediate table, TPS_HR_SAP_MONTHLY_SUMMARIES, with REUSE STORAGE
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 26 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Summarise TPS_HR_SAP_SUMMARIES into TPS_HR_SAP_MONTHLY SUMMARIES
Itcontains the data for:
a) the required hr_sap_group and hr_sap_period_id
’) the same group and previous periods, where the data has not already been delivered (i.e. data which
has been received late)
ie. where hr sap_group ~ required value AND
hr_sap_period_id <= required period AND
delivered_period id= NULL
Join to TPS_HR_SAP_CTT NUMBERS on ctt_number to identify how the quantity and
txn_count fields in the source data are to be handled, to provide values for quantity
Field amount is just derived by summing the amount field.
If late data has been picked up, there are liable to be duplicate entries in the source table for the
same group/CTT. To ensure there are not duplicates in the target table, group by these fields.
Using format definition in AIS HRSAP and data in TPS_HR_SAP_MONTHLY SUMMARIES,
construct header and detail lines and write in zipped format to the output file. There will be a detail
line for every row in the table, Note that, if negative then a leading minus sign is required in the Value
and/or Volume fields in the file.
Update TPS_FILE_REGISTER to record the value of hr_sap_period_id for the required file ~ this
denotes that the file has been successfully created.
Update TPS_HR_SAP_SUMMARIES to
a) set the delivered_period_id for all rows which were picked up above
b) set late_flag—"Y” for all rows
where delivered period id = required period AND
delivered_period_id NE target_period_id
Update TPS_SYSTEM_PARAMETERS to increment the value of the SEQ parameter for the
appropriate group e.g. “PIVOT FILE SEQ”.
If current value is 9999, set sequence number back to 0001
Call StopExporFile to
a) close output file
b) call StopControl
c) Commit transactions
3.2.2.24.2.2.2 Changed Modules
3.2:2:244.2.2.2.1 _ TPSC211 Start of Day
Funetion
TPSC211 performs the “start of day” functions e.g. it sets the working day (SYSTEM DATE in table
TPS_SYSTEM PARAMETERS)
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 27 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAHLDI009
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 28 of 120
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Change
Create a new function DropCreatePartition() to perform the partition management on table
‘TPS_HR_SAP_SUMMARIES.
This table is partitioned on a period (i.e. month) basis, so the majority of time no action is required
‘The partition for a period only needs to be created at the beginning of that period i.e. when transactions
are about to be received for that period, To allow for potential problems in the creation of the new
partition, it actually attempts to create it 3 days before it is needed. The corresponding drop of the
oldest partition(s) will be performed in the same run which creates the new partition, so old data is,
retained for the maximum possible time. Allowance is made to perform multiple drops of old
partitions in case the drop the previous month could not be performed due to the partition containing
transactions which had not been handed over. Each time this module performs some action on the
partitions, it attempts to reduce the total number of partitions to 4.
To check whether action is required:
a) Identify the maximum target period which current system_date +3 maps to, using table
TPS_HR_SAP_DEF PERIODS
Note that whilst there are 2 levels of deferment, any given date will have 2 target periods -for
the 1 month and 2 month deferments. However, the partition for the 1 month deferment (the
lowest) will have been created the previous month when it would have been the maximum!
b) Using the metadata tables, check whether a partition exists for the target period. Allow for
null target period (meaning do nothing) in case the reference table has not been populated when
first called.
Ifa new partition is required then:
Identify the oldest partitions, such that dropping them will leave 3 partitions. Normally there
will just be one which requires dropping — there will only be more if some very old transaction
data has just tumed up, which hasn’t been handed over. Also during the migration, will start off
with just 2 partitions — in this case, just create a partition.
For each of the old partitions, check that the data has been completely handed over
i.e. no entries in TPS_HR_SAP_SUMMARIES for that period with
delivered_period id = null
If all data in partition has been handed over then
Delete partition, by calling ALTER TABLE, specifying DROP PARTITION
Create new primary partition by calling ALTER TABLE, specifying ADD PARTITION
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 29 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
3222,24.2. C210 Harvest Receipt Info
Function
TPSC210 performs 2 functions. It updates the TPS TIP_SUB FILE REGISTER and
TPS FILE REGISTER tables with the date that the TIP files were successfully received by POL. It
also deletes any files in the STIP_OUTPUT directory which are older than the File Retention Period
(in days).
Change
‘Widen the scope so that it checks the receipt of ALL files transferred by FTMS — not just those which
were transferred via STIP_OUTPUT.
‘Additional receipt files to be handled are:
SHRSAP_OUTPUT/<filename>.HRR
SCTS_OUTPUT/<filename>.CTR
S$FRTS_OUTPUT/<filename>.FRR.
In detail:
a) change main body of code to replace call to
HandleReceipts();
by calls for each of the different receipt files
HandleReceipts(“TIP_OUTPUT”,"TPR");
HandleReceipts(“HRSAP_OUTPUT
HandleReceipts(“
HandleReceipts(“FRTS_OUTPUT”,
) change HandleReceipts to
‘Add 2 input parameters for the EV and Suffix
Pass the EV through to the call to getenv
Pass the Suffix through to the call to RenameFiles
b) change RenameFiles to
‘Add a single input parameter for the Suffix
If Suffix = “TPR”, remainder of processing is as current i.e. update TPS_FILE_REGISTER and
‘TPS_TIP_SUB_FILE_REGISTER with the tip_receipt_date
For all other Suffixes, just update field receipt_date in TPS_FILE_REGISTER. An update is not
performed to TPS TIP_SUB FILE REGISTER as these data types do not contain sub files.
TPSC206 Create Delivery File
Funetion
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 30 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EA/HLD/009
FUJITSU Corrections HLD Version: 2.02.0
FUTSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
‘TPSC206 creates the Outward Delivery File that records information about the timeliness of delivery
of TIP files. This file is sent to the DWH via NFS mount $MIS_OUTPUT. The SLA indicator in the
sub file register (TPS_TIP_SUB_ FILE REGISTER) is updated to denote delivery of the SLA
information to the DWH and a check is performed for unacknowledged files.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 31 of 120
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Change
Files generated by TPS are being delivered to different locations, as opposed to all files going to TIP.
‘The destination is identified in the destination field in TPS_FILE_REGISTER
If destination = “T”, then this identifies files which were sent via the existing “TIP interface” — they
should be handled in the same way, with the information for the output file being extracted from
TPS_TIP_SUB_FILE_REGISTER
If destination = “S”, “C” or “EF”
should be handled as follows:
All information is retrieved from TPS_FILE_ REGISTER
‘The files which have been successfully delivered AND not already been reported on have
then this identifies files which were sent to the new locations — they
sla_reported = N
AND
receipt_date = not null
Allow for reporting on the delivery of multiple files for a given destination. For example, the FRTS.
feed comprises 2 files, so this will result in 2 rows being appended to the Outward Delivery File.
If destination = “S” i.e. HR SAP, then
Construct a row containing the following fields with the same field spacing as the existing “TIP
delivery lines”, as defined in AIS_DWH
file id =TPS_FILE REGISTER file name
source “rps”
destination = see below
© date = see below
d date = TPS_FILE REGISTER. receipt_date
records 1
FAD Code = null
The destination is derived from TPS_SYSTEM PARAMETERS using parameter_name
=TPS_FILE_REGISTER.hr_sap_group +“ DEST”
e.g. if the current value of hr sap group is “PIVOT” and TPS_SYSTEM PARAMETERS
contains parameter_name = “PIVOT DEST” with a value of “HRS2”, then destination =
“HRS
The ¢ date is derived from TPS_HR SAP SCHEDULES delivery date by subtracting the
number of offset days defined in TPS SYSTEM PARAMETERS (parameter_name = “HR
SAP CREATION OFFSET”). It is 00:00 on that day.
e.g. if delivery date = 14/05/04 and the value of parameter “HR SAP CREATION OFFSET” is
3, then ¢ date = “11/05/04 00:00”
Note that the value of “HR SAP CREATION OFFSET” of 3 is the value which will be used
and is therefore the value which should be assumed when changing the DWH metadata which
drives the performance measures.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 32 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
If destination = “C” i.e. CTS, then
Construct a row containing the following fields:
file id =TPS_FILE REGISTER .file_name
source =“TPS”
destination = “CT:
c date ‘TPS_FILE_REGISTER start_date
d date = TPS_FILE REGISTER.receipt_date
records =1
FAD Code = null
If destination = “F” i.e. FRTS, then
Construct a row containing the following fields:
file id =TPS_FILE REGISTER .file_name
source = “TPS”
destination FRTS'
© date = TPS_FILE REGISTER start_date
d date =TPS_FILE REGISTER receipt _date
records =1
FAD Code = = null
Set sla_reported = “Y”, so the file will not be reported on again.
To cater for files, which have been created, but not successfully delivered, extend function
Check receipt date to check for unacknowledged files in TPS FILE REGISTER ~ it currently just
performs checks at the sub-file level, using table TPS_TIP_SUB_FILE_REGISTER.
3.2:2:244.2.2.2.4 _TPSC207 Harvest Branch Info from RDDS
Funetion
This module copies reference data from RDDS to TPS.
Chang
Note that this document just describes changes which are required for HR SAP and Transaction
Correction processing, Further $80 changes are described in TPS_POLFS_HLD.
‘The general policy with regards to handling errors during the accessing of the RDDS tables should be:
if error occurs
then if $80 MIGRATION POINT < 25
then rollback any changes, output a warning message and continue
else rollback any changes, raises an exception and exit with a failure
a) HR SAP Changes
‘Add a new function UpdateHRSapData to replace the contents of tables:
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 33 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
TPS_HR_SAP_CTT NUMBERS
TPS_HR SAP DEF PERIODS
TPS_HR_SAP_MAPPINGS
TPS_HR_SAP_SCHEDULES
‘The tables are copied from tables/views in RDDS as described below.
Table/View in RDDS Table in TPS Column omitted during
copy from RDDS to TPS
HIR_SAP_CTT NUMBERS TPS_HR_SAP_CTT NUMBERS I creation date
change id
HIR_SAP_DEF PERIODS TPS_HR SAP DEF PERIODS I version number
HIR_SAP_MAPPING HISTORIES I TPS_HR_SAP_MAPPINGS version_number
creation _date
change id
HIR_SAP_SCHEDULES TPS_HR SAP SCHEDULES I version number
creation_date
change I
Change function ReadRDDS to cater for the addition of the following fields to
TPS_OUTLETS
office_status
hr_sap group
b) Transaction Corrections change
Populate new table
‘TPS_TRANS_MODE_CONVERSIONS
with fields
pocl_trans_mode_type_code
pathway trans mode type code
©) Removal of Cash Account Processing changes
The set of reference tables which are used for Cash Account processing no longer need to be populated
‘Therefore replace calls to
call ReadRDDSCashAceTables()
call genDenormalisefCACData()
by
a call to new function ReadRDDSProductData) to populate tables.
PRODUCT TRANS MODE HISTORIES
PRODUCT HISTORIES.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 34 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD
ealiaaniaaeal COMMERCIAL IN CONFIDENCE
2.2.3 Common Routines
3:2-2,344.2,
StartExportFile
Function
Ref:
Version:
Date:
EA/HLD/009
2.02.0
45/1024/11/04
This routine constructs the filename to be created and opens the file for write access. A row is inserted
into TPS_FILE_REGISTER for the new file
For example
if dest = “T" ; filetype = “TP_
then filename = $TIP_OUTPUT/<data centre>_<tip_date><seq_num>.TP_pz
Change
Cater for the additional destinations:
If dest ="C”, output file to directory $CT:
; OUTPUT
the remainder of the filename is constructed in the same way as for dest="T”
If dest=
output file to directory $FRTS_OUTPUT
the remainder of the filename is constructed in the same way as for dest="T”
All updates to TPS_FILE_REt ER should set:
destination = <parameter value>
sla_reported =N
The other new fields being added are all nullable.
StartExportHRSAPFile
Funetion
This routine constructs the filename for HR SAP files only and opens it for write access. A row is
inserted into TPS_FILE_REGISTER for the new file,
It is a new routine and is based upon StartExportFixedSequenceFile
Description
Pass in group = <HR SAP Group>
Perform the following actions:
1) filename = $HRSAP_OUTPUT/<xxxx><yyyy>.hrp.pz
where <xxxx> is extracted from TPS_SYSTEM_PARAMETERS for
parameter_name = <HR SAP Group> FILE PREFIX e.g. pivt
<yyyy> is extracted from TPS_SYSTEM_PARAMETERS for
parameter_name = <HR SAP Group> FILE SEQ eg. 1001
© 2004 Fujitsu Services COMMERCIAL IN CONFIDENCE
Page: 35 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
2) include the <HR SAP Group> in the row which is added to TPS_FILE_ REGISTER
Set destination =S
sla_reported =N
3) the sequence number is 4 digits — as opposed to 3 digits.
Note that the length of the variable is passed to LPAD when it is being zero filled to the correct
length, prior to being incremented ready for the next run
Performance Considerations
3.2.34.
3.2.3:44.2.3.1 Partitions and Indexes
The only table requiring partitioning/indexing is TPS_HR_SAP_SUMMARIES
It will have:
© Range Partitioning on target period id, as opposed to List Partitioning. Four primary
partitions are required ~ the current period plus the previous 3 periods. This will allow for the
required 2 month deferment, plus an extra month for very late transactions being harvested. ice.
transactions which are harvested between 10 and 16 weeks afier they occurred.
Note that if List Partitioning were used, then more partitions would need to be retained to
accommodate the late transactions. For example, with Range Partitioning, if a transaction
turned up 20 weeks late, after the partition containing it’s target partition had been dropped,
then it will merely be inserted into the oldest existing partition. However, in the case of List
Partitioning, the update will fail if the required partition does not exist
© Sub Partitioning on Hash (Group ID)
* A local index (prefixed) on hr sap_group, group id and ct_number
3.2,3.24.2.3.2 Tablespaces
With regards the mapping from tablespaces to partitions there are 4 options
store all partitions within the table in a single tablespace
© allocate a tablespace per primary part
© allocate a tablespace per sub partition
# allocate a tablespace per primary/sub partition pair
The 2™.3" and 4" options allow more control over the allocation of data across multiple disks, and
therefore enable the level of parallelism to be controlled more accurately. For example, it is known that
the vast majority of data access will be to the most recent primary partition i.e. the most recent period.
Therefore, to achieve the full benefit of the parallel access performed by Oracle, it is desirable that a
single period is stored on several physical disks. However, this introduces an extra level of complexity
and a maintenance overhead, which is not warranted by the relatively low data volumes. ‘Therefore the
1* option is the chosen one
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 36 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref:
FUJITSU Corrections HLD Version:
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date:
‘The name of the single tablespace is
TPS_FACT DATA
3.2,.44.2.4 _Volumetrics
3.2.4.14.2.4.1 Main Assumptions
Branches
number of branches (SPSO) = 16000
© number of multiples CFPO) =250
© number of Directly Managed Branches = 600
Product/Modes
© number of CTT numbers =200
© number of produet/iransaction mode pairs = 3000
Proportion of products transacted by a single branch
© cach day =10%
* cach period (month) = 30%
3.24.24.2.4.2 Tables sizes
‘The following new tables are being added to the database
TPS_HR_SAP_CTT NUMBERS
This table contains a row for every CTT.
Estimated size = 200 rows
TPS_HR_SAP_DEF_PERIODS
EA/HLD/009
2.02.0
45/1024/11/04
This table contains a row for every HR SAP Period/deferment. The elapsed period would vary
during the year, as the data is aged in RDS, from a maximum of approx 18 months (to allow
for late arrival of data)
Estimated size = 36 rows
TPS_HR_SAP_MAPPINGS
This table contains a row for every product/transaction pair, which has a corresponding CTT
number
Estimated size = 3000 rows
TPS_HR_SAP_SCHEDULES
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 37 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
This table contains a row for every HR SAP Period/HR SAP Group. The elapsed period would
vary during the year, as the data is aged in RDDS, from a maximum of approx 18 months (to
allow for late arrival of data)
Estimated size = 36 rows
TPS_HR_SAP_DAILY_SUMMARIES.
This table contains a row for every period/branch/CTT for which transactions were harvested in
the current run. Normally the data will be for a single period.
Estimated size = 16250 * 200 * 0.1 = 0.32
TPS_HR_SAP_MONTHLY_ SUMMARIES.
This table contains a row for every branch/C for which there were transactions in the current
period, The data will be for a single period
Estimated size = 16250 * 200 * 0.5 = 1.6 million rows
‘TPS_HR_SAP_SUMMARIES
This table contains a row for every target period id/branch/CTT number.
Assuming 4 periods worth of data must be kept, to allow inclusion of products transacted 2
months in arrears
There will be approx
16250 * 200 * 0.5 * 4 = 6.5 million rows
3:24:34.2.4.3 File sizes
SHRSAP_OUTPUT/pivt<nnnn>.hrp
This file contains all the “PIVOT data” i.e. SPSO for a single period
There will be approx
16000 * 200 * 0.5 = 1.60 million rows
Max row length = 40
Assuming average length of rows is half the maximum, the unzipped file will be approx
1.6 * 20 =32 MB
Note that the file will be created as a zipped file and will therefore be considerably smaller.
SHRSAP_OUTPU
This file contains all the “PIVOT2 data” i.e. CFPO for a single period
‘There will be approx
250 * 200 * 0.5 = 25,000 rows
vt2<nnnn>.hrp
Max row length = 40
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 38 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAIHLD/009
FUJITSU Corrections HLD Version: 2.02.0
FUTSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Assuming average length of rows is half the maximum, the unzipped file will be approx
25000* 20 = 0.5 MB
Note that the file will be created as a zipped file and will therefore be considerably smaller.
NB: The above file sizes are more or less in line with the predicted data volumes in AIS_HRSAP
(Section 2.9)
3.2.54.
Housekeeping
3.2.5.14.2.5.1 Database
‘The summary data contained in TPS_HR_SAP_SUMMARIES does not need to be kept indefinitely.
Once it has been extracted and delivered to HR SAP there is no real need to retain it and in fact it
would have a detrimental effect on performance if it were allowed to grow unnecessarily large.
Housekeeping of the table is performed by dropping the oldest partition, once it has been confirmed
that all data in it has been handed over to HR SAP. This is performed by the Start of Day process (see
All other new tables being introduced are small and their size is controlled by RDDS, or are truncated
before being re-populated.
Filestore
The files contained in the new host directories described in 5.3.7.1 need to be included in the standard
housekeeping performed in TPSHouseKeep sh.
This is performed by virtue of the update to table TPS_FILES_TO_HOUSEKEEP as described in
5.3.6.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 39 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref EAMLDiocs
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
3.34.3 Transaction Corrections
3.344.3.1 Overview
‘The handling of Transaction Corrections is a new set of business processes to enable corrections to be
made to the Branch accounts as a result of various central investigations. It involves the sending of a
file of corrections from POL FS to TPS. TPS performs some validation and translation on the data and
then sends the resultant messages onto the counters. It replaces the existing Error Notice functionality,
which is a totally manual process.
Currently there is only one agent interfacing with TPS — the TPS Bulk Harvester, which is
responsible for transferring messages from the counters to TPS. Handling of Transaction Corrections
involves the creation of a second agent ~ the TPS TC Bulk Loader, which will be responsible for
transferring messages from TPS to the counters, where counter staff can process them,
In summary the whole process works as follows:
© The central accounting function decides that it is necessary to make some adjustment to the
Branch accounts
* A Transaction Correction is defined which will carry out the necessary changes (i.e. the central
user will define an amount to be transacted for a given Product in a given Branch and a
corresponding settlement Product)
© The Transaction Correction will also define a list of possible actions that the Branch Manager
can take and also text to be presented to the Branch Manager informing him / her of the affect
of carrying out any of these actions.
© A daily file of such 1
overnight
ansaction Corrections is generated from POL FS and passed to TPS
* TPS receives this file from an NFS mounted interface directory, validates the data and performs
the required translations using reference data. For example, it converts the SAP Article Id into a
Horizon Product.
© TPS sends messages for the Transaction Corrections to the specified branches. It uses the
normal Bulk Loader technology (including the use of Acks from the Branch to acknowledge
successful receipt of the Transaction Correction). A single message is written for the
appropriate Branch for each Transaction Correction.
© Changes at the Counter enable a person’ with the required role to
a) be made aware of the existence of outstanding Transaction Corrections
b) apply any of theses corrections
© The result of processing a Transaction Correction will normally be the creation of the specified
‘Transactions, which will be retumed to POL FS as part of the normal flow of Summarised
Transaction data at the end of the Trading Day on which the Transaction Correction was
processed at the Branch.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 40 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
‘As stated above, Transaction Corrections is a daily feed from POL FS to TPS. Normally a single file
will be delivered, containing all the transaction corrections generated in a single Business Day,
However, delivery could also include corrected txn correction files from previous days. Although not
expected, there is a possibility that no file is delivered. Therefore on any one day, an indeterminate
number of txn correction files will be delivered. The code must cope with all eventualities
‘There are 3 directories concerned with transaction corrections, which are identified by the following
EVs:
+ $POLFS_INPUT_SHARE
Identifies directory on POLES host (NFS mounted on TPS host). Will contain
a) the transaction correction files created by POLFS
) error files generated by TPS and then copied from SPOLFS_INPUT by TPS
Housekeeping of files in this directory is the responsibility of POLFS
© SPOLFS_INPUT
Identifies directory on TPS host. Will contain
a) the transaction correction files copied from SPOLFS_INPUT_SHARE by TPS
b) error files generated by TPS
Housekeeping of these files is performed by TPS
* $POLFS_AUDIT
Identifies directory on TPS host. Will contain
a) links to transaction correction files received into SPOLFS_INPUT
) links to error files generated in SPOLFS_INPUT.
Housekeeping of these files is performed by the audit system. Note that this is an existing
directory which is used for auditing the current set of files sent to POLFS.
The name of the input file name generated by POLES is:
if<yyyymmdd><nnn>.ten
where <yyyymmdd> is the date when the file was first generated
<nnn> is the sequence number, starting at 001 for an Original file
Tt contains:
© header line
# adetail line for each Transaction Correction required
© 1 trailer line
‘TPS will process all possible lines of the file(s), unless the header or trailer fails, in which case the
whole file is rejected. All errors, whether at the file level or the individual correction level, are reported
back to POL in an error file. There is a single error file for each input file, which contained errors.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 41 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
‘The name of the error file returned to POLFS is:
if<yyyymmdd><nnn>.err
where <yyyymmdd> is the date of the failed file
<nnn> is the sequence number of the failed file
It contains:
Ref: EA/HLD/009
© I header line
* anerror line for each error detected, which could be more than the number of lines containing
errors e.g. if the header line contained an invalid date and invalid file identifier fields, there will
be two error lines corresponding to the invalid header line
© I trailer line
All the Transaction Correction processing is contained within a single Maestro schedule (described in
APPENDIX 2: Maestro Scheduler), which is executed once every day. For simplicity, and ease of re~
starting, it has been split into multiple smaller processes as described below:
* A Pre-Process job checks for Transaction Correction files in the interface directory
(SPOLFS_INPUT SHARE). For each new file, a row is added to the control table,
TPS_TC FILE DETAIL and the file pre-processed prior to loading the data into the database
This involves copying the file to the local directory (SPOLFS_INPUT), creating a link to it in
the audit directory (SPOLFS_AUDIT) and then checking the structure of the file is correct and
that the header and trailer records themselves are valid and consistent (e.g. the checksums in
the trailer are correct). Any errors result in rows being written to table, TPS TC_ERROR:
identify the error. All errors associated with the header or trailer cause the entire file to be
rejected.
The output from this process is a single file containing all the Transaction Correction detail
lines for all the input files, with valid header and trailer lines, which existed in
SPOLFS_INPUT. Each line has two extra fields added to identify the source filename and
record number in the file, so that any errors in the detail lines can be mapped back to the
original input file.
* An Initial Load is performed to load the pre-processed file into table, TPS_TC_DETAIL,
using SQL*Loader. No specific validation is performed at this stage.
* The Load TMS Table process performs the bulk of the validation of the detail lines contained
in TPS_TC_DETAIL and converts
a) article to horizon product
b) instruction to horizon product
¢) allowed modes to modes
Any errors cause just the record containing the error to be rejected. Rows are written to
TPS_TC_ERRORS to identify all the errors in the rejected record,
All valid and complete rows are written to the agent interface table,
TMS_TX TPS TC DETAIL.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 42 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
© The Create Error File process reads TPS_TC_ERRORS -and creates an error file for each
source file, which contained errors. Therefore if there were no errors, then no error file is
created. The name of the file is based upon the name of the original file.
© The TPS TC Bulk Loader agent is run to extract the data from TMS_TX_TPS TC_DETAIL,
form it into attribute grammar and send the messages to the counter via Riposte. A single
message is written for the appropriate Branch for each Transaction Correction.
© Finally, the End Job process tidies up. In particular, it renames the input file in both
SPOLES_INPUT SHARE and $POLES_ INPUT to
if<yyyymmdd><nnn>.TCN
so that it will not be picked up again.
This process also checks that a file has been received for the current Business Day ~ provided it
is not a Sunday. If no file has been received, an alert is raised.
Error Handling
Overview
‘TPS validates the supplied file of Transaction Corrections to ensure that:
© it is consistent within itself e.g. the checksums within the trailer match the totals in the
individual detail lines
© the corrections, when sent to the counters by the Agent Loader, are capable of being actioned
e.g. the product/item is capable of being transacted in the requested mode
If any errors are detected in the header or trailer, then the whole file is rejected. However, errors in
individual detail lines only cause that detail line to be omitted i.e. not passed onto the counters,
All errors are notified back to POL by the creation of an error file:
if<yyyymmdd><nnn>.err
where <yyyymmdd> is the date of the failed file
<nnn> is the sequence number of the failed file
One error file is created for each txn correction file, which contained errors. If no errors are detected,
then no error file is created.
The file is created initially in directory identified by SPOLFS INPUT and then copied to
SPOLFS_INPUT SHARE from where POLFS access it. In addition, a link is created in
SPOLFS_AUDIT for audit purposes.
Housekeeping of files in the 3 directories is performed as follows:
+ SPOLFS_INPUT - performed automatically by TPS
© SPOLFS_AUDIT ~ performed automatically by audit system
© SPOLFS_INPUT_SHARE ~ this is on POL FS’s host and as such is their responsibility
The general principle with validation is that as many errors are detected and notified back to POL as
possible. For example, if'@ header file contains both invalid Source and Version fields, then both errors
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 43 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EA/HLD/009
FUJITSU Corrections HLD Version: 2.02.0
PUITSUSERMCES COMMERCIAL IN CONFIDENCE Date: 16/1024/14/04
will be detected and reported in the error file. However, some errors, such as wrong number of fields,
are liable to cause numerous
cause the cessation of checking on the current row.
incorrect errors” if checking were continued. Therefore, such errors
A full description of the format of the error file is contained in AIS_POLFS
3:3:2.24.3.2.2 Errors Codes
‘The following table contains columns:
Error Code This is the code, which identifies the type of error. It is written to the
error file.
Description This is a description of the error. The 1" 30 chars are written to the error
file
Validation This is the test, which is performed.
Ree Type This is the type of record in error.
‘THZ = Header
TDZ = Detail
TIZ = Trailer
‘These are the values which are required in the error table.
Note that the set of error codes below is an extension of the set described in AIS_POLFS. This AIS will
be updated in due course to reflect the following definitive set.
Error I Description Validation Ree
Code Type
001 Invalid ‘Label Identifier’ I ‘Label Identifier’ field is “TCNHI", “TCINV”, “TCCRM” or I TDZ
field “TCNTR®
NB:
1. this will also be caused by the line having no “;" init
2. there is no easy way to identify the type of record in error, since
that is what this field is doing. However, all error codes are
required to have a “record type” associated with them — hence the
value of “TDZ”.
002. I Invalid ‘Source’ field “Source” field is as contained in TPS_SYSTEM_ PARAMETERS I THZ
parameter_name=TC_SOURCE eg. “POLFS
003 I Invalid ‘Version’ field I ‘Version’ field is as contained in TPS EM_PARAMETERS I THZ
parameter_name=TC_INTERFACE_VERSION e.g, “01”
004 I Invalid “Date & Time’ I ‘File Creation Date & Time” field must be a valid: THZ
field a) date = yyyymmdd
») time = hhmmss
©) not be in the future
005 [Invalid “Original or I ‘Original or Correction’ field must be ‘O° or ‘C* THZ
Correction’ field
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 44 of 120
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal ‘COMMERCIAL IN CONFIDENCE Date: 15/4024/11/04
Error I Description Validation Ree
Code Type
006 I Invalid” File Identifier’ I Vile Identifier field isa valid format: THz
field a) format is if <yyyymmdd><nnn>.ten
’) date isnot in the future
¢) ile has not already been received
4) matches terminal filename
007 I Branch FAD code does I Oulet exist in TPS_OUTLETS DZ
not exist
008 I Branch FAD code I Oullet exists in TPS OUTLETS, with office status ne T TZ
is Permanently Closed
009 I Branch FAD code I Oullet exists in TPS_OUTLETS, with office status ne 2 TDZ
is Temporarily Closed
O10 I Line Count is incorrect I Count of detail records = Tine Count” in trailer record TIZ
O11 I Value Sum is incorrect I Sum of the ‘Value’ fields in detail records = Value Sum’ in I TTZ
trailer record
012 I Non numeric value ‘All number fields in detail and Waller records are numeric TDz
013 [NULL supplied for I A value has been supplied forall of elds 1 to 9 and field 11 im I TDZ
mandatory detail field I ‘he detail record
O15 I Last record is not Trailer I Lastrecord isthe taller record TIZ
O16 I First record is not Header I First record i the header record TAZ
O17 I Quantity Sum is incorrect I Sum of the ‘Quantity Reds in detail records = Quantity Sam? in I TTZ
trailer record
O18 I Anficle/Instruction cannot I Artcle/Insiraction is valid and can be mapped to a Horizon TDZ
bbe mapped to a Horizon I Product withthe comet accounting sense
Product with the conest Ii article, then TCINVITCCRM should map to
default_prod_pos/default_prod_neg
accounting sense if instruction, then TCINV/TCCRM should map to
default_prod_neg/default_prod_pos
021 _I Allowed Modes is ‘Allowed Modes exisis in TPS_TC_MODES_MAPPING DZ
invalid
022 I Quantity and Value are I Bither Quantity or Value is > 0 TDZ
both 0
030 _I Header has incorrect Number of felis 6 THz
number of fields
031 _I Detail has incorrect ‘Number of fields 11 TZ
number of fields
032. I Trailer has incorrect ‘Number of fields is 4 TIZ
number of fields
033 I Duplicate header record _I Only T header revord THz,
034 I Duplicate trailer record I Only T wailer record TIZ
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE Page: 45 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUSiT SU Corrections HLD Version: 2.02.0
ealiaaniaaeal ‘COMMERCIAL IN CONFIDENCE Date: 15/4024/11/04
Error I Description Validation Ree
Code Type
035 I Article does not exist I Anlicle exists in TPS_POL_FS ARTICLES TDz
036 I Product does not exist I Product exiss in PRODUCT. HISTORIES TDz
037 _I Product is not valid Product exisis within date range specilied in TDZ
PRODUCT HISTORIES (start date to end_date)
038 I Product cannot be Entries exisis in PRODUCT. TRANS MODE_ HISTORIES for I DZ
transacted in required _I the product and all the transaction modes which the
mode “Allowed. Modes’ field has been translated to
039 I Quantity is outside T7 Quanity has been specified, vis within the range specitiedin I TDZ
permitted range for PRODUCT HISTORIES (minimum quantity to
ws maximum quantity)
040_I Value is outside Tf Value has been specified, tis within the range specified in TZ
permitted range for PRODUCT HISTORIES (minimum. value to maximum value)
product
041 _I Value is not a multiple of I If Value has been specified and price override SNOT allowed I TDZ
sitice then iffadjustment_price > 0
then check value is an exact multiple of adjustment_price
elsf (adjustment_price is NULL and retail_price > 0)
then check value is an exaet multiple of retail_price
fi
042_I Value is supplied for If Value has been specified and prise ovenide SNOT allowed I DZ
product; whose prise then if adjustment_price = 0_or
Pe natte adiarted adjustment price is NULL and retail_price = 0
J then error
fi
adjustment-price = 0-or{adjustment-price is NULL-and
retail_prive=0)
043 I Quantity not supplied for I Allowed Modes 91 oF 92 TDZ
stock adjustment then check Quantity is non-zero
044 I Quantity is supplied for [1 Allowed Modes is not @T oF 92) TDz
mode other than stock I tien check Quantity is zero
adjustment
045 I Value supplied for stock [If Allowed Modes is 91 or 92 TDz
adjustment then check Value is zero
046 I Duplicate transaction I Anenry docs not existin TPS_TC_RECEIVED Tor the TDz
correction transaction correction (identified by SAP_Reference_ID and
eration Flag)
099 I Oracle SQL* Loader error I Validation performed by SQL*Loader: TDZ
a)all fields are valid data types
») correctly formatted record (so that SQL*Loader can find field
boundaries)
¢) no constraints violated
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE Page: 46 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
3.3.34. Code
3.3.344.3.3.1 New Modules
TPSC282 TC Pre-Process
TPSC282 checks for Transaction Correction files inthe __ interface directory
(SPOLFS_INPUT_SHARE). The file names are assumed to be:
$POLFS_INPUT_SHARE/if<yyyymmdd><nnn>.ten
where <yyyymmdd> is the date when the file was first generated
<nnn> is the sequence number, starting at 001 for an Original file
All files which match SPOLFS_INPUT_SHARE /if*.ten, but then fail any of the validation checks
(e.g. file already received) are ignored and an error file is created by a later process. However, note that
all validation of the file is performed on the file in $POLFS_INPUT ie. after it has been copied over
onto the TPS host.
All files which exist in $POLES_INPUT_SHARE, but don’t match if*.ten (e.g. previously processed
files which will have a TCN suffix) are ignored. There is no mention of any such files in the error file.
For each file with a valid name, a row is added to the control table, TPS_TC_FILE_DETAIL and then
the file pre-processed.
This involves
a) copying the file from interface directory $POLFS INPUT SHARE to local directory
SPOLFS INPUT
) creating a link in SPOLFS_AUDIT to newly copied file in SPOLFS_INPUT
©) checking the structure of the file is correct i.e. It contains:
* I header line, with correct number of fields
© 1 or more detail lines, with correct number of fields
or
no detail lines
© L trailer line, with correct number of fields.
d) checking the header line is correct i.e. all fields contain valid values
©) checking the trailer line is correct ic. all fields contain valid values ~ in particular it checks that the
line count and check sums are correct for the preceding detail lines.
Any errors result in rows being written to table, TPS_TC_ERRORS, to identify the error. All errors
associated with the header or trailer cause the entire file to be rejected. Errors associated with a detail
line just cause that line to be rejected.
The output from this process is a s
$POLFS_INPUT/te_details.tmp
ngle file in the local directory
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 47 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
containing all the “valid” Transaction Correction detail lines for all the new input files which existed
in $POLFS_INPUT and had valid headers and trailers. Each line has two extra fields added to identify
the source filename and record number, so that any errors in the detail lines can be mapped back to the
original input file. Note that the majority of the validation of the detail lines is performed by a later
module.
If there are no files with valid headers and trailers, then the output file is not created.
Interface
‘TPSC282 does not expect any parameters.
Rerunability
If the module fails, then it can be rerun.
Design
TPS Common routines such as OraConnect() and Startup() are used so that, as far as possible, tasks
common to several programs are performed in a consistent fashion and that the program is able to
restart after a failure
Logon to Oracle using OraConnect ().
Perform Initialisation using Startup ()
Perform process control (for restartability) using StartControl ()
if 0 retumed (i.. already run successfully), then exit
To allow for re-running:
Delete rows from TPS_TC_FILE_DETAIL where processed flag
Truncate table TPS_TC_ERRORS
Delete intermediate file
Delete output file (SPOLFS_INPUT/te_details.mp)
SYSTEM_PARAMETERS for validation of headers:
> SOURCE ; parameter_name INTERFACE_VERSION
Identify all the files with names of the form $POLFS_INPUT_SHARE/if*.ten.
For each file:
Extract parameters from TP!
parameter_name =
Copy file from $POLFS_INPUT_SHARE to $POLFS_INPUT
Create link in SPOLFS_AUDIT to newly copied file in SPOLFS_INPUT
Cheek that:
a) remainder of the filename is correct format i.e. <yyyymmdd><nnn>
b) date is not in the future
c) file has not already been received ie. no entry in TPS_TC_FILE_DETAIL
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 48 of 120
TPS HR SAP Summarisation & Transaction Ref EAMLDiocs
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
If any errors
then write row to TPS_TC ERRORS (error_code = 006)
else write row to TP!
: TC _FILE_DETAIL to identify file(s) to be processed, assigning fields:
file name full file name
receive date current date
create date = extracted from file name
sequence no
error_in_hdr_flag
error_in_trl_flag
error_in_dtl_flag
processed
tracted from file name
ay
flag =*N?
Set record_number to 0
For each line in current file
Increment record_number
Split line on field separator “;”
If Field 1 =“TCNH1”, then it is a HEADER line
Perform the following tests:
test error_code
1. Number of fields is 6 030
2. Field 2 contains a valid source 002
compare against extracted value
3.Field 3 contains a valid version 003
compare against extracted value
4. Field 4 contains a valid date & time, 004
5. Field 5 contains
date = yyyymmédd ; time = hhmmss ; not in the future
Note that the time in the TC file is GMT
” (Original) or “C” (Correction file) 00s
6. Field 6 contains a terminal name which matches physical name 006
If any of the tests fail, then
If Field 1 = “TCINV” or “TCCRA
write a row to TPS_TC_ERRORS with specified error code
if Test I fails, then do not perform remaining tests on current row
reject entire file, but continue validating file to check for other errors
”, then it is a DETAIL line
Increment line count (detail_line_count)
Perform the following tests:
test error_code
1. Number of fields is 11 031
2. Value supplied for fields 1 to 9 and field 11 013
3. The following fields are numeric 012
Field 2ie. group id
© 2004 Fujitsu Services COMMERCIAL IN CONFIDENCE Page: 49 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Field 4 ie. value
Field 5 i.e. quantity
Field 8 i.e. allowed_modes
Ifany of the tests fail, then
write a row to TPS_TC_ERRORS with specified error code
if Test 1 fails, then do not perform remaining tests on current row
If current row is valid, then
‘Add value of Field 4 to detail_value_sum
Add value of Field 5 to detail_quantity sum
Prefix current line with
terminal filename (e.g. if20040624001 ten)
record_number
with a “;” separator between each field
Write line to intermediate file
If Field 1 = “TCNTR” then it is a TRAILER line
Perform the following tests:
test error_code
1. Number of fields is 4 032
2. The following fields are numeric o12
ield 2 ie. line_count
Field 3 ie. value_sum
Field 4 i.e, quantity_sum
If any of the tests fail, then
write a row to TPS_TC_ERRORS with specified error code
if Test 1 fails, then do not perform remaining tests on current row
reject entire file
If current row is valid, then
Extract Line Count from field 2
Extract Value Sum from field 3
Extract Quantity Sum from field 4
If Field 1 contains any other value or is null (i.e. unable to split on ), then
write a row to TPS_TC_ERRORS with error code = 001
Repeat —_#alll lines in current file read
Perform the following tests:
test error_code
1. First row was a header 016
2. Last row read was a trailer 015
3. Only I header line in file 033
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 50 of 120
FUJ00090327
FUJ00090327
Ps marisati Ref: EA/HLD/009
FUJITSU TONSA Gorectons HL Version: 2020
alien ‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
4. Only I trailer line in file 034
5. Line count in trailer matches number of detail lines 010
6, Value_Sum in trailer matches sum of value in detail lines ou
7. Quantity Sum in trailer matches sum of quantity in detail lines 017
If any of the tests fail, then
write a row to TPS_TC_ERRORS with specified error code.
reject entire file
If errors detected in header and/or trailer, then
update current row of TPS TC FILE DETAIL to set
error_in_hdr flag =“Y” and/or error_in_trl_ flag =""
else
append intermediate file to output file SPOLFS_INPUT/te_details.tmp
end if;
delete intermediate file
update current row of TPS_TC_FILE_DETAIL to set detail_line_ count
Repeat Hall files processed
Identify the process as having been completed successfully using StopControl()
‘Commit all transactions
Funetion
TPSX283.sh TC Initial Load
SX283.sh loads txn correction detail lines contained in file $POLFS_INPUT/te_details.tmp, into
table TPS_TC DETAIL. If this file does not exist, signifying that there were not any txn correction
files with valid headers and trailers, then this module does nothing ~ in such cases, it leaves the table
empty.
The only validation which is performed at this stage is that which is performed by SQL*Loader e.g. to
check that all fields are the correct data type. Any records, which fail its checks, are written to a “bad
file”, from where they are picked up by TPSC285.
Interface
‘TPSX283.sh does not expect any parameters.
Rerunability
If the module fails, then it can be rerun.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 51 of 120
TPS HR SAP Summarisation & Transaction Ref: EAIHLD/009
FUJITSU Corrections HLD Version: 2.02.0
FUTSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Design
This is a UNIX Shell script, calling SQL*Loader. It performs:
‘Truncate table TPS_TC_DETAIL
Delete “bad file” $POLFS_INPUT/te_details.bad
If input file $POLFS_INPUT/te_details.tmp exists, then there are detail lines to process
Load input file into table, directing any rejects to the above “bad file”.
3:33:434.3.3.1.3 TPSC284 TC Load TMS Table
Funetion
The Load TMS Table process performs the validation of the detail lines contained in
‘TPS_TC_DETAIL and converts:
a) article to horizon product, storing the product id in the article field in the target table
) instruction to horizon product, storing the product id in the instruction field in the target table
©) allowed modes to modes, storing the modes in fields mode_lmode 2 and mode 3 in the target
table, Note that mode_2 and mode_3 will be null if the allowed_modes only maps to 1 mode
Any errors cause just the record containing the error to be rejected, Rows are written to
‘TPS_TC_ERRORS -to identify all the errors in the rejected record.
All valid and complete rows are written to the agent interface table, TMS_TX_TPS_TC_DETAIL.
Interface
‘TPSC284 does not expect any parameters
Rerunability
If the module fails, then it can be rerun.
Design
Note that due to the complexity of this module, the design is described in terms of the following
pseudo code.
Main body of code
Description:
This is the controlling routine in the module to perform the conversion and validation of details
lines in TPS_TC_DETAIL. The following tests are performed:
test error _code
1. Branch exists 007
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 52 of 120
FUJ00090327
FUJ00090327
FUIITSUSERVICES.
TPS HR SAP Summarisation & Transaction Ref:
FUJITSU Corrections HLD Version:
‘COMMERCIAL IN CONFIDENCE Date:
2. Branch exists and has not been permanently closed
3. Branch exists and has not been temporarily closed
4, Allowed modes is valid
5. Quantity is >0 or Value is >0
6. Quantity is > 0 for stock adjustments (modes 91 and 92)
7. Quantity is 0 for mode other than stock adjustment
8. Value is > 0 for stock adjustment
EA/HLD/009
2.02.0
45/1024/11/04
008
009
021
022
043
044
045
9. Transaction correction not received before (both this and previous runs) 046
If any tests fail, then a row is added to TPS_TC_ERRORS.
Funetions Convert To Product and Validate Product are called, performing extra
described below.
Finally, for each row, which contained no errors, a corresponding row is written to
TMS_TX_TPS_TC_DETAIL
Pseudo Code:
Logon to Oracle using OraConnect ().
Perform Initialisation using Startup ()
Perform process control (for restartability) using StartControl ()
if 0 returned (ie. already run successfully), then exit
To allow for rerunning and to delete old/processed messages, delete rows from
TMS_TX_TPS TC DETAIL where actioned _ind = null
For each row in table TPS_TC_DETAIL
If group id exists in TPS_ OUTLETS
then if branch is permanently closed (office_status ="1”)
then write arow to TPS_TC ERRORS with error code = 008
elsf branch is temporarily closed (office_status ="2”)
then write arow to TPS_TC_ERRORS with error code = 009
end if;
else write a row to TPS_TC_ERRORS with error code = 007
end if,
if allowed_modes exists in TPS_TC_MODES_MAPPING
then — extract all values — there will be up to 3 modes
else write arow to TPS_TC_ERRORS with error code = 021
end if;
if quantity = 0 and value = 0
then write a row to TPS_TC_ERRORS with error code = 022
end if,
if allowed _modes is 91 or 92 i.e. stock adjustment
then if quantity-0
then write arow to TPS_TC_ERRORS with error code = 043
fi
tests as
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 53 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref EAMLDiocs
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
if value>0_
then write arow to TPS_TC_ERRORS with error code = 045
fi
else if quantity>0
then write arow to TPS_TC_ERRORS with error code = 044
fi
end if;
if entryexists in TPS'TC_RECEIVED for same SAP_Reference_id and
iteration_flag
then write arow to TPS_TC_ERRORS with error code = 046
end ifs
call function Convert To Product to convert the article to a product
if conversion successful then call Validate Product
call function Convert_To Product to convert the instruction to a product
if conversion successful then call Validate Product
If no errors in the current row
then write row to TMS_TX_TPS_TC_DETAIL specifying:
group id =TPS_TC DETAIL. group _id
SAP reference id = TPS TC _DETAIL.SAP reference id
iteration_flag = TPS_TC_DETAIL iteration_flag
article = prod_id from conversion of article
instruction = prod_id from conversion of instruction
accounting sense = TPS_TC_DETAIL.label_id
value = TPS_TC_DETAIL.value/100 (to convert pence
to pounds and pence)
quantity =TPS_TC_DETAIL.quantity
allowed_modes =TPS_TC_DETAILallowed_modes
mode_1 = 1*\ mode from conversion of allowed_modes
mode 2 = 2" mode from conversion of allowed_modes or null
mode 3 = 3" mode from conversion of allowed_modes or null
message = TPS_TC_DETAIL.message
client reference id =TPS_TC_DETAIL.client_reference_id
processed tmp = null
actioned ind ="N”
write row to TPS_TC_RECEIVED specifying:
SAP reference id = TPS_TC_DETAILSAP reference id
iteration _flag =TPS_TC DETAIL iteration flag
receive date = current date
end ifs
Repeat # all rows in table
Identify the process as having been completed successfully using StopControl()
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 54 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAHLDI009
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Commit all transactions
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 55 of 120
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Convert_To_Product function
Description:
This function converts the specified SAP article to a horizon product, performing the
following tests:
test error_code
1. Article exists 035
2. The article maps to an horizon product with the correct 018
accounting sense
If either tests fail, then a row is added to TPS_TC_ERRORS.
Input Parameters:
file_name, record_number, field_number,
label id - value supplied to label field e.g. “TCINV”
article - value supplied to article field (if not supplied, instruction is supplied)
instruction _- value supplied to instruction field (if not supplied, article is supplied)
Return Parameter:
prod_id — 0 returned if conversion fails
Pseudo Code:
If rowexists in TPS_POL_FS_ARTICLES for specified article or instruction
then if label id= “TCINV
then if article supplied
then if default _prod_posis set
then this is the required product
else write arow to TPS_TC_ERRORS with error code = 018
end if;
else if — default_prod_neg is set
then this is the required product
else write arow to TPS TC ERRORS with error code = 018
end ify
end if,
elsf label_id = “TCCRM”
then if article supplied
then if default_prod_neg is set
then _ this is the required product
else write arow to TPS_TC_ERRORS with error code = 018
end if;
else if default_prod_pos is set
then this is the required product
else write arow to TPS TC ERRORS with error code = 018
end ifs
end if;
end if,
else write arow to TPS_TC_ERRORS with error code = 035
end if;
Ref: EA/HLD/009
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 56 of 120
FUJ00090327
FUJ00090327
FUSITSU
FUIITSUSERVICES.
If product found
then return prod_id
else return 0
end if
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
Corrections HLD Version: 2.02.0
‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
Validate_Produet function
Description:
This function checks the product is valid, performing the following tests:
test error_code
1. Produet exists 036
2. Product is within the start_date/end_date range 037
3. Product can be transacted in all the required modes 038
4, Quantity, if specified, is within the permitted range 039
5. Value, if specified, is within the permitted range 040
6. Value, if specified, is a multiple of price O41
7. Value, if specified, can be adjusted 042
If any tests fail, then a row is added to TPS_TC_ERRORS
Input Parameters:
file_name, record_number, field_number,
prod_id, value, quantity, mode 1, mode 2, mode 3
Return Parameter:
Pseudo Code:
If row exists for specified product in PRODUCT_HISTORIES
then if
then
else
outsidi
write a row to TPS_TC_ERRORS with error code
le date range (defined by start_date and end_date)
37
# carry on with validating the product
For each of modes whieh is not null
repeat
If
then
end if;
If
then
convert to numeric trans mode code, by joining to
TPS_TRANS_MODE_CONVERSIONS
if _noentry exists in PRODUCT_TRANS_MODE_HISTORIES
then write arow to TPS_TC_ERRORS with error code = 038
end if;
quantity > 0
if outside range (< minimum quantity or > maximum quantity)
then write arow to TPS_TC_ERRORS with error code = 039
end if;
value > 0
if outside range (< minimum _value or > maximum_value)
then write arow to TPS_TC_ERRORS with error code = 040
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE Page: 57 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
alien ‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
end if
if overide_price allowed = “N”
then if adjustment_price > 0
then if value isnot exact multiple of adjustment_price
then write row to TPS_TC_ERRORS-code = 041
end if;
elseif (adjustment _price is NULL and retail_price > 0)
then if value is not exact multiple of retail_price
then write row to TPS_TC_ERRORS-code = 041
end if;
end if;
ei
if adjustment_price = 0 or
(adjustment_price is NULL and retail_price = 0)
then write a row to TPS_TC_ERRORS with error code = 042
fi
end if
end ifs
else _ write a row to TPS_TC_ERRORS with error code = 036
end if;
If no errors detected
then — return “Y”
else return “N”
end if;
3:3:34-44.3.
Funetion
‘TPSC285 reads
TPSC285 TC Create Error File
a) table TPS_TC_ERRORS
b) file SPOLFS_INPUT/te_details.bad
and creates an error file for each source file, which contained errors.
Ifalso updates TPS_TC_FILE_DETAIL to record the number of lines which contained errors. If there
were no errors, then no error file is created.
‘The name of the error file is:
SPOLFS_INPUT/if<yyyymmdd><nnn>.err
where <yyyymmdd> is the date of the failed file
<nnn> is the sequence number of the failed file
Each file contains:
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 58 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction
FUSiTSU Corrections HLD
FUIITSUSERVICES.
COMMERCIAL IN CONFIDENCE
¢ Theader line (label_id=”TCERH”)
© anerror line for each error in the table (label_id="TCERR")
1 trailer line (label_id-"TCERT”)
Ref:
Version:
Date:
FUJ00090327
FUJ00090327
EA/HLD/009
2.02.0
45/1024/11/04
When the file has been created, a link is created to it from SPOLFS_AUDIT and the file copied to
SPOLFS_INPUT_SHARE.
Interface
‘TPSC285 does not expect any parameters.
Rerunability
Ifthe module fails, then it can be rerun.
Design
‘TPS Common routines such as OraConnect() and Startup() are used so that, as far as possible, tasks
common to several programs are performed in a consistent fashion and that the program is able to
restart after a failure.
Logon to Oracle using OraConnect ().
Perform Initialisation using Startup ()
Perform process control (for restartability) using StartControl ()
if 0 returned (i.e. already run successfully), then exit
For each distinct file name in TPS_TC_ERRORS and “bad file”
Construct name of error file, by replacing “ten” suffix by “err”
Delete error file, in case this is a rerun
Construct header:
label_id =“TCERH”
date_generated= <date/time file was generated>
file_in error ~ <terminal name of file containing error> e.g. if20040505010.ten
file name
Write header
For each error in current file (recorded in TPS_TC_ERRORS)
Construct error line:
= <terminal name of error file> e.g. if20040505010.err
label _id =*TCERR”
record type = <value from field in TPS_TC_ERRORS>
error_ code = <value from current row>
error_dese <first 30 chars of value from TPS_TC_ERROR_CODES>
record_number= <value from current row>
field_number = <value from current row>
© 2004 Fujitsu Services COMMERCIAL IN CONFIDENCE
Page: 59 of 120
TPS HR SAP Summarisation & Transaction Ref:
FUJITSU Corrections HLD Version:
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date:
Write error line
Increment count
repeat
If “bad file” exists e.g. SPOLFS_INPUT/te_details.bad
For each error in current file (recorded in “bad file”)
Construct error line:
label_id =“TCERR”
record type =“TDZ”
error code = "099"
error dese = “Oracle SQL*Loader error”
record_number= <value from current line>
field_number = 0
Write error line
Increment count
repeat
end if
Construct trailer:
label_id =“TCERT”
count = count of error lines, excluding header and trailer
Write trailer
EA/HLD/009
2.02.0
45/1024/11/04
Calculate number of detail rows, which contained errors (join to TPS_TC_ERROR_CODES on
error_code where record type="TDZ”), Allow for multiple errors on same line (i.e. count
distinct record_number in TPS_TC_ERRORS).
If there were any detail lines in error, then
Update TPS_TC_FILE DETAIL and assign
error_line_count = <value calculated above>
error_in_dil_ flag = “Y”
repeat # all distinct file names in TPS_TC_ERRORS and “bad file”
Create link in SPOLFS_AUDIT to newly created file in SPOLFS_INPUT
Copy file from $POLFS_INPUT to $POLFS_INPUT_SHARE
Identify the process as having been completed successfully using StopControl()
Commit all transactions
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 60 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
FUJITSU TPS HR SAP Summarisation 7 Transaction cas
alien ‘COMMERCIAL IN CONFIDENCE 46/1024/11/04
33.3454.3 TPSC286 TC End Job
Function
FRSC287-TPSC286 tidies up at the end of the Transaction Corrections processing,
In particular, it:
‘* renames the input file(s) to if<yyyymmdd><nnn>.TCN in both SPOLFS_INPUT_SHARE and
SPOLFS_INPUT
© updates TPS_TC_FILE_DETAIL to mark the files as having been processed
«checks whether a file has been received for the current Business Day (provided it is not a +— (Formatted: Bullets and Numbering )
Sunday). If no file has been received, then an alert is raised.
Interface
‘TPSC286 does not expect any parameters.
Rerunability
If the module fails, then it can be rerun.
Design
TPS Common routines such as OraConnect() and Startup() are used so that, as far as possible, tasks
common to several programs are performed in a consistent fashion and that the program is able to
restart after a failure
Logon to Oracle using OraConneet ().
Perform Initialisation using Startup ()
Perform process control (for restartability) using StartControl ()
if 0 returned (i.e. already run successfully), then exit
For each of the current set of files ie. those in TPS_TC_FILE_DETAIL with processed_flag="N”
rename files in both SPOLFS_INPUT_SHARE and SPOLFS_INPUT, to change suffix to
“Ton
update TPS_TC_FILE_DETAIL to set processed _flag="Y”
Repeat
If___ current Business Day is not a Sunday
AND
an entry does not exist in TPS_TC_FILE_DETAIL for if<yyyymmdd>001.ten
where <yyyymmdd> identifies the Business Day
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 61 of 120
TPS HR SAP Summarisation & Transaction Ref:
FUJITSU Corrections HLD Version:
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date:
System
Identify the process as having been completed successfully using StopControl()
Commit all transactions
EA/HLD/009
2.02.0
45/1024/11/04
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 62 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
3.3.44. Volumetrics
33-4.
The following new tables are being added to the database. All are insigi
TMS_TX_TPS_TC_DETAIL
This table contains a row for every valid transaction correction received in the current day.
Tables sizes
icant in size.
Estimated size = 1200 rows
‘TPS_TC_ERROR_CODES
This table contains a row for every error, which is checked for in the txn correction file.
Estimated size = 30 rows
TPS_TC_FILE DETAIL
This table contains a row for every txn correction file received. There will be 1 new one, plus
possible correction files, every day.
Estimated size = will increase by about 300 rows/year
‘TPS_TC_MODES_MAPPING
This table contains a row for every mapping.
Estimated size = 20 rows
‘TPS_TC_DETAIL
This table contains a row for every txn correction detail line in the files received in the current
day.
Assuming
a) a txn correction is raised for 5% of branches each day
») there is 1 detail line per affected branch
Estimated size = 1200 rows
TPS_TC_ERRORS
This table contains a row for every error detected in the files received in the current day.
This will probably be in range 0 to 1200 rows, assuming at most a single error in every
expected detail line ~ although there could potentially be multiple errors detected in a single
ither way, the expected size is insignificant.
3.3.4.24.3.4.2 File sizes
$POLFS_INPUT/if<yyyymmdd><nnn>.ten
This file contains the Transaction Corrections created by POL. for the current day.
Estimated size = 1200 rows or 55KB (as defined in AIS_POLFS)
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 63 of 120
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
SPOLF: <yyyymmdd><nnn>.err
This file contains the errors detected in the input file.
Estimated size = 120 rows or SKB
Housekeeping
3.3:544.3.5.1 Database
‘The following fact & control tables are being added:
TMS _TX_TPS_TC DETAIL
All rows which have been processed (actioned_in
‘TPS_TC_FILE_DETAIL
Old data is deleted by the standard housekeeping process
‘TPS_TC_RECEIVED
null) are deleted every day TPSC
Old data is deleted by the standard housekeeping process
TPS_TC_DETAIL
‘Truncated every day
‘TPS_TC_ERRORS
Truncated every day
‘The files contained in the new host directories described in 5.3.7.1 need to be included in the standard
housekeeping performed in TPSHouseKeep sh.
This is performed by virtue of the update to table TPS_FILES_TO_HOUSEKEEP described in 5.3.6
Note that housekeeping of files in directories whose physical location is on the POLFS host (e.g.
SPOLFS_INPUT_SHARE) is the responsibility of POLES.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 64 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAIHLD/009
FUSiTSU Corrections HLD Version: 2.02.0
FUTSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
S44 POL MIS Data Feed
3.44.
Overview
Prior to $80, TPS sent a whole host of files, which included the transaction files required by POL MIS,
to TIP, via directory STIP_OUTPUT. TIP then sent the files onto POL MIS as well as other locations.
AL S80, TPS will just send the files required by POL MIS to directory STIP_OUTPUT. The other files
will be sent to new interface directories described elsewhere.
‘There are 4 aspects, which need to be considered:
Changing format of files sent to POL MIS
‘Suppression of settlement transactions
Sending additional transaction data for NWB, DCS and BDC transactions to POL MIS
Preventing new events being sent to TIP during the migration
Harvesting additional event information
1
Sending addition transaction data-Changing format of files sent to POLMIS
FUJ00090327
FUJ00090327
‘There are a number of the more recent “specialised” transactions where all that is passed to TIP is the
basic EPOSS Transactional Data. For the affected transactions types (NWB, DCS and BDC) a set of
additional fields has been identified in AIS_POLMIS (Sections BS and B6). These new fields need to
be appended to the lines for these transaction types. However, these new fields must not be sent to
TIP.
‘The inclusion of the extra transaction data must also be allowed for in the code, which processes
rejected MIS files. It already caters for some “additional data” so the basic functionality is there e.g.
OBCS transactions have an extra 3 fields added to the standard set. However, the length of the
“additional data” in the resend tables is increased significantly by these changes.
Differentiation between different record types (CR272),
{Formatted
Currently all transaction detail lines have a Record Type of OTX. To enable it to be easier to identify [Formatted
which records have “additional data”, each type of transaction is to have a different Record Type.
Harvester Table Record Type _—[Formatted
‘TMS_RX_APS_TRANSACTIONS APS
TMS_RX_BDC_TRANSACTIONS BDC
TMS _RX_EFT TRANSACTIONS. Des
TMS RX NWB TRANSACTIONS (application type-NBA) NBS
TMS_RX_NWB TRANSACTIONS (application type=ETU) __ETU
‘TMS_RX_EPOSS TRANSACTIONS OTX
TMS_RX_OBCS TRANSACTIONS OBP
TMS_RX_EPOSS EVENTS EVT
TMS_RX_OBCS STATUSES OBB
NB: Events of Type 913 are sent to TPS via TMS_RX_OBCS STATUSES, instead of
TMS _RX_O!
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE
Page: 65 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction EATHLDI008
FUJITSU Corrections HLD 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE 45/1024/11/04
Changing to handling of signs (CR272). __{ Formatted
‘There are 2 aspects: ~~(Formatted
a) Allow positive and negative values to be populated in the Amount and Quantity fields.
b) Populate Amount field with the sign recorded within Horizon i.e. remove processing convertin;
negative o positive.
Removal of Cash Account and Balance Period fields
The following fields should be removed from the generated files:
cash_account_period, cash_account_day and balance_period
This affects all modules generating or processing the POLMIS files TI 7, TPSC240 and
TPSC
Increasing the length of the Quantity field in the POL MIS Feed (to cater for the Turkish Lira)
This involves:
a) TPSC287 retrieving the quantity from the purchased quantity field of harvester table
TMS_RX BDC_TRANSACTIONS (i.e. for Bureau transactions). For all other transactions types, it
obtains the quantity from the quantity field of the harvester table.
) changing the modules which process the rejected transaction files to cater for the increased length
ie. TPSC229 (possibly), TPSC240 and TPSC232
©) increasing the length of quantity field in TPS_RX_OTX_RESEND_x tables
There is no change to the Quantity field in the TPS transaction tables. This change has no impact on
TPSC201
_—-{Formatted
4.1.2 Suppression of settlement transactions
Currently, there are a number of special products, which are used for settlement transactions. These
“settlement products’ are managed by Fujitsu and do not come from POL as ‘Type A Reference Data.
Historically these products are of no interest to POL and in particular are not passed to TIP. In general
this is achieved by suppressing such products in either the TPS Harvester or in the TPS Host. The
current set of ‘settlement products’ is:
Product Numbers I Category
70639 & 10990 I Revaluations
412128 11213 I Revaluations
11401 ‘Adjustment
44200 & 11207 Transfers
11202-11211, I Rems
11215 8 11216
plus others at S60
41214 Parcel Traffic
11217 - 11208 Rems
11300-17401 ‘Scales
11999 - 12002 BES
© 2004 Fujitsu Services COMMERCIAL IN CONFIDENCE Page: 66 of 120
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
However at S80, these products are being converted to be “normal” products, which will not be
suppressed by the TPS Harvester, and so the replacement products will need to be suppressed,
‘Transactions on these ‘settlement products’ must not be sent to POL MIS (or TIP) and so the
Reference Data is being enhanced to identify them, thereby allowing TPS to suppress them from the
feed to POL MIS. A new field Transfer ‘Txn To MIS is being added to table
PRODUCT HISTORIES to allow identification of such transactions, Note that control of these
products will be passed back to POL, since they need to be aware of those products used for
summarisation to POL FS. This will result in them having new Product Ids allocated.
It should be noted that the products in the list above are present at the counter only. ‘They do not
appear in the product reference data that is presented to TPS. As these products will be eventually
superseded by POL Products, they will appear with new product Ids in the range 1-999. These will
then have the Transfer ‘Txn_To_MIS field set to prevent them being from being sent to POL MIS.
3.4.1.34.4.1.3 Preventing new events being sent to TIP
‘A new group of EPOSS events are being introduced at S80, since there is a requirement to make the
associated actions (e.g. remove excess cash) explicitly visible. These events must not be sent to TIP,
since it will not be expecting them and will get annoyed. However they must be passed to POL MIS as
soon as they start getting harvested. The events affected are:
D Description
55 Trading Statement Created
56 Trading Statement Period rolled
57 Trading Statement Period Roll Abandoned
58 [Excess Cash Removed
59 ‘Cash Shortage Made Good
60 (Cash Variance Report Previewed
61 ‘Cash Variance Report Printed
62 (Outstanding Transaction Correction Reminder Displayed
Note that the above event ID’s are “counter events”, which are translated to “products” by the TPS
Harvester. This requires a change to TMS_RX_EPOSS_EVENTS to increase the length or Event_Id to
Number(10) to accommodate these produet_ids. It is these “products” which will be sent to TPS and
therefore must be temporarily excluded from the feed to TIP. This is achieved by filtering against a list
of products in a new table TPS_TIP_EXCLUDED_PRODUCTS.
Some of the new events require additional information harvesting and passing through to POLMIS.
This involves:
a) adding new fields to the harvester interface table, TMS_RX_EPOSS_EVEI
b) TPSC287 retrieving these new values, and including them in the relevant fields in the POLMIS
files.
‘There is no impact on TPSC201
TS
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 67 of 120
FUJ00090327
FUJ00090327
Harvesting additional event information (Formatted: Bullets and Numbering )
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
4-4-4-64.4.1.5 Migration —{ Formatted: Bullets and Numbering )
‘There are migration issues with the transition to the S80 POL MIS feed. For example, there will be an
overlap period during when the old TIP feed is continuing and new events are being harvested (Points
30 to 40 in Section 5.1). Whilst they could be handled with a single instance of code (TPSC201), it
would mean introducing and retaining extra complexity in the module. Therefore the following
approach is being adopted:
a) create a new module, TPSC287, based on TPSC201, but with extra functionality to
cater for additional fields
suppress the new settlement transactions
cater for increase in length of the Quantity field
cater for additional event information
b) change TPSC201 to
suppress the new S80 events
suppress the new settlement transactions
To allow for a single update to the Maestro schedule (at Point 10), then both TPSC201 and TPSC287
must detect the current position of the migration such that
a) before Point 40,
1 ‘201 is active and generating the transaction files for POL MIS
SC287 does nothing
b) at and after Point 40,
‘TPSC201 does nothing
‘TPSC287 is active and generating the new style transaction files for POL MIS
Also, it should be noted that due to the changing format of the transaction files (i.e. by extension of
quantity field and addition of extra fields), the code will not be able to handle processing of files
which were rejected before Point 40, (ie. old style) and then corrected and re-submitted by SSC after
Point 40, since the code will be expecting new style transaction files at this point.
a4 +——[Formatted: Bullets and Numbering }
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 68 of 120
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
-Code
34:3-44.4.2.1 New Modules
TPSC287 Produce POLMIS Files
‘TPSC287 extracts APS, OBCS, EPOSS, NWB, DCP and BDC transactions, Order Book Status
EPOSS Events data from the TPS Host database and creates a series of flat data files containing the
data extracted for these transactions and events.
Design
This module is based on TPSC201, but with the following changes:
a) Cater for additional fields, by changing functions
« NWB Transaction to include the extra fields specified in AIS POLMIS (Section B6)
Note that that the source table (TMS RX NWB_ TRANSACTIONS) does not include field
merchant_number ~ the field should be set to spaces. This table also includes “E Top-Up”
transactions.
¢ EFT_Transaction to include the extra fields specified in AIS_POLMIS (Section B6)
This function processes the DCS transactions
© BDC_Transaction to include the extra fields specified in AIS_POLMIS (Section BS)
b) Suppress settlement transactions by changing function
«¢ EPOSS_Transaction to omit the settlement transactions.
‘There is a new field in PRODUCT HISTORIES called Transfer_‘Txn_To_MIS ~ set to “Y” or
“N”. Allow for a value of NULL (meaning transfer transaction) ~ this will occur if there is a
delay between migration of the table and the correct data being available in RDDS. It is
assumed that this field will be properly populated before any such transactions are generated.
¢) Remove fields cash_account_period, cash_account_day and balance_period from detail lines.
ed) Cater for the increase in the length of the Quantity field from NUMBER(S) to NUMBER(14) in the
POL MIS feed. For bureau transactions, retrieve the quantity from the purchased_quantity field. For all
other transactions, retrieve it from the quantity field. i.e. as TPSC201 does currently
¢) Identify the different transaction types via the new values for Record Type, as described above.
£) Allow positive and negative values to be populated in the Amount and Quantity fields.
2) Populate Amount field with the sign recorded within Horizon ie. remove processing converting
negative to positive
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 69 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
h) For events, include the values of adjustment_amount and txn_correction_count in the amount and
quantity fields respectively.
i) For events, inchide the value of trading_period in the additional_data field. This should only be done
for events for which the field is non-null in the TMS_RX table.
ej) The module should be active if system parameter “S80 MIGRATION POINT” >= 40. For all other
values, output message saying “Migration Point 40 NOT reached. No action taken”
Note that for all reconciliation totals (including those for Bureau transactions) produced by TPSC287,
the Quantity (not Purchased_Quantity) field will be used.
3:4.3:.24.4.2.2_ Changed Modules
BAS. 244, 1 TPSC201 Produce TIP Files
Funetion
‘TPSC201 extracts APS, OBCS, EPOSS, NWB, DCP and BDC transactions, Order Book Statuses and
EPOSS Events data from the TPS Host database and creates a series of flat data files containing the
data extracted for these transactions and events.
Change
a) Suppress new S80 events by changing function
© EPOSS_Event to suppress the new counter events
This is achieved by suppressing transactions whose product id is listed in
TPS _TIP_ EXCLUDED PRODUCTS. This table will be populated with the set of products
corresponding to the new counter events.
+b) Suppress settlement transactions by changing function
«¢ EPOSS_Transaction — same as in 4.4.2.1.1 ie. use field Transfer_Txn_To_MIS in
PRODUCT _HISTORIES
©) The module should be active if system parameter “S80 MIGRATION POINT” < 40. For all other
values, output message saying “Migration Point 40 reached. No action taken”
3.4:3.2.24.4. rest of file to TIP
‘TPSC229 Strip out errored Sub-files & retur
Function
‘TPSC229 reads the files returned by POL as errored (Original Filename.TPX) together with the
associated log file (TPZ) and generates a good file (for returning to POL) and a bad file (for
processing by the SSC)
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 70 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Change
i
a) cater for increase in the length of the quantity field in the POL MIS feed and the inclusion of
additional fields in the detail lines for BDC, DCS (EFT) and NWB transactions.
(0 MIGRATION POINT” >= 40 then
b) if necessary, cater for change of Record Type from OTX to value identifying the transaction type
all the different transaction types should still be written to the same files
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 71 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction EATHLDI008
FUJITSU Corrections HLD 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE 45/1024/11/04
4.4.2.2.3 __ TPSC240 Load corrected ‘errored Sub-files’ into TPS + (Formatted: Bullets and Numbering )
Function
‘TPSC240 reads in the .OTE, .CAE and .STE files created by SSC and loads the details into the
TPS_RX_OTX RESEND, ‘TPS_RX_STX RESEND, TPS_RX_CAC_RESEND or
‘TPS_RX _CLT_RESEND tables as appropriate, These tables are read by a later process to generate
new files for sending to TIP.
Note that the CAE and STE files will no longer be relevant afier Point 40,
Change
If“S80 MIGRATION POINT” >= 40 then
a) cater for increase in the length of the quantity field and the inclusion of additional fields in the detail
lines for BDC, DCS (EFT) and NWB transactions. All the additional fields are to be included in an
extended additional data field in table TPS _RX_OTX_RESEND. The affected function is
ProcessOTXSubFile
b) cater for removal of fields: cash_account_period, cash_account_day and balance_period
©) extend the check on record type=OTX_RECORD_ PREFIX to include checks on all record types Al Formatted )
which will now be included in the file i.e. as identified in Section 4.4.1.1 , to ensure that all lines will
be written to TPS_RX_OTX_RESEND
Formatted }
‘bd) when called for parameters CAE and STE, then exit doing nothing
34.3:2:34.4 ‘TPSC232 Create TIP OTX Resend File
Function
‘TPSC232 takes any data in the TPS RX_OTX_ RESEND table and uses it to create a transaction file
for sending to POL.
If “S80 MIGRATION POINT
a) cater for increase in the length of the quantity field and the inclusion of additional fields in the detail
lines for BDC, DCS (EFT) and NWB transactions.
b) cater for removal of fields: cash_account_period, cash_account_day and balance_period
= 40 then
© 2004 Fujitsu Services COMMERCIAL IN CONFIDENCE Page: 72 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
FRTS Data Feed
Overview
The FRTS feed consists of two files:
a) a Bureau Transaction Data file (BTD), which contains the base data
b) a Bureau Control Total file (BCT), which contains aggregations by Cash Account Period, Margin
Product and Transaction Mode Code
Cash Account Periods will no longer be used in $80 (they are being replaced by Trading Periods) and
therefore the BCT file will just be aggregated by Margin Product and Transaction Mode Code.
[DN: Not yet confirmed, but this is the working assumption]
Note that the Cash Account Period and Balance Period fields will still be in the generated file at $80,
but they will both be null, [The necessary change to the table constraints is defined in Section 5.6]
With the removal of TIP, TPS has assumed responsibility for sending the output files to numerous
different locations, as summarised in 4.1
This has implications on the feed of the Bureau files, which are transferred by POL to FRTS. Prior to
$80, TPS output them to the directory identified by EV STIP_OUTPUT. At $80, they will be output to
the directory identified by EV SFRTS_OUTPUT.
Note that a change is required by POL to enable EDG to access the bureau files from the new interface
directory. The interface between TPS and FRTS is defined in AIS_FRTS
35.24.5.2 Code
changed Modules
3.5.2444.5.2.1.1 _TPSC271 Generate Bureau Feed
Funetion
TPSC271 produces the Bureau de Change transactions feed for FRTS. It consists of two files — the
BTD file and BCT file.
Change
Remove Cash Account Period from the aggregation which is performed for writing to the BCT file ie.
just perform aggregation on Margin Product and Transaction Mode Code.
The bureau files are now delivered to a different FTMS service. Therefore change call to
StartExportFile to pass in dest = “F”, to cause them to be output to the directory identified by EV
SERTS_OUTPUT.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 73 of 120
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
3.64.6 Client Transmission Summaries Data Feed
3.644. Overview
With the removal of TIP, TPS has assumed responsibility for sending the output files to numerous
different locations, as summarised in 4.1
This has implications on the Client Transmission Summaries file, of which one is created every day.
Prior to S80, TPS output it to the directory identified by EV STIP_OUTPUT. At S80, it will be output
to the directory identified by EV SCTS_OUTPUT.
‘There is no change to the content of the file.
3.6.24.6.2 Code
3.6-244.6.2.1 Changed Modules
3.6.2444.6.2.1.1 _TPSC223 Create CTS File
Funetion
‘TPSC223 reads a flat file containing APS Client Summaries sent from the APS Host and generates a
file that is later sent to TIP via FTMS. TPSC223 passes the CLT records present in the file sent from
APS Host as it is but regenerates the Sub-File Header/Trailer and File Header/Trailer.
Change
‘The CTS file is now delivered to a different FTMS service. Therefore change call to StartExportFile to
pass in dest = “C”, to cause it to be output to the directory identified by EV SCTS_ OUTPUT
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 74 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES ‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
374.7 Removal of Cash Account Processing
3-7.
‘The move from a
schedules and proc
Overview
me week Cash Account Period” to a “monthly Trading Period” means that all the
es concerned with Cash Accounts can be discontinued or made inactive.
‘The Host Cash Account Reconciliation is being stopped in Point 10. Therefore all modules, which are
directly connected with it, can be removed from the codes set and the Maestro schedule at this point.
The affected modules are:
TPSC265 TPS CAC Transaction Processing
TPSC266 TPS CAC Stock Processing
TPSC267 Generate Non Leaf CAC Lines
TPSC268 Compare counter generated CA with HOST
All other modules related to Cash Account processing need to remain active until Point 40, at which
point they will become inactive. This will be achieved within the code, so that only one update to
Maestro is required (at Point 10). The affected modules are:
TPSC238 Resend corrected CAC & STK files
TPSC251 CA Control Reconciliation
TPSC252.__ Counter reported CA Reconciliation Errors
TPSC25S Create TPS_CA_LINE_COMAPARISONS table
TPSC256 CA Line Comparison Report
TPSC269 Offices with Over due CA
In addition, the process that generates CAC / STX files for TIP (TPSC225) needs to suppress any cash
accounts that are greater than the “final cash account”, since there will be a short period during the
migration when branches will be generating “cash account data” post the final cash account,
Finally, the process which swops the ‘A’ and ‘B? tables at end of day (TPSC209) no longer needs to
perform actions on the “cash account tables”
3.7.24.7.2 Code
3.7.2:44.7.2.1 Changed Modules
3-Fedd44.7.2.1.1 TPSC225 Create TIP CAC/STX files
Funetion
225 program creates the ‘Cash Account and Stock Holding’ files for the TIP interface. A file is
produced for each non-empty partition of the TMS RX CASH ACCOUNTS por
TMS_RX STOCK HOLDINGS p tables.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 75 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Change
Suppress data if cash_account period is > than value defined in “FINAL CBDB CAP” in
‘TPS_SYSTEM_PARAMETERS.
‘The module should be active if system parameter “S80 MIGRATION POINT” < 40. For all other
values, output message saying “Migration Point 40 reached. No action taker
3.7.24.24.7.2.1.2 __TPSC209 Drop temporary tables and swap A and B sets of TMS tables
Funetion
‘TPSC209 truncates the TMS interface tables and resend tables ready for the next days processing,
Change
Do not perform any actions on the following tables:
TPS_CASH_ ACCOUNTS pp
TPS_CASH_ACCOUNTS_ARC
where pp is the partition number
Cash Account Processing Modules
Function
The following modules are all concerned with processing Cash Account data
TPSC238 Resend corrected CAC & STK files
TPSC251 CA Control Reconciliation
TPSC252 Counter reported CA Reconciliation Errors
Create TPS_CA_LINE_ COMAPARISONS table
CA Line Comparison Report
TPSC265 TPS CAC Transaction Processing
Change
‘The modules should be active if system parameter “S80 MIGRATION POINT” < 40, For all other
values, output message saying “Migration Point 40 reached. No action taken”
+ —{Fermatted: ules and Numbering }
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 76 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAHLDI009
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 77 of 120
TPS HR SAP Summarisation & Transaction Ref:
FUJITSU Corrections HLD Version:
FITSU SERVICES COMMERCIAL IN CONFIDENCE Date:
3.7.34.7.3 Schema
3.7,344.1.3.1 Tables to be identified as redundant
The following Cash Account tables should be identified as redundant in documentation:
Cash Account Reference Tables
FINIANCIAL_YEARS
ACCOUNTING_WEEKS
CASH_ACCOUNT
C_A CODES
TABLES
C_A SUB_TABLES
"A _LINE_CODES
_A_TABLE_LINES
C_A NODES
DEN_FINANCIAL_YEAR_C_A_VERSION
DEN_PRODUCT_CAC_MAPPING
DEN_CAC_PARENT_CAC_MAPPING
PROD_TRANS_ MODE CODES
All above tables are populated by TPSC207
Re-con
jon tables
TMS OUTLET IN PARTITION
TPS _BAL_DUE_TO POST OF
TPS_CASH_ACCOUNTS <p>
TPS _CASH_ACCOUNTS ARC
TPS _CA LINES TO COMPARE
TPS_CA LINE COMPARISONS
TPS _CA TOTAL LINES
TPS _CA TOTAL LINES ARC
TPS_C_A TABLE MAPPING RULE.
TPS _RX_CA COMPLETION EVENTS
TPS_RX_CA_COMPLETION-EVENTS
TPS_SH_TOTAL LINES
TPS_SH_TOTAL LINI
nd tables
‘TPS_RX_CAC_RESEND_A & _B——
TPS_RX_STX_RESEND_A & _B
AR
Harvester Tables
TMS_RX_CASH_ACCOUNTS_<p><s>
TMS_RX_CA_CT EXCEPTIONS
‘TMS_RX_CA_CT_EXCEPTIONS_ARC
TMS_RX CA_TOTAL LINES
TMS RX CA TOTAL LINES ARC
TMS RX SH TOTAL LINES
EA/HLD/009
2.02.0
45/1024/11/04
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 78 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAIHLD/009
FUJITSU Corrections HLD Version: 2.02.0
FUTSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
TMS RX SH TOTAL LINES ARC
TMS_RX STOCK HOLDINGS_<p><s>
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 79 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal ‘COMMERCIAL IN CONFIDENCE Date: 15/4024/11/04
4.5. MIGRATION
445.1 Overview
‘The main migration issues are described in a separate migration HLD (MIGRATION). The following
diagram, which summ:
Point 10 Point 20
Phase A
Horizon Data
Centre Migration
Horizon Counter
Software Migration
arises the main events, is extracted from this document:
Point 25 Point30 Point 40 Point 50
Sn. SAP ER SAP HR
en rom CBDB/ OPTip From TMS data
NewS on
switch ho new
Pals OPTip ed off
= Phase B Phase C
—
Foal
Final FB cash fours
cash Account
Diserenf week i
‘Sema Tine itfrent branches
tw ALL branches
POLES
cape
The events, which are
© Point 10
© Point 25
© Point 30
© Point 40
© Phase C
relevant to this document, are:
Migration Preparation - 5.2
Horizon Data Centre Migration~ see 5.3
Start POL FS summarisation — see 5.4
Final CBDB Cash Account — see 5.5
Switch off feed to TIP and start replacement feeds to POL MIS, FRTS and
CTS ~see 5.6
Switch over HR SAP extraction from CBDB to TPS ~ see 5.7
Further migration details for TPS are included in TPS_POLFS_HLD and TPS_AGENT_HLD
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE Page: 80 of 120
TPS HR SAP Summarisation & Transaction Ref: EA/HLD/009
FUJITSU Corrections HLD Version: 2.02.0
PUITSUSERMCES COMMERCIAL IN CONFIDENCE Date: 16/1024/14/04
4.25.2 Migration Preparation
‘These actions should be performed during the run-up to the start of the migration proper
POL actions:
# allocate filestore to be used for transaction corrections and any error files generated
Fujitsu actions:
* mount above filestore so that it can be accessed via an NFS share
4.35.3 Horizon Data Centre Migration
Thi
performed at Point 10
‘The migration of the data centres is concemed with getting the TPS host boxes and associated systems,
in the correct state. As much as the migration as possible, is performed at this point. The following
steps are involved:
© migrate RDMC/RDDS ~ see 5.3.1
© migrate the DWH ~ see 5.3.2
© install TPS host software see 5.3.3
© prevent settlement transactions and new events being sent to TIP ~ see 5.3.3
© enable new Maestro schedules to perform POL FS (Initial summarisation only), HR SAP &
Transaction Correction processing ~ see 5.3.4
© create tablespace ~ see 5.3.5
* schema migration — see 5.3.6
© change environment of TPS users e.g. to create new directories and EV’s ~ see 5.3.7
* enable the new FTMS services ~ see 5.3.8
© install changed TPS Harvester Agent and new TPS Bulk Loader Agent
Migrate RDMC/RDDS
All the new tables required for the POL FS and HR SAP summarisation should be created, ready for
receiving the new reference data off nRDS (the POL system which feeds RDMC). See AIS_RDDS for
description of tables.
Note that the new data may not be available immediately ~ it will probably filter through during the
following days.
This is not a problem for either the HR SAP or Transaction Correction processing. since:
a) the HR SAP summarisation code includes an initial check that the transaction occurred in a CAP
after the identified Final CAP. Therefore all the transactions during this period will be ignored
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 81 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
b) Transaction Corrections will not be submitted until significantly later (sometime after Point 25 in
the Migration)
4,3.25.3.2 _ Migrate the DWH
To cater for a change in the Outward Delivery File generated by TPS (to report on delivery of HR SAP
and CTS files), the following change is required:
© Change OUTWARD_DF_DELIVERY_W<nnnyyyy> so fad_code is nullable
Note: This does not require any change within TPS but will require a change to DWH at Point 10,
4,3.35.3.3 Install TPS Host Software
All new/changed software described in this document should be installed in the standard directories.
Note that the following modules have changed:
HR Sap Changes
TPSC211 Start of Day
Will cause a partition of the HR SAP summary table to be created at the beginning of each
month,
TPSC210 Harvest Receipt Info
Will look for new acknowledgment files at the gateways. These won't exist yet and therefore
the changes will have no impact
TPSC206 Create Delivery File
Will report on delivery of the HR SAP files twice a month, Reference data will determine when
the first HR SAP delivery from TPS is performed. Will have no impact.
‘TPSC207 Harvest Branch Info from RDDS
Will cause the new HR SAP reference tables to be populated and stop populating redundant
Cash Account tables, Note that this is dependant on the successful migration of RDDS.
POL MIS Chang
‘TPSC201 Produce TIP Files
Will suppress settlement transactions and the new events being sent to TIP. Note that the
suppression of settlement transactions is being performed using a new field,
transfer_txn_to_mis, which is being added to PRODUCT_HISTORIES. We are dependant are
not receiving any such transactions until the field has been populated in RDDS ice. the field
must be populated in advance of such transactions being sent to TPS,
‘The new events will not start turning up until the counters are migrated (Point 20), but might as
well get the code in place ready.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 82 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
TPSC229 Strip out errored Sub-files & return rest of file to TIP
TPSC240 Load corrected ‘errored Sub-files’ into TPS
FPSC240-Lead a d Sub-files inte TRS
TPSC232 Create TIP OTX Resend File
All above are being changed to cater for inclusion of extra transaction data in additional_data
field and increase in length of Quantity field.
Will have no impact, since includes a check on the migration point.
Bureau Chang
TPS
‘271 Generate Bureau Feed
Will have no impact, since the initial value of $FRTS_OUTPUT will be the same as
S$TIP_OUTPUT
c anges
‘TPSC: Create CTS File
Will have no impact, since the initial value of SCTS_ OUTPUT will be the same as
STIP_OUTPUT
Removal of Cash Account Processing Changes
Will suppress cash accounts > “final cash account”
Becomes inactive at Point 40
‘TPSC209 Drop temporary tables and swap A and B sets of TMS tables
Will stop processing the “cash account tables”
Resend corrected CAC & STK files
CA Control Reconciliation
Counter reported CA Reconciliation Errors
Create TPS_CA_LINE_COMAPARISONS table
TPS CA Line Comparison Report
TPSC269 Offices with Over due CA
All above will become inactive at Point 40
4,3.45.3.4__ Enable new Maestro Schedules
Enable the new Maestro Schedule.
Note that there is only one update to the schedule and therefore if modules require to either become
active or inactive at certain points in the schedule, then this is handled within the code, For example,
‘TPSC201 remains active until Point 40 ~ after that point it is inactive
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 83 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EA/HLD/009
FU ITSU Corrections HLD Version: 2.02.0
FITSU SERVICES ‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
43:55. Create Tablespace
‘The following tablespace is required for the new fact data
TPS_FACT DATA
43.0;
The schema migration is concerned with creating/changing tables and populating new tables where
required.
A patch is required to:
Schema Migration
4.3.6.15.3.6.1 Create new tables
‘They are either created on existing tablespace TPS_REF_DATA or new tablespace
‘TPS_FACT DATA as identified:
a) HR SAP
TPS HR_SAP_CTT NUMBERS on TPS _REF DATA
TPS HR_SAP_DEF PERIODS on TPS _REF DATA
TPS HR_SAP_MAPPINGS. on TPS REF DATA
TPS_HR_SAP_SCHEDULES. on TPS_REF_DATA
TPS _HR_SAP_DAILY SUMMARIES on TPS_ FACT DATA
TPS HR_SAP_ MONTHLY SUMMARIES on TPS FACT DATA
TPS_HR_SAP_SUMMARIES. on TPS_ FACT DATA
This table is partitioned.
Initially just 2 partitions are created by the migration script, for periods 200501 and
200502. Note that these will not have any transactions stored in them as this is well
before the expected Point 30. Therefore, the required CREATE TABLE is :
CREATE TABLE tps_hr_sap_summaries (
PARTITION BY RANGE _(target_period_id)
IBPARTITION BY HASH (aroup id) SUBPARTITIONS 64
PARTITION tps_hr_sap summaries 200502 VALUES LESS THAN (200503) TABLESPACE.
tps_fact_ data
Sk
‘A new partition is created by TPSC211, when TPS_HR_SAP_DEF PERIODS has
been populated AND the current date falls within the date range of one of the periods.
‘The following month the 4" partition is created by TPSC211
b)Transaction Corrections
TPS TRANS MODE CONV! IONS on TPS_REF_DATA
TMS_AWT_TP: DETAIL on TPS_ FACT DATA
TMS_TX_TPS_TC_DETAIL, on TPS_FACT DATA
‘TMS_EXCPTNS. on TPS_FACT DATA
TPS TC_ERROR CODES on TPS_REF DATA
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 84 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
alien ‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
TPS _TC_FILE DETAIL on TPS FACT DATA
TPS_TC_MODES MAPPING on TPS REF DATA
TPS _TC_RECEIVED on TPS FACT DATA
TPS _TC_DETAIL on TPS_FACT DATA
TPS TC_ERRORS on TPS_FACT DATA
© POL MIS feed
TPS_TIP_ EXCLUDED PRODUCTS on TPS REF DATA
4:36.25. Create new sequences
Create the following sequences for use by the loader agent.
All sequences require synonyms of the same name.
« TMS SCHEDULE SEQ
ie, CREATE SEQUENCE TMS_SCHEDULE_SEQ MAXVALUE 99999 MINVALUE 1 CYCLE
Associate sequence with column TMS_AWT_TPS TC DETAIL schedule id
* TMS SEQ AWT TPS_TC DETAIL
Note that size and other attributes of the sequence do not matter as the agents software drops
and re-creates the sequence every time an agent schedule is run
Associate sequence with column TMS_AWT_TPS_TC_DETAIL.chunk_seq_no
* TMS SEQ EXCPTNS
ie, CREATE SEQUENCE TMS_SEQ_EXCPTNS MINVALUE 1;
Associate sequence with column TMS_EXCPTNS.exeptn_seq
.3_ Change definition of existing tables
TMS_RX_EPOSS_ EVENTS
The existing data must be retained
The new fields, adjustment_amount , txn_correction_count and trading_period
should be set to NULL Note that the event_id field is being changed to NUMBER(10)
The affected tables are:
‘TMS_RX_EPOSS EVENTS ppx
‘TPS FILE REGISTER
The existing data must be retained,
All new fields should be defaulted to NULL
Change Allowable Values of file_type field to be ‘M’, ‘T”, *C’ ,"F” and ‘S?
‘TPS_OUTLETS
The existing data must be retained,
‘The new fields, office_status and hr_sap_group should be set to 0 & NULL
respectively ~ they will be replaced by the correct values as soon as it is
available in DDS
‘TPS _RX_OTX_RESEND_A
‘TPS_RX_OTX_RESEND_B
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 85 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EA/HLD/009
FUJITSU Corrections HLD Version: 2.02.0
PUITSUSERMCES COMMERCIAL IN CONFIDENCE Date: 16/1024/14/04
The existing data must be retained
Change
a) length of additional_data field to accommodate the extra transaction
data being held for DCS and NWB transactions by increasing its length to
VARCHAR2(182)
b) length of quantity field to NUMBER(14)
PRODUCT HISTORIES.
The existing data must be retained,
‘The new fields should be set to NULL - they will be replaced by the correct,
values as soon as it is available in RDDS
.3.6.4 Populate new TPS reference tables
a) Transaction Corrections
TPS _TC_ERROR CODES
The values are defined in section 4.3.2.2
‘TPS_TC_MODES MAPPING
The values are defined in document AIS_POLFS (Appendix 7)
b) POL MIS feed
‘TPS_TIP_EXCLUDED_PRODUCTS
The values are to be supplied by POL prior to the migration
436,553.65 Truncate redundant Cash Account tables
The following tables require truncating.
FINIANCIAL_YEAR
ACCOUNTING WEEKS.
CASH_ACCOUNT
C_A CODES
C_A TABLES
“A_SUB_TABLES
C_A_LINE CODES
C_A TABLE LINES
C_A_NODES
DEN_FINANCIAL_YEAR_C_A_VERSION
DEN_PRODUCT_CAC MAPPING
DEN_CAC PARENT CAC MAPPING
PROD_TRANS MODE_CODES
TPS_CASH_ ACCOUNTS pp (where pp= 1 to 64)
TPS_CASH_ACCOUNTS_ARC
4.3:6,65.3.6.6 Update metadata
a) Add new parameters to TPS_SYSTEM_PARAMETERS
Note that the “sequence” and “final cbdb cap” parameters are just given default initial values. The
actual values must be provided by POL, and captured in the table before Point 30.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 86 of 120
FUSITSU
FUNITSUSE:
VICES
TPS HR SAP Summarisation & Transaction
Corrections HLD
COMMERCIAL IN CONFIDENCE
b)Add entries to TPS_PROCESSES for all new processes
specify
Process name
values:
TPSC280
) Add
TPSC281
TPSC282
TPSX283.sh
TPSC284
TPSCIaS
TPSC28G
TPSC287
table housekeeping performed by TPSC212
specify
values:
Ref: EA/HLD/009
Version: 2.02.0
Date: 15/1024/11/04
entry to TPS_ARCHIVED_TABLES to identify new tables to be included in the standard
alias
application I table_alias I tablename
‘smp_column_name
archive_type
TPS TED ‘TPS_TC_FILE_DETAIL I receive date
RP
TPS TR TPS_TC RECEIVED _ I receive date
RP
directory
retention_period I archive threshold
additional_criteria
Tvnw0l tps/tpsarch
365 null
null
Tbvaw0 I ipslipsarch
30 ‘ull
ull
4d) Add entries to TPS_FILES_TO_HOUSEKEEP to identify new file types to be included in the
standard filestore housekeeping performed by TPSHouseKeep.sh
specify
values:
directory_name file_id Tetention_period delete_subdir
7benw0 I/ipsitrans/polmis pare ny N
Tovnw0i ipsirans/polmis “pr a N
Tovnw0 psitrans/polmis * 35 N
Tovnw0iipsitrans/hrsap * 30 N
Tovaw0 ipstransiets . q N
Tovnw0 I ipsirans/irts * a N
Tovaw0i/ipsitrans/pols input I * a N
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE
Page: 87 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction EATHLDI008
FUJITSU Corrections HLD 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE 45/1024/11/04
43:6:75,3.6.7 Change Role Defi
The following roles already existing in the TPS database. They require the identified object privileges
adding:
Role Name ‘Objects Privileges Required
BST Allnew tables (Gee 5.3.6.1) SELECT
‘MONITORS Allnew tables SELECT
TPS BATCH ‘Allnew tables SELECT, UPDATE, INSERT, DELETE
‘TPS_AGENTS TMS_TX_TPS_TC DETAIL SELECT, UPDATE
TMS_AWT_TPS_TC_DETAIL SELECT, UPDATE, INSERT, DELETE
‘TMS_EXCPTNS SELECT, UPDATE, INSERT, DELETE
4.3.6.85.3.6.8 Create User
A new user, TPS_LOADER_AGENT, with Role=TPS_AGENTS, is required for the new loader agent.
Check Constr
‘The following constraints should be changed. Note that some further constraints are changed at Point
40 (see 5.6)
1) Extend the following Check Constraint
Transaction Mode Id in
(1, 2, 3,4, 5, 7, 8, 9, 10, 11, 12, 13, 14,15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28)
to include new modes (29,30,31,32,33,34)
‘This change affects the following transaction tables:
TMS RX ttt TRANSACTIONS ppx
TMS _RX_ttt TRANSACTIONS 65
where_ttt= APS, BDC, EFT, EPOSS, NWB and OBCS
pp=1 to 64
=A andB
2) Add new Check Constraint
“TRADING _DATE" is not null
This change affects the following transaction and event tables:
‘TMS RX ttt TRANSACTIONS ppx
‘TMS RX ttt TRANSACTIONS 65
TMS_RX_ttt TRANSACTIONS RC
T RX_EPC VE ppx
TMS_RX_EPOSS_ EVENTS _65
© 2004 Fujitsu Services COMMERCIAL IN CONFIDENCE Page: 88 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction EATHLDI008
FUJITSU Corrections HLD 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE 45/1024/11/04
TMS RX _OBCS STATUSES ppx
TMS RX OBCS STATUSES 65
TPS _RX_OTX RESEND x
where_ttt= APS, BDC, EFT, EPOSS, NWB and OBCS
" ffeots-all-Ti Sapa bh
4,3.75.3.7__Change environment of TPS Users
4.3.7.15.3.7.1_ Create Host Directories
New directories are required on the TPS host to support the extra FTMS services described in 4.1.1.1
/ovnw01/tps/trans/polmis
/ovnw0/tps/trans/ets
/ovnw0/ips/trans/firts
Jovnw01/ips/trans/hrsap
New directories are required on the TPS host for Transaction Corrections.
Jovnw0 /tps/trans/polfs input
A new directory is required on the POLFS host for Transaction Corrections. Note that it is created in
the share created in 5.2
Jovnw01/tps/trans/polfs_input_share
43:7.25,3.7.2 Environment Variables.
The following EV’s need to be created in all users on the host system, which run the TPS schedule:
Variable Value
CTs_OUTPUT ovnw0lipsiransitip __# see below
FRIS_OUTPUT Tovnw0lipsitanstip __# see below
TIRSAP_OUTPUT Tovnw0ltpsitranvhrsap
POLFS_INPUT Tvnw0 I tpstrans/polls_input
POLFS_INPUT_SHARE Tovnw0l ipsltans/polls_inpat_share
NB: CTS_OUTPUT and EFRSERTS OUTPUT must be set to same value as STIP_OUTPUT initially, since TIP
is still carrying on with normal processing at this stage, and will continue doing to until Point 40. Also, at this point,
© 2004 Fujitsu Services COMMERCIAL IN CONFIDENCE Page: 89 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
4,3-85.3.8 Enable the new FTMS services
This will have no impact at this stage, since all files will continue to be output to the old “tip”
directory, and therefore be transferred from it, until the relevant EV’s are re-assigned at Point 40.
4.45.4 Start POL FS summarisation
This is performed at Point 25.
At this stage, the new POL FS interface is made available. It should coincide with the commencement
of the POLFS summarisation.
Details are defined in TPS_POLFS_HLD.
4.55.5 Final CBDB Cash Account
This is performed at Point 30.
It is the point at which the final Cash Account is generated for a branch. The number of the final Cash
Account for all branches will be the same. However, the generation of this Cash Account will occur at
different points in time, although the majority will be generated over several elapsed days.
Once the number of the final CAP has been confirmed, check the following parameters in
§_ SYSTEM_PARAMETERS, and if necessary adjust them
INAL CBDB CAP
PIVOT FILE SEQ
PIVOT2 FILE SEQ
4.65.6 Switch off feed to TIP and start replacement feeds
This is performed at Point 40
Between Points 30 and 40, there will still be cash account information coming from some branches
(following non-polling), which will need to be sent to CBDB. The CAC & STX feed to TIP will
contain only those branches, which have not produced their final CBDB Cash Account (any cash
account data post the final Cash Account will have been suppressed by a new version of process
‘TPSC225 introduced at Point 10)
‘Therefore the interface to TIP needs to maintained during this period i.e. TIP will continue to process
all the txn files as well as the FRTS and CTS feed files.
‘Once all Final Cash Accounts have been sent through, it is then possible to:
a) switch off the feed to TIP
») start new feed to POLMIS
©) stop processing all Cash Account and Stock Holding data
4) relax some of the constraints on the TPS Harvester interface tables
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 90 of 120
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Note that a), b) and c) are all performed automatically at this point by the code, and therefore no
changes to the maestro schedule are required.
‘The completion of final cash accounts will be monitored from OpTIP data.
Fujitsu actions:
1) change the following EVs to their final values, so that the 3 sets of files are output to different
directories, instead of .../tip as was the case up to Point 40.
‘TIe_OUTPUT = fovnw01 /tps/trans/polmis
FRTS OUTPUT = /bvnw01/tps/trans/fits
CTS_OUTPUT = fovnw0/tps/trans/ets
2) execute script to remove the following constraints on harvester tables
"BALANCE, PERIOD" is not null
balance_period >= 0
"CASH_ACCOUNT PERIOD" is not null
cash_account_period >= 0
This change willaffects all-the following’F transaction tables:
TMS _RX_ttt TRANSACTIONS _ppx.
TMS RX ttt TRANSACTIONS 65
T RX_ttt_ TRA! RC
where_ttt= APS, BDC, EFT, EPOSS, NWB and OBCS
pp=1 to 64
Aand B
Therefore, for each transaction type, there are 130 tables affected
POL actions:
1) disable TIP
2) change the processes which access the POLMIS, FRTS and CTS files to accept the files from the
new locations
3) commence the generation of transaction corrections
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 91 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transact Ref: EA/HLD/009
FUJITSU Corrections HD Version: 2.02.0
FUTSU SERVICES ‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
Bi.
5.7__ Switch over HR SAP extraction from CBDB to TPS +— Formatted: Bullets and Numbering )
This is complicated somewhat by the current situation whereby there are 2 levels of deferment
applicable to transactions, which are included in the 2 files delivered to HR SAP each month,
Allowance must be made for completing the “final cash account period” during the day. In all cases,
transactions, which occur before the final CAP, must be sent to CBDB and those after the final CAP
must be summarised for sending to POL FS and HR SAP. The exact time varies from branch to branch
and therefore the initial summarisation table, TPS_PROD_MODE_SUMMARIES, identifies the cash
account period, which the transactions are included in, This enables the HR SAP summarisation to
exclude all transactions which will have been sent to CBDB and so there will be a clean switch over
for all branches.
For example, consider the following:
Extracts fom TP:
geo
Extracts from CBDB FPO ‘SPSO
Point 30 ”*** 7
4s,
Ereok “hkI sso
I __»I
es
48 Sf 4 2a ae I 236 .
hy 7
Transecions I [ Level 1] Yevel 17) [evel t I
mcrpos II Level2 [evel 2! Level 2
i if
Transactions I { Level 1 I} "Level 4 Level 1!
arseses I [Level 2!) Level 2! Level2
ae 200501 200502 200503.
All transactions before Point 30, will be passed to CBDB, for subsequent inclusion in HR SAP
extracts, All transactions after Point 30, will be summarised by TPS, which will subsequently generate
the HR SAP extracts.
‘The penultimate extract from CBDB (during period 200501 in above diagram) will be a complete
extract. To keep it simpler, this is not shown in the diagram,
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 92 of 120
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
The final extract from CBDB (during period 200502) will NOT be a complete extract, since the Level
1 transactions from SPSOs (identified above) will not have been passed to CBDB.
‘Therefore the complete HR SAP feed for this period comprises:
CFPO extract from CBDB, consisting of all transactions from period 200412
'SPSO extract from CBDB, consisting of Level 2 transactions from period 200412
SPSO extract from TPS, consisting of Level I transactions from period 200501
Note that the two SPSO extracts will have the same sequence numbers (i. filenames), but will be
output to different directories as controlled by CBDB and TPS. It is POL’s responsibility to handle
the merging of this data for HR SAP.
The next extract, and all future ones, will be from the new HR SAP summarisation table in TPS, with
the date being controlled by RDDS/TPS reference data,
‘The first such extract comprises:
CFPO extract from TPS, consisting of all transactions from period 200501
SPSO extract consisting of _ Level 1 transaction from period 200502
Level 2 transactions from period 200501
Note that:
1. The switching on of all the new processes at Point 10 will cause HR SAP extraction process to be
run from this point. If the reference data in TPS HR_SAP_ SCHEDULES is such that no deliveries
are due in the first few periods, then none will be attempted. Even if the extracts were done, the files
generated would be empty, since the transactions will not have been summarised into
TPS_HR_SAP_SUMMARIES
2. Need to allow for the fact that a branch may roll Point 30 early, so the calendar must ensure that all
transactions with a CAP greater than the Final CAP that occurred before that period, are included
This can be achieved by adjusting the start date of that period to be say a week earlier.
3. No credence should be read into Point 30 in above example being at the end of period 200512 ~ all
that is required is that it coincides with a month/period end.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 93 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref EAMLDiocs
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
5.6. APPENDIX 1: Database Schema
Note that alteration to Check Constraints is defined in Sections 5.3.6.9 and 5.6
46.1 Fact Tables
5,446.11 HR SAP Tables
Tablename I TPS_HR_SAP_DAILY SUMMARIES
This is an intermediate summary table, which is populated with HR SAP summaries for
transactions harvested in the current daily run. The majority of the transactions will be for
the same day, but they could also include some late harvested transactions from earlier days.
‘The source data is the initial summary table, TPS_ PROD_MODE_SUMMARIES.
It contains a row for every period/branch/CTT number for which there were transactions in
the current run, It is used to update table TPS_HR_SAP_SUMMARIES
It is truncated at the beginning of the process which populates it and therefore the data for
the previous run is available until the start of the next run.
Column Type Null Description
target_period id Number(6) N_ I The HR SAP Period into which the data
will be included when the file is generated,
Format is yyyymm,
group id Number(6) N_ I A unique identifier that identifies a branch,
This attribute corresponds to the FAD
Code.
hr_sap_group Varehar2(10) I N IThe HR SAP Group, which the branch
identified in the source table, maps to
ett_number Varchar2(10) N_ I The CTT number which the product/mode
identified in the source table maps to.
accounting sense I Varchar2(1) I N I The accounting sense of the product/mode,
derived from PRODUCT TRANS MODE HISTORIES
either “#” or “-”
quantity Number(14) N__I Number of items transacted
amount Number(13,2) I N_ I Value of items transacted in £ (sterling)
txn_count Number(5) N_ I Number of transactions.
update row Varehar2(1) N__I Identifies whether the current row is to be
used to update an existing row or insert a
new row in the target table
-Y
=N
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 94 of 120
TPS HR SAP Summarisation & Transaction Ref EAMLDiocs
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Tablename I TPS_HR_SAP_MONTHLY_ SUMMARIES.
This is an intermediate summary table, which is populated when the monthly HR SAP file
for an HR SAP Group/Period is being created.
‘The source data is table, TPS_HR_SAP_SUMMARIES.
It contains a row for every branch/CTT number for the current HR SAP Group and Period
and it is used to create the HR SAP file for the that group and period,
It is truncated at the beginning of the process which populates it and therefore the data for
the previous run is available until the start of the next run which is going to create an
extract. Since an HR SAP extract is only created every few weeks, this means that the data
for a run remains in this table for a few weeks.
Column Type Null Description
group id Number(6) N__ [A unique identifier that identifies a branch.
‘This attribute corresponds to the FAD
Code.
ett_number Varchar2(10) N_ I The CTT number
quantity Number(i4) I N_ I Either identifies the total number of items
transacted or the total number of,
transactions, depending on reference data
for the current CTT.
‘amount Number(13,2) [I N [Total of value of items transacted in £
(sterling)
Tablename I TPS_HR_SAP_SUMMARIES
This is a summary table which is updated by module TPSC280 from data in the
intermediate summary table TPS_HR_SAP_DAILY_SUMMARIES.
It contains a row for every target period id/branch/CTT number and is used to generate the
feed file to HR SAP twice a month — once for each HR SAP Group.
‘The table normally contains data for 4 periods - the current 2 (allowing for 2 levels of
deferment) and the previous 2 (allowing for receipt of late transactions).
This table is partitioned, as described in 4.2.3.1
Column Type Null Description
target_period id Number(6) N_ I The HR SAP Period into which the data
will be included when the file is generated.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 95 of 120
FUJ00090327
FUJ00090327
FUSITSU
FUIITSUSERVICES.
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
COMMERCIAL IN CONFIDENCE Date: 15/1024/14/04
Corrections HLD Version: 2.02.0
Format is yyyymm.
group id
Number(6)
‘A unique identifier that identifies a branch.
This attribute corresponds to the FAD
Code.
hr sap group
Varehar2(10)
The HR SAP Group, which the branch
identified in the source table, maps to
‘ett_number
Varchar2(10)
Zz
The CTT number which the produetmode
identified in the source table maps to
‘quantity
Number(14)
Number of items transacted
amount
Number(13,2)
‘Value of items transacted in £ (sterling)
txn_count
Number(5)
Number of transactions.
Tast_ updated system
date
Date
zI z\ zI z
Business Day when this row was last
updated or created (if it is a newly inserted
row).
In the case of late transactions, which are
missed out of their intended delivery file,
this allows one to determine exactly how
late the data was,
Tate flag
Varchar2(1)
Identifies whether the data has been
delivered late to POL i.e. after the delivery
date defined in reference data
N
=Y
delivered_period id
Number(6)
Identifies the HR SAP period in which the
data was delivered. For all data delivered
on time, this will be the same as the
target_period_id.
= null Not delivered
=yyyymm — HR SAP Period ID
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE Page: 96 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref EAMLDiocs
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Tablename TPS_PROD_MODE_SUMMARIES
This is an overall view on all the TPS PROD_MODE SUMMARIES xxx tables. The
tables contain the initial summaries and are populated from data in the individual
transaction tables TMS_RX_<nnn> TRANSACTIONS. xxxx.
The base tables contain a row for every CAP/trading date/branch/productmode
combination that has been harvested by the TPS EOD harvester today. The majority of data
will be for a single trading date, but it is liable to include some data from previous days due
to late receipt of data from a branch.
‘The data is used by the processes performing the POL FS summarisation (described in
‘TPS_POLFS_HLD) and the HR SAP summarisation (described in this document)
Note that the complete definition of this table, together with the process to populate it,
is described in TPS_POLFS_HLD
Column Type I Null Description
cash_account_period The cash account period, which the
transactions are included in.
This is only set/used during the migration
to ensure that there is a clean switch over
between CBDB and POL FS/HR SAP,
with no transactions either being missed
out or double counted
After the migration it will be null.
trading date The trading date
group id ‘A unique identifier that identifies a branch.
This attribute corresponds to the FAD
Code.
prod_id ‘The product identifier
transaction mode id ‘The transaction mode identifier
total_transaction_quantity Sum of the quantity field in the source
tables i.e. number of items transacted.
‘otal_transaction_amount Sum of the amount field in the source
tables in £ (sterling)
total_transaction_count Sum of the number of transactions.
account reference id
client reference id
insert date
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 97 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
FUJITSU TPS HR SAP Summarisation is Transaction Verna cas
alien ‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
5-426. Transaction Correction Tables
Tablename I TMS_AWT_TPS TC DETAIL
This table is used by the txn correction Bulk Loader Agent to multistream it’s process, allowing
multiple instances of the Agent to load sets of branch data in parallel,
Note that the full definition of this table is in GEN_AGENT_SPE
Column Type Null Description
chunk seq_no Number(10) N
start_seq_no Varchar2(7) Y
end_seq_no Varchar2(7) Y
status Varchar2(1) N
‘computername Vareharas) I Y
instance id Varchar2(3) Y
progress timestamp I Date Y
processed pono I Varchar2(7) Y
processed aux seq I Varehar2(20) I Y
cod date Date Y
schedule id Number(5) Y
Tablename I TMS_EXCPTNS
This table is used by the txn correction Bulk Loader Agent to log exceptions
Note that the full definition of this table is in GEN_AGENT_SPE
Column Type Null Description
module id Varchar2(12)_ I N
‘exepin_seq Number(16) N
timestamp Date N
exepin_code Number(6) N
source Varchar2(10) I ¥
exeptn_detail Varehar2(800) I Y
file service id Varchar2(5) Y
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 98 of 120
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
file_seq_no Number(4) Y
file_type Number(3) Y
module version Varchar2(12) Y
Tablename I TMS_TX_TPS_TC_DETATL
‘The table is populated by module TPSC284.
This is the interface table for the Txn Correction Bulk Loader Agent.
It contains a row for every transaction correction, which requires a message sending to
Riposte. Where applicable, the mapping from field to message attribute is identified
Column Type Null Description
group id Number(6) N_I A unique identifier that identifies a branch,
sap_reference id I Varchar2(18) I N_ I Identifies the SAP Reference ID
Attribute = Data.Ref
iteration flag Varehar2(1) N_ I Single character id to inform Horizon the
status of the “evidence” collection, When
concatenated with sap reference id it
forms a unique Horizon Reference ID for
the transaction correction.
Attribute = Data.Iter
‘article Number(10) N I [dentifies the Horizon Product
corresponding to the SAP Article Number
in the article field in table
‘TPS_TC_DETAIL
Attribute = Data. Article
instruction Number(10) N I Identifies the Horizon Product
corresponding to the instruction field in
table TPS_TC_DETAIL
Attribute = Data.Instruction
accounting sense I Varchar2(5) N_ I Mapped from label_id field in table
TPS_TC_DETAIL
Attribute = Data.AecountingSense
value Number(11,2) I N_ I Value of Transaction Correction in pounds
and pence
Attribute = Data. Value
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 99 of 120
FUJ00090327
FUJ00090327
FUSITSU
FUIITSUSERVICES.
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
COMMERCIAL IN CONFIDENCE Date: 15/1024/14/04
Corrections HLD Version: 2.02.0
‘quantity
Number(5)
N
‘Quantity relating to Transaction Correction
Attribute = Data.Qty
allowed modes
Number(2)
‘A 2-digit number which identifies the set
of modes, which are available to the
counter staff on processing.
Attribute = Data.AllowedModes
mode_1
Varchar2(2)
The 1* of the modes corresponding to the
specified allowed modes field.
Attribute = Data.Modes.1
mode 2
Varchar2(2)
The 2" of the modes corresponding to the
specified allowed modes field.
= null allowed _modes identified just
maps to 1 mode
Attribute = Data.Modes.2
mode 3
Varchar2(2)
The 3" of the modes corresponding to the:
specified allowed modes field.
= null allowed_modes identified just
maps to 1 or 2 modes
Attribute = Data.Modes.3
message
Varehar2(500)
Contains extra instructions to the Branch to
identify the impact of applying the
correction.
Attribute = Data.Text
client_reference id
Varchar2(16)
Reference number of the client
Attribute = Data.ClientRef
processed _tsmp
Date
Date/Time when the Message was created.
Set by the Loader Agent when it has loaded
the record into Riposte
‘actioned_ind
Varchar2(1)
Status of the row with regards to its
processing by the Loader Agent
=N__ New (Not Processed)
=null Processed Ok by Agent
=F Processing by Agent Failed
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE Page: 100 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Tablename TPS_TC_RECEIVED
This table contains a list of all valid transaction corrections received and is used to check
for duplicate corrections being received by TPS ie. same sap reference id AND
iteration flag.
Old entries are deleted by the standard housekeeping process after 50 days i.e. number of
days which the correction can exist in the message store waiting to be processed at the
counter.
‘The table is populated by TPSC284.
Column Type Null Description
sap reference id I Varchar2(18) I N_ I Identifies the SAP Reference ID
iteration flag Varchar2(1)__ I N_ I Single character id to inform Horizon the
status of the “evidence” collection.
Teceive date Date N_ I Date when the transaction correction was
received.
Tablename I TPS_TC DETAIL.
This table is populated by module TPSX283.sh from a single file created during the txn
correction pre-processing stage. It is truncated at the beginning of this module.
It contains a row for every Transaction Correction detail line (ie. excluding headers and
trailers) in the txn correction files, which were pre-processed in the current run.
‘The table is used by module TPSC284 to populate table TMS_TX_TPS_TC_DETAIL with
the completed Transaction Correction record.
Column Type Null Description
file name Varchar2(30) I N_ I Identifies the terminal name of the file,
which contained the Transaction
Correction.
e.g. if20040608001.ten
record_number Number(5) N_ I The record number in the file. Count starts
at 001 i.e. it includes the Header Record.
Tabel_id Varchar2(5) N_ I The detail record type
=TCINV Agent is debited
=TCCRM Agent is credited
group id Number(6) N_I A unique identifier that identifies a branch,
‘article Varchar2(10) I N_ I Identifies the SAP Article Number — this
Ref: EA/HLD/009
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 101 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EA/HLD/009
FUJITSU Corrections HLD Version: 2.02.0
PUITSUSERMCES COMMERCIAL IN CONFIDENCE Date: 16/1024/14/04
‘generates part of the adjustment posting, It
is mapped to a Horizon Product
value Number(11) N_I Value of Transaction Correction in pence
‘quantity Number(5) N__I Quantity relating to Transaction Correction
instruction Varehar2(10) I N_ I The Article Number of the second product
to be adjusted, It is mapped to a Horizon
Product.
iteration flag Varchar2(1) N_ I Single character id to inform Horizon the
status of the “evidence” collection. When
concatenated with sap reference id it
forms a unique Horizon Reference ID for
the transaction correction.
=N__ New txn correction
=E Evidence provided
allowed modes Number(2) N_ [A 2digit number which identifies the set
of modes, which are available to the
counter staff on processing. It is mapped to
the actual modes using reference data.
sap_reference id I Varchar2(18)_ I N_ I Identifies the SAP Reference ID
client reference id I Varchar2(16) I Y I Reference number of the client
message Varchar2(500) I Y I Contains extra instructions to the Branch to
identify the impact of applying the
correction.
Tablename I TPS_TC_ERRORS
This table contains details of all the errors detected during either the pre-processing stage
(by module TPSC282) or during the loading of the agent interface table
TMS_TX_TPS_TC_DETAIL (by module TPSC284).
It contains a row for every error detected. The number of errors detected may be greater
than the number of rows containing errors i.e. if some rows have >I error.
Itis used by module TPSC285 to create the error file(s) for passing back to POL.
It is truncated at the start of the Transaction Correction schedule (by module TPSC282),
Column Type Null Description
file name Varchar2(30) I N_ I Identifies the terminal name of the file
which contained the ‘Transaction
Correction which is in error
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 102 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES ‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
€.g, 1£20040608001.ten
‘error_code Number(3) N_ I Unique Error code which identifies the
type of error. This is an index into table
TPS_TC_ERROR_CODES
record_number Number(S) Y I The record number in the file that is in
error, Count starts at 001 and includes the
Header Record
=null Error code does not relate to a
specific record,
field_number Number(2) Y IThe field number in the record in error.
Field numbers start at 01
=null Error code does not relate to a
specific field
54.36.13 Other Tables
Tablename I TMS_RX_EPOSS EVENTS
This is the harvester interface table for events
‘The following columns are being added to it.
Column Type Null Description
event id Number(10) Y I Eventid.
NB: Type changed from Number(3)
adjustment amount I Number(9,2) YY I Adjustment amount
txn_correction_count I Number(5) Y I Number of transaction corrections
trading_period Number(3) Y I The trading period. This is only
populated for counter events 55,56 & 57
Tables I ‘Change
TPS RX OTX RESEND I Increase Additional Data field to VARCHAR2(182) to
accommodate additional transaction data
Increase Quantity field to NUMBER(14)
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 103 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Reference Tables
HR SAP Tables
Tablename I TPS_HIR_SAP_CTT NUMBERS
This identifies details of all the CTT Numbers, which are required in the HR SAP feed,
For each CTT Number it identifies what transaction details are required in the final file and
the type of deferment required between the actual transaction and inclusion of the details in
an HR SAP feed.
Column Type Null Description
ett_number Varchar2(10) I N_ I The CTT number
description Varchar2(30) I N_ I Deseription of the CIT
‘t_quantity type I Varchar2(1) N I=Q Total of the QUANTITY fields in
the initial transactions is required in
the file. ie. the number of items
transacted
=C Total count of transactions is
required
i.e, the number of transactions
=N No “quantity detail
is required
deferment_type Number(1) N_ I Identifies the deferment level. Currently
this is the number of months delay between
the receipt of the transaction and passing
the details to HR SAP.
=1 1 month deferment
=2 2 months deferment
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 104 of 120
TPS HR SAP Summarisation & Transaction Ref EAMLDiocs
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Tablename I TPS_HR_SAP_DEF_PERIODS
Defines the transaction period for each Deferment Type that is included in each HR SAP
schedule
Column Type Null Description
hr sap_group Varehar2(10) I N IThe HR SAP Group that the schedule
applies to
hr sap period id I Number(6) N_ [A unique code to identify the year and
month of the HR SAP Group scheduled
delivery
deferment_type Number(1) N_ [Groups CTT Numbers based on the
deferment period between transaction
creation and delivery to HR SAP
period start date I Date N IThe date when an Accounting period
commences ~ this is the start date for the
summarisation.
period end date I Date N IThe date when an Accounting period
finishes — this is the end date for the
summarisation,
Tablename I TPS_HR_SAP_MAPPINGS
those transactions are not required by HR SAP.
Defines the mapping of CTT Numbers to product/iransaction modes.
It contains a row for every produet/transaction which has a corresponding CTT number and
hence is required in the HR SAP extraction. Lack of a mapping for any pair implies that
Column Type Null Description
ett_number Varehar2(10) I N_ I The HR SAP CTT Number
prod id Number(10) N__ I A unique code to identify the product
trans_mode id Number(10) N_ IA unique code to identify the type of
Transaction Mode.
start_date Date N__ I Date when effective.
end_date Date Y__ I Date when ceases to be effective.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 105 of 120
FUJ00090327
FUJ00090327
FITSU SERVICES ‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
Tablename I TPS_HR_SAP_SCHEDULES
Defines the delivery schedule for the feed files to HR SAP.
Column Type Null Description
hr sap_group Varchar2(10) N The HR SAP Group that the schedule
applies to
hr sap period id I Number(6) N ‘A unique code to identify the year and
month of the HR SAP Group scheduled
delivery.
Format is YYYYMM
delivery_date Date N The date on which transactions for the
schedule period are due to be delivered to
HR SAP
5,226. Transaction Correction Tables
Tablename I TPS_POL_FS_ARTICLES
Defines the POL FS articles and their associated horizon products,
Note that the full definition of this table will be in TPS_POLFS_HLD
Column Type Null Description
article id Varchar2-(10) I N
dummy Varchar2(1) N
description Varchar2(30) I N
default_prod pos I Number(10) I Y
default_prod neg I Number(10) I Y
© 2004 Fujitsu Services COMMERCIAL IN CONFIDENCE Page: 106 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAIHLD/009
FUJITSU Corrections HLD Version: 2.02.0
FUTSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Tablename I TPS_TC_ERROR_CODES
Defines the Transaction Correction error codes.
‘These codes identify all the errors, which are explicitly checked for by TPS during the pre-
processing of the file (generally header/trailer errors) and during the later processing of the
data (ie. detail errors)
The codes are used by the modules TPSC282 and TPSC284. Therefore any changes to the
set of codes could impact either of these modules.
Column Type Null Description
error_code Number(3) N_I Identifies the 3 digit error code e.g. 007
description Varchar2(100) I N_ I Description of the error e.g. “Invalid Label
Identifier”. This is included in the error file
created,
record type Varchar2(@3) N_ I Identifies the type of record, which the
error occurred in. This is included in the
error file created.
THZ Header record
=TDZ Detail record
=TIZ Trailer record
Tablename I TPS_TC_MODES MAPPING
Defines the mapping between the Allowed Modes ID (as supplied in a Transaction
Correction file) and the actual mode, which will be selected at the counter. There will be up
to 3 modes associated with each Allowed Modes ID.
Column Type Null Description
allowed modes id I Number(2) N_ I Identifies the 2 digit Allowed Modes ID
eg. 11
te mode Varchar2(2) N__I Identifies the actual mode e.g. “MG”
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 107 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref:
FUJITSU Corrections HLD Version:
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date:
$236. Other Tables
EA/HLD/009
2.02.0
45/1024/11/04
Tablename
TPS_TRANS_MODE_CONVERSIONS
alphabetic Fujitsu transaction modes.
It is a new table in TPS.
This table contains the mapping between the numeric POL transaction modes and the
Column Type Null Description
POCL Trans_ Number(10) I N_ I Identifies the numeric transaction mode.
Mode Type Code
Pathway Trans I Varchar2(10) I Y_ I Identifies the corresponding alphabetic
Mode Type Code transaction mode.
Tablename
TPS_TIP_ EXCLUDED PRODUCTS
It is a new table in TPS.
‘This table is only required during the migration to S80.
This table contains a list of products, which are to be filtered out of the transaction feed
files, which are generated for TIP during the migration. They are the products which
correspond to a new group of EPOSS events being introduced at S80.
I TPS_OUTLETS
Column Type Null Description
prod id Number(10) I N_ I Identifies the product
Tablename
This table contains details of all the branches.
‘The following columns are being added to it,
The full definition of this table as in AIS_ RDDS
Column Type
Null
Description
office status Number(1)
Identifies the status of the branch
=0 Open
=1 Permanently Closed
2 ‘Temporarily Closed
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE
Page: 108 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal ‘COMMERCIAL IN CONFIDENCE Date: 15/4024/11/04
hr sap_group Varehar2(10) I Y ] The HR SAP group that the Post Office branch
isa member of
Branch is a Directly Managed
Branch
ed
site_code Varchar2(4)
Site code for a branch
Tablename I PRODUCT_HISTORIES
This table contains details of all the products
The following columns are being added to it.
The full definition of this table as in AIS_ RDDS
Column Type Null
Description
adjusiment_price I Number(11,3) I Y I Adjustment price in pounds and pence
transfer_txn_to. Varchar2(1) Identifies whether the transaction should be
mis transferred to MIS
=Y
=N
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 109 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
$36.3 Control Tables
Transa
5.346.
‘tion Correction Tables
Tablename [TPS TC FILE DETAIL
This table contains details of all Transaction Correction files received by TPS, which have
valid filenames (as determined by module TPSC282)
Associated with each file are various metrics and flags identifying the results of the file
validation. Completion of the processing of the file is also recorded.
Old entries are deleted by the standard housekeeping process after 365 days.
Note that the detail lines of the file are only checked for errors if the header and trailer are
valid ice. the file is suecessfully pre-processed
Column Type Null Description
file_name Varchar2(30) N__I Identifies the terminal name of the file
e.g. if20040608001 ten
receive date Date N_ I Date when the transaction correction file was
received.
‘ereate_date Varchar2(8) I N IThe date component of the file name eg.
20040608.
sequence no Varchar2(3)_I N IThe sequence component of the file name
eg. 001
detail line count ‘Number(6) Y I Count of number of detail lines in the file ice.
excluding the header and trailer
error_inhdr flag I Varehar2(1) I Y
Identifies whether there was an error in the
file header
=YN
error in tl flag I Varcharay) I Y
Identifies whether there was an error in the
file trailer
=Y/N
earor_in dil flag I VarcharX) I Y
Identifies whether there was an error in at
least one of the detail lines.
=Y/N
error line count — I Number(6) Y
Count of number of detail lines in the file,
which were rejected because of an error.
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE Page: 110 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
Processed flag Varchar2(1) I N_ I Kentifies whether the file has been processed
=YIN
53:26. General Tables
Tablename I TPS FILE REGISTER
This table contains details of all files successfully created by TPS.
The row is created initially when the file is opened by StartExportFile or
StartExportHRSAPFile.
‘The row for a file is updated at various stages in its processing ~ notably when it has been
successfully delivered to POL (via FTMS) and when delivery of the file has been reported
to the DWH.
‘The following columns are being added to it
Column Type Null Description
destination Varchar2(1) Y I Identifies the destination of the file
=T POLMIS
S HRSAP
=C POLCTS
=F ERTS
null TIP (i.e. pre-S80 data)
hr sap group Varchar2(10) I Y IThe HR SAP group for which the file has
been created.
= null File is not destined for HR SAP
hr sap period id I Number(6) Y_ I A unique code to identify the year and month
ofthe HR SAP Group scheduled delivery.
The value is set when the HR SAP file has
been successfully created.
Format is yyyymm
=null File has not been successfully created
yet
Ref: EA/HLD/009
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 111 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal ‘COMMERCIAL IN CONFIDENCE Date: 15/4024/11/04
receipt _date Date Y IFor all destinations except “I”, this field
denotes the successful receipt of files
transferred by FTMS.
Receipt of files with a destination of “T” are
recorded at the sub file level in
TPS_TIP_SUB FILE REGISTER
=null File has not been successfully
transferred yet.
5-46,
Control Data
Tablename I TPS SYSTEM PARAMETERS
This table contains various Static/Dynamic parameters used in TPS processing
‘The following parameters are being added to it for use during the HR SAP and Transaction
Corrections processing.
Name
Value I Type
Description
FINAL CBDB CAP
99 Num
Final Cash Account Period, which is to be
sent to CBDB.
Itis used during the migration to $80, to
ensure there is a clean switch over between
transactions being sent to CBDB and being
summarised for sending to POL FS / HR
SAP.
Note that this value is only the initial value
set at Point 10. The actual value is assigned
by a separate script just before Point 30.
OF
HR SAP CREATION 3 Num
Number of days before the due delivery
date that creation of the HR SAP file will
be first attempted for the current period.
This value is used by the DWH metadata,
which drives the performance measure.
Therefore if its value is changed, a
corresponding change to the metadata must
be made.
PIVOT DEST
HRS2 Text
Destination of file — this value is included
in the Outward Delivery File for the DWH.
Max allowed length = 4 chars
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE Page: 112 of 120
FUJ00090327
FUJ00090327
FUJITSU TPS HR SAP Summarisation & Transaction Ref: EAMHLD/000
FUIITSUSERVICES.
Corrections HLD Version: 2.02.0
COMMERCIAL IN CONFIDENCE Date: 15/1024/14/04
PIVOT2 DEST
HRS! I Text
Destination of file — this value is included
in the Outward Delivery File for the DWH.
Max allowed length = 4 chars
PIVOT FILE PREFIX
pit I Text
File prefix of the file delivered to HR SAP
for the CEPOSPSO. Used in conjunction
with PIVOT FILE SEQ to form the
filename.
PIVOT? FILE PREFIX
pv2 I Text
File prefix of the file delivered to HR SAP
for SPSOCEPO. Used in conjunction with
PIVOT2 FILE SEQ to form the filename,
PIVOT FILE SEQ
Sequence of file delivered to HR SAP for
CEPOSPSO. This is incremented after
each delivery.
Note that this value is only the initial value
set at Point 10. The actual value is assigned
bya separate script just before Point 30
PIVOT2 FILE SEQ
Sequence of file delivered to HR SAP for
SPSOCEPO. This is incremented after
each delivery.
Note that this value is only the initial value
set at Point 10. The actual value is assigned
bya separate script just before Point 30.
TC SOURCE POLES I Text I Source of Transaction Correction file. This
value must be contained in the 2"' field of
the file header.
TC INTERFACE I 01 Text I Version of interface between POLFS and
VERSION ‘TPS. This value must be contained in the
3" field of the file header.
S80 MIGRATION 0 Num _I Identifies the current migration point.
POINT
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE Page: 113 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref EAMLDiocs
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
6-7. APPENDIX 2: Maestro Scheduler
The following new schedules are required to execute the HR SAP, Transaction Correction and POL
MIS extract processes. The full schedule will-beis described in TPS_OPS:
Schedule Schedule Job Name Notes
Name Dependency
TPS_POLMIS I TPS_POLFS_FIL POL MIS feed
TPSC287 Generate POL MIS files
Generate Bureau Txn Feed for FRTS.
‘TPS_HRSAP_ I TPS_POLMIS HR SAP extract
TPSC280 Summarise HR SAP data
TPSC281 Create HR SAP file
TPS_TC TPSPOLMIS, Transaction Corrections
FPBULKLDRIPS
EOD
TPSC282 Pre-Process
TPSX283.SH I Initial Load (shell script)
TPSC284 Load TMS Table
TPSC285 Create Error File
TPSC286 End Job.
The detailed scheduler changes for POL FS, which affects T2SSUMM—TPS INIT SUMM, aad
TPS_POLFS_SUMM and TPS_POLFS MIG, are described in TPS_POLFS_HLD
On the following 2 pages are diagrams summarising the overall TPS schedule for
a) prior to S80
b) S80
The following key has been adopted:
Process or Schedule which will be become INACTIVE at S80 Migration Point 40- applies
to "Prior to S80" diagram:
Process or Schedule which will be DELETED at $80 Migration Point 10 - applies to
"Prior to $80" diagram
NEW process or schedule - applies to "S80" diagram
ey I
EXTERNAL schedule - applies to both diagrams
Note that the changing of existing processes is not recorded in the diagrams ~ they just denote changes
at the schedule level.
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 114 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction
I FUJITSU Corrections HLD
FUNTSUSERVICES
Prior to S80
COMMERCIAL IN CONFIDENCE
Ref: EA/HLD/009
Version: 2.02.0
Date: 1/4024/11/04
TR FLEW my ome Font TT
8)
rx)
anny S80 cg be ay eae OTE ha)
Tes. LD AAR) Tes FL-0-TeR a TO
rs pus te 13 Pus LonB Tel PLE_ CHR TEh
ite ot GRO ang ‘Sea Tema ora o pero Be
ee
‘ete 105-17 REVERSALS
Sian
Ieohers fom prow a Oss ate ie
coto.exuer
[Peau
(Co cette Pap
‘ae ty oT eae ae ny
‘autre etare Test und spt FRE REGISTER ane
ER UR FRESE oo toa POC
Sen itn unary gt tye nd
‘tise crt ay sn TP
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE
Page: 115 of 120
FUJ00090327
FUJ00090327
FUSITSU
FUIITSUSERVICES.
S80
TPS HR SAP Summarisation & Transaction
Corrections HLD
COMMERCIAL IN CONFIDENCE
Version: 2.02.0
Date: 15/1024/14/04
Ref: EA/HLD/009
FPS FLEL GWT omy Soran tor OT
1) contnng stein og “OFX fr mania
773 PUPS FON rE)
TPs FueLOAD Ta)
rscaa acy edo TSI. rscae rscawo of
‘oct eed ered on ona ‘are POLFS atm spans ERR Taneaecd 0 ca “OTE
11 en ena tenon (Pp [I totems secoan tre Svc Sermo Whe POF REREMD
3 Fe L080.
ave dot CORO 8 om
TPF A9-RAro 9)
"nar
Sermo 1. RST RESO
(Gate 1 Rs fom OFX a
sciie Pon
<a (509
Th esc) sp arate
rece Pon
escar at Pont so)
Gente an cpr ety ee
esc Stop a Pi
"Grae EOT te eag atove
pe
Mt spans misactnon Toras
for lpuitne toot ajpateon 6s I I pgoart
5.178 OATRCT_
Ro exeacy Ro Rost xrRcT
FoexTRAcT ROLE Teer
meg SREF atom
Seo ty patton is
Trscz6t Cnccomaéron Sap at Pot a)
Treciss Gacrcunasor Eves [Stopat Pom aa}
936262 ens ans ovort St fee Soney
Testa Cardo Cas ecune (Sop Pot 42)
(Goma 1 Tabepace sg pat
‘sbi brat tne, ssn Sm
——\_
revo oy 0 Guo eto He
Lo Ten opt Pit
ez iy can 0 a om rad wed iy
ioe BaF EORTC vy SE
coo RT <a 0.4 Sua pt cy ta a ec
Se ot Sasa corel yt
© 2004 Fujitsu Services
‘COMMERCIAL IN CONFIDENCE
Page: 116 of 120
FUJ00090327
FUJ00090327
FUSITSU
Corrections HLD
ealiaaniaaeal COMMERCIAL IN CONFIDENCE
POPU SW Ee Seto
TPS HR SAP Summarisation & Transaction
Ref: EA/HLD/009
Version: 2.02.0
Date: 15/1024/14/04
Sh cvnig eter tof "07K ra?
orn by SC ge ay tg Hora“ OTE he)
TF a aaa 3
173 FLE.LO8D ALAM
ws CORD ing
aera i ta “OTE Meo TES RX OH RESEND
TUE LOAD AS
TPs FLL0 TER TO
195 PLE LOAD. TERG, TPS PRE CHURT TERM 5 _ow_ Cu
‘Se eat ral prereae
Te mn tom ot I I Ta
"dk ns fom 20a TSF a88 TRANSACTIONS»
‘super TPS TOVSACTON TOTALS
[ a err
gC ra Soy (7 Fn
Ieeiveres om previous ao Ours ete ie
PSTN oa
Torerate wet pon waster EF "ropa
teen me
(enranT Tbenec ne Feat
rsca Tavoutaean reoto.euer
ee ae om ts ey Te Teowo-onure
TTR Etc kp na ToT
Perr PS te 6h 0s "fae coy ep es .F. h tsweeed md try
hte sto ute TPS FLE REGISTER rd
nei SUF ReTEN d yTSE
=r Soretereeenee
‘PLESYS_BRUPT rs. SoneDuLe_ COMPLETE
mens Sooaione
© 2004 Fujitsu Services COMMERCIAL IN CONFIDENCE
Page: 117 of 120
FUJ00090327
FUJ00090327
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction Ref: EAHLDI009
FUJITSU Corrections HLD Version: 2.02.0
FITSU SERVICES ‘COMMERCIAL IN CONFIDENCE Date: 15/1024/11/04
7.8. APPENDIX 3: Documents Affected
TA8.1 Changed documents
The following documents should be changed as part of this development.
Ref Description ‘Comment ‘Owner
DWAFS/021 I TPSto DWH AIS 7a) OFD file contains SAP, CTS and I Fujitsu
ERTS details as well.
b) FAD code is now nullable (see
53.7.2)
EA/ES/002’ POL FS to TMS/Horizon ‘Bring set of Error Codes in-line with I POL
‘Transaction Corrections Interface this document.
Spee
NB/FS/012 I Bureau Feed for FRTS a) CAP and BP fields in BTD file I Fujitsu
will be null
_b) BCT aggregation changed
c) Interface directory to EDG
"changed
PHLLD/005 Process TIP Reject I Cater for increase in length of Fujitsu
POLMIS lines e.g. due to inclusion
I of more additional data '
PULLD/007 Create TIP OTX If S80 MIGRATION POINT” >= 40 I Fujitsu
es then _ cater for changing format of
POLMIS
'PULLD/027 TPSC206 — Create Delivery File Now include SAP info, derived from Fujitsu
‘TPS_FILE_REGISTER
PVLLD/028° I TPSC207 - Harvest Branch InfoI New tables being populated. Fujitsu
from RDDS- I
PILLD/030 - TPSC210 - Harvest Receipt Handle files other than *TPR Fujitsu
Information
PILLD/031 I TPSC211 — Start of Day TAdd function DropCreatePartition I Fujitsu
PILLD/036 I TPSC223 — Create CTS Flie a) File now output to $CTS_OUPUT I Fujitsu
b) Reference new AIS (EA/IFS/005)
PI/LLD/037 I TPSC225 ~ Create TIP STX Files I a) Suppress data if CAP > Final CAP I Fujitsu
b) Module is only active if “S80
MIGRATION POINT” < 40
© 2004 Fujitsu Services COMMERCIAL IN CONFIDENCE Page: 118 of 120
FUJ00090327
FUJ00090327
TPS HR SAP Summarisation & Transaction EATHLDI008
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE 45/1024/11/04
PULLD/038 I TPS Common StartExportFile changed and Fujitsu
StartExportHRSAPFile created
PI/LLD/046 I TPSC240 — Load Resend Tables If “S80 MIGRATION POINT” >= 40 I Fujitsu
then
a) cater for changing format of
POLMIS
b) cater for extending set of record
types.
©) when called for parameters CAE
and STE, then exit doing nothing
PULLD/049 I TPSC271 — Generate Bureau Feed I a) BCT aggregation changed Fujitsu
b) STIP_OUTPUT replaced by
SFRTS_OUTPUT
TD/ION/005 I FTMS Configuration for Pathway I New FTMS services required Fujitsu
TPS to POCL TIP Links
TVDES/002 TPS High Level Design Carry all changes into main TPS doc Fujitsu
TVIFS/008 Horizon to Post Office TIS “Interface to TIP replaced by several Fujitsu
‘new interfaces
TVMAN/002 I TPS Operations Manual a) Changes to schedule Fujitsu
b) New processes introduced
©) New parameters in
TPS_SYSTEM_PARAMETERS
d) Identify how HR SAP file, which
is delivered late, can be identified.
728.2 Withdrawn documents
The following documents should be withdrawn as part of this development — but not until after Point
40 in the Migration.
Ref Description ‘Comment
I PI/LLD/012 I TPSC238 - Resend corrected CAC & STK files I Cash Accounts no longer required
PPULLD/019 I TPSC255 - Create ‘Cash Accounts no longer required
TPS_CA_LINE_COMAPARISONS table
PI/LLD/020 I TPSC256 CA Line Comparison Report ‘Cash Accounts no longer required
PULLD/023 I TPSC201 - Produce TIP Files Replaced by TPSC287
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 119 of 120
FUJ00090327
FUJ00090327
Ref: EA/HLD/009
TPS HR SAP Summarisation & Transaction
FUJITSU Corrections HLD Version: 2.02.0
ealiaaniaaeal COMMERCIAL IN CONFIDENCE Date: 45/1024/11/04
FPVLLD/040 I TPSC265 - TPS CAC Transaction Processing I Cash Accounts no longer required
I PILLD/041 I TPSC266 - TPS CAC Stock Process
FPI/LLD/042 I TPSC267 - Generate Non Leaf CAC Lines I Cash Accounts no longer required
" “Cash Accounts no longer required
I PULLD/043 I TPSC268 - Compare counter generated CA I Cash Accounts no longer required I
I with HOST
© 2004 Fujitsu Services ‘COMMERCIAL IN CONFIDENCE Page: 120 of 120