Schedule F8- Data Feed- Medicare Pharmacy.xls
XLS spreadsheet 61 KB Posted
- Attached to
- Health Insurance Database Management System
- Solicitation number
- RFP-171-250000003097-2
- Issued by
- Michigan
About this file
This document is a detailed Schedule F8 Data Feed specification for a Medicare Pharmacy database extract, developed for the Michigan Office of Retirement Services as part of Request for Proposal No. 250000003097 for a Health Insurance Database Management System. The specification provides an extensive, fixed-length data layout for pharmacy claims information, detailing 63 specific data fields ranging from carrier and account identifiers to complex pharmacy transaction details including prescription information, patient demographics, pricing, and claim status.
The data feed encompasses comprehensive pharmacy claims data with precise numeric and text field specifications, including details like National Drug Code (NDC), prescription quantities, days supply, generic indicators, pricing components, patient payment amounts, and pharmacy provider information. The specification includes multiple lookup and reference tables for interpreting codes such as claim origins, drug status, and price types, with specific formatting instructions for decimal placement, field lengths, and data sources primarily drawn from the HEClaimsEDW database system. The document provides intricate technical guidance for extracting and formatting Medicare pharmacy claims data with high granularity and standardized reporting requirements.
View the file
Other files
| File | Type | Posted |
|---|---|---|
| Schedule B - Healthcare Database.xlsx | XLSX spreadsheet | |
| Schedule F2-Data Feed Non-Medicare Medical.csv | CSV file | |
| Schedule F5- Data Feed-Medicare Lab.csv | CSV file | |
| Schedule J Software Table.xlsx | XLSX spreadsheet | |
| 250000003097 - Health Insurance Database Management System RFP.docx | DOCX document | |
| Schedule F10- Data Feed- Vision.xlsx | XLSX spreadsheet | |
| Schedule F3- Data Feed-Medicare Medical.csv | CSV file | |
| Schedule F6- Data Feed-Hearing.xlsx | XLSX spreadsheet | |
| Schedule F7- Data Feed- Non Medicare Pharmacy.xlsx | XLSX spreadsheet | |
| Schedule F9- Data Feed-Dental.xls | XLS spreadsheet | |
| Schedule H Quarterly Vendor Reconciliation Report.xlsx | XLSX spreadsheet | |
| RFP 250000003097 - Bidder Question and Answer Response.xlsx | XLSX spreadsheet | |
| Schedule F4- Data Feed- Non-Medicare Lab.csv | CSV file | |
| Schedule G Service Availability.xlsx | XLSX spreadsheet | |
| Schedule I Sample Validation Report.xlsx | XLSX spreadsheet | |
| Schedule F1- Data Feed-ORS.xls | XLS spreadsheet |
Show all 16
On GovTribe
Work with this file
- Download the original file
- Contacts named in this file
- Similar government files
- Ask GovTribe AI about this file
Text version
Expanded SR15283 Layout
| Request for Proposal No. 250000003097 | |||||||||
| Health Insurance Database Management System for the Michigan Office of Retirement Services | |||||||||
| D.0 Expanded SR15283 Claims Extract - PassThru | |||||||||
| Name | Type | Field Length | Dec | ||||||
| Pos | Start | End | Comments (Blank if self-explanatory) | Data Source | Source | ||||
| Field | Notes | ||||||||
| Carrier | Text | 9 | 1 | 9 | Carrier ID | [HEClaimsEDW] dbo.vCasted_Claims | CARRIERID | ||
| Account | Text | 15 | 10 | 24 | Account ID | [HEClaimsEDW] dbo.vCasted_Claims | ACCOUNTID | ||
| Group Id | Text | 15 | 25 | 39 | Group ID | [HEClaimsEDW] dbo.vCasted_Claims | GROUPID | ||
| Group Name | Text | 25 | 40 | 64 | Group Name | [HEClaimsEDW] dbo.Dim_Client | GroupName | ||
| Member ID | Text | 20 | 65 | 84 | Member ID | [HEClaimsEDW] dbo.vCasted_Claims | MEMBERID | ||
| Person Code | Text | 3 | 85 | 87 | 01 = Cardholder |
02 = Spouse 03 = Dependent
| 04 = Other | [HEClaimsEDW] dbo.vCasted_Claims | MBRPRSNCDE | ||||||||
| Date of Birth | Numeric | 8 | 0 | 88 | 95 | Format: YYYYMMDD | [HEClaimsEDW] dbo.vCasted_Claims | MBRBIRTH | ||
| Gender | Text | 1 | 96 | 96 | M = Male | |||||
| F = Female | [HEClaimsEDW] dbo.vCasted_Claims | MBRSEX | ||||||||
| Member First Name | Text | 15 | 97 | 111 | Member First Name | [HEClaimsEDW] dbo.vCasted_Claims | MBRFRSTNME | |||
| Member Last Name | Text | 25 | 112 | 136 | Member Last Name | [HEClaimsEDW] dbo.vCasted_Claims | MBRLSTNME | |||
| Member SSN | Text | 9 | 137 | 145 | Member Social Security Number | [HEClaimsEDW] dbo.vCasted_Claims | SOCSECNBR | ** Added from SR15632 | ||
| Date Filled | Numeric | 8 | 0 | 146 | 153 | Submitted Date Filled | [HEClaimsEDW] dbo.vCasted_Claims | DTEFILLED | ||
| NDC | Text | 11 | 154 | 164 | Full NDC including package size indicator | [HEClaimsEDW] dbo.vCasted_Claims | PRODUCTID | |||
| Label Name | Text | 30 | 165 | 194 | Drug Label Name | [HEClaimsEDW] dbo.vCasted_Claims | LABELNAME | |||
| GPI Number | Text | 14 | 195 | 208 | GPI Number | [HEClaimsEDW] dbo.vCasted_Claims | GPINUMBER | |||
| Date Submitted | Numeric | 8 | 0 | 209 | 216 | YYYYMMDD | [HEClaimsEDW] dbo.vCasted_Claims | DATESBM | ||
| Claim Status | Text | 1 | 217 | 217 | P = Paid |
X = Reversed
| R = Rejected | [HEClaimsEDW] dbo.vCasted_Claims | CLAIMSTS | ||||||||
| Dispensed Quantity | Decimal | 12 | 3 | 218 | 229 | Submitted Quantity Dispensed | [HEClaimsEDW] dbo.vCasted_Claims | DECIMALQTY | Format: 99999999v999- | |
| Days Supply | Decimal | 4 | 0 | 230 | 233 | Submitted Days Supply | [HEClaimsEDW] dbo.vCasted_Claims | DAYSSUPPLY | Format: 999- | |
| DAW | Text | 1 | 234 | 234 | Submitted Product Selection Code | [HEClaimsEDW] dbo.vCasted_Claims | PSC | |||
| New Refill Indicator | Text | 2 | 235 | 236 | Fill Number | [HEClaimsEDW] dbo.vCasted_Claims | REFILL | |||
| Generic Indicator | Text | 1 | 237 | 237 | Y = Generic |
M = Brand (Multi-source w/o Generic Available) N = Brand (Single-source)
| O = Brand (Multi-source with Generic Available) | [HEClaimsEDW] dbo.vCasted_Claims | MULTSRCCDE | ||||||||
| Prior Authorization | Text | 11 | 238 | 248 | Member Prior Authorization Number | [HEClaimsEDW] dbo.vCasted_Claims | PRAUTHNBR | |||
| Usual & Customary | Decimal | 10 | 2 | 249 | 258 | U&C Amount | [HEClaimsEDW] dbo.vCasted_Claims | USUALNCUST | Format: 9999999v99- | |
| Final Pricing Indicator | Text | 10 | 259 | 268 | Plan Final Price Schedule | [HEClaimsEDW] dbo.vCasted_Claims | PLNFNLPSCH | Need option to Space Fill this field | ||
| Billed Ingredient Cost | Decimal | 10 | 2 | 269 | 278 | Client Ingredient Cost. Discounted Price of the Drug | ||||
| (usually AWP minus a percentage) | [HEClaimsEDW] dbo.vCasted_Claims | CLTINGCST | Format: 9999999v99- | |||||||
| Billed Fee | Decimal | 10 | 2 | 279 | 288 | Dispensing Fee Billed to the Plan | [HEClaimsEDW] dbo.vCasted_Claims | CLTDISPFEE | Format: 9999999v99- | |
| Billed Tax | Decimal | 10 | 2 | 289 | 298 | Tax Billed to the Plan | [HEClaimsEDW] dbo.vCasted_Claims | CLTSLSTAX | Format: 9999999v99- | |
| Billed Copay | Decimal | 10 | 2 | 299 | 308 | Copay subtracted before Billed to the Plan | [HEClaimsEDW] dbo.vCasted_Claims | CLTCOPAY | Format: 9999999v99- | |
| Total Amount Billed to Customer | Decimal | 10 | 2 | 309 | 318 | Total Amount Billed = | ||||
| (Billed Ing.Cost + Dispensing Fee + Tax) - Copay | [HEClaimsEDW] dbo.vCasted_Claims | CLTDUEAMT | Format: 9999999v99- | |||||||
| Unit AWP | Decimal | 14 | 5 | 319 | 332 | Average Wholesale Price Unit Cost | [HEClaimsEDW] dbo.vCasted_Claims | AWPUNITCST | Format: 99999999v99999- | |
| Prescriber ID | Text | 15 | 333 | 347 | Prescriber ID | [HEClaimsEDW] dbo.vCasted_Claims | PRESCRIBER | |||
| Physician First Name | Text | 15 | 348 | 362 | Primary Prescriber First Name | [HEClaimsEDW] dbo.vCasted_Claims | PPPRFSTNME | |||
| Physician Last Name | Text | 25 | 363 | 387 | Primary Prescriber Last Name | [HEClaimsEDW] dbo.vCasted_Claims | PPPRLSTNME | |||
| Pharmacy Chain Number | Text | 6 | 388 | 393 | AFFAFLD | [HEClaimsEDW] dbo.vCasted_Claims | AFFILIATIN | |||
| Pharmacy NABP | Text | 15 | 394 | 408 | SBMSVCPRVDRID | [HEClaimsEDW] dbo.vCasted_Claims | SRVPROVID | |||
| Mail Order | Text | 1 | 409 | 409 | Y = Mail Order | |||||
| N = Retail | [Reference] dbo.MailNetwork | Lookup: |
Case When RXNETWORK in dbo.MailNetwork Then 'Y' Else 'N'
| Pharmacy Name | Text | 25 | 410 | 434 | Service Provider Name | [HEClaimsEDW] dbo.vCasted_Claims | SRVPROVNME | |||
| Final Plan Code | Text | 10 | 435 | 444 | Final Plan Code | [HEClaimsEDW] dbo.vCasted_Claims | FNLPLANCDE | |||
| Drug Status | Text | 1 | 445 | 445 | Populated with Plan Drug Status | |||||
| (see Drug Status to Formulary Chart) | [HEClaimsEDW] dbo.vCasted_Claims | PLANDRUGST | ||||||||
| Pharmacy Zipcode | Text | 10 | 446 | 455 | Pharmacy Zip Code | [HEClaimsEDW] dbo.vCasted_Claims | PHARMZIP | |||
| Pharmacy State | Text | 2 | 456 | 457 | Pharmacy State | [HEClaimsEDW] dbo.Dim_Pharmacy | PhyLocStateCode | |||
| RxClaim# | Text | 15 | 458 | 472 | RX Claim Number | [HEClaimsEDW] dbo.vCasted_Claims | RXCLAIMNBR | |||
| Customer Location Code | Text | 2 | 473 | 474 | Submitted Customer Location Code | [HEClaimsEDW] dbo.vCasted_Claims | CUSTLOC | |||
| Compound Code | Text | 1 | 475 | 475 | Submitted Compound Code | [HEClaimsEDW] dbo.vCasted_Claims | COMPOUNDCD | |||
| Price Type | Text | 10 | 476 | 485 | Basis of Price Determination | [HEClaimsEDW] dbo.vCasted_Claims | CLTPRCTYPE | Need option to Space Fill this field | ||
| Patient Pay Amt | Decimal | 10 | 2 | 486 | 495 | Total Paid by Patient | [HEClaimsEDW] dbo.vCasted_Claims | CLTPATPAY | Format: 9999999v99- | |
| Product Selection Amt | Decimal | 9 | 2 | 496 | 504 | Patient Paid attributed to brand selection penalty | [HEClaimsEDW] dbo.vCasted_Claims | CLTPRODSEL | Format: 999999v99- | |
| Tax Amt | Decimal | 9 | 2 | 505 | 513 | Patient Paid attributed to sales tax | [HEClaimsEDW] dbo.vCasted_Claims | CLTATRTAX | Format: 999999v99- | |
| Deductible | Decimal | 9 | 2 | 514 | 522 | Patient Paid attributed to deductible | [HEClaimsEDW] dbo.vCasted_Claims | CLTATRRDED | Format: 999999v99- | |
| Excess Amt | Decimal | 9 | 2 | 523 | 531 | Patient Paid attributed to claim exceeding benefit max | [HEClaimsEDW] dbo.vCasted_Claims | CLTEXCEBFT | Format: 999999v99- | |
| Claim Seq# | Numeric | 3 | 0 | 532 | 534 | Unique claim identifier combined with RxCLAIM# | [HEClaimsEDW] dbo.vCasted_Claims | CLMSEQNBR | ||
| COB Amt | Decimal | 9 | 2 | 535 | 543 | Other payor amount | [HEClaimsEDW] dbo.vCasted_Claims | CLTOTHPAYA | Format: 999999v99- | |
| Patient Pay Price Schedule | Text | 10 | 544 | 553 | Patient Pay Schedule Used | [HEClaimsEDW] dbo.vCasted_Claims | CLTPTPS | |||
| Rx Number | Text | 12 | 554 | 565 | Submitted Rx Number | [HEClaimsEDW] dbo.vCasted_Claims | RXNUMBER | **Moved from Line 14 - expanded to 12 bytes | ||
| Primary Cardholder SSN | Text | 9 | 566 | 574 | Primary Cardholder Social Security Number | Member Eligibility V2.6 | SOCSECNBR |
WHERE FIRST X CHARACTERS OF MEMBERID MATCHES AND MBRRELCDE = 1
| X=LENGTH OF MEMBERID LESS LENGTH OF MBRPRSNCDE (SEE NOTE 4) | ** Added from SR15632 | |||||
| Claim Origin | Text | 1 | 575 | 575 | Manner in which claims were submitted. |
T = Electronic transaction M = Manually keyed B = Batch loaded 1 = Trial Batch 3 = Trial-Batch/DUR MSG ONLY 2 = Trial-Batch/SKIP DUR 4 = Trial-Manual 5 = Trial-Manual/SKIP DUR 6 = Trial-Manual/DUR MSG ON
| P = Retro-Active Eligibility Reprocess. | [HEClaimsEDW] dbo.vCasted_Claims | CLMORIGIN | ||||
| Relationship Code | Numeric | 1 | 576 | 576 | 1=CARDHOLDER |
2=SPOUSE
3=CHILD
| 4=OTHER | [HEClaimsEDW] dbo.vCasted_Claims | MBRRELCDE | |||||||
| Member Family ID | Text | 20 | 577 | 596 | Member Family ID | [HEClaimsEDW] dbo.vCasted_Claims | MBRFAMLYID | ||
| TC4 Gap Brand Discount | Decimal | 9 | 2 | 597 | 605 | [HEClaimsEDW] dbo.vCasted_Claims | |||
| rcex7p | AHCVQU | ** Added from C000027012_XXXXX |
Format: 999999v99-
| EX4 LICS Amount | Decimal | 9 | 2 | 606 | 614 | [HEClaimsEDW] dbo.vCasted_Claims | |
| rcex7p | AHNFHY | ** Added from C000027012_XXXXX |
Format: 999999v99-
| Client Incentive Amount | Decimal | 9 | 2 | 615 | 623 | [HEClaimsEDW] dbo.vCasted_Claims | CLTINCENTV | Format: 999999v99- | |
| Filler | Text | 27 | 624 | 650 | BLANK | NA | SPACES | ||
| NOTES: | |||||||||
| 1) Fixed length text file | |||||||||
| 2) Decimal positions are implied | |||||||||
| 3) Last position of Decimal fields designated for sign (negative only) | |||||||||
| 4) Cardholder SSN is populated with SOCSECNBR of |
primary cardholder, which is determined by examining finding MBRRELCDE = 1 from match on first X characters of MEMBERID.
X number of characters is calculated by subtracting length of MBRPRSNCDE from length of MEMBERID.
For example, if MEMBERID is 12 digits and MBRPRSNCDE is 2 digits, then match on first 10 characters (12-2).
Claim Origin Chart
| Request for Proposal No. 250000002818 | |
| Health Insurance Database Management System for the Michigan Office of Retirement Services | |
| Claim Origin Values: | |
| B | Batch loaded |
| C | Trial - API/DUR MSG, SKIP DEDUCTIBLE |
| E | Trial - API/SKIP DUR, DEDUCTIBLE |
| G | Trial - API/SKIP DEDUCTIBLE |
| H | Batch Trial Mass Amendment |
| I | Batch Mass Amendment Update |
| J | Manual Single Amendment Update |
| M | Manually keyed |
| P | Retro-Active Eligibility Reprocess |
| T | Electronic Transaction |
| 1 | Trial-Batch |
| 2 | Trial -Batch/SKIPDUR |
| 3 | Trial-Batch/DUR MSG ONLY |
| 4 | Trial-Manual |
| 5 | Trial-Manual/SKIP DUR |
| 6 | Trial-Manual/DUR MSG ONLY |
| 7 | Trial-API |
| 8 | Trial-API/SKIP DUR |
| 9 | Trial-API/DUR MSG ONLY |
Drug Status to Formulary Chart
| Request for Proposal No. 250000002818 | |
| Health Insurance Database Management System for the Michigan Office of Retirement Services | |
| Plan Drug Status | Description |
| b | Non-Formulary (Skp OOP No Accm) |
| e | Non-Formulary (No Ded/Ben; apply OOP) |
| f | Formulary (NoDed/Ben; apply OOP) |
| g | Formulary (No OOP; apply Ded/Ben) |
| h | Formulary (Override-List/PA Dtls 2) |
| x | Non-Formulary (O Non-Formulary w/Msg; Formulary) |
| y | Non-Formulary (Y Formulary; Non-Formulary w/Msg) |
| z | Formulary (Y Formulary; Non-Formulary Reject) |
| A | 1 Formulary; Pd Non-Formulary w/ MSG |
| B | No Prescription Drug Benefit |
| C | Non-Formulary (Paid Non-Formulary w/ Msg) |
| D | 15 Formulary; Pd Non-Formulary w/MSG |
| E | 1 Formulary; Non-Formulary Reject |
| F | Formulary |
| G | Non-Formulary (Paid Non-Formulary w/o Msg) |
| H | Formulary Captured |
| I | 15 Formulary; Non-Formulary Reject |
| J | Formulary (1 Formulary; Non-Formulary (NoDed/BenAccum)) |
| K | Formulary (SkipDed/BenMax) |
| L | Non-Formulary (SkipDed/BenMax) |
| M | Message |
| N | Non-Formulary Reject |
| O | Override |
| P | Prior Authorization |
| R | Reject |
| S | Formulary (NoDed/BenAccum) |
| T | Non-Formulary (NoDed/BenAccum) |
| W | Negative Pharmacy List; Reject |
| 3 | Override-Use List/PA Dtls |
| 5 | Formulary 5 |
| 6 | Formulary 6 |
| 7 | Non-Formulary Captured |
| 8 | Non-Formulary (Non-Preferred Formulary Reject) |
| 9 | Non-Formulary (Y Pd NFw/oMSG; Pd NFw/MSG) |
Price Type Chart
| Request for Proposal No. 250000002818 | |
| Health Insurance Database Management System for the Michigan Office of Retirement Services | |
| Code | Description |
| SD | Submitted Drug Cost |
| SM | Submitted Amount Due |
| U | Usual and Customary |
| AWP | Average Wholesale Price |
| HCFA | Federal Upper Limit cost as established by the Health Care Financing Administration. |
| MAC248 | Catalyst Rx MAC Price |
File details come from the government source that posted it. Updated .