sap maintainace manual

RSS #12013031549CCMSCoverage & Duplicate

member folder

Client ServerSoftware Design Document

Version # : 0.2Released on : 08/28/2014Author : Sreedhar Reddy

Dell ServicesBlue Cross Blue Shield of Rhode Island Account1 Cookson PlaceProvidence, RI02903

Dell Services Confidential

BCBSRI SDD of 12013031549_CCMS_Coverage & Duplicate member folderDell Services

Table of Contents

1. Introduction........................................................................................................................ 4

1.1 Purpose.................................................................................................................... 41.2 Scope....................................................................................................................... 41.3 Out-of-Scope............................................................................................................41.4 Contact Details.........................................................................................................51.5 Intended Audience....................................................................................................51.6 Acronyms and Definitions.........................................................................................51.7 References...............................................................................................................51.8 Requirement Summary.............................................................................................6

2. Design Approach................................................................................................................8

2.1 Current Processing...................................................................................................82.2 Proposed Processing...............................................................................................9

2.2.1 Approach........................................................................................................92.2.2 Technical details...........................................................................................10

3. Architectural Design.........................................................................................................16

3.1 Architectural Representation..................................................................................163.2 Architectural Goals and Constraints.......................................................................203.3 Use-Case View and Realizations...........................................................................203.4 Process View (Optional).........................................................................................203.5 Deployment / Implementation View (Optional).......................................................20

3.5.1 Overview.......................................................................................................203.5.2 Layers...........................................................................................................20

3.6 Data View (Optional)..............................................................................................203.7 Performance Considerations (Optional).................................................................20

4. General Design and Implementation................................................................................21

4.1 Interfaces with Other Systems................................................................................214.2 List of Impacted Inventory......................................................................................214.3 Exception Handling.................................................................................................224.4 Software Distribution..............................................................................................224.5 Reusable Components...........................................................................................224.6 Scheduling Impact..................................................................................................22

5. Database Design..............................................................................................................22

6. Inputs and Outputs...........................................................................................................23

6.1 Screen Layouts.......................................................................................................236.2 Report Layouts.......................................................................................................23

6.2.1 Input Structure..............................................................................................236.2.2 Output Structure...........................................................................................23

7. Non-Development Services Considerations.....................................................................23

8. Constraints....................................................................................................................... 23

9. Assumptions and Issues..................................................................................................23

10. Appendices...................................................................................................................... 24

10.1 Appendix 1 – Data Flow Diagram of Existing process............................................2410.2 Appendix 2 – Queries to create IMEMBER.CMS file..............................................2410.3 Appendix 3 – Queries to create IMEMCVR.CMS file..............................................2410.4 Appendix 4 – Queries to create IMEMCVRO.CMS file...........................................2410.5 Appendix 5 – Queries to create IMEMPCPH.CMS file...........................................2510.6 Appendix 6 – Query to extract next DFG sequence number from CCMS...............25

SDD Confidential of 26


10.7 Appendix 7-Mapping Sheet....................................................................................25

11. Amendment History..........................................................................................................26

12. Approval of Final Document.............................................................................................26



1. Introduction

1.1 PurposeThe purpose of this document is to present the Client Server Functional Specification & Software Design for the RSS #12013031549 “CCMS Coverage & Duplicate member folder”.

This request is to 1) correctly display the members’ current coverage, 2) correct the members’ historical coverage start and end dates and 3) merge deactivated records with the members’ active record so that each member record is whole within the CCMS system.

The purpose of this document is to identify the Member Eligilibility for CCMS.

1.2 ScopeThe scope of this RSS includesmodifying thecurrent Member/Coverage extract process which loads data from the EDR/ODS/Facets into CCMS such that the records are written to the .CMS file based on the comparison process between the EDR and the CCMS data unlike the existing process where the member Data that was added after the last CCMS update timestamp is extracted from the EDR and sent over to the CCMS load process

Along with this, the whole CMMEMB process will be redesigned on Datastage 8.1 and the member merge process (CMEVENT) will be merged in that one single flow.

The Following changes is done to meet the requirement for Prospect

Indicate on the CCMS Eligibility file that the Medicare Advantage Members have a Prospect PCP.

1.3 Out-of-ScopeThe following items are out of scope for this RSS:

1. Data mapping between the EDR and CCMS with respect to any key or non-key data elements of member eligibility data. The CCMS eligibility data load process must work using the existing data mapping structure.

