Informatica MDM - How to generate match report?

Once you are done with a running match job, if you would like to create a match report so that business users can review it before finalizing it then you can use the below query to generate a match report. The SQL query can be tuned to accommodate your data model changes. The result of the match report includes attributes from the Party as well as the child tables. You can adjust the result parameters as per your needs.

WITH
CTE_GOLDEN_RECORDS (GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND)
AS
(
  SELECT ROWID_OBJECT_MATCHED AS GR_GROUP_ID, ROWID_OBJECT_MATCHED AS ROWID_OBJECT, 'FIRST_RECORD' AS RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND
  FROM (  SELECT DISTINCT ROWID_OBJECT_MATCHED, ROWID_MATCH_RULE, AUTOMERGE_IND, ROW_NUMBER() OVER(PARTITION BY ROWID_MATCH_RULE ORDER BY DBMS_RANDOM.VALUE) RANDSEL
          FROM CMX_ORS.C_BO_PARTY_MTCH where
          ROWID_OBJECT_MATCHED in (select rowid_object from cmx_ors.c_bo_party
                where PARTY_ROLE_TYPE='ACCT' )
        ) GR
       
  WHERE RANDSEL <= (SELECT CEIL(30000/COUNT(RULE_NO)) AS RULE_FETCH_COUNT
                   FROM CMX_ORS.C_REPOS_MATCH_RULE
                   WHERE MATCH_FOR_ROWID_TABLE = ( SELECT ROWID_TABLE FROM CMX_ORS.C_REPOS_TABLE WHERE TABLE_NAME = 'C_BO_PARTY')
                          AND RULE_ENABLED_IND = 1)
),
CTE_SUSPECT_RECORDS (GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND)
AS
(
  SELECT SR.ROWID_OBJECT_MATCHED, SR.ROWID_OBJECT, 'OTHER_RECORD' AS RECORD_TYPE, SR.ROWID_MATCH_RULE, SR.AUTOMERGE_IND
  FROM CMX_ORS.C_BO_PARTY_MTCH SR
  JOIN CTE_GOLDEN_RECORDS GR ON GR.ROWID_OBJECT = SR.ROWID_OBJECT_MATCHED AND GR.ROWID_MATCH_RULE = SR.ROWID_MATCH_RULE
),
CTE_TRANS_SUSPECT_RECORDS (GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND)
AS
(
  SELECT SR.ROWID_OBJECT_MATCHED, SR.ORIG_ROWID_OBJECT_MATCHED, 'OTHER_RECORD' AS RECORD_TYPE, SR.ROWID_MATCH_RULE, SR.AUTOMERGE_IND
  FROM CMX_ORS.C_BO_PARTY_MTCH SR
  JOIN CTE_GOLDEN_RECORDS GR ON GR.ROWID_OBJECT = SR.ROWID_OBJECT_MATCHED AND GR.ROWID_MATCH_RULE = SR.ROWID_MATCH_RULE
),
CTE_MATCH_RECORDS (GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND)
AS
(
  SELECT GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND
  FROM CTE_GOLDEN_RECORDS
 
  UNION ALL
 
  SELECT GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND
  FROM CTE_SUSPECT_RECORDS
 
  UNION ALL
 
  SELECT GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND
  FROM CTE_TRANS_SUSPECT_RECORDS
),
CTE_PARTY_IDENTIFICAITON (ROWID_OBJECT, LINK_ID, TIN, EID, MBR_ID, ACCT_ID) AS
(
  SELECT ROWID_PARTY, LINK_ID_IDFCTN_NBR, TIN_IDFCTN_NBR, EID_IDFCTN_NBR, MBR_ID_IDFCTN_NBR, ACCTID_IDFCTN_NBR
  FROM (SELECT PR.ROWID_PARTY, PI.IDFCTN_TYPE_CODE, PI.IDFCTN_NBR
        FROM CMX_ORS.C_BO_PARTY_ROLE PR
        JOIN CMX_ORS.C_BO_PARTY_ROLE_IDFCTN PI ON PR.ROWID_OBJECT = PI.ROWID_PARTY_ROLE
        JOIN CTE_MATCH_RECORDS MR ON MR.ROWID_OBJECT = PR.ROWID_PARTY
        ) PI
  PIVOT (
          MAX(IDFCTN_NBR) AS IDFCTN_NBR FOR IDFCTN_TYPE_CODE IN ('LINK ID' AS LINK_ID, 'TIN' AS TIN, 'EID' AS EID, 'MBR_ID' AS MBR_ID, 'Account Number' AS ACCTID)
        )
),
CTE_PARTY_ADDRESS (ROWID_OBJECT, RESIDENT, CORRESP) AS
(
  SELECT ROWID_PARTY, RESIDENTIAL_ADDRESS_VALUE, CORRESPONDENCE_ADDRESS_VALUE 
  FROM (  SELECT PR.ROWID_PARTY, PRA.ADDR_TYPE_CODE, POADDR.ADDR_LINE_1, POADDR.ADDR_LINE_2 , POADDR.ADDR_LINE_3 , POADDR.CITY, POADDR.ZIP_POSTAL_CODE
  , POADDR.STATE_CODE , POADDR.CNTRY_CODE
          FROM CMX_ORS.C_BO_PARTY_ROLE PR
          JOIN CTE_MATCH_RECORDS MR ON MR.ROWID_OBJECT = PR.ROWID_PARTY
          JOIN CMX_ORS.C_BO_PARTY_ROLE_ADDR PRA ON PR.ROWID_OBJECT = PRA.ROWID_PARTY_ROLE         
          JOIN CMX_ORS.C_BO_POSTAL_ADDR POADDR ON PRA.ROWID_ADDR = POADDR.ROWID_OBJECT
        ) PRA
  PIVOT (
          MAX(ADDR_LINE_1 || ' ' || ADDR_LINE_2 || ' ' || ADDR_LINE_3 || ' ' || CITY || ' ' || ZIP_POSTAL_CODE || ' ' ||  STATE_CODE || ' ' || CNTRY_CODE) AS ADDRESS_VALUE
            FOR ADDR_TYPE_CODE IN ('RSDNTL' AS RESIDENTIAL, 'CRSPDC' AS CORRESPONDENCE)
        )
),
CTE_PARTY_PHONE (ROWID_OBJECT, HOME, MAIN, WORK, MOBILE) AS
(
  SELECT ROWID_PARTY, HOME_PHONE_NUM, MAIN_PHONE_NUM, WORK_PHONE_NUM, MOBILE_PHONE_NUM
  FROM (  SELECT PR.ROWID_PARTY, PP.PHONE_USG_TYPE_CODE, PP.CNTRY_CODE, PP.AREA_CODE, PP.PHONE_NBR, PP.EXTN_CODE
          FROM CMX_ORS.C_BO_PARTY_ROLE PR
          JOIN CMX_ORS.C_BO_PARTY_ROLE_PHONE PP ON PR.ROWID_OBJECT = PP.ROWID_PARTY_ROLE
          JOIN CTE_MATCH_RECORDS MR ON MR.ROWID_OBJECT = PR.ROWID_PARTY
          ) PRP
  PIVOT (
          MAX(CNTRY_CODE || ' ' || AREA_CODE || ' ' || PHONE_NBR || ' ' || EXTN_CODE ) AS PHONE_NUM
            FOR PHONE_USG_TYPE_CODE IN ('HOME_PHONE' AS HOME, 'PHONE' AS MAIN, 'WORK_PHONE' AS WORK, 'MOBILE' AS MOBILE)
        )
)
SELECT DISTINCT
  MR.GR_GROUP_ID,
  MR.ROWID_OBJECT AS MDM_PARTY_ID,
  MR.RECORD_TYPE,
  RL.RULE_NO AS RULE_DESCRIPTION,
  MR.AUTOMERGE_IND AS AUTO_MERGE,
  PARTY_XR.ROWID_SYSTEM AS SOURCE,
  PARTY_XR.PKEY_SRC_OBJECT AS SOURCE_KEY,
  PARTY.PARTY_TYPE_CODE AS PARTY_TYPE,
  PARTY.PARTY_ROLE_TYPE AS ROLE_TYPE,
  PARTY.ORG_NAME AS ORG_NAME,
  PARTY.ACCT_TYPE_CODE AS ACCT_TYPE_CODE,
  PARTY.ACCT_LVL_CODE AS ACCT_LVL_CODE, 
  PARTY.DISPLAY_NAME AS FULL_NAME,
  PARTY.FIRST_NAME ,
  PARTY.MID_NAME ,
  PARTY.LAST_NAME ,
  PARTY.DOB AS BIRTH_DATE,
  PARTY.GENDER_CODE AS GENDER,
  PARTY.INDUSTRY_CLS_CODE,
  PARTY.ACCT_NBR,
  PI.LINK_ID,
  PI.TIN TIN,
  PI.EID EID,
  PI.MBR_ID AS MEMBER_ID,
  PI.ACCT_ID AS ACCT_ID,
  PA.RESIDENT AS RESIDENTIAL_ADDR,
  PA.CORRESP AS CORRESPONDENCE_ADDR,
  PP.HOME AS HOME_PHONE,
  PP.MAIN AS MAIN_PHONE,
  PP.WORK AS WORK_PHONE,
  PP.MOBILE AS MOBILE_PHONE
 
FROM CTE_MATCH_RECORDS MR
JOIN CMX_ORS.C_REPOS_MATCH_RULE RL ON RL.ROWID_MATCH_RULE = MR.ROWID_MATCH_RULE
JOIN CMX_ORS.C_BO_PARTY PARTY ON PARTY.ROWID_OBJECT = MR.ROWID_OBJECT
JOIN CMX_ORS.C_BO_PARTY_XREF PARTY_XR ON PARTY_XR.ROWID_OBJECT = PARTY.ROWID_OBJECT
LEFT JOIN CTE_PARTY_IDENTIFICAITON PI ON PI.ROWID_OBJECT = MR.ROWID_OBJECT
WHERE PARTY_XR.ROWID_OBJECT = PARTY_XR.ORIG_ROWID_OBJECT
LEFT JOIN CTE_PARTY_ADDRESS PA ON PA.ROWID_OBJECT = MR.ROWID_OBJECT
LEFT JOIN CTE_PARTY_PHONE PP ON PP.ROWID_OBJECT = MR.ROWID_OBJECT
ORDER BY GR_GROUP_ID, RULE_NO, RECORD_TYPE;