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?
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?
Hello Nancy,
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",
CASE
WHEN PVAC.ORPHANED_DATE IS NOT NULL THEN 'Yes'
ELSE 'No'
END AS "IS_ORPHANED",
CASE
WHEN
PVAC.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 PVAC
JOIN (SELECT PVA.NAME, PVA.ID FROM avuser.PV_APPLICATION PVA) PVASUB ON PVASUB.ID=PVAC.APPLICATION_ID
WHERE PVASUB.NAME = 'My Business Source Name'
ORDER BY PVASUB.NAME ASC, PVAC.CREATION_DATE ASC
Hello Nancy,
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.