I'm trying to understand data flow from one module to another in GP 2015 R2, with respect to Inventory. If anyone could answer the following questions it would be greatly appreciated. I'm not finding answers in my research.
1. Do all inventory item transactions (items of Sales Inventory type) from Project Accounting, POP, and SOP modules flow to Inventory, and are then posted from Inventory to the GL? Are there transactions that go directly to the GL, bypassing Inventory module?
2. Is the POP10500 table (Purchasing Receipt Line Quantities) both a Work and Historical table? This is the only table where I find receipt line item quantity for unposted and posted documents. Or should I be using the IV10200 table:
IV10200 -- Purchase Receipt Work
IV10201 -- Purchase Receipt Work Details
These tables are used specifically to calculate average cost.
IV10200 is used to store all receipts or positive adjustments or transfers on
inventory to a specific site. It also tracks how many of the receipt quantity
has been used or "sold" and what the adjusted weighted average cost that was used.
IV10201 captures the use of these quantities that were tracked in the IV10200 table.
3. What is the real difference between the ASIV0001 view and the POP10500 table? The ASIV0001 view sums quantities, but when I compare the view and the table, quantities are identical per item document, so it seems the sum is unnecessary at this level of detail but maybe I'm missing something.
I have been using the following query for historical POP receipts, is this accurate:
SELECT -- POP Receipt History -- get headers with line items
Module = 'POP'
,DocState = 'History'
,TRXSOURCE = h.TRXSORCE
,BatchNum = h.BACHNUMB
,BatchSource = h.BCHSOURC
,POPRCTNM = h.POPRCTNM -- POP Receipt Number
,RCPTLNNM = d.RCPTLNNM -- POP Receipt Line Number
,POPTYPE = CASE h.POPTYPE
WHEN 1 THEN 'Shipment' -- Receipt
WHEN 2 THEN 'Invoice'
WHEN 3 THEN 'Shipment/Invoice'
WHEN 4 THEN 'Return'
WHEN 5 THEN 'Return w/Credit'
WHEN 6 THEN 'IV Return'
WHEN 7 THEN 'IV Return w/Credit'
WHEN 8 THEN 'In-Transit Inventory'
ELSE 'Unknown'
END
,ItemNmbr = d.ITEMNMBR
,TRX_QTY = CASE h.POPTYPE
WHEN 1 THEN CONVERT(INT,q.QTYSHPPD) -- Shipment
WHEN 2 THEN CONVERT(INT,q.QTYINVCD) -- Invoice
WHEN 3 THEN CONVERT(INT,q.QTYSHPPD) -- Shipment/Invoice
WHEN 4 THEN CONVERT(INT,q.QTYRESERVED) -- Return
WHEN 5 THEN CONVERT(INT,q.QTYRESERVED) -- Return w/Credit
--WHEN 5 THEN CONVERT(INT,q.QTYINVRESERVE) -- Return w/Credit
WHEN 6 THEN CONVERT(INT,q.QTYSHPPD) -- IV Return?
WHEN 7 THEN CONVERT(INT,q.QTYSHPPD) -- IV Return w/Credit?
--WHEN 6 THEN CONVERT(INT,q.QTYREPLACED) -- IV Return?
--WHEN 7 THEN CONVERT(INT,q.QTYINVADJ) -- IV Return w/Credit?
ELSE CONVERT(INT,q.QTYSHPPD)
END
,ExtdCost = CONVERT(DECIMAL(12,2),d.EXTDCOST)
,PONUMBER = d.PONUMBER
,DocDate = CONVERT(DATE,h.ReceiptDate)
,DocUserID = h.USER2ENT -- User who entered the record
,GLPostDate = CONVERT(DATE,h.GLPOSTDT) -- date to be be assigned if Posting Setup is set to post by batch date
,Posted_UserID = h.PTDUSRID -- used for recurring batches; last user to post this transaction
,PACOSTCATID = d.CostCatID
,Voided = IIF(h.VOIDSTTS = 0,'No','Yes') -- all 0
,VEND_DocNum = h.VNDDOCNM
,VEND_ID = h.VENDORID
FROM
dbo.POP30300 as h -- Purchasing Receipt History
JOIN dbo.POP30310 as d ON d.POPRCTNM = h.POPRCTNM -- Purchasing Receipt Line History
LEFT OUTER JOIN dbo.POP10500 as q ON q.POPRCTNM = d.POPRCTNM -- Purchasing Receipt Line Quantities
AND q.RCPTLNNM = d.RCPTLNNM
--LEFT OUTER JOIN dbo.ASIV0001 as q ON q.POPRCTNM = d.POPRCTNM -- Purchasing Receipt Line Quantities (Summary View)
-- AND q.RCPTLNNM = d.RCPTLNNM
WHERE
d.NONINVEN = 0 -- Inventory items only