I'm looking for a way to create a complete listing of all accounts, including orphans, that are collected from a specific business source and include the collected app role as well. Is there a canned report that does that?
You can accomplish all your reporting needs by using the Public Database Schema and SQL Developer. Use SQL Developer to build your report data and then use that query within the Report Console.
I created a sample report query for you.
You can filter on "Application Name" to select only one business source.
Let me know if this helps.
SELECT PVASUB.NAME AS "APPLICATION NAME", PVAC.NAME AS "ACCOUNT NAME", CASEWHEN PVAC.ORPHANED_DATE IS NOT NULL THEN 'Yes'ELSE 'No'END AS "IS_ORPHANED",CASEWHENPVAC.IS_DISABLED = 0 THEN 'No'ELSE 'Yes'END AS "IS_DISABLED",PVAC.CREATION_DATE,PVAC.ORPHANED_DATE,PVAC.ADC_NAME AS "Collector Name" FROM avuser.PV_ACCOUNT PVACJOIN (SELECT PVA.NAME, PVA.ID FROM avuser.PV_APPLICATION PVA) PVASUB ON PVASUB.ID=PVAC.APPLICATION_IDWHERE PVASUB.NAME = 'My Business Source Name'ORDER BY PVASUB.NAME ASC, PVAC.CREATION_DATE ASC
Retrieving data ...