Skip to main content

CLR CDR

Document Version: 1.0 Last Updated: 25-01-2026 Maintained By: Niha

Table of Contents​

  1. Overview
  2. Table Schema
  3. Field Reference
  4. Business Logic
  5. Data Quality
  6. Appendix

Overview​

Summary​

This document provides detailed documentation for the Resource Clearance CDR (CLR) table, which records free unit expiry, balance clearance, and resource cleanup events for billing purposes.

Free unit expirations can be joined with PE_FREE_UNIT table to identify the exact offering which expired.

Tables discussed​

Table NameDescriptionImportant Columns
CLR CDRMain CDR table for Resource ClearanceCDR_ID, SUBSCRIBER_KEY, BILL_CYCLE_ID
PE_FREE_UNITFree unit instances linked to offeringsFREE_UNIT_ID, OFFERING_ID
OfferingsOffering metadataOFFERING_ID

Table Schema​

Field Reference​

1. CDR Identifiers​

Fields that uniquely identify clearance records and events.

CDR_ID​

  • Description: Unique identifier for each CLR CDR record, auto-generated by the CBS system
  • Business Logic: Primary key for the clearance event
  • Example: 123456789012345

CUST_LOCAL_START_DATE​

  • Description: Clearance event timestamp in local timezone
  • Business Logic:
    • Time when the clearance event occurred
    • Used for billing cycle assignment and reporting
    • Typically represents when free units expired or balance was cleared
  • Example: 2026-01-25 14:30:45
  • Timezone: Local time (Maldives: UTC+5)

BILL_CYCLE_ID​

  • Description: Billing cycle identifier
  • Business Logic:
    • Links clearance event to specific billing cycle
    • Used to track which billing period the clearance belongs to
    • Important for end-of-cycle reconciliation
  • Example: 202601
  • Format: Typically YYYYMM or cycle-specific format
  • Use Cases: Billing cycle reporting, period-end analysis

2. Subscriber Information​

Fields identifying the subscriber and account hierarchy.

PRI_IDENTITY​

  • Description: Primary identity - MSISDN (mobile number)
  • Format: International format without + symbol
  • Example: 9607123456
  • Business Logic: The subscriber whose resources are being cleared

SUBSCRIBER_KEY​

  • Description: Service ID from CRM system
  • Business Logic: Unique identifier for the service instance, links to CRM
  • Example: 5001234567
  • Joins: Link to subscriber dimension tables

ACCOUNT_KEY​

  • Description: Payment account ID from CRM
  • Business Logic: Multiple subscribers can share same account (family plans)
  • Example: 3001234567
  • Joins: Link to account dimension tables

UserState​

  • Description: Numeric string of seven digits, in the format CCMMMMM. The first and second digits C indicate the life cycle state of a subscriber. The first digit C indicates the state of a prepaid subscriber, and the second digit C indicates the state of a postpaid subscriber. The options are as follows:
    • 0 - Idle
    • 1 - Active
    • 2 - Suspended
    • 3 - Disable
    • 4 - Pool
    • 5 - Pool without activation
  • Business Logic: Service status at time of clearance event

OBJ_TYPE​

  • Description: Object type being cleared
  • Valid Values:
    • S - Subscriber (individual service)
    • A - Account (account-level clearance)
    • G - Subscriber Group
  • Business Logic: Determines what entity the clearance applies to
  • Example: S

OBJ_ID​

  • Description: Object identifier corresponding to OBJ_TYPE
  • Business Logic:
    • When OBJ_TYPE = 'S': OBJ_ID = SUBSCRIBER_KEY
    • When OBJ_TYPE = 'A': OBJ_ID = ACCOUNT_KEY
    • When OBJ_TYPE = 'G': OBJ_ID = Subscriber Group ID
  • Example: 5001234567

3. Balance​

Fields capturing what balances were cleared.

4. Free Units​

Fields capturing what free units were cleared.

FREE_UNIT_ID_[1-10]​

  • Description: Unique ID of free unit instance
  • Business Logic:
    • Links to PE_FREE_UNIT table for offering details
    • Up to 10 different free unit instances can be consumed in one CDR
    • NULL if no free unit consumed in this slot
  • Join: JOIN PE_FREE_UNIT ON FREE_UNIT_ID_1 = PE_FREE_UNIT.FREE_UNIT_ID

CHG_AMOUNT_[1-10]​

  • Description: Free unit amount for this instance
  • Business Logic: The actual usage expended (cleared) from this free unit instance

FU_MEASURE_ID_[1-10]​

  • Description: Free unit measurement ID
  • Business Logic: Defines the unit of CHG_AMOUNT

Business Logic​

Clearance Event Types​

1. Free Unit Expiry​

  • Trigger: Free units reach their expiry date
  • Business Rule: Unused allowances are cleared at end of validity period
  • Impact: Subscriber loses unused voice/data/SMS allowances
  • Example: 5GB of unused data expires at end of month

2. Balance Expiry​

  • Trigger: Prepaid balance or bonus balance reaches expiry date
  • Business Rule: Expired balances are removed from account
  • Impact: Subscriber loses monetary balance
  • Example: Promotional bonus MVR 50 expires after 30 days

Data Quality​

Validation Rules​

Mandatory Field Checks​

  • CDR_ID, SUBSCRIBER_KEY, CUST_LOCAL_START_DATE, CLEARANCE_TYPE must not be NULL
  • At least one of CLEARED_BALANCE_AMOUNT or CLEARED_FREE_UNIT_AMOUNT must be > 0

Logical Consistency Checks​

  • CLEARED_BALANCE_AMOUNT >= 0
  • CLEARED_FREE_UNIT_AMOUNT >= 0
  • CUST_LOCAL_START_DATE >= EXPIRY_DATE (clearance happens at or after expiry)
  • If FREE_UNIT_TYPE is populated, CLEARED_FREE_UNIT_AMOUNT should be > 0

Appendix​

Table NamePurposeJoin Condition
PE_FREE_UNITFree unit instances and offering mappingsFREE_UNIT_ID_[1-10] = FREE_UNIT_ID
PE_FREE_UNIT_TYPEFree unit bucket typesVia PE_FREE_UNIT
[SUBSCRIBER]Subscriber attributesSUBSCRIBER_KEY
[ACCOUNT]Account attributesACCOUNT_KEY
[OFFERING]Offering/plan detailsMainOfferingID, LastEffectOffering, OpposeMainOfferingID

Change Log​

VersionDateAuthorChanges
1.02026-01-25NihaInitial documentation

Document End