SBX - Search With Button

SBX - Forum Post Title

AP and AR current balances

Microsoft Dynamics SL Forum

sreeharivijayan asked a question on 2 Jun 2019 2:42 PM

Question Status

Unanswered

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,

Reply
Manuel Vela responded on 5 Jun 2019 12:29 PM
My Badges

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')

Reply

SBX - Two Col Forum

SBX - Migrated JS