Where PH.POPTYPE IN (2,3) and PH.receiptdate > ‘’ PL.EXTDCOST, PL.UNITCOST, ph.POPRCTNM, ph.POPTYPE PH.VENDORID, PH.VENDNAME, PH.RECEIPTDATE, PH.VCHRNMBR, PL.ITEMNMBR, PL.ITEMDESC, If you want to restrict this to only vendors with a class ID of inventory, I would add an additional restriction inside your subquery – something like:Īnd PH.VENDORID in (select VENDORID from PM00200 where VNDCLSID = ‘INVENTORY’) I added a date restriction inside your subquery to improve performance – there is no reason to get all the raw data when all you want are transactions after a certain date.Īlso, I commented out your link to the PM00200 table, as it’s not changing your results in any way.
#Delete item receipt microsoft dynamics pos 2009 code
Try the code below, I believe that should fix it. I think the issue is your link to the POP10500 table – that is only on vendor, so it’s not selecting unique results properly (and as you’re seeing, multiplying the results). ORDER BY P.VENDORID, P.VENDNAME, P.ITEMNMBR, P.ITEMDESC GROUP BY P.VENDORID, P.VENDNAME, P.ITEMNMBR, P.ITEMDESC (SELECT PH.VENDORID, PH.VENDNAME, PH.RECEIPTDATE, PH.VCHRNMBR, PL.ITEMNMBR, PL.ITEMDESC, PL.EXTDCOST, PL.UNITCOST SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2017 THEN CONVERT(DECIMAL(10,2),P.EXTDCOST) ELSE 0 END) SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2017 THEN CAST (Q.QTYSHPPD AS INT) ELSE 0 END) , SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2018 THEN CONVERT(DECIMAL(10,2),P.EXTDCOST) ELSE 0 END) , SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2018 THEN CAST (Q.QTYSHPPD AS INT) ELSE 0 END) , SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2019 THEN CONVERT(DECIMAL(10,2),P.EXTDCOST) ELSE 0 END) , –SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2019 THEN CONVERT(DECIMAL(10,2),P.EXTDCOST/P.UNITCOST) ELSE 0 END) , SUM(CASE WHEN YEAR(P.RECEIPTDATE) = 2019 THEN Q.QTYSHPPD ELSE 0 END) , SELECT P.VENDORID, P.VENDNAME, P.ITEMNMBR, P.ITEMDESC, Should I pull the data from another table instead? Say POP10500 instead? Please advise. I feel like I shouldn’t use POPRCTNM to join the 2 tables but I can’t find another common column to join themīelow is the query I have. However, the result looks very weird, looks like it is multiply by itself. I am trying to pull item purchased by vendor by year from POP30300 & 30310 using POPRCTNM to join the two tables.
Cruz Narvaez for this one)ĥ – Payment Term ID Note Index – PONOTIDS_5Ħ – Shipping Method Note Index – PONOTIDS_6ĩ – Freight Tax Schedule Index – PONOTIDS_9ġ0 – Misc Tax Schedule Index – PONOTIDS_10Ģ – Location Code Note Index – POLNEARY_2Ĥ – Inventory Index Note Index – POLNEARY_4Ħ – Item Tax Schedule Note Index – POLNEARY_6ħ – Site Tax Schedule Note Index – POLNEARY_7Ĩ – Landed Cost Group ID Note Index – POLNEARY_8ĩ – Shipping Method Note Index – POLNEARY_9 POP30310 – Receipt Line History (line detail)Ġ – Voided ġ – Active (includes New, Open and Modified)Ģ – Closed (includes Cancelled and Closed)Ĩ – Intransit Transfer (thanks to Jesus M. POP30110 – Purchase Order Line History (line detail) POP30100 – Purchase Order History (header) POP10600 – Purchasing Shipment Invoice Apply
POP10550 – Line Item Comment (line detail) POP10500 – Receipt Line Quantities (line detail) POP10310 – Receipt Work Line (line detail) POP10306 – Purchasing Receipt User-Defined POP10150 – Purchase Order Comment (header) POP10110 – Purchase Order Line Work (line detail)
0 kommentar(er)
