Hi Heather,
I think David Musgrave gave me this query several years ago. This one will search PM for missing apply records. I'll check to see if I have one for Receivables. If this doesn't paste well, shoot me an e-mail (leslievail@earthlink.net) and I'll send the file to you.
Kind regards,
Leslie
/* SQL PM Find Missing Apply Records
This statement goes through the pm30200 table and looks for any record that does
not have a matching apply document(s) to satisfy the balance*/
SET quoted_identifier OFF
SET nocount ON
PRINT "Deleting Temp tables (if they exist)"
go
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = Object_id('tempdb..#PMTrans')
AND type = 'U')
DROP TABLE #pmtrans
go
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = Object_id('tempdb..#PMApply')
AND type = 'U')
DROP TABLE #pmapply
/* Create the table used to hold PM30200 Info */
PRINT "Creating Temp tables"
go
CREATE TABLE #pmtrans
(
vchrnmbr# CHAR (21) NULL
, docnumbr# CHAR (21) NULL
, doctype# SMALLINT NULL
, billmode# TINYINT NULL
, vendor CHAR (15) NULL
, DocAmtAfterWriteOff NUMERIC(19, 5) NULL
, docdate DATETIME NULL
)
CREATE INDEX #pmtrans_pk
ON #pmtrans (vchrnmbr#, doctype#, billmode#, vendor,
DocAmtAfterWriteOff)
/* Create the table used to hold Sum of Apply Records from PM30300 */
go
CREATE TABLE #pmapply
(
vchrnmbr# CHAR (21) NULL
, doctype# SMALLINT NULL
, billmode# TINYINT NULL
, vendor CHAR (15) NULL
, totalappliedamt NUMERIC(19, 5) NULL
, writeoffamt NUMERIC(19, 5) NULL
, discountamt NUMERIC(19, 5) NULL
, realizedamt NUMERIC(19, 5) NULL
, origcurrency INT NULL
)
CREATE INDEX #pmapply_pk
ON #pmapply (vchrnmbr#, doctype#, billmode#, vendor, totalappliedamt,
writeoffamt, discountamt, realizedamt, origcurrency)
PRINT "Selecting Bills in History"
go
INSERT #pmtrans
(vchrnmbr#
, docnumbr#
, doctype#
, billmode#
, vendor
, DocAmtAfterWriteOff
, docdate)
SELECT vchrnmbr
, docnumbr
, doctype
, 0
, vendorid
, ( docamnt - distknam )
, docdate
FROM pm30200
WHERE doctype < 4
AND voided = 0
AND ( docamnt - distknam ) <> 0.0 -- Zero Value Documents
ORDER BY vchrnmbr
INSERT #pmtrans
(vchrnmbr#
, docnumbr#
, doctype#
, billmode#
, vendor
, DocAmtAfterWriteOff
, docdate)
SELECT vchrnmbr
, docnumbr
, doctype
, 1
, vendorid
, ( docamnt - distknam )
, docdate
FROM pm30200
WHERE doctype >= 4
AND voided = 0
AND ( docamnt - distknam ) <> 0.0 -- Zero Value Documents
AND NOT ( doctype = 4
AND ttlpymts = docamnt ) -- Remove fully paid returns
ORDER BY vchrnmbr
PRINT "Selecting Apply Records and Totally Apply Credits in History"
go
/* Get Functional Currency ID */
DECLARE @FUNC VARCHAR(5)
SELECT TOP 1 @FUNC = funlcurr
FROM mc40000
INSERT #pmapply
(vchrnmbr#
, doctype#
, billmode#
, vendor
, totalappliedamt
, writeoffamt
, discountamt
, realizedamt
, origcurrency)
SELECT aptvchnm
, aptodcty
, 0
, vendorid
, Sum(appldamt)
, Sum(wrofamnt)
, 0
, 0
, Sum (CASE
WHEN @FUNC != ''
AND @FUNC != curncyid THEN 1
ELSE 0
END)
FROM pm30300
GROUP BY aptvchnm
, aptodcty
, vendorid
ORDER BY aptvchnm
INSERT #pmapply
(vchrnmbr#
, doctype#
, billmode#
, vendor
, totalappliedamt
, writeoffamt
, discountamt
, realizedamt
, origcurrency)
SELECT vchrnmbr
, doctype
, 1
, vendorid
, Sum(appldamt)
, 0
, Sum(distknam)
, Sum(rlganlos)
, Sum (CASE
WHEN @FUNC != ''
AND @FUNC != curncyid THEN 1
ELSE 0
END)
FROM pm30300
GROUP BY vchrnmbr
, doctype
, vendorid
ORDER BY vchrnmbr
PRINT "Entries that are missing apply records"
go
SELECT a.billmode#
, a.vchrnmbr#
, a.docnumbr#
, a.doctype#
, a.vendor
, a.docdate
, a.DocAmtAfterWriteOff
, Isnull(b.totalappliedamt, 0)
AS TotalAppliedAmt
, Isnull(( a.DocAmtAfterWriteOff - (
b.totalappliedamt + b.writeoffamt -
b.discountamt
- b.realizedamt ) ), 0) AS
Differ
FROM #pmtrans a
LEFT OUTER JOIN #pmapply b
ON a.vchrnmbr# = b.vchrnmbr#
AND a.doctype# = b.doctype#
AND a.billmode# = b.billmode#
AND a.vendor = b.vendor
WHERE ( ( Isnull(( a.DocAmtAfterWriteOff - (
b.totalappliedamt + b.writeoffamt -
b.discountamt
-
b.realizedamt ) ), 0) <> 0 )
OR ( b.vendor IS NULL ) )
AND ( a.billmode# = 0
OR Isnull(b.origcurrency, 0) = 0
OR ( Abs(Isnull(( a.DocAmtAfterWriteOff - (
b.totalappliedamt + b.writeoffamt
-
b.discountamt
-
b.realizedamt ) ), 0)) > (
Isnull(b.origcurrency, 0) / 100.0 )
) ) -- Rounding on Credit Documents
ORDER BY a.vendor
PRINT "Removing Temp tables"
go
DROP TABLE #pmtrans
DROP TABLE #pmapply
Share
Report
9,142
All responses (
Answers (
