Stock Schema

Designed for dBXL, a dBASE III PLUS clone with Quicksilver language extensions

Click here to view instructions on "How to Use" and to download a Development System of dBXL and Quicksilver

DEBTOR.dbf	Main Customer master file See Option 1
Indexes	DEBTOR.ndx Customer number 4 digits DEBTORA.ndx Customer name
	Includes MTD Statement balances Current, 30 day, 60 day, 90 day balances and Bal c/f Previous month as well as numerous other fields.
DEBCLUBS.dbf	Customer Clubs file See bottom line menu via Option 1.2
Index	DEBCLUBS.ndx Customer number 4 digits then Club Number 2 digits
	Customer Clubs - i.e. New Book Club , Album of the month club. Shows quantity required.
MEMFILE.dbf	Customer Notes file See bottom line menu via Option 1.2
Indexes	MEMFILE.ndx Customer number 4 digits then Date of Note MEMFILE2.ndx Date of Note then Customer number 4 digits (Pop up Reminders only)
EMSENT.dbf	Customer Emails sent file See Option 1.8
Index	EMSENT.ndx Customer number 4 digits then Date Sent
	Rarely (if at all) used. Keeps account of Emails Sent directly by Stock program that bypass MS Outlook

BKO.dbf	Main Back Orders Outstanding file See Option 2
Indexes	BKOD.ndx Customer number 4 digits then Product code 7 chars BKOP.ndx Product code 7 chars then Customer number 4 digits

PRODUCT.dbf	Main Product master file See Option 3
Indexes	PRODUCT.ndx Product Code 7 chars ISBNC.ndx ISBN 13 chars DESC.ndx Title CDESC.ndx Supplier's BarCode 13 chars CDESC2.ndx For Reordering purposes: Stock Available flag 1 char, Supplier Code 3 digits, Backorder Flag 1 char, Sales qty (descending) 6 digits CDESC3.ndx Product Category 2 digits, Publisher 3 chars, Title
PRODRPH.dbf	Product Replacement History Linkage File See bottom line menu via Option 3.2
Index	PRODRPH.ndx Product Code 7 chars
	When a new product is substituted to provide ongoing sales of a now deleted product, a linkage can be set up and its history reflected in this history file
ITEMS.dbf	Manufactured Products Linkage file See bottom line menu via Option 3.2
Indexes	ITEMS.ndx Manufactured Product Code 7 chars then Individual Product Item Code 7 chars ITEM2.ndx Individual Product Item Code 7 chars then Manufactured Product Code 7 chars
	Links Manufactured Products to their individual items, and vice-versa.
PMEMFILE.dbf	Product Notes File See bottom line menu via Option 3.2
Index	PMEMFILE.ndx Product Code 7 chars then Date of Entry

CATEGORY.dbf	Main Category master file See Option 4.1 and 4.2
Index	CATEGORY.ndx Category Code 3 digits
	Codes 1-99 Product Categories Codes 101-399 Suppliers - except for CLC Wholesale Codes 401-499 Customer Clubs - i.e. New Book Club , Album of the month club Codes 501-799 Product subjects or sub-categories Codes 801-999 Publisher Codes for PCONTROL.dbf - Product Prefix Control file
CATSUPP.dbf	Supplier master file for CLC Wholesale See Option 4.5
Index	CATSUPP.ndx Supplier Code 3 digits
	Codes 1-395 Suppliers Prior to June 2008 upgrade Codes 501-861 New Suppliers for CBW shops only
REGION.dbf	Sales Region Budgets file See Option 4.6
Index	REGION.ndx Customer Sales Region Code 2 characters
	Provides Monthly and Yearly Sales Budgets for each Sales Region
PCONTROL.dbf	Product Prefix Control file See Option 4.7
Index	PCONTROL.ndx Prefix 3 characters
	Inserts Supplier Code 3 digits and Publisher Code 3 chars when a product with the applicable prefix is created.
PCMASTER.dbf	Postcode Sales Region Lookup file See Option 4.8
Index	PCMASTER.ndx Customer Sales Region Code 2 characters
	Allocates a Sales Region to a specific Postcode / Postcode Range
EXCHANGE.dbf	Supplier Exchange Rate file See Option 4.E
Index	EXCHANGE.ndx Country Code 2 chars
	Provides Exchange Rate conversion as new stock arrives and is entered using foreign currency
