Dynamics Community Forum Thread Details

Tracing receipt and PO information after a lot has been transferred.

I put together a query that from the lot number master would find the receipt and PO the lot came from, it worked well until they transferred the lot from one bin to another. The query below is what comes from smartlist builder to show what I put together so far. I can see the transfer info in IV30400 but unsure how to connect it all. The receipt line retains the first bin it was put into. Please tell me if the effort is futile, I can think of a few things to believe it is because they might not transfer all of it and I use qty etc OR if you may have a genius suggestion taking it out of smartlist builder and creating the sql view, thanks.

select
* from [FASTR]..IV00300 T1 with (nolock)
INNER JOIN [FASTR]..IV00101 T2 with (nolock)
on
T2.[ITEMNMBR] = T1.[ITEMNMBR]
LEFT JOIN (
select 0 as SLB_TYPE_3, POPRCTNM, RCPTLNNM, SLTSQNUM, ITEMNMBR, SERLTNUM, SERLTQTY, TRXSORCE, DATERECD, DTSEQNUM, UNITCOST, QTYTYPE, BIN, MFGDATE, EXPNDATE
from [FASTR]..POP10330 with (nolock)
union
select
1 as SLB_TYPE_3,
POPRCTNM as POPRCTNM,
RCPTLNNM as RCPTLNNM,
SLTSQNUM as SLTSQNUM,
ITEMNMBR as ITEMNMBR,
SERLTNUM as SERLTNUM,
SERLTQTY as SERLTQTY,
TRXSORCE as TRXSORCE,
DATERECD as DATERECD,
DTSEQNUM as DTSEQNUM,
UNITCOST as UNITCOST,
QTYTYPE as QTYTYPE,
BIN as BIN,
MFGDATE as MFGDATE,
EXPNDATE as EXPNDATE
from [FASTR]..POP30330 with (nolock)
) T3
on
T3.[ITEMNMBR] = T1.[ITEMNMBR] and T3.[SERLTNUM] = T1.[LOTNUMBR] and T3.[QTYTYPE] = T1.[QTYTYPE] and T3.[SERLTQTY] = T1.[QTYRECVD] and T3.[DATERECD] = T1.[DATERECD]
LEFT JOIN [FASTR]..POP10500 T4 with (nolock)
on
T4.[POPRCTNM] = T3.[POPRCTNM] and T4.[RCPTLNNM] = T3.[RCPTLNNM]