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()));
           
    }
}

Comments

Post a Comment

Popular posts from this blog

SysOperationFramework with use of Query

Image
 The SysOperation framework is very good and effective, but unforutnatly a bit complicated to get in to and do as you want.  I had one case in which a simple table should be updated, the job was to be able to set in batch. Also it had to use a query so that the user could easily set up the ranges as he pleased. Note that I will use the same query for updating the table which I mentioned. So the same query is used all the way through ending in using Quer::updateRecordset For this you need: Data contract class Controller class Service class Simple query oject Action Menu item Main menu where to make the menu item available. 1. Data contract class Note the simple query highlighted in bright yellow .  [DataContractAttribute     , SysOperationGroupAttribute('Parameters',  "Parameters", '1')] internal final class CustAutoCancelUpdateDataContract {     str             packedQuery;     MCRAutoCancel  ...
Read more

Customer payment transaction, mark for settlement against invoice and post

Pre requisite is that you have your CustInvoiceJour table containing the invoice you wanted to receive payment for and settle. //... //Create your LedgerJournalTable and LedgerJournalTrans //ledgerJournalTrans.InvoiceId must be blank to hinder this becoming a free text invoice ledgerJournalTrans.MarkedInvoice = custInvoiceJour.InvoiceId; ledgerJournalTrans.MarkedInvoiceCompany = custInvoiceJour.DataAreaId; ledgerJournalTrans.MarkedInvoiceRecId = CustTransOpen::getFirstDueRecIdByInvoice(ledgerJournalTrans.MarkedInvoice); ledgerJournalTrans.insert(); //mark the open customer transactions LedgerJournalEngine_CustPayment::updateMarkedInvoiceSpecTrans(ledgerJournalTrans); After posting the payment journal, if everything validates, your transactions will be settled. //Post the journal LedgerJournalCheckPost::newLedgerJournalTable(ledgerJournalTable, NoYes::Yes).runOperation();
Read more

Retail Attributes on sales lines

Image
Recently I've dived into Retail. There will be some posts about retail and the problems I've faced with solutions. First off will be the Retail Attributes, they can be problematic here is a data base diagram illustrating a simplyfied view, assuming the value is of type text. And here is the select statement for listing all sales lines with their attributes.         SalesTable salesTable;         SalesLine salesLine;         RetailSalesTableInstanceValue instanceValue;         EcoResAttribute attribute;         EcoResAttributeValue attributeValue;         EcoResTextValue      textValue;     while select salesId                  from  salesLine  ...
Read more