Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics SL forum
Unanswered

AP and AR current balances

Posted on by Microsoft Employee

Hello all,

Can somebody post me sql query to find AP and AR total for a period. I want to build a custom dashboard showing some KPI metrics from solomon. I am new to solomon and really dont know how the tables are related.

Thanks in advance,

  • manuel vela Profile Picture
    manuel vela 430 on at
    RE: AP and AR current balances

    Hi sreeharivijayan, i hope this help,these are my query's i used, could you share your KPI metrics

    thanks

    AR_Balances

    SELECT

    cCuryID=c.CuryID,c.ClassId,

    ARAcct =CASE d.DocType WHEN 'PA' THEN c.ARAcct WHEN 'PP' THEN c.PrePayAcct ELSE d.BankAcct END,

    ARSub = CASE d.DocType WHEN 'PA' THEN c.ARSub  WHEN 'PP' THEN c.PrePaySub ELSE d.BankSub END,

    d.CustID,

    Cliente =Rtrim(d.CustID)+' '+Rtrim(c.Name),

    CName = c.Name,

    cStatus = c.Status,

    d.RefNbr,

    d.CuryID,

    d.DueDate,

    Year=datepart(YEAR,d.DueDate),

    Mes=

    case  

    when datepart(month,d.duedate)=1 then 'Enero'

    when datepart(month,d.duedate)=2 then 'Febrero'

    when datepart(month,d.duedate)=3 then 'Marzo'

    when datepart(month,d.duedate)=4 then 'Abril'

    when datepart(month,d.duedate)=5 then 'Mayo'

    when datepart(month,d.duedate)=6 then 'Junio'

    when datepart(month,d.duedate)=7 then 'Julio'

    when datepart(month,d.duedate)=8 then 'Agosto'

    when datepart(month,d.duedate)=9 then 'Septiembre'

    when datepart(month,d.duedate)=10 then 'Octubre'

    when datepart(month,d.duedate)=11 then 'Noviembre'

    when datepart(month,d.duedate)=12 then 'Diciembre' end,

    d.DiscDate,

    d.DocDate,

    hoy=CONVERT(VarChar(50), CONVERT(VarChar(50), GETDATE(), 102), 102),

    d.DocType,

    OrigDocAmt =     CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * d.OrigDocAmt,

    DocBal =         CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * d.DocBal,

    CuryOrigDocAmt = CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * d.CuryOrigDocAmt,

    CuryDocBal =     CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * d.CuryDocBal,

    Descr = ISNULL(t.Descr, ''), -- b.AvgDayToPay,

    SlsperId=Rtrim(c.SlsperId)+' '+Rtrim(v.Name),

    Cur = CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD')

                       THEN 1

                      ELSE -1

                 END * CASE WHEN d.DocType NOT IN ('CM', 'PA', 'PP') AND CONVERT(VarChar(50), GETDATE(), 102) <= d.DueDate

                                     OR d.DocType IN ('CM', 'PA', 'PP') AND (CONVERT(VarChar(50), GETDATE(), 102)<=d.DocDate OR ARSetup.S4Future09=0)

                               THEN d.DocBal

                            ELSE 0

                       END,

    Past00 = CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD')

                          THEN 1

                         ELSE -1

                    END * CASE WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP')

                                                         THEN d.DocDate

                                                       ELSE d.DueDate

                                                  END, CONVERT(VarChar(50), GETDATE(), 102)) <= s.AgeDays00 AND

            DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP')

                                                         THEN d.DocDate  

                                                       ELSE d.DueDate

                                                  END, CONVERT(VarChar(50), GETDATE(), 102)) >= 1 AND

                                    (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1)

                                 THEN d.DocBal

               ELSE 0 END,

    Past01 = CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * CASE

    WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) <= s.AgeDays01 AND

    DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > s.AgeDays00 AND

                           (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) THEN d.DocBal

    ELSE 0 END,

    Past02 = CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * CASE

    WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) <= s.AgeDays02 AND

    DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > s.AgeDays01 AND

                           (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) THEN d.DocBal

    ELSE 0 END,

    Over02 = CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * CASE

    WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > s.AgeDays02 AND

                   (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) THEN d.DocBal

    ELSE 0 END,

    cCur = CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * CASE

    WHEN d.DocType NOT IN ('CM', 'PA', 'PP') AND CONVERT(VarChar(50), GETDATE(), 102) <= d.DueDate OR

    d.DocType IN ('CM', 'PA', 'PP') AND (CONVERT(VarChar(50), GETDATE(), 102)<=d.DocDate OR ARSetup.S4Future09=0 ) THEN d.CuryDocBal ELSE 0 END,

    cPast00 = CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * CASE

    WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) <= s.AgeDays00 AND

    DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) >= 1 AND

                           (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) THEN d.CuryDocBal

    ELSE 0 END,

    cPast01 = CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * CASE

    WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) <= s.AgeDays01 AND

    DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > s.AgeDays00 AND

                           (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) THEN d.CuryDocBal

    ELSE 0 END,

    cPast02 = CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * CASE

    WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) <= s.AgeDays02 AND

    DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > s.AgeDays01 AND

                           (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) THEN d.CuryDocBal

    ELSE 0 END,

    cOver02 = CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * CASE

    WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > s.AgeDays02 AND

                  (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) THEN d.CuryDocBal

    ELSE 0 END

    -- AgeDays00 = s.AgeDays00,

    -- AgeDays01 = s.AgeDays01,

    -- AgeDays02 = s.AgeDays02,

    --       c.User1 as CustUser1, c.User2 as CustUser2, c.User3 as CustUser3, c.User4 as CustUser4,

    --       c.User5 as CustUser5, c.User6 as CustUser6, c.User7 as CustUser7, c.User8 as CustUser8,

    --       d.User1 as ARDocUser1, d.User2 as ARDocUser2, d.User3 as ARDocUser3, d.User4 as ARDocUser4,

    --       d.User5 as ARDocUser5, d.User6 as ARDocUser6, d.User7 as ARDocUser7, d.User8 as ARDocUser8

     FROM  --RptRuntime r INNER JOIN RptCompany y

                               -- ON y.RI_ID=r.RI_ID

                        --INNER JOIN

    ARDoc d

                              --  ON d.CpnyID=y.CpnyID

                        INNER JOIN AR_Balances b

                                ON b.CustID=d.CustID

                        INNER JOIN Customer c

                                ON c.CustID=d.CustID

    inner join Salesperson v

    on v.SlsperId=c.SlsperId

                        INNER JOIN (SELECT StmtCycleID, AgeDays00 = CONVERT(INT,AgeDays00),

                                           AgeDays01 = CONVERT(INT,AgeDays01), AgeDays02 = CONVERT(INT,AgeDays02)

                                      FROM ARStmt) s

                                ON s.StmtCycleID=c.StmtCycleID

                         LEFT JOIN Terms t

                                ON d.Terms <> '' AND t.TermsID=d.Terms

                        CROSS JOIN ARSetup (NOLOCK)

    WHERE d.Rlsed=1 AND d.DocBal<>0

    --and d.User1 =1

    Order by d.CustID

    AP_Balances

    SELECT

    Tipo='V',

    'Tipo_Prov'=SUBSTRING(VendID,1,1),

    'Descripcion'=VName,

    'Clase'=ClassID,

    'Referencia'=RefNbr,

    'StatusDoc'= dStatus,

    'Moneda'=CuryID,

    'OC'=PONbr,

    'Factura'=InvcNbr,

    'Tipo Doc'=DocType,

    'Fecha Factura'=InvcDate,

    'No. de Parte'= '',

    'Cantidad'= '',

    'P. Unitario'= '',

    Total=DocBal,

    'Fecha Vencimeinto'=DueDate,

    'Fecha Arribo'= '',

    Actual = SUM(CASE

    WHEN CAST(DATEDIFF(DAY, 0, GETDATE()) AS DATETIME)  <= DueDate THEN DocBal ELSE 0 END),

    'Semana 1' = SUM(CASE

    WHEN DATEDIFF(Day, DueDate, GETDATE() ) <= CONVERT(INT, 7) AND

    DATEDIFF(Day, DueDate, GETDATE() ) >= 1 THEN DocBal

    ELSE 0 END),

    'Semana 2' = SUM(CASE

    WHEN DATEDIFF(Day, DueDate, GETDATE() ) <= CONVERT(INT, 14) AND

    DATEDIFF(Day, DueDate, GETDATE() ) > CONVERT(INT, 7) THEN DocBal

    ELSE 0 END),

    'Semana 3' = SUM(CASE

    WHEN DATEDIFF(Day, DueDate, GETDATE() ) <= CONVERT(INT, 21) AND

    DATEDIFF(Day, DueDate, GETDATE() ) > CONVERT(INT, 14) THEN DocBal

    ELSE 0 END),

    ----------------------------------------------

    'Semana 4' = SUM(CASE

    WHEN DATEDIFF(Day, DueDate, GETDATE() ) <= CONVERT(INT, 28) AND

    DATEDIFF(Day, DueDate, GETDATE() ) > CONVERT(INT, 21) THEN DocBal

    ELSE 0 END),

    '31-60' = SUM(CASE

    WHEN DATEDIFF(Day, DueDate, GETDATE() ) <= CONVERT(INT, 60) AND

    DATEDIFF(Day, DueDate, GETDATE() ) > CONVERT(INT, 28) THEN DocBal

    ELSE 0 END),

    '61-90' = SUM(CASE

    WHEN DATEDIFF(Day, DueDate, GETDATE() ) <= CONVERT(INT, 90) AND

    DATEDIFF(Day, DueDate, GETDATE() ) > CONVERT(INT, 60) THEN DocBal

    ELSE 0 END),

    'Mas de 90' = SUM(CASE

    WHEN DATEDIFF(Day, DueDate, GETDATE() ) > CONVERT(INT, 90) THEN DocBal

    ELSE 0 END)

    FROM XDocumentosCxP

    WHERE DocBal <>0

    GROUP BY VendID,VName,ClassID,RefNbr,dStatus,PONbr,InvcNbr,CuryID,DocType,InvcDate,DocBal,DueDate

    --AP view documents

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    CREATE VIEW XDocumentosCxP  AS

    SELECT

    VendID=d.VendId,

       VName =Rtrim(d.VendID)+' '+Rtrim(v.Name),

    dStatus = d.Status,

    RefNbr = d.RefNbr,

    PONbr=d.PONbr,

    DueDate = d.DueDate,

    PayDate = d.PayDate,

    DiscDate = d.DiscDate,

    DocDate = d.DocDate,

    InvcNbr = d.InvcNbr,

    InvcDate = d.InvcDate,

    DocType = d.DocType,

       PerEnt = d.PerEnt,

       InvtID='',

       --MasterDocNbr = d.MasterDocNbr,

    PerPost = d.PerPost,

    --PerClosed = d.PerClosed,

    OrigDocAmt = d.OrigDocAmt * (CASE d.DocType WHEN 'AD' THEN -1 WHEN 'PP' THEN -1 ELSE 1 END),

    DocBal = CASE d.DocType WHEN 'AD' THEN -d.DocBal

                                    WHEN 'PP' then isnull((SELECT -sum(j.adjamt) from apadjust j where d.refnbr = j.adjdrefnbr and j.adjddoctype='PP'),0)

    ELSE d.DocBal END,

    CuryOrigDocAmt = d.CuryOrigDocAmt * (CASE d.DocType WHEN 'AD' THEN -1 WHEN 'PP' THEN -1 ELSE 1 END),

    CuryDocBal = CASE d.DocType WHEN 'AD' THEN -d.CuryDocBal

                                    WHEN 'PP' then isnull((SELECT -sum(j.curyadjdamt) from apadjust j where d.refnbr = j.adjdrefnbr and j.adjddoctype='PP'),0)

    ELSE d.CuryDocBal END,

    CuryID = d.CuryID,

    ParentType = d.DocType,

       vStatus = v.Status,

       vCuryID = v.CuryID,

    ClassID =Rtrim(v.ClassID)+' '+Rtrim(C.Descr)

    FROM Vendor v

    INNER JOIN APDoc d ON d.VendID=v.VendID

    INNER JOIN dbo.VendClass C ON v.ClassID = C.ClassID

    WHERE d.Rlsed = 1 AND d.DocType NOT IN ('CK','HC','ZC', 'VC', 'VM', 'VT') AND (d.DocBal<>0 OR d.DocType='PP')

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,914 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,549 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans