Hi I have made a BAQ which is working no problem at all its a simple one but dose the job. Im using PartTran and PORel for a goodsin BAQ Report now here is the Question Finance has seen the BAQ and they like it but have asked for invoice number for out subcons to be added, but i seem to be having a massive issue linking the Invhead, but im not sure if thats is even the right one to use.
/*
* 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
(case
when PORel.TranType='PUR-STK' then 'Inventory'
when PORel.TranType='PUR-SUB' then 'Sub-contract'
else 'Other'
end) as [Calculated_TranType],
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[TranDate] as [PartTran_TranDate],
[PartTran].[PONum] as [PartTran_PONum],
[PartTran].[PackSlip] as [PartTran_PackSlip],
[PartTran].[TranQty] as [PartTran_TranQty],
[PartTran].[LotNum] as [PartTran_LotNum],
[PartTran].[ExtCost] as [PartTran_ExtCost]
from Erp.PartTran as PartTran
inner join Erp.PORel as PORel on
PartTran.PONum = PORel.PONum
and PartTran.POLine = PORel.POLine
and PartTran.PORelNum = PORel.PORelNum
where (PartTran.TranType like 'PUR%')
order by PartTran.SysRevID Desc