Skip to main content

Upsert Customer - Assign Customer Segments Implementation Analysis (On-Prem)

Analysis of existing code of “Assign Customer Segments” feature​

Input Request:​

  • Header
    • CustomerHeaderType ( Source System, User, Brand)
  • Params
    • Customer Code
    • SCV Code
    • Customer Segments (SegmentationsType)
      • Segments (SegmentType)
        • SegmentType
          • Brand ( Brand code, Brand name)
          • Segment Classification ( Segment Classification Code, Segment Classification Value, Segment Classification Name, Segment Classification Description)
          • Unassign Segment Flag

Implementation:​

  • IF customer code is not given
    • a) check whether given SCV code is valid or not. (DB query)
      • if valid —> get customer code based on SCV code from DB (DB query)
  • OTHERWISE
    • check whether given customer code is valid or not (DB query)
      • if invalid —> throw an exception
  • Create UTIL BEAN
    • Set customer code, data source connection, customer header (Customer Header Type)
    • Put UTIL BEAN into JNDI registry (transactionRegistry) ?
  • Create CustomerCompleteType
    • Set customerCode
  • Retrieve Customer Details by Customer Code
  • LOG response time (performance)
  • UtilBean —> Set existing customer VO with customer details
  • IF customer segments are not empty from request params
    • Get customer segments from request params
    • Build customer segment VO list from list of customer segments)
      • SegmentationsTypeVO ( brand name,
        • brand code,
        • classification,
        • classification code,
        • classification value,
        • classification description )
      • prepare segment VO list
      • prepare segment VO list for deletion (UnassignSegmentFlag is true)
      • UTIL BEAN —> set list of segment VO list for deletion.
    • UTIL BEAN —> get existing customer segments and
      • Set classification code with value
      • Set classification value with code
    • Get customer segments to be deleted
    • Mark respective existing customer segments for deletion
    • Set customer segments list with existing customer segments list from UTIL BEAN
    • Validate multiple segments allowed for segments list (DB Query)
  • Create assign customer segment response MAP
  • Put customer into map
  • Below steps are implemented through INFORMATICA. Need to change the process in order to perform the below steps according to modern design
    • Create Siperian Client from Get SIFC Client (To write new logic according to modernisation)
    • Call dummy upsert using Siperian client (To write new logic according to modernisation)
      • Get customer row id (getPTRPartyRoleRowId) by customer code (DB Query)
      • Create new PutRequest()
      • Create new Record()
      • Create new RecordKey()
      • From UTILBEAN’s customer header, get sour e system, last update user details.
      • Set source system into RecordKey
      • Set RecordKay into PutRequest
      • Set generate source key as false into PutRequest
      • Set source key as “source system” + “.” + “customer row id”
      • Set SiberianObjectUid as “BASE_OBJECT.C_PTR_CUST”
      • Set RowId of RecordKey with the response from DB query
      • If Valid row id,
        • Set Record into PutRequest
        • Process the PutRequest through siperian client
  • Set response code as “CMDWS-200”, Status as “assignment is successful”
  • Log if it is not a KEY CLIENT
  • Log deleted segments

DB Queries:​

  • To find given SCV code is valid or not

SELECT COUNT(1) CNT FROM PKG_RETRIEVE_CUSTOMER WHERE ALT_CODE_TYPE = 'PTR_ALT_CODES.CSTSCV' AND HUB_STATE_IND = 1 AND CODE = (?)

  • To find customer code based on SCV code

WITH SCV_NUMBER AS ( SELECT ROWID_OBJECT, PTR_ROWID, CODE CUST_SCV_NUMBER FROM C_PTR_ALT_CODES WHERE HUB_STATE_IND = 1 AND TYP_TYPE_CD = 'PTR_ALT_CODES.CSTSCV' AND CODE = (?) ),

SCV_CODE AS ( SELECT ROWID_OBJECT, PTR_ROWID, CODE CUST_SCV_CODE FROM C_PTR_ALT_CODES WHERE HUB_STATE_IND = 1 AND TYP_TYPE_CD = 'PTR_ALT_CODES.CSTFCT' )

SELECT SCV_NUMBER.PTR_ROWID, SCV_NUMBER.CUST_SCV_NUMBER, SCV_CODE.CUST_SCV_CODE FROM SCV_NUMBER LEFT OUTER JOIN SCV_CODE ON ( SCV_CODE.PTR_ROWID = SCV_NUMBER.PTR_ROWID AND SUBSTR(SCV_NUMBER.CUST_SCV_NUMBER,4) = SUBSTR(SCV_CODE.CUST_SCV_CODE,3) )

  • To find customer code is valid or not

