AnsweredAssumed Answered

How do I get a complete business source user listing with collected app role?

Question asked by Nancy Newton on Jul 23, 2018
Latest reply on Jul 25, 2018 by Jayson Abrams

Like • Show 0 Likes0
Comment • 1

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?

No one else has this question

Outcomes

1 Reply

Jayson Abrams Jul 26, 2018 8:51 AM

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

Actions