Hi Jose
Do you have something similar to this for AP Aged Report?
I have created a Excel spread sheet with ODBC connection for A/R aged Receivables. it works fine for the current open receivables. But when I try using the apinvhed.invoicedate as As of date, for a previous date.
It is missing almost half of the invoices. Help Please.
It is missing almost half of the invoices. Help Please.
Should you be using AR instead of APinvhed.invoicedate?
How did you do that? Can you help me out?
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of sanjay219@...
Sent: Thursday, April 4, 2013 4:13 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Aged Receivables as of Date
I have created a Excel spread sheet with ODBC connection for A/R aged Receivables. it works fine for the current open receivables. But when I try using the apinvhed.invoicedate as As of date, for a previous date.
It is missing almost half of the invoices. Help Please.
[Non-text portions of this message have been removed]
How did you do that? Can you help me out?
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of sanjay219@...
Sent: Thursday, April 4, 2013 4:13 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Aged Receivables as of Date
I have created a Excel spread sheet with ODBC connection for A/R aged Receivables. it works fine for the current open receivables. But when I try using the apinvhed.invoicedate as As of date, for a previous date.
It is missing almost half of the invoices. Help Please.
[Non-text portions of this message have been removed]
I need to implement the 'as of date' of Aged receivables in my spreadsheet. Any idea, how Vantage does this??
Sorry, that was a mistake. My bad! I used invhead.invoicedate. Not apinvhed
Can you help me out with that? What version of excel are you on?
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of sanjay219@...
Sent: Thursday, April 4, 2013 4:23 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Aged Receivables as of Date
Sorry, that was a mistake. My bad! I used invhead.invoicedate. Not apinvhed
[Non-text portions of this message have been removed]
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of sanjay219@...
Sent: Thursday, April 4, 2013 4:23 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Aged Receivables as of Date
Sorry, that was a mistake. My bad! I used invhead.invoicedate. Not apinvhed
[Non-text portions of this message have been removed]
A true AR Agend Receivables looks at PaymentDates and Credit Memos and
such. I suspect that if you are only looking at InvcHead or InvcDtl that's
why I won't work. here is a SQL Report I wrote which does all this
I won't spec time explaining it though, hope it helps you some what.
USE [YOURDB]
GO
/****** Object: StoredProcedure [dbo].[AR_Aging] Script Date:
04/04/2013 16:35:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jose C Gomez
-- Create date: 7/6/2012
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[AR_Aging]
-- Add the parameters for the stored procedure here
@Company As varchar(8),
@AsOfDate As VarChar(MAX),
@AgeOnDueDate Bit,
@CustID As Varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE #ARAging(
Company VARCHAR(8),
InvcType VARCHAR(2),
InvoiceNum INT,
InvoiceAmt DECIMAL(18,2),
InvoiceBal DECIMAL (18,2),
InvoiceDate DateTime,
DueDate DateTime,
DaysOut INT,
CustNum INT,
CustID VARCHAR(50),
CustName VARCHAR(250),
PONum VARCHAR(50),
CustomerPhone VARCHAR(50),
FiscalPeriod VARCHAR(50),
CurrencyCode VARCHAR(12)
)
CREATE TABLE #TempPay
(
Company VARCHAR(8),
InvoiceNum INT,
InvoicePayments DECIMAL(18,2)
)
INSERT INTO #ARAging
SELECT IH.Company,(CASE WHEN IH.CreditMemo=1 THEN 'CM' WHEN IH.DebitNote=1
THEN 'DN' ELSE 'IV' END) , IH.InvoiceNum, IH.InvoiceAmt, IH.InvoiceAmt,
IH.InvoiceDate,IH.DueDate, DATEDIFF(DAY, CASE WHEN @AgeOnDueDate=1 THEN
IH.DueDate ELSE IH.InvoiceDate END,@AsOfDate), IH.CustNum, C.CustID,
C.Name, IH.PONum, C.PhoneNum,'0',CASE IH.COMPANY WHEN 'CAN' THEN
REPLACE(IH.CurrencyCode,'BASE','CAN') ELSE
REPLACE(IH.CurrencyCode,'BASE','USD') END
FROM Mfgsys803..InvcHead IH WITH(NOLOCK)
INNER JOIN Mfgsys803..Customer C WITH(NOLOCK) ON IH.Company= C.Company AND
IH.CustNum=C.CustNum
WHERE IH.InvoiceDate <= @AsOfDate
AND (IH.ClosedDate > @AsOfDate OR IH.ClosedDate IS NULL)
AND IH.Company=@Company
AND C.CustID LIKE ISNULL(@CustID,'%')
INSERT INTO #TempPay
SELECT CS.Company, CS.InvoiceNum,SUM(CASE WHEN CS.TranType='PayInv' THEN
CS.TranAmt*-1 ELSE CS.TranAmt END)
FROM Mfgsys803..CashDtl CS WITH(NOLOCK)
WHERE CS.TranDate <=@AsOfDate AND CS.Posted=1 AND CS.Company=@Company AND
CS.InvoiceNum IN (
SELECT IH.InvoiceNum
FROM Mfgsys803..InvcHead IH WITH(NOLOCK)
INNER JOIN Mfgsys803..Customer C WITH(NOLOCK) ON IH.Company= C.Company AND
IH.CustNum=C.CustNum
WHERE IH.InvoiceDate <= @AsOfDate
AND (IH.ClosedDate > @AsOfDate OR IH.ClosedDate IS NULL)
AND IH.Company=@Company)
AND CS.DebitNote=0
Group By CS.Company, CS.InvoiceNum
INSERT INTO #TempPay
SELECT CS.Company, CS.InvoiceRef,SUM(CS.TranAmt*-1)--SUM(CASE WHEN
CS.TranType='PayInv' THEN CS.TranAmt*-1 ELSE CS.TranAmt END)
FROM Mfgsys803..CashDtl CS WITH(NOLOCK)
WHERE CS.TranDate <=@AsOfDate AND CS.Posted=1 AND CS.Company=@Company AND
CS.InvoiceRef IN (
SELECT IH.InvoiceNum
FROM Mfgsys803..InvcHead IH WITH(NOLOCK)
INNER JOIN Mfgsys803..Customer C WITH(NOLOCK) ON IH.Company= C.Company AND
IH.CustNum=C.CustNum
WHERE IH.InvoiceDate <= @AsOfDate
AND (IH.ClosedDate > @AsOfDate OR IH.ClosedDate IS NULL)
AND IH.Company=@Company)
AND CS.DebitNote=0
Group By CS.Company, CS.InvoiceRef
--UPDATE ARG
--SET ARG.InvoiceBal = (ARG.InvoiceBal + CASE WHEN CD.TranType='PayInv'
THEN CD.TranAmt*-1 ELSE CD.TranAmt END)
--FROM #ARAging ARG
--INNER JOIN Mfgsys803..CashDtl CD WITH(NOLOCK) ON ARG.Company = CD.Company
AND ARG.InvoiceNum=CD.InvoiceNum
-- WHERE CD.TranDate <=@AsOfDate AND CD.Posted=1 AND CD.DebitNote=0
UPDATE ARG
SET ARG.InvoiceBal = (ARG.InvoiceBal + ISNULL(T.InvoicePayments,0))
FROM #ARAging ARG INNER JOIN
#TempPay T ON ARG.Company = T.Company AND ARG.InvoiceNum=T.InvoiceNum
DECLARE @FiscalP As Varchar(50)
SELECT @FiscalP = CAST(F.FiscalYear AS nvarchar) + '/' +
CAST(F.FiscalPeriod As nvarchar)
FROM Mfgsys803..Fiscal F WITH(NOLOCK)
WHERE @AsOfDate BETWEEN F.StartDate AND F.EndDate AND F.Company=@Company
Update #ARAging
SET FiscalPeriod =@FiscalP
SELECT * FROM #ARAging
Order By CustName, CustID , InvoiceNum
DROP TABLE #ARAging
DROP TABLE #TempPay
END
*Jose C Gomez*
*Software Engineer*
*
*
*
*T: 904.469.1524 mobile
E: jose@...
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>
<http://www.usdoingstuff.com>
*Quis custodiet ipsos custodes?*
such. I suspect that if you are only looking at InvcHead or InvcDtl that's
why I won't work. here is a SQL Report I wrote which does all this
I won't spec time explaining it though, hope it helps you some what.
USE [YOURDB]
GO
/****** Object: StoredProcedure [dbo].[AR_Aging] Script Date:
04/04/2013 16:35:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jose C Gomez
-- Create date: 7/6/2012
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[AR_Aging]
-- Add the parameters for the stored procedure here
@Company As varchar(8),
@AsOfDate As VarChar(MAX),
@AgeOnDueDate Bit,
@CustID As Varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE #ARAging(
Company VARCHAR(8),
InvcType VARCHAR(2),
InvoiceNum INT,
InvoiceAmt DECIMAL(18,2),
InvoiceBal DECIMAL (18,2),
InvoiceDate DateTime,
DueDate DateTime,
DaysOut INT,
CustNum INT,
CustID VARCHAR(50),
CustName VARCHAR(250),
PONum VARCHAR(50),
CustomerPhone VARCHAR(50),
FiscalPeriod VARCHAR(50),
CurrencyCode VARCHAR(12)
)
CREATE TABLE #TempPay
(
Company VARCHAR(8),
InvoiceNum INT,
InvoicePayments DECIMAL(18,2)
)
INSERT INTO #ARAging
SELECT IH.Company,(CASE WHEN IH.CreditMemo=1 THEN 'CM' WHEN IH.DebitNote=1
THEN 'DN' ELSE 'IV' END) , IH.InvoiceNum, IH.InvoiceAmt, IH.InvoiceAmt,
IH.InvoiceDate,IH.DueDate, DATEDIFF(DAY, CASE WHEN @AgeOnDueDate=1 THEN
IH.DueDate ELSE IH.InvoiceDate END,@AsOfDate), IH.CustNum, C.CustID,
C.Name, IH.PONum, C.PhoneNum,'0',CASE IH.COMPANY WHEN 'CAN' THEN
REPLACE(IH.CurrencyCode,'BASE','CAN') ELSE
REPLACE(IH.CurrencyCode,'BASE','USD') END
FROM Mfgsys803..InvcHead IH WITH(NOLOCK)
INNER JOIN Mfgsys803..Customer C WITH(NOLOCK) ON IH.Company= C.Company AND
IH.CustNum=C.CustNum
WHERE IH.InvoiceDate <= @AsOfDate
AND (IH.ClosedDate > @AsOfDate OR IH.ClosedDate IS NULL)
AND IH.Company=@Company
AND C.CustID LIKE ISNULL(@CustID,'%')
INSERT INTO #TempPay
SELECT CS.Company, CS.InvoiceNum,SUM(CASE WHEN CS.TranType='PayInv' THEN
CS.TranAmt*-1 ELSE CS.TranAmt END)
FROM Mfgsys803..CashDtl CS WITH(NOLOCK)
WHERE CS.TranDate <=@AsOfDate AND CS.Posted=1 AND CS.Company=@Company AND
CS.InvoiceNum IN (
SELECT IH.InvoiceNum
FROM Mfgsys803..InvcHead IH WITH(NOLOCK)
INNER JOIN Mfgsys803..Customer C WITH(NOLOCK) ON IH.Company= C.Company AND
IH.CustNum=C.CustNum
WHERE IH.InvoiceDate <= @AsOfDate
AND (IH.ClosedDate > @AsOfDate OR IH.ClosedDate IS NULL)
AND IH.Company=@Company)
AND CS.DebitNote=0
Group By CS.Company, CS.InvoiceNum
INSERT INTO #TempPay
SELECT CS.Company, CS.InvoiceRef,SUM(CS.TranAmt*-1)--SUM(CASE WHEN
CS.TranType='PayInv' THEN CS.TranAmt*-1 ELSE CS.TranAmt END)
FROM Mfgsys803..CashDtl CS WITH(NOLOCK)
WHERE CS.TranDate <=@AsOfDate AND CS.Posted=1 AND CS.Company=@Company AND
CS.InvoiceRef IN (
SELECT IH.InvoiceNum
FROM Mfgsys803..InvcHead IH WITH(NOLOCK)
INNER JOIN Mfgsys803..Customer C WITH(NOLOCK) ON IH.Company= C.Company AND
IH.CustNum=C.CustNum
WHERE IH.InvoiceDate <= @AsOfDate
AND (IH.ClosedDate > @AsOfDate OR IH.ClosedDate IS NULL)
AND IH.Company=@Company)
AND CS.DebitNote=0
Group By CS.Company, CS.InvoiceRef
--UPDATE ARG
--SET ARG.InvoiceBal = (ARG.InvoiceBal + CASE WHEN CD.TranType='PayInv'
THEN CD.TranAmt*-1 ELSE CD.TranAmt END)
--FROM #ARAging ARG
--INNER JOIN Mfgsys803..CashDtl CD WITH(NOLOCK) ON ARG.Company = CD.Company
AND ARG.InvoiceNum=CD.InvoiceNum
-- WHERE CD.TranDate <=@AsOfDate AND CD.Posted=1 AND CD.DebitNote=0
UPDATE ARG
SET ARG.InvoiceBal = (ARG.InvoiceBal + ISNULL(T.InvoicePayments,0))
FROM #ARAging ARG INNER JOIN
#TempPay T ON ARG.Company = T.Company AND ARG.InvoiceNum=T.InvoiceNum
DECLARE @FiscalP As Varchar(50)
SELECT @FiscalP = CAST(F.FiscalYear AS nvarchar) + '/' +
CAST(F.FiscalPeriod As nvarchar)
FROM Mfgsys803..Fiscal F WITH(NOLOCK)
WHERE @AsOfDate BETWEEN F.StartDate AND F.EndDate AND F.Company=@Company
Update #ARAging
SET FiscalPeriod =@FiscalP
SELECT * FROM #ARAging
Order By CustName, CustID , InvoiceNum
DROP TABLE #ARAging
DROP TABLE #TempPay
END
*Jose C Gomez*
*Software Engineer*
*
*
*
*T: 904.469.1524 mobile
E: jose@...
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>
<http://www.usdoingstuff.com>
*Quis custodiet ipsos custodes?*
On Thu, Apr 4, 2013 at 4:25 PM, Anthony Gercar <agercar@...> wrote:
> **
>
>
> Can you help me out with that? What version of excel are you on?
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of sanjay219@...
> Sent: Thursday, April 4, 2013 4:23 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Aged Receivables as of Date
>
>
> Sorry, that was a mistake. My bad! I used invhead.invoicedate. Not apinvhed
>
> [Non-text portions of this message have been removed]
>
>
>
[Non-text portions of this message have been removed]
Thank you Jose,
Now I am very much closer. Earlier it was 1M$ difference, Now its only 7k.
I used the following in Excel Data Properties Command text.
SELECT invchead.openinvoice, invchead.closeddate, invchead.posted, invchead.invoicenum, invchead.invoicedate, invchead.invoiceamt, invchead.invoicebal, customer.custid, customer.custnum, customer.name
FROM MfgTest803.dbo.customer customer, MfgTest803.dbo.invchead invchead
WHERE ((invchead.closeddate>'1/10/2013') or (invchead.closeddate is null )) and invchead.invoicedate<='1/10/2013' and customer.custnum = invchead.custnum AND customer.company = invchead.company
where 1/10/2013 is my as of date. And for invoice amount I used formula:
if OpenInvoice=1;
InvoiceBalance
else
InvoiceAmount.
How can I fix the difference? Please help.
Now I am very much closer. Earlier it was 1M$ difference, Now its only 7k.
I used the following in Excel Data Properties Command text.
SELECT invchead.openinvoice, invchead.closeddate, invchead.posted, invchead.invoicenum, invchead.invoicedate, invchead.invoiceamt, invchead.invoicebal, customer.custid, customer.custnum, customer.name
FROM MfgTest803.dbo.customer customer, MfgTest803.dbo.invchead invchead
WHERE ((invchead.closeddate>'1/10/2013') or (invchead.closeddate is null )) and invchead.invoicedate<='1/10/2013' and customer.custnum = invchead.custnum AND customer.company = invchead.company
where 1/10/2013 is my as of date. And for invoice amount I used formula:
if OpenInvoice=1;
InvoiceBalance
else
InvoiceAmount.
How can I fix the difference? Please help.
Sanya
I gave you a fully working copy of my accounts receivable production. it
appears you cut off half of the code ...
you are missing any reference to payment credit debit etc...
**
Thank you Jose,
Now I am very much closer. Earlier it was 1M$ difference, Now its only 7k.
I used the following in Excel Data Properties Command text.
SELECT invchead.openinvoice, invchead.closeddate, invchead.posted,
invchead.invoicenum, invchead.invoicedate, invchead.invoiceamt,
invchead.invoicebal, customer.custid, customer.custnum, customer.name
FROM MfgTest803.dbo.customer customer, MfgTest803.dbo.invchead invchead
WHERE ((invchead.closeddate>'1/10/2013') or (invchead.closeddate is null ))
and invchead.invoicedate<='1/10/2013' and customer.custnum =
invchead.custnum AND customer.company = invchead.company
where 1/10/2013 is my as of date. And for invoice amount I used formula:
if OpenInvoice=1;
InvoiceBalance
else
InvoiceAmount.
How can I fix the difference? Please help.
[Non-text portions of this message have been removed]
I gave you a fully working copy of my accounts receivable production. it
appears you cut off half of the code ...
you are missing any reference to payment credit debit etc...
**
Thank you Jose,
Now I am very much closer. Earlier it was 1M$ difference, Now its only 7k.
I used the following in Excel Data Properties Command text.
SELECT invchead.openinvoice, invchead.closeddate, invchead.posted,
invchead.invoicenum, invchead.invoicedate, invchead.invoiceamt,
invchead.invoicebal, customer.custid, customer.custnum, customer.name
FROM MfgTest803.dbo.customer customer, MfgTest803.dbo.invchead invchead
WHERE ((invchead.closeddate>'1/10/2013') or (invchead.closeddate is null ))
and invchead.invoicedate<='1/10/2013' and customer.custnum =
invchead.custnum AND customer.company = invchead.company
where 1/10/2013 is my as of date. And for invoice amount I used formula:
if OpenInvoice=1;
InvoiceBalance
else
InvoiceAmount.
How can I fix the difference? Please help.
[Non-text portions of this message have been removed]