ALTER PROCEDURE [dbo].[seeRMAgeTrailBalance]
@SORTBY CHAR(15),
@i_CUSTOMER_RS CHAR(15) = '',
@i_CUSTOMER_RE CHAR(15) = '',
@i_CUSTNAME_RS CHAR(65) = '',
@i_CUSTNAME_RE CHAR(65) = '',
@i_CUSTCLS_RS CHAR(15) = '',
@i_CUSTCLS_RE CHAR(15) = '',
@i_CUSTTYPE_RS CHAR(21) = '',
@i_CUSTTYPE_RE CHAR(21) = '',
@i_SLSPERSN_RS CHAR(15) = '',
@i_SLSPERSN_RE CHAR(15) = '',
@i_SLSTERRY_RS CHAR(15) = '',
@i_SLSTERRY_RE CHAR(15) = '',
@DETAIL TINYINT = 0
AS
SET NOCOUNT ON;
DECLARE @AGINGBKLBL1 CHAR(15),
@AGINGBKLBL2 CHAR(15),
@AGINGBKLBL3 CHAR(15),
@AGINGBKLBL4 CHAR(15),
@AGINGBKLBL5 CHAR(15),
@AGINGBKLBL6 CHAR(15),
@AGINGBKLBL7 CHAR(15),
@AGINGBKDY1 INT,
@AGINGBKDY2 INT,
@AGINGBKDY3 INT,
@AGINGBKDY4 INT,
@AGINGBKDY5 INT,
@AGINGBKDY6 INT,
@AGINGBKDY7 INT,
@MAXDAYS INT,
@MAXINDX TINYINT,
@AGEBY TINYINT,
@AGEUNAPPLDCR TINYINT,
@AGEDATE CHAR(12),
@CDATE CHAR(12),
@C_CUSTNMBR CHAR(15),
@C_RMDTYPAL TINYINT,
@C_DTDIFF INT,
@C_DOCDATE DATETIME,
@C_DUEDATE DATETIME,
@C_CURTRXAM NUMERIC(19, 5),
@C_MY_DEX_ROW INT;
SET @AGINGBKLBL1 = '';
SET @AGINGBKLBL2 = '';
SET @AGINGBKLBL3 = '';
SET @AGINGBKLBL4 = '';
SET @AGINGBKLBL5 = '';
SET @AGINGBKLBL6 = '';
SET @AGINGBKLBL7 = '';
SET @AGINGBKDY1 = 0;
SET @AGINGBKDY2 = 0;
SET @AGINGBKDY3 = 0;
SET @AGINGBKDY4 = 0;
SET @AGINGBKDY5 = 0;
SET @AGINGBKDY6 = 0;
SET @AGINGBKDY7 = 0;
SET @CDATE = CONVERT(CHAR(12), GETDATE(), 101);
SELECT @AGINGBKLBL1 = RMPERDSC,
@AGINGBKDY1 = RMPEREND
FROM RM40201
WHERE INDEX1 = 1;
SELECT @AGINGBKLBL2 = RMPERDSC,
@AGINGBKDY2 = RMPEREND
FROM RM40201
WHERE INDEX1 = 2;
SELECT @AGINGBKLBL3 = RMPERDSC,
@AGINGBKDY3 = RMPEREND
FROM RM40201
WHERE INDEX1 = 3;
SELECT @AGINGBKLBL4 = RMPERDSC,
@AGINGBKDY4 = RMPEREND
FROM RM40201
WHERE INDEX1 = 4;
SELECT @AGINGBKLBL5 = RMPERDSC,
@AGINGBKDY5 = RMPEREND
FROM RM40201
WHERE INDEX1 = 5;
SELECT @AGINGBKLBL6 = RMPERDSC,
@AGINGBKDY6 = RMPEREND
FROM RM40201
WHERE INDEX1 = 6;
SELECT @AGINGBKLBL7 = RMPERDSC,
@AGINGBKDY7 = RMPEREND
FROM RM40201
WHERE INDEX1 = 7;
SELECT @MAXDAYS = MAX(RMPEREND)
FROM RM40201;
SELECT @MAXINDX = INDEX1
FROM RM40201
WHERE RMPEREND = @MAXDAYS;
SET @MAXINDX = @MAXINDX - 1;
SELECT @MAXDAYS = RMPEREND
FROM RM40201
WHERE INDEX1 = @MAXINDX;
SELECT @AGEBY = AGEBY,
@AGEUNAPPLDCR = AGEUNAPPLDCR
FROM RM40101;
IF OBJECT_ID('tempdb..#RMOPENTRX') IS NULL
BEGIN
CREATE TABLE #RMOPENTRX
(
CUSTNMBR CHAR(25) NOT NULL
DEFAULT '',
DOCNUMBR CHAR(21) NOT NULL
DEFAULT '',
RMDTYPAL TINYINT NOT NULL
DEFAULT 0,
DOCABREV CHAR(8) NOT NULL
DEFAULT '',
DOCDATE DATETIME NOT NULL
DEFAULT '01/01/1900',
GLPOSTDT DATETIME NOT NULL
DEFAULT '01/01/1900',
DUEDATE DATETIME NOT NULL
DEFAULT '01/01/1900',
ORTRXAMT NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
CURTRXAM NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
WROFAMNT NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
DISTKNAM NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
DISAVAMT NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
DISAVTKN NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
DISCDATE DATETIME NOT NULL
DEFAULT '01/01/1900',
TRXDSCRN CHAR(31) NOT NULL
DEFAULT '',
CSPORNBR CHAR(21) NOT NULL
DEFAULT '',
SLPRSNID CHAR(15) NOT NULL
DEFAULT '',
SALSTERR CHAR(15) NOT NULL
DEFAULT '',
VOIDSTTS TINYINT NOT NULL
DEFAULT 0,
CURNCYID CHAR(15) NOT NULL
DEFAULT '',
PYMTRMID CHAR(21) NOT NULL
DEFAULT '',
SHIPMTHD CHAR(15) NOT NULL
DEFAULT '',
VOIDDATE DATETIME NOT NULL
DEFAULT '01/01/1900',
CUSTNAME CHAR(65) NOT NULL
DEFAULT '',
CUSTCLAS CHAR(15) NOT NULL
DEFAULT '',
BALNCTYP TINYINT NOT NULL
DEFAULT 0,
LASTAGED DATETIME NOT NULL
DEFAULT '01/01/1900',
USERDEF1 CHAR(50) NOT NULL
DEFAULT '',
CNTCPRSN CHAR(65) NOT NULL
DEFAULT '',
PHONE1 CHAR(31) NOT NULL
DEFAULT '',
CRLMTAMT NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
CRDTSTRG CHAR(200) NOT NULL
DEFAULT '',
CRLMTPER TINYINT NOT NULL
DEFAULT 0,
CRLMTPAM NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
NUMOFDYS INT NOT NULL
DEFAULT 0,
AGINGBK1 NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
AGINGBK2 NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
AGINGBK3 NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
AGINGBK4 NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
AGINGBK5 NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
AGINGBK6 NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
AGINGBK7 NUMERIC(19, 5) NOT NULL
DEFAULT 0.00,
MY_DEX_ROW INT IDENTITY(1, 1)
);
END;
DELETE #RMOPENTRX;
IF (
@i_SLSPERSN_RE = 'þþþþþþþþþþþþþþþ'
AND @i_SLSTERRY_RE = 'þþþþþþþþþþþþþþþ'
)
BEGIN
INSERT INTO #RMOPENTRX
(
CUSTNMBR,
DOCNUMBR,
RMDTYPAL,
DOCDATE,
GLPOSTDT,
DUEDATE,
ORTRXAMT,
CURTRXAM,
WROFAMNT,
DISTKNAM,
DISAVAMT,
DISAVTKN,
DISCDATE,
TRXDSCRN,
CSPORNBR,
SLPRSNID,
VOIDSTTS,
CURNCYID,
PYMTRMID,
SHIPMTHD,
VOIDDATE,
CUSTNAME,
CUSTCLAS,
BALNCTYP,
USERDEF1,
CNTCPRSN,
PHONE1,
CRLMTAMT,
CRLMTPER,
CRLMTPAM,
CRDTSTRG
)
SELECT a.CUSTNMBR,
a.DOCNUMBR,
a.RMDTYPAL,
a.DOCDATE,
a.GLPOSTDT,
a.DUEDATE,
a.ORTRXAMT,
a.CURTRXAM,
a.WROFAMNT,
a.DISTKNAM,
a.DISAVAMT,
a.DISAVTKN,
a.DISCDATE,
a.TRXDSCRN,
a.CSPORNBR,
a.SLPRSNID,
a.VOIDSTTS,
a.CURNCYID,
a.PYMTRMID,
a.SHIPMTHD,
a.VOIDDATE,
b.CUSTNAME,
b.CUSTCLAS,
b.BALNCTYP,
b.USERDEF1,
b.CNTCPRSN,
b.PHONE1,
b.CRLMTAMT,
b.CRLMTPER,
b.CRLMTPAM,
'$' + RTRIM(CAST(CAST(b.CRLMTAMT AS NUMERIC(19, 2)) AS CHAR(15))) + ' - Or if the Sum of Period '
+ CAST(b.CRLMTPER AS CHAR(1)) + ' and Beyond Exceeds ' + RTRIM(CAST(b.CRLMTPAM AS CHAR(15)))
FROM RM20101 a,
RM00101 b
WHERE a.CUSTNMBR = b.CUSTNMBR
AND a.CUSTNMBR
BETWEEN @i_CUSTOMER_RS AND @i_CUSTOMER_RE
AND b.CUSTNAME
BETWEEN @i_CUSTNAME_RS AND @i_CUSTNAME_RE
AND b.CUSTCLAS
BETWEEN @i_CUSTCLS_RS AND @i_CUSTCLS_RE
AND b.USERDEF1
BETWEEN @i_CUSTTYPE_RS AND @i_CUSTTYPE_RE
ORDER BY a.CUSTNMBR,
a.DOCDATE ASC;
END;
ELSE
BEGIN
INSERT INTO #RMOPENTRX
(
CUSTNMBR,
DOCNUMBR,
RMDTYPAL,
DOCDATE,
GLPOSTDT,
DUEDATE,
ORTRXAMT,
CURTRXAM,
WROFAMNT,
DISTKNAM,
DISAVAMT,
DISAVTKN,
DISCDATE,
TRXDSCRN,
CSPORNBR,
SLPRSNID,
VOIDSTTS,
CURNCYID,
PYMTRMID,
SHIPMTHD,
VOIDDATE,
CUSTNAME,
CUSTCLAS,
BALNCTYP,
USERDEF1,
CNTCPRSN,
PHONE1,
CRLMTAMT,
CRLMTPER,
CRLMTPAM,
CRDTSTRG
)
SELECT a.CUSTNMBR,
a.DOCNUMBR,
a.RMDTYPAL,
a.DOCDATE,
a.GLPOSTDT,
a.DUEDATE,
a.ORTRXAMT,
a.CURTRXAM,
a.WROFAMNT,
a.DISTKNAM,
a.DISAVAMT,
a.DISAVTKN,
a.DISCDATE,
a.TRXDSCRN,
a.CSPORNBR,
a.SLPRSNID,
a.VOIDSTTS,
a.CURNCYID,
a.PYMTRMID,
a.SHIPMTHD,
a.VOIDDATE,
b.CUSTNAME,
b.CUSTCLAS,
b.BALNCTYP,
b.USERDEF1,
b.CNTCPRSN,
b.PHONE1,
b.CRLMTAMT,
b.CRLMTPER,
b.CRLMTPAM,
'$' + RTRIM(CAST(CAST(b.CRLMTAMT AS NUMERIC(19, 2)) AS CHAR(15))) + ' - Or if the Sum of Period '
+ CAST(b.CRLMTPER AS CHAR(1)) + ' and Beyond Exceeds ' + RTRIM(CAST(b.CRLMTPAM AS CHAR(15)))
FROM RM20101 a,
RM00101 b
WHERE a.CUSTNMBR = b.CUSTNMBR
AND a.CUSTNMBR
BETWEEN @i_CUSTOMER_RS AND @i_CUSTOMER_RE
AND b.CUSTNAME
BETWEEN @i_CUSTNAME_RS AND @i_CUSTNAME_RE
AND b.CUSTCLAS
BETWEEN @i_CUSTCLS_RS AND @i_CUSTCLS_RE
AND b.USERDEF1
BETWEEN @i_CUSTTYPE_RS AND @i_CUSTTYPE_RE
AND b.SLPRSNID
BETWEEN @i_SLSPERSN_RS AND @i_SLSPERSN_RE
AND b.SALSTERR
BETWEEN @i_SLSTERRY_RS AND @i_SLSTERRY_RE
ORDER BY a.CUSTNMBR,
a.DOCDATE ASC;
END;
UPDATE a
SET a.LASTAGED = b.LASTAGED
FROM #RMOPENTRX a,
RM00103 b
WHERE a.CUSTNMBR = b.CUSTNMBR;
UPDATE a
SET a.SLPRSNID = b.SLPRSNID
FROM #RMOPENTRX a,
RM00101 b
WHERE a.CUSTNMBR = b.CUSTNMBR;
UPDATE a
SET a.SALSTERR = b.SALSTERR
FROM #RMOPENTRX a,
RM00301 b
WHERE a.SLPRSNID = b.SLPRSNID;
UPDATE a
SET a.DOCABREV = b.DOCABREV
FROM #RMOPENTRX a,
RM40401 b
WHERE a.RMDTYPAL = b.RMDTYPAL;
UPDATE #RMOPENTRX
SET PHONE1 = '(' + SUBSTRING(PHONE1, 1, 3) + ')' + SUBSTRING(PHONE1, 4, 3) + '-' + SUBSTRING(PHONE1, 7, 4) + ' Ext. '
+ SUBSTRING(PHONE1, 11, 4);
DECLARE TRX_RANGE CURSOR FOR
SELECT CUSTNMBR,
RMDTYPAL,
DOCDATE,
DUEDATE,
CURTRXAM,
MY_DEX_ROW
FROM #RMOPENTRX
ORDER BY MY_DEX_ROW;
OPEN TRX_RANGE;
FETCH NEXT FROM TRX_RANGE
INTO @C_CUSTNMBR,
@C_RMDTYPAL,
@C_DOCDATE,
@C_DUEDATE,
@C_CURTRXAM,
@C_MY_DEX_ROW;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @AGEDATE = LASTAGED
FROM RM00103
WHERE CUSTNMBR = @C_CUSTNMBR;
IF @AGEBY = 1
BEGIN
IF (@C_DUEDATE > @AGEDATE)
BEGIN
SET @C_DTDIFF = 0;
END;
ELSE IF (@AGEUNAPPLDCR = 0 AND @C_RMDTYPAL >= 7)
BEGIN
SET @C_DTDIFF = 0;
END;
ELSE IF (@AGEUNAPPLDCR = 1 AND @C_DUEDATE = '01/01/1900' AND @C_RMDTYPAL > 7)
BEGIN
SELECT @C_DTDIFF = DATEDIFF(DAY, @C_DOCDATE, @AGEDATE);
END;
ELSE
BEGIN
SELECT @C_DTDIFF = DATEDIFF(DAY, @C_DOCDATE, @AGEDATE);
END;
END;
ELSE
BEGIN
IF (@C_DUEDATE > @AGEDATE)
BEGIN
SET @C_DTDIFF = 0;
END;
ELSE IF (@AGEUNAPPLDCR = 0 AND @C_RMDTYPAL >= 7)
BEGIN
SET @C_DTDIFF = 0;
END;
ELSE IF (@AGEUNAPPLDCR = 1 AND @C_DUEDATE = '01/01/1900' AND @C_RMDTYPAL > 7)
BEGIN
SELECT @C_DTDIFF = DATEDIFF(DAY, @C_DUEDATE, @AGEDATE);
END;
ELSE
BEGIN
SELECT @C_DTDIFF = DATEDIFF(DAY, @C_DUEDATE, @AGEDATE);
END;
END;
IF @C_RMDTYPAL >= 7
BEGIN
SET @C_CURTRXAM = @C_CURTRXAM * -1;
END;
IF (@C_DTDIFF = 0)
BEGIN
UPDATE #RMOPENTRX
SET AGINGBK1 = @C_CURTRXAM,
NUMOFDYS = @C_DTDIFF
WHERE MY_DEX_ROW = @C_MY_DEX_ROW;
SET @C_DTDIFF = 0;
FETCH NEXT FROM TRX_RANGE
INTO @C_CUSTNMBR,
@C_RMDTYPAL,
@C_DOCDATE,
@C_DUEDATE,
@C_CURTRXAM,
@C_MY_DEX_ROW;
CONTINUE;
END;
IF (
(@C_DTDIFF <= @AGINGBKDY1)
OR
(
@C_DTDIFF > @MAXDAYS
AND @AGINGBKDY2 > @MAXDAYS
)
)
BEGIN
UPDATE #RMOPENTRX
SET AGINGBK1 = @C_CURTRXAM,
NUMOFDYS = @C_DTDIFF
WHERE MY_DEX_ROW = @C_MY_DEX_ROW;
SET @C_DTDIFF = 0;
FETCH NEXT FROM TRX_RANGE
INTO @C_CUSTNMBR,
@C_RMDTYPAL,
@C_DOCDATE,
@C_DUEDATE,
@C_CURTRXAM,
@C_MY_DEX_ROW;
CONTINUE;
END;
IF (
(@C_DTDIFF <= @AGINGBKDY2)
OR
(
@C_DTDIFF > @MAXDAYS
AND @AGINGBKDY2 > @MAXDAYS
)
)
BEGIN
UPDATE #RMOPENTRX
SET AGINGBK2 = @C_CURTRXAM,
NUMOFDYS = @C_DTDIFF
WHERE MY_DEX_ROW = @C_MY_DEX_ROW;
SET @C_DTDIFF = 0;
FETCH NEXT FROM TRX_RANGE
INTO @C_CUSTNMBR,
@C_RMDTYPAL,
@C_DOCDATE,
@C_DUEDATE,
@C_CURTRXAM,
@C_MY_DEX_ROW;
CONTINUE;
END;
IF (
(@C_DTDIFF <= @AGINGBKDY3)
OR
(
@C_DTDIFF > @MAXDAYS
AND @AGINGBKDY3 > @MAXDAYS
)
)
BEGIN
UPDATE #RMOPENTRX
SET AGINGBK3 = @C_CURTRXAM,
NUMOFDYS = @C_DTDIFF
WHERE MY_DEX_ROW = @C_MY_DEX_ROW;
SET @C_DTDIFF = 0;
FETCH NEXT FROM TRX_RANGE
INTO @C_CUSTNMBR,
@C_RMDTYPAL,
@C_DOCDATE,
@C_DUEDATE,
@C_CURTRXAM,
@C_MY_DEX_ROW;
CONTINUE;
END;
IF (
(@C_DTDIFF <= @AGINGBKDY4)
OR
(
@C_DTDIFF > @MAXDAYS
AND @AGINGBKDY4 > @MAXDAYS
)
)
BEGIN
UPDATE #RMOPENTRX
SET AGINGBK4 = @C_CURTRXAM,
NUMOFDYS = @C_DTDIFF
WHERE MY_DEX_ROW = @C_MY_DEX_ROW;
SET @C_DTDIFF = 0;
FETCH NEXT FROM TRX_RANGE
INTO @C_CUSTNMBR,
@C_RMDTYPAL,
@C_DOCDATE,
@C_DUEDATE,
@C_CURTRXAM,
@C_MY_DEX_ROW;
CONTINUE;
END;
IF (
(@C_DTDIFF <= @AGINGBKDY5)
OR
(
@C_DTDIFF > @MAXDAYS
AND @AGINGBKDY5 > @MAXDAYS
)
)
BEGIN
UPDATE #RMOPENTRX
SET AGINGBK5 = @C_CURTRXAM,
NUMOFDYS = @C_DTDIFF
WHERE MY_DEX_ROW = @C_MY_DEX_ROW;
SET @C_DTDIFF = 0;
FETCH NEXT FROM TRX_RANGE
INTO @C_CUSTNMBR,
@C_RMDTYPAL,
@C_DOCDATE,
@C_DUEDATE,
@C_CURTRXAM,
@C_MY_DEX_ROW;
CONTINUE;
END;
IF (
(@C_DTDIFF <= @AGINGBKDY6)
OR
(
@C_DTDIFF > @MAXDAYS
AND @AGINGBKDY6 > @MAXDAYS
)
)
BEGIN
UPDATE #RMOPENTRX
SET AGINGBK6 = @C_CURTRXAM,
NUMOFDYS = @C_DTDIFF
WHERE MY_DEX_ROW = @C_MY_DEX_ROW;
SET @C_DTDIFF = 0;
FETCH NEXT FROM TRX_RANGE
INTO @C_CUSTNMBR,
@C_RMDTYPAL,
@C_DOCDATE,
@C_DUEDATE,
@C_CURTRXAM,
@C_MY_DEX_ROW;
CONTINUE;
END;
ELSE
BEGIN
UPDATE #RMOPENTRX
SET AGINGBK7 = @C_CURTRXAM,
NUMOFDYS = @C_DTDIFF
WHERE MY_DEX_ROW = @C_MY_DEX_ROW;
SET @C_DTDIFF = 0;
FETCH NEXT FROM TRX_RANGE
INTO @C_CUSTNMBR,
@C_RMDTYPAL,
@C_DOCDATE,
@C_DUEDATE,
@C_CURTRXAM,
@C_MY_DEX_ROW;
CONTINUE;
END;
FETCH NEXT FROM TRX_RANGE
INTO @C_CUSTNMBR,
@C_RMDTYPAL,
@C_DOCDATE,
@C_DUEDATE,
@C_CURTRXAM,
@C_MY_DEX_ROW;
END;
CLOSE TRX_RANGE;
DEALLOCATE TRX_RANGE;
SELECT *
FROM #RMOPENTRX
ORDER BY CUSTNMBR,
RMDTYPAL;