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
I meant in your original code. Didn’t look at that code, and I gotta run 
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);
}
Please post your complete code…
Use -``` to format your code.
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.
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;