A Database System High Level Design Template | Oracle Database ...

January 31, 2018 | Author: Anonymous | Category: Oracle
Share Embed


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

Copyright © 2017 DATENPDF Inc.