2. Issues related to Consistent Member Id where the Legacy member Id is different for the same member. This issue will need to continue to be addressed by users through other systems, such as LRSP and Facets, with corrections through the appropriate business departments.

3. Data stage job dependency issues creating increases in processing time – these issues are being handled separately as production incidents and the resolutions should be implemented in Production before the RSS is implemented.

4. Any issues with FEP membership. FEP membership does not reside in the EDR.



5. Benefits extract and upload process.

6. Providers extract and upload process.

1.4 Contact Details

Name Location Contact Number

Requested by Natalie Figueiredo RI

Project Manager Abhijit Adak RI 2916

Business Analyst VikasSaini RI 1717

DBA

Primary Business Customer Natalie Figueiredo RI

POC ManojYaduvanshi Noida 3860

Developer Swati Madan Noida 2117

Quality Control AmarjeetGulati Noida 3860

1.5 Intended Audience

Name – Primary Stakeholders Role Department

Natalie Figueiredo Primary Business Customer Shared Services

Maureen Beaudoin IMU Lead EIM

Al Zannini IMU Lead Application Design & Support

Abhijit Adak Delivery Manager ADM

Development Team Development ADM

Testing Team Testing ADM

1.6 Acronyms and DefinitionsPlease refer to the RAID document attached in Section 9.

1.7 References

Name

BRS_12013031549_CCMS_Coverage & Duplicate member folder_V1.0_20121115



1.8 Requirement Summary Essential (E) – Implies the system will not be acceptable unless the requirements are provided

in an agreed-upon manner

Conditional (C) – Implies these are requirements that would enhance the software product; would not make the system unacceptable if they were absent

Optional (O) – Implies a requirement that may or may not be worthwhile

Requirement ID Source

Requirement Description

Additional Supporting Information

PriorityE / C / O

BR01 EDRExtract all active eligibility records

E

BR02 CCMSExtract all active eligibility records

E

BR03 DataStage

Join both data sets to keep all records from EDR and CCMS

E

BR04 DataStageFilter out unchanged records

E

BR05 DataStage

Format remaining records based on most current data elements available

E

BR06 DataStage

Load remaining records into text file of the CCMS required format

E

BR07 CCMSLoad records using CCMS interface

E

BR08 DataStage

Run merge process on all members,including those with events, open reminders and active cases

E



Requirement ID Source

Requirement Description

Additional Supporting Information

PriorityE / C / O

BR09 DataStageEstablish priority for merge process

E

BR10 DataStage

Propagate all members with event, open reminders and active cases to merge process the same day a change has occurred

E

BR11 DataStage

Reevaluate if there is a limit to the number of merge records that can be processed in a single night and reset that limit accordingly

E

BR12 DataStage

Combine all DataStage daily processes into one master series and run the jobs sequentially in one stream

Suggested by Mike Goldrick

C

SR_PM_CCMS01:( BFR71179)BCBSRI CM and UM staff must be able to uniquely identify Medicare

Advantage members with a Prospect PCP so that they may transfer any CM or DM activities to Prospect.

SR_PM_CCMS01.1: Add/Identify a field on the CCMS eligibility file that will indicate the Medicare Advantage Member with prospect PCP. Changes would be required in IMEMCVR.CMS

Medicare Advantage Members in CCMS can be identified by member_coverage.benefit_plan = "3" (PLAN_DESC = CHiPMedicare)

Prospect PCPs can be identified as: PCPs in Table - CMC_NWPX_RELATION Field - NWPX_ID ID – ‘CHRC’



Get the Provider Ids from above criteria, create a custom table/file and feed this list to the PCP_ATTRIBUTION_FACT and get the list of all the Members associated with the PCPs.

The Med Adv Members who have the attributed PCPs can be identfied by joining Consistent Member Id with PCP_ATTRIBUTION_FACT table and get the Id who have attributed PCPs and use these PCPs to perform above look up.

Changes would be required in Eligibility Files sent to CCMS to uniquely identify Medicare Advantage Members with prospect PCP.

There is a field called member_risk_group on the member interface file. Business is looking to use that field to indicate the member's PCP is with Prospect. The field is part of an existing interface so that would just be a change to that DS module.

2. Design Approach

2.1 Current ProcessingThere are three processes in Datastage to load and update the members’ data in CCMS. Please refer to the attached diagram file in Appendix 1 for the logical diagrams of existing systems.

