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 (
    BillInternal# char (21) null,
    BillDoc# char (21) null,
    BillDocType# smallint null,
    BillMode# tinyint null,
    Vendor char (15) null, 
    OriginalAmtAfterWriteOff numeric(19, 5) null,
    DocDate datetime null
    )
create index #PMTrans_PK on #PMTrans (BillInternal#, BillDocType#, BillMode#, Vendor, OriginalAmtAfterWriteOff)

/* Create the table used to hold Sum of Apply Records from PM30300 */
go
create table #PMApply (
    BillInternal# char (21) null,
    BillDocType# 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 (BillInternal#, BillDocType#, BillMode#, Vendor, TotalAppliedAmt, WriteOffAmt, DiscountAmt, RealizedAmt, OrigCurrency)

print "Selecting Bills in History"
go
insert #PMTrans ( BillInternal#, BillDoc#, BillDocType#, BillMode#, Vendor, OriginalAmtAfterWriteOff, 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 ( BillInternal#, BillDoc#, BillDocType#, BillMode#, Vendor, OriginalAmtAfterWriteOff, 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 ( BillInternal#, BillDocType#, 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 ( BillInternal#, BillDocType#, 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.BillInternal#, a.BillDoc#, a.BillDocType#, a.Vendor, a.DocDate, a.OriginalAmtAfterWriteOff, 
    isnull(b.TotalAppliedAmt,0) as TotalAppliedAmt,
    isnull((a.OriginalAmtAfterWriteOff - (b.TotalAppliedAmt + b.WriteOffAmt - b.DiscountAmt - b.RealizedAmt)),0) as Differ
    from #PMTrans a 
    left outer join #PMApply b on 
        a.BillInternal# = b.BillInternal# and a.BillDocType# = b.BillDocType#
        and a.BillMode# = b.BillMode# and a.Vendor = b.Vendor
    where ((isnull((a.OriginalAmtAfterWriteOff - (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.OriginalAmtAfterWriteOff - (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