Attachment W_Pharmacy Claims Data Layout.xlsx

XLSX spreadsheet 169 KB Posted

Attached to
RFP for Medical & Pharmacy Benefits
Solicitation number
S-44300-00014758
Issued by
Marion County, Oregon

About this file

This document is a Pharmacy Claims Data Layout file from Merative, specifically designed for the Oregon Employees' Benefit Board (OEBB/PEBB) drug claims data submission. The file provides comprehensive functional specifications for a monthly prescription drug claims file interface, with submissions required on or before the 15th of the month following each reporting period. The detailed layout specifies 45 distinct data fields including claim identifiers, financial information, patient demographics, prescription details, provider information, and network indicators, with a fixed-record length ASCII file format supporting precise data capture across various claim types and adjustment scenarios.

The specifications outline rigorous data formatting requirements, including specific guidelines for character fields, date formats, numeric representations, and financial field treatments. Key technical requirements include right-justification for numeric fields, implied decimal point conventions, and explicit instructions for handling adjustments, voids, and replacements in claim records. The document supports both fully and self-funded claim arrangements, with fields for tracking coordination of benefits, third-party liability, and additional financial components like HRA and HSA amounts. The comprehensive data layout is clearly intended to support detailed pharmaceutical claims analysis and management for the Oregon public employees' benefits program, with robust provisions for data integrity and comprehensive claims documentation.

View the file

Other files

File Type Posted
AttachB_Definitions.pdf PDF
AttachE_COBID.pdf PDF
PEBB Medical_Rx_MainRFP_clean.pdf PDF
AttachA_Questionnaire.pdf PDF
Total Revenue Disclosure Statement_Blank.xlsx XLSX spreadsheet
Attachment V - Detailed Weight Component Info.pdf PDF
AttachI_SampleGrpBenCon_MedRx.pdf PDF
AttachI_SampleGrpBenefitsCon_Rx_v4.pdf PDF
AttachK_Point Solutions Guidelines.pdf PDF
AttachG_FinalSubForm_v2.pdf PDF
Attachment M_Medical Claims Data_clean.xlsx XLSX spreadsheet
AttachI_SampleGrpBenCon_MedOnly.pdf PDF
Attachment N_Provider Disruption Data_clean.xlsx XLSX spreadsheet
AddendumFive_clean.pdf PDF
AttachC_PlanDesigns.pdf PDF
Attachment L_Census Data_clean.xlsx XLSX spreadsheet
AttachF_RespInquiry.pdf PDF
AddendumSix_Final.pdf PDF
AttachI_SampleGrpBenCon_MedOnly_v3.pdf PDF
Oregon PEBB 2025 Medical and Pharmacy RFP_20251111.pdf PDF
Oregon PEBB 2025 Medical and Pharmacy RFP_20251016PT.pdf PDF
Attachment A_10032025.pdf PDF
Attachment N_Provider Disruption Data updated.xlsx XLSX spreadsheet
Amendment 001 - RFI PAD MOUNT XFMR 2025.pdf PDF
Attachment Q_Pharmacy Clinical Programs.xlsx XLSX spreadsheet
Attachment S_Pharmacy Data Tables.xlsx XLSX spreadsheet
AddendumTwo.pdf PDF
AttachB_Definitions_v2.pdf PDF
Oregon PEBB 2025 Medical and Pharmacy RFP_20251015.pdf PDF
AddendumFour~1.pdf PDF
Attachment T_Pharmacy Pricing Terms_10132025.xlsx XLSX spreadsheet
AttachI_SampleGrpBenCon_MedRx_v3.pdf PDF
Attachment U_Pharmacy Claims Data.xlsx XLSX spreadsheet
AttachI_SampleGrpBenCon_MedRx_v2.pdf PDF
AttachG_FinalSubForm.pdf PDF
Attachment R_Pharmacy GeoAccess Analysis Template.xlsx XLSX spreadsheet
AddendumThree_20251015_clean.pdf PDF
Attachment P_Pharmacy Plan Design Summary.xlsx XLSX spreadsheet
AttachI_SampleGrpBenCon_MedOnly_v2.pdf PDF
MainRfp_UpdatedByAddenda1-2_20250917.pdf PDF
MainRfp_UpdatedByAddenda1-3_20251015~1.pdf PDF
AddendumOne~1.pdf PDF
Attachment T_Pharmacy Pricing Terms.xlsx XLSX spreadsheet
Attachment O_Provider Discount Data_clean.xlsx XLSX spreadsheet
MainRfp_UpdatedByAddenda1-6_Final.pdf PDF
Attachment S_Pharmacy Data Tables_10132025.xlsx XLSX spreadsheet
AttachI_SampleGrpBenefitsCon_Rx_v3.pdf PDF
Attachment J_Data Warehouse File Layout_clean.xlsx XLSX spreadsheet
GeoAccess Standards for Out of State Providers.xlsx XLSX spreadsheet
AttachI_SampleGrpBenefitsCon_Rx.pdf PDF
Show 40 more

