All Payables Info Script
/*********************************************************************************
** 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 Distriubtion 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 = '0010049'
select @DOCTYPE = '1'
----------------------------------------------------------------------------------
/*Payables info*/
print '=================================================================================='
print 'Payables info'
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 )
if @DOCTYPE >=4 (select * from PM10200 where VCHRNMBR=@VCHRNMBR )
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 PM10201 where APTVCHNM=@VCHRNMBR )
if @DOCTYPE >=4 (select * from PM10201 where PMNTNMBR = @VCHRNMBR)
End
Begin
print 'PM20200 - PM Distribution OPEN OPEN Temporary'
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 '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 )
if @DOCTYPE >=4 (select * from PM30300 where VCHRNMBR = @VCHRNMBR)
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 PM30200 where VCHRNMBR = @VCHRNMBR and DOCTYPE=@DOCTYPE
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 ''
Begin
print 'GL10000 - Transaction Work'
/*print '========'*/
select * from GL10000 where DTAControlNum= @VCHRNMBR AND DTATRXType =@DOCTYPE
End
Begin
print 'GL10001 - Transaction Amounts Work'
/*print '========'*/
select * from GL10001 WHERE JRNENTRY IN ( SELECT JRNENTRY FROM GL10000 WHERE DTAControlNum= @VCHRNMBR AND DTATRXType= @DOCTYPE)
End
Begin
print 'GL20000 - Year-to-Date Transaction Open'
/*print '========'*/
select * from GL20000 where ORCTRNUM = @VCHRNMBR AND ORTRXTYP=@DOCTYPE
End
Begin
print 'GL30000 - Account Transaction History'
/*print '========'*/
select * from GL30000 where ORCTRNUM = @VCHRNMBR AND ORTRXTYP=@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