I have been working on making a way in SQL to match the invoice line receipt to the PO line reciept. My boss wants me to have a report that can compare an item's receipt with the date we received the receipt. GP does this on a line-by-line basis so there must be a connection somewhere but I'm having trouble consistently getting it right. Here's what I have so far that returns a great number of correct lines but is still not accurate enough:
select isnull(
case when
(select top(1) glpostdt from pop30300 where poprctnm =
(select top(1) poprctnm from pop10500 where itemnmbr = a.itemnmbr and ponumber = a.ponumber
and appytype = 3 and polnenum = a.polnenum and qtyinvcd = a.qtyshppd order by daterecd asc)) is null then
(select top(1) glpostdt from pop30300 where poprctnm =
(select top(1) poprctnm from pop10500 where itemnmbr = a.itemnmbr and ponumber = a.ponumber
and appytype = 3 and polnenum = a.polnenum order by daterecd asc)) else
(select top(1) glpostdt from pop30300 where poprctnm =
(select top(1) poprctnm from pop10500 where itemnmbr = a.itemnmbr and ponumber = a.ponumber
and appytype = 3 and polnenum = a.polnenum and qtyinvcd = a.qtyshppd order by daterecd asc)) end
, '2/22/2222') as invodate, isnull(
case when
(select top(1) glpostdt from pop30300 where poprctnm =
(select top(1) poprctnm from pop10500 where itemnmbr = a.itemnmbr and ponumber = a.ponumber
and appytype = 3 and polnenum = a.polnenum and qtyinvcd = a.qtyshppd order by daterecd asc)) is null then
'U' else
case when
(select count(poprctnm) from pop10500 where itemnmbr = a.itemnmbr and ponumber = a.ponumber
and appytype = 1 and polnenum = a.polnenum and qtyshppd = a.qtyshppd) > 1 then
'M' else '' end end
, '') as flag,
daterecd, poprctnm as receipt,
itemnmbr, (select itemdesc from iv00101 where itemnmbr = a.itemnmbr) as itemdesc, qtyshppd as qty, pchrptct as cost,
vendorid, (select vendname from pm00200 where vendorid = a.vendorid) as vendname,
isnull((select top(1) vnddocnm from pop30300 where poprctnm =
(select top(1) poprctnm from pop10500 where itemnmbr = a.itemnmbr and ponumber = a.ponumber
and appytype = 3 and polnenum = a.polnenum order by daterecd asc)), 'Cannot Find') as vendordoc
from pop10500 a
where appytype = 1
The "U" and "M" flags mean that the connection is unkown or it found multiple items for a PO and warns that the invoice receipt date is likely inaccurate. Any help is appreciated.
*This post is locked for comments
I have the same question (0)
