BPM LINQ with subquery

[ck.ong (ONG CHEE KEONG)]

Hi,

Currently i have this query:

Select SUM(d.OrderQty) ToBuildQty --d.OrderQty, ISNULL(j.ProdQty,0) ProdQty
 From erp.OrderDtl d 
 left join erp.OrderHed o ON d.Company = o.company and d.OrderNum = o.OrderNum
 where o.OpenOrder = 1 and o.voidorder = 0 
 and d.PartNum= 'PART0002482' 

Which able to convert to BPM by writing this:

decimal OrderQty = (from d in Db.OrderDtl 
                    join o in Db.OrderHed on 
                    new { d.Company, d.OrderNum } equals 
                    new { o.Company, OrderNum = o.OrderNum } into joinedData 
                    from o in joinedData.DefaultIfEmpty()
                    where o.OpenOrder && 
                     !o.VoidOrder && 
                    d.PartNum == Part.PartNum 
                   select d.OrderQty).Sum();

If i would like to join subquery like below:

Select SUM(d.OrderQty - ISNULL(j.ProdQty,0)) ToBuildQty 
 From erp.OrderDtl d 
 left join erp.OrderHed o ON d.Company = o.company and d.OrderNum = o.OrderNum
 left join 
 (Select u.Number01, u.Number02, h.PartNum, Sum(number04) AS ProdQty 
    from Ice.UD04 u left join JobHead h ON u.Company = h.company and u.key1 = h.JobNum 
	where h.Cancel_c = 0 
	group by u.Number01, u.Number02, h.PartNum) j 
 ON d.OrderNum = j.Number01 and d.OrderLine = j.Number02 and d.PartNum = j.PartNum
 where o.OpenOrder = 1 and o.voidorder = 0 
 and d.PartNum= 'PART0002482' 
 and d.OrderQty - ISNULL(j.ProdQty,0) > 0

How should i write in BPM level?
Or is it achievable through BPM control?

I will paste my full BPM query which is putting at jobhead.

foreach (var JobHead in ttJobHead.Where(r => r.RowMod == "U"))
{
    decimal ProdQty = JobHead.ProdQty;

    if (ProdQty != 0)
    {
        var PartPlant = Db.PartPlant.FirstOrDefault(r => r.Company == callContextClient.CurrentCompany && r.PartNum == JobHead.PartNum);
        var Part = Db.Part.FirstOrDefault(r => r.Company == callContextClient.CurrentCompany && r.PartNum == JobHead.PartNum);

        if (Part != null)
        {
            if (Part.PartNum != "")
            {
                if (PartPlant != null)
                {
                    if (PartPlant.SafetyQty != 0)
                    {
                        var PartBinExist = Db.PartBin.FirstOrDefault(r => r.Company == callContextClient.CurrentCompany && r.PartNum == JobHead.PartNum);

                        decimal PartOnHandQty = 0;
                        if (PartBinExist != null)
                        {
                            var PartBin1 = (from poRD1 in Db.PartBin
                                            where poRD1.Company == callContextClient.CurrentCompany && poRD1.PartNum == Part.PartNum
                                            select poRD1.OnhandQty).Sum();

                            PartOnHandQty = Convert.ToDecimal(PartBin1);
                        }

                        decimal PartProdQty = 0;
                        var PartJobExist = Db.JobHead.FirstOrDefault(r => r.Company == callContextClient.CurrentCompany && r.PartNum == JobHead.PartNum && r.JobComplete == false && r.JobClosed == false && r.Cancel_c == false);

                        if (PartJobExist != null)
                        {
                            decimal prodQty = (from poRD2 in Db.JobHead
                                               join p in Db.UD04 on
                                               new { poRD2.Company, poRD2.JobNum } equals
                                               new { p.Company, JobNum = p.Key1 } into joinedData
                                               from p in joinedData.DefaultIfEmpty()
                                               where poRD2.PartNum == Part.PartNum &&
                                                     !poRD2.JobComplete &&
                                                     !poRD2.JobClosed &&
                                                     !poRD2.Cancel_c &&
                                                     p.Key1 == null
                                               select poRD2.ProdQty).Sum();

                            PartProdQty = Convert.ToDecimal(prodQty);
                        }

                        decimal PartTotalQty = PartOnHandQty + PartProdQty;
                        
                        decimal OrderQty = (from d in Db.OrderDtl 
                                          join o in Db.OrderHed on 
                                          new { d.Company, d.OrderNum } equals 
                                          new { o.Company, OrderNum = o.OrderNum } into joinedData 
                                          from o in joinedData.DefaultIfEmpty()
                                          where o.OpenOrder && 
                                               !o.VoidOrder && 
                                               d.PartNum == Part.PartNum 
                                          select d.OrderQty).Sum();

                        if (PartPlant.SafetyQty < PartTotalQty)
                        {
                            throw new Ice.BLException("Error: OnHand qty " + String.Format("{0:0.##}", PartOnHandQty) +
                                                      " \nOpen JR qty " + String.Format("{0:0.##}", PartProdQty) +
                                                      " \nTotal Qty " + String.Format("{0:0.##}", PartTotalQty) +
                                                      " \nOrder Qty " + String.Format("{0:0.##}", OrderQty) +
                                                      " more than safety stock level qty " + String.Format("{0:0.##}", PartPlant.SafetyQty) +
                                                      " meet. No more JR can be issued. [Job.BPM]");
                        }
                    }
                }
            }
        }
    }
}