GROUPS.dbf	Customer Discount Groups file See Option 4.D
Index	GROUPS.ndx Discount Group 1 char then Supplier Code 3 digits then Category Code / Product Code7 chars
	Provides a number of Discount price variations for customer groups such as Koorong for 1. a specific product, or 2. a specific supplier and product category, or 3. a specific supplier, or 4. just overall for that group.
SALECODE.dbf	Sales Code file See Option 4.S
Index	SALECODE.ndx Sales Code 2 digits
	Provides a list of the Sales Codes used when entering Invoices / Credit Notes
CCLASS.dbf	Customer Classes file See Option 4.C
Index	CCLASS.ndx Customer Class 1 char
	Provides a list of the Customer Class Groups (up to 5) that each customer can be a member of
GRIDFILE.dbf	Card Grid file See Option 4.G
Index	GRIDFILE.ndx Grid ID 6 characters
	Provides a lookup for current product code 7 chars attached to a Card ID

RUNTRANS.dbf	Worksheet, Invoice, CreditNote, Payment, Journal, Consignment — Main Input file Used by Options 5.1 and 5.2
Indexes	RUNTRANS.ndx Action 1char then Number 5 digits then Print order RUNTRAN2.ndx Product Code 7 chars then Action 1char then Number 5 digits RUNTRAN3.ndx Delivery Account 4 digits then Action 1char then Number 5 digits then Warehouse Bin
RUNCTRL.dbf	Worksheets, Invoices, Credits Input Status file Used by Options 5.1 and 5.2
Index	RUNCTRL.ndx Number 5 digits
RUNBATCH.dbf	Web Orders Batch Control file See Option 5.1.B
Index	RUNBATCH.ndx File 4 characters
	Shows date a specific batch file number was downloaded from web
DAUDIT.dbf	Deleted Transactions Audit file See Option 5.D
Index	DAUDIT.ndx Customer Account 4 digits then Date of deletion
	Provides audit trail of transactions cancelled prior to update (Option 5.4)
YTDTRANS.dbf	Main Accounts History file Updated by Option 5.4
Indexes	YTDTRANS.ndx Statement Account Customer number 4 digits then Date Applicable YTDD.ndx Delivery Account Customer number 4 digits then Date Issued YTDNUM.ndx Action 1 char then Number 5 digits then Date Issued—descending
	Updated automatically by Option 5.4. Contains many years of account statement history - not just one year to date.
CONTRANS.dbf	Main Consignment History file Updated by Option 5.4
Indexes	CONTRAND.ndx Customer number 4 digits then Product 7 chars then Date then Action 1 char (C or I) CONTRANP.ndx Product 7 chars then Customer number 4 digits then Date then Action 1 char (C or I) CONTRAN2.ndx Customer number 4 digits then Product 7 chars then Action 1 char (C or I) then Date—Descending for last consigned price.
STKTRANS.dbf	Main Stock Sales History file Updated by Option 5.4
Indexes	STKD.ndx Customer number 4 digits then Product Code 7 chars then Date STKP.ndx Product Code 7 chars then Customer number 4 digits then Date
STKMONTH.dbf	MTD Inv, Credits History file Updated by Option 5.4
Index	STKMONTH.ndx Action 1char then Number 5 digits then Print order
	Updated automatically by Option 5.4. Used to reprint invoices or credits.
RUNCTRH.dbf	Inv, Credits History Status file Updated by Option 5.4
Index	RUNCTRH.ndx Number 5 digits
PTDTRANS.dbf	Inv, Pays, Jnls, Credits MTD Transaction file Updated by Option 5.4
Indexes	PTDTRANS.ndx Statement Account Customer number 4 digits then Date Transaction Issued PTDD.ndx Delivery Account Customer number 4 digits then Date Transaction Issued
	Updated automatically by Option 5.4. Used when printing statements and for MTD Sales Reports.
FMTD.dbf	Customer Freight MTD file Updated by Option 5.4 Used by Option 1.4.
Index	FMTD.ndx Delivery Account Customer number 4 digits then date
	Used to show actual sales MTD on Customer Enquiry, by reducing Invoiced total by Freight total
