---
title: "PK82396_MEDICAL_BILLS stored procedure documentation"
summary: Oracle procedure documentation for PK82396_MEDICAL_BILLS.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-07-28
library: SAIF.Business.Claim.MedicalBill
reference_type: package
---

# PK82396_MEDICAL_BILLS

## Overview

| Field | Value |
| --- | --- |
| **Schema** | WCIS_DBA |
| **Type** | PACKAGE |
| **Status** | Found in Oracle during Stage 1b analysis |
| **Calling libraries** | `SAIF.Business.Claim.MedicalBill` |
| **Calling classes** | `clsAuditServiceCollection`, `clsEOBNoteCollection`, `clsServiceAdjustmentCollection` |

## Purpose

This package is invoked by the library's BOF persistence or retrieval paths and is documented here from the calling-code contracts captured during Stage 1b analysis.

## Procedures / Methods

### GetAuditServices

| Field | Value |
| --- | --- |
| **Returns** | Result set or rowcount contract depends on the calling SQL wrapper. |
| **Called by** | `clsAuditServiceCollection` |

**Description:** Oracle procedure call surfaced through the BOF criteria wrapper.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `:inBillId` | Input | Unknown | — | Passed from clsAuditServiceCollection via BeforeRetrieve. |
| `recordset` | Output | REF CURSOR | — | Passed from clsAuditServiceCollection via BeforeRetrieve. |

### GetServiceAdjustments

| Field | Value |
| --- | --- |
| **Returns** | Result set or rowcount contract depends on the calling SQL wrapper. |
| **Called by** | `clsServiceAdjustmentCollection` |

**Description:** Oracle procedure call surfaced through the BOF criteria wrapper.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `:inBillId` | Input | Unknown | — | Passed from clsServiceAdjustmentCollection via BeforeRetrieve. |
| `:inAuditSeq` | Input | Unknown | — | Passed from clsServiceAdjustmentCollection via BeforeRetrieve. |
| `recordset` | Output | REF CURSOR | — | Passed from clsServiceAdjustmentCollection via BeforeRetrieve. |

### GetEOBNotes

| Field | Value |
| --- | --- |
| **Returns** | Result set or rowcount contract depends on the calling SQL wrapper. |
| **Called by** | `clsEOBNoteCollection` |

**Description:** Oracle procedure call surfaced through the BOF criteria wrapper.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `:inBillId` | Input | Unknown | — | Passed from clsEOBNoteCollection via BeforeRetrieve. |
| `recordset` | Output | REF CURSOR | — | Passed from clsEOBNoteCollection via BeforeRetrieve. |

## Business Rules

## PR-001: Service adjustments are sorted so code `FT` appears first, `PA` appears last, and all other codes sort in the middle.

