Issue Material Dashboard

Epicor ERP 10
1

Hi all,
trying to build a BAQ to show me all the materials where the Issued Complete checkbox is not selected but the technical details are not showing and DB field and I’m lost on how to accomplish this, any guidance will be appreciated.
Thanks

image

2

When you don’t see a DB Field, it usually means that it is calculated from the Business Object.

I believe what you are looking for is JobMtl.IssuedComplete

2 Likes
3

you are right, but then something is wrong with my BAQ as that filter is not working, not filtering the data as needed

4

What does your BAQ look like?

5

Kind of like an advanced spreadsheet, but that’s not important right now.

1 Like
6

:point_down:

select 
	[JobMtl].[IssuedComplete] as [JobMtl_IssuedComplete],
	[JobHead].[ProjectID] as [JobHead_ProjectID],
	[Project].[Description] as [Project_Description],
	[JobHead].[JobNum] as [JobHead_JobNum],
	[JobHead].[PartDescription] as [JobHead_PartDescription],
	[JobMtl].[AssemblySeq] as [JobMtl_AssemblySeq],
	[JobMtl].[MtlSeq] as [JobMtl_MtlSeq],
	[JobMtl].[PartNum] as [JobMtl_PartNum],
	[JobMtl].[Description] as [JobMtl_Description],
	[JobMtl].[RequiredQty] as [JobMtl_RequiredQty],
	[JobMtl].[RelatedOperation] as [JobMtl_RelatedOperation],
	[OpMaster].[OpDesc] as [OpMaster_OpDesc],
	[JobMtl].[IssuedQty] as [JobMtl_IssuedQty],
	[JobMtl].[ReqDate] as [JobMtl_ReqDate],
	[JobMtl].[BuyIt] as [JobMtl_BuyIt],
	[JobMtl].[Direct] as [JobMtl_Direct],
	[PORel].[PONum] as [PORel_PONum],
	[PORel].[POLine] as [PORel_POLine],
	[PORel].[PORelNum] as [PORel_PORelNum],
	[PORel].[DueDate] as [PORel_DueDate],
	[JobHead].[JobClosed] as [JobHead_JobClosed],
	[JobHead].[JobComplete] as [JobHead_JobComplete],
	[JobHead].[CheckBox02] as [JobHead_CheckBox02],
	[PODetail].[DocUnitCost] as [PODetail_DocUnitCost],
	[PORel].[RelQty] as [PORel_RelQty],
	(PORel.XRelQty * PODetail.DocUnitCost) as [Calculated_RelAmount],
	[JobMtl].[PurComment] as [JobMtl_PurComment],
	[OpMasDtl].[ResourceGrpID] as [OpMasDtl_ResourceGrpID],
	[ResourceGroup].[Description] as [ResourceGroup_Description]
from Erp.JobHead as JobHead
inner join Erp.JobMtl as JobMtl on 
	JobHead.Company = JobMtl.Company
	and JobHead.JobNum = JobMtl.JobNum
	and ( JobMtl.IssuedComplete = 0  )

left outer join Erp.PORel as PORel on 
	JobMtl.Company = PORel.Company
	and JobMtl.JobNum = PORel.JobNum
	and JobMtl.AssemblySeq = PORel.AssemblySeq
	and JobMtl.MtlSeq = PORel.JobSeq
left outer join Erp.PODetail as PODetail on 
	PORel.Company = PODetail.Company
	and PORel.PONUM = PODetail.PONum
	and PORel.POLine = PODetail.POLine
left outer join Erp.POHeader as POHeader on 
	PODetail.Company = POHeader.Company
	and PODetail.PONum = POHeader.PONUM
left outer join Erp.PurAgent as PurAgent on 
	POHeader.Company = PurAgent.Company
	and POHeader.BuyerID = PurAgent.BuyerID
left outer join Erp.JobOper as JobOper on 
	JobMtl.Company = JobOper.Company
	and JobMtl.JobNum = JobOper.JobNum
	and JobMtl.AssemblySeq = JobOper.AssemblySeq
	and JobMtl.RelatedOperation = JobOper.OprSeq
left outer join Erp.OpMaster as OpMaster on 
	JobOper.Company = OpMaster.Company
	and JobOper.OpCode = OpMaster.OpCode
left outer join Erp.OpMasDtl as OpMasDtl on 
	OpMaster.Company = OpMasDtl.Company
	and OpMaster.OpCode = OpMasDtl.OpCode
left outer join Erp.ResourceGroup as ResourceGroup on 
	OpMasDtl.Company = ResourceGroup.Company
	and OpMasDtl.ResourceGrpID = ResourceGroup.ResourceGrpID
inner join Erp.Project as Project on 
	JobHead.Company = Project.Company
	and JobHead.ProjectID = Project.ProjectID
where (JobHead.JobClosed = 0  and not JobHead.JobNum in ('B%', 'F%'))
8

This looks good to me.
Keep in mind Erp.Project is an inner join so this query will only return jobs that are linked to projects.

1 Like