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 = '355682000794'
                            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 = '355682000794';

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,
    '355682000794', -- 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 = '355682000794' -- 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 = '355682000794'
                            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 <= ?;