Top Level Where Used Query

,

Here’s a good late Friday request.

I’ve been asked if there’s a way to pull Top Level Where Used for some lower level parts or a reverse Indented BOM report? Basically for a group of low-level stocked parts they want to report the information from Where Used screen if you were to track up all the way to the top. The tuff part is there can be multiple paths or multiple top-levels that use these parts.

Does anyone have any insight on how to go about this in either a BAQ or SQL? Is it even possible?

CTE is your friend here, but that sounds super hairy

I agree… This might be a Monday project; I think it might be beer thirty soon.

This could be fun; it’s sometimes referred to as a BOM IMPLOSION…

Here’s a really dumb and lazy way to do it. There are definitely improvements to be had, but it met my needs so I figured I’d share. It works off a fixed rawParts list (parts to analyze) and soldParts list (as this was much more efficient for me to pull it out once.

Crawls up indefinitely. Logs in console in a questionable color scheme. Writes output to file.

using EpicorRestAPI;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace UW_WhereUsedConsole
{
    class Program
    {
       static  StringBuilder sb = new StringBuilder();
       static  Dictionary<string, PartInfo> PartInfos = new Dictionary<string, PartInfo>();
//this could easily be queried from REST or BAQ
       static  List<string> soldParts = new List<string>()
        {            "YOURSOLDPARTSHERE","1002","1003","1004","1005","1006","1007","1008","1009","1010","1011","1012","10124","1013","1013FF","1013RP","1014","1019","1027","20GAGALA177","3000","3005","3006","3007","3011","3017","3023"
        };

        static List<string> rawParts = new List<string>()
        {            "YOURRAWPARTSHERE","4395DOT","4527","4527DOT","4563","4563DOT","4577","4749","4749DOT","4777","4777DOT","4791","4791DOT","4809","4809DOT","4822","4822DOT","4832","4832DOT","4838","4838DOT","4851","4851DOT"
        };

        static List<KeyValuePair<string, string>> AllSoldParts = new List<KeyValuePair<string, string>>();
        static void Main(string[] args)
        {
            InitRest();
            DoItGood();
        }

        static void InitRest()
        {
            EpicorRest.APIVersion = EpicorRestVersion.V1;
            EpicorRest.AppPoolHost = "EPICORSERVER";
            EpicorRest.AppPoolInstance = "EpicorInstance";
            EpicorRest.UserName = "chrisconn";            
            EpicorRest.Password = "HahaHasoNotGettingMyPassAgain";
            EpicorRest.IgnoreCertErrors = true;
            EpicorRest.CallSettings = new CallSettings("UWLVL", "MfgSy", "", "");
            EpicorRest.Company = "UWLVL";
        }

        private static void DoItGood()
        {
            AllSoldParts.Clear();

            sb.Clear();
            sb.AppendLine($"RawPart,SoldPart");
            foreach (string part in rawParts)
            {

                Console.BackgroundColor = ConsoleColor.Green;
                Console.WriteLine($"Starting RAW: {part}");
                PartInfos.Clear();
                var rawPart = new RawPart(part);
                GoUp(ref rawPart, part);
                foreach (string soldPart in rawPart.SoldParts)
                {
                    if (!AllSoldParts.Where(w => w.Key == rawPart.PartNum && w.Value == soldPart).Any())
                    {
                        AllSoldParts.Add(new KeyValuePair<string,string>(rawPart.PartNum, soldPart));
                    }
                 
                }           
            }
            //--- all done crawling, sum it up and write it

            //sb.AppendLine($"{rawPart.PartNum},{soldPart}");
            foreach (KeyValuePair<string, string> sp in AllSoldParts)
            {
                sb.AppendLine($"{sp.Key},{sp.Value}");
            }

            var path = "RawPartAnalysis.txt";
            File.WriteAllText(path, sb.ToString());

        }

        private static void GoUp(ref RawPart rawPart, string PartNum)
        {

            /*    if(PartInfos.ContainsKey(PartNum))
                {
                    //already done
                    return;
                }*/

            var parameters = new Dictionary<string, string>();
            parameters.Add("whereUsedPartNum", $"{PartNum}");
            parameters.Add("pageSize", "0");
            parameters.Add("absolutePage", "0");
            var res = EpicorRest.BoPost("Erp.BO.PartSvc", "GetPartWhereUsed", parameters);
            var pwu = res.ResponseData.returnObj.PartWhereUsed;

            //            if (PartInfos.ContainsKey(PartNum)) return;//we already crawled this guy up, dont do it again


            var pi = new PartInfo();
            pi.PartNum = PartNum;
            pi.Sold = IsSold(PartNum);

            if (!PartInfos.ContainsKey(PartNum))
                PartInfos.Add(PartNum, pi);

            Console.BackgroundColor = pi.Sold ? ConsoleColor.Blue : ConsoleColor.Yellow;
            Console.WriteLine($"Entering {PartNum}. IsSold:{pi.Sold}");
            if (pi.Sold) rawPart.AddSoldPart(PartNum);


            foreach (var pw in pwu)
            {
                string pwpart = pw.PartNum;
                string pwrev = pw.RevisionNum;
                Console.ResetColor();
                Console.WriteLine($"    {PartNum}. Parent:{pwpart}");


                //  if (!rawPart.IntermediateParts.ContainsKey(pwpart))
                {
                    //   rawPart.IntermediateParts.Add(pwpart, pwrev);
                    var isSold = IsSold(pwpart);


                    pi.UsedBy.Add(pwpart);
                    if (isSold)
                    {
                        rawPart.AddSoldPart(pwpart);
                        Console.BackgroundColor = pi.Sold ? ConsoleColor.Blue : ConsoleColor.Black;
                        Console.WriteLine($"RAW: {rawPart.PartNum} SOLD: {pi.PartNum}");
                    }
                    bool up = pw.CanTrackUp;
                    //dont mind me @Jose, just an infinite loop thats all
                    if (up) GoUp(ref rawPart, pwpart);
                }
            }
        }

        private static bool IsSold(string PartNum)
        {            
            //found it to be much more efficient to query all sold items first and work from memory
            var ret = soldParts.Contains(PartNum);
            return ret;
        }

        //represents a part we already crawled up (cause by nature we may encounter again)
        //but should we if we are looking at new raw part?
        public class PartInfo
        {
            public string PartNum { get; set; }
            public string Rev { get; set; }

            public bool Sold { get; set; }

            public List<string> UsedBy { get; set; }

            public PartInfo()
            {
                UsedBy = new List<string>();
            }

        }


        //represents lowest level
        public class RawPart
        {
            public string PartNum { get; set; }
            public List<string> SoldParts { get; set; }

            public Dictionary<string, string> IntermediateParts { get; set; }  //part/rev

            public RawPart(string partNum)
            {
                PartNum = partNum;
                SoldParts = new List<string>();
                IntermediateParts = new Dictionary<string, string>();
            }

            public void AddSoldPart(string PartNum)
            {
                if (!SoldParts.Contains(PartNum)) SoldParts.Add(PartNum);
            }
        }

    }
}



Results:
image