Skip to content

Drug pricing data management

This operation merges the full CRUD surface for national drug codes (clsNationalDrug), their effective-dated prices (clsNationalDrugPrice/clsNationalDrugPriceCollection), and the append-style price history archive (clsNdcPriceHistory/clsNdcPriceHistoryCollection), plus the in-memory-only clsNationalDrugCollection container. These classes are merged into one page because a national drug's prices are always saved as its children in a single cascading save, and the active-price selection and price-history archival patterns only make sense in the context of that combined lifecycle.

Field Value
Operation ID OP-MB-06
Trigger clsNationalDrug.NewCbpNationalDrug / GetNationalDrug / property edits / Save(); clsNationalDrugPrice CRUD via the parent's DrugPrices collection; clsNdcPriceHistory.NewNdcPriceHistory(dpi, drugPrice) / GetNdcPriceHistory
Inputs National drug code, description, and reference fields; price effective/expiration dates and amounts; the clsNationalDrugPrice instance being archived (for history creation)
Outputs Persisted CBP_NATIONAL_DRUG and CBP_NATIONAL_DRUG_PRICE rows; inserted CBP_NDC_PRICE_HISTORY rows; resolved ActivePrice for pricing calculations elsewhere in the library
Primary components clsNationalDrug.SaveMe, clsNationalDrug.ActivePrice, clsNationalDrug.DrugPrices, clsNationalDrugPrice insert/retrieve/update/delete, clsNationalDrugPriceCollection.GetCbpNationalDrugPrices, clsNdcPriceHistory.NewNdcPriceHistory, clsNdcPriceHistory.GetNdcPriceHistory, clsNdcPriceHistoryCollection
Entry point Business\SAIF.Business.Claim.MedicalBill\clsNationalDrug.vb, Business\SAIF.Business.Claim.MedicalBill\clsNationalDrugPrice.vb, Business\SAIF.Business.Claim.MedicalBill\clsNdcPriceHistory.vb

Steps

  1. Create a national drug. clsNationalDrug.NewCbpNationalDrug(dpi, nationalDrugCode) constructs a new in-memory object; the caller sets description and reference fields before saving.
  2. Retrieve a national drug. clsNationalDrug.GetNationalDrug issues a direct SQL retrieve against CBP_NATIONAL_DRUG (SQL-008, CBP_NATIONAL_DRUG).
  3. Lazy-load the drug's prices. Reading clsNationalDrug.DrugPrices retrieves the child clsNationalDrugPriceCollection via clsNationalDrugPriceCollection.GetCbpNationalDrugPrices (SQL-010, CBP_NATIONAL_DRUG_PRICE).
  4. Select the active price. clsNationalDrug.ActivePrice first scans DrugPrices for the first row whose ExpDate equals Date.MaxValue or Date.MinValue (treated as unexpired) and returns it immediately if found; otherwise it scans all prices again and returns the row with the latest EffDate (BR-MB-018).
  5. Insert a national drug (SaveMe → Save/SaveChild). A new, dirty clsNationalDrug inserts its own header row (SQL-001, CBP_NATIONAL_DRUG); AfterInsert checks the rows-affected return value and adds an InsertError when it is not 1, else marks the object old (BR-MB-004).
  6. Update a national drug. An existing, dirty clsNationalDrug updates its header row (SQL-014, CBP_NATIONAL_DRUG); AfterUpdate adds a DB contention UpdateError when rows affected is not 1 (BR-MB-004).
  7. Cascade the save to prices. After the drug's own header save (or unconditionally if the header itself was not dirty), SaveMe always calls SaveChildObject(DrugPrices), cascading insert/update/delete to every dirty or removed row in the price collection regardless of whether the parent header changed.
  8. Insert a drug price. A new clsNationalDrugPrice row inserts into CBP_NATIONAL_DRUG_PRICE (SQL-002); its AfterInsert applies the same rows-affected check as step 5 (BR-MB-004).
  9. Retrieve a drug price. clsNationalDrugPrice retrieval issues a direct SQL read (SQL-009, CBP_NATIONAL_DRUG_PRICE).
  10. Update a drug price. An existing, dirty clsNationalDrugPrice updates its row (SQL-015); AfterUpdate applies the same contention check as step 6 (BR-MB-004).
  11. Delete a drug price. clsNationalDrugPrice supports deletion — added 06/22/2015 "to eliminate overlapping drug prices" — which deletes the row (SQL-016); AfterDelete adds a DeleteError on contention, or marks the object new (available for re-insert) on success (BR-MB-004).
  12. Archive a price into history. clsNdcPriceHistory.NewNdcPriceHistory(dpi, drugPrice) constructs a new history object by snapshotting the fields of the given clsNationalDrugPrice instance, then creates it via the data-access layer, inserting into CBP_NDC_PRICE_HISTORY (SQL-003, CBP_NDC_PRICE_HISTORY); its AfterInsert applies the same rows-affected check as step 5 (BR-MB-004).
    • No code path in this library automatically calls NewNdcPriceHistory before deleting or overwriting a clsNationalDrugPrice row; archival appears to be a caller-orchestrated workflow rather than something enforced by the business objects themselves.*
  13. Retrieve price history. clsNdcPriceHistory.GetNdcPriceHistory(dpi, nationalDrugCode, effDate) reads a single history row (SQL-011, CBP_NDC_PRICE_HISTORY); clsNdcPriceHistoryCollection reads the full history for a drug (SQL-012, CBP_NDC_PRICE_HISTORY).
  14. In-memory drug collection. clsNationalDrugCollection is a create-only container with no retrieval SQL of its own; calling its RetrieveSQL path throws, so it is populated purely by adding individually-retrieved clsNationalDrug instances rather than by a bulk query.*

