Searching for Mobile Number (MSISDN) in System Queries

Overview

This article explains the process for retrieving account details associated with a mobile number (MSISDN) within a database system. The search involves navigating various tables, such as service instances and account relationships, while considering tenant and billing area constraints. The procedure ensures accurate identification of customer accounts by leveraging predefined account types and relationship mappings. Additionally, guidance is provided for patching missing subscriber data and re-establishing relationships when necessary.

Issues with MSISDN searchability can arise when data is absent from the tbltnetworkserviceinstance table, often due to migration or backend modifications. To resolve such issues, ensure terminated accounts have the correct status values in the table: COMMONSTATUSID set to 'CST03' and SYSTEMGENERATED set to 'Y'. No further table interventions are needed if these values are correctly configured. Accurate data correction and queries can restore MSISDN search functionality and maintain seamless account management.

Input Details

• MSISDN: Captured from the UI and set as networkElementName.
• Request VO Field: Mapped as mobileOrMSISDNNumber, not registeredMobileNumber.
• Default Account Type: The system assumes account type ACT09 (Customer Account) for this operation.

Query Flow

Step-by-Step Explanation

1. Find Service Instance by MSISDN:

• Query starts in the tbltnetworkserviceinstance table to locate the service instance associated with the MSISDN.

2. Join Relationships in tblmaccountaccountrel:

• First join uses account relationship type ART04 to link the service instance to the service.
• Second join uses account relationship type ART08 to link the service to the customer account.

These joins help identify the correct service and account by mapping relationships effectively.

3. Tenant and Billing Area Context:
The query further filters the results based on:

• Tenant (tblmaccount.tenantid).
• Billing Area (tblmaccount.billingareaid).

4. Pagination:

• To ensure that the result set is manageable, the query applies pagination by wrapping the final output with row number filters.

Complete Query Example

The following is the full query used for searching a mobile number (MSISDN):

SELECT
  *
FROM
  (
    SELECT
      ROWNUM AS ROWNUMBER,
      account.*
    FROM
      (
        SELECT
          DATA.*
        FROM
          (
            SELECT
              tblmaccount.accountnumber,
              tblmaccount.tenantid,
              tblmaccount.accountstatusid,
              tblmaccount.name,
              tblmaccount.accounttypeid,
              tblmaccount.systemgenerated,
              tblmaccount.accountid,
              tblmaccount.username AS username,
              tblmaccount.currencyid,
              NVL(
                tblmaccount.billingareaid, 'BAR0000'
              ) AS billingareaid,
              tblmaccount.emailid,
              custdem.strparam1 AS customercategory,
              custdem.strparam3 AS restrictionstatus,
              custdem.STRPARAM10 AS REGISTEREDMOBILENUMBER,
              tblmaccount.emailid AS REGISTEREDEMAIL
            FROM
              tblmaccount,
              tblmcustaccdem custdem
            WHERE
              tblmaccount.accountid = custdem.accountid
              AND tblmaccount.SYSTEMGENERATED = 'N'
              AND tblmaccount.accounttypeid = 'ACT09'
              AND tblmaccount.ACCOUNTID = (
                SELECT
                  accountrel.relationaccountid AS accountid
                FROM
                  (
                    SELECT
                      relationaccountid
                    FROM
                      tblmaccountaccountrel accountrel,
                      tbltnetworkserviceinstance tn
                    WHERE
                      accountrelationtypeid = 'ART04'
                      AND tn.serviceinstanceid = accountrel.accountid
                      AND tn.subscriberidentifier = '3XXXXXXXXXX4'
                      AND tn.SYSTEMGENERATED = 'N'
                  ) serviceaccount,
                  tblmaccountaccountrel accountrel
                WHERE
                  accountrelationtypeid = 'ART08'
                  AND serviceaccount.relationaccountid = accountrel.accountid
              )
              AND tblmaccount.tenantid IN (?) -- Optional for multi-tenant filtering
              AND tblmaccount.billingareaid IN (?) -- Optional based on billing area
              ) DATA
      ) account
    WHERE
      ROWNUM <= ?
  )
WHERE
  ROWNUMBER >= ?
  AND ROWNUMBER <= ?;

Patching Data for Missing Subscriber Identifier

Steps to Patch Missing Data

Step1: Verify the Missing Data:

• Check if the subscriberidentifier exists in the subscriberidentifier table:

SELECT
  *
FROM
  tbltnetworkserviceinstance
WHERE
  subscriberidentifier = '3XXXXXXXXXX4';

