Announcements
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,
*This post is locked for comments
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')
#1
Community Member
136
#2
Mohamed Amine Mahmoudi
102
Super User 2025 Season 1
#3
REUser
8
Share
Report
All responses (
Answers (
