Complete Invoice detail and Payment detail needed - AP 1

[jcw12000]

HI

I am very new to Accpac.

I am trying to compile a "Invoice table" for a 3rd party program for reporting. What a need is basic invoice detail and the payment detail of the invoices linked. All invoices and payments for the AP side. Hope you guys understand what I need......

Where do I start?

What tables do I need to use?

What are the links between tables?

thanks

 

[Jay Converse]

APOBL - Invoices
APOBP - Payments

The links will be obvious.

 

[jcw12000]

Thanks did know where to start as ALL AP tables were dumped on me with no documentation or anything..

 

[Jay Converse]

What kind of a report are you trying to make? You may need other tables if you need GL accounts.

 

[jcw12000]

Well I need to show AP invoice details the normal stuff you see on the paper invoice with the payment detail (normal remitance tipe stuff) and if possible the TAX amounts and the tax amounts posted to the Input Tax side

 

[Jay Converse]

Then use APPJH and APPJD, that's the posting journal, it has tax fields and GL accounts.

 

[jcw12000]

Looks like with your help I am going in the right way

 

[jcw12000]

With all the help I am still not getting the write results. What I need is a script or joins to have all the invoices (closed transaction) and their corresponding payment/IDRMIT numbers with all the payment detail.

The prepayments are also giving me trouble

 

[Jay Converse]

APOBL->APOBP

 

[jcw12000]

what joins/links do I need to use to get the info. do I need to split data or what

 

[Jay Converse]

Join IDVEND and IDINVC, there's no "splitting" data, whatever that means.

 

[jcw12000]

What I ment by that is to filter for only invoice transactions and only payment transactions.


I have joined the tables by that way. Alot of the transactions link to prepayments (PP0000000xxxx). How do I get the PY000000xxx nubers for those PP000000xxx transactions?


I also see that some of my PY000000xxxx Remitance amounts are 0.00 due to the PP000000XXXX . Is that normal?

 

[Jay Converse]

Roto ID:AP0025 DLL Name: APOBL Documents


Index List - 7 Index(es)
0 - Vendor Number/Document Number
IDVEND, IDINVC
1 - Paid/Order Number
SWPAID, IDORDERNBR, IDVEND, IDINVC
2 - Paid/PO Number
SWPAID, IDPONBR, IDVEND, IDINVC
3 - Due Date
DATEINVCDU, IDVEND, IDINVC
4 - Paid/Vendor Number
SWPAID, IDVEND, IDPREPAY
5 - Vendor Number/Invoice Date
IDVEND, DATEINVC
6 - Retainage Outstanding/Vendor Num
SWRTGOUT, IDVEND, IDINVC

Field List - 140 fields

