February 13, 2009 at 12:07 pm
This is the scenario: We have a report to display just a SUMMARY of customers and how much the owe us. The customer ids are then parsed into another report which prints the details of all the customers.
Here's the issue: We have about 900 customers and Im not sure if its due to that but the report in SSRS is blowing up. Does anyone know if there's a limit of records that can be displayed or ran?
We have already changed the SSRS Report property not to time out. Is there any where else or anything else we can do to stop this? This has been going on for over three months now.:crazy:
Any direction or help would be much appreciated.
Thanks,
Sam
February 13, 2009 at 12:32 pm
Where is the timeout occurring, on the report server or at the source database?
Have you looked at a trace of this query to see what is happening at the backend database. If the query is taking a long time perhaps that would be the best place to start...
Also how are you doing the detail rows? You stated "The customer ids are then parsed into another report which prints the details of all the customers. " Does that mean you have a primary report and a subreport? If I'm remembering correctly when using subreports the original query is re-executed for each sub report or some such silliness...
Again a trace would be the best place to look for this behavior. If that is the case, then you might want to find a different way of displaying your data.
-Luke.
February 13, 2009 at 12:46 pm
Luke L (2/13/2009)
Where is the timeout occurring, on the report server or at the source database?Have you looked at a trace of this query to see what is happening at the backend database. If the query is taking a long time perhaps that would be the best place to start...
Also how are you doing the detail rows? You stated "The customer ids are then parsed into another report which prints the details of all the customers. " Does that mean you have a primary report and a subreport? If I'm remembering correctly when using subreports the original query is re-executed for each sub report or some such silliness...
Again a trace would be the best place to look for this behavior. If that is the case, then you might want to find a different way of displaying your data.
-Luke.
The time out is occuring on the report server and in Visual Studio when you run the report locally. the customer ids from the summary report are dumped into a physical table we created and then the detail report goes into that table row by row (dbts_cursor) and prints out the details (which could be 10 pages long depending on the customer) of that specific customer. I thought about the subreport report approach but not sure that would work either like you said.
I will run a trace to see what happens. I appreciate your help in this Luke.
Thanks,
Sam 🙂
February 13, 2009 at 12:52 pm
The time out is occuring on the report server and in Visual Studio when you run the report locally. the customer ids from the summary report are dumped into a physical table we created and then the detail report goes into that table row by row (dbts_cursor) and prints out the details (which could be 10 pages long depending on the customer) of that specific customer. I thought about the subreport report approach but not sure that would work either like you said.
I will run a trace to see what happens. I appreciate your help in this Luke.
Thanks,
Sam 🙂
So it sounds like the database server itself is where the timeout is occurring. What do you mean the detail report goes into your other table Row by Row. Sounds like some sort of RBAR situation. Any reason why you are using a Cursor to process this row by row? Why can't you just bring it back as a dataset and then use SSRS to do your agregation and such?
Perhaps if you post your query and some sample data etc for your details query we might be able to help you tune it to perform much better.
Check out Jeff's article on posting sample data to get the best help here... http://www.sqlservercentral.com/articles/Best+Practices/61537/
-Luke.
February 13, 2009 at 1:13 pm
Below is the script that generates the summary report
*******************************************************
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[apus_sp_rpt_FSARefunds]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[apus_sp_rpt_FSARefunds]
GO
IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'aaaFSAStudentRefundLedger') and type = 'U')
BEGIN
CREATE TABLE aaaFSAStudentRefundLedger
(
StudentID nvarchar(15) NOT NULL,
)
END
GO
/****** Object: StoredProcedure [dbo].[apus_sp_rpt_FSARefunds]
Script Date: 11/11/2008 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[apus_sp_rpt_FSARefunds]
(@T4CreditBalanceWithdrawalDesc varchar(4000))
AS
BEGIN
SET NOCOUNT ON
DECLARE @DateStart DateTime
,@DateEnd DateTime
SET @DateStart = '01-01-1900'
SET @DateEnd = '12-31-2099'
DECLARE @data AS VARCHAR(300)
DECLARE @SplitOn AS CHAR(1)
DECLARE @Cnt INT
--DECLARE @tblT4CreditBalanceWithdrawalDesc TABLE( T4CB VARCHAR(50) )
create table #tblT4CreditBalanceWithdrawalDesc (T4CB VARCHAR(500))
SET @data = @T4CreditBalanceWithdrawalDesc
SET @SplitOn = ','
SET @Cnt = 1
--Set @data= 'PEL,FDG'
WHILE (Charindex(@SplitOn,@Data)>0)
BEGIN
INSERT INTO #tblT4CreditBalanceWithdrawalDesc (T4CB)
SELECT ltrim(rtrim(Substring(@Data, 0,Charindex(@SplitOn,@Data))))
SET @data = LTRIM(RTRIM(Substring(@Data, Charindex(@SplitOn,@Data) + 1, Len(@Data))))
END
INSERT INTO #tblT4CreditBalanceWithdrawalDesc (T4CB)
SELECT @data
create clustered index temp_ix_#tblT4CreditBalanceWithdrawalDesc_T4CB on #tblT4CreditBalanceWithdrawalDesc(T4CB)
create table #tblCreditBalance
(
StudentID varchar(20)
,CreditBalance varchar(25)
,Semester varchar(50)
)
INSERT INTO #tblCreditBalance
--Select * from tmpBalance
SELECT *
FROM dbo.fn_CreditBalance(@DateStart, @DateEnd)
create clustered index temp_ix_#tblCreditBalance on #tblCreditBalance(StudentID)
-------------------------
create table #tblLedger (CUSTNMBRvarchar(15) ,NEWDOCNUMvarchar(22),Semester varchar(31))
INSERT INTO #tblLedger (CUSTNMBR, NEWDOCNUM, Semester)
---------------------
SELECT DISTINCT
r.CUSTNMBR
,CASE WHEN LEFT(RTRIM(r.DOCNUMBR),2) in ('DP','WD')
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
ELSE RTRIM(r.DOCNUMBR)
END
,hm2503.DSCRIPTN AS Semester
FROM dbo.SOP10106 s WITH (NOLOCK)
RIGHT OUTER JOIN dbo.SOP30300 sh WITH (NOLOCK)
RIGHT OUTER JOIN dbo.HM02503 hm2503 WITH (NOLOCK)
INNER JOIN dbo.HM02500 hm25 WITH (NOLOCK) ON hm2503.HM_Key_3 = hm25.HM_Key_3
RIGHT OUTER JOIN dbo.RM20101 r WITH (NOLOCK) ON hm25.DOCNUMBR = r.DOCNUMBR
ON sh.SOPNUMBE = r.DOCNUMBR
ON s.SOPNUMBE = r.DOCNUMBR
WHERE (hm25.RMDTYPAL <> 99 OR hm25.RMDTYPAL IS NULL)
AND r.RMDTYPAL <> 3
AND r.CUSTNMBR IN ( SELECT convert(varchar, StudentID, 15)
FROM APUS_PAD_LINKED.apus_pad.dbo.tblAcademicYear )
-- ANDr.CUSTNMBR = @StudentID
--Payables
INSERT INTO #tblLedger (CUSTNMBR, NEWDOCNUM, Semester)
SELECT b.CUSTNMBR
,CASE WHEN LEFT(RTRIM(b.APTODCNM),2) in ('DP','WD')
THEN RIGHT(RTRIM(b.APTODCNM),7)
WHEN LEFT(RTRIM(b.APTODCNM),5) = 'DEBIT'
THEN RIGHT(RTRIM(b.APFRDCNM),7)
ELSE RTRIM(b.APTODCNM)
END
,f.DSCRIPTN AS HM2503Desc
FROM dbo.HM02500 a WITH (NOLOCK)
INNER JOIN dbo.RM20201 b WITH (NOLOCK) ON a.DOCNUMBR = b.APFRDCNM
INNER JOIN dbo.PM30200 c WITH (NOLOCK) ON b.APTODCNM = RIGHT(RTRIM(c.TRXDSCRN), 13)
INNER JOIN dbo.PM30300 d WITH (NOLOCK) ON c.DOCNUMBR = d.APTODCNM AND c.VENDORID = d.VENDORID
INNER JOIN dbo.HM02500A E WITH (NOLOCK) ON a.DOCNUMBR = E.DOCNUMBR AND b.APTODCNM = E.hm_string_10
INNER JOIN dbo.HM02503 f WITH (NOLOCK) ON a.HM_Key_3 = f.HM_Key_3
WHERE a.RMDTYPAL = 9 and b.CUSTNMBR in (SELECT convert(varchar, StudentID, 15)
FROM APUS_PAD_LINKED.apus_pad.dbo.tblacademicyear)
-- AND b.CUSTNMBR = @StudentID
--Select * from #tblLedger
create clustered index temp_ix_#tblLedger on #tblLedger(CUSTNMBR)
---------------
create table #tblFSARefunds
(
StudentID char(20) null
,RefundAmount Numeric(11,2) null
,Semester varchar(30) null
,NonFSAIndicator varchar(1) null
,PELLIndicator varchar(1) null
,FFELPIndicator varchar(1) null
,DirectLoan varchar(1) null
)
INSERT INTO #tblFSARefunds (StudentID, RefundAmount, Semester,
NonFSAIndicator, PELLIndicator,FFELPIndicator,DirectLoan)
SELECT DISTINCT RM_TRX.CUSTNMBR
,Credit.CreditBalance
,Credit.Semester
,MAX(CASE
When UPPER(RM_TRX.BACHNUMB) like '%FSS%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%FFU%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%FFP%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%PELL%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%FDS%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%FDU%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%FDP%' then 'N'
When UPPER(RM_TRX.BACHNUMB) like '%UNSUB%' then 'N' -- Alias to FFU
When UPPER(RM_TRX.BACHNUMB) like '%SUB%' then 'N' -- Alias to FDS
When UPPER(RM_TRX.BACHNUMB) like '%PLUS%' then 'N' -- Alias to FDP
Else 'Y'
END) AS NonFSAIndicator
,MAX(CASE UPPER(HM.HM_Key_2)WHEN 'PELL' THEN 'Y'
ELSE 'N'
END) AS PELLIndicator
,MAX(CASE UPPER(HM.HM_Key_2)WHEN 'SUB' THEN 'Y'
WHEN 'UNSUB' THEN 'Y'
WHEN 'PLUS' THEN 'Y'
ELSE 'N'
END) AS FFELPIndicator
,MAX(CASE UPPER(HM.HM_Key_2)WHEN 'DSUB' THEN 'Y'
WHEN 'DUNSUB' THEN 'Y'
WHEN 'DPLUS' THEN 'Y'
ELSE 'N'
END) AS DirectLoan
FROM RM20101 AS RM_TRX (NOLOCK)
INNER JOIN #tblLedger AS Ledger ON RM_TRX.CUSTNMBR = Ledger.CUSTNMBR
AND RM_TRX.DOCNUMBR = NEWDOCNUM
INNER JOIN #tblCreditBalance AS Credit ON RM_TRX.CUSTNMBR = Credit.StudentID
AND Ledger.Semester = Credit.Semester
INNER JOIN HM02500 AS HM (NOLOCK) ON HM.DOCNUMBR = RM_TRX.DOCNUMBR
GROUP BY RM_TRX.CUSTNMBR, Credit.CreditBalance, Credit.Semester
INSERT INTO #tblFSARefunds (StudentID, RefundAmount, Semester)
SELECT StudentID
,CreditBalance
,Semester
FROM #tblCreditBalance AS Credit
WHERE LTRIM(RTRIM(Credit.Semester)) = ''
-- AND Credit.StudentID = @StudentID
--SELECT * FROM #tblFSARefunds
create clustered index temp_ix_#tblFSARefunds on #tblFSARefunds (StudentID)
-------------------------------------
Create table #MostRecentAY
(
StudentID int
,SemesterID int
,AYDesc varchar(100)
)
INSERT #MostRecentAY (StudentID, SemesterID)
SELECT ay.StudentID
,MAX(ay.SemesterID) as 'SemesterID'
FROM APUS_PAD_LINKED.apus_pad.dbo.tblAcademicYear ay
WHERE DateCancelled is NULL
-- AND ay.StudentID = CAST(@StudentID as INT)
GROUP BY ay.StudentID
ORDER BY ay.StudentID
create clustered index temp_ix_#MostRecentAY on #MostRecentAY(StudentID)
------
UPDATE RecentAY
SET AYDesc = ay.TitleIVDescription
FROM #MostRecentAY RecentAY, APUS_PAD_LINKED.apus_pad.dbo.tlkpSemester ay
WHERE RecentAY.SemesterID = ay.SemesterID
SELECT DISTINCT s.StudentID
,st.Description
,s.FirstName
,s.LastName
,CustomerMaster.ADDRESS1
,CustomerMaster.ADDRESS2
,CASE
WHEN LEFT(CustomerMaster.ZIP,3) in ('090','091','092','093','094','095','096','097','098','962','963','964','965','340')
THEN 'APO'
ELSE CustomerMaster.CITY
END AS City
,CASE
WHEN LEFT(CustomerMaster.ZIP,3) in ('090','091','092','093','094','095','096','097','098') THEN 'AE'
WHEN LEFT(Zip,3) in ('962','963','964','965') THEN 'AP'
WHEN LEFT(Zip,3) = '340' THEN 'AA'
ELSE CustomerMaster.STATE
END AS State
,CustomerMaster.ZIP
,CustomerMaster.COUNTRY
,@T4CreditBalanceWithdrawalDesc T4BCDescription
,T4CreditBalanceWithdrawalDesc TitleIVCredBalance
,RefundAmount
,Semester
,mray.AYDesc
,NonFSAIndicator
,PELLIndicator
,DirectLoan
,FFELPIndicator
FROM #tblFSARefunds AS Refunds
INNER JOIN RM00101 AS CustomerMaster (NOLOCK) ON Refunds.StudentID = CustomerMaster.CUSTNMBR
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tblStudent s ON Refunds.StudentID =s.StudentID
INNER JOIN APUS_PAD_LINKED.APUS_PAD.dbo.tblstudentdegree sd on s.StudentID = sd.StudentID
LEFT JOIN APUS_PAD_LINKED.APUS_PAD.dbo.tlkpT4CreditBalanceWithdrawal tb ON tb.T4CreditBalanceWithdrawalID = s.T4CreditBalanceID
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tlkpAcademicStatus st ON s.CurrentAcademicStatusID = st.AcademicStatusID
LEFT JOIN #MostRecentAY mray ON mray.StudentID=Refunds.StudentID
WHERE tb.T4CreditBalanceWithdrawalDesc IN (SELECT T4CB FROM #tblT4CreditBalanceWithdrawalDesc)
AND Refunds.RefundAmount < 0
AND AYDesc NOT IN ('')
ORDER BY StudentID
-------delete old records and insert new records from a table---
TRUNCATE TABLE aaaFSAStudentRefundLedger
insert into aaaFSAStudentRefundLedger
select distinct s.StudentID FROM #tblFSARefunds AS Refunds
INNER JOIN RM00101 AS CustomerMaster (NOLOCK) ON Refunds.StudentID = CustomerMaster.CUSTNMBR
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tblStudent s ON Refunds.StudentID =s.StudentID
INNER JOIN APUS_PAD_LINKED.APUS_PAD.dbo.tblstudentdegree sd on s.StudentID = sd.StudentID
LEFT JOIN APUS_PAD_LINKED.APUS_PAD.dbo.tlkpT4CreditBalanceWithdrawal tb ON tb.T4CreditBalanceWithdrawalID = s.T4CreditBalanceID
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tlkpAcademicStatus st ON s.CurrentAcademicStatusID = st.AcademicStatusID
LEFT JOIN #MostRecentAY mray ON mray.StudentID=Refunds.StudentID
WHERE tb.T4CreditBalanceWithdrawalDesc IN (SELECT T4CB FROM #tblT4CreditBalanceWithdrawalDesc)
AND Refunds.RefundAmount < 0
AND AYDesc NOT IN ('')
ORDER BY StudentID
drop table #tblCreditBalance
drop table #tblFSARefunds
drop table #tblLedger
drop table #MostRecentAY
SET NOCOUNT OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXECUTE ON [dbo].[apus_sp_rpt_FSARefunds] TO [CrystalReports]
*********************************************************************
After the report runs, the studentids (customer ids) are dumped in the table we created (aaFSAStudentRefundLedger ). We have a hyperlink on the report that has a default value of zero that is parsed after the users click the hyperlink that triggers the details report to run.
Please see below for the details report script. Please note that we had to clone the script into two parts since we want to separate parsing single cutomer ids and mulitple ids (0) from the summary report.
************************************************************************
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[apus_sp_rpt_StudentLedger_FSA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[apus_sp_rpt_StudentLedger_FSA]
GO
/****** Object: StoredProcedure [dbo].[apus_sp_rpt_StudentLedger_FSA]
Script Date: 09/03/2008 16:33:45, MSO ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[apus_sp_rpt_StudentLedger_FSA]
@StudentID int
AS
DECLARE @studentVARCHAR(20)
DECLARE @InvoiceVARCHAR(30)
DECLARE @sidVARCHAR(20)
--set @StudentID = '3086343'
DECLARE @tblLedger TABLE
(
CUSTNMBR varchar(15)
,DOCNUMBR varchar(22)
,NEWDOCNUMvarchar(22)
,DOCDATE datetime null
,ORTRXAMT numeric(19,5)
,CURTRXAMnumeric(19,5)
,RMDTYPALint
,PaymentDatedatetime null
,Paymentnumeric(19,5)
,PaidFromvarchar(22)
,Statusvarchar(20)
,FRTSCHIDvarchar(16)
,MSCSCHIDvarchar(16)
,TRXDSCRNvarchar(32)
,BACHNUMBvarchar(16)
,ITEMNMBRvarchar(32)
,ITEMDESCvarchar(92)
,SOPTYPEint
,USRDAT01datetime
,USRDAT02datetime
,USERDEF1varchar(22)
,USERDEF2varchar(22)
,USRDEF05varchar(22)
,FundSrc25 varchar(11)
,FundSrc18varchar(11)
,HM1803IDint
,HM1803Semvarchar(31)
,HM2503IDint
,HM2503Semvarchar(31)
,CheckNumvarchar(21)
,CheckDTdatetime null
,VoidSTTSint
)
--create clustered index temp_ix_#tblLedger on #tblLedger (CUSTNMBR)
---
DECLARE @tblFinalLedger TABLE
(
CUSTNMBR varchar(15)
,DOCNUMBR varchar(22)
,NEWDOCNUMvarchar(22)
,DOCDATE datetime null
,ORTRXAMT numeric(19,5)
,CURTRXAMnumeric(19,5)
,RMDTYPALint
,PaymentDatedatetime null
,Paymentnumeric(19,5)
,PaidFromvarchar(22)
,Statusvarchar(20)
,FRTSCHIDvarchar(16)
,MSCSCHIDvarchar(16)
,TRXDSCRNvarchar(32)
,BACHNUMBvarchar(16)
,ITEMNMBRvarchar(32)
,ITEMDESCvarchar(92)
,SOPTYPEint
,USRDAT01datetime
,USRDAT02datetime
,USERDEF1varchar(22)
,USERDEF2varchar(22)
,USRDEF05varchar(22)
,FundSrc25 varchar(11)
,FundSrc18varchar(11)
,HM1803IDint
,HM1803Semvarchar(31)
,HM2503IDint
,HM2503Semvarchar(31)
,CheckNumvarchar(21)
,CheckDT datetime null
,VoidSTTSint
,StudentID int
,FullNameAndStatus varchar(50)
,RegID int
,StudentDegreeID int
,TitleIVDescription varchar(100)
,SemStart DATETIME null
)
--DECLARE @custnmbr varchar(15)
--,@docnumbr varchar(20)
--,@newdocnum varchar(20)
--
--declare @tblPMENT table
--(
--CUSTNMBRvarchar(15),
--DOCNUMBR varchar(22),
--NEWDOCNUMvarchar(22)
--)
--create clustered index temp_ix_@tblFinalLedger on @tblFinalLedger (CUSTNMBR)
--create table #tblPMENT
--(
--CUSTNMBRvarchar(15),
--DOCNUMBR varchar(22),
--NEWDOCNUMvarchar(22)
--)
--
--create clustered index temp_ix_#tblPMENT on #tblPMENT(CUSTNMBR)
-------------------------
IF @StudentID = 0
Begin
-- begin
-- TRUNCATE TABLE aaaFSAStudentRefundLedger
--insert into aaaFSAStudentRefundLedger Select @StudentID as StudentID
--end
DECLARE curStudentLedger INSENSITIVE CURSOR
--FAST_FORWARD READ_ONLY
FOR SELECT distinct StudentID
FROM dbo.aaaFSAStudentRefundLedger WITH (NOLOCK)
OPEN curStudentLedger
FETCH NEXT FROM curStudentLedger
INTO @sid
--go through each of those student and find the validated (by Registra) request
WHILE @@FETCH_STATUS = 0
BEGIN
set @student = cast(@SID as varchar(20))
INSERT @tblLedger
SELECT DISTINCT r.CUSTNMBR
, RTRIM(r.DOCNUMBR) AS DocNum
,CASE WHEN LEFT(RTRIM(r.DOCNUMBR),2) IN ('DP','WD')
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN LEFT(RTRIM(r.DOCNUMBR),3) like 'CP%'
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN (LEFT(RTRIM(r.DOCNUMBR),3) LIKE 'CR%' AND r.RMDTYPAL <> 7)
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN LEFT(RTRIM(r.DOCNUMBR),5) = 'CREDT'
THEN CASE WHEN LEFT((SELECT TOP 1 APTODCNM
FROM RM20201 WITH (NOLOCK)
WHERE APFRDCNM = r.DOCNUMBR),2) = 'WD'
THEN RIGHT(RTRIM((SELECT TOP 1 APTODCNM
FROM RM20201 WITH (NOLOCK)
WHERE APFRDCNM = r.DOCNUMBR)),7)
ELSE (SELECT TOP 1 APTODCNM
FROM RM20201 WITH (NOLOCK)
WHERE APFRDCNM = r.DOCNUMBR)
END
ELSE RTRIM(r.DOCNUMBR)
END AS NewDocNum
,r.DOCDATE
,r.ORTRXAMT
,r.CURTRXAM
,r.RMDTYPAL
,NULL
,NULL
,NULL
,CASE WHEN r.VOIDSTTS <> 0--modified hard coded string 'Posted' to reflect the true RM Void Status, MSO, 09/19/2008
THEN 'Void'
ELSE 'Posted'
END Status
,r.FRTSCHID
,r.MSCSCHID
,r.TRXDSCRN
,r.BACHNUMB
,sh.ITEMNMBR
,sh.ITEMDESC
,s.SOPTYPE
,s.USRDAT01
,s.USRDAT02
,s.USERDEF1
,s.USERDEF2
,RTRIM(s.USRDEF05)
,hm25.HM_Key_2 AS FundSrc25
,NULL
,NULL
,NULL
,hm2503.DEX_ROW_ID AS HM2503ID
,CASE WHEN LEFT(RTRIM(r.DOCNUMBR),5) = 'DEBIT'
THEN (SELECT CASE WHEN LEFT(RTRIM(RM20201.APFRDCNM),4) = ('PYMT')
THEN (SELECT HM02503.DSCRIPTN
FROM HM02500 WITH (NOLOCK)
INNER JOIN HM02503 WITH (NOLOCK) ON HM02500.HM_Key_3 = HM02503.HM_Key_3
WHERE HM02500.DOCNUMBR = RTRIM(RM20201.APFRDCNM))END
FROM RM20101 WITH (NOLOCK)
JOIN RM20201 WITH (NOLOCK) ON RM20101.DOCNUMBR = RM20201.APTODCNM
WHERE RMDTYPAL = '3'
AND RM20101.CUSTNMBR =@student
AND RM20101.DOCNUMBR = r.DOCNUMBR)
ELSE hm2503.DSCRIPTN
END AS HM2503Desc,
--hm2503.DSCRIPTN AS HM2503Desc,
NULL
,NULL
,r.VOIDSTTS
FROM SOP10106 s WITH (NOLOCK)
RIGHT OUTER JOIN SOP30300 sh WITH (NOLOCK)
RIGHT OUTER JOIN HM02503 hm2503 WITH (NOLOCK)
INNER JOIN HM02500 hm25 WITH (NOLOCK) ON hm2503.HM_Key_3 = hm25.HM_Key_3
RIGHT OUTER JOIN RM20101 r WITH (NOLOCK) ON hm25.DOCNUMBR = r.DOCNUMBR ON sh.SOPNUMBE = r.DOCNUMBR ON s.SOPNUMBE = r.DOCNUMBR
WHERE r.CUSTNMBR = @student
AND (NOT r.RMDTYPAL IN (99,3,9) OR r.RMDTYPAL IS NULL)
AND (LEFT(sh.ITEMNMBR,2) <> 'TA' OR sh.ITEMNMBR IS NULL)
--DEBITS
DECLARE @custnmbr varchar(15)
,@docnumbr varchar(20)
,@newdocnum varchar(20)
DECLARE dbts_cursor CURSOR FOR
SELECT DISTINCT RM20101.CUSTNMBR
,RTRIM(RM20101.DOCNUMBR) as DOCNUMBR
,MAX(CASE WHEN LEFT(RTRIM(RM20201.APFRDCNM),2) IN ('WD','DP')
THEN RIGHT(RTRIM(rm20201.APFRDCNM),7)
WHEN LEFT(RTRIM(RM20201.APFRDCNM),3) like 'CP%' --modified hard coded string 'posted'
THEN RIGHT(RTRIM(RM20201.APFRDCNM),7) --to populate status column on the report based on the SO Number
WHEN (LEFT(RTRIM(RM20201.APFRDCNM),3) LIKE 'CR%' AND RM20201.APFRDCTY <> 7)
THEN RIGHT(RTRIM(RM20201.APFRDCNM),7)
ELSE RM20201.APFRDCNM
END) as NEWDOCNUM
FROM RM20101 WITH (NOLOCK)
LEFT OUTER JOIN RM20201 WITH (NOLOCK) ON RM20101.DOCNUMBR = RM20201.APTODCNM
WHERE RMDTYPAL = '3'
AND RM20101.CUSTNMBR = @student
GROUP BY RM20101.CUSTNMBR, RTRIM(RM20101.DOCNUMBR)
OPEN dbts_cursor
FETCH NEXT FROM dbts_cursor
INTO @custnmbr, @docnumbr, @newdocnum
WHILE @@fetch_status = 0
BEGIN
IF exists (SELECT DISTINCT custnmbr
FROM HM02500A WITH (NOLOCK)
INNER JOIN RM20101 WITH (NOLOCK) ON HM02500A.DOCNUMBR = RM20101.DOCNUMBR
WHERE CUSTNMBR = @student
AND HM_String_10 = @docnumbr) --HM_String_10 LIKE '%debit%')
BEGIN
INSERT @tblLedger
SELECT b.CUSTNMBR
,RTRIM(b.APTODCNM)
,MAX(CASE WHEN LEFT(RTRIM(b.APTODCNM),2) in ('DP','WD')
THEN RIGHT(RTRIM(b.APTODCNM),7)
WHEN LEFT(RTRIM(b.APTODCNM),3) like 'CP%'
THEN RIGHT(RTRIM(b.APTODCNM),7)
WHEN (LEFT(RTRIM(b.APTODCNM),3) LIKE 'CR%' AND b.APTODCTY <> 7)
THEN RIGHT(RTRIM(b.APTODCNM),7)
WHEN LEFT(RTRIM(b.APTODCNM),5) = 'DEBIT'
THEN RIGHT(RTRIM(b.APFRDCNM),7)
ELSE RTRIM(b.APTODCNM)
END)
--b.APTODCDT, NULL, NULL, b.APTODCTY, b.APFRDCDT, hm.hm_currency_1 as RAmount
,b.APTODCDT
,r.ORTRXAMT
,r.CURTRXAM
,b.APTODCTY
,b.APFRDCDT
,hm.hm_currency_1 as RAmount
,MAX(b.APFRDCNM)
,CASE WHEN r.VoidSTTS <> 0--modified hard coded string 'Posted' to reflect the true RM Void Status, MSO, 09/19/2008
THEN 'Void'
ELSE 'Posted'
END Status
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,hm.HM_Key_2 AS FundSrc25
,NULL
,NULL
,NULL
,HM02503.DEX_ROW_ID AS HM2503ID
,RTRIM(HM02503.DSCRIPTN) AS HM2503Desc
,d.APFRDCNM AS CheckNum
,c.DOCDATE AS CheckDT
,r.VoidSTTS
FROM HM02500 hm2 WITH (NOLOCK)
INNER JOIN HM02503 WITH (NOLOCK) ON hm2.HM_Key_3 = HM02503.HM_Key_3
RIGHT OUTER JOIN HM02500A hm WITH (NOLOCK)
INNER JOIN RM20201 b WITH (NOLOCK) ON hm.DOCNUMBR = b.APFRDCNM AND hm.HM_String_10 = b.APTODCNM
--RM20201 b WITH (NOLOCK) ON hm.DOCNUMBR = b.APFRDCNM Changed 8/07/07 to fix the duplicate entry problem -- Liz
INNER JOIN RM20101 r WITH (NOLOCK) ON b.APTODCNM = r.DOCNUMBR -- Added join to RM20101 on 8/09/07 to fix Open Amount problem -- Liz
--Converted to LEFT JOINs to display all DEBIT trx, MSO, 09/22/2008
LEFT JOIN PM30200 c WITH (NOLOCK) ON b.APTODCNM = RIGHT(RTRIM(c.TRXDSCRN), 13)
LEFT JOIN PM30300 d WITH (NOLOCK) ON c.DOCNUMBR = d.APTODCNM AND c.VENDORID = d.VENDORID ON hm2.DOCNUMBR = hm.DOCNUMBR
WHERE b.CUSTNMBR = @student
AND (hm.HM_String_10 = @docnumbr) --LIKE '%debit%')
--GROUP BY b.CUSTNMBR, RTRIM(b.APTODCNM), b.APTODCDT, b.APTODCTY, b.APFRDCDT, hm.hm_currency_1,
GROUP BY b.CUSTNMBR, RTRIM(b.APTODCNM), b.APTODCDT, r.ORTRXAMT, r.CURTRXAM, b.APTODCTY, b.APFRDCDT, hm.hm_currency_1,
hm.HM_Key_2, HM02503.DEX_ROW_ID, RTRIM(HM02503.DSCRIPTN), d.APFRDCNM, c.DOCDATE, r.VoidSTTS, b.APTODCNM
END
ELSE
BEGIN
INSERT @tblLedger
SELECT DISTINCT @custnmbr
,@docnumbr
,@newdocnum
,r.DOCDATE
,r.ORTRXAMT
,r.CURTRXAM
,r.RMDTYPAL
,NULL
,NULL
,NULL
,CASE WHEN r.VOIDSTTS <> 0--modified hard coded string 'Posted' to reflect the true RM Void Status, MSO, 09/19/2008
THEN 'Void'
ELSE 'Posted'
END Status
,r.FRTSCHID
,r.MSCSCHID
,r.TRXDSCRN
,r.BACHNUMB
,sh.ITEMNMBR
,sh.ITEMDESC
,s.SOPTYPE
,s.USRDAT01
,s.USRDAT02
,s.USERDEF1
,s.USERDEF2
,RTRIM(s.USRDEF05)
,hm25.HM_Key_2 AS FundSrc25
,NULL
,NULL
,NULL
,hm2503.DEX_ROW_ID AS HM2503ID
,CASE WHEN LEFT(RTRIM(@newdocnum),4) = ('PYMT')
THEN (SELECT HM02503.DSCRIPTN
FROM HM02500 WITH (NOLOCK)
INNER JOIN HM02503 WITH (NOLOCK) ON HM02500.HM_Key_3 = HM02503.HM_Key_3
WHERE HM02500.DOCNUMBR = @newdocnum)
ELSE hm2503.DSCRIPTN
END AS HM2503Desc
,NULL
,NULL
,r.VOIDSTTS
FROM SOP10106 s WITH (NOLOCK)
RIGHT OUTER JOIN SOP30300 sh WITH (NOLOCK)
RIGHT OUTER JOIN HM02503 hm2503 WITH (NOLOCK)
INNER JOIN HM02500 hm25 WITH (NOLOCK) ON hm2503.HM_Key_3 = hm25.HM_Key_3
RIGHT OUTER JOIN RM20101 r WITH (NOLOCK) ON hm25.DOCNUMBR = r.DOCNUMBR ON sh.SOPNUMBE = r.DOCNUMBR ON s.SOPNUMBE = r.DOCNUMBR --RIGHT OUTER JOIN @tblDEBIT db ON r.DOCNUMBR = db.DOCNUMBR
WHERE r.CUSTNMBR = @student and r.DOCNUMBR = @docnumbr
END
FETCH NEXT FROM dbts_cursor
INTO @custnmbr
,@docnumbr
,@newdocnum
END
CLOSE dbts_cursor
DEALLOCATE dbts_cursor
--PAYMENTS
declare @tblPMENT table
(
CUSTNMBRvarchar(15),
DOCNUMBR varchar(22),
NEWDOCNUMvarchar(22)
)
INSERT @tblPMENT
SELECT r.CUSTNMBR
,RTRIM(r.DOCNUMBR)--(SELECT TOP 1 APTODCNM FROM RM20201 WITH (NOLOCK) WHERE CUSTNMBR = @student and RTRIM(APFRDCNM) = RTRIM(r.DOCNUMBR))
,CASE WHEN (SELECT TOP 1 LEFT(RTRIM(APTODCNM),3)
FROM RM20201 WITH (NOLOCK)
WHERE CUSTNMBR = @student and RTRIM(APFRDCNM) = RTRIM(r.DOCNUMBR)) = 'WDF'
THEN (SELECT TOP 1 RIGHT(RTRIM(APTODCNM),7)
FROM RM20201 WITH (NOLOCK)
WHERE CUSTNMBR = @student and RTRIM(APFRDCNM) = RTRIM(r.DOCNUMBR))
ELSE (SELECT TOP 1 CASE WHEN LEFT(RTRIM(APTODCNM),3) like 'CP%'
THEN RIGHT(RTRIM(APTODCNM),7)
WHEN (LEFT(RTRIM(APTODCNM),3) LIKE 'CR%' AND APTODCNM <> 7)
THEN RIGHT(RTRIM(APTODCNM),7)
ELSE APTODCNM
END APTODCNM
FROM RM20201 WITH (NOLOCK)
WHERE CUSTNMBR = @student and RTRIM(APFRDCNM) = RTRIM(r.DOCNUMBR))
END
FROM SOP10106 s WITH (NOLOCK)
RIGHT OUTER JOIN SOP30300 sh WITH (NOLOCK)
RIGHT OUTER JOIN HM02503 hm2503 WITH (NOLOCK)
INNER JOIN HM02500 hm25 WITH (NOLOCK) ON hm2503.HM_Key_3 = hm25.HM_Key_3
RIGHT OUTER JOIN RM20101 r WITH (NOLOCK) ON hm25.DOCNUMBR = r.DOCNUMBR ON sh.SOPNUMBE = r.DOCNUMBR ON s.SOPNUMBE = r.DOCNUMBR
WHERE r.RMDTYPAL = '9'
AND r.CUSTNMBR = @student
INSERT @tblLedger
SELECT DISTINCT pm.CUSTNMBR
,pm.DOCNUMBR
,pm.NEWDOCNUM
,r.DOCDATE
,r.ORTRXAMT
,r.CURTRXAM
,r.RMDTYPAL
,NULL
,NULL
,NULL
,CASE WHEN r.VOIDSTTS <> 0 --modified hard coded string 'Posted' to reflect the true RM Void Status, MSO, 09/19/2008
THEN 'Void'
ELSE 'Posted'
END Status
,r.FRTSCHID
,r.MSCSCHID
,r.TRXDSCRN
,r.BACHNUMB
,sh.ITEMNMBR
,sh.ITEMDESC
,s.SOPTYPE
,s.USRDAT01
,s.USRDAT02
,s.USERDEF1
,s.USERDEF2
,RTRIM(s.USRDEF05)
,hm25.HM_Key_2 AS FundSrc25
,NULL
,NULL
,NULL
,hm2503.DEX_ROW_ID AS HM2503ID
,CASE WHEN LEFT(RTRIM(pm.DOCNUMBR),4) = ('PYMT')
THEN (SELECT DISTINCT HM02503.DSCRIPTN
FROM HM02500 WITH (NOLOCK)
INNER JOIN HM02503 WITH (NOLOCK) ON HM02500.HM_Key_3 = HM02503.HM_Key_3
WHERE HM02500.DOCNUMBR = pm.DOCNUMBR)
ELSE hm2503.DSCRIPTN
END AS HM2503Desc
,NULL
,NULL
,r.VOIDSTTS
FROM SOP10106 s WITH (NOLOCK)
RIGHT OUTER JOIN SOP30300 sh WITH (NOLOCK)
RIGHT OUTER JOIN HM02503 hm2503 WITH (NOLOCK)
INNER JOIN HM02500 hm25 WITH (NOLOCK) ON hm2503.HM_Key_3 = hm25.HM_Key_3
RIGHT OUTER JOIN RM20101 r WITH (NOLOCK) ON hm25.DOCNUMBR = r.DOCNUMBR ON sh.SOPNUMBE = r.DOCNUMBR ON s.SOPNUMBE = r.DOCNUMBR
RIGHT OUTER JOIN @tblPMENT pm ON r.DOCNUMBR = pm.DOCNUMBR
WHERE r.CUSTNMBR = @student
--Receivables?
INSERT @tblLedger
SELECT r.CUSTNMBR
,RTRIM(r.APTODCNM)
,CASE WHEN LEFT(RTRIM(r.APTODCNM),2) in ('DP','WD')
THEN RIGHT(RTRIM(r.APTODCNM),7) -- WHEN LEFT(RTRIM(r.APTODCNM),5) = 'DEBIT' THEN RTRIM(r.APFRDCNM)
WHEN LEFT(RTRIM(r.APTODCNM),3) like 'CP%'
THEN RIGHT(RTRIM(r.APTODCNM),7)
WHEN (LEFT(RTRIM(r.APTODCNM),3) LIKE 'CR%' AND r.APFRDCTY <> 7)
THEN RIGHT(RTRIM(r.APTODCNM),7)
ELSE RTRIM(r.APTODCNM)
END
,r.APTODCDT
,NULL
,NULL
,NULL
,r.APFRDCDT
,r.ActualApplyToAmount
,r.APFRDCNM
,'Posted' Status
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,CASE WHEN LEFT(RTRIM(r.APFRDCNM),4) = ('PYMT')
THEN (SELECT HM02503.DSCRIPTN
FROM HM02500 WITH (NOLOCK)
INNER JOIN HM02503 WITH (NOLOCK) ON HM02500.HM_Key_3 = HM02503.HM_Key_3
WHERE HM02500.DOCNUMBR = r.APFRDCNM)
END AS HM2503Desc
,NULL
,NULL
,NULL
FROM dbo.RM20201 r WITH (NOLOCK)
WHERE r.CUSTNMBR = @student
AND r.APTODCTY <> '3'
INSERT @tblLedger
SELECT r.CUSTNMBR
,rtrim(r.DOCNUMBR)--r.APFRDCNM,
,CASE WHEN LEFT(RTRIM(r.DOCNUMBR),2) in ('DP','WD')
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN LEFT(RTRIM(r.DOCNUMBR),3) like 'CP%'
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN (LEFT(RTRIM(r.DOCNUMBR),3) LIKE 'CR%' AND r.RMDTYPAL <> 7)
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
ELSE RTRIM(r.DOCNUMBR) END
,r.DOCDATE
,r.DOCAMNT
,r.ACCTAMNT
,r.RMDTYPAL
,null
,null
,null
,'Unposted' Status
,null
,null
,r.DOCDESCR
,r.BACHNUMB
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
FROM rm10301 r WITH (NOLOCK)
WHERE r.custnmbr = @student
--Payables - per Chris Donohoe on 10/3/06
-- UNPOSTED BATCHES
INSERT @tblLedger
SELECT r.CUSTNMBR
,RTRIM(r.SOPNUMBE)
,CASE WHEN LEFT(RTRIM(r.SOPNUMBE),2) IN ('DP','WD')
THEN RIGHT(RTRIM(r.SOPNUMBE),7)
WHEN LEFT(RTRIM(r.SOPNUMBE),3) like 'CP%'
THEN RIGHT(RTRIM(r.SOPNUMBE),7)
WHEN (LEFT(RTRIM(r.SOPNUMBE),3) LIKE 'CR%' AND r.DOCID LIKE 'RTN%')
THEN RIGHT(RTRIM(r.SOPNUMBE),7)
ELSE RTRIM(r.SOPNUMBE)
END
,r.DOCDATE
,r.DOCAMNT
,r.ACCTAMNT
,CASE r.DOCID
WHEN 'STDINV' THEN 1
WHEN 'RTN' THEN 8
end
,null
,null
,null
,CASE WHEN (r.VOIDSTTS <> 0)--modified hard coded string 'Unposted' to reflect the true RM Void Status, MSO, 09/19/2008
THEN 'Void'
ELSE 'Unposted'
END Status
,null
,null
,null
,r.BACHNUMB
,sh.ITEMNMBR
,sh.ITEMDESC
,s.SOPTYPE
,s.USRDAT01
,s.USRDAT02
,s.USERDEF1
,s.USERDEF2
,s.USRDEF05
,null
,null
,null
,null
,null
,null
,null
,null
,r.VOIDSTTS
FROM sop10100 r WITH (NOLOCK)
LEFT JOIN SOP10200 sh WITH (NOLOCK) ON (sh.SOPNUMBE = r.SOPNUMBE)
LEFT JOIN sop10106 s WITH (NOLOCK) ON (s.sopnumbe = r.SOPNUMBE)
WHERE r.custnmbr = @student
AND (left(sh.ITEMNMBR,2) <> 'TA' OR sh.ITEMNMBR IS NULL)
--Open transactions - DB 9/5/07
INSERT @tblLedger
SELECT DISTINCT p.VENDORID
,rtrim(r.DOCNUMBR)
,CASE WHEN LEFT(RTRIM(r.DOCNUMBR),2) IN ('DP','WD')
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN LEFT(RTRIM(r.DOCNUMBR),3) like 'CP%'
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN (LEFT(RTRIM(r.DOCNUMBR),3) LIKE 'CR%' AND r.RMDTYPAL <> 7)
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN LEFT(RTRIM(r.DOCNUMBR),5) = 'DEBIT'
THEN RIGHT(RTRIM(b.APFRDCNM),7)
ELSE RTRIM(r.DOCNUMBR)
END
,r.DOCDATE
,p.DOCAMNT
,null
,p.DOCTYPE
,null
,null
,null
,CASE WHEN (r.VOIDSTTS <> 0)----modified hard coded string 'Open' to reflect the true RM Void Status, MSO, 09/19/2008
THEN 'Void'
ELSE 'Open'
END Status
,null
,null
,r.TRXDSCRN
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,HM02503.DEX_ROW_ID AS HM2503ID
,RTRIM(HM02503.DSCRIPTN) AS HM2503Desc
,null
,null
,0
--FROMHM02500 hm2 WITH (NOLOCK) INNER JOIN
--HM02503 WITH (NOLOCK) ON hm2.HM_Key_3 = HM02503.HM_Key_3 RIGHT OUTER JOIN
--HM02500A hm WITH (NOLOCK) INNER JOIN
-- RM20201 b WITH (NOLOCK) ON hm.DOCNUMBR = b.APFRDCNM AND hm.HM_String_10 = b.APTODCNM INNER JOIN
-- RM20101 r WITH (NOLOCK) ON b.APTODCNM = r.DOCNUMBR ON hm2.DOCNUMBR = hm.DOCNUMBR INNER JOIN
--PM20000 p WITH (NOLOCK) ON p.VENDORID = r.CUSTNMBR AND RIGHT(RTRIM(p.TRXDSCRN), 13) = r.DOCNUMBR
FROM HM02500 hm2 WITH (NOLOCK)
INNER JOIN HM02503 WITH (NOLOCK) ON hm2.HM_Key_3 = HM02503.HM_Key_3
RIGHT OUTER JOIN HM02500A hm WITH (NOLOCK)
INNER JOIN RM20201 b WITH (NOLOCK) ON hm.DOCNUMBR = b.APFRDCNM AND hm.HM_String_10 = b.APTODCNM
INNER JOIN RM20101 r WITH (NOLOCK) ON b.APTODCNM = r.DOCNUMBR ON hm2.DOCNUMBR = hm.DOCNUMBR
LEFT OUTER JOIN PM20000 p WITH (NOLOCK) ON r.CUSTNMBR = p.VENDORID AND RTRIM(LTRIM(SUBSTRING(r.TRXDSCRN, 11, 35))) = p.VCHRNMBR
WHERE (r.RMDTYPAL = 3)
AND p.VENDORID = @student
INSERT INTO @tblFinalLedger
SELECT * from @tblLedger
--Appended to combine the two SPs
LEFT JOIN (SELECT tsd.StudentID StudentID
,convert(varchar(15),tsd.StudentID) + ', ' + convert(varchar(35),ts.FirstName + ' ' + ts.LastName + ', ' + (CASE WHEN tsd.AcademicStatus in ('1')
THEN 'Active'
WHEN tsd.AcademicStatus in ('2')
THEN 'Disenrolled'
WHEN tsd.AcademicStatus in ('3')
THEN 'Suspended'
WHEN tsd.AcademicStatus in ('5')
THEN 'Expelled'
WHEN tsd.AcademicStatus in ('6')
THEN 'Program Hold'
WHEN tsd.AcademicStatus in ('7')
THEN 'Graduated'
WHEN tsd.AcademicStatus in ('8')
THEN 'Deceased'
WHEN tsd.AcademicStatus in ('9')
THEN 'Applicant'
WHEN tsd.AcademicStatus in ('11')
THEN ''
WHEN tsd.AcademicStatus in ('12')
THEN 'REGISTRAR HOLD'
ELSE ''
END))FullNameAndStatus
,tr.RegistrationID RegID
,tsd.StudentDegreeID
,convert(varchar(35),tsem2.TitleIVDescription)TitleIVDescription
,tsem2.SemesterStart SemStart
--please update the APUS_PAD_LINKED.apus_pad datasource to APUS_PAD_LINKED.apus_pad_datafix before executing this query in any datafix environment!!
FROM APUS_PAD_LINKED.apus_pad.dbo.tlkpAcademicStatus tac WITH (NOLOCK)
RIGHT OUTER JOIN APUS_PAD_LINKED.apus_pad.dbo.tlkpSemester tsem2 (NOLOCK)
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tblStudent ts WITH (NOLOCK)
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tblRegistration tr(NOLOCK) ON ts.StudentID = tr.StudentID
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tblCoursesOffered tco(NOLOCK) ON tr.CourseOfferedID = tco.CourseOfferedID
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tlkpSemesterRelationship tsemr2 WITH (NOLOCK)
ON tco.SemesterID = tsemr2.ChildSemesterID
ON tsem2.SemesterID = tsemr2.ParentSemesterID
ON tac.AcademicStatusID = ts.CurrentAcademicStatusID
RIGHT OUTER JOIN APUS_PAD_LINKED.apus_pad.dbo.tblAcademicYear tay WITH (NOLOCK)
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tlkpSemester tsem1 WITH (NOLOCK) ON tay.SemesterID = tsem1.SemesterID
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tlkpSemesterRelationship tsemr1 WITH (NOLOCK)
ON tsem1.SemesterID = tsemr1.ParentSemesterID
ON tsem2.SemesterID = tsemr1.ChildSemesterID AND ts.StudentID = tay.StudentID
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tblStudentDegree tsd WITH (NOLOCK) ON ts.StudentID = tsd.StudentID
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.vw_Crystal_CurrentPrimaryStudentDegree vsd WITH (NOLOCK) ON tsd.StudentDegreeID = vsd.StudentDegreeID --Added view to show only current degrees
WHERE (tay.DateCancelled IS NULL)
AND (NOT (tr.RegistrationID IS NULL))
AND tsd.StudentID = @student)padtbl ON "@tblLedger".CUSTNMBR = padtbl.StudentID AND "@tblLedger".NEWDOCNUM = CONVERT(VARCHAR(12),padtbl.RegID)
UPDATE @tblFinalLedger
SET HM2503Sem = TitleIVDescription
WHERE HM2503Sem is null
and TitleIVDescription is not null
UPDATE FinLed
SET TitleIVDescription = HM2503Sem,
SemStart = Sem.SemesterStart
FROM @tblFinalLedger AS FinLed
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tlkpSemester Sem
ON LTRIM(RTRIM(FinLed.HM2503Sem)) = LTRIM(RTRIM(Sem.TitleIVDescription))
WHERE HM2503Sem is not null
and FinLed.TitleIVDescription is null
UPDATE A
SET HM2503Sem = B.HM2503Sem,
TitleIVDescription = B.HM2503Sem,
SemStart = B.SemStart
FROM @tblFinalLedger AS A
INNER JOIN
( SELECT DOCNUMBR, HM2503Sem, SemStart FROM @tblFinalLedger WHERE HM2503Sem IS NOT NULL) B
ON A.DOCNUMBR = B.DOCNUMBR
WHERE A.HM2503Sem IS NULL
UPDATE @tblFinalLedger --Added to display student name and status on each page of report, MSO, 09/27/2008
SET FullNameAndStatus = (select CASE WHEN tsd1.AcademicStatus in ('1')
THEN 'Active'
WHEN tsd1.AcademicStatus in ('2')
THEN 'Disenrolled'
WHEN tsd1.AcademicStatus in ('3')
THEN 'Suspended'
WHEN tsd1.AcademicStatus in ('5')
THEN 'Expelled'
WHEN tsd1.AcademicStatus in ('6')
THEN 'Program Hold'
WHEN tsd1.AcademicStatus in ('7')
THEN 'Graduated'
WHEN tsd1.AcademicStatus in ('8')
THEN 'Deceased'
WHEN tsd1.AcademicStatus in ('9')
THEN 'Applicant'
WHEN tsd1.AcademicStatus in ('11')
THEN ''
WHEN tsd1.AcademicStatus in ('12')
THEN 'REGISTRAR HOLD'
ELSE ''
END FullNameAndStatus
from APUS_PAD_LINKED.apus_pad.dbo.tblStudentDegree tsd1 (NOLOCK)
inner join APUS_PAD_LINKED.apus_pad.dbo.tblStudent tbs (NOLOCK) ON tsd1.StudentID = tbs.StudentID
inner join APUS_PAD_LINKED.apus_pad.dbo.vw_Crystal_CurrentPrimaryStudentDegree vsd WITH (NOLOCK) ON tsd1.StudentDegreeID = vsd.StudentDegreeID
where tsd1.StudentID = @student)
UPDATE @tblFinalLedger --Added to set SemStart to '1-1-2099' to sort by semester properly, MSO, 09/27/2008
SET SemStart = '1-1-2099'
WHERE HM2503Sem is null
UPDATE @tblFinalLedger --Added update statement to set CURTRXAM to 0 if VoidSTTS is 1(void), MSO, 09/27/2008
SET CURTRXAM = 0
WHERE VoidSTTS = 1
UPDATE FinLedOrig --Added update statement to update the Semester-orphaned WD and DP trx, MSO, 09/30/2008
SET HM2503Sem = FinLedNumeric.HM2503Sem,
TitleIVDescription = FinLedNumeric.HM2503Sem
FROM @tblFinalLedger AS FinLedOrig
INNER JOIN
( SELECT DOCNUMBR, HM2503Sem FROM @tblFinalLedger WHERE IsNumeric(DOCNUMBR) = 1 AND TitleIVDescription IS NOT NULL ) AS FinLedNumeric
ON FinLedOrig.NEWDOCNUM = FinLedNumeric.DOCNUMBR
WHERE IsNumeric(FinLedOrig.DOCNUMBR) = 0
AND TitleIVDescription IS NULL
delete from @tblLedger
FETCH NEXT FROM curStudentLedger
INTO @sid
END --cursor
CLOSE curStudentLedger
DEALLOCATE curStudentLedger
END
ELSE
--single StudentLedger
--==================================================
BEGIN
set @Student=@StudentID
INSERT @tblLedger
SELECT DISTINCT r.CUSTNMBR
, RTRIM(r.DOCNUMBR) AS DocNum
,CASE WHEN LEFT(RTRIM(r.DOCNUMBR),2) IN ('DP','WD')
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN LEFT(RTRIM(r.DOCNUMBR),3) like 'CP%'
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN (LEFT(RTRIM(r.DOCNUMBR),3) LIKE 'CR%' AND r.RMDTYPAL <> 7)
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN LEFT(RTRIM(r.DOCNUMBR),5) = 'CREDT'
THEN CASE WHEN LEFT((SELECT TOP 1 APTODCNM
FROM RM20201 WITH (NOLOCK)
WHERE APFRDCNM = r.DOCNUMBR),2) = 'WD'
THEN RIGHT(RTRIM((SELECT TOP 1 APTODCNM
FROM RM20201 WITH (NOLOCK)
WHERE APFRDCNM = r.DOCNUMBR)),7)
ELSE (SELECT TOP 1 APTODCNM
FROM RM20201 WITH (NOLOCK)
WHERE APFRDCNM = r.DOCNUMBR)
END
ELSE RTRIM(r.DOCNUMBR)
END AS NewDocNum
,r.DOCDATE
,r.ORTRXAMT
,r.CURTRXAM
,r.RMDTYPAL
,NULL
,NULL
,NULL
,CASE WHEN r.VOIDSTTS <> 0--modified hard coded string 'Posted' to reflect the true RM Void Status, MSO, 09/19/2008
THEN 'Void'
ELSE 'Posted'
END Status
,r.FRTSCHID
,r.MSCSCHID
,r.TRXDSCRN
,r.BACHNUMB
,sh.ITEMNMBR
,sh.ITEMDESC
,s.SOPTYPE
,s.USRDAT01
,s.USRDAT02
,s.USERDEF1
,s.USERDEF2
,RTRIM(s.USRDEF05)
,hm25.HM_Key_2 AS FundSrc25
,NULL
,NULL
,NULL
,hm2503.DEX_ROW_ID AS HM2503ID
,CASE WHEN LEFT(RTRIM(r.DOCNUMBR),5) = 'DEBIT'
THEN (SELECT CASE WHEN LEFT(RTRIM(RM20201.APFRDCNM),4) = ('PYMT')
THEN (SELECT HM02503.DSCRIPTN
FROM HM02500 WITH (NOLOCK)
INNER JOIN HM02503 WITH (NOLOCK) ON HM02500.HM_Key_3 = HM02503.HM_Key_3
WHERE HM02500.DOCNUMBR = RTRIM(RM20201.APFRDCNM))END
FROM RM20101 WITH (NOLOCK)
JOIN RM20201 WITH (NOLOCK) ON RM20101.DOCNUMBR = RM20201.APTODCNM
WHERE RMDTYPAL = '3'
AND RM20101.CUSTNMBR =@student
AND RM20101.DOCNUMBR = r.DOCNUMBR)
ELSE hm2503.DSCRIPTN
END AS HM2503Desc,
--hm2503.DSCRIPTN AS HM2503Desc,
NULL
,NULL
,r.VOIDSTTS
FROM SOP10106 s WITH (NOLOCK)
RIGHT OUTER JOIN SOP30300 sh WITH (NOLOCK)
RIGHT OUTER JOIN HM02503 hm2503 WITH (NOLOCK)
INNER JOIN HM02500 hm25 WITH (NOLOCK) ON hm2503.HM_Key_3 = hm25.HM_Key_3
RIGHT OUTER JOIN RM20101 r WITH (NOLOCK) ON hm25.DOCNUMBR = r.DOCNUMBR ON sh.SOPNUMBE = r.DOCNUMBR ON s.SOPNUMBE = r.DOCNUMBR
WHERE r.CUSTNMBR = @student
AND (NOT r.RMDTYPAL IN (99,3,9) OR r.RMDTYPAL IS NULL)
AND (LEFT(sh.ITEMNMBR,2) <> 'TA' OR sh.ITEMNMBR IS NULL)
--DEBITS
DECLARE @custnmbr1 varchar(15)
,@docnumbr1 varchar(20)
,@newdocnum1 varchar(20)
DECLARE dbts_cursor CURSOR FOR
SELECT DISTINCT RM20101.CUSTNMBR
,RTRIM(RM20101.DOCNUMBR) as DOCNUMBR
,MAX(CASE WHEN LEFT(RTRIM(RM20201.APFRDCNM),2) IN ('WD','DP')
THEN RIGHT(RTRIM(rm20201.APFRDCNM),7)
WHEN LEFT(RTRIM(RM20201.APFRDCNM),3) like 'CP%' --modified hard coded string 'posted'
THEN RIGHT(RTRIM(RM20201.APFRDCNM),7) --to populate status column on the report based on the SO Number
WHEN (LEFT(RTRIM(RM20201.APFRDCNM),3) LIKE 'CR%' AND RM20201.APFRDCTY <> 7)
THEN RIGHT(RTRIM(RM20201.APFRDCNM),7)
ELSE RM20201.APFRDCNM
END) as NEWDOCNUM
FROM RM20101 WITH (NOLOCK)
LEFT OUTER JOIN RM20201 WITH (NOLOCK) ON RM20101.DOCNUMBR = RM20201.APTODCNM
WHERE RMDTYPAL = '3'
AND RM20101.CUSTNMBR = @student
GROUP BY RM20101.CUSTNMBR, RTRIM(RM20101.DOCNUMBR)
OPEN dbts_cursor
FETCH NEXT FROM dbts_cursor
INTO @custnmbr1, @docnumbr1, @newdocnum1
WHILE @@fetch_status = 0
BEGIN
IF exists (SELECT DISTINCT custnmbr
FROM HM02500A WITH (NOLOCK)
INNER JOIN RM20101 WITH (NOLOCK) ON HM02500A.DOCNUMBR = RM20101.DOCNUMBR
WHERE CUSTNMBR = @student
AND HM_String_10 = @docnumbr1) --HM_String_10 LIKE '%debit%')
BEGIN
INSERT @tblLedger
SELECT b.CUSTNMBR
,RTRIM(b.APTODCNM)
,MAX(CASE WHEN LEFT(RTRIM(b.APTODCNM),2) in ('DP','WD')
THEN RIGHT(RTRIM(b.APTODCNM),7)
WHEN LEFT(RTRIM(b.APTODCNM),3) like 'CP%'
THEN RIGHT(RTRIM(b.APTODCNM),7)
WHEN (LEFT(RTRIM(b.APTODCNM),3) LIKE 'CR%' AND b.APTODCTY <> 7)
THEN RIGHT(RTRIM(b.APTODCNM),7)
WHEN LEFT(RTRIM(b.APTODCNM),5) = 'DEBIT'
THEN RIGHT(RTRIM(b.APFRDCNM),7)
ELSE RTRIM(b.APTODCNM)
END)
--b.APTODCDT, NULL, NULL, b.APTODCTY, b.APFRDCDT, hm.hm_currency_1 as RAmount
,b.APTODCDT
,r.ORTRXAMT
,r.CURTRXAM
,b.APTODCTY
,b.APFRDCDT
,hm.hm_currency_1 as RAmount
,MAX(b.APFRDCNM)
,CASE WHEN r.VoidSTTS <> 0--modified hard coded string 'Posted' to reflect the true RM Void Status, MSO, 09/19/2008
THEN 'Void'
ELSE 'Posted'
END Status
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,hm.HM_Key_2 AS FundSrc25
,NULL
,NULL
,NULL
,HM02503.DEX_ROW_ID AS HM2503ID
,RTRIM(HM02503.DSCRIPTN) AS HM2503Desc
,d.APFRDCNM AS CheckNum
,c.DOCDATE AS CheckDT
,r.VoidSTTS
FROM HM02500 hm2 WITH (NOLOCK)
INNER JOIN HM02503 WITH (NOLOCK) ON hm2.HM_Key_3 = HM02503.HM_Key_3
RIGHT OUTER JOIN HM02500A hm WITH (NOLOCK)
INNER JOIN RM20201 b WITH (NOLOCK) ON hm.DOCNUMBR = b.APFRDCNM AND hm.HM_String_10 = b.APTODCNM
--RM20201 b WITH (NOLOCK) ON hm.DOCNUMBR = b.APFRDCNM Changed 8/07/07 to fix the duplicate entry problem -- Liz
INNER JOIN RM20101 r WITH (NOLOCK) ON b.APTODCNM = r.DOCNUMBR -- Added join to RM20101 on 8/09/07 to fix Open Amount problem -- Liz
--Converted to LEFT JOINs to display all DEBIT trx, MSO, 09/22/2008
LEFT JOIN PM30200 c WITH (NOLOCK) ON b.APTODCNM = RIGHT(RTRIM(c.TRXDSCRN), 13)
LEFT JOIN PM30300 d WITH (NOLOCK) ON c.DOCNUMBR = d.APTODCNM AND c.VENDORID = d.VENDORID ON hm2.DOCNUMBR = hm.DOCNUMBR
WHERE b.CUSTNMBR = @student
AND (hm.HM_String_10 = @docnumbr1) --LIKE '%debit%')
--GROUP BY b.CUSTNMBR, RTRIM(b.APTODCNM), b.APTODCDT, b.APTODCTY, b.APFRDCDT, hm.hm_currency_1,
GROUP BY b.CUSTNMBR, RTRIM(b.APTODCNM), b.APTODCDT, r.ORTRXAMT, r.CURTRXAM, b.APTODCTY, b.APFRDCDT, hm.hm_currency_1,
hm.HM_Key_2, HM02503.DEX_ROW_ID, RTRIM(HM02503.DSCRIPTN), d.APFRDCNM, c.DOCDATE, r.VoidSTTS, b.APTODCNM
END
ELSE
BEGIN
INSERT @tblLedger
SELECT DISTINCT @custnmbr1
,@docnumbr1
,@newdocnum1
,r.DOCDATE
,r.ORTRXAMT
,r.CURTRXAM
,r.RMDTYPAL
,NULL
,NULL
,NULL
,CASE WHEN r.VOIDSTTS <> 0--modified hard coded string 'Posted' to reflect the true RM Void Status, MSO, 09/19/2008
THEN 'Void'
ELSE 'Posted'
END Status
,r.FRTSCHID
,r.MSCSCHID
,r.TRXDSCRN
,r.BACHNUMB
,sh.ITEMNMBR
,sh.ITEMDESC
,s.SOPTYPE
,s.USRDAT01
,s.USRDAT02
,s.USERDEF1
,s.USERDEF2
,RTRIM(s.USRDEF05)
,hm25.HM_Key_2 AS FundSrc25
,NULL
,NULL
,NULL
,hm2503.DEX_ROW_ID AS HM2503ID
,CASE WHEN LEFT(RTRIM(@newdocnum1),4) = ('PYMT')
THEN (SELECT HM02503.DSCRIPTN
FROM HM02500 WITH (NOLOCK)
INNER JOIN HM02503 WITH (NOLOCK) ON HM02500.HM_Key_3 = HM02503.HM_Key_3
WHERE HM02500.DOCNUMBR = @newdocnum1)
ELSE hm2503.DSCRIPTN
END AS HM2503Desc
,NULL
,NULL
,r.VOIDSTTS
FROM SOP10106 s WITH (NOLOCK)
RIGHT OUTER JOIN SOP30300 sh WITH (NOLOCK)
RIGHT OUTER JOIN HM02503 hm2503 WITH (NOLOCK)
INNER JOIN HM02500 hm25 WITH (NOLOCK) ON hm2503.HM_Key_3 = hm25.HM_Key_3
RIGHT OUTER JOIN RM20101 r WITH (NOLOCK) ON hm25.DOCNUMBR = r.DOCNUMBR ON sh.SOPNUMBE = r.DOCNUMBR ON s.SOPNUMBE = r.DOCNUMBR --RIGHT OUTER JOIN @tblDEBIT db ON r.DOCNUMBR = db.DOCNUMBR
WHERE r.CUSTNMBR = @student and r.DOCNUMBR = @docnumbr1
END
FETCH NEXT FROM dbts_cursor
INTO @custnmbr1
,@docnumbr1
,@newdocnum1
END
CLOSE dbts_cursor
DEALLOCATE dbts_cursor
--PAYMENTS
declare @tblPMENT1 table
(
CUSTNMBRvarchar(15),
DOCNUMBR varchar(22),
NEWDOCNUMvarchar(22)
)
INSERT @tblPMENT1
SELECT r.CUSTNMBR
,RTRIM(r.DOCNUMBR)--(SELECT TOP 1 APTODCNM FROM RM20201 WITH (NOLOCK) WHERE CUSTNMBR = @student and RTRIM(APFRDCNM) = RTRIM(r.DOCNUMBR))
,CASE WHEN (SELECT TOP 1 LEFT(RTRIM(APTODCNM),3)
FROM RM20201 WITH (NOLOCK)
WHERE CUSTNMBR = @student and RTRIM(APFRDCNM) = RTRIM(r.DOCNUMBR)) = 'WDF'
THEN (SELECT TOP 1 RIGHT(RTRIM(APTODCNM),7)
FROM RM20201 WITH (NOLOCK)
WHERE CUSTNMBR = @student and RTRIM(APFRDCNM) = RTRIM(r.DOCNUMBR))
ELSE (SELECT TOP 1 CASE WHEN LEFT(RTRIM(APTODCNM),3) like 'CP%'
THEN RIGHT(RTRIM(APTODCNM),7)
WHEN (LEFT(RTRIM(APTODCNM),3) LIKE 'CR%' AND APTODCNM <> 7)
THEN RIGHT(RTRIM(APTODCNM),7)
ELSE APTODCNM
END APTODCNM
FROM RM20201 WITH (NOLOCK)
WHERE CUSTNMBR = @student and RTRIM(APFRDCNM) = RTRIM(r.DOCNUMBR))
END
FROM SOP10106 s WITH (NOLOCK)
RIGHT OUTER JOIN SOP30300 sh WITH (NOLOCK)
RIGHT OUTER JOIN HM02503 hm2503 WITH (NOLOCK)
INNER JOIN HM02500 hm25 WITH (NOLOCK) ON hm2503.HM_Key_3 = hm25.HM_Key_3
RIGHT OUTER JOIN RM20101 r WITH (NOLOCK) ON hm25.DOCNUMBR = r.DOCNUMBR ON sh.SOPNUMBE = r.DOCNUMBR ON s.SOPNUMBE = r.DOCNUMBR
WHERE r.RMDTYPAL = '9'
AND r.CUSTNMBR = @student
INSERT @tblLedger
SELECT DISTINCT pm.CUSTNMBR
,pm.DOCNUMBR
,pm.NEWDOCNUM
,r.DOCDATE
,r.ORTRXAMT
,r.CURTRXAM
,r.RMDTYPAL
,NULL
,NULL
,NULL
,CASE WHEN r.VOIDSTTS <> 0 --modified hard coded string 'Posted' to reflect the true RM Void Status, MSO, 09/19/2008
THEN 'Void'
ELSE 'Posted'
END Status
,r.FRTSCHID
,r.MSCSCHID
,r.TRXDSCRN
,r.BACHNUMB
,sh.ITEMNMBR
,sh.ITEMDESC
,s.SOPTYPE
,s.USRDAT01
,s.USRDAT02
,s.USERDEF1
,s.USERDEF2
,RTRIM(s.USRDEF05)
,hm25.HM_Key_2 AS FundSrc25
,NULL
,NULL
,NULL
,hm2503.DEX_ROW_ID AS HM2503ID
,CASE WHEN LEFT(RTRIM(pm.DOCNUMBR),4) = ('PYMT')
THEN (SELECT DISTINCT HM02503.DSCRIPTN
FROM HM02500 WITH (NOLOCK)
INNER JOIN HM02503 WITH (NOLOCK) ON HM02500.HM_Key_3 = HM02503.HM_Key_3
WHERE HM02500.DOCNUMBR = pm.DOCNUMBR)
ELSE hm2503.DSCRIPTN
END AS HM2503Desc
,NULL
,NULL
,r.VOIDSTTS
FROM SOP10106 s WITH (NOLOCK)
RIGHT OUTER JOIN SOP30300 sh WITH (NOLOCK)
RIGHT OUTER JOIN HM02503 hm2503 WITH (NOLOCK)
INNER JOIN HM02500 hm25 WITH (NOLOCK) ON hm2503.HM_Key_3 = hm25.HM_Key_3
RIGHT OUTER JOIN RM20101 r WITH (NOLOCK) ON hm25.DOCNUMBR = r.DOCNUMBR ON sh.SOPNUMBE = r.DOCNUMBR ON s.SOPNUMBE = r.DOCNUMBR
RIGHT OUTER JOIN @tblPMENT1 pm ON r.DOCNUMBR = pm.DOCNUMBR
WHERE r.CUSTNMBR = @student
--Receivables?
INSERT @tblLedger
SELECT r.CUSTNMBR
,RTRIM(r.APTODCNM)
,CASE WHEN LEFT(RTRIM(r.APTODCNM),2) in ('DP','WD')
THEN RIGHT(RTRIM(r.APTODCNM),7) -- WHEN LEFT(RTRIM(r.APTODCNM),5) = 'DEBIT' THEN RTRIM(r.APFRDCNM)
WHEN LEFT(RTRIM(r.APTODCNM),3) like 'CP%'
THEN RIGHT(RTRIM(r.APTODCNM),7)
WHEN (LEFT(RTRIM(r.APTODCNM),3) LIKE 'CR%' AND r.APFRDCTY <> 7)
THEN RIGHT(RTRIM(r.APTODCNM),7)
ELSE RTRIM(r.APTODCNM)
END
,r.APTODCDT
,NULL
,NULL
,NULL
,r.APFRDCDT
,r.ActualApplyToAmount
,r.APFRDCNM
,'Posted' Status
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,CASE WHEN LEFT(RTRIM(r.APFRDCNM),4) = ('PYMT')
THEN (SELECT HM02503.DSCRIPTN
FROM HM02500 WITH (NOLOCK)
INNER JOIN HM02503 WITH (NOLOCK) ON HM02500.HM_Key_3 = HM02503.HM_Key_3
WHERE HM02500.DOCNUMBR = r.APFRDCNM)
END AS HM2503Desc
,NULL
,NULL
,NULL
FROM dbo.RM20201 r WITH (NOLOCK)
WHERE r.CUSTNMBR = @student
AND r.APTODCTY <> '3'
INSERT @tblLedger
SELECT r.CUSTNMBR
,rtrim(r.DOCNUMBR)--r.APFRDCNM,
,CASE WHEN LEFT(RTRIM(r.DOCNUMBR),2) in ('DP','WD')
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN LEFT(RTRIM(r.DOCNUMBR),3) like 'CP%'
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN (LEFT(RTRIM(r.DOCNUMBR),3) LIKE 'CR%' AND r.RMDTYPAL <> 7)
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
ELSE RTRIM(r.DOCNUMBR) END
,r.DOCDATE
,r.DOCAMNT
,r.ACCTAMNT
,r.RMDTYPAL
,null
,null
,null
,'Unposted' Status
,null
,null
,r.DOCDESCR
,r.BACHNUMB
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
FROM rm10301 r WITH (NOLOCK)
WHERE r.custnmbr = @student
--Payables - per Chris Donohoe on 10/3/06
-- UNPOSTED BATCHES
INSERT @tblLedger
SELECT r.CUSTNMBR
,RTRIM(r.SOPNUMBE)
,CASE WHEN LEFT(RTRIM(r.SOPNUMBE),2) IN ('DP','WD')
THEN RIGHT(RTRIM(r.SOPNUMBE),7)
WHEN LEFT(RTRIM(r.SOPNUMBE),3) like 'CP%'
THEN RIGHT(RTRIM(r.SOPNUMBE),7)
WHEN (LEFT(RTRIM(r.SOPNUMBE),3) LIKE 'CR%' AND r.DOCID LIKE 'RTN%')
THEN RIGHT(RTRIM(r.SOPNUMBE),7)
ELSE RTRIM(r.SOPNUMBE)
END
,r.DOCDATE
,r.DOCAMNT
,r.ACCTAMNT
,CASE r.DOCID
WHEN 'STDINV' THEN 1
WHEN 'RTN' THEN 8
end
,null
,null
,null
,CASE WHEN (r.VOIDSTTS <> 0)--modified hard coded string 'Unposted' to reflect the true RM Void Status, MSO, 09/19/2008
THEN 'Void'
ELSE 'Unposted'
END Status
,null
,null
,null
,r.BACHNUMB
,sh.ITEMNMBR
,sh.ITEMDESC
,s.SOPTYPE
,s.USRDAT01
,s.USRDAT02
,s.USERDEF1
,s.USERDEF2
,s.USRDEF05
,null
,null
,null
,null
,null
,null
,null
,null
,r.VOIDSTTS
FROM sop10100 r WITH (NOLOCK)
LEFT JOIN SOP10200 sh WITH (NOLOCK) ON (sh.SOPNUMBE = r.SOPNUMBE)
LEFT JOIN sop10106 s WITH (NOLOCK) ON (s.sopnumbe = r.SOPNUMBE)
WHERE r.custnmbr = @student
AND (left(sh.ITEMNMBR,2) <> 'TA' OR sh.ITEMNMBR IS NULL)
--Open transactions - DB 9/5/07
INSERT @tblLedger
SELECT DISTINCT p.VENDORID
,rtrim(r.DOCNUMBR)
,CASE WHEN LEFT(RTRIM(r.DOCNUMBR),2) IN ('DP','WD')
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN LEFT(RTRIM(r.DOCNUMBR),3) like 'CP%'
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN (LEFT(RTRIM(r.DOCNUMBR),3) LIKE 'CR%' AND r.RMDTYPAL <> 7)
THEN RIGHT(RTRIM(r.DOCNUMBR),7)
WHEN LEFT(RTRIM(r.DOCNUMBR),5) = 'DEBIT'
THEN RIGHT(RTRIM(b.APFRDCNM),7)
ELSE RTRIM(r.DOCNUMBR)
END
,r.DOCDATE
,p.DOCAMNT
,null
,p.DOCTYPE
,null
,null
,null
,CASE WHEN (r.VOIDSTTS <> 0)----modified hard coded string 'Open' to reflect the true RM Void Status, MSO, 09/19/2008
THEN 'Void'
ELSE 'Open'
END Status
,null
,null
,r.TRXDSCRN
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
,HM02503.DEX_ROW_ID AS HM2503ID
,RTRIM(HM02503.DSCRIPTN) AS HM2503Desc
,null
,null
,0
--FROMHM02500 hm2 WITH (NOLOCK) INNER JOIN
--HM02503 WITH (NOLOCK) ON hm2.HM_Key_3 = HM02503.HM_Key_3 RIGHT OUTER JOIN
--HM02500A hm WITH (NOLOCK) INNER JOIN
-- RM20201 b WITH (NOLOCK) ON hm.DOCNUMBR = b.APFRDCNM AND hm.HM_String_10 = b.APTODCNM INNER JOIN
-- RM20101 r WITH (NOLOCK) ON b.APTODCNM = r.DOCNUMBR ON hm2.DOCNUMBR = hm.DOCNUMBR INNER JOIN
--PM20000 p WITH (NOLOCK) ON p.VENDORID = r.CUSTNMBR AND RIGHT(RTRIM(p.TRXDSCRN), 13) = r.DOCNUMBR
FROM HM02500 hm2 WITH (NOLOCK)
INNER JOIN HM02503 WITH (NOLOCK) ON hm2.HM_Key_3 = HM02503.HM_Key_3
RIGHT OUTER JOIN HM02500A hm WITH (NOLOCK)
INNER JOIN RM20201 b WITH (NOLOCK) ON hm.DOCNUMBR = b.APFRDCNM AND hm.HM_String_10 = b.APTODCNM
INNER JOIN RM20101 r WITH (NOLOCK) ON b.APTODCNM = r.DOCNUMBR ON hm2.DOCNUMBR = hm.DOCNUMBR
LEFT OUTER JOIN PM20000 p WITH (NOLOCK) ON r.CUSTNMBR = p.VENDORID AND RTRIM(LTRIM(SUBSTRING(r.TRXDSCRN, 11, 35))) = p.VCHRNMBR
WHERE (r.RMDTYPAL = 3)
AND p.VENDORID = @student
INSERT INTO @tblFinalLedger
SELECT * from @tblLedger
--Appended to combine the two SPs
LEFT JOIN (SELECT tsd.StudentID StudentID
,convert(varchar(15),tsd.StudentID) + ', ' + convert(varchar(35),ts.FirstName + ' ' + ts.LastName + ', ' + (CASE WHEN tsd.AcademicStatus in ('1')
THEN 'Active'
WHEN tsd.AcademicStatus in ('2')
THEN 'Disenrolled'
WHEN tsd.AcademicStatus in ('3')
THEN 'Suspended'
WHEN tsd.AcademicStatus in ('5')
THEN 'Expelled'
WHEN tsd.AcademicStatus in ('6')
THEN 'Program Hold'
WHEN tsd.AcademicStatus in ('7')
THEN 'Graduated'
WHEN tsd.AcademicStatus in ('8')
THEN 'Deceased'
WHEN tsd.AcademicStatus in ('9')
THEN 'Applicant'
WHEN tsd.AcademicStatus in ('11')
THEN ''
WHEN tsd.AcademicStatus in ('12')
THEN 'REGISTRAR HOLD'
ELSE ''
END))FullNameAndStatus
,tr.RegistrationID RegID
,tsd.StudentDegreeID
,convert(varchar(35),tsem2.TitleIVDescription)TitleIVDescription
,tsem2.SemesterStart SemStart
--please update the APUS_PAD_LINKED.apus_pad datasource to APUS_PAD_LINKED.apus_pad_datafix before executing this query in any datafix environment!!
FROM APUS_PAD_LINKED.apus_pad.dbo.tlkpAcademicStatus tac WITH (NOLOCK)
RIGHT OUTER JOIN APUS_PAD_LINKED.apus_pad.dbo.tlkpSemester tsem2 (NOLOCK)
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tblStudent ts WITH (NOLOCK)
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tblRegistration tr(NOLOCK) ON ts.StudentID = tr.StudentID
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tblCoursesOffered tco(NOLOCK) ON tr.CourseOfferedID = tco.CourseOfferedID
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tlkpSemesterRelationship tsemr2 WITH (NOLOCK)
ON tco.SemesterID = tsemr2.ChildSemesterID
ON tsem2.SemesterID = tsemr2.ParentSemesterID
ON tac.AcademicStatusID = ts.CurrentAcademicStatusID
RIGHT OUTER JOIN APUS_PAD_LINKED.apus_pad.dbo.tblAcademicYear tay WITH (NOLOCK)
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tlkpSemester tsem1 WITH (NOLOCK) ON tay.SemesterID = tsem1.SemesterID
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tlkpSemesterRelationship tsemr1 WITH (NOLOCK)
ON tsem1.SemesterID = tsemr1.ParentSemesterID
ON tsem2.SemesterID = tsemr1.ChildSemesterID AND ts.StudentID = tay.StudentID
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tblStudentDegree tsd WITH (NOLOCK) ON ts.StudentID = tsd.StudentID
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.vw_Crystal_CurrentPrimaryStudentDegree vsd WITH (NOLOCK) ON tsd.StudentDegreeID = vsd.StudentDegreeID --Added view to show only current degrees
WHERE (tay.DateCancelled IS NULL)
AND (NOT (tr.RegistrationID IS NULL))
AND tsd.StudentID = @student)padtbl ON "@tblLedger".CUSTNMBR = padtbl.StudentID AND "@tblLedger".NEWDOCNUM = CONVERT(VARCHAR(12),padtbl.RegID)
UPDATE @tblFinalLedger
SET HM2503Sem = TitleIVDescription
WHERE HM2503Sem is null
and TitleIVDescription is not null
UPDATE FinLed
SET TitleIVDescription = HM2503Sem,
SemStart = Sem.SemesterStart
FROM @tblFinalLedger AS FinLed
INNER JOIN APUS_PAD_LINKED.apus_pad.dbo.tlkpSemester Sem
ON LTRIM(RTRIM(FinLed.HM2503Sem)) = LTRIM(RTRIM(Sem.TitleIVDescription))
WHERE HM2503Sem is not null
and FinLed.TitleIVDescription is null
UPDATE A
SET HM2503Sem = B.HM2503Sem,
TitleIVDescription = B.HM2503Sem,
SemStart = B.SemStart
FROM @tblFinalLedger AS A
INNER JOIN
( SELECT DOCNUMBR, HM2503Sem, SemStart FROM @tblFinalLedger WHERE HM2503Sem IS NOT NULL) B
ON A.DOCNUMBR = B.DOCNUMBR
WHERE A.HM2503Sem IS NULL
UPDATE @tblFinalLedger --Added to display student name and status on each page of report, MSO, 09/27/2008
SET FullNameAndStatus = (select CASE WHEN tsd1.AcademicStatus in ('1')
THEN 'Active'
WHEN tsd1.AcademicStatus in ('2')
THEN 'Disenrolled'
WHEN tsd1.AcademicStatus in ('3')
THEN 'Suspended'
WHEN tsd1.AcademicStatus in ('5')
THEN 'Expelled'
WHEN tsd1.AcademicStatus in ('6')
THEN 'Program Hold'
WHEN tsd1.AcademicStatus in ('7')
THEN 'Graduated'
WHEN tsd1.AcademicStatus in ('8')
THEN 'Deceased'
WHEN tsd1.AcademicStatus in ('9')
THEN 'Applicant'
WHEN tsd1.AcademicStatus in ('11')
THEN ''
WHEN tsd1.AcademicStatus in ('12')
THEN 'REGISTRAR HOLD'
ELSE ''
END FullNameAndStatus
from APUS_PAD_LINKED.apus_pad.dbo.tblStudentDegree tsd1 (NOLOCK)
inner join APUS_PAD_LINKED.apus_pad.dbo.tblStudent tbs (NOLOCK) ON tsd1.StudentID = tbs.StudentID
inner join APUS_PAD_LINKED.apus_pad.dbo.vw_Crystal_CurrentPrimaryStudentDegree vsd WITH (NOLOCK) ON tsd1.StudentDegreeID = vsd.StudentDegreeID
where tsd1.StudentID = @student)
UPDATE @tblFinalLedger --Added to set SemStart to '1-1-2099' to sort by semester properly, MSO, 09/27/2008
SET SemStart = '1-1-2099'
WHERE HM2503Sem is null
UPDATE @tblFinalLedger --Added update statement to set CURTRXAM to 0 if VoidSTTS is 1(void), MSO, 09/27/2008
SET CURTRXAM = 0
WHERE VoidSTTS = 1
UPDATE FinLedOrig --Added update statement to update the Semester-orphaned WD and DP trx, MSO, 09/30/2008
SET HM2503Sem = FinLedNumeric.HM2503Sem,
TitleIVDescription = FinLedNumeric.HM2503Sem
FROM @tblFinalLedger AS FinLedOrig
INNER JOIN
( SELECT DOCNUMBR, HM2503Sem FROM @tblFinalLedger WHERE IsNumeric(DOCNUMBR) = 1 AND TitleIVDescription IS NOT NULL ) AS FinLedNumeric
ON FinLedOrig.NEWDOCNUM = FinLedNumeric.DOCNUMBR
END
SELECT tfl.*,rmm.CUSTNAME
INTO #fsaleg
FROM @tblFinalLedger tfl
INNER JOIN (SELECT CUSTNMBR,CUSTNAME FROM RM00101(NOLOCK))rmm ON tfl.CUSTNMBR = rmm.CUSTNMBR
SELECT DISTINCT *
FROM #fsaleg
create clustered index temp_ix_#fsaleg on #fsaleg (CUSTNMBR)
DROP TABLE #fsaleg
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXECUTE ON [dbo].[apus_sp_rpt_StudentLedger_FSA] TO [CrystalReports]
*************************************************************************
We had to separate them because it is a heavily used script and we could have multiple users running either of the reports at the same time.
Thanks,
Sam
February 18, 2009 at 3:04 pm
In the last part of the code, it's creating a clustered index on a temp table, then immediately dropping it. Why?
That is a nasty procedure. *I* would put debug statements in after each major statements that print a timestamp, then you should be able to tell what is taking a long time to execute. Some might analyze the execution plan or put some keepfixed plan options in there.
You should be able to execute the SP in a timely fashion before worrying about rendering a SSRS report with that data.
Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply