CODEWITHSUNDEEP
sap-commerce-cloud
Sanchit Khera
Sanchit Khera

Reputation: 1005

Hybris: Mark products with no super-category as inactive

We are performing catalog clean up task and require all products which have no super-category to be marked as inactive. Such products can be seen lying directly under the catalog root in PCM and not under any super-category.

I want to get the list of all such products and via impex I can update the approvalStatus of all such products as 'check', so that they are no longer visible to user at storefront.

Any help is greatly appreciated!

Upvotes: 2

Views: 920

Answers (1)

user6904265
user6904265

Reputation: 1938

Get your target products

The idea is to extract all products from 'YOUR_CATALOG_ID' except (not in) all the products that have at least one category. You could use this flexible search:

select {p.pk}
from { Product as p join CatalogVersion as cv on {p.catalogversion}={cv.pk}
  join Catalog as catalog on {cv.catalog}={catalog.pk} }
where {catalog.id}='YOUR_CATALOG_ID'
and {cv.version}='Staged'
and {p.pk} not in ({{
  select {p.pk}
  from {Product as p join CategoryProductRelation as pc on {p.pk}={pc.target}
    join Category as c on {pc.source}={c.pk} 
    join CatalogVersion as cv on {p.catalogversion}={cv.pk}
    join catalog as catalog on {cv.catalog}={catalog.pk}}
  where {catalog.id}='YOUR_CATALOG_ID'
  and {cv.version}='Staged'
}})

You should replace "Product" with your custom Product Type if your data model has been customized.

Update products approvalStatus

As second step you could create an impex in order to change the approvalStatus:

INSERT_UPDATE Product;code[unique=true];approvalstatus(code);
;target_product_pk;check;

Upvotes: 4

Related Questions