Stock Schema as at June 10, 2008

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 the latest source code.

Click here for a development system

Development System:

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

DB3C.exe The command DB3C -a -o -f stock runs the automatic compile process for all modules in the system.

DB3L.exe The command DB3L -f stock links all the modules into a single module: stock.dbc — then generates a multi-user executable and overlay file: stock.exe and stock.ovl.

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.

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

** End of Schema