Step 2: Insert the Service Instance Record

If no record exists, perform the following steps:

1. Get the Service Account ID

SELECT
  accountid
FROM
  tblmaccount
WHERE
  accountnumber = 'SERVICE_ACCOUNT_NUMBER';

2. Insert the service instance

INSERT INTO tbltnetworkserviceinstance (
  serviceinstanceid, subscriberidentifier,
  systemgenerated, createdate, lastmodifieddate
)
VALUES
  (
    SEQ_NETWORKSERVICEINSTANCE.nextval,
    '3XXXXXXXXXX4',
    -- This is the MSISDN Number
    'N',
    -- Assuming 'N' is intended for the 'systemgenerated' field
    SYSDATE,
    -- Sets the current system date for 'createdate'
    SYSDATE -- Sets the current system date for 'lastmodifieddate'
    );

3. Create the Account Relationship

INSERT INTO tblmaccountaccountrel (
  accountid, relationaccountid, accountrelationtypeid,
  createdate, lastmodifieddate
)
VALUES
  (
    SEQ_NETWORKSERVICEINSTANCE.currval,
    -- Service instance ID from the sequence
    [SERVICE_ACCOUNT_ID],
    -- Replace with the actual service account ID from Step 1
    'ART04',
    -- Account relation type ID
    SYSDATE,
    -- Current system date for 'createdate'
    SYSDATE -- Current system date for 'lastmodifieddate'
    );

Step 3: Verify Relationships

Ensure that relationships are established correctly:

SELECT
  si.subscriberidentifier,
  sa.accountnumber AS service_account,
  ca.accountnumber AS customer_account
FROM
  tbltnetworkserviceinstance si
  JOIN tblmaccountaccountrel ar1 ON si.serviceinstanceid = ar1.accountid
  JOIN tblmaccount sa ON ar1.relationaccountid = sa.accountid
  JOIN tblmaccountaccountrel ar2 ON sa.accountid = ar2.accountid
  JOIN tblmaccount ca ON ar2.relationaccountid = ca.accountid
WHERE
  si.subscriberidentifier = '3XXXXXXXXXX4' -- This is the MSISDN Number
  AND ar1.accountrelationtypeid = 'ART04'
  AND ar2.accountrelationtypeid = 'ART08';

Step 4: Run the search query again

SELECT
  *
FROM
  (
    SELECT
      ROWNUM AS ROWNUMBER,
      account.*
    FROM
      (
        SELECT
          DATA.*
        FROM
          (
            SELECT
              tblmaccount.accountnumber,
              tblmaccount.tenantid,
              tblmaccount.accountstatusid,
              tblmaccount.name,
              tblmaccount.accounttypeid,
              tblmaccount.systemgenerated,
              tblmaccount.accountid,
              tblmaccount.username AS username,
              tblmaccount.currencyid,
              NVL(
                tblmaccount.billingareaid, 'BAR0000'
              ) AS billingareaid,
              tblmaccount.emailid,
              custdem.strparam1 AS customercategory,
              custdem.strparam3 AS restrictionstatus,
              custdem.STRPARAM10 AS REGISTEREDMOBILENUMBER,
              tblmaccount.emailid AS REGISTEREDEMAIL
            FROM
              tblmaccount
              JOIN tblmcustaccdem custdem ON tblmaccount.accountid = custdem.accountid
            WHERE
              tblmaccount.systemgenerated = 'N'
              AND tblmaccount.accounttypeid = 'ACT09'
              AND tblmaccount.accountid = (
                SELECT
                  accountrel.relationaccountid
                FROM
                  (
                    SELECT
                      relationaccountid
                    FROM
                      tblmaccountaccountrel accountrel
                      JOIN tbltnetworkserviceinstance tn ON tn.serviceinstanceid = accountrel.accountid
                    WHERE
                      accountrelationtypeid = 'ART04'
                      AND tn.subscriberidentifier = '3XXXXXXXXXX4'
                      AND tn.systemgenerated = 'N'
                  ) serviceaccount
                  JOIN tblmaccountaccountrel accountrel ON serviceaccount.relationaccountid = accountrel.accountid
                WHERE
                  accountrelationtypeid = 'ART08'
              )
              AND tblmaccount.tenantid IN (?) -- Optional if multi-tenant
              AND tblmaccount.billingareaid IN (?) -- Optional based on billing area
              ) DATA
      ) account
    WHERE
      ROWNUM <= ?
  )
WHERE
  ROWNUMBER >= ?
  AND ROWNUMBER <= ?;