WITH CUSTOMER_IDENTIFIER_INFO AS ( SELECT * FROM ( SELECT PTR_ROWID CUSTOMER_MDM_ID, TYP_TYPE_CD, CODE FROM C_PTR_ALT_CODES WHERE HUB_STATE_IND = 1 AND TYP_TYPE_CD IN ('PTR_ALT_CODES.CSTSCV','PTR_ALT_CODES.CSTFCT')

AND EXISTS ( SELECT 1 FROM C_PTR_ALT_CODES KK WHERE C_PTR_ALT_CODES.PTR_ROWID = KK.PTR_ROWID AND KK.CODE = (?) )

) PIVOT (MIN(CODE) FOR (TYP_TYPE_CD) IN ( 'PTR_ALT_CODES.CSTSCV' AS CUST_SCV_CODE, 'PTR_ALT_CODES.CSTFCT' AS CUST_FACT_CODE)) ) SELECT RPAD(DECODE(CUST_MDM_ID,'99999999999999', 'NOT AVAILABLE',CUST_MDM_ID),14,' ') CUST_MDM_ID, CUST_SCV_CODE, CUST_FACT_CODE FROM ( SELECT DISTINCT CUST_MDM_ID, CUST_SCV_CODE, CUST_FACT_CODE, DENSE_RANK() OVER (ORDER BY CUST_MDM_ID) D_RANK FROM ( SELECT CUSTOMER_IDENTIFIER_INFO.CUSTOMER_MDM_ID CUST_MDM_ID, CUSTOMER_IDENTIFIER_INFO.CUST_SCV_CODE, CUSTOMER_IDENTIFIER_INFO.CUST_FACT_CODE FROM CUSTOMER_IDENTIFIER_INFO INNER JOIN C_PTR_CUST ON C_PTR_CUST.PTR_CUST_ROWID = CUSTOMER_IDENTIFIER_INFO.CUSTOMER_MDM_ID WHERE C_PTR_CUST.HUB_STATE_IND = 1 AND ( NVL(C_PTR_CUST.PTR_CUST_STAT_CD ,'A') = 'A' OR ( NVL(C_PTR_CUST.PTR_CUST_STAT_CD ,'S') = 'S' and C_PTR_CUST.NMB_FLAG='Y') ) UNION ALL SELECT '99999999999999', '99999999999999','99999999999999' FROM DUAL ) ) WHERE D_RANK = 1

  • To check whether multiple segments are allowed or not

WITH SEGMENT_INFO AS ( SELECT C_PTR_CLSSN_TYP.ROWID_OBJECT SEGMENT_TYP_MDM_ID, C_PTR_REL_CLSSN.ROWID_OBJECT SEGMENT_VAL_MDM_ID, C_PTR_CLSSN_TYP.MULTI_VAL_ALWD_IND, C_PTR_CLSSN_TYP.CODE SEGMENT_TYP_CD, C_PTR_CLSSN_TYP.NAME SEGMENT_TYP_NM, C_PTR_REL_CLSSN.CODE SEGMENT_VAL_CD, C_PTR_REL_CLSSN.NAME SEGMENT_VAL_NM, C_PTR_REL_CLSSN.DESCRIPTION SEGMENT_VAL_DESC FROM C_PTR_CLSSN_TYP INNER JOIN C_PTR_REL_CLSSN ON C_PTR_REL_CLSSN.CLSSN_TYPE_CD = C_PTR_CLSSN_TYP.CODE ), BRAND_INFO AS ( SELECT C_PTR_PARTY_RL.ROWID_OBJECT BRAND_MDM_ID, C_PTR_PARTY_RL.PARTY_RL_NM BRAND_NAME, C_PTR_ALT_CODES.CODE BRAND_CODE FROM C_PTR_PARTY_RL , C_PTR_ALT_CODES WHERE C_PTR_PARTY_RL.ROWID_OBJECT = C_PTR_ALT_CODES.PTR_ROWID AND C_PTR_PARTY_RL.HUB_STATE_IND = 1 AND C_PTR_ALT_CODES.HUB_STATE_IND = 1 AND C_PTR_PARTY_RL.RL_TYP_CD = 'CARR' AND C_PTR_ALT_CODES.TYP_TYPE_CD = 'PTR_ALT_CODES.LRA_CD' ), BRAND_SEGMENT AS ( SELECT BRAND_INFO.BRAND_MDM_ID, BRAND_INFO.BRAND_NAME, BRAND_INFO.BRAND_CODE, SEGMENT_INFO.SEGMENT_TYP_MDM_ID, SEGMENT_INFO.MULTI_VAL_ALWD_IND, SEGMENT_INFO.SEGMENT_TYP_CD, SEGMENT_INFO.SEGMENT_TYP_NM FROM BRAND_INFO INNER JOIN C_PTR_REL_CLSSN_REL ON C_PTR_REL_CLSSN_REL.PTR_RL_ROWID = BRAND_INFO.BRAND_MDM_ID INNER JOIN SEGMENT_INFO ON C_PTR_REL_CLSSN_REL.PTR_CLSSN_ROWID = SEGMENT_INFO.SEGMENT_VAL_MDM_ID )

SELECT DISTINCT * FROM BRAND_SEGMENT WHERE SEGMENT_TYP_CD = ? AND BRAND_CODE = ?

  • To get Customer Row Id

SELECT DECODE(CUST_MDM_ID,'9999999999999999', 'NOT AVAILABLE',CUST_MDM_ID) CUST_MDM_ID FROM ( SELECT DISTINCT CUST_MDM_ID, DENSE_RANK() OVER (ORDER BY TO_NUMBER(CUST_MDM_ID)) D_RANK FROM ( SELECT DISTINCT C_PTR_PARTY_RL.ROWID_OBJECT CUST_MDM_ID FROM C_PTR_PARTY_RL INNER JOIN C_PTR_ALT_CODES ON C_PTR_ALT_CODES.PTR_ROWID = C_PTR_PARTY_RL.ROWID_OBJECT WHERE C_PTR_ALT_CODES.TYP_TYPE_CD = 'PTR_ALT_CODES.CSTFCT' AND C_PTR_ALT_CODES.CODE = ? UNION ALL SELECT '9999999999999999' FROM DUAL ) ) WHERE D_RANK = 1

  • To get Row Id Object

SELECT ROWID_OBJECT, PTR_CUST_ROWID, ACC_GRP_CD, BROK, FORCOM, PTR_CUST_STAT_CD, PTR_CUST_TYP_CD, PTR_STS_RSN_CD, URL, INTL_DIALNG_ROWID_PH, PH_NUM, COMM_TYP, MOB_VALIDN_REQ_TS, MOB_VALIDN_REQ_ID FROM C_PTR_CUST WHERE PTR_CUST_ROWID = ?

Was this page helpful?