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 HasPartner 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 year14 to 28 = accounting periods of the first open fiscal year29 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 Journal1 = Cash Receipts Journal2 = Cash Disbursements (Payments) Journal3 = Sales Journal4 = Purchase Journal5 = Payroll Journal6 =Cost of Goods Sold Journal7 = Inventory Adjustment Journal8 = Assembly Adjustments (Build/Unbuild) Journal9 = 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 Journal11 = Sales Orders Journal12 = 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 = 0General = 1Consolidation Entry = 2Cash Receipt = 3Receipt Purge Rollup = 4Cash Disbursement = 5Disbursement Purge Rollup = 6Write Check = 7Sales Invoice = 8Customer Credit Memo = 9Progress Billing = 10 (Sage 50 Quantum Accounting)Purchase = 11Vendor Credit Memo = 12Payroll = 13Inventory Adjustment = 14Assembly Adjustment = 15Work Ticket Assembly Adjustment = 16 (Sage 50 Quantum Accounting)Temporarily Below Zero Inventory Adjustment = 17Purchase Order = 18Sales Order = 19Proposal = 20Journal 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 invoice0 = the text note should print before line items on the invoiceFor 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 include0 = regular transaction1 = cash disbursement transaction from Write Checks2 = sales transaction is customer credit memo3 = purchase transaction is vendor credit memo4 = transaction created during the purge process: a "roll up" transaction5 = transaction created during company consolidation6 = assembly transaction related to a work ticket7 = sales transaction is a Progress Billing Invoice8 = 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 transaction1=Some credit card information exists, but no authorization number2=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.

Feedback