1. CMMEMB (Datastage 7.5) – This is a load to add new members and member coverage data from the EDR to CCMS. This process extracts the member and coverage details from the EDR that have the highest end date subsequent to the last CCMS update timestamp. These records are then selected for delivery to CCMS. This process generates the following export files for CCMS on a daily basis:

a. member file - IMEMBER.CMSb. member coverage file - IMEMCVR.CMSc. member coverage other file - IMEMCVRO.CMSd. member pcp history file - IMEMPCPH.CMS

2. CMRETRO (Datastage 7.5) – This process compares the CCMS members against the EDR members and activates/deactivates members or member coverage records in CCMS This process excludes any members with events, open reminders or active cases at this time. This process generates the following export files for CCMS on a weekly basis:

a. member file - IMEMBER.CMSb. member coverage file - IMEMCVR.CMS

3. CMEVENT (Datastage version 7.5.2) - In this process the CCMS Member Data for Inactive Members is compared with Active members based on the Legacy_Member_id or Policy Number. This process is also called the ‘Member Merge Process’. and generates the following export file for CCMS on a weekly basis:

a. member merge file - ITRANS.CMS



2.2 Proposed ProcessingAs per the proposed processing, all the above member processes (CMMEMB, CMRETRO and CMEVENT) will be merged together into one daily process to address both the retroactivity and member merging. And this daily process will be newly created in Datastage 8.1. Also the approach to capture the new/changed records for load from EDR to CCMS will be changed.

2.2.1 Approach

The complete architectural design is attached in section 3 of the document.

ForBR01 thru BR12 and BR17,The below approach will be used for generating the .CMS files that will be loaded at CCMS through the REFRESH process:

1. Extract the membership or coverage or pcp history data from EDR (depending upon the type of extract to be generated) for only those members that are having the LEGACY_SOURCE_CODE as ‘370LRSP’ or ‘370FACS’ and MEMBER_MEDICAL_COB_CODE not equal to ‘N’.

2. Convert all the data elements from EDR according to the existing CCMS data mapping.3. Extract the membership or coverage or pcp history data from CCMS (depending upon the

type of extract to be generated) for only those members that are active and whose MEMBER_ID are not prefixed with a ‘$’ or ‘FEP’.

4. Compare each and every data element of the EDR and CCMS extracts obtained from step 1 and step 3 respectively using the Change Capture stage of Datastage. Based on the comparison, incoming data will be segregated in to 4 extracts: New records: Those records that are in EDR but not in CCMS Changed records: Those records that are in both EDR and CCMS but with a change in

values of one or more columns. Unchanged records: Those records that are in both EDR and CCMS with all column’s

values exactly same. Void records: Those records that are in CCMS but not in EDR

5. In case of membership extract, process the void records as per the merge process (explained later in this section). In case of coverage and pcp history extract, combine the new, changed and void records (with ACTIVE_CODE as ‘N’) to create the .CMS files to be updated in CCMS through REFRESH process.

MERGE PROCESS

The void members will be processed further using the below steps:

1. Check if any of the members has any case/event/reminder/note in CCMS or not. If not, the member can be deactivated in CCMS.2. The LEGACY_MEMBER_IDs of the members having any case/event/reminder/note in CCMS will be looked up in EDR to check if the member exists in EDR with same LEGACY_MEMBER_ID but different CONSISTENT_MEMBER_ID. If the member exists, write the member’s old CONSISTENT_MEMBER_ID and new CONSISTENT_MEMBER_ID in ITRANS.CMS file.



There will be no change in the approach for creating member coverage other extract. The job will be just re-designed on datastage server 8.1 without any change.

Refer the Technical details section for more details.

2.2.2 Technical details

New jobs will be created on version 8.1 of Datastage.

Datastage Project: DMDDEVFolder/Category: Jobs\CCMS\CMMEMBSequence: CMMEM_UPD_SEQJobs: CMMEM_UPD_SOJ

CMMEM_MBR_SEQCMMEM_MBR_CMPRCMMEM_MBR_MERGECMMEM_MBR_EXTCMMEM_CVR_EXTCMMEM_CVRO_EXTCMMEM_PCP_EXTCMMEM_GEN_FILE_LSTCMMEM_GET_NXT_SEQ_NUMCMMEM_UPD_EOJ

