APPENDIX J

Activity File Formats

MED HOSP ACT DATA - HEADER

Field Description / Field name / Length / Length on the file / Comments
Record Type / X(6) / 1-6 - 6 / Default to ‘HEADER’
Creation date / Sy-datum / D(8) / 7-14 - 8 / MMDDYYYY
Create Time / X(6) / 15-20 - 6 / HHMMSS
As of date / D(8) / 21 - 28 - 8 / End date on the selection screen MMDDYYYY
Contact Person Name / X(40) / 29 - 68 - 40 / For Ex: “John Smith”
Telephone Number / X(10) / 69 - 78 - 10 / For Ex: “7177059295”
Email Address / X(25) / 79 - 103 - 25 / For Ex: “”
File Name / X(40) / 104 –143 – 40 / For Ex: “ZH_INTF_TO_PEBTF”
Record Count / 9(8) / 144 - 151 - 8 / For Ex: “00000230” (will include all the data records and header record)
MED HOSP ACT DATA –TRANSACTION
Field Description / Field name / Length / Length on the file / Comments
Details Indicator / X(1) / 1-1 - 1 / Default to ‘D’
Personnel Number / P0000-PERNR / X(8) / 2-9 - 8
Personnel ID Number / P0002-PERID / X(20) / 10-29 - 20 / Current SSN
Action Type / P0000-MASSN / X(2) / 30-31 - 2 / 01.16.03 – Only include actions that are from the select option.
Reason for Action / P0000- MASSG / X(2) / 32-33 - 2
Start Date of Action / P0000-BEGDA / D(8) / 34-41 - 8 / YYYYMMDD – This is the begin date of the last valid action
Org. Asgn. Effective date / P0001-BEGDA / D(8) / 42-49 - 8 / YYYYMMDD
Org. Asgn. End Date / P0001-ENDDA / D(8) / 50-57 - 8 / YYYYMMDD – If this date is less than 99991231 but is greater than the end date on the selection screen, this date should be 99991231
Position / P0001-PLANS / X(8) / 58-65 - 8
Organizational Unit / P0001-ORGEH / X(8) / 66-73 - 8
Personnel area / P0001-WERKS / X(4) / 74-77 - 4 / Right justify and zero pad the leading characters. (21 would become 0021)
Personnel subarea / P0001-BTRTL / X(4) / 78-81 - 4 / 2nd and 3rd char of the Personnel subarea is the bargaining unit
Employee Group / P0001- PERSG / X(1) / 82-82 - 1
Employee Sub group / P0001- PERSK / X(2) / 83-84 - 2
Employee Status / P0000-STAT2 / X(1) / 85-85 - 1 / 1=Inactive , 2 = Retiree, 3 = Active , 0 =
Withdrawn
Old Personnel ID Number / ZHR_T_SSN_XWALK-PERID / X(20) / 86-105 - 20 / Check ZHR_T_SSN_XWALK to see if the employee has a previous SSN, if they do, use that SSN otherwise use the current SSN
Personal data effective date / P0002-BEGDA / D(8) / 106-113 - 8 / YYYYMMDD
If this date is before 20040110, make this date 20040110.
Personal data end date / P0002-ENDDA / D(8) / 114-121 - 8 / YYYYMMDD – If this date is less than 99991231 but is greater than the end date on the selection screen, this date should be 99991231
Last Name / P0002-NACHN / X(40) / 122-161 - 40
First Name / P0002-VORNA / X(40) / 162-201 - 40
Middle Name / P0002-MIDNM / X(40) / 202-241 - 40
2nd title / P0002-TITL2 / X(15) / 242-256 - 15
Suffix / P0002- NAMZU / X(15) / 257-271 - 15
Date of Birth / P0002-GBDAT / D(8) / 272-279 - 8 / YYYYMMDD
Gender Key / P0002-GESCH / X(1) / 280-280 - 1 / "1" for male, or "2" for female.
Marital Status / P0002- FAMST / X(1) / 281-281 - 1 / ‘1’married, ‘0’single, ‘2’widow, ‘3’divorce.
Address Effective date / P0006-BEGDA / D(8) / 282-289 - 8 / YYYYMMDD Permanent Residence address (Subtype 1)
Address End date / P0006-ENDDA / D(8) / 290-297 - 8 / YYYYMMDD – If this date is less than 99991231 but is greater than the end date on the selection screen, this date should be 99991231 Permanent Residence address (Subtype 1)
House Number and Street / P0006-STRAS / X(60) / 298-357 - 60 / It is the permanent Residence address
2nd address line / P0006- LOCAT / X(40) / 358-397 - 40 / Permanent Residence address (Subtype 1)
City / P0006-ORT01 / X(40) / 398-437 - 40 / Permanent Residence address (Subtype 1)
Region (State, Province, County) / P0006-STATE / X(3) / 438-440 - 3 / Permanent Residence address (Subtype 1)
Zip Code / P0006-PSTLZ / X(10) / 441-450 - 10 / Format should be xxxxx-xxxx
If there is only 5 digit zip code in the data,
Then concatenate that with ‘-0000’ to the make it 10 char. Permanent Residence address (Subtype 1)
County code / P0006- ZZ_COUNC / X(3) / 451-453 - 3
Benefit information effective date / P0171-BEGDA / D(8) / 454-461 - 8
Benefits end date / P0171-ENDDA / D(8) / 462-469 - 8
Benefit first program grouping / P0171-BENGR / X(4) / 470-473 - 4 / Benefit Group based on county.
Benefit second program grouping / P0171-BSTAT / X(4) / 474-477 - 4 / Benefit Status
Planned working time effective date / P0007-BEGDA / D(8) / 478-485 - 8
Planned working time end date / P0007-ENDDA / D(8) / 486-493 - 8
Position Regular hours / P0007-WOSTD / 9(4)V99 / 494-499 - 6 / Multiply the value in P0007-WOSTD by 2 to make it Biweekly
Daily working hours / P0007- ARBST / 9(4)V99 / 500-505 - 6
Customer-Specific Status / P0000-STAT1 / X(1) / 506-506 - 1 / Employee position pay status
Plan Begin date / P0167-BEGDA / D(8) / 507-514 - 8 / YYYYMMDD
Plan End date / P0167-ENDDA / D(8) / 515-522 - 8 / YYYYMMDD – If this date is less than 99991231 but is greater than the end date on the selection screen, this date should be 99991231
Benefit Plan Type / P0167-PLTYP / X(4) / 523-526 - 4
Ben. Plan code / P0167-BPLAN / X(4) / 527-530 - 4
Participation Date / P0167-PARDT / D(8) / 531-538 - 8 / Date of Participation in the Plan
YYYYMMDD – Need to look at previous medical record (plan type MEDI), if it is the same plan and there is no gap between the records, use the previous participation date. This would be recursive until there is a gap in coverage or the plan is not the same or there is no previous plan.
Benefit dependent coverage code / P0167-DEPCV / X(4) / 539-542- 4 / EE only, FAM(EE+ Family), or WAIV.
Adjustment 1 Effective date / P0378-BEGDA / D(8) / 543-550 - 8
Adjustment Reason 1 / P0378-EVENT / X(20) / 551-570 - 20
Adjustment 2 Effective date / P0378-BEGDA / D(8) / 571-578 - 8 / If another record exist in the 0378 within the date range, display that under this and adjustment reason.
Adjustment Reason 2 / P0378-EVENT / X(20) / 579-598 - 20
Adjustment 3 Effective date / P0378-BEGDA / D(8) / 599-606 - 8 / If another record exist in the 0378 within the date range, display that under this and adjustment reason.
Adjustment Reason 3 / P0378-EVENT / X(20) / 607-626 - 20
Adjustment 4 Effective date / P0378-BEGDA / D(8) / 627-634 - 8 / If another record exist in the 0378 within the date range, display that under this and adjustment reason.
Adjustment Reason 4 / P0378-EVENT / X(20) / 635-654 - 20
Insured Employee Name / P0167-ZZ_INS_NAME_01 / X(40) / 655-694 - 40 / This information from here to the end of this record is related to the additional providers for the dependents, i.e., if the dependents are covered under any of the other providers.
Personnel ID Number / P0167- ZZ_ID_NUM_01 / X(20) / 695-714 - 20
Name of insurance company / P0167- ZZ_INS_COMP_01 / X(30) / 715-744 - 30
Employer's Insurance Group Code / P0167- ZZ_ER_GROUP_01 / X(20) / 745-764 – 20
Group No. / P0167- ZZ_GROUP_NUM_01 / X(10) / 765-774 – 10
Names of all the Dependents covered under the policy / P0167- ZZ_DEP_NAMES_01 / X(120) / 775-894 - 120
Insured Employee Name / P0167-ZZ_INS_NAME_02 / X(40) / 895-934 – 40
Personnel ID Number / P0167- ZZ_ID_NUM_02 / X(20) / 935-954 – 20
Name of insurance company / P0167- ZZ_INS_COMP_02 / X(30) / 955-984 – 30
Employer's Insurance Group Code / P0167- ZZ_ER_GROUP_02 / X(20) / 985-1004 – 20
Group No. / P0167- ZZ_GROUP_NUM_02 / X(10) / 1005-1014 – 10
Names of all the Dependents covered under the policy / P0167- ZZ_DEP_NAMES_02 / X(120) / 1015-1134 – 120
Insured Employee Name / P0167-ZZ_INS_NAME_03 / X(40) / 1135-1174 – 40
Personnel ID Number / P0167- ZZ_ID_NUM_03 / X(20) / 1175-1194 – 20
Name of insurance company / P0167- ZZ_INS_COMP_03 / X(30) / 1195-1224 – 30
Employer's Insurance Group Code / P0167- ZZ_ER_GROUP_03 / X(20) / 1225-1244 – 20
Group No. / P0167- ZZ_GROUP_NUM_03 / X(10) / 1245-1254 – 10
Names of all the Dependents covered under the policy / P0167- ZZ_DEP_NAMES_03 / X(120) / 1255-1374 – 120
Spouse Coverage (Yes or No) / P0167-ZZ_SPOUSE_COV / X(1) / 1375-1375 – 1 / Fill Spouse SSN if yes
Medical information Effective date / P0376-BEGDA / D(8) / 1376-1383 - 8 / YYYYMMDD
Medical information end date / P0376-ENDDA / D(8) / 1384-1391 - 8 / YYYYMMDD – If this date is less than 99991231 but is greater than the end date on the selection screen, this date should be 99991231
Physician name / P0376-DOCN1 / X(30) / 1392-1421 - 30 / This for the Medical plan
Physician Id / P0376-DOCI1 / X(20) / 1422-1441 – 20
Physician name / P0376-DOCN2 / X(30) / 1442-1471 – 30 / This is for the Dental plan
Physician ID / P0376-DOCI2 / X(20) / 1472-1491 – 20
Spouse SSN / P0106-PERID / X(20) / 1492-1511 – 20 / Value is on subtype 1 – Spouse – only fill if spouse coverage is yes
Home Phone / P0006-TELNR / X(14) / 1512-1525 – 14 / Value is on subtype 1 – Home address
Work Phone / P0006-TELNR / X(14) / 1526- 1539 – 14 / Value is on subtype 10 – Work address
Last Changed IT0001 / P0001-AEDTM / D(8) / 1540- 1547 - 8 / YYYYMMDD – Only populate if the date is greater than or equal to the previous run date and less than or equal to the current run date. OR Populate this date if the begin date of the infotype is GE to the previous run date
Last Changed IT0002 / P0002-AEDTM / D(8) / 1548- 1555 – 8 / YYYYMMDD – Only populate if the date is greater than or equal to the previous run date and less than or equal to the current run date. OR Populate this date if the begin date of the infotype is GE to the previous run date
Last Changed IT0006 / P0006-AEDTM / D(8) / 1556- 1563 – 8 / YYYYMMDD – Only populate if the date is greater than or equal to the previous run date and less than or equal to the current run date. OR Populate this date if the begin date of the infotype is GE to the previous run date.
Last Changed IT0007 / P0007-AEDTM / D(8) / 1564- 1571 – 8 / YYYYMMDD – Only populate if the date is greater than or equal to the previous run date and less than or equal to the current run date. OR Populate this date if the begin date of the infotype is GE to the previous run date
Last Changed IT0171 / P0171-AEDTM / D(8) / 1572- 1579 – 8 / YYYYMMDD – Only populate if the date is greater than or equal to the previous run date and less than or equal to the current run date. OR Populate this date if the begin date of the infotype is GE to the previous run date
Last Changed IT0167 / P0167-AEDTM / D(8) / 1580- 1587 – 8 / YYYYMMDD – Only populate if the date is greater than or equal to the previous run date and less than or equal to the current run date. OR Populate this date if the begin date of the infotype is GE to the previous run date
Last Changed IT0376 / P0376-AEDTM / D(8) / 1588- 1595 – 8 / YYYYMMDD – Only populate if the date is greater than or equal to the previous run date and less than or equal to the current run date. OR Populate this date if the begin date of the infotype is GE to the previous run date
Last Changed IT0000 / P0000-AEDTM / D(8) / 1596 – 1603 – 8 / YYYYMMDD - Only populate if the date is greater than or equal to the previous run date and less than or equal to the current run date. OR Populate this date if the begin date of the infotype is GE to the previous run date
Action Indicator / X(1) / 1604 – 1604 – 1 / This field is determined from if the employee has more than one valid action for the time period that is being run. If there is, the field should = X otherwise it should be a space.
Marital status as-of date / P0002-FAMDT / D(8) / 1605 – 1612 – 8 / YYYYMMDD
Current Patient Indicator -Medical / P0376-ZZPAT_IND1 / X(4) / 1613-1616 – 4
Type of Physician - Medical / P0376-ZZTYP_PHYS1 / X(4) / 1617-1620 – 4
Type of Physician Practice - Medical / P0376-ZZTYP_PRAC1 / X(4) / 1621-1624 – 4
Reason for Change – Medical / P0376-ZZREAS1 / X(4) / 1625-1628 – 4
Provider ID Number – Medical / P0376-ZZPROVID1 / X(4) / 1629-1632 – 4
Current Patient Indicator – Dental / P0376-ZZPAT_IND2 / X(4) / 1633-1636 – 4
Type of Physician - Dental / P0376-ZZTYP_PHYS2 / X(4) / 1637-1640 – 4
Type of Physician Practice - Dental / P0376-ZZTYP_PRAC2 / X(4) / 1641-1644 – 4
Reason for Change – Dental / P0376-ZZREAS2 / X(4) / 1645-1648 – 4
Provider ID Number – Dental / P0376-ZZPROVID2 / X(4) / 1649-1652 – 4
Current service date / IT0041-DAR01 / D(8) / 1653-1660 - 8 / YYYYMMDD. Get the Z1 date.
Benefit Eligible Control date / IT0041-DAR01 / D(8) / 1661-1668 / YYYYMMDD. Get the ZT date.
Address Effective date / P0006-BEGDA / D(8) / 1669-1676 - 8 / YYYYMMDD Mailing address (Subtype 5)
Address End date / P0006-ENDDA / D(8) / 1677-1684 - 8 / YYYYMMDD – If this date is less than 99991231 but is greater than the end date on the selection screen, this date should be 99991231 Mailing address (Subtype 5)
House Number and Street / P0006-STRAS / X(60) / 1685-1744 - 60 / It is the Mailing address
2nd address line / P0006- LOCAT / X(40) / 1745-1784 - 40 / Mailing address (Subtype 5)
City / P0006-ORT01 / X(40) / 1785-1824 - 40 / Mailing address (Subtype 5)
Region (State, Province, County) / P0006-STATE / X(3) / 1825-1827 - 3 / Mailing address (Subtype 5)
Zip Code / P0006-PSTLZ / X(10) / 1828-1837 - 10 / Format should be xxxxx-xxxx
If there is only 5 digit zip code in the data,
Then concatenate that with ‘-0000’ to the make it 10 char. Mailing address (Subtype 5)
MED HOSP ACT DATA-TRANSACTION2
Field Description / Field name / Length / Length on the file / Comments
Dependents Indicator / X(1) / 1-1 - 1 / Default to ‘B’
Dependent effective date / P0167-BEGDA / D(8) / 2-9 - 8 / YYYYMMDD - Need to look at previous P0167 record for the plan type, if it is the same plan and there is no gap between the records and the same dependent is being covered. This would be recursive until there is a gap in coverage or the plan is not the same or there is no previous plan or the dependent is not covered by the plan. Use the BEGDA of the first P0167 record where the dependent is covered.
Dependent end date / P0167-ENDDA / D(8) / 10-17 - 8 / YYYYMMDD – If this date is less than 99991231 but is greater than the end date on the selection screen, this date should be 99991231
Dependent First name / P0021- FAVOR / X(40) / 18-57 - 40
Dependent Last name / P0021- FANAM / X(40) / 58-97 - 40 / If P0021-FNMZU is not initial, concatenate P0021-FANAM with P0021-FNMZU with a space between the fields into the last name field.
Dependent Middle int / P0021-FINIT / X(1) / 98-98 - 1
Dependent Type / P0021- FAMSA / X(4) / 99-102 - 4 / "1" for spouse; "2" for child; or "6" for stepchild, or “7” for Emergency contact or “9” for other or “10” Divorced Spouse.
Dependent Date of Birth / P0021- FGBDT / D(8) / 103-110 - 8 / YYYYMMDD
Dependent Gender / P0021- FASEX / X(1) / 111-111 - 1 / "1" for male, or "2" for female.
Dependent Id number / P0106-PERID / X(20) / 112-131 - 20
Dependent House number and street / P0106- STRAS / X(30) / 132-161 - 30
Dependent 2nd address line / P0106- LOCAT / X(30) / 162-191 - 30
Dependent city / P0106- ORT01 / X(25) / 192-216 - 25
Dependent state / P0106- STATE / X(3) / 217-219 - 3
Dependent Zip code / P0106- PSTLZ / X(10) / 220-229 - 10 / Format should be xxxxx-xxxx
If there is only 5 digit zip code in the data,
Then concatenate that with ‘-0000’ to the make it 10 char.
Benefit physician name / P0106-DOCNM / X(30) / 230-259 - 30 / This is for the Medical plan
Benefit physician ID / P0106-DOCID / X(20) / 260-279 – 20
Benefit physician name / P0106-DOCN2 / X(30) / 280-309 – 30 / This is for the Dental plan
Benefit physician ID / P0106-DOCI2 / X(20) / 310-329 – 20
Reason code / P0167-ZZ_RESCD / X(2) / 330-331 – 2 / This field should only be populated when the dependent is being dropped from coverage or added to coverage - Fill reason effective date if there is a value in this field
Reason Description / ZHR_T_DPTRESCD-RDESC / X(40) / 332-371 – 40 / This field should only be populated when the dependent is being dropped from coverage or added to coverage - Fill reason effective date if there is a value in this field
Reason Effective date / P0167-BEGDA / D(8) / 372-379 – 8 / YYYYMMDD – only fill if the reason code has a value
Disability Indicator / P0106-DISAB / X(1) / 380-380 – 1
Student Indicator / P0106-BEN03 / X(1) / 381-381 – 1
Last Changed IT0021 / P0021-AEDTM / D(8) / 382- 389 – 8 / YYYYMMDD – Only populate if the date is greater than or equal to the previous run date and less than or equal to the current run date. OR Populate this date if the begin date of the infotype is GE to the previous run date
Last Changed IT0106 / P0106-AEDTM / D(8) / 390- 397 – 8 / YYYYMMDD – Only populate if the date is greater than or equal to the previous run date and less than or equal to the current run date. OR Populate this date if the begin date of the infotype is GE to the previous run date
Employee SSN / P0002-PERID / X(20) / 398 – 417 - 20
Current Patient Indicator -Medical / P0021-ZZPAT_IND1 / X(4) / 418-421 – 4
Type of Physician - Medical / P0021-ZZTYP_PHYS1 / X(4) / 422-425 – 4
Type of Physician Practice - Medical / P0021-ZZTYP_PRAC1 / X(4) / 426-429 – 4
Reason for Change – Medical / P0021-ZZREAS1 / X(4) / 430-433 – 4
Provider ID Number – Medical / P0021-ZZPROVID1 / X(4) / 434-437 – 4
Current Patient Indicator – Dental / P0021-ZZPAT_IND2 / X(4) / 438-441 – 4
Type of Physician - Dental / P0021-ZZTYP_PHYS2 / X(4) / 442-445– 4
Type of Physician Practice - Dental / P0021-ZZTYP_PRAC2 / X(4) / 446-449 – 4
Reason for Change – Dental / P0021-ZZREAS2 / X(4) / 450-453 – 4
Provider ID Number – Dental / P0021-ZZPROVID2 / X(4) / 454-457 – 4
New Born indicator / P0021-ZZNEWBORNIND / X(1) / 458-458-1