Dynamics Community Forum Thread Details

Adding where-used to open purchase orders report for inventory items using smartlist builder

Running GP 10... I have created a smartlist using builder for open purchase order commitments...inventory items. I have been asked by management to add the 'where-used' for the purchased items. My problem is, some of the purchased items (components to BoMs) have multiple where-used and when I run the report the p/o line items are replicating for each (additional) where-used they are associated with. Is there something wrong with the way I have linked my tables and/or is there a way around this so that my report totals are not erroneously inflated?

Here is my current view:

select *
from MAERO..IV00101 T1 with (nolock)
LEFT JOIN MAERO..POP10110 T2 with (nolock)
on
T2.[ITEMNMBR] = T1.[ITEMNMBR]
LEFT JOIN MAERO..POP10100 T3 with (nolock)
on
T3.[PONUMBER] = T2.[PONUMBER]
LEFT JOIN MAERO..BM010115 T5 with (nolock)
on
T5.[CPN_I] = T2.[ITEMNMBR]
LEFT JOIN [MAERO].dbo.[POP10500] T6 with (nolock)
on
T6.[POLNENUM] = T2.[LineNumber] WHERE isnull(T3.[BUYERID], '') = '12'
and T2.[POLNESTA] IN (2, 3)
and T1.[ITEMTYPE] <> '2'
and T1.[ITMCLSCD] <> 'OV'