All the intermediate files, final extracts and the EOJ will be created at the unix location ‘/data/dev/dmd/ccms/cmmemb/’ on BRIDDU0027 server.

Below are the job details:

1. CMMEM_UPD_SEQJob Sequence will be controlling the execution of jobs. In case an error comes, the whole Datastage process will be terminated.

Following job activities would be part of the sequence-

CMMEM_UPD_SOJCMMEM_MBR_SEQ (sub-sequence)CMMEM_CVR_EXTCMMEM_CVRO_EXTCMMEM_PCP_EXTCMMEM_GEN_FILE_LSTCMMEM_GET_NXT_SEQ_NUMCMMEM_UPD_EOJ

2. CMMEM_UPD_SOJ

This job will delete the previously generated EOJ file.



3. CMMEM_MBR_SEQ

This job sequence will be called by the master sequence CMMEM_UPD_SEQ.

Following job activities would be part of this sequence-CMMEM_MBR_CMPRCMMEM_MBR_MERGECMMEM_MBR_EXT

4. CMMEM_MBR_CMPR

This job will perform the steps mentioned below:1. Extract the member demographic data from the view VW_MEMBER of EDR for all

members having the LEGACY_SOURCE_CODE as ‘370LRSP’ or ‘370FACS’ and MEMBER_MEDICAL_COB_CODE<> ‘N’. Refer the query ‘EDRExtract.sql’ used to extract the EDR data attached with Appendix 2. Retain the EDR extract query results in to a sequential file ‘EDR_Mbr_Ext.txt’ to be used further in merge process.

Also store the subscriber id, dependent number and consistent member id of all members coming out of the query in an intermediate file ‘NZ_member.txt’ to be used in member other coverage process.

The impute PCP Id for the member comes from a file that is created from the job CMImputePCP90A ofanother process CMImputPCPUpdate. As per the existing design, the process is creating a hash file. The process will be modified a little to create a sequential file ‘PCPImpute.txt’ as well along with the hash file so that it can be ftp’ed to datastage server 8.1 and can be used in this CCMS membership process.

2. Extract the member demographic data from tables MEMBER and MEMBER_PHONE of CCMS database for all the active members (ACTIVE_CODE = ‘Y’) that are not having MEMBER_ID prefixed with a ‘$’ or ‘FEP’ (as FEP or temporary members do not exist in EDR). Refer the query ‘CCMSExtract.sql’ used to extract the CCMS data attached with Appendix 2.

3. Compare the EDR and CCMS extract created in above two steps using a Change Capture stage. Key to be used to join both extracts is

EDR.CONSISTENT_MEMBER_ID= CCMS.MEMBER_ID

Four extracts will be created based on the above compare process:a) NewMbrRecExt.txt: All the records that are present in EDR but not in

CCMS will be captured in ‘NewMbrRecExt.txt’ file. b) ChangedMbrRecExt.txt: All the records that are present in both EDR

and CCMS but with a change in values of one or more columns will be captured in ‘ChangedMbrRecExt.txt’ file.

c) UnchangedMbrRecExt.txt: All the records that are present in both EDR and CCMS and having all column’s values exactly same will be captured in ‘UnchangedMbrRecExt.txt’ file. (The file will not be used further but it has been created for the analysis purpose.)

d) VoidMbrRecExt.txt: All the records that are present in CCMS but not in EDR will be captured in ‘VoidMbrRecExt.txt’ file.

5. CMMEM_MBR_MERGE

This job will process the void records captured in ‘VoidMbrRecExt.txt’ using the below steps:



1. The job will first check if any of the members is having any case/event/notes/reminder (open/closed) in CCMS or not. If the member is not having any case/event/notes/reminder in CCMS, the job will write the record in an intermediate file ‘InactiveMbr.txt’ with the ACTIVE_CODE of that member as ‘N’. Refer the query ‘CaseStatus.sql’ used to check the case/event/reminder/notes status attached with Appendix 2.

If (AUTH_STATUS = 'X' AND REF_STATUS = 'X' AND SERV_STATUS = 'X' AND CAS_STATUS = 'X' AND REMD_STATUS = 'X' AND NOTE_STATUS = ‘X’) Then deactivate the recordElse move to step 2

2. The members for whom any case/event/notes/reminder exists in CCMS will be processed in further steps.

