Subcontract Ship List BAQ

[Nick_Crow]

I am trying to create a subcontract ship list as a BAQ because I can’t seem to find anywhere in Epicor that shows this.

Basically what I’m looking for in the BAQ is -

-Job status is open
-Job operations is marked subcontract
-Job operations is not complete
This is the hardest one for me -
-No open PO lines/releases for the job/ sequence number. (We don’t use subcontract shipment entry so I’m using this as that. If there is an open PO for that job/ seq the subcontract op has been shipped, and does not need to appear on this list.)

I’ve tried PO.Rel and used job number = job number, op seq = op seq. With criteria of line/ release = open, but the issue is if there is not PO, than the job doesn’t show up at all in the BAQ.

Here is what our list currently looks like -

Any ideas on how to take jobs off that list that have an open PO for that subcontract sequence? I’m probably missing something obvious.

[dcamlin (David Camlin)]

I would try to join PartTran to JobOper table on:

JobNum = JobNum
AssemblySeq = AssemblySeq
OprSeq = JobSeq

This will allow you to see all part transactions against those op sequences.

BUT… if you add a couple Table Criteria to the PartTran table:
JobSeqType = S (subcontract)
TranType ISNULL

… you should then only get results of operations that haven’t been transacted against yet. So, parts that haven’t yet shipped out.

[dcamlin (David Camlin)]

@Nick_Crow

We don’t use subcontract shipment entry

Ah crap… I missed that part… ummm… the above may not work in that case. The above would basically filter out the mfg-Ven transactions. But since you’re not using subcontract shipments… you wouldn’t have those anyway.

So, sorry, that approach may not work in your case.

[dcamlin (David Camlin)]

@Nick_Crow

I’ve tried PO.Rel and used job number = job number, op seq = op seq. With criteria of line/ release = open, but the issue is if there is not PO, than the job doesn’t show up at all in the BAQ.

To me this portion here sounds like it could just be a join issue.

If you’re using an inner join and getting matching records

, you’ll only get the records where PO’s exist.

But if you do an outer join,

you should maintain all JobNums whether they have a PO referenced against them or not.

[Nick_Crow]

Here is what I have now -

Now the rows with PO numbers, I don’t want to see those on this list.

How would I make criteria to eliminate those only?

Thanks for the help!

[dcamlin (David Camlin)]

Click on your PORel table… add a Table Criteria of: PONum ISNULL

Try and see if that works. It should only return rows where PONum is empty.

[Nick_Crow]

Awesome, this worked. Thank you!

[Nick_Crow]

Okay, spoke too soon.

Here is the report before adding PO is null

Here is after -

It essential just got rid of the PO #'s.

Any ideas?

[dcamlin (David Camlin)]

Change the join on PORel:

back to an inner join

[Nick_Crow]

When I do that, no rows return in the BAQ

[dcamlin (David Camlin)]

Can you paste your query in here so we can see it?

[Nick_Crow]

Here you go -

/*

• Disclaimer!!!
• This is not a real query being executed, but a simplified version for general vision.
• Executing it with any other tool may produce a different result.
  */

select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[Vendor].[Name] as [Vendor_Name],
[JobOper].[PurPoint] as [JobOper_PurPoint],
[JobOper].[CommentText] as [JobOper_CommentText],
[JobOper].[StartDate] as [JobOper_StartDate],
[JobOper].[DaysOut] as [JobOper_DaysOut],
[JobOper].[DueDate] as [JobOper_DueDate],
[PORel].[PONum] as [PORel_PONum]
from Erp.JobHead as JobHead
inner join Erp.JobOper as JobOper on
JobHead.Company = JobOper.Company
and JobHead.JobNum = JobOper.JobNum
and ( JobOper.SubContract = 1 and JobOper.OpComplete = 0 )

inner join Erp.Vendor as Vendor on
JobOper.Company = Vendor.Company
and JobOper.VendorNum = Vendor.VendorNum
inner join Erp.PORel as PORel on
JobOper.Company = PORel.Company
and JobOper.JobNum = PORel.JobNum
and JobOper.OprSeq = PORel.JobSeq
and ( PORel.PONum is null )

where (JobHead.JobComplete = 0)
order by JobOper.StartDate

[dcamlin (David Camlin)]

Umm… change your join back to a left outer join.

Then try this criteria:

Don’t miss that NOT checkbox.

So, we only want records where PONum is NOT > 0.

[Nick_Crow]

This for some reason, still returns all the rows… Hmmm

[dcamlin (David Camlin)]

@Nick_Crow

inner join Erp.PORel as PORel on
JobOper.Company = PORel.Company
and JobOper.JobNum = PORel.JobNum
and JobOper.OprSeq = PORel.JobSeq

Add:

and JobOper.AssemblySeq = PORel.AssemblySeq

in that join criteria… wonder if other assemblies are pulling in oddities.

[Nick_Crow]

No dice on that either. Would a sub query be of any help?

[dcamlin (David Camlin)]

Yeah, I was just testing that and it works (I think). Still not sure why it is required.

SubQuery:

(ignore the table criteria on JobOper… I was just limiting it to one job so test with).

TOP Level Query:

DO Need the Subquery criteria which we tried before as a table criteria:

[dcamlin (David Camlin)]

With the subquery criteria:

31 rows on this job

Without the subquery criteria… I get 58 rows and PO Nums start appearing:

[Nick_Crow]

I think this works, thank you! Happy Holidays!