-
Type:
Support Activity
-
Status: Closed
-
Priority:
Medium
-
Resolution: Done
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: BenAdmin
-
Labels:None
-
Support Task Type:Query Resolution
-
Reported by:Support
-
Module:Platform
-
Severity:Simple
Attached [^Field Mapping Definitions - 20160712.xls] is the field mapping definitions we have currently. Would it be possible to get an updated data dictionary for the Workterra fields.
| Field | Original Value | New Value |
|---|---|---|
| Account Executive | Cindy Wibbing [ cindy.wibbing ] | |
| Description | Attached [^Field Mapping Definitions - 20160712.xls] is the field mapping definitions we have currently. Would it be possible to get an updated data dictionary for the Workterra fields. |
Attached [^Field Mapping Definitions - 20160712.xls] is the field mapping definitions we have currently. Would it be possible to get an updated data dictionary for the Workterra fields. Cc: [~abhay.patil] [~amitthorve] |
| Developer | Lalit Kumar [ lalit.kumar ] | |
| QA | Hrishikesh Deshpande [ hrishikesh.deshpande ] |
| Assignee | Vijay Siddha [ vijays ] | Lalit Kumar [ lalit.kumar ] |
-
- Time Spent:
- 0.5h
-
Discussion and understanding
| Remaining Estimate | 0h [ 0 ] | |
| Original Estimate | 8h [ 28800 ] |
| Time Spent | 0.5h [ 1800 ] | |
| Worklog Id | 30909 [ 30909 ] |
| Remaining Estimate | 0h [ 0 ] | 7.5h [ 27000 ] |
| Remaining Estimate | 7.5h [ 27000 ] | 3.5h [ 12600 ] |
| Time Spent | 0.5h [ 1800 ] | 4.5h [ 16200 ] |
| Worklog Id | 30910 [ 30910 ] |
-
- Time Spent:
- 2h
-
Checked below fields implementation in Export.
- Import/Export Card/Welcome Pkg Indicator
- Import/Export Claims Free Indicator
- Import/Export Name Print Indicator
- Import/Export Medical Flex Spending Account
- Import/Export Transaction Code
- Import/Export Dependent Care Flex Spending Account
- Import/Export Child Serial Number
- Import/Export Beneficiary Serial Number
- Import/Export Emload Change Reason
- Import/Export Country Code
- Import/Export Action Type
| Remaining Estimate | 3.5h [ 12600 ] | 1.5h [ 5400 ] |
| Time Spent | 4.5h [ 16200 ] | 6.5h [ 23400 ] |
| Worklog Id | 31602 [ 31602 ] |
Lalit Kumar (Inactive)
made changes -
| Attachment | Field Mapping Definitions - 20170314.xls [ 42300 ] |
Lalit Kumar (Inactive)
made changes -
| Attachment | Field Mapping Definitions - 20170314.xls [ 42300 ] |
Lalit Kumar (Inactive)
made changes -
| Attachment | Field Mapping Definitions - 20170316.xls [ 42301 ] |
Hello Cindy Wibbing
The updated data dictionary is attached to the ticket. Please click [here |^Field Mapping Definitions - 20170316.xls] to download the sheet.
Fields #365 to #378 are added in the sheet.
Fields #258 to #268 are updated in the sheet.
CC: Satya,Vijay Siddha,Rakesh Roy
Regards
Lalit Kumar
Lalit Kumar (Inactive)
added a comment - Hello Cindy Wibbing
The updated data dictionary is attached to the ticket. Please click [here |^Field Mapping Definitions - 20170316.xls] to download the sheet.
Fields #365 to #378 are added in the sheet.
Fields #258 to #268 are updated in the sheet.
CC: Satya , Vijay Siddha , Rakesh Roy
Regards
Lalit Kumar Lalit Kumar (Inactive)
made changes -
| Assignee | Lalit Kumar [ lalit.kumar ] | Cindy Wibbing [ cindy.wibbing ] |
| Remaining Estimate | 1.5h [ 5400 ] | 1.25h [ 4500 ] |
| Time Spent | 6.5h [ 23400 ] | 6.75h [ 24300 ] |
| Worklog Id | 31687 [ 31687 ] |
Lalit Kumar (Inactive)
made changes -
| Resolution | Done [ 10000 ] | |
| Status | Open [ 1 ] | Resolved [ 5 ] |
| Remaining Estimate | 1.25h [ 4500 ] | 0.75h [ 2700 ] |
| Time Spent | 6.75h [ 24300 ] | 7.25h [ 26100 ] |
| Worklog Id | 31806 [ 31806 ] |
Thank you!!
Satya thanks for sharing the data dictionary. I have a few questions, it would be great if you could respond.
(1) Can you please specify the "nature". E.g. as I understand the field source/ nature can be of the following types.
- Direct field from the database - from demographics (e.g. Subscriber SSN), enrollment (Enrollment effective date) etc.
- Derived or conditional field (macro?) - i.e. it is derived by consulting a set of direct fields
- Behavior - i.e. it doesn't give a value, but changes formatting behavior (e.g. Remove Trailing Comma, Multiple Child Seq Indicator).
(2) Whether the field is amenable to data transformation or not. When one goes to data transformation, ONLY the fields that are amenable to data transformation show up. It will be good to know about this property.
(3) At most places the description is too cryptic. We would like to have description that is good enough to go into a user manual. E.g. in one case it says ""Used on Custom Template Do not Use Returns employee cost for respective plan".
Thanks,
| Resolution | Done [ 10000 ] | |
| Status | Resolved [ 5 ] | Reopened [ 4 ] |
| Assignee | Cindy Wibbing [ cindy.wibbing ] | Satya [ ID10004 ] |
| Status | Reopened [ 4 ] | In Progress [ 3 ] |
Satya - can you please also share the data model - i.e. database tables and their relationship (primary and foreign keys) as discussed in our last meeting? Thanks!
Hi Abhay,
Yes, we will share these informations for data dictionary & more explanation for fields.
Regards,
Satya Prakash
Cc: Samir Umesh Kanade Vijay Siddha Indraprakash Tiwari Lalit Kumar Rakesh Roy Vinayak Kulkarni
Hi Lalit,
Let's discuss to modify our previously shared document.
Regards,
Satya Prakaksh
| Assignee | Satya [ ID10004 ] | Lalit Kumar [ lalit.kumar ] |
-
- Time Spent:
- 4.5h
-
Review all the fields
Classifying the fields into :
1) Indicators - Behavioral fields
2) Macro's - Business Logic fields
3) Direct Fields -Merging the DTS transformation sheet with Field sheet by finding each listed field from the sheet provided by Vinayak K.
Discussion with Satya on the same.
| Attachment | Field Mapping Definitions - 20170407.xls [ 44761 ] |
| Attachment | Data Dictionary with constraints and reference key.xlsx [ 44762 ] |
Hi Abhay Patil,
Please find attached two modified documents:
- Data Dictionary with constraints and reference key.xlsx
- [^Field Mapping Definitions - 20170407.xls]
#1 - 'Specifiying nature of fields' and #2 - 'amenable to data transformation' have been done today along with Updates in Data dictionary, however #3 - to update field description is in progress and we will be sharing that in next week (before 04/14).
Please let us know in case more details required.
Thanks & Regards,
Satya Prakash
Cc: Cindy Wibbing Umesh Kanade Samir Vijay Siddha Lalit Kumar
-
- Time Spent:
- 1h
-
Getting DTS information.
| Remaining Estimate | 0.75h [ 2700 ] | 0h [ 0 ] |
| Time Spent | 7.25h [ 26100 ] | 8.25h [ 29700 ] |
| Worklog Id | 37270 [ 37270 ] |
-
- Time Spent:
- 2h
-
Added description for fields in data dictionary
Lalit Kumar (Inactive)
logged work - 12/Apr/17 04:35 PM -
- Time Spent:
- 0.75h
-
Field description discussion with EDI team
-
- Time Spent:
- 1.5h
-
Added description for fields in data dictionary
| Time Spent | 8.25h [ 29700 ] | 12.75h [ 45900 ] |
| Worklog Id | 38182 [ 38182 ] |
Lalit Kumar (Inactive)
made changes -
| Attachment | Field Mapping Definitions - 20170414.xlsx [ 45498 ] |
Hello [Abhay Patil,
I have attached an [updated sheet | ^Field Mapping Definitions - 20170414.xlsx] of data dictionary.
This is an intermediate version and we are still working on this. On high level there 2 pending items
- Identify fields which are redundant.
- Description for remaining fields.
Regards,
Lalit Kumar
Lalit Kumar (Inactive)
added a comment - Hello [ Abhay Patil ,
I have attached an [updated sheet | ^Field Mapping Definitions - 20170414.xlsx] of data dictionary.
This is an intermediate version and we are still working on this. On high level there 2 pending items
Identify fields which are redundant.
Description for remaining fields.
Regards,
Lalit Kumar
CC : Cindy Wibbing , Umesh Kanade , Samir , Vijay Siddha , Satya -
- Time Spent:
- 2.5h
-
Updated field description
Lalit Kumar (Inactive)
logged work - 14/Apr/17 04:34 PM -
- Time Spent:
- 0.75h
-
Fields description discussion with EDI team
Lalit Kumar (Inactive)
logged work - 17/Apr/17 04:33 PM -
- Time Spent:
- 1h
-
Reviewing list of fields for deletion with dev team
Lalit Kumar (Inactive)
made changes -
| Time Spent | 12.75h [ 45900 ] | 13.75h [ 49500 ] |
| Worklog Id | 39124 [ 39124 ] |
Lalit Kumar (Inactive)
made changes -
| Time Spent | 13.75h [ 49500 ] | 14.5h [ 52200 ] |
| Worklog Id | 39125 [ 39125 ] |
Lalit Kumar (Inactive)
made changes -
| Time Spent | 14.5h [ 52200 ] | 15.25h [ 54900 ] |
| Worklog Id | 39126 [ 39126 ] |
Lalit Kumar (Inactive)
made changes -
| Time Spent | 15.25h [ 54900 ] | 17.25h [ 62100 ] |
| Worklog Id | 39144 [ 39144 ] |
Lalit Kumar (Inactive)
made changes -
| Time Spent | 17.25h [ 62100 ] | 18.75h [ 67500 ] |
| Worklog Id | 39145 [ 39145 ] |
Lalit Kumar (Inactive)
made changes -
| Worklog Id | 39145 [ 39145 ] |
Lalit Kumar (Inactive)
made changes -
| Worklog Id | 39144 [ 39144 ] |
Lalit Kumar (Inactive)
made changes -
| Time Spent | 18.75h [ 67500 ] | 21.25h [ 76500 ] |
| Worklog Id | 39147 [ 39147 ] |
Lalit Kumar (Inactive)
made changes -
| Worklog Id | 39147 [ 39147 ] |
Lalit Kumar (Inactive)
made changes -
| Time Spent | 21.25h [ 76500 ] | 23.25h [ 83700 ] |
| Worklog Id | 39190 [ 39190 ] |
| Time Spent | 23.25h [ 83700 ] | 25.25h [ 90900 ] |
| Worklog Id | 39201 [ 39201 ] |
| Time Spent | 25.25h [ 90900 ] | 31.75h [ 114300 ] |
| Worklog Id | 39396 [ 39396 ] |
| Time Spent | 31.75h [ 114300 ] | 33.75h [ 121500 ] |
| Worklog Id | 39986 [ 39986 ] |
| Time Spent | 33.75h [ 121500 ] | 41.75h [ 150300 ] |
| Worklog Id | 40072 [ 40072 ] |
-
- Time Spent:
- 4h
-
Done with all remaining export fields description.
| Time Spent | 41.75h [ 150300 ] | 45.75h [ 164700 ] |
| Worklog Id | 40416 [ 40416 ] |
| Time Spent | 45.75h [ 164700 ] | 46.75h [ 168300 ] |
| Worklog Id | 41685 [ 41685 ] |
| Time Spent | 46.75h [ 168300 ] | 47.75h [ 171900 ] |
| Worklog Id | 41713 [ 41713 ] |
Lalit Kumar (Inactive)
made changes -
| Attachment | Field Mapping Definitions - 20170428.xlsx [ 46622 ] |
Hello Abhay Patil,
I have attached updated data dictionary .
During the process we have identified 55 fields (marked in red color) which are not getting used in any feeds.
Should we delete them from data dictionary (this document)? If yes, next question to evaluate would be "Should any changes (e.g. dropping these fields) be done in software as well. Your thoughts around this.
Regards,
Lalit Kumar,Satya
Lalit Kumar (Inactive)
added a comment - Hello Abhay Patil ,
I have attached updated data dictionary .
During the process we have identified 55 fields (marked in red color) which are not getting used in any feeds.
Should we delete them from data dictionary (this document)? If yes, next question to evaluate would be "Should any changes (e.g. dropping these fields) be done in software as well. Your thoughts around this.
Regards,
Lalit Kumar , Satya Lalit Kumar (Inactive)
made changes -
| Assignee | Lalit Kumar [ lalit.kumar ] | Abhay Patil [ abhay.patil ] |
| Module | Parent values: BenAdmin(10100) | Parent values: Platform(10106) |
As for the 55 unused fields, if they show up as options in the system then they need to be safely removed from the system first.
CC Satya Cindy Wibbing Chris Ellenberger Damion M Velasquez Van Nguyen Amit Thorve
(Note: can you please delete the outdated attachments? There are five files that have names beginning with "Field Mapping". Of course one can see the dates, but it would be better if you could retain just the latest file.)
| Assignee | Abhay Patil [ abhay.patil ] | Lalit Kumar [ lalit.kumar ] |
Lalit Kumar (Inactive)
made changes -
| Attachment | Field Mapping Definitions - 20170414.xlsx [ 45498 ] |
Lalit Kumar (Inactive)
made changes -
| Attachment | Field Mapping Definitions - 20170316.xls [ 42301 ] |
Hello Satya,
As indicated above we can remove the 55 unused fields if they show up in the system.Requesting you to please create a separate ticket for removing the fields.
Also, I have removed the old files uploaded by me. Please remove the following files uploaded by you.
[^Field Mapping Definitions - 20160712.xls] and [^Field Mapping Definitions - 20170407.xls]
Regards,
Lalit Kumar
Lalit Kumar (Inactive)
added a comment - - edited Hello Satya ,
As indicated above we can remove the 55 unused fields if they show up in the system.Requesting you to please create a separate ticket for removing the fields.
Also, I have removed the old files uploaded by me. Please remove the following files uploaded by you.
[^Field Mapping Definitions - 20160712.xls] and [^Field Mapping Definitions - 20170407.xls]
Regards,
Lalit Kumar Lalit Kumar (Inactive)
made changes -
| Assignee | Lalit Kumar [ lalit.kumar ] | Satya [ ID10004 ] |
| Attachment | Field Mapping Definitions - 20160712.xls [ 41936 ] |
| Attachment | Field Mapping Definitions - 20170407.xls [ 44761 ] |
Hi Vinayak,
As discussed, can you please schedule to remove 55 mentioned fields. Link JIRA ticket to this parent ticket to keep track.
Regards,
Satya Prakash
| Assignee | Satya [ ID10004 ] | Vinayak Kulkarni [ vinayak.kulkarni ] |
| Resolution | Done [ 10000 ] | |
| Status | In Progress [ 3 ] | Resolved [ 5 ] |
Hi All,
New jira WT-9922 is created to remove unused fields from fields mapping screen.
| Status | Resolved [ 5 ] | Closed [ 6 ] |
Can we get an updated Field Mapping Definitions lists. We were looking for some of the fields that are now in Workterra but not on the 2017 list provided.
Also, from the Macros listing you provided we are trying to verify our interpretation of the macros so that we can make sure we are using them correctly on the file feeds. Can you please review and make any additional notations associated with the last column on our interpretation.
| Field Name (Harbinger) | Import / Export | File Type | Definition | Nature | Amenable to data transformation? | Current EDI Interpretation/Comment |
| Enrollment Plan Type | Export | Both | This field will fetch 'Plan Type' from the database table 'plan design.' | Direct Field From Database |
NO | Pull Plan Type on Benefit. Is this only for a single plantype selected? Will it pull for multiple plan types selected on the template. |
| Enrollment Enrolled IN | Export | Both | Do not use as an updated field 'Member Enrolled In' | Conditional Field (Macro) | YES | Pulls the enrollment for the employee record only even if defined on dependent subtemplates. Ignores the enrollment associated with any dependent records. |
| Enrollment Last Enrolled IN | Custom Field | Both | This field will populate the last planned enrolled by the employee | Conditional Field (Macro) | NO | Pulls the most recent enrollment for any member, EE, SP or Child. Used to pull information on terminated records (i.e. Health Tiers, Effective dates.) |
| Enrollment Spouse Enrolled In | Custom Field | This property indicates whether the spouse is enrolled in benefit plan or not. | Conditional Field (Macro) | YES | Only looks to the Spouse benefit. Not subtemplate dependent but plan dependent. Will look to see if the spouse is in the enrollment for plan in rule. Can be used on columnwise Employee SubTemplate to look to Spouse enrollment for plan. | |
| Enrollment Member Enrolled IN | Export | Both | This property indicates whether a member (employee, spouse, child) is enrolled in a benefit plan or not. | Conditional Field (Macro) | YES | Subtemplate dependent not plan dependent. Will look to see if the the member is in the enrollment for plan in rule engine based on the subtemplate the rule is defined on. Member Enrolled IN on Spouse Subtemplate looks to whether the Spouse is enrolled on the plan, Member Enrolled IN on Employee Subtemplate looks to whether the Employee is enrolled on the plan and Member Enrolled IN on Child Subtemplate looks to whether the Child is enrolled on the plan. |
| Enrollment Member Enrolled In Benefittype | Export | Conditional Field (Macro) | ? | |||
| Enrollment Child Enrolled IN | Custom Field | Both | This property indicates whether the child is enrolled in benefit plan or not. | Conditional Field (Macro) | YES | Only looks to the Child benefit. Not subtemplate dependent but plan dependent. Will look to see if the child is in the enrollment for plan in rule. Can be used on columnwise Employee SubTemplate to look to Child enrollment for plan. |
Thanks!
Cindy
| Link | This issue is blocked by DEV-6830 [ DEV-6830 ] |
| Transition | Time In Source Status | Execution Times |
|---|
Lalit Kumar (Inactive)
made transition
-
|
4d 23h 37m | 1 |
|
21d 11h 33m | 1 |
|
1m 43s | 1 |
|
76d 14h | 1 |
|
5d 18h 9m | 1 |
Hi Lalit,
Can you please share updated data dictionary information with Cindy?
Vinayak Kulkarni, Suraj Sokasane, Ganesh Sadawarte Hrishikesh Deshpande will help you in finding list of all columns getting used in Export/Import template.
Let me know in case any query.
Regards,
Satya Prakash