SELECT OUTER_MP.MASTER_PERSON_ID, (SELECT COUNT (*) FROM INCIDENT_PERSON_ROLES IPR INNER JOIN INCIDENT_PEOPLE IP ON IPR.INC_PER_ID = IP.INC_PER_ID INNER JOIN MASTER_NAMES MN ON IP.PER_PERSON_ID = MN.PER_PERSON_ID AND IP.MN_MN_ID = MN.MN_ID INNER JOIN PEOPLE P ON MN.PER_PERSON_ID = P.PERSON_ID INNER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID INNER JOIN ROLE_CODES RC ON RC.ROLE_TYPE = IPR.Role_Type WHERE MP.MASTER_PERSON_ID = OUTER_MP.MASTER_PERSON_ID AND RC.Inc_Section_Code = 'OFFENDERS') AS OFFENDER_CNT, (SELECT COUNT (*) FROM INCIDENT_PERSON_ROLES IPR INNER JOIN INCIDENT_PEOPLE IP ON IPR.INC_PER_ID = IP.INC_PER_ID INNER JOIN MASTER_NAMES MN ON IP.PER_PERSON_ID = MN.PER_PERSON_ID AND IP.MN_MN_ID = MN.MN_ID INNER JOIN PEOPLE P ON MN.PER_PERSON_ID = P.PERSON_ID INNER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID WHERE MP.MASTER_PERSON_ID = OUTER_MP.MASTER_PERSON_ID AND IPR.ROLE_TYPE = 'A') AS ARRESTEE_CNT, (SELECT COUNT (DISTINCT EWAR.WARRANT_ID) FROM E_WARRANTS EWAR INNER JOIN MASTER_NAMES MN ON EWAR.MN_PER_PERSON_ID = MN.PER_PERSON_ID AND EWAR.MN_MN_ID = MN.MN_ID INNER JOIN PEOPLE P ON MN.PER_PERSON_ID = P.PERSON_ID INNER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID WHERE MP.MASTER_PERSON_ID = OUTER_MP.MASTER_PERSON_ID AND EWAR.WSC_CODE = 'A') AS ACTIVE_WARRANTS, (SELECT COUNT (DISTINCT CI.CITATION_ID) FROM CITATIONS CI INNER JOIN CITATION_PEOPLE CP ON CI.CITATION_ID = CP.CITATION_ID INNER JOIN MASTER_NAMES MN ON CP.PERSON_ID = MN.PER_PERSON_ID AND CP.MN_ID = MN.MN_ID INNER JOIN PEOPLE P ON MN.PER_PERSON_ID = P.PERSON_ID INNER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID WHERE MP.MASTER_PERSON_ID = OUTER_MP.MASTER_PERSON_ID) AS CITATIONS, (SELECT COUNT (*) FROM PERMIT_PEOPLE PP INNER JOIN PEOPLE P ON PP.PERSON_ID = P.PERSON_ID INNER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID WHERE MP.MASTER_PERSON_ID = OUTER_MP.MASTER_PERSON_ID) AS PERMITS, (SELECT COUNT(1) FROM GANG_PEOPLE GP INNER JOIN PEOPLE P ON GP.PERSON_ID = P.PERSON_ID WHERE P.MASTER_PERSON_ID = OUTER_MP.MASTER_PERSON_ID) AS GANG_COUNT, (SELECT COUNT (*) FROM FLDINT_PEOPLE FP INNER JOIN PEOPLE P ON FP.PERSON_ID = P.PERSON_ID INNER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID LEFT OUTER JOIN CASE_FIELD_INTS CFI ON CFI.FLDINT_ID = FP.FLDINT_ID WHERE CFI.CASE_ID IS NULL AND MP.MASTER_PERSON_ID = OUTER_MP.MASTER_PERSON_ID) AS FIELD_INTERVIEWS, (SELECT COUNT (*) FROM PERSON_CAUTIONS PC INNER JOIN PEOPLE P ON PC.PER_PERSON_ID = P.PERSON_ID INNER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID WHERE MP.MASTER_PERSON_ID = OUTER_MP.MASTER_PERSON_ID AND (PC.START_DATE IS NULL OR SYSDATE > PC.START_DATE) AND (PC.EXPIRE_DATE IS NULL OR (PC.EXPIRE_DATE IS NOT NULL AND PC.EXPIRE_DATE > SYSDATE))) AS ACTIVE_CAUTION_CODES, (SELECT COUNT (*) FROM COURT_PAPER_PARTIES CPP INNER JOIN PEOPLE P ON CPP.PERSON_ID = P.PERSON_ID INNER JOIN COURT_PAPER_SERVICE_DETAILS CPSD ON CPSD.CP_PARTY_ID = CPP.CP_PARTY_ID WHERE P.MASTER_PERSON_ID = OUTER_MP.MASTER_PERSON_ID AND CPSD.CP_STATUS_CODE = 'A') AS COURT_PAPERS, (SELECT COUNT(*) FROM PERSON_GEO_VW PGV WHERE MASTER_PERSON_ID = OUTER_MP.MASTER_PERSON_ID) AS GEO_EVENTS, -- (SELECT COUNT ('1') FROM PEOPLE P INNER JOIN MASTER_NAMES MN ON P.PERSON_ID = MN.PER_PERSON_ID WHERE P.MASTER_PERSON_ID = OUTER_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 = OUTER_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 = OUTER_MP.MASTER_PERSON_ID AND PI.MC1_MUGSHOT_CODE = 'FRO') AS MUGSHOT_COUNT, (SELECT COUNT('1') FROM PEOPLE P INNER JOIN PEOPLE_VEHICLES PV ON P.PERSON_ID = PV.PERSON_ID WHERE P.MASTER_PERSON_ID = OUTER_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 = OUTER_MP.MASTER_PERSON_ID) AS INCIDENT_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 = OUTER_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 = OUTER_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 = OUTER_MP.MASTER_PERSON_ID) AS ANY_PROP_COUNT FROM MASTER_PEOPLE OUTER_MP