Latest 50 of 54 listed here. See the rest on GovTribe.

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

Cover

Mercer - OEBB/PEBB Drug Claims Functional Specification

10/10/2025

Revision History

REVISION HISTORY
DATEAUTHORDESCRIPTION OF ACTIVITY
10/10/25Adrienne A. RayInitial Creation

&"Calibri,Bold"&9 Drug Claims Functional Specifications for File Layout

&9&K000000© Copyright Merative 2023 &9&K000000Merative &9&P

General Information

DESCRIPTION / GENERAL INFORMATION
This interface is designed to produce a prescription drug claims file for plan participants administered through <Data Supplier>.
DEFINITIONS
Prescription drug data are claim records for services that result in direct payment to a pharmacy on a service-specific (for example, prescription-specific) basis.
Fully denied claims should be removed from the extract of claims prior to submission, while partially denied claims should be included. Merative defines denied claims as follows:

� Fully denied claim: The entire claim has been denied (typical reasons include an ineligible member, an ineligible provider, or a duplicate claims).

� Partially denied claim: The claim contains one or more service lines that are denied, but some that are paid. All service lines should be included on the file.

FILE / DATA FORMATTING AND SUBMISSION
DATA SUBMISSIONMerative supports a number of file submission options including FTP and Web Submission.

The data will be submitted to Merative on a monthly basis. Monthly files should be submitted on or before the 15th of the month following the close of each month.

FILE FORMAT• Fixed-Record Length, ASCII File
• Contains Detail (Data) Layout and Trailer Layout for each layout group
CHARACTER FIELDS• Includes A - Z (lower or upper case), 0 – 9, and spaces

• Left justified, right blank/space filled

• Unrecorded or missing values in character fields are blank/spaces

DATE FIELDS• Format of all dates should be MM/DD/CCYY
NUMERIC FIELDS• All numeric fields should be right-justified and left zero-filled

• Unrecorded or missing values in numeric fields should be set to zero

FINANCIAL FIELDS • All financial fields should be right-justified and left zero-filled

• Merative prefers to receive both dollars and cents, with an implied decimal point before the last two digits in the data. For example: "1234567" would represent $12,345.67 Please do not include an actual decimal point in the data.

• Negative signs should be the leading value in the first position. For example: "-1234567" would represent -$12,345.67

• Unrecorded or missing values in numeric fields should be zero (000 to accommodate the 2-digit implied decimal)

INVALID CHARACTERS Please note that the following characters should not be included in the data or the descriptions in the data dictionary.

* ! ? % _ (under score) , (comma)

&"Calibri,Bold" Eligibility Functional Specifications for File Layout

&"Calibri,Bold"&9 Drug Claims Functional Specifications for File Layout

&9&K000000© Copyright Merative 2023 &9&K000000Merative &9&K000000&P

Financials and Corrections

FINANCIAL RELATIONSHIP
Merative defines the relationship among financial fields as follows. Those marked with an asterisk are desirable, but not required for the data extract.
Charge Submitted
Not Covered Amount *Charge Covered *
Discount AmountAllowed Amount
Coinsurance
CopaymentNet Payment
Deductible
Penalty/Sanction Amount *
Third Party Amount
CORRECTIONS TO PAID CLAIMS
Data suppliers generally use either Void/Replacement or Adjustment records to make corrections to paid claims. Merative defines these as follows:
VOID/REPLACEMENT