3. The job will then check the existence of members coming from step 1 in the EDR_Mbr_Ext.txt file created in step 1 of CMMEM_MBR_CMPR job using the key POLICY_NUMBER (i.e. the LEGACY_MEMBER_ID in EDR). This check will capture all records having same LEGACY_MEMBER_ID but different CONSISTENT_MEMBER_ID. All these records will be merged and written to the ITRANS.CMS file. The members having any of the case/event/notes/reminder status (from step 1) as ‘O’ (open) will be written to the ITRANS.CMS file first followed by the members with closed/deactivated case/event/notes/reminder (having status ‘C’) with a limit of 1000.No limit will be applied to the open case/event/notes/reminder.

6. CMMEM_MBR_EXT

This job will merge the below files and create a single file ‘IMEMBER.CMS’ to be sent to CCMS.

1. NewMbrRecExt.txt (created out of step 3(a) of CMMEM_MBR_CMPR job)2. ChangedMbrRecExt.txt (created out of step 3(b) of CMMEM_MBR_CMPR job)3. InactiveMbr.txt (created out of step 2 of CMMEM_MBR_MERGE job)

7. CMMEM_CVR_EXT

This job will create the file ‘IMEMCVR.CMS’ using the steps mentioned below:1. Extract the member coverage data from the view

VW_MEMEBER_ELIGIBILITY_FACT join to VW_MEMBER using key MEMBER_ID of EDR for all members having the LEGACY_SOURCE_CODE as ‘370LRSP’ or ‘370FACS’ and MEMBER_MEDICAL_COB_CODE<> ‘N’. Refer the query ‘EDRExtract.sql’ used to extract the EDR data attached with Appendix 3.

The Plan Id for the member comes from a file that is created from the job CMBenProd12F ofanother process CMBenUpdate. As per the existing design, the process is creating a hash file. The process will be modified a little to create a sequential file ‘NZProduct.txt’ as well along with the hash file so that it can be ftp’ed to datastage server 8.1 and can be used in this CCMS coverage membership process.

2. Extract the member coverage data from tables MEMBER_COVERAGE and MEMBER_COVERAGE_HISTORY ofCCMS database for all the active member coverages (ACTIVE_CODE = ‘Y’) that are not having MEMBER_ID prefixed with a



‘$’ or ‘FEP’ (as FEP or temporary members do not exist in EDR). Refer the query ‘CCMSExtract.sql’ used to extract the CCMS data attached with Appendix 3.

3. Compare the EDR and CCMS extract created in above two steps using a Change Capture stage. Keys to be used to join both extracts are

MEMBER_IDBENEFIT_GROUPBENEFIT_PLANBENEFIT_PRODUCTENROLLMENT_DATE

The change capture stage segregates the incoming data and passes it to four different links based on the change code values (i.e. 0, 1, 2, 3)a) Change code 0: These will be the eligibility records that are present in

both EDR and CCMS and having all column’s values exactly same. All these records will be captured in a file ‘UnchangedCovRecRxt.txt’. (The file will not be used further but it has been created for the analysis purpose.)

b) Change code 1: These will be the eligibility records (void) that are present in CCMS but not in EDR. All these records will be passed further but with the ACTIVE_CODE as ‘N’.

c) Change code 2: These will be the eligibility records that are present in EDR but not in CCMS.

d) Change code 3: These will be the eligibility records that are present in both EDR and CCMS but with a change in values of one or more columns.

4. The records coming from steps 3(b), 3(c) and 3(d) will be combined using a funnel stage and then sorted based on keys

MEMBER_IDBENEFIT_GROUPBENEFIT_PLANBENEFIT_PRODUCTENROLLMENT_DATE

5. The job will then check for multiple occurrences of MEMBER_ID || BENEFIT_GROUP || BENEFIT_PLAN || BENEFIT_PRODUCT || ENROLLMENT_DATEIf multiple occurrences found, the job will pass only that record that has the value in column ‘primary_secondary’ as ‘P’ (Primary coverage).

6. All the records coming from step 5 will be written to a file ‘IMEMCVR.CMS’ that will be sent to CCMS.

8. CMMEM_CVRO_EXT

This job will create the file ‘IMEMCVRO.CMS’ using the steps mentioned below:1. Extract the member other coverage data from the table TB_MHN_NON_OCCURS of

ODS based on the criteria specified in the query attached with Appendix 4. 2. Get the member’s consistent member id from the file ‘NZ_member.txt’ created in step 1

