List all product in assortment for a store

A customer wanted a functionality that needed the listing of all products in a stores assortment. The assortment for a store can be viewed in the online stores form.

To list these is quite cumbersome and involves quite a few tables. I got this query by debugging the init method of the called query. Rewrote it from standard MS SQL syntax to X++ select statement. The statement lists the same products as viewed in the View assortment  products button.

private void onlineStoreProductListByOperatingUnitId(RefRecId _omOperatingUnit)

{

   

    EcoResProduct ecoResProduct;

    EcoResProductMaster ecoResProductMaster;

    EcoResProductDimensionGroupProduct ecoResProductDimensionGroupProduct;

    EcoResProductDimensionGroup ecoResProductDimensionGroup;

    EcoResProductTranslation ecoResProductTranslation;

    InventTable inventTable;

    RetailChannelAssortedProductView retailChannelAssortedProductView;

    while select RecID, DisplayProductNumber,InstanceRelationType

        from ecoResProduct

        ORDER BY EcoResProduct.DisplayProductNumber ASC

        WHERE ((ecoResProduct.InstanceRelationType == 10812 || ecoResProduct.InstanceRelationType == 13678))

    OUTER JOIN ProductDimensionGroup

        FROM EcoResProductDimensionGroupProduct

        where EcoResProductMaster.RecId == EcoResProductDimensionGroupProduct.Product

    OUTER JOIN Name

        FROM EcoResProductDimensionGroup

        where EcoResProductDimensionGroupProduct.ProductDimensionGroup == EcoResProductDimensionGroup.RecId

        OUTER JOIN Name

            FROM ecoResProductTranslation

            Where ecoResProduct.RecId == ecoResProductTranslation.Product

            && ecoResProductTranslation.LanguageId == 'en-us'

            JOIN ItemId

                from inventTable

                where ecoResProduct.RecId == inventTable.Product

                EXISTS JOIN retailChannelAssortedProductView

                    WHERE inventTable.Product == retailChannelAssortedProductView.ProductID

                    && inventTable.dataAreaId == retailChannelAssortedProductView.InventLocationDataAreaId

                    && retailChannelAssortedProductView.OMOperatingUnitId == _omOperatingUnit

    {

        //Product number, Product Name, Currency, Price, Price Adjustment, Discount, Active Price, Calculated discount

        Info(strFmt('%1 - %2 - %3', retailChannelAssortedProductView.ProductID, InventTable.ItemId, InventTable.itemDescriptionOrName()));

           

    }

}