The rules vary a bit depending on what sort of document you have.
For voucher-like documents, RefNbr is unique - so a Join on RefNbr and any
other field sounds reasonable.
For payment-like documents, I believe that in APDoc, BankAcct, BankSub,
RefNbr are unique (ie a cheque number is unique for a given bank account.)
As all documents in a batch must be for the same bank account, then a Join
on RefNbr & BatNbr sounds like it should work.
One thing I haven't considered is pre-payments and multiple-installment
documents - I'm not sure if they might add any complications...
Must admit that if I was doing that Join, I would possibly get cautious (or
is that over-cautious...) and add some extra fields - possibly
DocType/TranType.
However, I note that there is a non-clustered index on RefNbr & BatNbr, so
it is possible that adding extra fields to the Join may make it run slower.
It is also possible that adding extra fields may cause problems with things
like pre-payments...
Barry
--
Barry Flynn
Complete Solutions DG
Post by hassanvoyeauI would like to confirm that APDOC and APTRAN can be joined in a query using
BatNbr and RefNbr.
e.g. if I wanted to list AP transactions and include the Invoice Number from
APDOC table
I noticed that in report 03640b they used this criteria for linking both
tables.