Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
karetess 2/22/2017 2:27:40 PM

Trying to understand data flow from one module to another, with respect to Inventory

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'
      ,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)
      ,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
             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
       d.NONINVEN = 0  -- Inventory items only



Version: GP 2015
Section: Dynamics GP

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables