Journal Header Fields

The following field information is intended to assist those who design reports for Sage 50 using Crystal Reports.

Filename: JRNLHDR.DAT

JRNLHDR.DAT has one record for each transaction entered in Sage 50. There is a one-to-many relationship between header records and row records. Each header record can have any number of related rows.

Note: Memorized Transactions are stored in StoredTransHeaders.DAT. All the field names are the same as in JrnlHdr.DAT.

Indexes and Their Uses

JRNLHDR.DAT has 22 keys, as follows:

0 = By JournalKey (Shadow, Journal, Period, TrxNumber, Partner.

1 = By PostOrder

2 = By Posted and then by PostOrder

3 = By LedgerKey (Module, CustVendEmpID, Date, and then by PostOrder

4 = By Reference

5 = By Date

6 = By Journal, then by Date, then by Reference, then by LedgerKey.RecordNumber, and then by PostOrder..

7 = By JournalKey_Journal, then by JrnlKey_Per, and then by Reference.

8 = ByGUID

9 = By Journal, then by Reference.

10 = By Journal, then by Completed Date

11 = By ReceiptNumber

12 = By Journal, then by MainAccount, then by Deposit Ticket, and then by Date

13 = By Journal, then by Extended JournalType, then by Date, then by Reference, then by CustVendEmpID, and then by PostOrder

14 = By Journal, then by Date, and then by PostOrder

15 = JrnlHdrByJrnlCVEReference

16 = JrnlHdrByElectronicDateSent

17 = By Recur Number, By Date

18 = By JournalEx, then by Date, then by Reference, then by LedgerKey.RecordNumber, and then by PostOrder

19 = RecurNumber

20 = JrnlHdrByElectronicDateSent

21 = By JournalKey_Journal, then by CustVendId, and finally by Date

22 = ByLedgertKeyModule, then by CustVendEmpID and then by Date

Fields

The following table shows the fields available in the JRNLHDR.DAT file and includes the type and size in number of bytes of each field.

Field Name

Type

Size

Comment

JrnlKey_Partner

Logical

Only used for transactions that are related to other transactions. Journal partner transactions post separately and print in all journals and ledgers, but they do not appear in that journal's edit list box.

JrnlKey_Partner is used in two places:

  • Cash receipts entered in the Sales/Invoicing window or payments entered in the Purchases window are considered Partner journal entries. They appear in customer or vendor ledgers You cannot edit these entries in their the respective cash journals. Instead, you can edit the partner entry in the sales or purchase journal where it was entered. The Period, Shadow and TrxNumber will be the same; all the programmer has to do is change the Journal and set the Partner flag to find the related entry.
  • Reversing entered in the General Journal window and present in the General Journal are also considered "Partner" journal entries. In this case, the Journal remains the same, but the Period is incremented by 1. (Reversing entries are not allowed for the last period of the second (Next) fiscal year. Again, the entry appears in the proper listings at the proper time, but it cannot be edited except by opening the original General Journal entry.

Note: For original transactions (those that cause this partner to be generated), the Have_Partner flag will be true (see below).

JrnlKey_TrxNumber

Integer

Identifies the unique transaction number within each journal and within each accounting period.

JrnlKey_Per

Integer

Identifies the (internal) accounting period number in which the transaction exists. Valid values include:

0 to 13 = accounting periods of the last closed fiscal year

14 to 28 = accounting periods of the first open fiscal year

29 to 41 = accounting periods of the second open fiscal year

Note: About Internal Accounting Period Numbers: Sage 50 internal accounting period numbers differ from what is visible to the user in the application. Also, Sage 50's internal accounting periods include a thirteenth period, which many companies may not use.

JrnlKey_Journal

Integer

Identifies the journal number associated with the transaction. Valid values include:

0 = General Journal

1 = Cash Receipts Journal

2 = Cash Disbursements (Payments) Journal

3 = Sales Journal

4 = Purchase Journal

5 = Payroll Journal

6 =Cost of Goods Sold Journal

7 = Inventory Adjustment Journal

8 = Assembly Adjustments (Build/Unbuild) Journal

9 = TempBelowZeroInvAdj (These are system-generated transactions within Sage 50's inventory costing engine. The Sage 50 user will never see this journal. Also, these transactions do not contain header records.)

10 = Purchase Orders Journal

11 = Sales Orders Journal

12 = Quotes Journal

JrnlKey_Shadow

Logical

1

This is used ONLY for transaction entries that are shadows of other entries; they are posted (generated) automatically (behind the scenes) without going through an actual posting process. An example of this would be when a cash receipt prepayment is posted; a shadow sales journal entry is entered (behind the scenes) at the same time during posting. This shadow entry has the same Period # and TrxNumber as the original entry, so it can be found instantly.

JournalEx

Integer

Expanded journal value:

None = 0

General = 1

Consolidation Entry = 2

Cash Receipt = 3

Receipt Purge Rollup = 4

Cash Disbursement = 5

Disbursement Purge Rollup = 6

Write Check = 7

Sales Invoice = 8

Customer Credit Memo = 9

Progress Billing = 10 (Sage 50 Quantum Accounting)

Purchase = 11

Vendor Credit Memo = 12

Payroll = 13

Inventory Adjustment = 14

Assembly Adjustment = 15

Work Ticket Assembly Adjustment = 16 (Sage 50 Quantum Accounting)

Temporarily Below Zero Inventory Adjustment = 17

Purchase Order = 18

Sales Order = 19

Proposal = 20

Journal Quotes = 21

Module

Integer

Identifies which accounting module the journal entry belongs to (in a broad sense). Valid values are:

‘ ’ (space) and ‘G’ => does not belong in any subledgers.

‘P’ => Accounts Payable

‘R’ => Accounts Receivable

‘Y’ => Payroll

‘I’ => Inventory (most inventory transactions are associated A/R or A/P and will not display ‘I’ )

CustVendId

Integer

If this is a simple transaction, then this field indicates the customer or vendor. Otherwise, it is blank.

TransactionDate

Date

The date of the journal transaction.

JournalGUID

UBinary

GUID (Globally Unique Identifier) used for linking the JrnlHdr table.

PostOrder

Integer

This number is unique to each whole transaction. The same PostOrder can exist on any number of journal rows, but only one journal header can share this number.

Description

ZString

30

Description of the journal entry. Some journal descriptions are system-generated, while most are entered on the respective task window.

MainAmount

Float

The net amount of this transaction. This is only useful when all you want is the transaction amount. You cannot tell from here which account it went to.

Reference

ZString

20

Identifies the journal transaction (for example, invoice number or check number).

TrxIsPosted

Integer

1 (True) => If this transaction has been posted to general ledger.

0 (False) => If this transaction has not been saved but not posted. Unposted (saved) transactions do not appear in “ledger”-type reports because they are not yet valid (real).

TrxIsPrepay

Integer

1 (True) => only if the Prepayment check box is selected for transactions entered in the Receipts or Payments window.

CannotBePurged

Integer

Flag for not eligible for purge.

IsBegBal

Integer

1 (TRUE) => if this transaction is a beginning balance entry (G/L, customer, vendor, or inventory).

Internal

Date

Do not select or use this field. It is used by Sage 50 for internal reasons.

SalesTaxCode

ZString

8

If this is a sale, receipt, or customer credit memo, then this indicates the sales tax code for this transaction.

RecurNumber

Integer

Used to manage recurring journal entries.

Recurr2

Integer

Used to manage recurring journal entries.

EmpRecordNumber

Integer

If this is a sale, receipt, or customer credit memo, then this indicates the sales rep ID associated with this transaction, if any.

Note: Do not confuse with the EmpRecordNumber field in the JrnlRow table, which is used for the employee record number used on the employee paycheck.

HasPartner

Integer

True => if this is the other side of a partner transaction. See JrnlKey_Partner.

TrxIsReadOnly

Integer

True => only if this journal entry is a converted payroll transaction. These transactions cannot be edited.

IsReversed

Integer

True => if the transaction is a general journal entry AND it is a reversing entry.

Memo

ZString

30

The Memo field text entered in either the Payments or Write Checks window. For all other transaction types, this field is not relevant.

DepositTicketID

ZString

8

Deposit ticket ID entered in the Receive Money window. For all other transaction types, this field is not relevant.

DepositDate

Date

Deposit ticket date.

GLAcntNumber

Integer

Primary cash account used for deposits and receipts.

TrxName

ZString

39

(Customer, vendor, or employee) name associated with the transaction.

TrxAddress1

ZString

30

(Customer, vendor, or employee) address line 1 (street address) associated with the transaction.

TrxAddress2

ZString

30

(Customer, vendor, or employee) address line 2 (street address) associated with the transaction.

TrxCity

ZString

20

(Customer, vendor, or employee) city address associated with the transaction.

TrxState

ZString

2

(Customer, vendor, or employee) 2-character state abbreviation associated with the address in transaction.

TrxZIP

ZString

12

(Customer, vendor, or employee) ZIP code address associated with the transaction.

TrxCountry

ZString

15

(Customer, vendor, or employee) country address associated with the transaction.

PaymentMethod

ZString

20

Payment method selected in the Receive Money window. For all other transaction types, this field is not relevant.

PayCustOrRecVend

Logical

True => only if this is a payment to a customer or a receipt from a vendor.

CustVendRecordNumber

Integer

If the previous field is true, then this field identifies the customer or vendor record associated with the transaction.

PurchOrder

ZString

20

If the transaction is a sales invoice or credit memo, then this is the customer’s PO number.

DateDue

Date

Date invoice is due. This payment term field only applies to sales or purchase invoices.

DiscountDate

Date

Latest date in which an early payment discount can be applied. This payment term field only applies to sales or purchase invoices.

DiscountAmount

Float

The discount amount that can be applied if the invoice is paid by the discount date (above). This payment term field only applies to sales or purchase invoices.

TermsDescription

ZString

39

The "displayed " payment term options in text format. This payment term field only applies to sales or purchase invoices and customer credit memos.

GoodThruDate

Date

The last date in which the sales quote is valid. This field is only relevant to quote transactions.

PrintCommentAtEnd

Integer

If this invoice transaction includes a note, this field defines where the note should print on invoice forms. 1 = the text note should print after line items on the invoice

0 = the text note should print before line items on the invoice

For more information about transactions notes and the note text, see Comment1 (below).

IsDropShip

Integer

If this is a purchase invoice, 1 => Drop ship check box is selected and the DropShipRecordNumer (customer record) is relevant.

DropShipRecordNumber

Integer

If IsDropShip = 1, then this field identifies the customer record the purchase transaction is shipped to by the vendor on your companies behalf.

ShipToName

ZString

39

Identifies the ship-to address name associated with this sales transaction. For all other transaction types, this field is not relevant.

ShipToAddress1

ZString

30

Identifies the ship-to address line 1 (street address) associated with sales transaction. For all other transaction types, this field is not relevant.

ShipToAddress2

ZString

30

Identifies the ship-to address line 2 (street address) associated with sales transaction. For all other transaction types, this field is not relevant.

ShipToCity

ZString

20

Identifies the ship-to city address associated with sales transaction. For all other transaction types, this field is not relevant.

ShipToState

ZString

2

Identifies the ship-to two-character state abbreviation associated with sales transaction. For all other transaction types, this field is not relevant.

ShipToZIP

ZString

12

Identifies the ship-to ZIP code address associated with sales transaction. For all other transaction types, this field is not relevant.

ShipToCountry

ZString

15

Identifies the ship-to country address associated with sales transaction. For all other transaction types, this field is not relevant.

ReceiptNum

ZString

20

The receipt number for the transaction.

ShipVia

ZString

20

Identifies the shipping method selected for this sales or purchase transaction. Shipping methods are set up in the Inventory Item Defaults window. For all other transaction types, this field is not relevant.

QuoteIDForSales

ZString

20

Identifies the quote number associated with the sales invoice transaction, if available. This field is only accessible when you convert a quote to a sales invoice and the quote already has a quote number.

ShipDate

Date

Only for sales orders selected on the Sales/Invoicing window, this identifies when a sales invoice was shipped. For all other transaction types, this field is not relevant.

MemorizedID

ZString

20

MemorizedShipToIndex

Integer

4

MemorizRemitToIndex

Integer

4

INV_POSOOrderNumber

ZString

20

Identifies purchase order number or sales order number associated with an invoice transaction.

WaitingForBill

Integer

1 (True) => if the Waiting for Bill check box is selected in the Purchases window for this transaction. For all other transaction types, this field is not relevant.

CustomerInvoiceNo

ZString

20

Applies to purchases and purchase orders. If this is a purchase or a purchase order, then this is the manually entered sales invoice number that appears in the Ship-to dialog box. It represents your company's sales invoice for drop shipments directly from a vendor to a customer. For sales invoices or sales orders, this is the customer's PO number.

CompletedDate

Date

The date that a purchase or sales invoice is fully paid.

POSOisClosed

Integer

1 (True) => if the purchase order or sales order is fulfilled (closed).

LastUsedDistNumber

Integer

Identifies the last used distribution number for line items in sales orders and purchase orders transactions. For all other transaction types, this field is not relevant.

AmountPaid

Float

For sales invoices and purchase invoices only. This indicates how much money has been applied against THIS invoice (in the Receive Money at time of sale and Amount Paid dialogs).

TotalInvoicePaid

Float

For receipts and payments only. This indicates how much of this check was applied to all invoices selected.

EndOfPayPeriod

Date

Date that identifies the end of a pay period selected for all payroll transactions.

WeeksWorked

Integer

Identifies the number of weeks in the payroll period selected for all payroll transactions. This is used to calculate some payroll taxes.

PRHours1

Float

Number of hours worked or salary amount for employee pay level 1.

Note: About Payroll Hours: These are used to calculate the employees gross pay on paychecks. Pay levels are set up in Employee Defaults window and pay rates are set up in the Maintain Employees/Sales Reps window.

PRHours2

Float

Number of hours worked or salary amount for employee pay level 2.

PRHours3

Float

Number of hours worked or salary amount for employee pay level 3.

PRHours4

Float

Number of hours worked or salary amount for employee pay level 4.

PRHours5

Float

Number of hours worked or salary amount for employee pay level 5.

PRHours6

Float

Number of hours worked or salary amount for employee pay level 6.

PRHours7

Float

Number of hours worked or salary amount for employee pay level 7.

PRHours8

Float

Number of hours worked or salary amount for employee pay level 8.

PRHours9

Float

Number of hours worked or salary amount for employee pay level 9.

PRHours10

Float

Number of hours worked or salary amount for employee pay level 10.

PRHours11

Float

Number of hours worked or salary amount for employee pay level 11.

PRHours12

Float

Number of hours worked or salary amount for employee pay level 12.

PRHours13

Float

Number of hours worked or salary amount for employee pay level 13.

PRHours14

Float

Number of hours worked or salary amount for employee pay level 14.

PRHours15

Float

Number of hours worked or salary amount for employee pay level 15.

PRHours16

Float

Number of hours worked or salary amount for employee pay level 16.

PRHours17

Float

Number of hours worked or salary amount for employee pay level 17.

PRHours18

Float

Number of hours worked or salary amount for employee pay level 18.

PRHours19

Float

Number of hours worked or salary amount for employee pay level 19.

PRHours20

Float

Number of hours worked or salary amount for employee pay level 20.

Comment

ZString

2000

The first 249 characters of a transaction (invoice) note, if it exists.

Note: About Transaction Notes: Invoice notes can be entered in the Sales/Invoicing, Purchases, Sales Orders, Purchase Orders, and Quotes windows by selecting the Notes toolbar button. In Sage 50, invoice notes can be up to 2000 characters. The maximum field length for Crystal Reports is 250 characters. Therefore, the note (comment) may be truncated in Crystal Reports.

TrxVoidedBy

Integer

If the transaction is an invoice and if it has been voided using Void Invoices, then this is the PostOrder of the voiding transaction. If this is the voiding invoice, then this field refers to the original invoice.

JrnlTypeEx

Integer

Possible values include

0 = regular transaction

1 = cash disbursement transaction from Write Checks

2 = sales transaction is customer credit memo

3 = purchase transaction is vendor credit memo

4 = transaction created during the purge process: a "roll up" transaction

5 = transaction created during company consolidation

6 = assembly transaction related to a work ticket

7 = sales transaction is a Progress Billing Invoice

8 = sales order transaction is a Proposal

CreditCardName

ZString

39

Name on the credit card

CreditCardAddress1 [1-2]

ZString

30

Address lines for the credit card address

CreditCardCity

ZString

20

City for the credit card address

CreditCardState

ZString

3

State for the credit card address

CreditCardZip

Zstring

12

Zip code for the credit card address

CreditCardCountry

ZString

15

Country for the credit card address

CcStoredAcctRef

UBinary

 

Identifies the vault where the credit card information is stored

CreditCardMaskedNbr

ZString

30

Masked credit card number

CreditCardDate

Date

4

Credit card expiration date

CreditCardAuth

ZString

6

Authorization code for a manual credit card transaction

AuthorizationStatu

Integer

2

0=No credit card information within the transaction

1=Some credit card information exists, but no authorization number

2=Authorization number has been entered (Pending)

3=Authorized by credit card service

CreditCardNote

ZString

 

Note for the credit card transaction

LastActivityDate

Date

4

 

CreditCardAmountAuth

Decimal

12

Amount authorized on the credit card when it was processed

Comment2 [2-3]

ZString

 

 

BeforeInvoiceRef

Integer

1

 

enECommerceSale

Integer

4

 

ECApprovalType

Integer

4

 

TC_DLState

ZString

2

 

TC_DLNumber

ZString

 

 

TC_CheckType

Integer

 

 

TC_BankRoutingNumber

ZString

9

 

TC_AccountNbr

ZString

 

 

TC_CheckNbr

ZString

 

TC_Email

ZString

 

TC_Phone

ZString

20

ShipByDate

Date

Ship By date - the date by which the sales order should ship.

ReturnAuthorization

ZString

20

Return authorization code for credit or return of sales items.

UseDirectDeposit

Integer

4

Flag for company that uses direct-deposit service.

DirDepCmpnyAcctNumb

ZString

17

Company Account Number with the Direct Deposit service.

DirDepCmpnyRtngNumb

ZString

9

Routing number for direct deposit.

IsDDDistLocked1 [1-4]

Integer

4

TransactionCode1 [1-4]

Integer

2

DrctDpstGUID1 [1-4]

Binary

AmtPctToDistribute1 [1-4]

Decimal

Amount1 [1-4]

Decimal

AllocationMethod11-4]

Integer

4

LastUpdateCounter

Integer

4

FromRegister

Integer

1

SuperRecordNumber

UBinary

If a work ticket converted to a transaction, this is the number of the employee acting as supervisor for the transaction (Sage 50 Quantum Accounting).

CustomerSONo

ZString

20

If the transaction is an Auto-Created PO, this is the customer sales order covering the PO (Sage 50 Premium Accounting and higher).

AppName

ZString

14

Name of the application that generated the transaction, if external to Sage 50. In almost all cases, the transaction will come from Timeslips, and the field will read TIMESLIP.

UPSShipmentRecNum

UBinary

If transaction represents a UPS shipment, this is the shipment record number.

Proposal Accepted

Logical

TRUE indicates that a proposal has been accepted; and FALSE indicates when it is not accepted.

PayMethod

Integer

2

Payment method used for payroll transactions

LastPostedAt

DateTime

Date/Time this transaction was last posted

IsManualPayrollCheck

Logical

TRUE indicates that you did not have access to payroll tax formulas at the time this paycheck was last saved;

FALSE indicates that you had access to payroll tax formulas at the time this paycheck was last saved.

CCRelatedTrx Integer 4 This value contains the postorder of the related credit card transaction. This value is used to link a Refund/Void credit card transaction with the original sale transaction in Sage 50. For time of sale (TOS) receipts, the related transaction will have the postorder to the Refund/Void receipt. However, the Refund/Void receipt will have the postorder of the invoice paid. For non-TOS receipts, the credit/void receipt will have the postorder of the original receipt.
CC_VANReference ZString 16 This is the transaction reference assigned by Paya (SPS). It’s a unique identifier used when creating refunds/voids against original transactions. It lets the SPS system know which transaction you will be performing a refund/void on. The VANReference on a refund/void transaction is only used as a reference to the refunds/void transaction in the SPS system.