We would like to be able to inform owners of deliveries whenever their delivery gets deleted. So, even if the delivery is deleted intentionally or by mistake, every night a workflow will run and identify the owners of the deliveries. A communication will be sent, informing the owners about their delivery getting deleted.
Now, we know the delivery is moved to 'Deleted Deliveries' for which the internal name is 'nmsRecycledDelivery' but is there an easier way to see the contents of this folder? The delivery table does not have a FolderID column inside of it.
So,when you have just deleted deliveries, you see them in the folder nmsRecycledDelivery. They first have the deleteStatus='recycled' values.
Please add the column deleteStatus in your folder configuration to better understand.
Regarding the Generic query tool, the problem is that for delivery schema dimension, the tool automatically adds a filter to not find the deleted deliveries, as you can see in the SQL query generated:
SELECT top 201 D0.iDeleteStatus, D0.tsDelete, D0.sLabel FROM NmsDelivery D0 WHERE (D0.tsDelete >= convert(datetime, '20170927 00:00:00.000', 112)) AND (((D0.iDeliveryId > 0 OR D0.iDeliveryId < 0)) AND ((D0.iDeleteStatus = 0)))
Hmm, interesting. Will have to look for some other way to notify the user once the delivery is deleted (even by mistake). We tried using QueryDef too but it automatically adds the criteria that doesn't let you select deleted/recycled deliveries.
Thanks JS. I can now see the deleteStatus under deliveries. However, using a Query box, whenever I specify the condition to list down all deliveries that are deleted (i.e. deleteStatus=Yes), I see no results. This is despite there being a few deleted deliveries under Campaign Management / Deliveries / Deleted deliveries folder.
Furthermore, when I list deliveries where Delete date or on after 02/08/17 (i.e. deleteDate on or after 02/08/17) , it lists a few deliveries. When I add @deleteStatus as a next column to be viewed, I see a No (under Deleted) against all those listed.
Please see below a screenshot but for AC 6.1.1 French version (hereinafter the Delivery list configuration, but you can do it in Schema Data tab also, or in Query activity on dimension Delivery, etc). Type deleteStatus in search bar for displaying it in bold.