A void is a claim that reverses or backs out a previously paid one. All financials and quantities are negated on the void record. A replacement record that contains the corrected information generally follows it. The original, void and replacement need not appear in the same file. After adjudication, a paid claim with a $25 Copay and $50 Net Pay, a correction was necessary. The correction contains a $10 Copay and $65 Net Pay.

Record TypeSvc CountCharge SubmittedCopayDeductibleNet Payment
Original1$ 75.00$ 25.00$ - 0$ 50.00
Void-1$ (75.00)$ (25.00)$ - 0$ (50.00)
Replacement1$ 75.00$ 10.00$ - 0$ 65.00

ADJUSTMENT

A financial adjustment is a claim line where one or more of the financial fields display the difference between the original amount and the final amount. Any financial not being adjusted should be zero. All quantities should be zero on the adjustment as well. The original and adjustment need not appear in the same file. After a claim was adjudicated with a $25 Copay and $50 Net Pay, it was discovered that there should have been a $10 Copay and $65 Net Pay.

Record TypeSvc CountCharge SubmittedCopayDeductibleNet Payment
Original1$ 75.00$ 25.00$ - 0$ 50.00
Adjustment0$ - 0$ (15.00)$ - 0$ 15.00

&"Calibri,Bold"&9 Drug Claims Functional Specifications for File Layout

&9&K000000© Copyright Merative 2023 &9&K000000Merative &9&P

Detail Layout

Field NumberField NameStartEndLengthTypeData Element DescriptionHealth Insights FieldData Dictionary NeededData Supplier Instructions/Notes
Fixed-Record Length
0ROW FOR CALCULATIONS ONLY100
1Adjustment Type Code111CharacterClient-specific code for the claim adjustment typeYesYesAdjustment Type values will be identified in the Data Dictionary.
2Allowed Amount21110NumericThe maximum amount allowed by the plan for payment.YesFormat 9(8)v99 (2 - digit, implied decimal)
3Capitated Service Indicator12121CharacterAn indicator that this service (encounter record) was capitatedYesApplicable field values are "Y" for Capitated services and "N" for non-cap services.
4Charge Submitted132210NumericThe submitted or billed charge amountYesFormat 9(8)v99 (2 - digit, implied decimal)
5Claim ID233715CharacterThe client-specific identifier of the claim.Yes
6Claim Type Code38392CharacterClient-specific code for the type of claimYesYesClaim Type Codes will be identified in the Data Dictionary.
7Co-Insurance404910NumericThe coinsurance paid by the subscriber as specified in the plan provision.YesFormat 9(8)v99 (2 - digit, implied decimal)
8Copayment505910NumericThe copayment paid by the subscriber as specified in the plan provision.YesFormat 9(8)v99 (2 - digit, implied decimal)
9Date of Birth606910DateThe birth date of the person.MM/DD/CCYY format

The member’s birth date is part of the Person ID key and is, therefore, critical to tagging claims to eligibility.

The four-digit year is required for date of birth. The century cannot be accurately assigned based on a two-digit year.

10Date of Service707910DateThe date of service for the drug claim.YesMM/DD/CCYY format
11Date Paid808910DateThe date the claim or data record was paid.YesMM/DD/CCYY format

This is the check date.

12Days Supply90934NumericThe number of days of drug therapy covered by the prescription.Yes
13Deductible9410310NumericThe amount paid by the subscriber through the deductible arrangement of the plan.YesFormat 9(8)v99 (2 - digit, implied decimal)
14Dispensing Fee10411310NumericAn administrative fee charged by the pharmacy for dispensing the prescription.YesFormat 9(8)v99 (2 - digit, implied decimal)
15Family ID/Employee SSN1141229CharacterThe unique identifier for the subscriber (contract holder, employee) and their associated dependents.YesFor OEBB/PEBB, we must have the e-number or the p-numbers. No exceptions are allowed.
16Formulary Indicator1231231CharacterAn indicator that the prescription drug is included in the formulary.Yes“Y” or “N”
17Gender Code1241241CharacterThe member's gender code.Yes“M” or “F”

