4 Replies Latest reply on Nov 21, 2016 9:14 AM by Shareef Mohammad

    AQL Query

    Shareef Mohammad Master

      Hi Experts,

       

      Can anyone provide me the Query to extract the list of Items in each of the hosted Catalogs.

       

       

      Thanks.

        • Re: AQL Query
          PreethiKencha Expert

          All the catalog items are stored in XML format in this class ariba."catalog".admin.core.StagedBag

          field - Clob[ariba.catalog.admin.core.CatalogLongString] .

          it is very difficult to extract the contents of the field in a column format.

           

          if you want to see the list of all items in a catalog or items active in your system , you can use below query .

          ariba.procure.core.CatalogReportingEntry gives us ability to report on the catalog items. This class is populated when we run Generate Subscriptions  scheduled task

           

           

          SELECT

          CatalogReportingEntry.SupplierPartNumber AS SupplierPartNumber,

          CatalogReportingEntry.Description.Description AS Description,

          CatalogReportingEntry.Description.CommonSupplier.Name AS Supplier,

          CatalogReportingEntry.Description.CommonSupplier AS SupplierBaseID,

          CatalogReportingEntry.Description.ManPartNumber AS ManPartNumber,

          CatalogReportingEntry.Description.LeadTime AS LeadTime,

          CatalogReportingEntry.Description.UnitOfMeasure.Name AS UOM,

          CatalogReportingEntry.Description.Price.ApproxAmountInBaseCurrency AS Price,

          CatalogReportingEntry.Description.Price.AmountInReportingCurrency AS RepPrice,

          CatalogReportingEntry.Description.CommonCommodityCode.Name AS CommodityCode,

          CatalogReportingEntry.Description.CommonCommodityCode AS CommodityCodeBaseID

          FROM ariba.procure.core.CatalogReportingEntry

               JOIN ariba.procure.core.ProductDescription USING CatalogReportingEntry.Description

          LEFT OUTER JOIN ariba.basic.core.CommodityCode USING CommonCommodityCode

          WHERE

               CatalogReportingEntry.Description.ExpirationDate > currentdate() OR

               CatalogReportingEntry.Description.ExpirationDate IS NULL

           

          Hope this helps!