SELECT /*+PUSH PRED MP*/ (SELECT COUNT ('1') FROM PEOPLE P INNER JOIN MASTER_NAMES MN ON P.PERSON_ID = MN.PER_PERSON_ID WHERE P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID AND MN.NAME_TYPE_CODE_TYPE = 'NAME_TYPE_CODES' AND MN.NAME_TYPE_CODE != 'P') as ALIAS_COUNT, (SELECT COUNT('1') FROM PEOPLE P INNER JOIN PERSON_IMAGES PI ON P.PERSON_ID = PI.PER_PERSON_ID WHERE P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID) AS ANY_PHOTO_COUNT, (SELECT COUNT('1') FROM PEOPLE P INNER JOIN PERSON_IMAGES PI ON P.PERSON_ID = PI.PER_PERSON_ID WHERE P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID AND PI.MC1_MUGSHOT_CODE = 'FRO') AS PHOTO_COUNT, (SELECT COUNT('1') FROM PEOPLE P INNER JOIN PEOPLE_VEHICLES PV ON P.PERSON_ID = PV.PERSON_ID WHERE P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID) AS ANY_VEHICLE_COUNT, (SELECT COUNT('1') FROM PEOPLE P INNER JOIN INCIDENT_PEOPLE IP ON P.PERSON_ID = IP.PER_PERSON_ID WHERE P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID) AS INCIDENT_COUNT, (SELECT COUNT('1') FROM PEOPLE P INNER JOIN GANG_PEOPLE GP ON P.PERSON_ID = GP.PERSON_ID WHERE P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID) AS GANG_COUNT, (SELECT COUNT('1') FROM PEOPLE PE INNER JOIN PEOPLE_PROPERTIES PP ON PE.PERSON_ID = PP.PERSON_ID INNER JOIN PROPERTIES P ON PP.PROPERTY_ID = P.PROPERTY_ID INNER JOIN PROPERTY_TYPE_CODES PTC ON P.PROPERTY_TYPE_CODE = PTC.PROPERTY_TYPE WHERE PE.MASTER_PERSON_ID = MP.MASTER_PERSON_ID AND PTC.PROPERTY_CATEGORY_TYPE = 'PROPERTY_CATEGORY_CODES' AND PTC.PROPERTY_CATEGORY = 'GUN') AS GUN_COUNT, (SELECT COUNT('1') FROM PEOPLE PE INNER JOIN PEOPLE_PROPERTIES PP ON PE.PERSON_ID = PP.PERSON_ID INNER JOIN PROPERTIES P ON PP.PROPERTY_ID = P.PROPERTY_ID INNER JOIN PROPERTY_TYPE_CODES PTC ON P.PROPERTY_TYPE_CODE = PTC.PROPERTY_TYPE WHERE PE.MASTER_PERSON_ID = MP.MASTER_PERSON_ID AND PTC.PROPERTY_CATEGORY_TYPE = 'PROPERTY_CATEGORY_CODES' AND PTC.PROPERTY_CATEGORY != 'GUN') AS ARTICLE_COUNT, (SELECT COUNT('1') FROM PEOPLE PE INNER JOIN PEOPLE_PROPERTIES PP ON PE.PERSON_ID = PP.PERSON_ID WHERE PE.MASTER_PERSON_ID = MP.MASTER_PERSON_ID) AS ANY_PROP_COUNT, (SELECT COUNT('1') FROM PEOPLE P INNER JOIN CITATION_PEOPLE CP ON P.PERSON_ID = CP.PERSON_ID WHERE P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID) AS CITATION_COUNT, (SELECT COUNT('1') FROM PEOPLE P INNER JOIN PERMIT_PEOPLE PP ON P.PERSON_ID = PP.PERSON_ID WHERE P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID) AS PERMIT_COUNT, (SELECT COUNT('1') FROM PEOPLE P INNER JOIN ARRESTS ARR ON P.PERSON_ID = ARR.PERSON_ID WHERE P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID) AS BOOKING_COUNT, (SELECT COUNT('1') FROM E_WARRANTS EW INNER JOIN PEOPLE P ON EW.MN_PER_PERSON_ID = P.PERSON_ID WHERE P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID AND EW.WSC_CODE_TYPE = 'WARRANT_STATUS_CODES' AND EW.WSC_CODE = 'A') as WARRANT_COUNT, MID.ID_NUMBER, MID.STATE_CD_STATE_CODE, PA.DATE_OF_INFO, ADDR.ADDRESS_ID, ADDR.STREET_NUMBER || ' ' || ADDR.STREET_NAME RESIDENCE_STREET1, DECODE ( ADDR.ADDR_SC_ADDRESS_SUBTYPE_CODE, NULL, SUB_NUMBER, ADDR.ADDR_SC_ADDRESS_SUBTYPE_CODE || ' ' || ADDR.SUB_NUMBER) RESIDENCE_STREET2, ADDR.CITY RESIDENCE_CITY, ADDR.STATE_CD_STATE_CODE RESIDENCE_STATE, ADDR.ZIP5 RESIDENCE_ZIP, MN.PER_PERSON_ID, MN.MN_ID, MN.NAME_TYPE_CODE, MN.LNAME, MN.FNAME, MN.MNAME, MN.DOB, MN.SSN, MN.SLNAME, MN.SFNAME, MP.RACE_CODE, MP.SEX_CODE, (SELECT EJSC.DESCRIPTION FROM EJS_CODES EJSC WHERE EJSC.CODE_TYPE = MP.RACE_CODE_TYPE AND EJSC.CODE = MP.RACE_CODE) as RACE_DESC, (SELECT EJSC.DESCRIPTION FROM EJS_CODES EJSC WHERE EJSC.CODE_TYPE = MP.SEX_CODE_TYPE AND EJSC.CODE = MP.SEX_CODE) as SEX_DESC FROM MASTER_PEOPLE MP INNER JOIN PEOPLE PPL ON MP.MASTER_PERSON_ID = PPL.MASTER_PERSON_ID INNER JOIN MASTER_NAMES MN ON PPL.PERSON_ID = MN.PER_PERSON_ID AND MN.NAME_TYPE_CODE_TYPE = 'NAME_TYPE_CODES' AND MN.NAME_TYPE_CODE = 'P' LEFT OUTER JOIN MISC_IDS MID ON PPL.PERSON_ID = MID.PER_PERSON_ID AND MID.MISC_CD_MISC_ID_CODE = 'DL' LEFT OUTER JOIN PERSON_ADDRESSES PA ON PPL.PERSON_ID = PA.PERSON_ID LEFT OUTER JOIN ADDRESSES ADDR ON PA.ADDRESS_ID = ADDR.ADDRESS_ID WHERE MP.INDX_SEC_LEVEL_CODE = 1 AND (MID.CREATOR_DATE IS NULL OR MID.CREATOR_DATE = (SELECT /*+PUSH PRED P2*/ MAX(MID2.CREATOR_DATE) FROM MISC_IDS MID2 INNER JOIN PEOPLE P2 ON MID2.PER_PERSON_ID = P2.PERSON_ID WHERE P2.MASTER_PERSON_ID = MP.MASTER_PERSON_ID AND MID2.MISC_CD_MISC_ID_CODE = 'DL')) AND (PA.PERSON_ADDRESS_ID IS NULL OR PA.PERSON_ADDRESS_ID = (SELECT MAX(PA2.PERSON_ADDRESS_ID) FROM PERSON_ADDRESSES PA2 INNER JOIN PEOPLE P2 ON PA2.PERSON_ID = P2.PERSON_ID WHERE P2.MASTER_PERSON_ID = MP.MASTER_PERSON_ID AND PA2.ADDRESS_TYPE_CODE = 'RES' ))