Hello Roger,
The main table you will be looking for on the PM side will be the PM30200's DINVPDOF column. This column shows the date that the invoice is fully paid off (based of the apply dates in the PM30300. Below you will find the all payables script we use in support. This script is a great way to review the links between tables to help you write the scripts.
/******************************************************************************************************
** All Payables info script. Includes all Payables tables as well as GL, including:
** Payables info:
** PM00400 - PM Key Master
** PM10000 - PM Transaction WORK
** PM10100 - PM Distribution WORK OPEN
** PM10200 - PM Apply To WORK OPEN
** PM10201 - PM Payment Apply To Work
** PM10300 - PM Payment WORK
** PM10400 - PM Manual Payment WORK
** PM10500 - PM Tax Work
** PM20000 - PM Transaction OPEN
** PM20100 - PM Apply To OPEN OPEN Temporary
** PM20200 - PM Distribution OPEN OPEN Temporary
** PM30200 - PM Paid Transaction History
** PM30300 - PM Apply to History
** PM30600 - PM Distribution History
** PM30700 - PM Tax History
** PM30800 - PM Tax Invoices
** Payables Void Temporary Tables:
** PM10600 - PM Distribution Void WORK Temporary
** PM10801 - PM Payment Stub Duplicate
** PM10900 - Void Payment WORK Temporary
** PM10901 - PM Void Transaction WORK Temporary
** PM10902 - PM Tax Void Work Temporary
** General Ledger:
** GL10000 - Transaction Work
** GL10001 - Transaction Amounts Work
** GL20000 - Year-to-Date Transaction Open
** GL30000 - Account Transaction History
**Multicurrency:
**MC020103 - Multicurrency Payables Transactions
**MC020105 - Multicurrency RM Revaluation Activity
**Bank Reconcilation:
**CM20200 - CM Transaction
Instructions:
Step 1. Replace 00000000000000447 with the document's Voucher/Payment Number for @VCHRNMBR. The Voucher/Payment Number can be seen from Inquiry>
Purchasing> Transaction by Vendor. Click the Show details button to view the Voucher/Payment Number.
Step 2. Enter the appropriate DOCTYPE Value for @DOCTYPE :
1=Invoice
2=Finance Charge
3=Miscellaneous Charge
4=Return
5=Credit Memo
6=Payment
Step 3. Select the appropriate company database and click Execute.
----------------------------------------------------------------------------------
********************************************************************************************************************/
declare @VCHRNMBR char(20)
declare @DOCTYPE smallint
select @VCHRNMBR = '00000000000000774'
select @DOCTYPE = '1'
----------------------------------------------------------------------------------
/*Payables info*/
print '========================================================================================================='
print ('Payables info - Voucher Entered in Script: ' + @VCHRNMBR + 'Document Type Entered in Script: ' + CAST(@DOCTYPE AS VARCHAR))
print '========================================================================================================='
print ''
Begin
Begin
print 'PM00400 - PM Key Master'
select * from PM00400 where CNTRLNUM = @VCHRNMBR and DOCTYPE=@DOCTYPE
End
Begin
print 'PM10000 - PM Transaction WORK'
select * from PM10000 where VCHNUMWK = @VCHRNMBR and DOCTYPE=@DOCTYPE
End
Begin
print 'PM10100 - PM Distribution WORK OPEN'
if @DOCTYPE <=5 (select * from PM10100 where VCHRNMBR = @VCHRNMBR and CNTRLTYP=0)
if @DOCTYPE =6 (select * from PM10100 where VCHRNMBR = @VCHRNMBR and CNTRLTYP=1)
End
Begin
print 'PM10200 - PM Apply To WORK OPEN'
if @DOCTYPE <=3 (Select * from PM10200 where APTVCHNM=@VCHRNMBR AND APTODCTY = @DOCTYPE) /*jnelson amelroe 06/20/2016*/
if @DOCTYPE >=4 (select * from PM10200 where VCHRNMBR=@VCHRNMBR AND DOCTYPE = @DOCTYPE) /*jnelson amelroe 06/9/2016*/
End
Begin
print 'PM10201 - PM Payment Apply To Work'
if @DOCTYPE <=3 (Select * from PM10201 where APTVCHNM=@VCHRNMBR)
if @DOCTYPE =6 (select * from PM10201 where PMNTNMBR = @VCHRNMBR)
End
Begin
if @DOCTYPE = 6
print 'PM10300 - PM Payment WORK'
if @DOCTYPE = 6 (Select * from PM10300 where PMNTNMBR=@VCHRNMBR)
End
Begin
if @DOCTYPE = 6
print 'PM10400 - PM Manual Payment WORK'
if @DOCTYPE = 6 (Select * from PM10400 where PMNTNMBR=@VCHRNMBR)
End
Begin
print 'PM10500 - PM Tax Work'
Select * from PM10500 where VCHRNMBR=@VCHRNMBR
End
Begin
print 'PM20000 - PM Transaction Open'
select * from PM20000 where VCHRNMBR = @VCHRNMBR and DOCTYPE=@DOCTYPE
End
Begin
print 'PM20100 - PM Apply To OPEN OPEN Temporary'
if @DOCTYPE <=3 (Select * from PM20100 where APTVCHNM=@VCHRNMBR) /*amelroe 10/08/2014*/
if @DOCTYPE >=4 (select * from PM20100 where VCHRNMBR = @VCHRNMBR) /*amelroe 10/08/2014*/
End
Begin
print 'PM20200 - PM Distribution OPEN OPEN Temporary'
if @DOCTYPE <=5 (select * from PM20200 where APTVCHNM = @VCHRNMBR) /*amelroe 10/08/2014*/
if @DOCTYPE = 6 (select * from PM20200 where VCHRNMBR = @VCHRNMBR) /*amelroe 10/08/2014*/
End
Begin
print 'PM30200 - PM Paid Transaction History'
select * from PM30200 where VCHRNMBR = @VCHRNMBR and DOCTYPE=@DOCTYPE
End
Begin
print 'PM30300 - PM Apply To History'
if @DOCTYPE <=3 (Select * from PM30300 where APTVCHNM=@VCHRNMBR AND APTODCTY = @DOCTYPE) /*jnelson amelroe 06/20/2016*/
if @DOCTYPE >=4 (select * from PM30300 where VCHRNMBR = @VCHRNMBR AND DOCTYPE = @DOCTYPE) /*jnelson amelroe 06/9/2016*/
End
Begin
print 'PM30600 - PM Distribution History'
if @DOCTYPE <=5 (select * from PM30600 where VCHRNMBR = @VCHRNMBR and CNTRLTYP=0)
if @DOCTYPE = 6 (select * from PM30600 where VCHRNMBR = @VCHRNMBR and CNTRLTYP=1)
End
Begin
print 'PM30700 - PM Tax History'
select * from PM30700 where VCHRNMBR = @VCHRNMBR and DOCTYPE=@DOCTYPE /*amelroe 10/08/2014*/
End
Begin
print 'PM30800 - PM Tax Invoices'
select * from PM30800 where VCHRNMBR = @VCHRNMBR and DOCTYPE=@DOCTYPE
End
/*Payables Void Temporary Tables*/
print '=================================================================================='
print 'Payables Void Temporary Tables'
print '=================================================================================='
print ''
Begin
print 'PM10600 - PM Distribution Void WORK Temporary'
if @DOCTYPE <=5 (select * from PM10600 where VCHRNMBR = @VCHRNMBR and CNTRLTYP=0)
if @DOCTYPE = 6 (select * from PM10600 where VCHRNMBR = @VCHRNMBR and CNTRLTYP=1)
End
Begin
if @DOCTYPE = 6
print 'PM10801 - PM Payment Stub Duplicate'
if @DOCTYPE = 6 (select * from PM10801 where PMNTNMBR = @VCHRNMBR)
End
Begin
print 'PM10900 - Void Payment WORK Temporary'
select * from PM10900 where VCHRNMBR = @VCHRNMBR and DOCTYPE=@DOCTYPE
End
Begin
print 'PM10901 - PM Void Transaction WORK Temporary'
select * from PM10901 where VCHRNMBR = @VCHRNMBR and DOCTYPE=@DOCTYPE
End
Begin
print 'PM10902 - PM Tax Void Work Temporary'
select * from PM10902 where VCHRNMBR = @VCHRNMBR
End
/*General Ledger*/
print '=================================================================================='
print 'GL info'
print '=================================================================================='
print ''
If Exists (select * from GL10000 where DTAControlNum= @VCHRNMBR AND DTATRXType=@DOCTYPE)
Begin
print 'GL10000 - Transaction Work'
/*print '========'*/
select * from GL10000 where DTAControlNum= @VCHRNMBR AND DTATRXType=@DOCTYPE
End
Else
Begin
Print '**Journal Entry Per Batch**'
print 'GL10000 - Transaction Work'
select * from GL10000 where ORTRXSRC = (SELECT TRXSORCE FROM PM00400 WHERE CNTRLNUM=@VCHRNMBR AND DOCTYPE=@DOCTYPE)
End
If Exists (select * from GL10001 WHERE JRNENTRY IN (SELECT JRNENTRY FROM GL10000 WHERE DTAControlNum= @VCHRNMBR AND DTATRXType=@DOCTYPE))
Begin
print 'GL10001 - Transaction Amounts Work'
/*print '========'*/
select * from GL10001 WHERE JRNENTRY IN (SELECT JRNENTRY FROM GL10000 WHERE DTAControlNum= @VCHRNMBR AND DTATRXType=@DOCTYPE)
End
If Exists (select * from GL20000 where ORCTRNUM = @VCHRNMBR AND ORTRXTYP=@DOCTYPE)
Begin
print 'GL20000 - Year-to-Date Transaction Open'
/*print '========'*/
select * from GL20000 where ORCTRNUM = @VCHRNMBR AND ORTRXTYP=@DOCTYPE
End
Else
Begin
Print '**Journal Entry Per Batch**'
print 'GL20000 - Year-to-Date Transaction Open'
select * from GL20000 where ORTRXSRC = (SELECT TRXSORCE FROM PM00400 WHERE CNTRLNUM=@VCHRNMBR AND DOCTYPE=@DOCTYPE)
END
If Exists (select * from GL30000 where ORCTRNUM = @VCHRNMBR AND ORTRXTYP=@DOCTYPE)
Begin
print 'GL30000 - Account Transaction History'
/*print '========'*/
select * from GL30000 where ORCTRNUM = @VCHRNMBR AND ORTRXTYP=@DOCTYPE
End
Else
Begin
Print '**Journal Entry Per Batch**'
print 'GL30000 - Account Transaction History'
select * from GL30000 where ORTRXSRC = (SELECT TRXSORCE FROM PM00400 WHERE CNTRLNUM=@VCHRNMBR AND DOCTYPE=@DOCTYPE)
End
End
/*Multicurrency Info*/
print '=================================================================================='
print 'Multicurrency Info'
print '=================================================================================='
print ''
Begin
print 'MC020103 - Multicurrency Payables Transactions'
/*print '========'*/
select * from MC020103 where VCHRNMBR=@VCHRNMBR and DOCTYPE=@DOCTYPE
End
Begin
print 'MC020105 - Multicurrency RM Revaluation Activity'
/*print '========'*/
select * from MC020105 where VCHRNMBR=@VCHRNMBR and DOCTYPE=@DOCTYPE
End
/*Bank Reconcilation*/
print '=================================================================================='
print 'Bank Reconcilation'
print '=================================================================================='
print ''
Begin
print 'CM20200 - CM Transaction'
/*print '========'*/
select * from CM20200 where SRCDOCNUM=@VCHRNMBR and SRCDOCTYP=@DOCTYPE
End
Share
Report
5
All responses (
Answers (
