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 |
---|---|---|---|
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:
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. |
|
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. |
|
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 |
|
AccountNumber1 [1-4] |
ZString |
||
RoutingNumber1 [1-4] |
ZString |
||
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. |