Health PMI Build Guide
How each Health PMI ODS table is filled from GMS and ACPM. Every table is shown in three parts: what the source file sends, how the ODS builds each row, and what the finished table holds.
Two example policies run through every table: 001BZB, a family with one primary member and a partner, and 520417, a group policy with two primary members, each with their own dependants.
All values are made-up examples. IDs are shown as short labels (C1, R2…); in the ODS each one is a generated unique identifier. Table and column names are illustrative: the LDM name in capitals with underscores. Logic as in LDD v0.8.
1
What the source sendsThe file as it arrives. Blue cells are the values used to build the key.
2
How the ODS builds each rowThe steps in order, the key that makes each row unique, and what the row links to.
3
What the table holdsThe finished rows. C1 is this table’s own ID; C1 is a link to another table; — is empty; TBC has no source yet.
How the tables connect
Arrows point from a table to the tables that link to it. “1 : 0 or many” means one row on the first table can have no rows, one row or many rows on the second; “1 : 0 or 1” means at most one.
PMI_POLICYsame ID as PMI_CONTRACTPMI_SCHEMEsame ID as PMI_CONTRACTPMI_CONTRACTfrom GMS_POLICYCATEGORYfrom GMS_MEMCOVCONTRACT_ROLE_PHONE_NUMBERfrom GMS_MEMBFILECONTRACT_ROLE_EMAIL_ADDRESSfrom GMS_BDPRCNAK1_MEMBERPMI_CONTRACT_ROLEfrom GMS_MEMBFILE + GMS_POLICYMEMBER_COVERfrom GMS_MEMCOVADDRESSfrom GMS_BDPRCNAK1_MEMBERCONTRACT_ROLE_ADDRESSfrom GMS_BDPRCNAK1_MEMBERCLAIMfrom ACPM_THI_CLAIM1 : 0 or 11 : 0 or 11 : 0 or many1 : 0 or many1 : 0 or many1 : 0 or many1 : 0 or many1 : 0 or many1 : 0 or many1 : many1 : 0 or many1 : 0 or manya dependant links to its primary member
Scroll sideways to see the whole map.
PMI Contract
One row for each policy (case) in GMS. A renewal does not create a new row.
1What the source sends
GMS_POLICY
The policy file: one row per policy.
| CASE_NO | STATUS | EFFECTIVE_DATE | STATUS_DT |
|---|---|---|---|
| 001BZB | AC | 20061201 | 20061201 |
| 520417 | AC | 20190301 | 20190301 |
2How the ODS builds each row
- Take each policy in
GMS_POLICY. Here there are two: 001BZB and 520417. - Create the Contract ID from ‘GMS’ +
CASE_NO: 001BZB gives C1, 520417 gives C2. This is the only place a Contract ID is created. - Copy
CASE_NOas the Policy Identifier, kept as text so leading zeros stay. - Set Policy Admin System to ‘GMS’.
- Contract Type: Scheme if
CASE_NOstarts with 95, 96 or 98; otherwise Policy. Both examples are policies. - Copy
STATUSas the Contract Status andEFFECTIVE_DATEas the Effective Date. - End Date: if
STATUSis TM (terminated), useSTATUS_DT; otherwise leave it empty. Both examples are active (AC), so End Date is empty.
Key
Policy Admin System + Policy IdentifierExample: 'GMS' + 520417
Links
- Top of the model: it links to nothing above it.
- A contract can have none, one or many categories, and none, one or many roles.
- Each contract is either one PMI Policy or one PMI Scheme.
3What the table holds
PMI_CONTRACT
| CONTRACT_IDPK | POLICY_IDENTIFIER | POLICY_ADMIN_SYSTEM | CONTRACT_TYPE | CONTRACT_STATUS | EFFECTIVE_DATE | END_DATE |
|---|---|---|---|---|---|---|
| C1 | 001BZB | GMS | Policy | AC | 2006-12-01 | — |
| C2 | 520417 | GMS | Policy | AC | 2019-03-01 | — |
Still open: Whether GMS end dates are the last day covered or the first day not covered (Q-43).
PMI Policy and PMI Scheme
Each contract is either a policy or a scheme, never both. These tables reuse the contract’s ID.
1What the source sends
No separate file. Built from the PMI Contract rows above, which come from GMS_POLICY.
2How the ODS builds each row
- Each PMI Contract with Contract Type = Policy gets a PMI Policy row with the same Contract ID: C1 and C2.
- Each PMI Contract with Contract Type = Scheme gets a PMI Scheme row with the same Contract ID. Neither example is a scheme; a contract whose number starts with 95, 96 or 98 (for example 950417) would go here.
- Scheme Name: no source found yet, so it stays empty.
Key
Contract ID: the same ID as PMI Contract; no new ID is madeExample: C1 (001BZB), C2 (520417)
Links
- Each row is exactly one PMI Contract.
- A contract has one row in PMI Policy or one row in PMI Scheme, never both.
3What the table holds
PMI_POLICY
| CONTRACT_IDPK, FK → PMI_CONTRACT |
|---|
| C1 |
| C2 |
PMI_SCHEME
| CONTRACT_IDPK, FK → PMI_CONTRACT | SCHEME_NAME |
|---|---|
| No rows in this example: neither contract is a scheme. | |
Still open: Where the scheme name is held (Q-07).
Category
One row for each plan code + plan code version used on a contract.
1What the source sends
GMS_MEMCOV
The member cover file. A member’s cover is a period during which that member holds one plan code + version. This file has one row for each cover period: which policy and member, which plan code and version, and the start and end dates. It is the only GMS file where the policy, plan code and version sit together.
| MEMBPCD_POLICY_NO | MEMBPCD_MEMBER_NO | MEMBPCD_PLAN_CODE | MEMBPCD_PLAN_CODE_VERSION | MEMBPCD_START_DATE | MEMBPCD_END_DATE |
|---|---|---|---|---|---|
| 001BZB | 000001100 | 74A | 01 | 20240122 | 20250122 |
| 001BZB | 000001100 | 86K | 03 | 20250122 | 59990101 |
| 001BZB | 000001200 | 86K | 03 | 20250122 | 59990101 |
| 520417 | 000001100 | 91B | 02 | 20190301 | 59990101 |
| 520417 | 000001200 | 91B | 02 | 20190301 | 59990101 |
| 520417 | 000002100 | 93C | 01 | 20210601 | 59990101 |
| 520417 | 000002200 | 93C | 01 | 20210601 | 59990101 |
| 520417 | 000002300 | 93C | 01 | 20220915 | 59990101 |
2How the ODS builds each row
- Read the cover rows in
GMS_MEMCOV. Each row is one member holding one plan code + version for a period. - Keep one row for each distinct policy + plan code + version. Here 001BZB has 74A / 01 and 86K / 03; 520417 has 91B / 02 and 93C / 01.
- Find the contract using the policy number on the cover row: ‘GMS’ +
MEMBPCD_POLICY_NOfinds the Contract ID created in step 1, C1 or C2. - Create the Category ID from ‘GMS’ + policy + plan code + version: K1 for 001BZB 86K / 03, K2 for 001BZB 74A / 01, K3 for 520417 91B / 02, K4 for 520417 93C / 01.
Key
Contract ID + Plan Code + Plan Code VersionExample: 'GMS' + 520417 + 93C + 01
Links
- Belongs to one PMI Contract. A contract can have none, one or many categories.
- A category can be linked to none, one or many roles. K2 has none today: nobody holds 74A / 01 any more.
3What the table holds
CATEGORY
| CATEGORY_IDPK | CONTRACT_IDPK, FK → PMI_CONTRACT | PLAN_CODE | PLAN_CODE_VERSION |
|---|---|---|---|
| K1 | C1 | 86K | 03 |
| K2 | C1 | 74A | 01 |
| K3 | C2 | 91B | 02 |
| K4 | C2 | 93C | 01 |
Still open: A plan code set up on a policy but used by no member does not appear (Q-32).
PMI Contract Role
One row for each member on a contract (for as long as they keep one member number), plus one row for each contract’s Policyholder.
1What the source sends
GMS_MEMBFILE
The member file: one row per member record. Each row has its own copy of the member’s policy number, in MIS_MEMBER_CASE_NO.
| MIS_MEMBER_CASE_NO | MIS_MEMBER_NO | MIS_MEMBER_STATUS | MIS_MEMBER_EFF_DT | MIS_MEMBER_CANC_DT |
|---|---|---|---|---|
| 001BZB | 000001100 | AC | 20240122 | 99999999 |
| 001BZB | 000001200 | AC | 20250122 | 99999999 |
| 520417 | 000001100 | AC | 20190301 | 99999999 |
| 520417 | 000001200 | AC | 20190301 | 99999999 |
| 520417 | 000002100 | AC | 20210601 | 99999999 |
| 520417 | 000002200 | AC | 20210601 | 99999999 |
| 520417 | 000002300 | AC | 20220915 | 99999999 |
How to read a member number
9 digits: the first 6 are the family (one family per primary member), the last 3 the relationship. GMS often shows them without the leading zeros.
| MIS_MEMBER_NO | Often shown as | Family | Relationship | Meaning |
|---|---|---|---|---|
| 000001100 | 1100 | 000001 | 100 | First family: the primary member |
| 000001200 | 1200 | 000001 | 200 | First family: the partner |
| 000002100 | 2100 | 000002 | 100 | Second family: another primary member (on a group policy, another employee) |
| 000002200 | 2200 | 000002 | 200 | Second family: the partner |
| 000002300 | 2300 | 000002 | 300 | Second family: a child |
Relationship codes: 1xx primary member, 2xx partner, 3xx child. Partners and children are dependants of the primary member in their family.
GMS_POLICY
Used only for the Policyholder rows.
| CASE_NO |
|---|
| 001BZB |
| 520417 |
The members’ covers in GMS_MEMCOV (Category section) are used to find each member’s current Category.
2How the ODS builds each row
- Take each member record in
GMS_MEMBFILE. - Find the member’s contract. The member file has its own copy of the policy number,
MIS_MEMBER_CASE_NO. Put ‘GMS’ in front of it and look it up in PMI Contract, which was built fromGMS_POLICYin step 1: ‘GMS’ + 001BZB finds C1; ‘GMS’ + 520417 finds C2. Nothing about the members is read from GMS_POLICY. - Create the PMI Contract Role ID from ‘GMS’ +
MIS_MEMBER_CASE_NO+MIS_MEMBER_NO, one per member record: R1, R2, R4, R5, R6, R7, R8. Member number 000001100 is on both policies; the policy number keeps them apart (R1 and R4). - Work out the role type from the last 3 digits of the member number: 1xx Primary Member, 2xx Partner, 3xx Child.
- Copy
MIS_MEMBER_NOas the Membership Number andMIS_MEMBER_STATUSas the Membership Status. - Start Date =
MIS_MEMBER_EFF_DT. End Date =MIS_MEMBER_CANC_DT; 99999999 means no end date, so it stays empty. - Link each dependant to their primary member: same policy, same family (first 6 digits), relationship 100. On 520417, 000002300 (child) and 000002200 (partner) both point to 000002100, R6.
- Link to the Category of the member’s cover in force today, from
GMS_MEMCOV. On 520417 the second family is on 93C / 01, so K4. - Give each member record an interim Party ID, P1 to P7, until the Party domain exists.
- Add one Policyholder row per contract from
GMS_POLICY: R0 for 001BZB and R3 for 520417. Administrator rows follow once a source is found.
Key
Members: Contract ID + Member Number. Policyholder or Administrator: Contract ID + Role TypeExample: 'GMS' + 520417 + 000002300
Links
- Belongs to one PMI Contract. A contract can have none, one or many roles.
- Can point to one Category (the cover in force today). A Policyholder has none.
- A dependant points to its primary member’s role, in the same table.
- Has one interim Party ID (members only, for now).
3What the table holds
PMI_CONTRACT_ROLE
| PMI_CONTRACT_ROLE_IDPK | CONTRACT_IDFK → PMI_CONTRACT | CATEGORY_IDFK → CATEGORY | PRIMARY_PMI_CONTRACT_ROLE_IDFK → PMI_CONTRACT_ROLE | PARTY_IDFK → PARTY (interim) | PMI_CONTRACT_ROLE_TYPE | MEMBERSHIP_NUMBER | MEMBERSHIP_STATUS | START_DATE | END_DATE |
|---|---|---|---|---|---|---|---|---|---|
| R0 | C1 | — | — | TBC | Policyholder | — | — | TBC | TBC |
| R1 | C1 | K1 | — | P1 | Primary Member | 000001100 | AC | 2024-01-22 | — |
| R2 | C1 | K1 | R1 | P2 | Partner | 000001200 | AC | 2025-01-22 | — |
| R3 | C2 | — | — | TBC | Policyholder | — | — | TBC | TBC |
| R4 | C2 | K3 | — | P3 | Primary Member | 000001100 | AC | 2019-03-01 | — |
| R5 | C2 | K3 | R4 | P4 | Partner | 000001200 | AC | 2019-03-01 | — |
| R6 | C2 | K4 | — | P5 | Primary Member | 000002100 | AC | 2021-06-01 | — |
| R7 | C2 | K4 | R6 | P6 | Partner | 000002200 | AC | 2021-06-01 | — |
| R8 | C2 | K4 | R6 | P7 | Child | 000002300 | AC | 2022-09-15 | — |
Still open: Where the Administrator and the Policyholder’s dates come from (Q-05); a policy + member number held under two billing units (Q-25); the meaning of codes 201-206 and 301-315 (Q-06).
Member Cover
One row for each cover period of a member: plan code + version + start date.
1What the source sends
GMS_MEMCOV
Same member cover rows as in the Category section: one row per cover period.
| MEMBPCD_POLICY_NO | MEMBPCD_MEMBER_NO | MEMBPCD_PLAN_CODE | MEMBPCD_PLAN_CODE_VERSION | MEMBPCD_START_DATE | MEMBPCD_END_DATE |
|---|---|---|---|---|---|
| 001BZB | 000001100 | 74A | 01 | 20240122 | 20250122 |
| 001BZB | 000001100 | 86K | 03 | 20250122 | 59990101 |
| 001BZB | 000001200 | 86K | 03 | 20250122 | 59990101 |
| 520417 | 000001100 | 91B | 02 | 20190301 | 59990101 |
| 520417 | 000001200 | 91B | 02 | 20190301 | 59990101 |
| 520417 | 000002100 | 93C | 01 | 20210601 | 59990101 |
| 520417 | 000002200 | 93C | 01 | 20210601 | 59990101 |
| 520417 | 000002300 | 93C | 01 | 20220915 | 59990101 |
GMS_MEMBER
Used for the underwriting type, held once per member record.
| CASE_NO | MEMBER_NO | MEMB_UW_TYPE |
|---|---|---|
| 001BZB | 000001100 | NMORI |
| 001BZB | 000001200 | NMORI |
| 520417 | 000001100 | MHD |
| 520417 | 000001200 | MHD |
| 520417 | 000002100 | MHD |
| 520417 | 000002200 | MHD |
| 520417 | 000002300 | MHD |
2How the ODS builds each row
- Each
GMS_MEMCOVrow is one cover period: one member holding one plan code + version from a start date. - Find the member’s role using the cover row’s own policy and member number: ‘GMS’ +
MEMBPCD_POLICY_NO+MEMBPCD_MEMBER_NOfinds the role created in step 4. For example 520417 + 000002300 finds R8. - Create the Member Cover ID from the role + plan code + version + start date: M0, M1, M2, M3, M4, M5, M6, M7.
- Copy plan code, version and start date. End Date =
MEMBPCD_END_DATE; 59990101 means no end date, so it stays empty. - Underwriting Type comes from
GMS_MEMBERfor the same policy + member number, so every cover of that member shows the same value.
Key
PMI Contract Role ID + Plan Code + Plan Code Version + Start DateExample: R8 + 93C + 01 + 2022-09-15
Links
- Belongs to one member’s role. A role can have none, one or many cover periods; a Policyholder has none.
- The end date is not part of the key, because it can change.
- A cover can be pointed to by none, one or many claims.
3What the table holds
MEMBER_COVER
| MEMBER_COVER_IDPK | PMI_CONTRACT_ROLE_IDPK, FK → PMI_CONTRACT_ROLE | PLAN_CODE | PLAN_CODE_VERSION | START_DATE | END_DATE | UNDERWRITING_TYPE |
|---|---|---|---|---|---|---|
| M0 | R1 | 74A | 01 | 2024-01-22 | 2025-01-22 | NMORI |
| M1 | R1 | 86K | 03 | 2025-01-22 | — | NMORI |
| M2 | R2 | 86K | 03 | 2025-01-22 | — | NMORI |
| M3 | R4 | 91B | 02 | 2019-03-01 | — | MHD |
| M4 | R5 | 91B | 02 | 2019-03-01 | — | MHD |
| M5 | R6 | 93C | 01 | 2021-06-01 | — | MHD |
| M6 | R7 | 93C | 01 | 2021-06-01 | — | MHD |
| M7 | R8 | 93C | 01 | 2022-09-15 | — | MHD |
Still open: A member holding two covers at the same time (Q-37); whether Underwriting Type belongs on the role instead (Q-39).
Address
One row for each distinct address. Everyone at the same address shares one row.
1What the source sends
GMS_BDPRCNAK1_MEMBER
The member name-and-address file: one row per member record, with the member’s address and email.
| ADDR_MEM_RCD_CASE | ADDR_MEM_RCD_NO | ADDR_ADDRESS2 | ADDR_ADDRESS3 | ADDR_ADDRESS4 | ADDR_ADDRESS5 | ADDR_ZIP |
|---|---|---|---|---|---|---|
| 001BZB | 000001100 | 1 SAMPLE ROAD. | Testville | — | — | AB1 2CD |
| 001BZB | 000001200 | 1 Sample Road | TESTVILLE | — | — | ab12cd |
| 520417 | 000001100 | 2 Example Street | Demoton | — | — | XY2 3EF |
| 520417 | 000001200 | 2 EXAMPLE ST | Demoton | — | — | XY2 3EF |
| 520417 | 000002100 | 3 Test Lane | Demoton | — | — | XY4 5GH |
| 520417 | 000002200 | 3 Test Lane | DEMOTON | — | — | xy45gh |
| 520417 | 000002300 | 3 Test Lane | Demoton | — | — | XY4 5GH |
2How the ODS builds each row
- Take
ADDR_ADDRESS2toADDR_ADDRESS5as address lines 1 to 4, andADDR_ZIPas the postcode. - Clean them into one standard form: capitals, punctuation removed, standard abbreviations (ROAD becomes RD, STREET becomes ST, LANE becomes LN), and the postcode with one space before the last three characters.
- Create the Address ID from the cleaned lines + postcode. The seven member rows clean to three addresses: A1, A2, A3. Everyone at the same address shares one Address ID.
Key
Address Line 1 to 4 + Postcode, after cleaningExample: 3 TEST LN + DEMOTON + XY4 5GH
Links
- No ‘GMS’ in the key: an address is the same whichever system sends it.
- An address can be used by one or many roles, through Contract Role Address.
3What the table holds
ADDRESS
| ADDRESS_IDPK | ADDRESS_LINE_1 | ADDRESS_LINE_2 | ADDRESS_LINE_3 | ADDRESS_LINE_4 | POSTCODE | ADDRESS_TYPE |
|---|---|---|---|---|---|---|
| A1 | 1 SAMPLE RD | TESTVILLE | — | — | AB1 2CD | — |
| A2 | 2 EXAMPLE ST | DEMOTON | — | — | XY2 3EF | — |
| A3 | 3 TEST LN | DEMOTON | — | — | XY4 5GH | — |
Still open: What ADDR_CORRES1 and ADDR_CORRES2 hold (Q-11); flags for wrong or third-party addresses (Q-51); Address Type is proposed for removal from this table (Q-12).
Contract Role Address
Links a member’s role to an address. One row for each role and address.
1What the source sends
GMS_BDPRCNAK1_MEMBER
Same rows as in the Address section. Each row has its own copy of the member’s policy number and member number.
| ADDR_MEM_RCD_CASE | ADDR_MEM_RCD_NO | ADDR_FM_DT |
|---|---|---|
| 001BZB | 000001100 | 20240122 |
| 001BZB | 000001200 | 20250122 |
| 520417 | 000001100 | 20190301 |
| 520417 | 000001200 | 20190301 |
| 520417 | 000002100 | 20210601 |
| 520417 | 000002200 | 20210601 |
| 520417 | 000002300 | 20220915 |
2How the ODS builds each row
- Find the member’s role. The address file has its own copy of the policy number and member number (
ADDR_MEM_RCD_CASE,ADDR_MEM_RCD_NO). Match them to the member file (MIS_MEMBER_CASE_NO,MIS_MEMBER_NO) to find the role created in step 4. For example 520417 + 000002300 finds R8. - Find the Address ID of the row’s cleaned address, for example A3.
- Add one row per role + address. No new ID is made: the two IDs together are the key.
- Address Type = ‘Provided Address’.
- Dates: no confirmed source yet.
ADDR_FM_DTis the candidate start date.
Key
PMI Contract Role ID + Address IDExample: R8 + A3
Links
- Points to one role and one address.
- A role can have none, one or many addresses; an address can be shared by many roles.
3What the table holds
CONTRACT_ROLE_ADDRESS
| PMI_CONTRACT_ROLE_IDPK, FK → PMI_CONTRACT_ROLE | ADDRESS_IDPK, FK → ADDRESS | ADDRESS_TYPE | START_DATE | END_DATE |
|---|---|---|---|---|
| R1 | A1 | Provided Address | TBC | TBC |
| R2 | A1 | Provided Address | TBC | TBC |
| R4 | A2 | Provided Address | TBC | TBC |
| R5 | A2 | Provided Address | TBC | TBC |
| R6 | A3 | Provided Address | TBC | TBC |
| R7 | A3 | Provided Address | TBC | TBC |
| R8 | A3 | Provided Address | TBC | TBC |
Still open: Start and end dates (Q-13); the same role using one address for two purposes (Q-31).
Contract Role Phone Number
One row for each phone number held for a member’s role.
1What the source sends
GMS_MEMBFILE
Same member records as in the PMI Contract Role section.
| MIS_MEMBER_CASE_NO | MIS_MEMBER_NO | MIS_TELEPHONE |
|---|---|---|
| 001BZB | 000001100 | — |
| 001BZB | 000001200 | 07700 900123 |
| 520417 | 000001100 | — |
| 520417 | 000001200 | — |
| 520417 | 000002100 | 07700 900456 |
| 520417 | 000002200 | — |
| 520417 | 000002300 | — |
2How the ODS builds each row
- Only rows with a phone number are used. Here there are two: 001BZB 000001200 and 520417 000002100.
- Find the role using the member file’s own policy and member number: R2 and R6.
- Clean each number to digits only, for example 07700 900123 becomes 07700900123.
- Create the Phone Number ID from the role + the number: T1, T2. A different number later gives a new row.
- Dates: no source yet.
Key
PMI Contract Role ID + Phone NumberExample: R2 + 07700900123
Links
- Belongs to one role. A role can have none, one or many numbers over time.
3What the table holds
CONTRACT_ROLE_PHONE_NUMBER
| CONTRACT_ROLE_PHONE_NUMBER_IDPK | PMI_CONTRACT_ROLE_IDPK, FK → PMI_CONTRACT_ROLE | PHONE_NUMBER | START_DATE | END_DATE |
|---|---|---|---|---|
| T1 | R2 | 07700900123 | TBC | TBC |
| T2 | R6 | 07700900456 | TBC | TBC |
Still open: Confirm MIS_TELEPHONE as the phone source (Q-11); start and end dates (Q-13).
Contract Role Email Address
One row for each email address held for a member’s role.
1What the source sends
GMS_BDPRCNAK1_MEMBER
Same rows as in the Address section.
| ADDR_MEM_RCD_CASE | ADDR_MEM_RCD_NO | ADDR_EMAIL |
|---|---|---|
| 001BZB | 000001100 | — |
| 001BZB | 000001200 | Partner@Example.com |
| 520417 | 000001100 | Employee.One@Example.com |
| 520417 | 000001200 | — |
| 520417 | 000002100 | — |
| 520417 | 000002200 | — |
| 520417 | 000002300 | — |
2How the ODS builds each row
- Only rows with an email are used. Here there are two: 001BZB 000001200 and 520417 000001100.
- Match each row to its member record in
GMS_MEMBFILE(same policy and member number), as for addresses: R2 and R4. - Clean each email: lower case, spaces trimmed.
- Create the Email Address ID from the role + the email: E1, E2. A different email later gives a new row.
- Dates:
ADDR_FM_DTis the candidate start date; there is no end date source.
Key
PMI Contract Role ID + Email AddressExample: R2 + partner@example.com
Links
- Belongs to one role. A role can have none, one or many emails over time.
3What the table holds
CONTRACT_ROLE_EMAIL_ADDRESS
| CONTRACT_ROLE_EMAIL_ADDRESS_IDPK | PMI_CONTRACT_ROLE_IDPK, FK → PMI_CONTRACT_ROLE | EMAIL_ADDRESS | START_DATE | END_DATE |
|---|---|---|---|---|
| E1 | R2 | partner@example.com | TBC | TBC |
| E2 | R4 | employee.one@example.com | TBC | TBC |
Still open: Start and end dates (Q-13).
Claim
One row for each ACPM claim. A claim keeps its number and year for its whole life.
1What the source sends
ACPM_THI_CLAIM
The ACPM claim file: one row per claim.
| CLAIM_NO | CLAIM_NO_YR | POLICY_NO | MEMBER_NO | CLAIM_ENTER_DT |
|---|---|---|---|---|
| 123456 | 25 | 001BZB | 000001200 | 14/03/2025 10:22:05 |
| 234567 | 26 | 520417 | 000002300 | 05/02/2026 09:15:40 |
2How the ODS builds each row
- One row per claim number + claim year.
- Create the Claim ID from ‘ACPM’ + claim number + claim year: CL1, CL2.
- Find the claimant’s role using the claim’s own policy and member number, ‘GMS’ +
POLICY_NO+MEMBER_NO(padded to 9 digits if shorter): CL1 finds R2; CL2 finds R8. - Find that role’s cover in force on the day the claim was registered (the date part of
CLAIM_ENTER_DT): CL1 on 2025-03-14 finds M2; CL2 on 2026-02-05 finds M7. - Claim System = ‘ACPM’.
Key
Claim System + Claim Number + Claim YearExample: 'ACPM' + 234567 + 26
Links
- Belongs to one member’s role. A role can have none, one or many claims.
- Points to the cover in force when the claim was registered.
3What the table holds
CLAIM
| CLAIM_IDPK | CLAIM_SYSTEM | CLAIM_NUMBER | CLAIM_YEAR | PMI_CONTRACT_ROLE_IDFK → PMI_CONTRACT_ROLE | MEMBER_COVER_IDFK → MEMBER_COVER |
|---|---|---|---|---|---|
| CL1 | ACPM | 123456 | 25 | R2 | M2 |
| CL2 | ACPM | 234567 | 26 | R8 | M7 |
Still open: The claim year format, 2 or 4 digits (Q-15); which cover a claim should point to when it continues under a new plan code (Q-47); registration date or treatment date (Q-38).
