The Registration module handles new user sign-up for the EKYC Demat Account Opening journey. When a customer enters their mobile number and taps Proceed, the backend performs eligibility checks (negative list, existing account dedupe, 90-day logic), creates a lead record, and triggers OTP delivery.
| Action Name | HTTP | Auth | Purpose | Repository Method |
SIGNUP_OLD | POST | Anonymous | Main registration (legacy) | SignUp() |
SignUpAsync | POST | Anonymous | Async registration (current) | SignUp() |
VALIDATEREFERALCODE | POST | Anonymous | Validate referral/BA code | ValidateReferalCode() |
CHECKMOBILEVERSION | POST | Anonymous | Check app version compatibility | CheckMobileVersion() |
INSERTUPDATESTAGEDETAILS | POST | Anonymous | Save/update user journey stage | InsertUpdateStageDetails() |
GETUSERDATA_TRUECALLER | POST | Anonymous | Get data from Truecaller SDK | GetUserData_TrueCaller() |
TRUECALLERCALLBACK | POST | Anonymous | Truecaller callback handler | TruecallerCallback() |
GETPINCODEDETAILS | POST | Anonymous | Validate/return pincode details | GetPincodeDetails() |
LIVEPHOTOLOCATIONDETAILS | POST | Authorized | Record live photo + location | LivePhotoLocationDetails() |
GeoLocation | POST | Anonymous | Record user geolocation | GeoLocation() |
Step-by-Step Backend Execution
1
Input Validation — CheckDangerousString() validates all input fields for SQL injection / XSS. chk_PhoneNo() validates Indian mobile format (10 digits, starts with 6/7/8/9). For NRI: chk_PhoneNo_NRI() with relaxed rules.
2
Application Type Resolution — Determines application type based on inputs:
• NRI + MOMEAST branch → ApplicationType = "DIRECT"
• AppLoginId = IFAFINCARE → ApplicationType = "CRM", FlowType = "DIY"
• CampaignName = FSFB3IN1 → FlowType = "APP"
• ApplicationType = PP + BACODE present → IsBaProspect = true
3
Call RegistrationRepository.SignUp() — Passes 40+ parameters including mobile, name, email, state, city, source, OTP, campaign, device info, UTM params, referral codes, branch codes.
4
Stored Procedure: [DIY].[USP_CLIENT_REGISTRATION_SJET] — This is the core registration SP (5,780+ lines). It performs:
• Dedupe check against TBL_DEDUPE_DATA_DUMP by mobile/email
• SSO user creation in MOSL_SSO.dbo.tblUser
• State/city validation against TBL_STATE_CITY_MASTER_LSQ
• Branch/RM assignment logic
• Campaign-specific routing
• Returns UserId + DEDUPE_PARAM (N/EPO/MPO/C2)
5
LeadSquare / Zoho CRM Lead Creation — If application is DIRECT, calls Proc_DedupeCheck_LeadSquare_LSQ_SJET to check CRM dedupe, then creates lead in LSQ or Zoho depending on IsZohoLead flag.
6
OTP Generation & Delivery — Generates OTP, stores in DB, sends via SMS cascade using SingleMessageService SOAP API (Netcore). Template from SMSTemplate.
7
Response — Returns GetLoginDetails with UserId, Status, Message, Token, and redirect information.
| Category | Key Parameters |
| Basic | @Name, @Email, @Mobile, @State, @City, @Password |
| Source / Campaign | @PSource, @PSourceDetail, @Source, @CampaignName, @utm_source, @utm_medium, @utm_campaign, @GCLID |
| Application | @ApplicationType, @AppLoginId, @ISNRI, @IsLeadDistribute, @IsSuperJet, @IsOcrUser |
| Device | @IsMobileApp, @DeviceType, @FirebaseToken, @MobileDeviceId, @AppVersion, @IpAddress |
| Employee / RM | @BranchRM_Empcode, @RMCode, @Emp_Code, @Emp_Name, @DAD_Advisor_Emp_Code |
| Referral | @ReferalClientCode, @Referee_Code, @Referee_Name, @Referee_Email, @Referee_Mobile |
A
Dedupe Check: Checks TBL_DEDUPE_DATA_DUMP for existing mobile/email. Sets DEDUPE_PARAM = N (new) / EPO (email prev owned) / MPO (mobile prev owned) / C2 (caught in stage transition)
B
Product Transition: If deduped, checks PRODUCT_TRANSITION_FLAG and transfers to acquiring business
C
State/City Validation: Validates against TBL_STATE_CITY_MASTER_LSQ with fallback to MST_CITY_STATE
D
Branch/RM Assignment: Complex routing for CRM sources (Alliance, IDFC, INDUSIND, AUSFBDIRECT, SBOM, DBIZ, SIB, CSFB). IFSC-to-RM mapping for INDUSIND.
E
Campaign Logic: IIBLMICROSITE, AUFSB, CBI, BA Self-Direct each have custom RM assignment and branch routing
F
SSO User Creation: Creates user in MOSL_SSO.dbo.tblUser + USR_ADDITIONAL_INFO if not exists. Generates encrypted credentials.
G
INSERT into TBL_DEDUPE_DATA_DUMP: Creates the main registration record
| Stored Procedure | Called From | Purpose | Key Tables |
[DIY].[Proc_DedupeCheck_LeadSquare_LSQ_SJET] |
RegistrationRepository |
CRM dedupe check. Checks if mobile/email exists in CRM. Returns LSQ_LEADID, OPPORTUNITY_ID. Checks TBL_FFF_NOT_ALLOW for fraud blocked list. |
TBL_DEDUPE_DATA_DUMP TBL_LSQ_LEADID_STAGEDETAILS TBL_DAD_TO_RETAIL_TRANSFERRED_LEADS TBL_FFF_NOT_ALLOW |
[DIY].[Proc_Dedupe_UserIDExistProcess_Revamp_LSQ_SJET] |
RegistrationRepository |
Checks if OAO UserId already exists. Handles product transition. Routes non-DIRECT apps to NEW_CRM database. |
TBL_DEDUPE_DATA_DUMP TBL_OAO_DETAILS |
[DIY].[proc_execute_dad_dedupe_discard_LSQ_SJET] |
RegistrationRepository |
Discards/removes existing duplicate lead for re-registration. Handles DAD-to-Retail transfer. |
TBL_DEDUPE_DATA_DUMP TBL_ZOHOEKYC_MAPPER |
[DIY].[USP_VALIDATE_REFERALCODE_SJET] |
RegistrationController |
Validates referral code against MOSL_FEED_CLIENT_DETAILS.CL_code. Returns referral name if valid. |
MOSL_FEED_CLIENT_DETAILS |
[DIY].[USP_GET_REGISTRATIONDETAILS_RESUME_SJET] |
RegistrationRepository |
Resume existing registration. Looks up mobile in TBL_DEDUPE_DATA_DUMP. Checks if OTP verified, if LSQ/Zoho ID exists. May delete only-registered clients. |
TBL_DEDUPE_DATA_DUMP TBL_CLIENT_STAGEDETAILS TBL_LSQ_LEADID_STAGEDETAILS |
[DIY].[USP_CHECK_MOBILE_EXISTS_BO_EXP_SJET] |
RegistrationRepository |
Checks if mobile already exists in back-office. Checks across MOSL_FEED, TBL_DPMASTER, TBL_PAN_MOBILE_EMAIL_WHITELIST. |
VW_MOSL_FEED_CLIENT_DETAILS TBL_DPMASTER TBL_PAN_MOBILE_EMAIL_WHITELIST |
[DIY].[USP_CHECK_MULTIPLE_USER_EXISTS_SJET] |
LoginRepository |
Counts records in TBL_DEDUPE_DATA_DUMP matching mobile/email with DedupParam IN (N, EPO, MPO). Returns 1 if multiple users found. |
TBL_DEDUPE_DATA_DUMP |
[DIY].[USP_BYPASS_MOBILE_EMAIL_PAN_SJET] |
RegistrationRepository |
Bypass logic for duplicate mobile/email/PAN. Checks INACTIVE, PMS, OWNER (Branch/SubBroker) conditions against MOSL_FEED tables. Sets ISBYPASS flag. |
MOSL_FEED_CLIENT_DETAILS MOSL_FEED_BRANCH MOSL_FEED_SUBBROKERS TBL_TRACK_BYPASS_MOBILE_EMAIL_PAN |
[DIY].[USP_CHECK_NINTY_DAY_LOGIC_LSQ_SJET] |
LoginRepository |
90-day dormant lead check. Queries Tbl_DAD_TO_RETAIL_TRANSFERRED_LEADS for non-dormant leads. Returns OpportunityId and RelatedProspectId. |
Tbl_DAD_TO_RETAIL_TRANSFERRED_LEADS |
[DIY].InsertApplicationStepsLog_SJET |
RegistrationRepository |
Logs each registration step for tracking/audit |
Application Steps Log table |
| Table | R/W | Purpose | Key Columns |
TBL_DEDUPE_DATA_DUMP | R/W | Primary registration/dedupe table. Every registration creates or updates a record here. | MOBILE, EMAIL, NAME, PANNO, DedupParam (N/EPO/MPO/C2), ISMOBILEBYPASS, ISEMAILBYPASS, ApplicationType, PRODUCT_TRANSITION_FLAG, OTPMobile, PSOR CE |
TBL_OAO_DETAILS | R/W | Account opening opportunity details. Stores campaign, product, ISJET, ISSUPERJET flags. | USERID, ProductTransitionFlag, ISOPPORTUNITY, ISSUPERJET, ISSUPERAPP, ISPANBYPASS |
TBL_CLIENT_STAGEDETAILS | R/W | Stage progression tracking. Tracks which stage each user has completed. | USERID, CLIENTCODEGENERATED, AccountType, HolderType |
TBL_LSQ_LEADID_STAGEDETAILS | R/W | LeadSquare lead ID and stage tracking. | USERID, LSQ_LEADID, OPPORTUNITY_ID |
MOSL_SSO.dbo.tblUser | R/W | SSO user account. Created during registration. | USER_LOGINNAME, Encrypted Password, IP, Device |
MOSL_SSO.dbo.USR_ADDITIONAL_INFO | Write | Additional SSO user info | User demographics, device info |
TBL_TRACK_BYPASS_MOBILE_EMAIL_PAN | Write | Logs bypass decisions for audit | TYPE, VALUE, VALUE_TYPE, ISBYPASS, CREATEDDATE |
| Table | Purpose | Used In |
MOSL_FEED_CLIENT_DETAILS | Back-office client master. Used for mobile/email/PAN duplicate check and bypass logic. | USP_BYPASS_MOBILE_EMAIL_PAN, USP_CHECK_MOBILE_EXISTS_BO_EXP, USP_VALIDATE_REFERALCODE |
MOSL_FEED_BRANCH | Branch master with mobile/email for branch owners | USP_BYPASS_MOBILE_EMAIL_PAN |
MOSL_FEED_SUBBROKERS | Sub-broker master with mobile/email for sub-broker owners | USP_BYPASS_MOBILE_EMAIL_PAN |
TBL_STATE_CITY_MASTER_LSQ | State/city reference for LSQ compliance | USP_CLIENT_REGISTRATION_SJET |
MST_CITY_STATE | Fallback city/state master | USP_CLIENT_REGISTRATION_SJET |
TBL_PAN_MOBILE_EMAIL_WHITELIST | Whitelist for bypassing duplicate checks | USP_CHECK_MOBILE_EXISTS_BO_EXP |
TBL_DPMASTER | DP master with CM_MOBILE and CB_PANNO | USP_CHECK_MOBILE_EXISTS_BO_EXP |
TBL_APPLICATIONCONFIG | System configuration / feature flags | USP_CLIENT_REGISTRATION_SJET |
VW_CRM_EMPLOYEEWISE_BRANCH_TRADERCODE | Employee-branch mapping view | USP_CLIENT_REGISTRATION_SJET |
TBL_ALLIANCE_RM_BRANCH_LIST | Alliance RM routing | USP_CLIENT_REGISTRATION_SJET |
TBL_IDFCCustomersDetails | IDFC customer data | USP_CLIENT_REGISTRATION_SJET |
TBL_FFF_NOT_ALLOW | Fraud / blocked list | Proc_DedupeCheck_LeadSquare_LSQ_SJET |
Tbl_DAD_TO_RETAIL_TRANSFERRED_LEADS | DAD-to-Retail migration tracking | USP_CHECK_NINTY_DAY_LOGIC_LSQ_SJET |
TBL_ZOHOEKYC_MAPPER | Zoho-EKYC system mapping | proc_execute_dad_dedupe_discard |
MOSL_SDLC_EMPLOYEEDETAILS | Employee details for RM assignment | USP_CLIENT_REGISTRATION_SJET |
1
USP_CLIENT_REGISTRATION_SJET checks TBL_DEDUPE_DATA_DUMP for existing mobile/email match
2
Sets DEDUPE_PARAM: N = new, EPO = email previously owned, MPO = mobile previously owned, C2 = caught in transition
3
Proc_DedupeCheck_LeadSquare_LSQ_SJET checks CRM-level dedupe including TBL_DAD_TO_RETAIL_TRANSFERRED_LEADS
4
USP_CHECK_MOBILE_EXISTS_BO_EXP_SJET checks back-office (MOSL_FEED, DPMASTER) for active Demat account with same mobile
5
USP_BYPASS_MOBILE_EMAIL_PAN_SJET evaluates if bypass is allowed: INACTIVE account, PMS-only, OWNER (Branch/SubBroker)
6
USP_CHECK_NINTY_DAY_LOGIC_LSQ_SJET checks 90-day rule on Tbl_DAD_TO_RETAIL_TRANSFERRED_LEADS
| External System | Purpose in Registration | Call Type | Failure Handling |
| LeadSquare CRM | Create lead, create opportunity, activity logging | Async HTTP | Lead created in EKYC DB. LSQ sync retried later. |
| Zoho CRM | Create/update lead (alternative to LSQ based on IsZohoLead flag) | HTTP PUT /crm/v7/Leads/{id} | Lead Status update deferred. Token refresh on 401. |
| Netcore SMS (SingleMessageService) | OTP delivery via SMS | SOAP Async | Logged to DB. Customer cannot proceed without OTP. |
| Firebase | Short URL generation, push notification setup | HTTP | Non-blocking. Graceful degradation. |
| Truecaller SDK | Auto-fill name/mobile from Truecaller profile | SDK callback | Falls back to manual entry. |
| SuperApp API | Cloud lead management, client code sync | HTTP | Lead created locally. SuperApp sync retried. |
| BRD Requirement | Current Code Status | Action Needed |
| Mobile number + Full Name + 3 mandatory consents as input |
Current code accepts mobile + many more fields. Email is optional at registration. No explicit consent tracking per version. |
Simplify registration input. Add consent versioning: consent_id, version, timestamp, ip_address, platform, lead_id |
| Mobile SHA-256 hash stored. Plain mobile never in DB. |
Current code stores plain mobile in TBL_DEDUPE_DATA_DUMP |
New: Implement SHA-256 hashing. Store mobile_hash only. |
| Negative List check on mobile + IP |
Current code has TBL_FFF_NOT_ALLOW fraud list but no IP-based negative list |
New: Add MOFSL negative list + SEBI debarred list check API. IP check. |
| CBOS check for existing active Demat account |
Current: USP_CHECK_MOBILE_EXISTS_BO_EXP_SJET checks MOSL_FEED + DPMASTER |
Map existing BO check to CBOS API. Add CBOS_DEDUPE_SKIPPED flag if API unavailable. |
| In-progress application on old platform (< 90 days) → redirect |
Current: USP_CHECK_NINTY_DAY_LOGIC_LSQ_SJET handles 90-day logic |
Existing. Enhance to differentiate old vs new platform. |
| Same channel/BA/RM resume; different channel block |
Current dedupe does not check channel/BA/RM match |
New: Add channel, BA code, RM code comparison in dedupe logic. |
| OTP: 4-digit, 5-min TTL in Redis only, never in DB |
Current: OTP stored in DB. Delivery via SMS only (Netcore SOAP). |
New: Move OTP to Redis. 4-digit. TTL 5 min. Cascade: SMS → WhatsApp → Push → RCS. |
| OTP max 5 wrong attempts → DROPPED. Max 3 resends in 30 min. |
Current has attempt limits but different thresholds. |
Align thresholds. Add DROP_OTP_LOCKED code. |
| Lead creation retry: up to 3 times at 2-sec intervals |
Current: Single attempt with generic error |
New: Implement retry with backoff. |
| Consent save must succeed before OTP is sent |
Current: No explicit consent-before-OTP gate |
New: Consent persistence as prerequisite to OTP. |
| Negative List / CBOS unavailable → flag on lead, proceed |
Current: No graceful degradation flags |
New: Add NEGATIVE_LIST_CHECK_SKIPPED, CBOS_DEDUPE_SKIPPED flags. |
| Session: 15-min timeout, Redis-backed |
Current: Session in .NET context, not Redis-backed |
New: Redis session with 15-min TTL, reset on interaction. |
| Area | Current Implementation | BRD 3.0 Requirement | Gap Severity |
| Data Privacy | Plain mobile stored in DB | SHA-256 hash only | HIGH |
| Consent Tracking | No versioned consent records | 3 consents with version, timestamp, IP, platform | HIGH |
| OTP Storage | Database | Redis only, never in DB | MEDIUM |
| OTP Delivery | SMS only (Netcore SOAP) | Cascade: SMS → WhatsApp → Push → RCS | MEDIUM |
| Negative List | TBL_FFF_NOT_ALLOW (fraud list) | MOFSL Negative List + SEBI Debarred List + IP check | MEDIUM |
| Channel-based Dedupe | Not implemented | Same channel/BA/RM = resume; different = block | HIGH |
| Session Management | .NET context based | Redis with 15-min TTL | MEDIUM |
| Lead Creation Retry | Single attempt | 3 retries at 2-sec intervals | LOW |
| Drop/Error Codes | Generic error messages | Specific codes: DROP_NEGATIVE_LIST, BE_REG_001-004, DROP_OTP_LOCKED | MEDIUM |
| Audit Trail | Basic logging via InsertUpdateAPI_Flagwise | Full audit: lead_id, stage_at_reset, timestamp, rm_id | MEDIUM |