of CMMEM_MBR_CMPR job by joining the query’s result with the file data based on the keys SUBR_ID_KEY and DEP_NO and fetching the CONSISTENT_MEMBER_ID from the file. Drop the records that are not matched.

3. Get the Facet’s other coverage data coming from the input file ‘IMEMCVRO.TXT’ and merge it with data coming from step 2 using a funnel stage. Retain the merged data in a sequential file ‘IMEMCVRO.CMS’ that will be sent to CCMS.



9. CMMEM_PCP_EXT

This job will create the file ‘IMEMPCPH.CMS’ using the steps mentioned below:1. Extract the member PCP data from the views VW_MEMBER_PCP_FACT_ALL joined

with VW_PROVIDER on PROV_ID of EDR based on the criteria specified in the query attached with Appendix 5.

2. Extract the member PCP data from the table MEMBER_PCP_HISTORY ofCCMS database for all the active PCPs (ACTIVE_CODE = ‘Y’) that are not having MEMBER_ID prefixed with a ‘$’ or ‘FEP’ (as FEP or temporary members do not exist in EDR). Refer the query ‘CCMSExtract.sql’ used to extract the CCMS data attached with Appendix 5.

3. Compare the EDR and CCMS extract created in above two steps using a Change Capture stage. Keys to be used to join both extracts are MEMBER_ID, MEMBER_PCP (i.e. LEGACY_PROV_ID in EDR) and PCP_START_DATE.

The change capture stage segregates the incoming dataand passes it to four different links based on the change code values (i.e. 0, 1, 2, 3)

a) Change code 0: These will be the records that are present in both EDR and CCMS and having all column’s values exactly same. All these records will be captured in a file ‘UnchangedPCPRecExt.txt’. (The file will not be used further but it has been created for the analysis purpose.)

b) Change code 1: These will be the records that are present in CCMS but not in EDR. All these records will be passed further but with the ACTIVE_CODE as ‘N’.

c) Change code 2: These will be the records that are present in EDR but not in CCMS.

d) Change code 3: These will be the records that are present in both EDR and CCMS but with a change in values of one or more columns.

4. The records coming from steps 3(b), 3(c) and 3(d) will be combined using a funnel stage and written to a file ‘IMEMPCPH.CMS’ that will be sent to CCMS.

10. CMMEM_GEN_FILE_LST

The job creates the file IFILELST.CMS from the input file IFILELST.SAV placed at the location ‘/data/dev/dmd/ccms/cmmemb/’ on BRIDDU0027 server. This file is further used in REFRESH process.

11. CMMEM_GET_NXT_SEQ_NUM

The job extracts the next sequence number for the DFG folder from the CCMS_CONFIG table of CCMS database using the query attached with Appendix 6and writes it to a file DFGSEQ.TXT. In case the next sequence number in database is less than 10, the job prefixes the sequence number with ‘00’, else if it is less than 100, job prefixes the sequence number with ‘0’ else pass it as is.

12. CMMEM_UPD_EOJ

This job will create a End of Job file having the input, load and the reject counts.



13.CMMS_PCP

SR_PM_CCMS01: BCBSRI CM and UM staff must be able to uniquely identify Medicare Advantage members with a Prospect PCP so that they may transfer any CM or DM activities to Prospect

New dataStage job will Created to identify the Medicare Advantage Member WithProspectPCP.

Medicare Advantage Members can be identify as below

member_coverage.benefit_plan = "3" (PLAN_DESC = CHiPMedicare)

Changes would be required inIMEMCVR.CMS

Prospect PCPs can be identified asCMC_NWPX_RELATION Field - NWPX_ID ID – ‘CHRC’

Members associated with the PCPs.PCP_ATTRIBUTION_FACT

Changes would be required in Eligibility Files sent to CCMS to uniquely identify Medicare Advantage Members with prospect PCP.



3. Architectural Design

3.1 Architectural RepresentationThe architectural representation of the Delta process is shown in the below diagram:


DATASTAGE PROCESSActive EDR, ODS and Facets Records

Conversion to CCMS data elements

File Created for CCMS interface data load

CCMS Interface for customer data load

Active CCMS records New or Changed


The architectural representation of the Merge process is shown in the below diagram:


Void/Y Consistent Member Id Change?

Use CCMS data to create record with Active Status=’N’

Any Cases/ Events/Reminders/Note

s?

Drop record, no change to CCMS,

