Query Assets count per BCC projects

BCC can tell you the items that edit a project, however this query can help you to get just product,sku and categories.
The items that will be indexed on a baseline.

	SET SERVEROUTPUT ON;
	DECLARE
	CURSOR c_get_project_id (p_display_name IN VARCHAR2) IS
	SELECT project_id, display_name FROM atgpub.epub_project WHERE display_name LIKE p_display_name AND completion_date IS NOT NULL AND completion_date > sysdate - 5 ORDER BY completion_date DESC;
	CURSOR c_get_count_assets ( p_project_id IN VARCHAR2 ) IS
	SELECT COUNT(*) FROM (
	WITH project_details AS
	(SELECT ep.project_id, ad.ID AS workspace_id
	FROM atgpub.epub_project ep
	INNER JOIN atgpub.avm_devline ad ON (ad.NAME = ep.workspace)
	INNER JOIN atgpub.epub_pr_history eph ON (eph.project_id = ep.project_id)
	INNER JOIN atgpub.epub_history eh ON (eh.history_id = eph.history))
	SELECT *
	FROM (
	SELECT pd.project_id, 'category' AS TYPE,
	AT.category_id AS repository_id, AT.asset_version, AT.is_head
	FROM project_details pd
	INNER JOIN atgpub.dcs_category AT ON (AT.workspace_id = pd.workspace_id)
	WHERE pd.project_id = p_project_id
	UNION
	SELECT pd.project_id, 'product' AS TYPE,
	AT.product_id AS repository_id, AT.asset_version, AT.is_head
	FROM project_details pd
	INNER JOIN atgpub.dcs_product AT ON (AT.workspace_id = pd.workspace_id)
	WHERE pd.project_id = p_project_id
	UNION
	SELECT pd.project_id, 'sku' AS TYPE,
	AT.sku_id AS repository_id, AT.asset_version, AT.is_head
	FROM project_details pd
	INNER JOIN atgpub.dcs_sku AT ON (AT.workspace_id = pd.workspace_id)
	WHERE pd.project_id = p_project_id
	));
	c_get_project_id_cursor_type c_get_project_id%rowtype;
	v_assets_count NUMBER;
	BEGIN
	dbms_output.put_line('projectId, display_name,assets_count');
	OPEN c_get_project_id ('CatalogByR3FeedStibo%');
	LOOP
	FETCH c_get_project_id INTO c_get_project_id_cursor_type;
	EXIT WHEN c_get_project_id%notfound;
	OPEN c_get_count_assets (c_get_project_id_cursor_type.project_id);
	LOOP
	FETCH c_get_count_assets INTO v_assets_count;
	EXIT WHEN c_get_count_assets%notfound;
	dbms_output.put_line(c_get_project_id_cursor_type.project_id || ',' || c_get_project_id_cursor_type.display_name || ','|| v_assets_count );
	END LOOP;
	CLOSE c_get_count_assets;
	END LOOP;
	CLOSE c_get_project_id;
	END;

view raw assets_count.sql hosted with ❤ by GitHub

Special thanks to Roberto Ibarra, here you can find more bcc specific queries.
http://betoit.blogspot.com/2019/08/bcc-project-details-with-sql-queries.html