Useful ATG/Oracle Commerce BCC tricks

Finding a BCC project from Workspace ID or Finding Workspace ID from project name

Using SQL- From Database- CA/Publishing schema

select DISPLAY_NAME from epub_project where workspace in ( select name from avm_devline where id = 16801 );

In above SQL workspace_id is 16801 

select ID from avm_devline where name in (select workspace from epub_project where DISPLAY_NAME ='PROJECT_DISPLAY_NAME');

Using RQL in Dyn/Admin - Repositories

Get name(workspace) from below RQL

Repository : /atg/epub/version/VersionManagerRepository

RQL : <query-items item-descriptor="developmentLine">id="16801"</query-items>

Sample Output :

<add-item item-descriptor="workspace" id="16801">

  <set-property name="checkedIn"><![CDATA[true]]></set-property>

  <set-property name="checkinTime"><![CDATA[9/10/2015 01:43:51]]></set-property>

  <set-property name="comment"><![CDATA[Checkin of workspace, epub-prj153002, by workflow action checkInProject.]]></set-property>

  <set-property name="dateCreated"><![CDATA[8/26/2015 01:47:40]]></set-property>

  <set-property name="userid"><![CDATA[admin]]></set-property>

  <set-property name="isEditable"><![CDATA[false]]></set-property>

  <set-property name="parent"><![CDATA[10100]]></set-property>

  <!-- rdonly   <set-property name="type"><![CDATA[workspace]]></set-property>  -->

  <set-property name="name"><![CDATA[epub-prj153002]]></set-property>

</add-item>

Get Project Name using below RQL

Repository : /atg/epub/PublishingRepository/

<query-items item-descriptor="project">workspace="epub-prj153002"</query-items>

Sample Output :

<add-item item-descriptor="project" id="prj153002">

  <set-property name="history"><![CDATA[hst153009]></set-property>

  <set-property name="workspace"><![CDATA[epub-prj153002]]></set-property>

  <!-- rdonly   <set-property name="assets"><![CDATA[]]></set-property>  -->

  <set-property name="creator"><![CDATA[portal-admin]]></set-property>

  <set-property name="checkedIn"><![CDATA[true]]></set-property>

  <set-property name="deploymentSnapshots"><![CDATA[tar1472=17305,tar1463=17304]]></set-property>

  <set-property name="displayName"><![CDATA[Test Project ]]></set-property>

  <set-property name="completionDate"><![CDATA[9/10/2015 01:43:51]]></set-property>

  <!-- rdonly   <set-property name="checkedInAssets"><![CDATA[]]></set-property>  -->

  <!-- rdonly   <set-property name="version"><![CDATA[140]]></set-property>  -->

  <set-property name="deploymentApprovalTimes"><![CDATA[tar1472=8/26/2015 09:13:22,tar1463=8/26/2015 04:58:53]]></set-property>

  <set-property name="deploymentStatuses"><![CDATA[tar1472=3,tar1463=3]]></set-property>

  <set-property name="deploymentTimes"><![CDATA[tar1472=8/26/2015 09:14:03,tar1463=8/26/2015 04:59:24]]></set-property>

  <set-property name="checkinDate"><![CDATA[9/10/2015 01:43:51]]></set-property>

  <set-property name="acl"><![CDATA[]]></set-property>

  <set-property name="deploymentIds"><![CDATA[tar1472=14600012,tar1463=14300009]]></set-property>

  <set-property name="status"><![CDATA[Completed]]></set-property>

  <set-property name="creationDate"><![CDATA[8/26/2015 01:47:40]]></set-property>

  <set-property name="editable"><![CDATA[false]]></set-property>

  <set-property name="locked"><![CDATA[true]]></set-property>

</add-item>

To Get number of assets in a BCC project from DB

• There is no direct DB association between a BCC project and assets in the project.
• So, here is a sample to get number of assets from project id assuming project has only category, product, media and/or sku.
• Note : In below SQL you just have to use"primary" table of each item type

select epub.display_name,assets.total from
PUBLISH.epub_project epub,
PUBLISH.avm_devline devline,
(select sum(tests) total, workspace_id from (
select count(1) tests, workspace_id  from publish.dcs_product group by workspace_id
union
select count(1) tests,workspace_id  from publish.dcs_sku group by workspace_id
union
select count(1) tests,workspace_id from publish.dcs_media group by workspace_id
union
select count(1) tests,workspace_id  from publish.dcs_folder group by workspace_id
union
select count(1) tests,workspace_id  from PUBLISH.dcs_category group by workspace_id
) group by workspace_id ) assets
where
epub.project_id='prj252002' and
epub.workspace = devline.name
and assets.workspace_id = devline.id
order by assets.total desc
;

BCC Deleting Project

Here I have listed out SQL to delete

1) A particular project
2) All active projects
3) All projects

Deleting a particular project

-- Find the project
select * from EPUB_PROJECT  where display_name like '%Catalog Import%' and status=0;

--Delete asset from Lock table
delete from avm_asset_lock where workspace_id in (select id from avm_devline where name in
(select workspace from epub_project where project_id = 'prj154011'));

---- delete history of the project
delete from EPUB_PR_HISTORY where project_id in (select project_id from epub_project where project_id = 'prj154011');
--
---- delete the project
delete from epub_project where project_id = 'prj154011';
--
---- delete history of the process
delete from EPUB_PROC_HISTORY where process_id in (select process_id from epub_process where project = 'prj154011');
--
---- delete task information of process
delete from EPUB_PROC_TASKINFO where id in (select process_id from epub_process where project = 'prj154011');
--
---- delete states of project (if any)
delete  from EPUB_IND_WORKFLOW where process_id in (select process_id from epub_process where project = 'prj154011');
--
---- finally delete the process
delete from epub_process where  project = 'prj154011';

Deleting all active/open projects

delete from avm_asset_lock ;

delete from EPUB_PR_HISTORY where project_id in (select project_id from epub_project where status=0);

delete from EPUB_PROC_HISTORY where process_id in

(select process_id from epub_process where project in (select project_id from epub_project where status=0));

delete from EPUB_PROC_TASKINFO where id in

(select process_id from epub_process where project in (select project_id from epub_project where status=0));

delete from EPUB_WORKFLOW_STRS where id in

(select ID from EPUB_IND_WORKFLOW where process_id in

(select process_id from epub_process where project in (select project_id from epub_project where status=0)));

delete  from EPUB_IND_WORKFLOW where process_id in

(select process_id from epub_process where project in (select project_id from epub_project where status=0));

delete from epub_process where project in (select project_id from epub_project where status=0);

delete from epub_project where status=0;

Deleting all projects

delete from EPUB_PR_HISTORY;

delete from EPUB_PROC_HISTORY;

delete from EPUB_PROC_TASKINFO ;

delete from EPUB_WORKFLOW_STRS ;

delete  from EPUB_IND_WORKFLOW ;

delete from epub_process ;

delete from epub_project ;

delete from avm_asset_lock;