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

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

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

These rules are also documented in Business rules.

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.

Source Code¶

PL/SQL package specification

-- 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;
 /

PL/SQL package/procedure body

-- 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;

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

PK82396_MEDICAL_BILLS¶

Overview¶

Purpose¶

Procedures / Methods¶

GetAuditServices¶

Parameters¶

GetServiceAdjustments¶

Parameters¶

GetEOBNotes¶

Parameters¶

Business Rules¶

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

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.¶

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

Operations¶

Source Code¶

Related documentation¶

Sources¶

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