Name Type Size Prec Description Index Attrib Initial Value
IDVEND String 12 0 Vendor Number 1 RKEA
Mask Presentation - %-12C
IDINVC String 22 0 Document Number 2 RKEA
Mask Presentation - %-22C
IDRMIT String 12 0 Check Number 3 EA
Mask Presentation - %-12D
IDORDERNBR String 22 0 Order Number 4 EA
Mask Presentation - %-22C
IDPONBR String 22 0 PO Number 5 EA
Mask Presentation - %-22C
DATEINVCDU Date 5 0 Due Date 6 EA
IDRMITTO String 6 0 Remit-To Location 7 EA
Mask Presentation - %-6N
IDTRXTYPE Integer 2 0 Transaction Type 8 EA 12
List Presentation with 8 items in the list:
12 - Invoice - Summary Entered
13 - Invoice - Recurring Charge
22 - Debit Note - Summary Entered
26 - Debit Note - Advance Credit Claim
32 - Credit Note - Summary Entered
40 - Interest Charge
50 - Prepayment - Posted
51 - Payment - Posted
TXTTRXTYPE Integer 2 0 Document Type 9 EA 1
List Presentation with 6 items in the list:
1 - Invoice
2 - Debit Note
3 - Credit Note
4 - Interest
10 - Prepayment
11 - Payment
DATEBTCH Date 5 0 Batch Date 10 EA
CNTBTCH BCD 5 0 Batch Number 11 EA 0
CNTITEM BCD 4 0 Entry Number 12 EA 0
IDVENDGRP String 6 0 Group Code 13 EA
Mask Presentation - %-6N
DESCINVC String 60 0 Doc. Description 14 EA
DATEINVC Date 5 0 Doc. Date 15 EA
DATEASOF Date 5 0 Invoice as-of Date 16 EA
CODETERM String 6 0 Terms 17 EA
Mask Presentation - %-6N
DATEDISC Date 5 0 Discount Date 18 EA
CODECURN String 3 0 Currency Code 19 EA USD
Mask Presentation - %-3N
IDRATETYPE String 2 0 Rate Type 20 EA SP
Mask Presentation - %-2N
SWRATEOVRD Integer 2 0 Rate Overridden 21 EA 0
List Presentation with 2 items in the list:
0 - No
1 - Yes
EXCHRATEHC BCD 8 7 Exchange Rate 22 EA 1
AMTINVCHC BCD 10 3 Func. Currency Invoice Amount 23 EA 0
AMTDUEHC BCD 10 3 Func. Currency Amount Due 24 EA 0
AMTTXBLHC BCD 10 3 Func. Currency Taxable Amount 25 EA 0
AMTNONTXHC BCD 10 3 Func. Currency Non-Taxable Amt. 26 EA 0
AMTTAXHC BCD 10 3 Func. Currency Tax Amount 27 EA 0
AMTDISCHC BCD 10 3 Func. Currency Discount Amount 28 EA 0
AMTINVCTC BCD 10 3 Vend. Currency Invoice Amount 29 EA 0
AMTDUETC BCD 10 3 Vend. Currency Amount Due 30 EA 0
AMTTXBLTC BCD 10 3 Vend. Currency Taxable Amount 31 EA 0
AMTNONTXTC BCD 10 3 Vend. Currency Non-Taxable Amt. 32 EA 0
AMTTAXTC BCD 10 3 Vend. Currency Tax Amount 33 EA 0
AMTDISCTC BCD 10 3 Vend. Currency Discount Amount 34 EA 0
SWPAID Integer 2 0 Fully Paid 35 EA 0
List Presentation with 2 items in the list:
0 - No
1 - Yes
DATELSTACT Date 5 0 Last Activity Date 36 EA
DATELSTSTM Date 5 0 Last Statement Date 37 EA
CNTTOTPAYM BCD 3 0 Number of Scheduled Payments 38 EA 0
CNTLSTPAYM BCD 3 0 Reserved - Last Payment Number P 39 E
CNTLSTPYST BCD 3 0 Payment Number on Last Statement 40 EA 0
AMTREMIT BCD 10 3 Reserved - Payment Amount 41 E
CNTLASTSCH BCD 3 0 Last Applied Payment Seq. No. 42 EA 0
SWTAXOVRD Integer 2 0 Tax Amount Control 43 EA 0
List Presentation with 3 items in the list:
0 - Enter
1 - Calculate
2 - Distribute
CODETAX1 String 12 0 Tax Auth. 1 44 EA
Mask Presentation - %-12N
CODETAX2 String 12 0 Tax Auth. 2 45 EA
Mask Presentation - %-12N
CODETAX3 String 12 0 Tax Auth. 3 46 EA
Mask Presentation - %-12N
CODETAX4 String 12 0 Tax Auth. 4 47 EA
Mask Presentation - %-12N
CODETAX5 String 12 0 Tax Auth. 5 48 EA
Mask Presentation - %-12N
AMTBASE1HC BCD 10 3 Func. Base Amount 1 49 EA 0
AMTBASE2HC BCD 10 3 Func. Base Amount 2 50 EA 0
AMTBASE3HC BCD 10 3 Func. Base Amount 3 51 EA 0
AMTBASE4HC BCD 10 3 Func. Base Amount 4 52 EA 0
AMTBASE5HC BCD 10 3 Func. Base Amount 5 53 EA 0
AMTTAX1HC BCD 10 3 Func. Tax Amount 1 54 EA 0
AMTTAX2HC BCD 10 3 Func. Tax Amount 2 55 EA 0
AMTTAX3HC BCD 10 3 Func. Tax Amount 3 56 EA 0
AMTTAX4HC BCD 10 3 Func. Tax Amount 4 57 EA 0
AMTTAX5HC BCD 10 3 Func. Tax Amount 5 58 EA 0
AMTBASE1TC BCD 10 3 Vend. Base Amount 1 59 EA 0
AMTBASE2TC BCD 10 3 Vend. Base Amount 2 60 EA 0
AMTBASE3TC BCD 10 3 Vend. Base Amount 3 61 EA 0
AMTBASE4TC BCD 10 3 Vend. Base Amount 4 62 EA 0
AMTBASE5TC BCD 10 3 Vend. Base Amount 5 63 EA 0
AMTTAX1TC BCD 10 3 Vend. Tax Amount 1 64 EA 0
AMTTAX2TC BCD 10 3 Vend. Tax Amount 2 65 EA 0
AMTTAX3TC BCD 10 3 Vend. Tax Amount 3 66 EA 0
AMTTAX4TC BCD 10 3 Vend. Tax Amount 4 67 EA 0
AMTTAX5TC BCD 10 3 Vend. Tax Amount 5 68 EA 0
FISCYR String 4 0 Fiscal Year 77 EA 0000
Mask Presentation - %04D
FISCPER String 2 0 Fiscal Period 78 EA 00
Mask Presentation - %02D
IDPREPAY String 22 0 Prepay Invoice Number 79 EA
Mask Presentation - %-22C
DATEBUS Date 5 0 Posting Date 80 EA
ID1099CLAS String 6 0 1099/CPRS Code 81 EA
Mask Presentation - %-6N
AMT1099ORG BCD 10 3 1099/CPRS Original Amount 82 EA 0
AMT1099REM BCD 10 3 1099/CPRS Remaining Amount 83 EA 0
RATEDATE Date 5 0 Rate Date 84 EA
RATEOP Integer 2 0 Rate Operator 85 EA 0
YPLASTACT String 6 0 Last Activity Year/Period 86 EA
IDBANK String 8 0 Bank Code 87 EA
Mask Presentation - %-8N
LONGSERIAL Long 4 0 Check Serial Number 88 EA 0
POSTSEQNCE BCD 5 0 Posting Sequence No. 89 EA 0
SWJOB Integer 2 0 Job Related 90 EA 0
List Presentation with 2 items in the list:
0 - No
1 - Yes
SWRTG Integer 2 0 Has Retainage 91 EA 0
List Presentation with 2 items in the list:
0 - No
1 - Yes
SWRTGOUT Integer 2 0 Retainage Outstanding 92 EA 0
List Presentation with 2 items in the list:
0 - No
1 - Yes
RTGDATEDUE Date 5 0 Date Retainage Due 93 EA
RTGOAMTHC BCD 10 3 Func. Curr. Orig. Rtng. Amt. 94 EA 0
RTGAMTHC BCD 10 3 Func. Curr. Retainage Amount 95 EA 0
RTGOAMTTC BCD 10 3 Vend. Curr. Orig. Rtng. Amt. 96 EA 0
RTGAMTTC BCD 10 3 Vend. Curr. Retainage Amount 97 EA 0
RTGTERMS String 6 0 Retainage Terms Code 98 EA
Mask Presentation - %-6N
SWRTGRATE Integer 2 0 Retainage Exchange Rate 99 EA 0
List Presentation with 2 items in the list:
0 - Use Original Document Exchange Rate
1 - Use Current Exchange Rate
RTGAPPLYTO String 22 0 Original Doc. No. 100 EA
Mask Presentation - %-22C
VALUES Long 4 0 Optional Fields 101 EA 0
SRCEAPPL String 2 0 Source Application 102 EA AP
Mask Presentation - %-2A
SWPYSTTS Integer 2 0 Payment Status 103 EA 0
List Presentation with 3 items in the list:
0 - Normal
1 - On Hold
2 - Forced
DATEPYSTTS Date 5 0 Date Payment Status Changed 104 EA
APVERSION String 3 0 A/P Version Created In 105 EA 60A
TYPEBTCH String 2 0 Batch Type 106 EA
Mask Presentation - %-2N
CNTOBLJ Long 4 0 Number of OBLJ Details 107 EA 0
CODECURNRC String 3 0 Tax Reporting Currency Code 108 EA
RATERC BCD 8 7 Tax Reporting Exchange Rate 109 EA 0
RATETYPERC String 2 0 Tax Reporting Rate Type 110 EA
RATEDATERC Date 5 0 Tax Reporting Rate Date 111 EA
RATEOPRC Integer 2 0 Tax Reporting Rate Operator 112 EA 0
SWRATERC Integer 2 0 Tax Reporting Rate Override 113 EA 0
List Presentation with 2 items in the list:
0 - No
1 - Yes
SWTXRTGRPT Integer 2 0 Report Retainage Tax 114 EA 0
CODETAXGRP String 12 0 Tax Group 115 EA
TAXVERSION Long 4 0 Tax State Version 116 EA 0
SWTXBSECTL Integer 2 0 Tax Base Calculate Method 117 EA 0
SWTXCTLRC Integer 2 0 Tax Reporting Calculate Method 118 EA 0
TAXCLASS1 Integer 2 0 Tax Class 1 119 EA 0
TAXCLASS2 Integer 2 0 Tax Class 2 120 EA 0
TAXCLASS3 Integer 2 0 Tax Class 3 121 EA 0
TAXCLASS4 Integer 2 0 Tax Class 4 122 EA 0
TAXCLASS5 Integer 2 0 Tax Class 5 123 EA 0
SWTAXINCL1 Integer 2 0 Tax Included 1 124 EA 0
List Presentation with 2 items in the list:
0 - No
1 - Yes
SWTAXINCL2 Integer 2 0 Tax Included 2 125 EA 0
List Presentation with 2 items in the list:
0 - No
1 - Yes
SWTAXINCL3 Integer 2 0 Tax Included 3 126 EA 0
List Presentation with 2 items in the list:
0 - No
1 - Yes
SWTAXINCL4 Integer 2 0 Tax Included 4 127 EA 0
List Presentation with 2 items in the list:
0 - No
1 - Yes
SWTAXINCL5 Integer 2 0 Tax Included 5 128 EA 0
List Presentation with 2 items in the list:
0 - No
1 - Yes
TXBSERT1TC BCD 10 3 Tax Base 1 129 EA 0
TXBSERT2TC BCD 10 3 Tax Base 2 130 EA 0
TXBSERT3TC BCD 10 3 Tax Base 3 131 EA 0
TXBSERT4TC BCD 10 3 Tax Base 4 132 EA 0
TXBSERT5TC BCD 10 3 Tax Base 5 133 EA 0
TXAMTRT1TC BCD 10 3 Tax Amount 1 134 EA 0
TXAMTRT2TC BCD 10 3 Tax Amount 2 135 EA 0
TXAMTRT3TC BCD 10 3 Tax Amount 3 136 EA 0
TXAMTRT4TC BCD 10 3 Tax Amount 4 137 EA 0
TXAMTRT5TC BCD 10 3 Tax Amount 5 138 EA 0
DATEFRSTBK Date 5 0 Earliest Backdated Activity Date 139 EA
DATELSTRVL Date 5 0 Last Revaluation Date 140 EA
ORATE BCD 8 7 Orig. Exchange Rate 141 EA 0
ORATETYPE String 2 0 Orig. Rate Type 142 EA
ORATEDATE Date 5 0 Orig. Rate Date 143 EA
ORATEOP Integer 2 0 Orig. Rate Operator 144 EA 0
OSWRATE Integer 2 0 Orig. Rate Override Flag 145 EA 0
List Presentation with 2 items in the list:
0 - No
1 - Yes
IDACCTSET String 6 0 Account Set 146 EA
Mask Presentation - %-6N
DATEPAID Date 5 0 Date Paid 147 EA
SWNONRCVBL Integer 2 0 Misc. Payment Flag 148 EA 0