Open Forum

1099 Info incorrect balances for AP

  • 1.  1099 Info incorrect balances for AP

    SILVER CONTRIBUTOR
    Posted Feb 24, 2021 02:19 PM
    I have a situation where the AP balances are not getting updated in SL 2018 for 1099 tax information.  I attempted to run SQL to pull the new balances but it's not correct in some cases.  Does anyone have SQL that will pull the information that I require?

    Your help is greatly appreciated!

    ------------------------------
    Mackenzie Platten
    Crestwood Associates L.L.C.
    Schaumburg IL
    ------------------------------


  • 2.  RE: 1099 Info incorrect balances for AP

    TOP CONTRIBUTOR
    Posted Feb 25, 2021 10:53 AM
    Which version of V2018 are you on?    In Jan 2020 when we were on V2018 CU2, I reported to Microsoft that the stored proc that rebuilds 1099 history was in error and had to fix it myself.   I believe it's fixed in later versions.   The issue had to do with how they were handling the backup withholding features that were new at that point.

    Below is the code fix I submitted to the Dynamics SL team where I rewrote the joins that were working incorrectly.    I believe it was later incorporated into a later version:


    /****** Object: StoredProcedure [dbo].[pp_Rebuild_1099] Script Date: 1/7/2020 11:08:48 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO


    --APPTABLE
    --USETHISSYNTAX

    ALTER procedure [dbo].[pp_Rebuild_1099] AS

    UPDATE AP_Balances SET LUpd_DateTime = GETDATE(), LUpd_Prog = '03990', LUpd_User = 'REBUILD',
    NYBox00 = ROUND(z.Box00,2),
    NYBox01 = ROUND(z.Box01, 2),
    NYBox02 = ROUND(z.Box02, 2),
    NYBox03 = ROUND(z.Box03, 2)+ Case when w.Box03 <> 0 then ROUND(w.Box03, 2) else 0 end ,
    NYBox04 = ROUND(z.Box04, 2),
    NYBox05 = ROUND(z.Box05, 2),
    NYBox06 = ROUND(z.Box06, 2),
    NYBox07 = ROUND(z.Box07, 2),
    NYBox09 = ROUND(z.Box09, 2),
    NYBox11 = ROUND(z.Box15a, 2),
    NYBox12 = ROUND(z.Box15b, 2),
    NYBox13 = ROUND(z.box13, 2),
    NYBox14 = ROUND(z.box14, 2)
    --FROM AP_Balances b, vp_Rebuild_1099 z, Vendor v left join vp_rebuild_BW w on v.VendId = w.VendId AND v.next1099Yr = w.CalendarYr AND b.CpnyId = w.CpnyId
    --WHERE v.VendId = z.VendId AND v.Next1099Yr = z.CalendarYr AND
    -- z.vendid = b.vendid AND b.CpnyId = z.CpnyId
    --modified 1/7/2019 GJ-N for clarity
    FROM AP_Balances b
    inner join Vendor v on b.vendid = v.vendid
    inner join vp_Rebuild_1099 z on z.vendid = b.vendid and v.Next1099Yr = z.CalendarYr AND b.CpnyId = z.CpnyId
    left join vp_rebuild_BW w on v.VendId = w.VendId AND v.next1099Yr = w.CalendarYr AND b.CpnyId = w.CpnyId




    UPDATE AP_Balances SET LUpd_DateTime = GETDATE(), LUpd_Prog = '03990', LUpd_User = 'REBUILD',
    CYBox00 = ROUND(z.Box00, 2),
    CYBox01 = ROUND(z.Box01, 2),
    CYBox02 = ROUND(z.Box02, 2),
    CYBox03 = ROUND(z.Box03, 2) + Case when w.Box03 <> 0 then ROUND(w.Box03, 2) else 0 end ,
    CYBox04 = ROUND(z.Box04, 2),
    CYBox05 = ROUND(z.Box05, 2),
    CYBox06 = ROUND(z.Box06, 2),
    CYBox07 = ROUND(z.Box07, 2),
    CYBox09 = ROUND(z.Box09, 2),
    CYBox11 = ROUND(z.Box15a, 2),
    CYBox12 = ROUND(z.Box15b, 2),
    CYBox13 = ROUND(z.box13, 2),
    CYBox14 = ROUND(z.box14, 2)
    --FROM AP_Balances b, vp_Rebuild_1099 z, Vendor v left join vp_rebuild_BW w on v.VendId = w.VendId AND v.Curr1099Yr = w.CalendarYr
    --WHERE v.VendId = z.VendId AND v.Curr1099Yr = z.CalendarYr AND
    -- z.vendid = b.vendid AND b.CpnyId = z.CpnyId AND b.CpnyId = w.CpnyId
    --modified 1/7/2020 GJ-N for clarity and because join in the where statemen elimated all records where there was no backup withholding
    FROM AP_Balances b
    inner join Vendor v on b.vendid = v.vendid
    inner join vp_Rebuild_1099 z on z.vendid = b.vendid and v.Curr1099Yr = z.CalendarYr AND b.CpnyId = z.CpnyId
    left join vp_rebuild_BW w on v.VendId = w.VendId AND v.Curr1099Yr = w.CalendarYr AND b.CpnyId = w.CpnyId

    GO

    Hope this helps.

    Best regards,

    Gail J-N



    ------------------------------
    Gail Jones-Nemeth
    Financial Systems Analyst
    Creative Associates Int'l
    Washington DC
    ------------------------------



  • 3.  RE: 1099 Info incorrect balances for AP

    SILVER CONTRIBUTOR
    Posted Feb 25, 2021 03:15 PM
    Yes Gail Thank you!  It is CU2.  I really appreciate your help with this!

    ------------------------------
    Mackenzie Platten
    Crestwood Associates L.L.C.
    Schaumburg IL
    ------------------------------



  • 4.  RE: 1099 Info incorrect balances for AP

    SILVER CONTRIBUTOR
    Posted Feb 25, 2021 10:59 AM
      |   view attached
    Mackenzie,

    For us, we invariably have line items that were mistagged in our Payables detail and for 99% of Vendors just wind up issuing a 1099 amount based on whatever we paid them according to APHist.  I've attached a SQL file that we use to generate who we're going to target for 1099's and how much we paid them.  It's a relatively simple SELECT statement with some variables at the top for you to filter by (if desired).

    I'm not sure if this is helps you as I'm not exactly sure what specifically you mean when you say "1099 tax information" but figured it may.  Give it a look and let me know if you have any questions!

    Joe


    ------------------------------
    Joe Miller
    Director of Systems Architecture
    Harrison, Walker & Harper
    Paris TX
    ------------------------------

    Attachment(s)

    txt
    Vend1099.sql.txt   2 KB 1 version