Regards,
CK Ong

[Hally (Simon Hall)]

I’d be inclined to test things with LinqPad if you are not doing so already. Here’s a link to the LINQPad starter…

[ Tools / Download ] Epicor LINQPad Starter Kit

[Epicor_LINQPad_5_1] Download: Epicor_10_LINQPad_Starter.linq (3.9 KB) Intro Have you always wanted to make use of LINQPad for BPM Prototyping, but got stuck in configuration limbo and weren’t quite sure how to reference assemblies to make use of the ErpContext? If yes, then this quick-starter guide is for you. I am going to assume you know what LINQPad is and what benefit’s you get with the professional version and if not then visit https://www.linqpad.net/ for more information. Screen…

[mbilodeau (Mathieu Bilodeau)]

At this point you could put that logic in a BAQ and call it from your BPM ; might be easier to test, debug and maintain.

[Doug.C (Doug Crabtree)]

ChatGPT says:

var result = (from d in Db.OrderDtl
			  join o in Db.OrderHed
			  on new { d.Company, d.OrderNum } equals new { o.Company, o.OrderNum } into orderJoin
			  from o in orderJoin.DefaultIfEmpty()
			  join j in (
				  from u in Db.UD04
				  join h in Db.JobHead
				  on new { u.Company, Number01 = u.key1, Number02 = u.Number02 } equals new { h.Company, h.JobNum, h.Number02 } into jobJoin
				  from h in jobJoin.DefaultIfEmpty()
				  where h.Cancel_c == 0
				  group new { u, h } by new { u.Number01, u.Number02, h.PartNum } into gj
				  select new
				  {
					  Number01 = gj.Key.Number01,
					  Number02 = gj.Key.Number02,
					  PartNum = gj.Key.PartNum,
					  ProdQty = gj.Sum(x => x.u.Number04)
				  }) 
			  on new { d.OrderNum, d.OrderLine, d.PartNum } equals new { j.Number01, j.Number02, j.PartNum } into prodJoin
			  from j in prodJoin.DefaultIfEmpty()
			  where o.OpenOrder == 1 && o.VoidOrder == 0 && d.PartNum == partNum && (d.OrderQty - (j.ProdQty ?? 0)) > 0
			  group d by 1 into gj
			  select new
			  {
				  ToBuildQty = gj.Sum(x => x.OrderQty - (j.ProdQty ?? 0))
			  }).FirstOrDefault();

But I have no idea if it will work. I would test with LINQPad

[ck.ong (ONG CHEE KEONG)]

Hi Mathieu,

Got any sample link can reference?
Currently my code is used to block new job created after calculate qty from different tables, by using BPM > Data Directive > jobhead > New In-Transaction Directive.

Regards,
CK Ong

[ck.ong (ONG CHEE KEONG)]

Hi Doug,

I try the code is not working. After modify abit and new error raised.

By the way, can know how u throw the question to chatGPT? As i cant get the same output as yours.

