1. Using Oracle Java Cloud Service - SaaS Extension
  2. PaaS-SaaS Association
  3. Creating a Report of Oracle Sales Cloud User Accounts

Creating a Report of Oracle Sales Cloud User Accounts

To effectively enable Oracle Sales Cloud users’ access Oracle Java Cloud Service - SaaS Extension, you should periodically synchronize those users with Oracle Java Cloud Service - SaaS Extension. This synchronization is a manual, two-step process: Creating the list of users from the Oracle Sales Cloud service and importing the users into Oracle Java Cloud Service - SaaS Extension instance.

The following procedure creates a report containing a comma-separated values (CSV) list of the Fusion Application user account information required to set up each user account. Once you create the CSV file, you can import these accounts to Oracle Cloud. To extract the Fusion Applications user account information:
  1. From the Fusion Applications Home page, click Navigator.
  2. Under Tools, select Reports and Analytics.
  3. Click the Browse Catalog icon to open the Oracle BI Catalog, then select New, then Data Model.
  4. Under the Diagram tab, click New, then SQL Query to create a new SQL Query data set.
  5. In the New Data Set - SQL Query dialog, enter a Name, select ApplicationDB_HCM as the Data Source and select Standard SQL as the Type of SQL. Enter the following SQL query in the SQL Query section, then click OK.
    --HCM
SELECT e.email_address AS email
FROM fusion.per_users u, fusion.per_roles_dn r, fusion.per_user_roles ur, 
fusion.per_all_people_f f
JOIN fusion.per_email_addresses e ON e.person_id = f.person_id 
AND e.email_address_id = f.primary_email_id AND e.email_type = 'W1'
WHERE TRUNC(SYSDATE) BETWEEN f.effective_start_date AND f.effective_end_date 
AND u.person_id = f.person_id AND u.active_flag = 'Y' AND r.role_common_name = :Bind 
AND r.role_guid = ur.role_guid AND ur.active_flag = 'Y' AND ur.terminated_flag != 'Y'

--TCA
UNION SELECT c.email_address AS email 
FROM fusion.per_users u, fusion.per_roles_dn r, fusion.per_user_roles ur, 
fusion.hz_person_profiles p 
JOIN fusion.hz_contact_points c ON c.owner_table_id = p.party_id 
AND c.owner_table_name = 'HZ_PARTIES' 
AND c.overall_primary_flag = 'Y' AND c.contact_point_type = 'EMAIL' AND c.status = 'A' 
AND TRUNC(SYSDATE) BETWEEN c.start_date AND c.end_date 
WHERE u.party_id = p.party_id AND TRUNC(SYSDATE) BETWEEN p.effective_start_date 
AND p.effective_end_date AND p.status = 'A' AND u.active_flag = 'Y' 
AND r.role_common_name = :Bind AND r.role_guid = ur.role_guid 
AND ur.active_flag = 'Y' AND ur.terminated_flag != 'Y'
  6. In the Add Parameter dialog, select the first Bind and click OK.
    This parameter is used as the input to the report for getting all users for a Role.
  7. Enter a name and display label for the bind parameter.
  8. Click View Data to display the Data tab, then enter a value for role name parameter, for example, FUSION_APPS_HCM_ADF_APPID.
    1. Click View.
    2. Once the data appears, click Save As Sample Data.
    3. Click Save to save the data model in the Drafts folder (under My Folders).
  9. At the top of the window, click New, then select Report.
  10. In the Select Data dialog, for Data Model, select the Data Model you created, accept the other default selections, then click Next.
  11. In the Select Layout dialog, accept the default report layout selections, then click Next.
  12. In the Create Table dialog, drag and drop the EMAIL column.
  13. Deselect the Show Grand Totals Row check box, and then click Next.
  14. In the Save Report dialog, select View Report and then click Finish.
  15. Save the report.
    1. In the Save As dialog, name the report Users, and save it in the Drafts folder (under My Folders), then click OK
      The Layout Editor automatically displays the Users report.
    2. From the output drop-down list, select your desired spreadsheet application; for example, Excel (*.xlsx).
    3. In the Open dialog, open the report with the default application, Microsoft Excel.
    4. In Microsoft Excel, from the Save As menu, select Save as type CSV, select where you want to store the file, and then click Save.
  16. In the CSV file, rename the column to Email to comply with the Oracle Cloud requirements for importing this file.
  17. Import the CSV file with the Fusion Applications account users to Oracle Cloud. For instructions, see Importing a Batch of User Accounts in Getting Started with Oracle Cloud.