When entering a credit in ASC, which table is the reference entered kept?
User trying to enter a debit note from a customer but the system is saying that the reference has already been used, where is this reference held?
Solution 1
When you enter a credit note in ASC, the reference you type in is stored in the transaction header table for the relevant ledger:
Sales ledger (debtors) credit note: SL_TRANSACTIONS table, column ST_HEADER_REF. A concatenated copy (reference + 'C') is also kept in ST_CON_REFTYPE for uniqueness checks.
Purchase ledger (creditors) credit note:PL_TRANSACTIONS table, column PT_HEADER_REF (with PT_CON_REFTYPE holding the ref + 'C' concatenation).
Both are written for credit notes with ST_TRANTYPE/PT_TRANTYPE = 'CRN'.
Note there's a separate PT_INTERNAL_REF/internal reference column used for system-generated credit note numbers when sub-ledgers are in use (SYS_SUB_LEDGERS.SUBL_CREDIT_NO counter).
If instead you mean a nominal/general journal credit entry, the transaction reference is field L-RF ("Transaction Reference") in the ledger record layout (LIB/SALDGRRC.CPY).
The "reference has already been used" check for sales ledger transactions is done by AA_CHECK_INV_CRN_NUMBERS_S, which looks in SL_TRANSACTIONS; specifically the column ST_CON_REFTYPE, not the raw ST_HEADER_REF.
ST_CON_REFTYPE is the entered reference concatenated with a type suffix, so the same number can exist once per transaction type:
'I'= invoice/debit note (INV),'C'= credit note (CRN),'P'= receipt (PAY),'A'= anything else.
Solution 2
So a debit note entered as INV with reference 12345 will clash with any existing row where ST_CON_REFTYPE = '12345I' ; including an old invoice using that number, not just other debit notes. If sub-ledgers are in use, the check is scoped per sub-ledger via ST_SUB_LEDGER.
Two ways to find the offending row:
-- Non-sub-ledger system
select ST_TRANTYPE, ST_HEADER_REF, ST_CON_REFTYPE, ST_ACCOUNT, ST_DATE
from SL_TRANSACTIONS
where ST_CON_REFTYPE = '<ref>' + 'I' and isnull(ST_SUB_LEDGER, '') = '';
-- If the reference is purely numeric, it can also clash with an unpicked sales order
select OH_ORDER_NUMBER, OH_HEADER_REF, OH_BATCH_FLAG
from ORD_HEADER
where OH_HEADER_REF = '<ref>' and OH_BATCH_FLAG = 1;
That second query matters: if the reference is numeric, the check also fails if it matches ORD_HEADER.OH_HEADER_REF on a batched (unposted) sales order. So the clash can be against a sales order number, not just a posted transaction.
If your colleagues are entering this through the purchase ledger side instead, the equivalent check is on PL_TRANSACTIONS.PT_CON_REFTYPE (plus POP_HEADER.POH_HEADER_REF for purchase orders).