foreach (var JobHead in ttJobHead.Where(r => r.RowMod == "U"))
{
    decimal ProdQty = JobHead.ProdQty;

    if (ProdQty != 0)
    {
        var PartPlant = Db.PartPlant.FirstOrDefault(r => r.Company == callContextClient.CurrentCompany && r.PartNum == JobHead.PartNum);
        var Part = Db.Part.FirstOrDefault(r => r.Company == callContextClient.CurrentCompany && r.PartNum == JobHead.PartNum);

        if (Part != null)
        {
            if (Part.PartNum != "")
            {
                if (PartPlant != null)
                {
                    if (PartPlant.SafetyQty != 0)
                    {
                        var PartBinExist = Db.PartBin.FirstOrDefault(r => r.Company == callContextClient.CurrentCompany && r.PartNum == JobHead.PartNum);

                        decimal PartOnHandQty = 0;
                        if (PartBinExist != null)
                        {
                            var PartBin1 = (from poRD1 in Db.PartBin
                                            where poRD1.Company == callContextClient.CurrentCompany && poRD1.PartNum == Part.PartNum
                                            select poRD1.OnhandQty).Sum();

                            PartOnHandQty = Convert.ToDecimal(PartBin1);
                        }

                        decimal PartProdQty = 0;
                        var PartJobExist = Db.JobHead.FirstOrDefault(r => r.Company == callContextClient.CurrentCompany && r.PartNum == JobHead.PartNum && r.JobComplete == false && r.JobClosed == false && r.Cancel_c == false);

                        if (PartJobExist != null)
                        {
                            decimal prodQty = (from poRD2 in Db.JobHead
                                               join p in Db.UD04 on
                                               new { poRD2.Company, poRD2.JobNum } equals
                                               new { p.Company, JobNum = p.Key1 } into joinedData
                                               from p in joinedData.DefaultIfEmpty()
                                               where poRD2.PartNum == Part.PartNum &&
                                                     !poRD2.JobComplete &&
                                                     !poRD2.JobClosed &&
                                                     !poRD2.Cancel_c &&
                                                     p.Key1 == null
                                               select poRD2.ProdQty).Sum();

                            PartProdQty = Convert.ToDecimal(prodQty);
                        }

                        decimal PartTotalQty = PartOnHandQty + PartProdQty;
                        
                        //decimal OrderQty = (from d in Db.OrderDtl 
                        //                  join o in Db.OrderHed on 
                        //                  new { d.Company, d.OrderNum } equals 
                        //                  new { o.Company, OrderNum = o.OrderNum } into joinedData 
                        //                  from o in joinedData.DefaultIfEmpty()
                        //                  where o.OpenOrder && 
                        //                       !o.VoidOrder && 
                        //                       d.PartNum == Part.PartNum 
                        //                  select d.OrderQty).Sum();
                        
                        decimal OrderQty = (from d in Db.OrderDtl
                        join o in Db.OrderHed
                        on new { d.Company, d.OrderNum } equals 
                        new { o.Company, o.OrderNum } into orderJoin
                        from o in orderJoin.DefaultIfEmpty()
                        join j in (
                          from u in Db.UD04
                          join h in Db.JobHead
                          on new { u.Company, u.Key1 } equals 
                          new { h.Company, Key1 = h.JobNum } into jobJoin
                          from h in jobJoin.DefaultIfEmpty()
                          where !h.Cancel_c 
                          group new { u, h } by new { u.Number01, u.Number02, h.PartNum } into gj
                          select new
                          {
                            Number01 = gj.Key.Number01,
                            Number02 = gj.Key.Number02,
                            PartNum = gj.Key.PartNum,
                            ProdQty = gj.Sum(x => x.u.Number04)
                          }) 
                        on new { d.OrderNum, d.OrderLine, d.PartNum } equals 
                        new { OrderNum = j.Number01, OrderLine = j.Number02, PartNum = j.PartNum } into prodJoin
                        from j in prodJoin.DefaultIfEmpty()
                        where o.OpenOrder && 
                              !o.VoidOrder && 
                              d.PartNum == Part.PartNum && 
                              (d.OrderQty - (j.ProdQty ?? 0)) > 0
                        group d by 1 into gj
                        select new
                        {
                          ToBuildQty = gj.Sum(x => x.OrderQty - (j.ProdQty ?? 0))
                        }).FirstOrDefault();

                        if (PartPlant.SafetyQty < PartTotalQty)
                        {
                            throw new Ice.BLException("Error: OnHand qty " + String.Format("{0:0.##}", PartOnHandQty) +
                                                      " \nOpen JR qty " + String.Format("{0:0.##}", PartProdQty) +
                                                      " \nTotal Qty " + String.Format("{0:0.##}", PartTotalQty) +
                                                      " \nOrder Qty " + String.Format("{0:0.##}", OrderQty) +
                                                      " more than safety stock level qty " + String.Format("{0:0.##}", PartPlant.SafetyQty) +
                                                      " meet. No more JR can be issued. [Job.BPM]");
                        }
                    }
                }
            }
        }
    }
}

Regards,
CK Ong

[mbilodeau (Mathieu Bilodeau)]

If you want to go down that road, here’s a link to a blog post explaining how to call a BAQ from a BPM or Epicor Function:

Epicor - Calling a BAQ in BPM or Epicor Function — All About That ERP