| Field | Value |
| --- | --- |
| **Statement** | Service adjustments are sorted so code `FT` appears first, `PA` appears last, and all other codes sort in the middle. |
| **Trigger** | `CASE adj.AUDIT_ADJUSTMENT_CODE WHEN FT THEN 0 WHEN PA THEN 999 ELSE 1 END`. |
| **Enforcement** | PK82396_MEDICAL_BILLS.GetServiceAdjustments |
| **Outcome** | Consumers receive a stable adjustment ordering for the bill audit UI. |
| **Confidence** | Source-backed |
| **Source** | [PK82396_MEDICAL_BILLS.GetServiceAdjustments](#getserviceadjustments) |

---

## PR-002: Service descriptions prefer bill service description text and fall back to generic drug name when the service code is a national drug code.

| Field | Value |
| --- | --- |
| **Statement** | Service descriptions prefer bill service description text and fall back to generic drug name when the service code is a national drug code. |
| **Trigger** | `NVL(CD.SERVICE_DESCR_TEXT, DRG.GENERIC_NAME)` with date-effective joins. |
| **Enforcement** | PK82396_MEDICAL_BILLS.GetAuditServices |
| **Outcome** | Audit-service retrieval returns the most specific available description. |
| **Confidence** | Source-backed |
| **Source** | [PK82396_MEDICAL_BILLS.GetAuditServices](#getauditservices) |

---

## PR-003: EOB note retrieval is always restricted to the requested bill identifier

| Field | Value |
| --- | --- |
| **Statement** | EOB note retrieval is always restricted to the requested bill identifier. |
| **Trigger** | `WHERE bill_id = inBillId`. |
| **Enforcement** | PK82396_MEDICAL_BILLS.GetEOBNotes |
| **Outcome** | Callers only receive note rows for the current medical bill. |
| **Confidence** | Source-backed |
| **Source** | [PK82396_MEDICAL_BILLS.GetEOBNotes](#geteobnotes) |

---

> These rules are also documented in [Business rules](../business-rules.md).

## Operations

| Operation | Description | Trigger |
| --- | --- | --- |
| Retrieve audit services | Returns audited bill service lines with effective-dated description enrichment. | `clsAuditServiceCollection.BeforeRetrieve`. |
| Retrieve service adjustments | Returns audited service adjustment rows for one bill/audit sequence pair. | `clsServiceAdjustmentCollection.BeforeRetrieve`. |
| Retrieve EOB notes | Returns explanation-of-benefits note rows for one bill. | `clsEOBNoteCollection.BeforeRetrieve`. |

> These operations are also listed in [Operations and processes](../operations-processes.md).

## Source Code

<details>
<summary>PL/SQL package specification</summary>

```sql
-- Actual PL/SQL spec retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.PK82396_MEDICAL_BILLS
PACKAGE          pk82396_medical_bills
AS

  TYPE ref_cursor IS REF CURSOR;

      PROCEDURE GetAuditServices(
      inBillId         IN   CBP_BILL.BILL_ID%TYPE,
      recordset        IN OUT ref_cursor);

    PROCEDURE GetServiceAdjustments(
      inBillId         IN   CBP_BILL.BILL_ID%TYPE,
      inAuditSeq       IN   CBP_AUDITED_SERVICE_ADJUSTMENT.AUDIT_SEQ_NUM%TYPE,
      recordset        IN OUT ref_cursor);

   PROCEDURE GetEOBNotes(
      inBillId         IN   CBP_BILL.BILL_ID%TYPE,
      recordset        IN OUT ref_cursor);

  FUNCTION ValidateProvReg(inBillId   IN cbp_bill.BILL_ID%TYPE,
                           inClaimNum IN CBP_BILL.SAIF_CLAIM_NUM%TYPE,
                           inProvider IN  PROVIDER_LOCATION.PROVIDER_SEQ_NUM%TYPE)
  RETURN NUMBER;
END;
 /
```

</details>

<details>
<summary>PL/SQL package/procedure body</summary>

```sql
-- Actual PL/SQL body retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.PK82396_MEDICAL_BILLS
PACKAGE BODY          pk82396_medical_bills
AS


   PROCEDURE GetAuditServices(
      inBillId         IN   CBP_BILL.BILL_ID%TYPE,
      recordset        IN OUT ref_cursor)
   IS
   BEGIN
      OPEN recordset FOR
            SELECT SVC.BILL_ID, SVC.AUDIT_SEQ_NUM, SVC.SERVICE_DATE, SVC.SERVICE_END_DATE,
                   nvl(SVC.BILL_SERVICE_CODE,SVC.NATIONAL_DRUG_CODE) AS service_code,
                   nvl(CD.SERVICE_DESCR_TEXT,DRG.GENERIC_NAME) AS service_descr, SVC.SERVICE_QUANTITY,
                   SVC.CHARGE_AMT, SVC.ALLOWED_AMT, SVC.MCO_ADMIN_FEE_AMT, SVC.BILL_PAYMENT_CATEGORY_CODE
            FROM cbp_Audited_Service svc
            LEFT OUTER JOIN cbp_service_description cd ON CD.BILL_SERVICE_CODE = SVC.BILL_SERVICE_CODE
                                    AND trunc(SVC.BILL_SERV_DESCR_EFF_DATE) BETWEEN
                                        trunc(CD.service_descr_eff_date) AND trunc(nvl(CD.service_descr_exp_date, SVC.BILL_SERV_DESCR_EFF_DATE + 1))
                                    AND CD.BILL_SERVICE_TYPE_CODE = SVC.BILL_SERVICE_TYPE_CODE
            LEFT OUTER JOIN cbp_national_drug DRG ON DRG.NATIONAL_DRUG_CODE = SVC.NATIONAL_DRUG_CODE
                                    AND trunc(SVC.SERVICE_DATE) BETWEEN trunc(DRG.EFF_DATE) AND trunc(nvl(DRG.EXP_DATE,SVC.SERVICE_DATE + 1))
            WHERE bill_id = inBillId
            ORDER BY SVC.SERVICE_DATE, nvl(SVC.BILL_SERVICE_CODE,SVC.NATIONAL_DRUG_CODE);
   END  GetAuditServices;

    PROCEDURE GetServiceAdjustments(
      inBillId         IN   CBP_BILL.BILL_ID%TYPE,
      inAuditSeq       IN   CBP_AUDITED_SERVICE_ADJUSTMENT.AUDIT_SEQ_NUM%TYPE,
      recordset        IN OUT ref_cursor)
   IS
   BEGIN
      OPEN recordset FOR
        SELECT ROWNUM AS SVC_ADJ_ID, adj.BILL_ID, adj.AUDIT_SEQ_NUM,
               adj.AUDIT_ADJUSTMENT_CODE, adj.AUDIT_ADJUSTMENT_AMT, adj.update_Date, ADJ.UPDATE_NUM, ADJ.UPDATE_USER,
                CASE adj.AUDIT_ADJUSTMENT_CODE
                  WHEN 'FT' THEN 0
                  WHEN 'PA' THEN 999
                  ELSE 1
                END AS SortAdjustCode
          FROM cbp_audited_service_adjustment adj
        WHERE adj.BILL_ID = inBillId
          AND adj.AUDIT_SEQ_NUM = inAuditSeq
          ORDER BY  SortAdjustCode, adj.update_date, adj.audit_seq_num, ADJ.AUDIT_ADJUSTMENT_CODE;
   END  GetServiceAdjustments;

     PROCEDURE GetEOBNotes(
      inBillId         IN   CBP_BILL.BILL_ID%TYPE,
      recordset        IN OUT ref_cursor)
   IS
   BEGIN
        OPEN recordset FOR
        SELECT  eob_note_id AS EOBNoteId, bill_id AS billid, eob_message_txt AS MessageText,  eob_message_code AS MessageCode,
                UPDATE_DATE, update_user, update_num
          FROM cbp_eob_note
        WHERE bill_id = inBillId
        ORDER BY eob_note_id;
  END GetEOBNotes;

  FUNCTION ValidateProvReg(inBillId   IN cbp_bill.BILL_ID%TYPE,
                           inClaimNum IN CBP_BILL.SAIF_CLAIM_NUM%TYPE,
                           inProvider IN  PROVIDER_LOCATION.PROVIDER_SEQ_NUM%TYPE)
  RETURN NUMBER
  IS
  v_cnt  number := 0;
  BEGIN
     BEGIN
        SELECT 1
        INTO v_cnt
        FROM cbp_bill bil, provider_location loc
        WHERE LOC.PROVIDER_LOCATION_SEQ_NUM = BIL.PROVIDER_LOCATION_SEQ_NUM
        AND BIL.BILL_ID = inBillId
        AND BIL.SAIF_CLAIM_NUM = inClaimNum
        AND LOC.PROVIDER_SEQ_NUM = inProvider
        AND trunc(BIL.CURRENT_STATUS_EFF_DATE) >= trunc(sysdate - 90);
     EXCEPTION
        WHEN NO_DATA_FOUND THEN
           v_cnt := 0;
     END;
     RETURN v_cnt;
  END ValidateProvReg;
END;
```

</details>

## Related documentation

- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

> **Note:** Statements marked with * were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts. Statements
> marked with ** could not be confirmed; treat as unknown until verified.

## Sources

- `Business\SAIF.Business.Claim.MedicalBill\clsAuditServiceCollection.vb`
- `Business\SAIF.Business.Claim.MedicalBill\clsEOBNoteCollection.vb`
- `Business\SAIF.Business.Claim.MedicalBill\clsServiceAdjustmentCollection.vb`