SQL Stored Procedure used for Financial Reporting extracts runs fine standalone, but when called by VB2010 fails intermittantly, at different locations (lines where failure occurs are highlighted below). When the @GLClass parameter results in less than 1000 records, the stored proc works just fine, but if the @GLClass parameter results in more, the stored proc fails and at random locations.
Code:
CREATE PROCEDURE [ids].[cst_Insert_GLOutput](
@GLRptID INT,
@GLSessionID INT,
@GLControlDesc VARCHAR(100),
@GLDivision INT = -1,
@GLClass INT,
@Company VARCHAR(1),
@GLItemDescr VARCHAR(253),
@GLLevel1 VARCHAR(100),
@GLLevel2 VARCHAR(100),
@GLLevel3 VARCHAR(100),
@GLLevel4 VARCHAR(100),
@GLLevel5 VARCHAR(100),
@GlLevel6 VARCHAR(100),
@GlLevel7 VARCHAR(100),
@GlLevel8 VARCHAR(100),
@GlLevel9 VARCHAR(100),
@GlLevel10 VARCHAR(100),
@GLItemAmount1 NUMERIC(12,2),
@GLItemAmount2 NUMERIC(12,2),
@GLItemAmount3 NUMERIC(12,2),
@GLItemAmount4 NUMERIC(12,2),
@GLItemAmount5 NUMERIC(12,2),
@GLItemAmount6 NUMERIC(12,2),
@GLItemAmount7 NUMERIC(12,2),
@GLItemAmount8 NUMERIC(12,2),
@GLRowCode VARCHAR(10),
@GLPrintCode VARCHAR(10),
@RevSign VARCHAR(3),
@GLFilter VARCHAR(253),
@GLFormula VARCHAR(253),
@GLItemInfo1 VARCHAR(50),
@GLItemInfo2 VARCHAR(50),
@GLItemDate VARCHAR(20),
@GLReport VARCHAR(1),
@GLAcctMonth NUMERIC(4),
@GLAcctYear VARCHAR(4),
@AccumRules1 VARCHAR(12),
@AccumRules2 VARCHAR(12),
@AccumRules3 VARCHAR(12),
@AccumRules4 VARCHAR(12),
@AccumRules5 VARCHAR(12),
@AccumRules6 VARCHAR(12),
@AccumRules7 VARCHAR(12),
@AccumRules8 VARCHAR(12),
@AccumRules9 VARCHAR(12),
@AccumRules10 VARCHAR(12)
)
AS
--============================================================================
-- Created By: Bruce Jenkins 08/24/2012
--============================================================================
-- III - 01/01/1900 - Change Comments
--============================================================================
DECLARE @NewId INT;
DECLARE @00 as numeric(10,2) = 0,
@00P as numeric(10,2) = 0,
@01 as numeric(10,2)= 0,
@01P as numeric(10,2)= 0,
@02 as numeric(10,2)= 0,
@02P as numeric(10,2)= 0,
@03 as numeric(10,2)= 0,
@03P as numeric(10,2)= 0,
@04 as numeric(10,2)= 0,
@04P as numeric(10,2)= 0,
@05 as numeric(10,2)= 0,
@05P as numeric(10,2)= 0,
@06 as numeric(10,2)= 0,
@06P as numeric(10,2)= 0,
@07 as numeric(10,2)= 0,
@07P as numeric(10,2)= 0,
@08 as numeric(10,2)= 0,
@08P as numeric(10,2)= 0,
@09 as numeric(10,2)= 0,
@09P as numeric(10,2)= 0,
@10 as numeric(10,2)= 0,
@10P as numeric(10,2)= 0,
@11 as numeric(10,2)= 0,
@11P as numeric(10,2)= 0,
@12 as numeric(10,2)= 0,
@12P as numeric(10,2)= 0,
@Year as VARCHAR(4) = '',
@Div as int = 0;
DECLARE @SubDivision as VARCHAR(30) = '';
DECLARE @DIVName as VARCHAR(50) = '';
DECLARE @GLItemAcct8 as VARCHAR(8)= '';
DECLARE @GLTRNAmount as numeric(12,2);
DECLARE @GLTRNPrevAmount as numeric(12,2);
DECLARE @GLTRNMonth as Numeric(4);
DECLARE @GLTRNDate as VARCHAR(20);
DECLARE @GLTRNDesc as VARCHAR(50);
DECLARE @USETRANMONTH1 as BIT= 0;
DECLARE @USETRANMONTH2 as BIT= 0;
DECLARE @USETRANMONTH3 as BIT= 0;
DECLARE @USETRANMONTH4 as BIT= 0;
DECLARE @USETRANMONTH5 as BIT= 0;
DECLARE @USETRANMONTH6 as BIT= 0;
DECLARE @COMPAREMONTH as NUMERIC(2,0)
DECLARE @COMPAREMONTHSTR as VARCHAR(4)
DECLARE @COMPAREYEAR as VARCHAR(4)
DECLARE @GLTRNID as INT = 0
DECLARE @GLItemAmount9 as numeric(10,2) = 0.00
DECLARE @GLItemAmount10 as numeric(10,2) = 0.00
DECLARE @RPT as VARCHAR(1) = ''
DECLARE @CY as BIT = 'FALSE'
DECLARE @PY as BIT = 'FALSE'
DECLARE @CM as BIT = 'FALSE'
DECLARE @PM as BIT = 'FALSE'
DECLARE @MON as BIT = 'FALSE'
DECLARE @YTD as BIT = 'FALSE'
DECLARE @DET as BIT = 'FALSE'
DECLARE @SUM as BIT = 'FALSE'
DECLARE @CYOFFSET as INT = 0
DECLARE @CMOFFSET as INT = 0
DECLARE @SPECIFIEDMONTH as INT = 0
DECLARE @SPECIFIEDDIVISION as INT = -1
DECLARE @LOOP as INT = 1
DECLARE @VALIDACCTFETCH as INT = 1
DECLARE @WORKACCUM VARCHAR(12) = '000000000000'
DECLARE @HoldAcctYear VARCHAR(4)
DECLARE @HoldAcctMonth Numeric(4)
DECLARE @TRANTYPE VARCHAR(1)
DECLARE @GLITEMKey as VARCHAR(8) = ''
DECLARE @MGAC as VARCHAR(2) = ''
DECLARE @NBRAccounts as INT = 0
BEGIN TRY
SET @HoldAcctMonth = @GLAcctMonth
SET @HoldAcctYear = @GLAcctYear
CREATE TABLE #Temp0 (
mnth INT,
Balance NUMERIC(10,2),
PYBalance NUMERIC(10,2),
TranType VARCHAR(1),
Div INT
)
SELECT @NbrAccounts = COUNT(*)
FROM UC_Jan16_1700.dbo.tmpglmas gm
INNER JOIN
(SELECT [AcctNo] = x.GLAcctNo,
[Div] = x.Division,
[SDiv] = isnull(sd.SubDivName,'Unknown' + CONVERT(varchar(2),x.subdivision)),
[DivName]= isnull(dv.DivName,'Unknown' + convert(varchar(2),x.Division)),
[GLClass] = aa.glclass1ID
FROM ids.GLRptAssignAccts aa
LEFT JOIN ids.GLRptXref x on x.GLRptXrefID = aa.GLRptXrefID
LEFT JOIN ids.SubDivisions sd on x.SubDivision = sd.SubDivision
LEFT JOIN UC_Jan16_1700.ids.Divisions dv on dv.Division = x.division and dv.Division not in ('A','B','C','-')
WHERE aa.GLClass1ID = @GLCLass) ac ON ac.AcctNO = gm.glmas_acct_no
WHERE gm.glmas_glno = 1
GROUP by ac.glclass
DECLARE GLAcct Cursor for
Select Distinct [GL_Acct_8] = gm.glmas_acct_no,
[GLDivisionName] = ac.DivName,
[GLDivision] = ac.Div,
[SubDivision] = ac.SDiv,
[GL_Acct_Desc]= gm.glmas_desc,
[GLReport] = gm.glmas_Report,
[janbal] = gm.glmas_janbal, [janprv] = gm.glmas_janprv,
[febbal] = gm.glmas_febbal, [febprv] = gm.glmas_febprv,
[marbal] = gm.glmas_marbal, [marprv] = gm.glmas_marprv,
[aprbal] = gm.glmas_aprbal, [aprprv] = gm.glmas_aprprv,
[maybal] = gm.glmas_maybal, [mayprv] = gm.glmas_mayprv,
[junbal] = gm.glmas_junbal, [junprv] = gm.glmas_junprv,
[julbal] = gm.glmas_julbal, [julprv] = gm.glmas_julprv,
[augbal] = gm.glmas_augbal, [augprv] = gm.glmas_augprv,
[sepbal] = gm.glmas_sepbal, [sepprv] = gm.glmas_sepprv,
[octbal] = gm.glmas_octbal, [octprv] = gm.glmas_octprv,
[novbal] = gm.glmas_novbal, [novprv] = gm.glmas_novprv,
[decbal] = gm.glmas_decbal, [decprv] = gm.glmas_decprv
FROM UC_Jan16_1700.dbo.tmpglmas gm
INNER JOIN
(SELECT [AcctNo] = x.GLAcctNo,
[Div] = x.Division,
[SDiv] = isnull(sd.SubDivName,'Unknown' + CONVERT(varchar(2),x.subdivision)),
[DivName]= isnull(dv.DivName,'Unknown' + convert(varchar(2),x.Division))
FROM ids.GLRptAssignAccts aa
LEFT JOIN ids.GLRptXref x on x.GLRptXrefID = aa.GLRptXrefID
LEFT JOIN ids.SubDivisions sd on x.SubDivision = sd.SubDivision
LEFT JOIN UC_Jan16_1700.ids.Divisions dv on dv.Division = x.division and dv.Division not in ('A','B','C','-')
WHERE aa.GLClass1ID = @GLCLass) ac ON ac.AcctNO = gm.glmas_acct_no
WHERE gm.glmas_glno = @Company
order by gm.glmas_acct_no
OPEN GLAcct
FETCH NEXT FROM GLAcct INTO @GLItemAcct8, @DivName, @GLDivision, @SubDivision, @GLItemDescr, @GLReport,
@01, @01P, @02, @02P, @03, @03P, @04, @04P, @05, @05P,
@06, @06P, @07, @07P, @08, @08P, @09, @09P, @10, @10P,
@11, @11P, @12, @12P
IF @@FETCH_STATUS = 0
BEGIN
SET @ValidAcctFetch = 1
END
else
BEGIN
SET @ValidAcctFetch = 999999999
END
WHILE @VALIDACCTFETCH <= @NBRAccounts
BEGIN
[highlight #F57900] SET @00 = @12P
SET @GLITEMKey = @GLItemAcct8
DELETE from #Temp0
INSERT INTO #Temp0 VALUES
[/highlight] (0, @00,@00P, 0, @GLDivision)
,(1, @01,@01P, 0, @GLDivision)
,(2, @02,@02P, 0, @GLDivision)
,(3, @03,@03P, 0, @GLDivision)
,(4, @04,@04P, 0, @GLDivision)
,(5, @05,@05P, 0, @GLDivision)
,(6, @06,@06P, 0, @GLDivision)
,(7, @07,@07P, 0, @GLDivision)
,(8, @08,@08P, 0, @GLDivision)
,(9, @09,@09P, 0, @GLDivision)
,(10,@10,@10P, 0, @GLDivision)
,(11,@11,@11P, 0, @GLDivision)
,(12,@12,@12P, 0, @GLDivision)
WHILE @LOOP < 11
BEGIN
SET @RPT = @GLREPORT
SET @WORKACCUM =
case when @LOOP = 1 then @AccumRules1
when @LOOP = 2 then @AccumRules2
when @LOOP = 3 then @AccumRules3
when @LOOP = 4 then @AccumRules4
when @LOOP = 5 then @AccumRules5
When @LOOP = 6 then @AccumRules6
When @LOOP = 7 then @AccumRules7
when @LOOP = 8 then @AccumRules8
WHEN @LOOP = 9 then @AccumRules9
WHEN @LOOP = 10 then @AccumRules10 end
IF @WORKACCUM <> '000000000000'
--"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
--''' PROCESS ACCUMULATOR ONE
--"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
BEGIN
SET @CY = 'FALSE'
SET @PY = 'FALSE'
SET @CM = 'FALSE'
SET @PM = 'FALSE'
SET @MON = 'FALSE'
SET @YTD = 'FALSE'
SET @DET = 'FALSE'
set @SUM = 'FALSE'
set @MGAC = ''
SET @GLAcctMonth = @HoldAcctMonth
SET @GLAcctYear = @HoldAcctYear
IF SUBSTRING(@WORKACCUM,1,1) = '1'
BEGIN
SET @CY = 'TRUE'
END
IF SUBSTRING(@WORKACCUM,1,1) = '2'
BEGIN
SET @PY = 'TRUE'
END
IF SUBSTRING(@WORKACCUM,2,1) = '1'
BEGIN
SET @CM = 'TRUE'
END
IF SUBSTRING(@WORKACCUM,2,1) = '2'
BEGIN
SET @PM = 'TRUE'
END
IF SUBSTRING(@WORKACCUM,3,1) = '1'
BEGIN
SET @MON = 'TRUE'
END
IF SUBSTRING(@WORKACCUM,3,1) = '2'
BEGIN
SET @YTD = 'TRUE'
END
IF SUBSTRING(@WORKACCUM,4,1) = '1'
BEGIN
SET @DET = 'TRUE'
SET @SUM = 'FALSE'
--SET @RPT = 'I'
END
IF SUBSTRING(@WORKACCUM,4,1) = '2'
BEGIN
SET @SUM = 'TRUE'
SET @DET = 'FALSE'
END
IF substring(@GLItemAcct8,1,4) = '3051' -- Retained Earnings always monthly
BEGIN
SET @YTD = 'FALSE'
SET @MON = 'TRUE'
END
SET @MGAC = SUBSTRING(@WORKACCUM,6,1)
IF @MGAC = '1'
BEGIN
PRINT @MGAC
END
if SUBSTRING(@WORKACCUM,8,1) <> '0'
BEGIN
SET @SPECIFIEDMONTH =
Case SUBSTRING(@WORKACCUM,8,1)
when '1' then 1
when '2' then 2
when '3' then 3
when '4' then 4
when '5' then 5
when '6' then 6
when '7' then 7
when '8' then 8
when '9' then 9
when 'A' then 10
when 'B' then 11
when 'C' then 12
END
SET @GLAcctMonth = @SPECIFIEDMONTH
END
IF SUBSTRING(@WORKACCUM,10,1) = 'D' -- Division filter feature turned on
BEGIN
SET @SPECIFIEDDIVISION =
CASE SUBSTRING(@WORKACCUM,9,1)
WHEN '0' then 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
WHEN 'G' THEN 16
WHEN 'H' THEN 17
WHEN 'I' THEN 18
END
END
IF @PM = 'TRUE'
BEGIN
SET @GLAcctMonth = @GLAcctMonth - 1
if @GLAcctMonth = 0
BEGIN
Set @GLAcctMonth = 12
set @GLAcctYear = @GLAcctYear - 1
END
END
IF @PY = 'TRUE'
BEGIN
SET @GLAcctYear = @GLAcctYear - 1
END
if @MON = 'TRUE' and @SUM = 'FALSE' -- Month Transactions Only - Detail
BEGIN
if @GLAcctMonth = 1 and @PY = 'TRUE'
BEGIN
if @RPT = 'B'
BEGIN
DECLARE GLTrans cursor for
SELECT [GLActMonth] = case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
end,
[GLTrnAmount] = tt.PYBalance,
[GLTRNPrevAmount] = tt.PYBalance,
[TRANTYPE] = tt.TranType,
[GLDivision] = tt.Div,
[GlRefDesc] = 'Month Balance',
[GLTrnDate] = case When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
end,
[GLTrnID] = -1
FROM #Temp0 tt
WHERE tt.mnth = @GLAcctMonth
ORDER BY gltrndate
END
ELSE -- Report = I
BEGIN
DECLARE GLTrans Cursor for
SELECT [GLActMonth] = t.gltrn_act_mth,
[GLTrnAmount] = t.gltrn_amount,
[GLTRNPrevAmount] = 0,
[TRANTYPE] = 1,
[GLDivision] = x.division,
[GLRefDesc] = t.gltrn_ref_desc,
[GLTrnDate] = convert(varchar(20),t.gltrn_trandt,101),
[GLTrnID] = t.id_num
FROM UC_Jan16_1700.dbo.tmpgltrn t
Left Join UC_Jan16_1700.dbo.tmpglmas m on m.glmas_acct_no = t.gltrn_acct_no
left join ids.GLRptXref x on x.GLAcctNo = t.gltrn_acct_no
GROUP BY t.gltrn_act_mth, t.gltrn_amount, t.gltrn_ref_desc, t.gltrn_trandt, t.gltrn_ytdrec,
m.glmas_report, t.gltrn_acct_no, t.AcctMonth, t.acctyear, t.gltrn_glno, t.gltrn_upd, t.id_num, x.division
HAVING t.gltrn_acct_no = @GLItemAcct8 and
t.gltrn_amount <> 0 and
t.gltrn_ref_desc > '' and
t.AcctMonth = @GLAcctMonth and
t.AcctYear = @GLAcctYear and
t.gltrn_upd = 'Y' and
--t.gltrn_ytdrec = 'Y' and
t.gltrn_glno = 1 and
((x.division = @SPECIFIEDDIVISION) OR
(@SPECIFIEDDIVISION = -1))
ORDER BY gltrndate
END
END
ELSE
BEGIN
if @RPT = 'B'
BEGIN
DECLARE GLTrans Cursor for
SELECT [GLActMonth] = case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
end,
[GLTrnAmount] = tt.balance,
[GLTrnPrevAmount] = tt.PYBalance,
[TRANTYPE] = tt.TranType,
[GLDivision] = tt.Div,
[GlRefDesc] = 'Prior Period Balance',
[GLTrnDate] = case When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
end,
[GLTrnID] = -1
FROM #Temp0 tt
WHERE tt.mnth = @GLAcctMonth -1
UNION
(SELECT [GLActMonth] = t.gltrn_act_mth,
[GLTrnAmount] = t.gltrn_amount,
[GLTRNPrevAmount] = 0,
[TRANTYPE] = 1,
[GLDivision] = x.division,
[GLRefDesc] = t.gltrn_ref_desc,
[GLTrnDate] = convert(varchar(20),t.gltrn_trandt,101),
[GLTrnID] = t.id_num
FROM UC_Jan16_1700.dbo.tmpgltrn t
LEFT JOIN UC_Jan16_1700.dbo.tmpglmas m on m.glmas_acct_no = t.gltrn_acct_no
LEFT JOIN ids.GLRptXref x on x.GLAcctNo = t.gltrn_acct_no
GROUP BY t.gltrn_act_mth, t.gltrn_amount, t.gltrn_ref_desc, t.gltrn_trandt, t.gltrn_ytdrec,
m.glmas_report, t.gltrn_acct_no, t.AcctMonth, t.acctyear, t.gltrn_glno, t.gltrn_upd, t.id_num, x.division
HAVING t.gltrn_acct_no = @GLItemAcct8 and
t.AcctMonth = @GLAcctMonth and
t.AcctYear = @GLAcctYear and
t.gltrn_amount <> 0 and
t.gltrn_ref_desc > '' and
t.gltrn_upd = 'Y' and
--t.gltrn_ytdrec = 'Y' and
m.glmas_report = 'B' and
t.gltrn_glno = 1)
END
ELSE -- Report = I
BEGIN
DECLARE GLTrans Cursor for
SELECT [GLActMonth] = t.gltrn_act_mth,
[GLTrnAmount] = t.gltrn_amount,
[GLTRNPrevAmount] = 0,
[TRANTYPE] = 1,
[GLDivision] = x.division,
[GLRefDesc] = t.gltrn_ref_desc,
[GLTrnDate] = convert(varchar(20),t.gltrn_trandt,101),
[GLTrnID] = t.id_num
FROM UC_Jan16_1700.dbo.tmpgltrn t
Left Join UC_Jan16_1700.dbo.tmpglmas m on m.glmas_acct_no = t.gltrn_acct_no
left join ids.GLRptXref x on x.GLAcctNo = t.gltrn_acct_no
GROUP BY t.gltrn_act_mth, t.gltrn_amount, t.gltrn_ref_desc, t.gltrn_trandt, t.gltrn_ytdrec,
m.glmas_report, t.gltrn_acct_no, t.AcctMonth, t.acctyear, t.gltrn_glno, t.gltrn_upd, t.id_num, x.division
HAVING t.gltrn_acct_no = @GLItemAcct8 and
t.gltrn_amount <> 0 and
t.gltrn_ref_desc > '' and
t.AcctMonth = @GLAcctMonth and
t.AcctYear = @GLAcctYear and
t.gltrn_upd = 'Y' and
--t.gltrn_ytdrec = 'Y' and
t.gltrn_glno = 1 and
((x.division = @SPECIFIEDDIVISION) OR
(@SPECIFIEDDIVISION = -1))
ORDER BY gltrndate
END
END
END
if @MON = 'TRUE' and @SUM = 'TRUE' -- Month Transactions Only - Summarized
BEGIN
if @RPT = 'B'
BEGIN
DECLARE GLTrans Cursor for
SELECT [GLActMonth] = case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
end,
[GLTrnAmount] = sum(tt.balance),
[GLTRNPrevAmount] = sum( tt.PYBalance),
[TRANTYPE] = max(tt.TranType),
[GLDivision] = max(tt.Div),
[GlRefDesc] = 'YTD Balance',
[GLTrnDate] = case When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
end,
[GLTrnID] = -1
FROM #Temp0 tt
WHERE tt.mnth = @GLAcctMonth
END
ELSE -- Report = I
BEGIN
DECLARE GLTrans Cursor for
SELECT [GLActMonth] = case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
end,
[GLTRnAmount] = SUM(t.gltrn_amount),
[GLTRNPrevAmount] = 0,
[TRANTYPE] = 1,
[GLDivision] = @GLDIVISION,
[GLRefDesc] = '',
[GLTrnDate] = case When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
end,
[GLTrnID] = -99
FROM UC_Jan16_1700.dbo.tmpgltrn AS t
LEFT OUTER JOIN ids.GLRptXref AS x ON x.GLAcctNo = t.gltrn_acct_no
LEFT OUTER JOIN UC_Jan16_1700.Dbo.tmpglmas AS m ON m.glmas_acct_no = t.gltrn_acct_no
WHERE (t.gltrn_acct_no = @GLItemAcct8) AND
(t.AcctMonth =@GLAcctMonth) AND
(t.AcctYear = @GLAcctYear) AND
(t.gltrn_ref_desc > '') AND
(t.gltrn_upd = 'Y') AND
--(t.gltrn_ytdrec = 'Y') AND
(m.glmas_glno = 1) AND
(t.gltrn_amount <> 0) and
((x.division = @SPECIFIEDDIVISION) OR
(@SPECIFIEDDIVISION = -1))
GROUP BY t.gltrn_acct_no
END
END
IF @YTD = 'TRUE' and @SUM = 'FALSE'
BEGIN
if @RPT = 'B'
BEGIN
DECLARE GLTrans Cursor for
SELECT [GLActMonth] = case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
end,
[GLTrnAmount] = tt.balance,
[GLTRNPrevAmount] = tt.PYBalance,
[TRANTYPE] = tt.TranType,
[GLDivision] = tt.Div,
[GlRefDesc] = 'Monthly Balance',
[GLTrnDate] = case When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
end,
[GLTrnID] = -1
FROM #Temp0 tt
WHERE tt.mnth <= @GLAcctMonth
ORDER BY gltrndate
END
ELSE -- Report = I
BEGIN
DECLARE GLTrans Cursor for
SELECT [GLActMonth] = t.gltrn_act_mth,
[GLTrnAmount] = t.gltrn_amount,
[GLTRNPrevAmount] = 0,
[TRANTYPE] = 1,
[GLDivision] = x.division,
[GLRefDesc] = t.gltrn_ref_desc,
[GLTrnDate] = convert(varchar(20),t.gltrn_trandt,101),
[GLTrnID] = t.id_num
FROM UC_Jan16_1700.dbo.tmpgltrn t
Left Join UC_Jan16_1700.dbo.tmpglmas m on m.glmas_acct_no = t.gltrn_acct_no
left join ids.GLRptXref x on x.GLAcctNo = t.gltrn_acct_no
GROUP BY t.gltrn_act_mth, t.gltrn_amount, t.gltrn_ref_desc, t.gltrn_trandt, t.gltrn_ytdrec,
m.glmas_report, t.gltrn_acct_no, t.AcctMonth, t.acctyear, t.gltrn_glno, t.gltrn_upd, t.id_num, x.division
HAVING t.gltrn_acct_no = @GLItemAcct8 and
t.gltrn_amount <> 0 and
t.gltrn_ref_desc > '' and
t.AcctMonth <= @GLAcctMonth and
t.AcctYear = @GLAcctYear and
t.gltrn_upd = 'Y' and
-- t.gltrn_ytdrec = 'Y' and
t.gltrn_glno = 1 and
((x.division = @SPECIFIEDDIVISION) OR
(@SPECIFIEDDIVISION = -1))
ORDER BY gltrndate
END
END
IF @YTD = 'TRUE' and @SUM = 'TRUE'
BEGIN
if @RPT = 'B'
BEGIN
DECLARE GLTrans Cursor for
SELECT [GLActMonth] = case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
end,
[GLTrnAmount] = sum(tt.balance),
[GLTRNPrevAmount] = sum( tt.PYBalance),
[TRANTYPE] = max(tt.TranType),
[GLDivision] = max(tt.Div),
[GlRefDesc] = 'YTD Balance',
[GLTrnDate] = case When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
end,
[GLTrnID] = -1
FROM #Temp0 tt
WHERE tt.mnth <= @GLAcctMonth
END
ELSE -- Report = I
BEGIN
DECLARE GLTrans Cursor for
SELECT [GLActMonth] = case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
end,
[GLTRnAmount] = SUM(t.gltrn_amount),
[GLTRNPrevAmount] = 0,
[TRANTYPE] = 1,
[GLDivision] = @GLDIVISION,
[GLRefDesc] = '',
[GLTrnDate] = case When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
end,
[GLTrnID] = -99
FROM UC_Jan16_1700.dbo.tmpgltrn AS t
LEFT OUTER JOIN ids.GLRptXref AS x ON x.GLAcctNo = t.gltrn_acct_no
LEFT OUTER JOIN UC_Jan16_1700.dbo.tmpglmas AS m ON m.glmas_acct_no = t.gltrn_acct_no
WHERE (t.gltrn_acct_no = @GLItemAcct8) AND
(t.AcctMonth <=@GLAcctMonth) AND
(t.AcctYear = @GLAcctYear) AND
(t.gltrn_ref_desc > '') AND
(t.gltrn_upd = 'Y') AND
--(t.gltrn_ytdrec = 'Y') AND
(m.glmas_glno = 1) AND
(t.gltrn_amount <> 0) and
((x.division = @SPECIFIEDDIVISION) OR
(@SPECIFIEDDIVISION = -1))
GROUP BY t.gltrn_acct_no
END
END
OPEN GLTrans
FETCH NEXT FROM GLTrans INTO @GLTRNMonth, @GLTRNAmount,@GLTRNPrevAmount, @TRANTYPE, @GLDIVISION, @GLTRNDesc, @GLTRNDate, @GLTRNID
if @@FETCH_STATUS = -1 -- NO RECORDS FOUND IN THIS SELECT
BEGIN
---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
---'''' DEFAULT Level7 8 9 and 10 ''
---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
SET @GLLevel7 = 'SWE-All Divisions';
SET @GLLevel8 =
CASE
WHEN Rtrim(@DivName) = 'Corporate' then 'SG&A'
ELSE @SubDivision
END;
SET @GLLevel9 =
CASE
WHEN @GLDivision = 0 then @SubDivision
ELSE @DIVName
END;
SET @GLLevel10 = @GLItemAcct8
---''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
---''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
IF ISNULL(@GLDIVISION,99) < 99 and ISNULL(@GLDIVISION,99) > -1 --- VALID DIVISION
BEGIN
BEGIN TRANSACTION;
INSERT INTO ids.GLRptOutput(GLRptID, GLSessionID, GLControlDescr, Division, GLItemAcct, GLItemAcct8,
GLItemDescr, GLLevel1, GLLevel2, GLLevel3, GLLevel4, GLLevel5, GLLevel6, GLLevel7, GLLevel8, GLLevel9, GLLevel10,
GLItemAmount1, GLItemAmount2, GLItemAmount3, GLItemAmount4, GLItemAmount5,
GLItemAmount6, GlItemAmount7, GlItemAmount8, GLItemAmount9, GlItemAmount10, GLRowCode, GLPrintCode, GLMapCode,
GLFilter, GLFormula, GLItemInfo1, GLItemInfo2, GLItemDate)
VALUES(@GLRptID, @GLSessionID, @GLControlDesc, isnull(@GLDIVISION,99), @GLItemKey, @GLItemAcct8,
@GLItemDescr, @GLLevel1, @GLLevel2, @GLLevel3, @GLLevel4, @GLLevel5, @GlLevel6, @GlLevel7, @GlLevel8, @GlLevel9, @GlLevel10,
@GLItemAmount1, @GLItemAmount2, @GLItemAmount3, @GLItemAmount4, @GLItemAmount5,
@GlItemAmount6, @GlItemAmount7, @GLItemAmount8, @GLItemAmount9, @GlItemAmount10, @GLRowCode, @GLPrintCode, @RevSign,
@GLFilter, @GLFormula, @GLItemInfo1, isnull(@GLItemInfo2,''), isnull(@GlItemDate,''));
SET @NewId = SCOPE_IDENTITY();
IF @NewId < 1
BEGIN
RAISERROR(60000, 16, 1, 'Insert failed to return the new Record Id. Contact IT via Help Desk to resolve this issue.');
END
COMMIT TRANSACTION;
END
END
---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
---''' TRANSACTION RECORDS WERE FOUND
---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @GLItemInfo2 = @GLTRNMonth;
SET @GLItemDate = @GLTRNDate;
SET @NewId = 0;
SET @GLItemInfo1 = @GLTRNDesc;
SET @COMPAREMONTHSTR = @GLTRNMonth
SET @COMPAREMONTH = CONVERT(NUMERIC(2),SUBSTRING(@COMPAREMONTHSTR,3,2))
SET @COMPAREYEAR = YEAR(@GLTRNDate)
IF @TRANTYPE = 0 -- Read activity from balance
BEGIN
IF @PY = 'True' -- Prior Year Balance requested
BEGIN
SET @GLTRNAmount = @GLTRNPrevAmount
END
END
---''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
---''' Place Amount in Correct Accum Field
---''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
IF @LOOP = 1 or @MGAC = '1'
BEGIN
SET @GLItemAmount1 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 2 or @MGAC = '2'
BEGIN
SET @GLItemAmount2 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 3 or @MGAC = '3'
BEGIN
SET @GLItemAmount3 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 4 or @MGAC = '4'
BEGIN
SET @GLItemAmount4 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 5 or @MGAC = '5'
BEGIN
SET @GLItemAmount5 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 6 or @MGAC = '6'
BEGIN
SET @GLItemAmount6 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 7 or @MGAC = '7'
BEGIN
SET @GLItemAmount7 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 8 or @MGAC = '8'
BEGIN
SET @GLItemAmount8 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 9 or @MGAC = '9'
BEGIN
SET @GLItemAmount9 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 10 or @MGAC = 'A'
BEGIN
SET @GLItemAmount10 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
---'''' DEFAULT Level7 8 9 and 10 ''
---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
SET @GLLevel7 = 'SWE-All Divisions';
SET @GLLevel8 =
CASE
WHEN Rtrim(@DivName) = 'Corporate' then 'SG&A'
ELSE @SubDivision
END;
SET @GLLevel9 =
CASE
WHEN @GLDivision = 0 then @SubDivision
ELSE @DIVName
END;
SET @GLLevel10 = @GLItemAcct8
---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
---'''' ''
---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
IF ISNULL(@GLDIVISION,99) < 99 and ISNULL(@GLDIVISION,99) > -1
BEGIN
BEGIN TRANSACTION;
INSERT INTO ids.GLRptOutput(GLRptID, GLSessionID, GLControlDescr, Division, GLItemAcct, GLItemAcct8,
GLItemDescr, GLLevel1, GLLevel2, GLLevel3, GLLevel4, GLLevel5, GLLevel6, GLLevel7, GLLevel8, GLLevel9, GLLevel10,
GLItemAmount1, GLItemAmount2, GLItemAmount3, GLItemAmount4, GLItemAmount5,
GLItemAmount6, GlItemAmount7, GlItemAmount8, GLItemAmount9, GLItemAmount10, GLRowCode, GLPrintCode, GLMapCode,
GLFilter, GLFormula, GLItemInfo1, GLItemInfo2, GLItemDate)
VALUES(@GLRptID, @GLSessionID, @GLControlDesc, isnull(@GLDIVISION,99), @GLItemKey, @GLItemAcct8,
@GLItemDescr, @GLLevel1, @GLLevel2, @GLLevel3, @GLLevel4, @GLLevel5, @GlLevel6, @GlLevel7, @GlLevel8, @GlLevel9, @GlLevel10,
@GLItemAmount1, @GLItemAmount2, @GLItemAmount3, @GLItemAmount4, @GLItemAmount5,
@GlItemAmount6, @GlItemAmount7, @GLItemAmount8, @GLITEMAmount9, @GLItemAmount10, @GLRowCode, @GLPrintCode, @RevSign,
@GLFilter, @GLFormula, @GLItemInfo1, isnull(@GLItemInfo2,''), isnull(@GlItemDate,''));
SET @NewId = SCOPE_IDENTITY();
IF @NewId < 1
BEGIN
RAISERROR(60000, 16, 1, 'Insert failed to return the new Record Id. Contact IT via Help Desk to resolve this issue.');
END
COMMIT TRANSACTION;
END
set @GLItemAmount1 = 0;
set @GLItemAmount2 = 0;
set @GLItemAmount3 = 0;
set @GLItemAmount4 = 0;
set @GLItemAmount5 = 0;
set @GLItemAmount6 = 0;
set @GLItemAmount7 = 0;
set @GLItemAmount8 = 0;
set @GLItemAmount9 = 0;
set @GLItemAmount10 = 0;
FETCH NEXT FROM GLTrans INTO @GLTRNMonth, @GLTRNAmount,@GLTRNPrevAmount, @TRANTYPE, @GLDIVISION, @GLTRNDesc, @GLTRNDate, @GLTRNID
END
Close GLTrans
deallocate GLTrans
END
---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
---''''' END ACCUMULATER ONE
---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
SET @LOOP = @LOOP + 1
END
FETCH NEXT FROM GLAcct INTO @GLItemAcct8, @Divname, @GLDivision, @SubDivision, @GLItemDescr, @GLReport,
@01, @01P, @02, @02P, @03, @03P, @04, @04P, @05, @05P,
@06, @06P, @07, @07P, @08, @08P, @09, @09P, @10, @10P,
@11, @11P, @12, @12P
IF @@FETCH_STATUS = 0
BEGIN
SET @ValidAcctFetch = @ValidAcctFetch + 1
END
ELSE
BEGIN
SET @ValidAcctFetch = 999999999
END
SET @LOOP = 1
END
close GLAcct
deallocate GlAcct
drop table #Temp0
SELECT [ErrorNumber] = 0,
[ErrorMessage] = 'New ids.GLRptOutput records have been Inserted.',
[NewID] = @NewId,
[GLRptID] = @GLRptID;
END TRY
BEGIN CATCH
If @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END
INSERT INTO ErrLog (Number, Severity, ErrState,
ErrSource, ErrLine, ErrMsg, Form, AppID)
SELECT ISNULL(ERROR_NUMBER(),-1), ISNULL(ERROR_SEVERITY(),0), ISNULL(ERROR_STATE(),0),
ISNULL(ERROR_PROCEDURE(),'Unknown.'), ISNULL(ERROR_LINE(),0),
ISNULL(ERROR_MESSAGE(),'ROLLBACK: Failed to Insert ids.GLRptOutput Record.'), OBJECT_NAME(@@PROCID), 1;
SELECT [ErrorNumber] = ISNULL(ERROR_NUMBER(),-1),
[ErrorMessage] = ISNULL(ERROR_MESSAGE(),'ROLLBACK: ids.GLRptOutput Record failed to Insert.'),
[ProcName] = OBJECT_NAME(@@PROCID),
[ErrorLine] = ISNULL(ERROR_LINE(),0),
[AppID] = 5,
[GLRptOutputID] = @NewId,
[GLRptID] = @GLrptID;
END CATCH