Table name: PAYER_PLAN_PERIOD

Key conventions

  • For each person, create a record associating the person to the duration they were on a specific type of benefit plan (defined by DATATYP, and PLANTYP) per the ENROLLMENT_DETAIL table. Payer plan entries are consolidated by combining records that indicate continuous enrollment over a period for a specific payer plan. Consolidation is done through the following steps:
    • Define the PAYER_SOURCE_VALUE (see logic in table below).
    • Only use records where the person has prescription benefits (RX=1).
    • ENROLLMENT_DETAIL records for each person are sorted in ascending order by DTSTART, DTEND.
    • Periods of continuous enrollment are consolidated by combining monthly records as long as the time between the end of one enrollment period and the start of the next is 32 days or less (<=32) and PAYER_SOURCE_VALUE have not changed.
    • The gap between plan periods needs to be 32 days or less (<=32).
    • Use both DATATYP (i.e. DATATYP=2, 4 for identifying capitated plan) and PLANTYP (e.g. HMO, PPO, etc.) to define PAYER_SOURCE_VALUE.
    • Switch of type of plan enrolled may cause the overlap of enroll periods on two plans:


DATATYP DTSTART DTEND ENROLID PLANTYP
1 10/1/2006 10/31/2006 9687901 6
1 10/5/2006 10/31/2006 9687901 5


Sort ENROLLMENT_DETAIL table by ENROLID, DTSTART and DTEND, and if there is some overlap between two coverage periods, always truncate the first one and discard the first record if DTEND < DSTART after truncation. The example above can be truncated as below:

DATATYP DTSTART DTEND ENROLID PLANTYP
1 10/1/2006 10/4/2006 9687901 6
1 10/5/2006 10/31/2006 9687901 5
  • Payer plans may break out differently than observation periods, the amount of observation time and payer plan period time may not match for each person.
  • Remove duplicate records before assigning PAYER_PLAN_PERIOD_ID.



Reading from ENROLLMENT_DETAIL

Destination Field Source field Logic Comment field
PAYER_PLAN_PERIOD_ID - System generated. -
PERSON_ID ENROLID - -
CONTRACT_PERSON_ID - NULL -
PAYER_PLAN_PERIOD_START_DATE DTSTART Minimum start date of a contiguous enrollment in a plan. -
PAYER_PLAN_PERIOD_END_DATE DTEND Maximum end date of a contiguous enrollment in a plan. -
PAYER_CONCEPT_ID - 0 -
PAYER_SOURCE_VALUE PLANTYP
DATATYP
Use PLANTYP string:
1 - Basic/major medical
2 - Comprehensive
3 - EPO
4 - HMO
5 - POS
6 - PPO
7 - POS with capitation
8 - CDHP
9 - HDHP

If DATATYP in (2,4) then replace [1] with “C”; else replace [1] with “N”;

If database is CCAE replace [2] replace with “Commercial”.
If database is MDCR replace [2] with “Medicare”.
NULL: Unknown
1: [1] [2] Basic/Major Medical
2: [1] [2] Comprehensive
3: [1] [2] EPO
4: [1] [2] HMO
5: [1] [2] POS
6: [1] [2] PPO
7: [1] [2] POS with Capitation
8: [1] [2] CDHP
9: [1] [2] HDHP
PAYER_SOURCE_CONCEPT_ID - 0 -
PLAN_CONCEPT_ID - 0 -
PLAN_SOURCE_VALUE - NULL -
PLAN_SOURCE_CONCEPT_ID - 0 -
CONTRACT_CONCEPT_ID - 0 -
CONTRACT_SOURCE_VALUE - NULL -
CONTRACT_SOURCE_CONCEPT_ID - 0 -
SPONSOR_CONCEPT_ID - 0 -
SPONSOR_SOURCE_VALUE - NULL -
SPONSOR_SOURCE_CONCEPT_ID - 0 -
FAMILY_SOURCE_VALUE ENROLID ENROLLMENT_DETAIL: SUBSTRING(RIGHT('00000000000' + CONVERT(VARCHAR,ENROLID), 11), 1,9) Family IDs are the first 9 digits of an ENROLID. For ENROLIDs, less than 11 digits, left-pad with zeros and only take the first 9 digits.
STOP_REASON_CONCEPT_ID - 0 -
STOP_REASON_SOURCE_VALUE - NULL -
STOP_REASON_SOURCE_CONCEPT_ID - 0 -

Please contact Clair Blacketer (https://github.com/clairblacketer) if you have any questions