BPM LINQ with UD table

, ,

Hi,

I would like to ask how to join UD table to get the data?

Try join normal table is working:

UD table got issue:

Ice.UD04 table is used to link between Job Order and Sales Order, where Key1 is linked to JobHead.JobNum and Number01 is linked to OrderHed.OrderNum.
This BPM is used to calculate total ProdQty that had linked with Orders. If not exist in UD04 table, means the job order is not linked with Orders.

Regards,
CK Ong

Try

decimal prodQty = 0;

if (PartJobExist != null)
{
    prodQty = Db.JobHead
        .Join(Db.UD04,
            poRD2 => new { poRD2.Company, poRD2.JobNum },
            p => new { p.Company, Key1 = p.Key1.Trim() },
            (poRD2, p) => new { poRD2, p })
        .Where(joinedData =>
            joinedData.poRD2.Company == Session.CompanyID &&
            joinedData.poRD2.PartNum == Part.PartNum &&
            !joinedData.poRD2.JobComplete &&
            !joinedData.poRD2.JobClosed &&
            !joinedData.poRD2.Cancel_c)
        .Sum(joinedData => joinedData.poRD2.ProdQty);
}


The erp.JobProd table already contains the links between jobs and sales orders, why do you need a UD table for that?

Hi Aaron,

Thanks for your solution. However, i try to paste the code but encounter another error:

Regards,
CK Ong

Hi Alisa,

I am not sure that too. Because previously the consultant did customized another screen to link SO. That why at erp.jobProd table OrderNum,OrderLine and OrderRel all showing 0. I cant differentiate the job from there.

Regards,
CK Ong

UD04.Key1 is a string, JobNum is an int.

It’s why you are getting the error.

poRD2.JobNum.ToString() in the top code should work

Hi Kevin,

I had another error prompt.

Regards,
CK Ong

I meant in your original code. Didn’t look at that code, and I gotta run :sob:

I’ll hit you back later if still needed.

Hi Kevin,

Its the same error i get after applied the “ToString” in the original code.

No problem, take your time.

Regards,
CK Ong

Try

decimal prodQty = 0;

if (PartJobExist != null)
{
    prodQty = Db.JobHead
        .Join(Db.UD04,
            poRD2 => new { poRD2.Company, poRD2.JobNum },
            p => new { p.Company, Key1 = p.Key1 },
            (poRD2, p) => new { poRD2, p })
        .Where(joinedData =>
            joinedData.poRD2.Company == Session.CompanyID &&
            joinedData.poRD2.PartNum == Part.PartNum &&
            !joinedData.poRD2.JobComplete &&
            !joinedData.poRD2.JobClosed &&
            !joinedData.poRD2.Cancel_c)
        .Sum(joinedData => joinedData.poRD2.ProdQty);
}

Hi Aaron,

The error is same appeared as the previously posted:

Regards,
CK Ong

Please post your complete code…

