A Database System High Level Design Template | Oracle Database ...
Short Description
Achieving the equivalent in IT systems design requires the taking of a similarly abstract view of the system design at t...
Description
Oracle
DATABASE SYSTEM HIGH LEVEL DESIGN
TEMPLATE Author:
Brendan Furey
Creation Date:
28 November 2010
Version:
1.0
Last Updated:
46877296.doc
.
Page 1 of 24
Table of Contents
Document Control...............................................................................................................4 Change Record............................................................................................................4 Introduction.........................................................................................................................5 Definitions....................................................................................................................5 System Overview................................................................................................................6 Overview......................................................................................................................6 Database Objects Summary........................................................................................6 Components.................................................................................................................7 Data Model.........................................................................................................................8 XX Custom and Several Standard Schemas................................................................8 ERD......................................................................................................................................8 Entities and Tables..............................................................................................................9
Other XX Tables.........................................................................................................10 Database Packages and Functions..................................................................................11 Backend.....................................................................................................................11 Web Service Packages..............................................................................................12 Functions....................................................................................................................12 Database Triggers............................................................................................................13 Trigger List.................................................................................................................13 Database Links.................................................................................................................14 Link List......................................................................................................................14 Views/Synonyms..............................................................................................................15 Views.........................................................................................................................15 Database Link Views/Synonyms................................................................................15 Interfaces..........................................................................................................................16 Interfaces List.............................................................................................................16 Interfaces Diagrams...................................................................................................16 Link Entities and Key..........................................................................................................16 XYZ Interfaces Diagram......................................................................................................18
Batch Programs................................................................................................................19 Interface Jobs.............................................................................................................19 Non-Interface Jobs.....................................................................................................19 Scripts...............................................................................................................................20 SQL Scripts................................................................................................................20 Unix Scripts................................................................................................................20 User Interactions..............................................................................................................21 Company.com............................................................................................................21 Call Centre.................................................................................................................21 Business Objects.......................................................................................................21 Database Jobs..................................................................................................................22 Job List.......................................................................................................................22 Batch Jobs Schedule........................................................................................................23 46877296.doc
Page 2 of 24
Interface Jobs Schedule.............................................................................................23 Other Jobs Schedule..................................................................................................23 Issues and References.....................................................................................................24 Issues.........................................................................................................................24 References.................................................................................................................24
46877296.doc
Page 3 of 24
Document Control Change Record Date
Author
Version
Change Reference
28-Nov-2010
BPF
1.0
Initial
46877296.doc
Page 4 of 24
Introduction [The concept of top-down design is very important in IT systems design. Every IT system is unique, but also shares many common features with other systems, and one of the keys to good design is to identify the common features. In this way established methods, and perhaps applications and code, can be applied to the majority of the system, leaving just a small part that is genuinely unique. It is similar to the concept in mathematics of solving a complex problem by first reducing it to a problem with a known solution plus an unsolved but smaller additional problem. Achieving the equivalent in IT systems design requires the taking of a similarly abstract view of the system design at the higher levels, and high level design documents can play a key role here. This document provides a template for the high level design of a database system. Incidentally, it seems likely that lack of a sufficiently rigorous top-down approach may be a major cause of the failure of some big IT projects, where it is especially necessary. I have noticed while working on large pan-European projects a lack of awareness of its importance even within the very large software services providers. There tends to be requirements specifications and very detailed design documents but a gap in the middle (non-technical people, such as managers and business analysts, usually think the more detailed the design the better, but that’s not the case). Unlike other templates I’ve published (for example, REF-4), I have decided to include examples to give a better idea of what a completed design might look like. The examples are based on a number of systems I’ve worked on that use Oracle CRM as a component in a larger database system, and have been modified to remove any details of the companies concerned. This template has been largely extracted from retrospective design documents, where one is documenting the design of existing systems, and thus would have some features that would differ from a design that is for a new system. It should be straightforward to adapt the template to requirements. For example, the design for a new system clearly would not have listings of the numbers of lines of code in the PL/SQL packages, but one might want to replace them with other estimated sizing metrics. The example design material should not be considered as recommendations for good design, merely how one might document a design.] This document provides a high level technical system design for the the XYZ (Xxx Yyy Zzz Application). It should be read in conjunction with Oracle’s documentation for the standard Oracle components of the system, as well as with the functional design documents. The structure of the document is that there is an overview section including a list of components, followed by subsequent sections describing these components.
Definitions
46877296.doc
•
ABC
– Aaa Bbb Ccc Application (the subject internal application of the design)
•
XYZ
– Xxx Yyy Zzz Application (another internal application)
•
CRM
– Customer Relationship Management
•
ETL
– Extract, Transform, Load
•
ETLT
– Staging database for ETL; used for interfacing
•
CIN
– Customer Identification Number
•
CP
– Concurrent Program (or batch program)
•
RS
– Request Set (a group of concurrent programs)
•
DFF – Oracle Descriptive Flexfield consisting of generic character fields on standard tables that can be configured to hold custom data
•
cron
– a Unix scheduling utility
.
Page 5 of 24
System Overview Overview XYZ is a loyalty system targeted at business customers (whereas ABC is the corresponding system targeted at retail customers). The database system is based on Oracle’s Telesales CRM system, with extensive customisations. It interacts with the ABC system and with two other internal database systems, ETL, the transactions data warehouse, and ETLT the staging database. The front end applications are: •
Oracle Applications screens – available to Call Centre staff
•
Company.com web site – available to customers through web browsers
•
Business Objects – available to IT staff for reporting
In addition to internal interactions, there are a number of external interfaces, both inbound and outbound. The system is centred around the member, who earns points for each transaction that he makes, and who may then redeem points to purchase goods and services. The member is assigned a tier on entry, that defines privileges such as …, and rules are defined in the database for tier qualification. The member is given a contract for his tier lasting up to a year, and rules are defined to determine transitions between the tiers at contract end, or earlier. There are further rules defining the mapping between prices paid and points awarded. The internal database architecture is depicted in the data model section below. The customer personal details are stored in Oracle’s Customer and CRM modules, along with his interactions with the loyalty scheme, while the loyalty scheme-specific data such as points and the business rules metadata are stored in a custom schema. The outbound interfaces include details being sent to a fulfilment house to supply membership cards by mail, and for SMS or emails to members in various circumstances. Inbound interfaces allow points assignments to be made from multiple sources, including partner companies, and also allow for confirmation of provisional points claims when transactions are confirmed in the ETL database. The interface relationships are depicted in the interfaces section below. This document is aimed at providing a high-level design view and is largely technical in nature. The tables below summarise the database objects, and list the main components in the XYZ system, and the remaining sections provide design specifications. Lower level documents (see references) describe the components in more detail.
Database Objects Summary Object Type Valid DATABASE LINK INDEX PACKAGE PACKAGE BODY PROCEDURE FUNCTION SEQUENCE SYNONYM TABLE
Count
Lines of code
2 48 68 47 2 8 30 239 170
NA NA 1,963 27,932 259 365 NA NA NA
TRIGGER
21
292
VIEW
82
3,491
Invalid PACKAGE PACKAGE BODY PROCEDURE
2 23 1
112 18,402 24
46877296.doc
Notes Public Owner or table owner = XX Objects like COMP% Owner = XX Owner or table owner = XX Owner = XX Table or trigger owner = XX or trigger name like COMP% Table owner = XX or view name like COMP % Objects like COMP%
Page 6 of 24
FUNCTION
1
25
Components Name APPLSYS Schema APPS Schema AR Schema HR Schema JTF Schema XX Schema Backend Web Service Web Service Wrapper Functions Obsolete
Custom Tables Standard Tables ETLT_LINK.US.ORACLE.COM ETL_LINK.US.ORACLE.COM Business Objects Specific Purpose Miscellaneous Database Link Views/Synonyms Company.com ETL Links ABC Links XYZ Transaction External Systems
Interface Jobs Non-Interface Jobs SQL Scripts Unix Scripts Gather Statistics Interface Jobs Non-Interface Jobs 46877296.doc
Description Schemas Oracle Applications foundation schema, owning tables such as fnd_user Oracle Applications schema that owns packages, views etc. Oracle Applications schema that owns Accounts Receivable, and customer, tables Oracle Applications schema that owns Human Resources tables Oracle Applications schema that owns Customer Relationship Management tables Custom schema owning all custom tables Database Packages and Functions Code for backend logic in concurrent programs and elsewhere Called by web services for Company.com Wrappers for use by Java interface code, for old style web services Small number of functions for various logical requirements returning a value Many packages are no longer used, or even invalid – we exclude these from this document as far as is known Database Triggers Some of the custom tables have triggers that perform some logic on insert or update; typically, this is to set the unique identifier from a sequence on insertion, or to set the ‘Who’ columns There is a custom trigger on the standard tasks table to set DFF attributes Database Links ETL Company Staging database ETL Company Transactions database Views/Synonyms Views created for Business Objects reporting Views created for purposes such as pre-fulfilment reporting Views intended for general purposes It’s considered good practice to create views or synonyms where access to remote databases is required, with the link included Interfaces Web site makes calls to XYZ web services Database links for Business Objects reports Database links for checking existence of claims and accounts across the two Loyalty systems Interface triggered from ETLT to transfer points-related data from ETL into XYZ 8 external interfaces have been identified, all using flat file transfers Batch Jobs Many of the interfaces are scheduled batch jobs, configured as concurrent programs or request sets (i.e. groups of concurrent programs) Other batch jobs perform such processing as contract renewal, archiving etc. Scripts Batch jobs normally call either a database package, or an SQL or Unix script to execute their logic, and these may in turn call packages, while Unix scripts may call SQL scripts or packages within embedded SQL Database Jobs The standard database administration job Batch Jobs Schedule Both interface and other jobs may be scheduled, as opposed to being triggered manually Page 7 of 24
Data Model This section covers the data model as far as current functionality is concerned. Many tables exist in the custom XX schema that are not part of this functionality – they may have been used for one-off data migrations, or have become obsolete for other reasons. The diagram covers entities from both standard Oracle Applications schemas and the custom schema that form the core data model, but excludes staging and other system entities, as well as standard entities that are not used directly.
XX Custom and Several Standard Schemas ERD
46877296.doc
Page 8 of 24
Entities and Tables Core entities and tables are listed here, and are shown on the ERD, while system and other tables are listed separately below, without appearing on the ERD. Standard entities are further described in Oracle’s documentation set (REF-1). Entity
Table
User
fnd_user
Notes APPLSYS Schema
AR Schema Basic account information including account number Customer Account hz_cust_accounts (our CIN) Customer Profile hz_customer_profiles Additional account information, with history Contact Point hz_contact_points Telephone numbers, email addresses etc. Basic information about a party (in general company or Party hz_parties person, and technical entities, but here always person) Party Site hz_party_sites Link from party to a site Party Site Use hz_party_site_uses Uses a party can make of a site (not used) Person Profile hz_person_profiles Additional account information, with history Location hz_locations Address data for a site HR Schema People per_people_f Data on person employed by Company JTF Schema Tables _b, _tl indicate base table, plus table of Action jtf_ih_actions_b, _tl translated description fields; view ending _vl joins the two Activity jtf_ih_activities CRM activity Group jtf_rs_groups_b, _tl Grouping of resources Group Member jtf_rs_group_members Member of a group Interaction jtf_ih_interactions Interaction with customer Note jtf_notes_b, _tl Note about customer Outcome jtf_ih_outcomes_b, _tl Outcome of an interaction Resource jtf_rs_resource_extns Resource (human) Result jtf_ih_results_b, _tl Result of interaction Role jtf_rs_roles_b, _tl Role that may be played by a resource Role Relation jtf_rs_role_relations Link between resource and role Task jtf_tasks_b, _tl CRM task Task Assignment jtf_task_assignments Assignment to resource of task XX Schema (except some APPLSYS tables, but considered custom entities) Additional Comm xx_additional_comms Actually interface table used for targetting mailshots Member details as entered via call centre in Oracle Application Form Company_appl_form application front end Company (none) Company selling transaction Currency (none) Currency ECOM Preference (none) Preference types as named on Company.com Type Market Mapping xx_market_mappings Maps company code to market Fulfilment Code xx_fulfillment_codes XYZ-ECOM Pref Maps preference type names between XYZ and xx_ws_XYZ_pref_mappings Type Mapping Company.com XYZ Points Company_points Member points earnings and redemptions Market (none) UK or FR or BE or other Party Preference xx_party_references Holds preference information Points Mapping xx_point_mappings Maps prices to points descriptive_flexfield_name Preference Type fnd_descr_flex_contexts ='XX_PARTY_REFERENCES' Member Qualification xx_membership_qualifications Rules defining qualification criteria for each tier Tier fnd_lookup_values lookup_type=’XYZ_MEMBERSHIP_STATUS_TYPES’ Transition Rule xx_membership_rules Points required for transitions between different tiers Travel Segment (none) Transactions table - exists on ETL system 46877296.doc
Page 9 of 24
Other XX Tables Some tables are used for interfaces or as staging tables for reporting purposes, or are system tables used for storing error, configuration parameters or other system data. These are listed here but not included in the ERD. Table xx_pqr_cinfiles xx_pqr_rejfiles xx_interface_batches Company_cc_enrol_verify Company_carhire_partner_t xn Company_etl_txn_error Company_etl_txn_interface Company_partner_load Company_partner_txn Company_output_file_audit
Purpose
Notes Interfaces Staging tables for PQR Company PQR Interface Request Set interface Audit table for EML XX Weekly QES to EML Request Set interface Further staging tables for partner interfaces Error and staging table for ETL Transaction interface SQL*Loader staging tables for partner interfaces Audit table for weekly fulfilment process
xx_mis Company_report_%
Report staging Report staging
xx_cin_ranges Company_conf_parameters
CIN creation Configuration parameters Web services error log
xx_ws_errors Company_errlog Company_gen_error
46877296.doc
XX Weekly Fulfilment Request Set Reports Used in monthly financial reports from Business Objects Various tables beginning ‘Company_report_’ System
General error logs
Page 10 of 24
Database Packages and Functions As with the tables, many obsolete packages and functions exist, but are excluded from this section (where known to be obsolete), as are all standard packages, for which Oracle’s own documentation should be consulted (REF-1).
Backend Package Name
XX_Generic_Redemption
Purpose Creates interactions for quarterly email process Creates preferences Archives customers due to inactivity or other reasons Processing of rec_eml_recon_stg table Creates output file for quarterly email process Generic processing such as contact retrieval Redemption processing
XX_Interface_Batches_PKG
Creates interface batches record
XX_XYZ_CIN_Create
CIN generation Create Magnetic Card Replacement task
XX_Add_Beh_Process_PKG XX_Create_Partner_Pref XX_Customer_Archiving XX_EML_Recon_Pro_PKG XX_Fulfillment_Process_PKG XX_Generic_PKG
XX_Magnetic_Card_Replace_PKG XX_Member_Status_Mgmt_PK
Member status management
XX_Pending_TXN
XX_Reference_Model XX_VFC_Validation Company_CC_File_Processing
Process points pending validation Standardises name for PQR customers Sends changed customers to PQR by flat file Get points for a transaction based on mtl_system_items Insert or update preferences Validate VFC membership code Processing for CC interface
Company_Convert_Txn_Mode_PK
Returns 1 for ACR, -1 for RED
Company_Email_Upload_PKG
Processes COMPANY_EMAIL_UPLOAD_TM P
Company_Error_PK
Error logging
XX_PK_Alpha_Test XX_PK_PQR XX_Points_Required
Company_ETL_Processing Company_ETL_Processing_SPC Company_Fulfillment_Mgmt_PK Company_Ful_Mgmt_Data_Fix_PK
Insert/update points for ETL transactions ETL processing, looks like migration code Creates the fulfilment file Data fix
Company_Incentive_Mgmt_PK
Old version of Company_Fulfillment_Mgmt_PK?
Company_Inter_Hist_PK
Wrapper for interactions
Company_Partner_Processing
Processing for Carhire interface
Company_Task_PK
Wrapper for tasks
46877296.doc
Top Level Calling Process XX Weekly QES to EML (Report Set) Web service (XX_Change_Details)
Obsolete? XX Weekly QES to EML (Report Set) XX Weekly QES to EML (Report Set) and others Web service (XX_Redemption) XX Weekly QES to EML (Report Set) Web service (XX_WS_Validation) Obsolete? Refers to customer in 2007 XX Weekly Fulfilment (Report Set) Also Web services, such as Create Account Company Pending TXN Processing Company PQR Interface (Report Set) Obsolete? Web service (XX_Change_Details) Web service (XX_WS_Validation) Company CC Interface XX Weekly QES to EML (Report Set) (from XX_Generic_PKG so could be various) Obsolete? XX Weekly Fulfilment (Report Set) and various XYZ Transaction Interface (ETL job) Obsolete? XX Weekly Fulfilment (Report Set) Obsolete? Obsolete? Mainly, but table API is still used by original version to insert into XX Weekly Fulfilment (Report Set) and various Carhire interface (manually triggered) Various including Web service such as Change Details Page 11 of 24
Company_Translation_Utility Company_Util_Pck
Format validation/conversion Utilities
Obsolete? XX Weekly Fulfilment (Report Set)
Web Service Packages Package Name XX_Change_Details XX_Create_Account
Purpose Changes member details Creates member account Checks tier status for a potential member. This is called via the ABC XX_XYZ_WS package XX_XYZ_WS owing to limitations of v8 of XYZ database XX_Redemption Redeems points XX_Retro_Claim Processes retro claims XX_View_Account Returns account details XX_View_Statement Returns statement details XX_WS_Generic_PKG Error handling and generic processing for web services XX_WS_Validation Web service validation Wrappers for use by Java interface code, for old style web services XX_Change_Details_PLSQL_Wrap Wrapper for XX_Change_Details XX_Create_Account_PLSQL_Wrap Wrapper for XX_Create_Account XX_Redemption_PLSQL_Wrapper Wrapper for XX_Redemption XX_Retro_Claim_PLSQL_Wrapper Wrapper for XX_Retro_Claim XX_View_Account_PLSQL_Wrappe Wrapper for XX_View_Account XX_View_Statement_PLSQL_Wrap Wrapper for XX_View_Statement
Functions Package Name XX_De_Duplication_Check XX_Get_Lookup_Value XX_Get_Point_Avl_Func XX_Price_Based_Pnt_Calc_Func Company_Convert_Txn_Mode Company_Get_Trans_String
46877296.doc
Purpose Checks whether prospect is an existing member Gets lookup value Gets points available Gets points for a transaction Decodes ACR/RED into 1/-1 Translates strings
Page 12 of 24
Database Triggers Trigger List Table Name xx_pqr_cinfiles xx_pqr_rejfiles xx_internet_files Company_cc_enrol_verify
Trigger Name DBT_PQR_CINFiles DBT_PQR_REJFiles DBT_INTRFile Company_TCC_Enrol_Verify
Company_cc_partner_txn
Company_TCC_Parnter_Txn
Company_campaign_hist Company_campaign_hist Company_etl_cust_interfac e Company_etl_cust_interfac e Company_etl_txn_interface Company_points_reload Company_points Company_points Company_gen_error Company_output_file_audit
Company_TCPN_Hist Company_TCPN_Hist_U
Company_partner_txn
Company_TPartner_Txn
Company_partner_load
Company_TPartnerLoad
jtf_tasks_b
Company_TTask_Creditcard
Company_trans
Company_TTrans Company_TXReference_FTP_Dat a
xreference_ftp_data_bck Company_ref_data
46877296.doc
Company_TETL_Cust_Interface
Purpose Sequence and Who columns Sequence and call Company_Util_PCK.Convert_CIN to prepend Company id Sequence and Who columns Sequence and Who columns Sequence and Who columns; decode nationality
Company_TETL_Cust_Interface
Sequence and Who columns
Company_TETL_Txn_Interface Company_TEURR_Points_INS Company_TEUR_Points_Date Company_TEUR_Pre_Change Company_TGeneral_Error Company_TOutput_File_Audit
Sequence and Who columns Add points to attribute13 of hz_parties Who columns (last update date only Add points to attribute13 of hz_parties Sequence Sequence Sequence and call Company_Util_PCK.Convert_CIN to prepend Company id Sequence and Who columns (last update date only Set DFF attributes for closed tasks according to type Null!
Company_UPD_Enrol_Source
Sequence Insert into enrolment_sources@etl_link, using attribute6 as code
Page 13 of 24
Database Links Link List Name ETLT_LINK.US.ORACLE.COM ETL_LINK.US.ORACLE.COM
46877296.doc
Target Host XYZNETLT XYZNETL
Target User ETLADMIN XYZ
Notes ETL Company Transactions database.
Page 14 of 24
Views/Synonyms Views Name
Notes Business Objects
XX_MIS% XX_BOJ% COMPANY_REPORT% XX_PRE_FULFILLMENT_V XX_FINANCIAL_V (Various)
Most views created for Business Objects reporting have the prefix shown Specific Purpose Returns the records that will be included in the weekly fulfilment job Financial report by month with cumulative values included Miscellaneous Various other views exist
Database Link Views/Synonyms These may be used for linking to other systems, for both table and package access. There are no current synonyms of this kind.
46877296.doc
Page 15 of 24
Interfaces Interfaces List External System Company.co m
In/Out ?
Frequency
Interface Type
In/Out
Real time
Web services
ETL Links
ETL
Out
Real time
Database links
ABC Links
ABC
In/Out
Real time
Database links
XYZ Transaction
ETL
In
Daily
PQR
PQR
Out
Daily
CC
CC
In/Out
Daily
Carhire
Carhire
In
Monthly
Weekly Fulfilment
Card supplier
Out
Weekly
Email Supplier
Email Supplier
Out
Sporadic
Octal
Octal
Out
Weekly
SMS Feed
ETL
Out
Daily
Name Company.com
Database links, scheduled Flat file, CFTP, scheduled Flat file, FTP, scheduled Flat file, email, manual Flat file, email, scheduled Flat file, FTP, scheduled Flat file, FTP, scheduled Flat file, FTP, scheduled
Notes Web site makes calls to XYZ web services Database links for Business Objects reports Database links for checking existence of claims and accounts across the two Loyalty systems – the XYZ-side link does not exist, but should CFTP is custom version of FTP. Company PQR Interface
Interfaces Diagrams Link Entities and Key Interfacing between internal databases is done using database links. The diagram below shows that information may be read from table (or view) synonyms or views pointing at remote tables and views, while synonyms to remote packages are used for writing information remotely, and may also return information.
46877296.doc
Page 16 of 24
46877296.doc
Page 17 of 24
XYZ Interfaces Diagram This diagram shows the interfaces between XYZ, the two internal databases, ABC and ETL, the various up and down-stream external systems, and the main front end interfaces.
46877296.doc
Page 18 of 24
Batch Programs Key: •
D = Daily
•
M = Monthly
•
S = Sporadic
•
4h = Every 4 hours
Interface Jobs Name Company CC Interface
F D
Executable File/Procedure XX_CC_FILE_PROCESS
Carhire Interface
D
partner_txn.sh
XXSMSYY - SMS transaction details extract
D
XXSMSYY.sql
XX Weekly Fulfilment
D
Company_Fulfillment_Mgmt_PK. Company_Fulfillment_Pro XX_Fulf_Email.prog XX_ADD_BEH_Process_PKG. XX_Create_Add_BEH_IH
Notes Credit Card Executed manually from Unix after copying emailed file to directory
Company Fulfilment Process XX Weekly Fulfilment Post-Processor Company Additional Behavioral Process Company Fulfillment Process Program
XX Weekly QES to EML
S XX_FULFILLMENT_PROCESS.prog
Company PQR Interface
D
XX_PK_PQR.PQR_BCE_Extract_D ata xx_pqr_interface
This calls two SQL scripts that in turn call a procedure and a CP, thus: XX_FULFILLMENT_PROCESS.sql -> XX_Fulfillment_Process_PKG.XXFulfill ment_Process_P XX_FTP_REQ.sql -> XXFTP Company PQR Data Extract Company PQR Interface File Transfer
Non-Interface Jobs Name
F
Executable File/Procedure
Company Contract Renewal Process
D
XX_Member_Status_Mgmt_PK. Renew_Contract_Proc
Company Downgrade Warning Process
M
XX_Member_Status_Mgmt_PK. Company_Card_Down_Warn
XX Archiving Program
D
Company Pending TXN Processing
D
XX Person Datafix
4h
XXPERDF.sql
XX Populate MIS Table
M
XXPMIS.sql
46877296.doc
XX_Customer_Archiving. XX_Customer_Archiving_Proc XX_Pending_TXN. XX_Pending_TXN_P
Notes Processes contract renewals and inactivations for processing by the fulfilment interface Creates interactions for warnings of likely downgrades for processing by the fulfilment interface Archives members inactive for a certain time, or marked for archiving Updates pending points when transactions take place Corrects known person data problems caused by web-site interface Populates staging tables used by monthly Business Objects financial reports
Page 19 of 24
Scripts SQL Scripts Name CC_partner_process.sql CC_fileout.sql XX_FULFILLMENT_PROC ESS.sql
Called From
Notes
XX_CC_FILE_PROCESS
XX_FULFILLMENT_PROCESS.prog
XX_FTP_REQ.sql XXPERDF.sql XXPMIS.sql XXSMSYY.sql partner_process.sql
XX Person Datafix XX Populate MIS Table XXSMSYY - SMS transaction details extract Carhire interface
Calls procedure XX_Fulfillment_Process_PKG.XXFulfillment _Process_P Triggers CP xXXFTP, which runs an FTP file transfer
Manually run
Unix Scripts Name XX_CC_FILE_PROCESS XX_Fulf_Email.prog
Called From Company CC Interface XX Weekly Fulfilment
XX_FULFILLMENT_PROCESS.prog
XX Weekly QES to EML
EUKFTP
XX_FTP_REQ.sql via a CP
partner_txn.sh
Manually from Unix
xx_pqr_interface
Company PQR Interface
46877296.doc
Notes XX Weekly Fulfilment Post-Processor Calls two SQL scripts that in turn call a procedure and a CP SQL script calls CP for EUKFTP Executed manually from Unix after copying emailed file to directory Uses cftsend file transfer utility
Page 20 of 24
User Interactions This section lists the main functional interactions possible through the front end applications, with source file names where in scope (i.e. for Call Centre forms, but web forms and Business Objects source files are out of scope)
Company.com Name Change Account Details Check Tier Status Claim Points Create Account Retro-Claim Points View Account View Statement
Notes A member can change the details on his account A non-member can apply for membership by submitting receipts, and a call is made to check what tier, if any, the member qualifies for, prior to account creation Entering the CIN number while making a purchase causes points to be assigned to the account A non-member has an account created after submitting receipts, if the call made to check what tier, if any, the member qualifies for, results in a membership tier Points can be claimed for a member for transactions already made A member can view his account details on-line A member can view his statement details on-line
Call Centre Name Run/View Requests screen eBusiness Centre screen XX Overview tab
Form File FNDRSRUN.fmb (standard)
ASTRCALL.fmb (customised version of standard form)
Status button Cancellation button Statement window Application window Retro window Redemption window Special window
COMPST04.fmb COMPST01.fmb COMPST02.fmb COMPST03.fmb COMPST06.fmb
References window
XXREF.fmb
Notes Standard functionality allowing reports to be run in batch and viewed in a screen or downloaded on completion This is a standard Oracle screen allowing customers to be viewed and updated, along with various standard CRM activities (notes, tasks, interactions etc.), and has a customised tab for custom functionality The XX tab on the eBusiness Centre screen allows the member’s tier and earnings to be viewed, and provides access to the windows and buttons listed below Allows the member’s tier to be changed manually Allows selected earnings to be cancelled Allows viewing of member’s statement Allows entry and viewing of an application form for a member Allows retro-claims to be made for points Allows recording of member redemptions Allows manual addition of points to an account This really means preferences. Preferences of the different available types may be viewed and set here
Business Objects Name Reporting
46877296.doc
Notes Miscellaneous reports are defined, and can be run by IT staff, as well as being scheduled as necessary
Page 21 of 24
Database Jobs Job List Name Gather Statistics
46877296.doc
Frequency Sundays at 21:00
Notes DBMS_Stats.Gather_Schema_Stats; 10% sampling
Page 22 of 24
Batch Jobs Schedule Interface Jobs Schedule •
* denotes that the job runs on the external system
•
CP denotes Oracle concurrent program
•
RS denotes Oracle concurrent request set
•
Cron is the Unix job scheduling utility
Start Time
Daily, or …
0500
Fridays
0700 1600 0800 NA
Monthly
NA
Sporadic
0800
Mondays
0630 1300
External System Card supplier
In/Out ?
Job
Job Type
Notes
Out
Company Fulfilment Process
CP
Runs after contract renewal and downgrade warning
ETL*
In
XYZ Transaction
cron
Twice daily
CC Carhire Email supplier Octal SMS provider PQR
In/Out In
Company CC Interface Carhire
CP Manual
Run manually from emailed file
Out
XX Weekly QES to EML
RS
Out
Octal XXSMSYY - SMS transaction details extract Company PQR Interface
cron
Out Out
Run on XYZ Server via cron
CP CP
Other Jobs Schedule Start Time
Daily, or …
0430 0445
4’th of month
0700 0700 0200 2200
Every 4 hours 2’nd of month
46877296.doc
Job Company Contract Renewal Process Company Downgrade Warning Process XX Archiving Program Company Pending TXN Processing XX Person Datafix XX Populate MIS Table
Job Type
Notes
CP Run before the weekly fulfilment interface CP CP CP CP CP
Page 23 of 24
Issues and References Issues #
Issue
1
Obsolete objects
2
Business logic
3
References
Description Intend to delete these at a later stage, but retain for version control Check whether covered sufficiently Need to add for detailed design documents
Note if closed
References REF
Document
REF-1
eTRM
REF-2 REF-3 REF-4
ETL Company Transactions Database - System Design.doc (detailed design documents) Unit Test Plan Template.doc
46877296.doc
Location http://etrm.oracle.com/pls/trm1157/etrm_s earch.search PVCS PVCS scribd
Page 24 of 24
View more...
Comments