Schedule F7- Data Feed- Non Medicare Pharmacy.xlsx
XLSX spreadsheet 26 KB Posted
- Attached to
- Health Insurance Database Management System
- Solicitation number
- RFP-171-250000003097-2
- Issued by
- Michigan
About this file
This is an Excel spreadsheet detailing a data feed specification for the Michigan Office of Retirement Services' Health Insurance Database Management System, specifically for a Non-Medicare Pharmacy Claims Extract (Request for Proposal No. 250000002818). The file provides a comprehensive 574-character fixed-length text file layout that defines precise data field specifications for pharmacy claims data, including detailed parameters for each data element such as field type, length, starting and ending positions, and specific content requirements.
The data feed specification encompasses extensive pharmacy claim details including carrier, account, group, and member information, prescription specifics (NDC, GPI number, drug label name), claim status, dispensing details, pricing components (ingredient cost, dispensing fees, taxes, copays), prescriber information, pharmacy details, and patient payment attributes. The specification includes nuanced coding for various claim origins, drug statuses, and price types, with explicit instructions for data formatting, decimal positioning, and handling of specific scenarios like compound codes, mail order transactions, and claim submission methods. Notable technical features include precise data source references, comprehensive validation rules, and granular tracking of pharmacy and prescription-related metadata.
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 F8- Data Feed- Medicare Pharmacy.xls | XLS spreadsheet | |
| 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 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. 250000002818 |
| Health Insurance Database Management System for the Michigan Office of Retirement Services |
| D.0 Expanded SR15283 Claims Extract - Traditional | |||||||||
| 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- | |
| Filler | Text | 10 | 259 | 268 | Blank | NA | SPACES | |||
| 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 | |||
| Filler | Text | 10 | 476 | 485 | BLANK | NA | SPACES | |||
| 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
| 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 | Health Insurance Database Management System for the Michigan Office of Retirement Services | |||
| Claim Origin Values: | ||||
| case CLMORIGIN | ||||
| when ' | B | ' then ' | Batch loaded | ' |
| when ' | C | ' then ' | Trial - API/DUR MSG, SKIP DEDUCTIBLE | ' |
| when ' | E | ' then ' | Trial - API/SKIP DUR, DEDUCTIBLE | ' |
| when ' | G | ' then ' | Trial - API/SKIP DEDUCTIBLE | ' |
| when ' | H | ' then ' | Batch Trial Mass Amendment | ' |
| when ' | I | ' then ' | Batch Mass Amendment Update | ' |
| when ' | J | ' then ' | Manual Single Amendment Update | ' |
| when ' | M | ' then ' | Manually keyed | ' |
| when ' | P | ' then ' | Retro-Active Eligibility Reprocess | ' |
| when ' | T | ' then ' | Electronic Transaction | ' |
| when ' | 1 | ' then ' | Trial-Batch | ' |
| when ' | 2 | ' then ' | Trial -Batch/SKIPDUR | ' |
| when ' | 3 | ' then ' | Trial-Batch/DUR MSG ONLY | ' |
| when ' | 4 | ' then ' | Trial-Manual | ' |
| when ' | 5 | ' then ' | Trial-Manual/SKIP DUR | ' |
| when ' | 6 | ' then ' | Trial-Manual/DUR MSG ONLY | ' |
| when ' | 7 | ' then ' | Trial-API | ' |
| when ' | 8 | ' then ' | Trial-API/SKIP DUR | ' |
| when ' | 9 | ' then ' | Trial-API/DUR MSG ONLY | ' |
| else NULL END |
Drug Status to Formulary Chart
| Request for Proposal No. 250000003097 | |
| Health Insurance Database Management System for the Michigan Office of Retirement Services | 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 | 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 .