We are being asked to create a report that lists any new entitlement relationships from that day's EDC run. Basically the New column. What view or table can I query to pull this back? Thanks in advance for any help!
See if the following helps - data storage wile collection
You could have a look at:
select * from T_AV_EXPLODEDUSERENTITLEMENTS;
There is a field for creation_date - I am assuming that this would be the creation date of the account-entitlement relationship.
I also see a flag for add_state (not sure maybe you would want to test this out).
Courtesy of the view:
Here is how you could tie back to the account/application/collector tables:
SELECT xxx FROM T_APPLICATIONS, T_RESOURCES, T_ENTITLEMENTS, T_AV_ACCOUNTS TAA, T_AV_EXPLODEDUSERENTITLEMENTS, T_AV_BUSINESS_UNITS, T_DATA_COLLECTORS, T_DATA_COLLECTORS TDCWHERE T_ENTITLEMENTS.APPLICATION_ID = T_APPLICATIONS.ID AND T_RESOURCES.DELETION_DATE IS NULL AND T_ENTITLEMENTS.APPLICATION_ID = T_RESOURCES.APPLICATION_ID AND T_APPLICATIONS.BUSINESS_UNIT_ID = T_AV_BUSINESS_UNITS.ID(+) AND T_ENTITLEMENTS.DELETION_DATE IS NULL AND T_ENTITLEMENTS.RESOURCE_ID = T_RESOURCES.ID AND T_AV_EXPLODEDUSERENTITLEMENTS.DELETION_DATE IS NULL AND T_AV_EXPLODEDUSERENTITLEMENTS.RUN_ID = T_RESOURCES.RUN_ID AND T_AV_EXPLODEDUSERENTITLEMENTS.ENTITLEMENT_ID = T_ENTITLEMENTS.ID AND T_AV_EXPLODEDUSERENTITLEMENTS.ENTITLED_TYPE = 'account' AND T_AV_EXPLODEDUSERENTITLEMENTS.ENTITLEMENT_TYPE = 'ent' AND T_DATA_COLLECTORS.ID = T_AV_EXPLODEDUSERENTITLEMENTS.DC_ID AND TAA.ID = T_AV_EXPLODEDUSERENTITLEMENTS.ENTITLED_ID AND TAA.DELETION_DATE IS NULL AND TAA.ADC_ID = TDC.ID AND TDC.IS_DELETED='FALSE'
Thanks Boris! This was exactly what I was looking for.
Retrieving data ...