FTRANS.dbf	Customer Freight to date file Updated by Option 8.1 Used by Option 1.4.
Index	FTRANS.ndx Delivery Account Customer number 4 digits then date
	Used to show actual sales YTD on Customer Enquiry, by reducing Invoiced total by Freight total

ORDTRANS.dbf	Main Purchase Orders Outstanding file See Option P
Indexes	ORDTRANS.ndx Supplier Code 3 digits then Order Number 5 digits then Product Code 7 chars ORDTRAN2.ndx Product Code 7 chars ORDTRAN3.ndx Supplier Code 3 digits, Product Code 7 chars
PURHDR.dbf	Current Purchase Orders Status file See Option P.C
Index	PURHDR.ndx Supplier Code 3 digits then Supplier Invoice Number
PURTRANS.dbf	Main Purchases History file Updated by Option P.3 (or 5.4)
Index	PURTRANS.ndx Product Code 7 chars then Date Supplied

ARCHIVES
JAN.dbf - DEC.dbf	Copy of Previous STKMONTH.dbf. Updated by Option 8.1
Index	JAN.ndx - DEC.ndx Action 1char then Number 5 digits then Print order
	Stored in ARCHIVE sub-directory initially, then 12 months later, copied to a sub-directory that has the 4 digits of the year number (e.g. 2007) to save being overwritten. Used to reprint invoices or credits.

2003.dbf (2004.dbf, etc)	Stock Sales History file for previous calendar years. Updated by Option 8.2
Indexes	2003D.ndx (2004D.ndx etc) Delivery Account Customer number 4 digits then Product Code 7 chars then Date 2003P.ndx (2004P.ndx etc) Product Code 7 chars then Delivery Account Customer number 4 digits then Date
2003s.dbf (2004s.dbf, etc)	Stock Sales Summary file of total sales by product code for each customer for previous calendar years. Updated by Option 8.2
	Stored in OLDSTOCK sub-directory. Used to keep track of past sales.

OTHER SUB-DIRECTORIES
UPDATE	Copy of Data Files prior to Last Update (Options 5.4, 8.1 and 8.2)
LG	All those same files following a Last Successful (Good) Update Option 5.4 only
LMTH	All the files prior to last End of Month update Option 8.1
LYR	Various Master files prior to last End of Year update Option 8.2
MONTHLY	Various MTD files placed in the appropriate sub-sub-directory using YYYYmmm where YYYY is the year number and mmm is the month. Run prior to an End of Month update Option 8.1
CONTROLS	Used to store Control Totals Option 5.6 when Email sub-option chosen only
YTDSALES	Recent directory used to generate monthly management reports
WORK	Temporary work files only

Main Processing Options

5.4 End of Day - Locks in completed invoices, credits, payments, journals and consignments.

8.1 End of Month

8.2 End of Year

P.3 Locks in Purchase Orders Received - Can be included in an End of Day.

A copy is taken to the UPDATE sub-directory before Options 5.4, 8.1, and 8.2 are run of all critical files . Exclusive use of these files is then required. A flag is set, the process is run, then the flag is cleared. In the event of a power fail or any other error causing the process to fail, the system automatically rolls back the UPDATE folder when the system is restarted.

SETUP.mem is a System Control file that stores approx 60 memory variables. Many can be updated manually in Option 9.4.

DEBTORnn.mem is a Local Control file for approx 40 memory variables applicable to the workstation. nn is generated by a command line environment variable SCREEN (e.g. SET SCREEN=5)

Click here for some background on Stephen Williamson Computing Services Pty Ltd

Click here for a development system for dBXL and Quicksilver

TS.exe A command line utility to Search all files for a specific piece of text.

FORMATSC.exe A command line utility to format .prg files, individually or as a batched run, indenting do while ... enddo loops and if ... endif conditions.

DBXL.exe The command DBXL runs this interpreter.
The command DBXL stock runs loading module stock.prg in interpretive mode. DBXL is a single-user dBASE III PLUS clone with Quicksilver language extensions.

DB3C.exe The compile command DB3C -a -o -f stock translates all of the dBASE III PLUS instructions into machine-level instructions, referred to as d-Code.

DB3L.exe The linker command DB3L -f stock assigns memory locations for these sets of instructions, places all of the d-Code instructions into overlay files within a single file: stock.dbc — then creates a multi-user run-time system in the form of stock.exe (and stock.ovl).

** End of Schema