Open Forum

Expand all | Collapse all

Future Re-occurring reports in GP- snapshot

  • 1.  Future Re-occurring reports in GP- snapshot

    Posted 13 days ago
    I am looking for a way to leverage GP where my organization can set-up re-occurring future reports of inventory on-hand.

    The purpose of this is to have an apples to apples comparison with our third party warehouse's monthly inventory reports for inventory reconciliation.

    The challenge I am facing is GP does not have the capability to back-date on-hand reports at a certain date.  Having the ability for GP to automatically run a report that pulls on-hand inventory at a specific date will allow for more accurate reconciliation.

    Please let me know.

    ------------------------------
    Matthew Raffio
    Granite Telecommunications
    Quincy MA
    ------------------------------


  • 2.  RE: Future Re-occurring reports in GP- snapshot

    TOP CONTRIBUTOR
    Posted 10 days ago
    My last company had a similar issue.
    What you are wanting to do is compare the GP counts to the warehouse package counts, and make adjustments when a certain threshold is exceeded.
    You would probably want to do this on a weekly rather than a monthly basis.
    Perhaps you would also want to do this for the weekly test counts most companies conduct on a rolling basis.

    ------------------------------
    Bruce Strom
    Programmer Analyst
    Associated Grocers of Florida / Supervalu
    Sunrise FL
    ------------------------------



  • 3.  RE: Future Re-occurring reports in GP- snapshot

    Posted 9 days ago
    Edited by Danny Maxwell 9 days ago
    Here's what I've done, essentially build a table to host the "snapshot data" and then create a scheduled stored procedure for once a day at a specified time to populate the data. Here's a jump off point, you can certainly add columns as you see necessary:

    USE [production]
    GO

    /****** Object: Table [dbo].[tbl_daily_inventory_snapshot] Script Date: 4/8/2019 2:23:06 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[tbl_daily_inventory_snapshot](
    [SnapshotDate] [smalldatetime] NOT NULL,
    [ITEMNMBR] [nvarchar](50) NOT NULL,
    [ITEMDESC] [char](100) NULL,
    [LOCNCODE] [char](40) NOT NULL,
    [QTYONHND] [INT] NULL,
    [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY]
    GO


    So now that you have a table to work with, a stored procedure might look like:

    /****** Object: StoredProcedure [dbo].[afl_sp_wo_percentage_snapshot_daily] Script Date: 4/8/2019 2:51:16 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    CREATE procedure [dbo].[afl_sp_inventory_snapshot_daily] as

    --created by: Daniel Maxwell
    --Created Date: 10/18/2018
    --Purpose: Fill data from iv00102 into "snap shot" table for historical reporting of item quantities by day


    insert into [dbo].[tbl_daily_inventory_snapshot]

    ([SnapshotDate],
    [ITEMNMBR],
    [ITEMDESC],
    [LOCNCODE],
    [QTYONHND]
    )

    select getdate(),IV00102.ITEMNMBR,ITEMDESC,IV00102.LOCNCODE,QTYONHND from [dbo].[IV00102]
    LEFT JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBR
    WHERE IV00102.LOCNCODE <> '' --if you want to limit the size of your table also add in "and QTYONHND <> 0"
    ;
    GO


    Then you'd just need to schedule the stored procedure to fire at a specified time with SQL Agent. Now you're ready to query the table or use it in a report by simply specifying your snapshot date which would give you on hand quantities at that time.

    ------------------------------
    Danny Maxwell
    Business Analyst
    AFL
    Duncan SC
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role