Im trying to up skill on BAQs so i have been making BAQs and this one could be usefil, the code below is to see whats coming in via the back door and its working great im going to be filtering it to PUR-STK, but i want to add the serial number to the things coming in. now everything i try and add SerialNo or SNTran it just breaks the output. can anyone point me in the right direction with what im missing. As i just end up with rows upon rows of a single part number. Any help would be great and thank you again.
select
[PartTran].[PartNum] as [PartTran_PartNum],
[PORel].[PONum] as [PORel_PONum],
[PORel].[POLine] as [PORel_POLine],
[PartTran].[TranType] as [PartTran_TranType],
[PartTran].[EntryPerson] as [PartTran_EntryPerson],
[PartTran].[TranDate] as [PartTran_TranDate],
(CONVERT(varchar, DATEADD(ss, PartTran.SysTime, 0), 8)) as [Calculated_Time]
from Erp.PartTran as PartTran
inner join Erp.PORel as PORel on
PartTran.Company = PORel.Company
and PartTran.PONum = PORel.PONum
and PartTran.POLine = PORel.POLine
and PartTran.PORelNum = PORel.PORelNum
order by PartTran.SysRevID Desc
You are on the right track with sntran, but you need to join via the RcvDtl table
select * from erp.sntran s, erp.RcvDtl r, erp.porel p
where s.company = r.company
and s.PackSlip = r.PackSlip
and s.PackSlipLine = r.PackLine
and r.company = p.company
and r.PONum = p.PONum
and r.POLine = p.POLine
and r.PORelNum = p.PORelNum
and s.trantype = 'PUR-STK'
select
[SNTran].[PartNum] as [SNTran_PartNum],
[SNTran].[SerialNumber] as [SNTran_SerialNumber],
[PORel].[PONum] as [PORel_PONum],
[PORel].[POLine] as [PORel_POLine],
[SNTran].[TranType] as [SNTran_TranType],
[SNTran].[TranDate] as [SNTran_TranDate]
from Erp.SNTran as SNTran
inner join Erp.RcvDtl as RcvDtl on
SNTran.Company = RcvDtl.Company
and SNTran.PackSlip = RcvDtl.PackSlip
and SNTran.PackLine = RcvDtl.PackLine
inner join Erp.PORel as PORel on
RcvDtl.Company = PORel.Company
and RcvDtl.PONum = PORel.PONum
and RcvDtl.POLine = PORel.POLine
and RcvDtl.PORelNum = PORel.PORelNum
Im getting duplicate Serial numbers is that how it should be?
this is the current one made by some else whos long left the company, mayb this can help, im still very new to Epicor.
(case
when PORel.TranType='PUR-STK' then 'Inventory'
when PORel.TranType='PUR-SUB' then 'Sub-contract'
else 'Other'
end) as [Calculated_TranType],
[Vendor].[Name] as [Vendor_Name],
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[TranDate] as [PartTran_TranDate],
(CONVERT(varchar, DATEADD(ss, PartTran.SysTime, 0), 8)) as [Calculated_Time],
[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
inner join Erp.Vendor as Vendor on
PartTran.Company = Vendor.Company
and PartTran.VendorNum = Vendor.VendorNum
where (PartTran.TranType like 'PUR%')
order by PartTran.SysRevID Desc
have learnt alot just by trying to remake this and add in a few extra bits of infomation. So should our system have PUR-STK and yes the goodsin guy uses Receipt Entry.
Under type there is 19 differant Trantypes if thats the right place to look
What’s wrong with this one? Looks eerily similar to what you are trying to re-create?
(case
when PORel.TranType='PUR-STK' then 'Inventory'
when PORel.TranType='PUR-SUB' then 'Sub-contract'
else 'Other'
end) as [Calculated_TranType],
[Vendor].[Name] as [Vendor_Name],
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[TranDate] as [PartTran_TranDate],
(CONVERT(varchar, DATEADD(ss, PartTran.SysTime, 0), 8)) as [Calculated_Time],
[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
inner join Erp.Vendor as Vendor on
PartTran.Company = Vendor.Company
and PartTran.VendorNum = Vendor.VendorNum
where (PartTran.TranType like 'PUR%')
order by PartTran.SysRevID Desc
Also has the LIKE ‘PUR%’ suggested by @aclements. From your above reply, PUR-INS pretty sure that is going to inspection versus directly to inventory.