Use -``` to format your code.

1 Like
decimal prodQty = 0;

if (PartJobExist != null)
{
    prodQty = Db.JobHead
        .Join(Db.UD04,
            job => new { job.Company, job.JobNum },
            ud04 => new { ud04.Company, Key1 = ud04.Key1 },
            (job, ud04) => new { Job = job, UD04 = ud04 })
        .Where(joinedData =>
            joinedData.Job.Company == Session.CompanyID &&
            joinedData.Job.PartNum == Part.PartNum &&
            !joinedData.Job.JobComplete &&
            !joinedData.Job.JobClosed &&
            !joinedData.Job.Cancel_c)
        .Sum(joinedData => joinedData.Job.ProdQty);
}

Hi Aaron,

My code is quite long.
Anyway this is created under BPM >> Data Directive Maintenance >> JobHead >> In-Transaction

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 PartOnHandQty = 100;
                  
                }
                
                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)
                {
                  //var PartProdQty1 = (from poRD2 in Db.JobHead 
                  //                    join p in Db.UD04 on
                  //                     new {poRD2.Company, poRD2.JobNum.ToString()} equals 
                  //                     new {p.Company, p.Key1}
                  //                    where poRD2.Company == callContextClient.CurrentCompany && poRD2.PartNum == Part.PartNum && poRD2.JobComplete == false && poRD2.JobClosed == false && poRD2.Cancel_c == false && p.Key1 == null select poRD2.ProdQty).Sum();
                  
                  var PartProdQty1 = Db.JobHead
                            .Join(Db.UD04,
                                poRD2 => new { poRD2.Company, poRD2.JobNum },
                                p => new { p.Company, Key1 = p.Key1 },
                                (poRD2, p) => new { poRD2, p })
                            .Where(joinedData =>
                                joinedData.poRD2.Company == Session.CompanyID &&
                                joinedData.poRD2.PartNum == Part.PartNum &&
                                !joinedData.poRD2.JobComplete &&
                                !joinedData.poRD2.JobClosed &&
                                !joinedData.poRD2.Cancel_c)
                            .Sum(joinedData => joinedData.poRD2.ProdQty);

                  PartProdQty = Convert.ToDecimal(PartProdQty1);
                }
                
                decimal PartTotalQty = PartOnHandQty + PartProdQty;
                
                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) + " more than safety stock level qty " + String.Format("{0:0.##}", PartPlant.SafetyQty) + " meet. No more JR can be issued. [Job.BPM]");
                }
              }
            
          }
          
         }
      }
    }
    
}

Regards,
CK Ong

Here you go.

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 PartOnHandQty = 100;
                  
                }
                
                decimal PartProdQty = 0;
                var PartJobExist = Db.JobHead.FirstOrDefault(r => r.Company == callContextClient.CurrentCompany && r.PartNum == JobHead.PartNum && r.JobComplete == false && r.JobClosed == false && r.CheckOff1 == false);
                
                if (PartJobExist != null)
                {
                  //var PartProdQty1 = (from poRD2 in Db.JobHead 
                  //                    join p in Db.UD04 on
                  //                     new {poRD2.Company, poRD2.JobNum.ToString()} equals 
                  //                     new {p.Company, p.Key1}
                  //                    where poRD2.Company == callContextClient.CurrentCompany && poRD2.PartNum == Part.PartNum && poRD2.JobComplete == false && poRD2.JobClosed == false && poRD2.Cancel_c == false && p.Key1 == null select poRD2.ProdQty).Sum();
                  
                      decimal prodQty = 0;
                      
                if (PartJobExist != null)
                {
                    prodQty = Db.JobHead
                        .Where(job => job.Company == Session.CompanyID &&
                                       job.PartNum == Part.PartNum &&
                                       !job.JobComplete &&
                                       !job.JobClosed &&
                                       !job.CheckOff1)
                        .Select(job => job.ProdQty)
                        .DefaultIfEmpty(0)
                        .Sum();
                }

                  PartProdQty = Convert.ToDecimal(prodQty);
                }
                
                decimal PartTotalQty = PartOnHandQty + PartProdQty;
                
                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) + " more than safety stock level qty " + String.Format("{0:0.##}", PartPlant.SafetyQty) + " meet. No more JR can be issued. [Job.BPM]");
                }
              }
            
          }
          
         }
      }
    }
    
}

Note: You will need to replace

r.CheckOff1

With your UD Cancel_c as I don’t have that UD Column.

Hi Aaron,

This code is working fine. However, my initial question is to join UD04 table and get the sum qty that is not linked with orders.

Regards,
CK Ong

Actually if writing in SQL server is simple as this only:

select Sum(ProdQty)  
from JobHead as poRD2 
left join Ice.UD04 p On poRD2.Company = p.Company and poRD2.JobNum = p.Key1
where poRD2.PartNum = 'PART0002416' 
and poRD2.JobComplete = 0 
and poRD2.JobClosed = 0 
and poRD2.Cancel_c = 0 
and p.Key1 is null

But i find so hard to translate it back into BPM level. :sob:

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.CheckOff1 == 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.CheckOff1 &&
                                                     p.Key1 == null
                                               select poRD2.ProdQty).Sum();

                            PartProdQty = Convert.ToDecimal(prodQty);
                        }

                        decimal PartTotalQty = PartOnHandQty + PartProdQty;

                        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) +
                                                      " more than safety stock level qty " + String.Format("{0:0.##}", PartPlant.SafetyQty) +
                                                      " meet. No more JR can be issued. [Job.BPM]");
                        }
                    }
                }
            }
        }
    }
}

This is your SQL in LINQ…

Again, CheckOff1 is your Cancel_c

Furthermore, Convert.ToDecimal(prodQty); is already a decimal above. This is not needed.

Just PartProdQty = prodQty;

1 Like