Previewing impacted sub-objects

Before you create and run a sub-object inactivation job, you can run a report to understand how many sub-objects (previously called child objects)will be impacted by the job. The report results provide the number of sub-objects for each entity by status.

To run the report:

1. On the Network menu bar, click Reports > Ad Hoc Queries.
2. Click the Advanced tab.
3. Paste the following SQL query into the text area.

   SELECT
           c.entity_type__v,
           e.EntityStatus,
           ChildObject,
           COUNT (*)
       FROM
           (
               SELECT
                       hcp.vid__v,
                       hcp.hcp_status__v AS "EntityStatus"
                   FROM
                       hcp
                   WHERE
                       hcp.hcp_status__v NOT IN (
                           'A',
                           'U'
                       )
               UNION
               ALL SELECT
                       hco.vid__v,
                       hco.hco_status__v AS "EntityStatus"
                   FROM
                       hco
                   WHERE
                       hco.hco_status__v NOT IN (
                           'A',
                           'U'
                       )
           ) e INNER JOIN (
               SELECT
                       entity_vid__v,
                       entity_type__v,
                       'Address' AS "ChildObject"
                   FROM
                       address
                   WHERE
                       address.address_status__v = 'A'
                       AND address.is_veeva_master__v = 'FALSE'
               UNION
               ALL SELECT
                       entity_vid__v,
                       entity_type__v,
                       'License' AS "ChildObject"
                   FROM
                       license
                   WHERE
                       license.license_status__v = 'A'
                       AND license.is_veeva_master__v = 'FALSE'
               UNION
               ALL SELECT
                       entity_vid__v,
                       entity_type__v,
                       'ParentHCO' AS "ChildObject"
                   FROM
                       parenthco
                   WHERE
                       parenthco.parent_hco_status__v = 'A'
                       AND parenthco.is_veeva_master__v = 'FALSE'
           ) c
               ON e.vid__v = c.entity_vid__v
       GROUP BY
           c.entity_type__v,
           e.EntityStatus,
           ChildObject
       ORDER BY
           entity_type__v,
           EntityStatus,
           ChildObject ;

4. To format the query, click Format at the bottom of the editor.
5. Click Run.

Review the sub-objects in the Results section.