This Oracle query will generate UMF messages from the data already in Identity Insight. This could be used to archive the data in an ingestable format, or to move the data to a different instance.
SELECT '<UMF_ENTITY><DSRC_ACTION>A</DSRC_ACTION><DSRC_CODE>' || DSRC_CODE || '</DSRC_CODE><DSRC_ACCT>' || DSRC_ACCT || '</DSRC_ACCT><DSRC_REF>' || DSRC_REF || '</DSRC_REF>' || NVL2(NAME_SEG,NAME_SEG,'') || NVL2(ADDR_SEG,ADDR_SEG,'') || NVL2(EMAIL_SEG,EMAIL_SEG,'') || NVL2(NUM_SEG,NUM_SEG,'') || NVL2(ATTR_SEG,ATTR_SEG,'') || '</UMF_ENTITY>' ADD_UMF
FROM DSRC_ACCT D
JOIN DSRC_CODE C ON D.DSRC_ID = C.DSRC_ID
LEFT JOIN (SELECT DSRC_ACCT_ID, LISTAGG('<NAME><NAME_TYPE>' || NAME_TYPE || '</NAME_TYPE>' || NVL2(NAME_PFX,'<NAME_PFX>' || NAME_PFX || '</NAME_PFX>','') || NVL2(FIRST_NAME,'<FIRST_NAME>' || FIRST_NAME || '</FIRST_NAME>','') || NVL2(MID_NAME,'<MID_NAME>' || MID_NAME || '</MID_NAME>','') || NVL2(LAST_NAME,'<LAST_NAME>' || LAST_NAME || '</LAST_NAME>','') || NVL2(NAME_GEN,'<NAME_GEN>' || NAME_GEN || '</NAME_GEN>','') || NVL2(NAME_SFX,'<NAME_SFX>' || NAME_SFX || '</NAME_SFX>','') || '</NAME>','') WITHIN GROUP (ORDER BY NAME_ID) NAME_SEG
FROM NAME
GROUP BY DSRC_ACCT_ID) N ON D.DSRC_ACCT_ID = N.DSRC_ACCT_ID
LEFT JOIN (SELECT DSRC_ACCT_ID, LISTAGG('<ADDRESS>' || NVL2(ADDR_TYPE,'<ADDR_TYPE>' || ADDR_TYPE || '</ADDR_TYPE>','') || NVL2(ADDR1,'<ADDR1>' || ADDR1 || '</ADDR1>','') || NVL2(ADDR2,'<ADDR2>' || ADDR2 || '</ADDR2>','') || NVL2(ADDR3,'<ADDR3>' || ADDR3 || '</ADDR3>','') || NVL2(CITY,'<CITY>' || CITY || '</CITY>','') || NVL2(STATE,'<STATE>' || STATE || '</STATE>','') || NVL2(POSTAL_CODE,'<POSTAL_CODE>' || POSTAL_CODE || '</POSTAL_CODE>','') || NVL2(COUNTRY,'<COUNTRY>' || COUNTRY || '</COUNTRY>','') || '</ADDRESS>','') WITHIN GROUP (ORDER BY ADDR_ID) ADDR_SEG
FROM ADDRESS
GROUP BY DSRC_ACCT_ID) R ON D.DSRC_ACCT_ID = R.DSRC_ACCT_ID
LEFT JOIN (SELECT DSRC_ACCT_ID, LISTAGG('<EMAIL>' || NVL2(ADDR_TYPE,'<ADDR_TYPE>' || ADDR_TYPE || '</ADDR_TYPE>','') || NVL2(EMAIL_ADDR,'<EMAIL_ADDR>' || EMAIL_ADDR || '</EMAIL_ADDR>','') || '</EMAIL>','') WITHIN GROUP (ORDER BY EMAIL_ADDR_ID) EMAIL_SEG
FROM EMAIL_ADDR
GROUP BY DSRC_ACCT_ID) E ON D.DSRC_ACCT_ID = E.DSRC_ACCT_ID
LEFT JOIN (SELECT DSRC_ACCT_ID, LISTAGG('<NUMBER><NUM_TYPE>' || NUM_TYPE || '</NUM_TYPE><NUM_VALUE>' || NUM_VALUE || '</NUM_VALUE>' || NVL2(NUM_LOCATION,'<NUM_LOCATION>' || NUM_LOCATION || '</NUM_LOCATION>','') || '</NUMBER>','') WITHIN GROUP (ORDER BY NUM_ID) NUM_SEG
FROM NUMS N
JOIN NUM_TYPE T ON N.NUM_TYPE_ID = T.NUM_TYPE_ID
GROUP BY DSRC_ACCT_ID) U ON D.DSRC_ACCT_ID = U.DSRC_ACCT_ID
LEFT JOIN (SELECT DSRC_ACCT_ID, LISTAGG('<ATTRIBUTE><ATTR_TYPE>' || ATTR_TYPE || '</ATTR_TYPE><ATTR_VALUE>' || ATTR_VALUE || '</ATTR_VALUE>' || '</ATTRIBUTE>','') WITHIN GROUP (ORDER BY ATTR_ID) ATTR_SEG
FROM ATTRIBUTE A
JOIN ATTR_TYPE T ON A.ATTR_TYPE_ID = T.ATTR_TYPE_ID
GROUP BY DSRC_ACCT_ID) A ON D.DSRC_ACCT_ID = A.DSRC_ACCT_ID
WHERE D.DSRC_ACCT_ID = <dsrc_acct_id>
This query, as written, will generate the UMF message for the selected identity. You can change the final where clause to generate for an entity (WHERE D.ENTITY_ID = <entity_id>), a full data source (WHERE D.DSRC_ID = <dsrc_id>), or for all the data by leaving off the WHERE clause.
No comments:
Post a Comment