ck.ong
(ONG CHEE KEONG)
July 13, 2023, 9:35am
1
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
aarong
(Aaron Gulley)
July 13, 2023, 10:03am
2
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?
ck.ong
(ONG CHEE KEONG)
July 14, 2023, 1:28am
4
Hi Aaron,
Thanks for your solution. However, i try to paste the code but encounter another error:
Regards,
CK Ong
ck.ong
(ONG CHEE KEONG)
July 14, 2023, 1:34am
5
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
klincecum
(Kevin Lincecum)
July 14, 2023, 3:38am
6
UD04.Key1 is a string, JobNum is an int.
It’s why you are getting the error.
klincecum
(Kevin Lincecum)
July 14, 2023, 3:49am
7
poRD2.JobNum.ToString() in the top code should work
ck.ong
(ONG CHEE KEONG)
July 14, 2023, 3:55am
8
Hi Kevin,
I had another error prompt.
Regards,
CK Ong
klincecum
(Kevin Lincecum)
July 14, 2023, 3:57am
9
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.
ck.ong
(ONG CHEE KEONG)
July 14, 2023, 4:09am
10
Hi Kevin,
Its the same error i get after applied the “ToString” in the original code.
No problem, take your time.
Regards,
CK Ong
aarong
(Aaron Gulley)
July 14, 2023, 8:08am
11
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);
}
ck.ong
(ONG CHEE KEONG)
July 14, 2023, 8:45am
12
Hi Aaron,
The error is same appeared as the previously posted:
Regards,
CK Ong
aarong
(Aaron Gulley)
July 14, 2023, 8:47am
13
Please post your complete code…
Use -``` to format your code.
1 Like
aarong
(Aaron Gulley)
July 14, 2023, 8:51am
14
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);
}
ck.ong
(ONG CHEE KEONG)
July 14, 2023, 8:56am
15
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
aarong
(Aaron Gulley)
July 14, 2023, 9:02am
16
ck.ong:
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]");
}
}
}
}
}
}
}
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]");
}
}
}
}
}
}
}
aarong
(Aaron Gulley)
July 14, 2023, 9:03am
17
Note: You will need to replace
r.CheckOff1
With your UD Cancel_c as I don’t have that UD Column.
ck.ong
(ONG CHEE KEONG)
July 14, 2023, 9:12am
18
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
ck.ong
(ONG CHEE KEONG)
July 14, 2023, 9:24am
19
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.
aarong
(Aaron Gulley)
July 14, 2023, 9:53am
20
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