Sub RMHistoricalAging()
Dim strAsOfDate As String
strAsOfDate = Range("B1").Value
If Not IsDate(strAsOfDate) Then
MsgBox ("Invalid Date")
Exit Sub
End If
'clear the sheet
'Dim deleteRange As Range
'deleteRange = ("a3:aa10")
'deleteRange.Rows.delete
Range("a4:aa10000").Rows.delete
' Create a connection object.
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=vmGP10;INITIAL CATALOG=two;INTEGRATED SECURITY=sspi;"
'Now open the connection.
oConn.Open strConn
' Create a recordset object.
Dim oRS As ADODB.Recordset
Set oRS = New ADODB.Recordset
Dim oCMD As ADODB.Command
Set oCMD = New ADODB.Command
oCMD.ActiveConnection = oConn
oCMD.CommandType = adCmdStoredProc
oCMD.CommandText = "FP_RMHistoricalAging"
oCMD.Parameters.Append oCMD.CreateParameter("@AsOf", adDBTimeStamp, adParamInput, 0, strAsOfDate)
Set oRS = oCMD.Execute
If Not oRS.EOF Then
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A4").CopyFromRecordset oRS
Sheet1.Cells.Columns.AutoFit
Else
MsgBox ("No data returned")
End If
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
End Sub
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'FP_RMHistoricalAging'
)
DROP PROCEDURE dbo.FP_RMHistoricalAging
GO
CREATE PROCEDURE dbo.FP_RMHistoricalAging
-- FP_RMHistoricalAging '1/2/2014'
@AsOf datetime
AS
--NOTE - this script shows all the distributions for a document. One document will have many lines, one for each distribution
set transaction isolation level read uncommitted
declare @dstindx int
select @dstindx = ACTINDX
from gl00105 g5
where g5.actnumst = '000-1200-00'
--select * from gl00105 where actindx = 6
select
RMopen.CUSTNMBR,
custMaster.custname,
RMopen.GLPOSTDT,
RMopen.docnumbr as vchrsopnumber,
rmopen.ORTRXAMT,
--dist.DSTINDX,
case dist.DISTTYPE when 1 then 'Cash'
when 2 then 'Payable'
when 3 then 'Discount Available'
when 4 then 'Discount Taken'
when 5 then 'Finance Charge'
when 6 then 'Purchase'
when 8 then 'Misc'
when 9 then 'Freight'
when 10 then 'Taxes'
when 11 then 'Writeoffs'
when 12 then 'Other'
when 13 then 'GST Disc'
else 'xx' end as DistType,
dist.CRDTAMNT,
dist.DEBITAMT,
RMopen.docnumbr,
case RMopen.RMDTYPAL when 1 then 'Invoice'
when 3 then 'Debit Memo'
when 4 then 'Fin Chrg'
when 5 then 'Service'
when 7 then 'Credit Memo'
when 8 then 'Return'
when 9 then 'Payment'
end as docType,
isnull(apply.appliedAmt,0) as appliedAmt,
apply.glpostdt as LastApplyDate
from rm20101 RMopen with (nolock)
join RM10101 dist on dist.DOCNUMBR = RMopen.DOCNUMBR and dist.RMDTYPAL = RMopen.RMDTYPAL
--apply to invoices
left join ( select aptodcnm as docnumbr,APTODCTY as rmdtypal,sum(apptoamt +distknam + WROFAMNT) as appliedAmt, MAX(date1) as glpostdt
from rm20201 with (nolock)
where glPostDt <= @AsOf
group by aptodcnm,APTODCTY
union all
select APFRDCNM,APFRDCTY, sum(APFRMAPLYAMT + APFRMDISCTAKEN + APFRMWROFAMT) as appliedAmt , MAX(date1) as glpostdt
from rm20201 with (nolock)
where glPostDt <= @AsOf
group by APFRDCNM,APFRDCTY
) apply
on apply.docnumbr = rmOpen.docnumbr
and apply.RMDTYPAL = rmOpen.RMDTYPAL
left join rm00101 CustMaster with (nolock) on custMaster.custnmbr = RMopen.custnmbr
where (RMopen.voidstts = 0 or (RMopen.voidstts = 1 and voiddate > @AsOf))
and rmOpen.glpostdt <= @AsOf
--hide fully paid docs
and RMopen.ORTRXAMT <> isnull(apply.appliedAmt,0)
order by rmopen.CUSTNMBR, RMopen.GLPOSTDT
-- FP_RMHistoricalAging '1/2/2014'
--select docnumbr, glpostdt, ortrxamt, curtrxam, voidstts from rm20101
GO
Grant exec on FP_RMHistoricalAging to public