Process flow

The following diagram visualizes this operation's control flow.

flowchart TD
    Start([Caller creates, retrieves,<br>or edits a national drug]) --> New{New or existing?}
    New -->|New| Insert[Insert national drug row]
    New -->|Existing dirty| Update[Update national drug row]
    Insert --> Rows1{RowsAffected = 1?}
    Update --> Rows2{RowsAffected = 1?}
    Rows1 -->|No| Err1([InsertError added])
    Rows2 -->|No| Err2([UpdateError: DB contention])
    Rows1 -->|Yes| Cascade
    Rows2 -->|Yes| Cascade
    Cascade[SaveChildObject: cascade<br>to DrugPrices collection] --> PriceOps{Per price row}
    PriceOps -->|Insert| PInsert[Insert price row]
    PriceOps -->|Update| PUpdate[Update price row]
    PriceOps -->|Delete| PDelete[Delete price row]
    PriceOps --> Active[ActivePrice: prefer unexpired,<br>else max EffDate]
    PDelete --> History{Caller archives<br>before delete?}
    History -->|Yes, manual| Archive[NewNdcPriceHistory: insert history row]
    History -->|No| Done
    Archive --> Done([Save complete])
    Active --> Done

Failure and edge-case behavior

  • Every insert/update/delete across clsNationalDrug, clsNationalDrugPrice, and clsNdcPriceHistory uses the same optimistic-concurrency pattern: a rows-affected value other than 1 adds an error message instead of throwing (BR-MB-004).
  • SaveMe always cascades to DrugPrices even when the parent drug header itself is not dirty, so a caller that only edits child price rows still gets those changes persisted.
  • ActivePrice returns Nothing if DrugPrices is empty or if no row's EffDate is ever greater than Date.MinValue (which should not occur for populated price rows, but is not explicitly guarded).*
  • The price-history archive-on-delete pattern is not automatically enforced — deleting a clsNationalDrugPrice row does not itself create a clsNdcPriceHistory snapshot; callers must invoke NewNdcPriceHistory themselves if they want the deleted price preserved.*
  • clsNationalDrugCollection cannot be populated by a bulk retrieve; it exists purely as an in-memory holder for individually-created or individually-retrieved drug objects.*

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts.

Sources

  • Business\SAIF.Business.Claim.MedicalBill\clsNationalDrug.vb
  • Business\SAIF.Business.Claim.MedicalBill\clsNationalDrugPrice.vb
  • Business\SAIF.Business.Claim.MedicalBill\clsNationalDrugPriceCollection.vb
  • Business\SAIF.Business.Claim.MedicalBill\clsNdcPriceHistory.vb
  • Business\SAIF.Business.Claim.MedicalBill\clsNdcPriceHistoryCollection.vb
  • Business\SAIF.Business.Claim.MedicalBill\clsNationalDrugCollection.vb