Sunday, June 2, 2013

Configurator: Query to get list of published models

In Configurator publication target instance, we can use following query to get list of all models published to this instance which are currently active.

SELECT si.segment1 item_name, 
   mp.publication_id,
   mp.remote_publication_id,
   mp.model_id,
   mp.top_item_id,
   mp.organization_id,
   mp.last_update_date,
   mp.product_key,
   round((sysdate - mp.last_update_date), 2) as days_ago,
   round((sysdate - mp.last_update_date)*24, 2) as hours_ago,
   round((sysdate - mp.last_update_date)*24*60, 2) as mins_ago
FROM cz_model_publications mp,
   mtl_system_items_b si
WHERE 1 =1
   AND mp.top_item_id = si.inventory_item_id
   AND si.organization_id = mp.organization_id
   AND deleted_flag =0
   AND disabled_flag =0
   AND sysdate BETWEEN applicable_from AND applicable_until
   AND export_status ='OK'
   AND object_type ='PRJ'
   AND source_target_flag ='T'  --only target publications
   AND publication_mode ='p'  --list publications in Production model, replace with 't' for Test mode
   --and mp.server_id=1700  --you would need this if multiple sources were publishing to current instance in past
   --and round((sysdate - mp.last_update_date)*24, 2) < 5 --to restrict the result to last N days publications
   --and si.segment1 like 'Laptop55%' --uncomment to get details for specific models
ORDER BY last_update_date desc;