keep member active

Proceed with creating merge records file to

send to CCMS.

NO

NO

YES

YES


The below screenshot depicts the Data stage job design of the master-sequence CMMEM_UPD_SEQ:



The below screenshot depicts the Data stage job design of the sub-sequence CMMEM_MBR_SEQ:



3.2 Architectural Goals and ConstraintsNot Applicable.

3.3 Use-Case View and RealizationsNot Applicable.

3.4 Process View (Optional)Not Applicable.

3.5 Deployment / Implementation View (Optional)

Not Applicable.

3.5.1 Overview

Not Applicable.

3.5.2 Layers

Not Applicable.

3.6 Data View (Optional)Not Applicable.

3.7 Performance Considerations (Optional)

Not Applicable



4. General Design and Implementation

4.1 Interfaces with Other SystemsEDR, ODS and CCMS databases

4.2 List of Impacted Inventory

A) Program

Sr. No. Name New/Old Change SummaryRequirement Number

1 CMMEM_UPD_SEQ New BR01 thru BR12 and BR17

2 CMMEM_UPD_SOJ New BR01 thru BR12 and BR17

3 CMMEM_MBR_SEQ New BR01 thru BR12 and BR17

4 CMMEM_MBR_CMPR New BR01 thru BR12 and BR17

5 CMMEM_MBR_MERGE New BR01 thru BR12 and BR17

6 CMMEM_MBR_EXT New BR01 thru BR12 and BR17

7 CMMEM_CVR_EXT New BR01 thru BR12 and BR17

8 CMMEM_CVRO_EXT New BR01 thru BR12 and BR17

9 CMMEM_PCP_EXT New BR01 thru BR12 and BR17

10 CMMEM_GEN_FILE_LST New BR01 thru BR12 and BR17

11 CMMEM_GET_NXT_SEQ_NUM New BR01 thru BR12 and BR17

12 CMMEM_UPD_EOJ New BR01 thru BR12 and BR17

13 CCMS_PCP New SR_PM_CCMS01

B) Copy Books, Schema Definitions, File Layout


N/A

H) Database


N/A



4.3 Exception HandlingEach Data Stage job contains an Exception Handling Stage. It is configurable in the job to create a warning or ABEND in any step of the job. Below is an excerpt from the DataStage Exception documentation.

An exception activity handles the situation where a job in the sequence fails to run (other exceptions in the job sequence are handled by triggers).

An exception activity can only have a single unconditional output trigger, so does not require a Triggers page. It has no input triggers. It serves as a starting point for a sequence of activities to run if an exception has occurred somewhere in the main sequence.

4.4 Software DistributionThe Data Stage jobs reside in Categories in each DataStage project. The jobs in respective category will be exported from Development and imported into UAT by the IS Data Center staff. After UAT testing, the Category will be exported from UAT and imported into Production by the IS Data Center staff.

4.5 Reusable ComponentsNot Applicable.

4.6 Scheduling ImpactNot Applicable.

5. Database Design

Not Applicable.



6. Inputs and Outputs

6.1 Screen LayoutsNot Applicable.

6.2 Report Layouts

6.2.1 Input Structure

Not Applicable.

6.2.2 Output Structure

Not Applicable.

7. Non-Development Services Considerations

Not Applicable.

8. Constraints

Not Applicable.

9. Assumptions and Issues

Please refer to the RAID document as attached below:



10. Appendices

10.1 Appendix 1 – Data Flow Diagram of Existing process

10.2 Appendix 2 – Queries to create IMEMBER.CMS file

10.3 Appendix 3 – Queries to create IMEMCVR.CMS file

10.4 Appendix 4 – Queries to create IMEMCVRO.CMS file



10.5 Appendix 5 – Queries to create IMEMPCPH.CMS file

10.6 Appendix 6 – Query to extract next DFG sequence number from CCMS

10.7 Appendix 7-Mapping Sheet



11. Amendment History

Version Date Additions / Modifications Prepared / Revised By

0.1 12/21/2012 Initial Version Swati Madan

0.2 08/31/2015 Updated for CCMS_PCP Sreedhar Reddy

12. Approval of Final Document

Name Role / Position Signature / Date

Michael Gold rick Tech Lead

ADM Peer Review Committee Peer Review


sap maintainace manual

Documents

ccms data

member data

ccms load process

ccms system

data view optional203

process view optional203

member record

data mapping