The member’s gender is part of the Person ID key and is, therefore, critical to tagging claims to eligibility.

18Ingredient Cost12513410NumericThe charge or cost associated with the pharmaceutical product.YesFormat 9(8)v99 (2 - digit, implied decimal)
19Metric Quantity Dispensed13514511NumericThe number of units dispensed for the prescription drug claim, as defined by the NCPDPD (National Council for Prescription Drug Programs) standard format.YesFormat 9(8)v99 (3 - digit, implied decimal)
20NDC Number Code14615611CharacterThe FDA (Food and Drug Administration) registered number for the drug, as reported on the prescription drug claims.YesPlease leave out the dashes.
21Net Payment15716610NumericThe actual check amount for the recordYesFormat 9(8)v99 (2 - digit, implied decimal)
22Network Paid Indicator1671671CharacterAn indicator of whether the claim was paid at in-network or out-of-network level.YesY or "N"
23Network Provider Indicator1681681CharacterIndicates if the servicing provider participates in the network to which the patient belongs.YesY or "N"
24Ordering Provider ID16918113CharacterThe ID number of the provider who prescribed the drug.YesIt is preferred that this is the same identifiers used in medical claims. Typically, this is the physician's NPI or the DEA#. If these are not available, the Federal Tax ID (TIN) is acceptable.
25Ordering Provider Name18221130CharacterThe Name of the provider who referred the patient or ordered the test or procedure.Yes
26Ordering Provider Zip Code2122165CharacterThe zip code of the provider who referred the patient or ordered the test or procedure.Yes
27PCP Responsibility Indicator2172171CharacterAn indicator signifying that the PCP is the physician considered responsible or accountable for this claim.Yes
28Provider ID21823013CharacterThe identifier for the provider of service.YesThis should be the NCPDP (National Council for Prescription Drug Programs) number. (Note: The pharmacy NPI is collected in field #36 in this layout.)
29Rx Dispensed as Written Code2312311CharacterThe NCPDP (National Council for Prescription Drug Programs) industry standard code that indicates how the product was dispensed.Yes
30Rx Mail or Retail Code2322321CharacterThe Merative standard code indicating the purchase place of the prescription.YesM for Mail, "R" for Retail
31Rx Payment Tier2332331CharacterClient-specific description for the payment tier of the drug claim.YesData Supplier will help Merative understand which fields to use in order to set this field for the customer. Examples of Rx Payment Tier are as follows:

1. Generic

2. Brand Formulary

3. Brand Non Formulary

32Rx Refill Number2342374NumericA number indicating the original prescription or the refill number.YesThis is the refill number, not the number of refills remaining.
33Sales Tax23824710NumericThe amount of sales tax applied to the cost of the prescription.YesFormat 9(8)v99 (2 - digit, implied decimal)
34Third Party Amount24825710NumericThe amount saved due to integration of third party liability (Coordination of Benefits) by all third party payers (including Medicare).YesFormat 9(8)v99 (2 - digit, implied decimal)
35Discount25826710NumericThe discount amount of the claim, applied to charges for any plan pricing reductions.YesFormat 9(8)v99 (2 - digit, implied decimal)
36Provider NPI Number26827710CharacterThe National Provider Identifier for the pharmacy.
37Funding Type Code2782781CharacterSpecifies whether the claim was paid under a fully or self-funded arrangementYes“S” = Self-funded

“F” = Fully-funded

38Account Structure2792868CharacterClient-specific code for the account structure of the plan that the member is enrolled in. This is usually a group number.TBDTBDAdditional fields may be added to the layout if there is more than one component of the account structure.
39HRA Amount28729610NumericThe amount paid from the HRA to pay the provider.YesFormat 9(8)v99 (2 - digit, implied decimal)
40HSA Amount29730610NumericThe financial amount of the healthcare savings account for consumer-driven health plansYesFormat 9(8)v99 (2 - digit, implied decimal)
41Compound Code3073071CharacterClient-specific code for the compound of the drug.YesYesCompound Codes will be identified in the Data Dictionary.

Note that the NCPDP values include:

‘0’ – Not Specified ‘1’ – Not a Compound ‘2’ – Compound

42Excess Copayment Amount30831710NumericThe amount paid by the patient outside of the flat copayment amount. Examples include when the patient chooses brand name instead of the generic alternative or non-formulary drug instead of the formulary option.YesFormat 9(8)v99 (2 - digit, implied decimal)
43Tax Amount31832710NumericThe amount charged by some states per drug claim.YesFormat 9(8)v99 (2 - digit, implied decimal)
44CUSTOM FIELD # (if applicable)3283281TBDAdditional Rows/Fields to be added as Custom Fields are identified.TBDTBDTBD
45Filler32939971CharacterReserved for future useFill with blanks
46Record Type4004001CharacterRecord type identifierHard Code to "D"
End of Layout - Do not remove this row - All field additions to be inserted above the Filler row

&"Calibri,Bold"&9 Drug Claims Functional Specifications for File Layout

&9&K000000© Copyright Merative 2023 &9&K000000Merative &9&P

Trailer Layout

Field NumberField NameStartEndLengthTypeData Element DescriptionData Supplier Instructions/Notes
Fixed-Record Length
0ROW USED FOR CALCULATIONS100
1Data Start Date11010DateData Start DateMM/DD/CCYY format – i.e. 09/01/2014

This will represent the 1st day of the month for which data is provided.

2 Data End Date 11 20 10 Date Data End Date MM/DD/CCYY format – i.e. 09/30/2014

This will represent the last day of the month for which data is provided.

3Record Count213010NumericNumber of Records on FileThe count of records provided in the data including the Trailer Record.
4Total Net Payments314414NumericTotal net payments on the fileThe sum of net payments provided in the file
5Filler45399355CharacterReserved for future useFill with Blanks
6Record Type4004001CharacterRecord Type IdentifierHard Code ‘T’
End of Layout - Do not remove this row - All field additions to be inserted above the Filler row

&"Calibri,Bold"&9 Drug Claims Functional Specifications for File Layout

&9&K000000© Copyright Merative 2023 &9&K000000Merative &9&P

Detail Layout (2)

Field NumberField NameStartEndLengthTypeData Element DescriptionData Supplier Instructions/NotesStandard
Advantage FieldData Dictionary NeededPopulation of Employee / Dependent Records
Fixed-Record Length
0ROW FOR CALCULATIONS ONLY100
1Field 1144
2CUSTOM FIELD # (if applicable)551
3Filler6499494CharacterReserved for future useFill with blanksMember-Specific
4Record Type5005001CharacterRecord type identifierHard Code to "D"Member-Specific
End of Layout - Do not remove this row - All field additions to be inserted above the Filler row

&"Calibri,Bold"&9Eligibility Functional Specifications for File Layout --- &A ---

&G &9&P &9&A &F

Trailer Layout (2)

Field NumberField NameStartEndLengthTypeData Element DescriptionData Supplier Instructions/NotesAdvantage FieldPopulation of Employee / Dependent Records
Fixed-Record Length
0ROW USED FOR CALCULATIONS100
1Start Date11010DateStart Date of the data within the fileMM/DD/CCYY format – i.e. 09/01/2015
This will represent the 1st day of the month for which data is provided.N/AN/A
2End Date112010DateEnd Date of the data within the fileMM/DD/CCYY format – i.e. 09/01/2015
This will represent the 1st day of the month for which data is provided.N/AN/A
3Record Count213010NumericNumber of Records on FileThe count of records provided in the data including the Trailer Record.N/AN/A
4Total Dollars314010NumericTotal dollars on the file
5Filler41499459CharacterReserved for future useFill with BlanksN/AN/A
6Record Type5005001CharacterRecord Type IdentifierHard Code ‘T’N/AN/A
End of Layout - Do not remove this row - All field additions to be inserted above the Filler row

&"Calibri,Bold"&9Eligibility Functional Specifications for File Layout --- &A ---

&G &9&P &9&A

Detail Layout (3)

Field NumberField NameStartEndLengthTypeData Element DescriptionData Supplier Instructions/NotesStandard
Advantage FieldData Dictionary NeededPopulation of Employee / Dependent Records
Fixed-Record Length
0ROW FOR CALCULATIONS ONLY100
1Field 1144Character
2CUSTOM FIELD # (if applicable)551TBDAdditional Rows/Fields to be added as Custom Fields are identified.

Common custom fields include:

Employee Status Relationship Department

Accrual Rate(s)TBDN/ATBDTBD
3Filler6399394CharacterReserved for future useFill with blanksMember-Specific
4Record Type4004001CharacterRecord type identifierHard Code to "D"Member-Specific
End of Layout - Do not remove this row - All field additions to be inserted above the Filler row

&"Calibri,Bold"&9Eligibility Functional Specifications for File Layout --- &A ---

&G &9&P &9&A

Trailer Layout (3)

Field NumberField NameStartEndLengthTypeData Element DescriptionData Supplier Instructions/NotesAdvantage FieldPopulation of Employee / Dependent Records
Fixed-Record Length
0ROW USED FOR CALCULATIONS100
1Start Date11010DateStart Date of the data within the fileMM/DD/CCYY format – i.e. 09/01/2015
This will represent the 1st day of the month for which data is provided.N/AN/A
2End Date112010DateEnd Date of the data within the fileMM/DD/CCYY format – i.e. 09/01/2015
This will represent the 1st day of the month for which data is provided.N/AN/A
3Record Count213010NumericNumber of Records on FileThe count of records provided in the data including the Trailer Record.N/AN/A
4Total Dollars314010NumericTotal dollars on the file
5Filler41399359CharacterReserved for future useFill with BlanksN/AN/A
6Record Type4004001CharacterRecord Type IdentifierHard Code ‘T’N/AN/A
End of Layout - Do not remove this row - All field additions to be inserted above the Filler row

&"Calibri,Bold"&9Eligibility Functional Specifications for File Layout --- &A ---

&G &9&P &9&A

Detail Layout (4)

Field NumberField NameStartEndLengthTypeData Element DescriptionData Supplier Instructions/NotesStandard
Advantage FieldData Dictionary NeededPopulation of Employee / Dependent Records
Fixed-Record Length
0ROW FOR CALCULATIONS ONLY100
1Field 1144Character
2CUSTOM FIELD # (if applicable)551TBDAdditional Rows/Fields to be added as Custom Fields are identified.

Common custom fields include:

Employee Status Relationship Department

Accrual Rate(s)TBDN/ATBDTBD
3Filler6399394CharacterReserved for future useFill with blanksMember-Specific
4Record Type4004001CharacterRecord type identifierHard Code to "D"Member-Specific
End of Layout - Do not remove this row - All field additions to be inserted above the Filler row

&"Calibri,Bold"&9Eligibility Functional Specifications for File Layout --- &A ---

&G &9&P &9&A

Trailer Layout (4)

Field NumberField NameStartEndLengthTypeData Element DescriptionData Supplier Instructions/NotesAdvantage FieldPopulation of Employee / Dependent Records
Fixed-Record Length
0ROW USED FOR CALCULATIONS100
1Start Date11010DateStart Date of the data within the fileMM/DD/CCYY format – i.e. 09/01/2015
This will represent the 1st day of the month for which data is provided.N/AN/A
2End Date112010DateEnd Date of the data within the fileMM/DD/CCYY format – i.e. 09/01/2015
This will represent the 1st day of the month for which data is provided.N/AN/A
3Record Count213010NumericNumber of Records on FileThe count of records provided in the data including the Trailer Record.N/AN/A
4Total Dollars314010NumericTotal dollars on the file
5Filler41399359CharacterReserved for future useFill with BlanksN/AN/A
6Record Type4004001CharacterRecord Type IdentifierHard Code ‘T’N/AN/A
End of Layout - Do not remove this row - All field additions to be inserted above the Filler row

&"Calibri,Bold"&9Eligibility Functional Specifications for File Layout --- &A ---

&G &9&P &9&A

Data

StyleFixed
IntroSplashNoShow

Access Admin Tools image1.png image2.png image3.png image4.svg image5.png

File details come from the government source that posted it. Updated .